openpyxl操作表格的基本用法

创建文件， 以及创建xlsx表格

from openpyxl import Workbook, load_workbook
import os


# 创建excel文件, 默认会有一个sheet命名的表
def create_xlsx(_path, _name):
    path_file = os.path.join(_path, _name)
    # 创建一个空白工作簿
    wb = Workbook()
    # 注意： 保存路径下含有相同文件会被覆盖,
    wb.save(path_file)


# 创建工文件， 同时创建表
def create_table(_path, _name):
    path_file = os.path.join(_path, _name)
    # 创建一个空白工作簿
    wb = Workbook()
    # 创建一个表名： sheet1, 位置再第一位
    ws = wb.create_sheet('sheet1', 0)
    ws2 = wb.create_sheet('sheet2', 1)
    # 保存文件
    wb.save(path_file)

读取表格的相关属性， 或者值

from openpyxl import Workbook, load_workbook
wb = load_workbook('work2.xlsx')
# 读取文件的所有表名
table_name = wb.sheetnames
# 根据表名获取表,
sheet_name = table_name[0]
# 读取某一个表
sheet = wb[sheet_name]
# 读取表格的某一行数据
row_data = sheet['1']
# 获取某一列数据, 获取的结果是tuple: (<Cell '订单'.A1>, <Cell '订单'.B1>)
col_data = sheet['B']
# 获取某一个单元格的数据
one_cell = sheet['B2'].value
# 获取单元格的值
cell_data = sheet.cell(row=2, column=3).value
# 修改某一个单元格的值, 最后必须要保存
sheet['B2'].value = 'hello'

读取excel数据， 结果返回： 每一行为dict, 添加到list中

def dict_key(ws):
    for row in ws.rows:
        key_value = [data.value for data in row]
        return key_value
        

# 读取文件内容以及表的内容
def read_xlsx(path_name):
    wb = load_workbook(path_name,  read_only=True)
    # 读取文件的所有表名
    table_name = wb.sheetnames
    # 根据表名获取表,
    sheet_name = table_name[0]
    _list = []
    ws = wb[sheet_name]
    key_value = dict_key(ws)
    # 遍历表格的多行数据， ws.rows: 获取表格多少行
    count = 0
    for row in ws.rows:
        count += 1
        if count == 1:
            continue
         value_list = [data.value for data in row]
        _dict = dict(zip(key_value, value_list))
        _list.append(_dict)
    return _list

表格数据增加一列数据

 name = '案例.xlsx'
    wb = load_workbook(name)
    # 读取文件的所有表名
    table_name = wb.sheetnames
    # 根据表名获取表,
    sheet_name = table_name[0]
    sheet = wb[sheet_name]
    sheet.cell(row=1, column=19, value='销售额')
    for row_index in range(2, sheet.max_row+1):
        sheet.cell(row=row_index, column=19, value=row_index)
    wb.save(name)

绘制折线图

    from openpyxl import Workbook, load_workbook
    import os
    from openpyxl.chart import BarChart, Reference, AreaChart
    wb = Workbook()
    ws = wb.active
    rows = [
        ['Number', 'Batch 1', 'Batch 2'],
        [2, 40, 30],
        [3, 40, 25],
        [4, 50, 30],
        [5, 30, 10],
        [6, 25, 5],
        [7, 50, 10],
    ]

    for row in rows:
        ws.append(row)

    chart = AreaChart()
    chart.title = "Area Chart"
    chart.style = 13
    chart.x_axis.title = 'Test'
    chart.y_axis.title = 'Percentage'
    cats = Reference(ws, min_col=1, min_row=1, max_row=7)
    data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=7)
    chart.add_data(data, titles_from_data=True)
    chart.set_categories(cats)
    ws.add_chart(chart, "A10")
    wb.save("area.xlsx")

官方文档请看： http://yumos.gitee.io/openpyxl3.0/index.html#document-charts/introduction