python操作excel（xlsx）

往 excel 写数据（xlsx）

#!D:/Tools/python-3.6.5-embed-amd64
# _*_ coding:utf-8 _*_
import os

import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment
from xlutils.copy import copy

# 创建excel文件
def write_data_xlsx(excel_save_path, sheet_name, data_title_list, data_list):
    if os.path.exists(excel_save_path):
        wbk = openpyxl.load_workbook(excel_save_path)
    else:
        wbk = openpyxl.Workbook()
    for i in range(len(data_list)):
        # 数据太多时，一个sheet会显示不了，这里设置超过100W行在添加一个sheet
        if i % 1000000 == 0:
            sheet_index = i // 1000000
            new_sheet_name = sheet_name
            if sheet_index >= 1:
                new_sheet_name = sheet_name + str(sheet_index)
            if check_sheet_xlsx(wbk, "Sheet"):
                sheet = wbk.get_sheet_by_name("Sheet")
                sheet.title = new_sheet_name
            else:
                sheet = wbk.create_sheet(new_sheet_name)
            # 冻结第一行
            sheet.freeze_panes = 'A2'
            for k in range(len(data_title_list)):
                date_title = data_title_list[k]
                sheet.cell(row=1, column=k + 1, value=date_title)
                # 设置第一行的样式
                set_title_style_xlsx(sheet.cell(row=1, column=k + 1))
                sheet_row = 1
        for j in range(len(data_list[i])):
            sheet.cell(row=sheet_row + 1, column=j + 1, value=data_list[i][j])
        sheet_row += 1
    wbk.save(excel_save_path)
    print("excel保存成功")


# 判读 sheet_name 是否已经存在
def check_sheet_xlsx(wbk, sheet_name):
    # wbk = openpyxl.Workbook()
    sheet_names = wbk.sheetnames
    for item in sheet_names:
        if item == sheet_name:
            return True
    return False;


# 设置title的默认样式
def set_title_style_xlsx(cell):
    font = Font(name='SimSun', bold=True)
    cell.font = font
    fill = PatternFill("solid", fgColor="00BFFF")
    cell.fill = fill
    align = Alignment(horizontal='center', vertical='center', wrap_text=True)
    cell.alignment = align


if __name__ == "__main__":
    data_title_list = ["姓名", "年龄", "职位"]
    data_list = []
    data_list.append(["小明", 22, "程序猿"])
    data_list.append(["小红", 22, "UI设计"])
    write_data_xlsx("D:/MyData/temp/B/temp.xlsx", "员工信息", data_title_list, data_list)

效果图

读取excel的数据（xlsx）

#!D:/Tools/python-3.6.5-embed-amd64
# _*_ coding:utf-8 _*_
import os

import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment
from xlutils.copy import copy

# 获取excel的内容
def get_data_xlsx(excel_path, sheet_name):
    workbook = openpyxl.load_workbook(excel_path)
    sheet = workbook.get_sheet_by_name(sheet_name)

    data_list = []
    max_row = sheet.max_row
    max_col = sheet.max_column
    for i in range(max_row):
        row_data = []
        for j in range(max_col):
            row_data.append(sheet.cell(row=i + 1, column=j + 1).value)
        data_list.append(row_data)
    return data_list

if __name__ == "__main__":
    datas = get_data_xlsx("D:/MyData/temp/B/temp.xlsx", "员工信息")
    for data in datas:
        print(data)

打印效果
[‘姓名’, ‘年龄’, ‘职位’]
[‘小明’, 22, ‘程序猿’]
[‘小红’, 22, ‘UI设计’]