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

 

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()

　　

openpyxl_chart_demos（openpyxl三：图表相关操作：创建图表、使用轴、图表布局）

https://blog.csdn.net/weixin_44015805/article/details/103392673