paython使用openpyxl处理excel

有哪些python处理excel的教程：https://www.zhihu.com/question/35904647

知乎专栏：https://www.zhihu.com/people/mars-15-29/columns

openpyxl官方文档：https://openpyxl.readthedocs.io/en/stable/charts/area.html

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

from openpyxl import Workbook
from openpyxl import load_workbook
import random
 
# 新建excel
def new_excel():
    # 实例化
    wb = Workbook()
    # 激活 worksheet
    ws = wb.active
    # 保存表格
    wb.save("test.xlsx")
 
# 打开excel
def open_excel():
    wb2 = load_workbook("test.xlsx")
    print(wb2.sheetnames)
 
# 新建工作簿
def new_sheet():
    wb = Workbook()
    ws = wb.active
 
    # 新建的工作簿插到末尾
    ws1 = wb.create_sheet("Myshee1")
    print(wb.sheetnames)
 
    # 新建的工作簿插到首部
    ws2 = wb.create_sheet("Mysheet2", 0)
    print(wb.sheetnames)
 
    # 新建的工作簿插到倒数第二个位置
    ws3 = wb.create_sheet("Mysheet3", -1)
    print(wb.sheetnames)
 
    wb.save("new_sheet.xlsx")
 
# 更新工作簿
def update_sheet():
    wb = Workbook()
 
    ws = wb.active
    print(wb.sheetnames)
 
    ws.title = "New Title"
    print(wb.sheetnames)
 
    wb.save("update_sheet.xlsx")
 
# excel添加单列
def insert_excel_data():
    wb = Workbook()
    ws = wb.active
 
    ws['A4'] = 10
    c=ws['A4'].value
    print(c)
 
    d=ws.cell(4,2,1000)
    print(d.value)
 
    wb.save("insert_excel_data.xlsx")
 
# excel添加多列
def insert_excel_datas():
    wb = Workbook()
    ws = wb.active
 
    for i in range(1, 40):
        for j in range(1, 60):
            ws.cell(i, j, random.randint(0, 60))
 
    # 使用切片访问
    range_data = ws['A1':'D40']
 
    # 使用列访问
    colC = ws['C']
    col_range = ws['C:D']
 
    # 使用行访问
    row10 = ws[10]
    row_range = ws[5:10]
 
    wb.save("update_excel_datas.xlsx")
 
# 插入行和列
def insert_excel_rows_cols():
    wb = load_workbook("update_excel_datas.xlsx")
    ws = wb.active
    # 默认值为1行或1列。例如，在第7行（在现有第7行之前）插入1行：
    ws.insert_rows(7)
    # 例如，在第H列（在现有第H列之前）插入3列。
    ws.insert_cols(8, 3)
    wb.save("insert_excel_rows_cols.xlsx")
 
# 删除行和列
def delete_excel_rows_cols():
    wb = load_workbook("update_excel_datas.xlsx")
    ws = wb.active
    # 从col == idx删除一列或多列
    # 例如，删除列F:H
    ws.delete_cols(6, 3)
    # 从row == idx删除一行或多行
    # 例如，删除行F:H
    ws.delete_rows(6, 3)
    wb.save("delete_excel_rows_cols.xlsx")
 
# 数学计算
def sum_and_average():
    wb = load_workbook("update_excel_datas.xlsx")
    ws = wb.active
 
    for i in range(1, 40):
        for j in range(1, 60):
            ws.cell(i, j, random.randint(0, 60))
 
    ws['F45'] = "=SUM(B1:F39)"
    ws['F46'] = "=AVERAGE(B2:D30)"
 
    wb.save("sum_and_average.xlsx")
 
def test():
    print("test")
 
if __name__ == '__main__':
    test()