Python（十六）- openpyxl和xlsxwriter对Excel的操作

前几天，项目中有个小需求：提供Excel的上传下载功能，使用模块：openpyxl 和 xlsxwriter，这里简单记录一下。

1.简介

Python中操作Excel的库非常多，为开发者提供了多种选择，如：xlrd、 xlwt、xlutils、xlwings、pandas、 win32com、openpyxl、xlsxwriter等等。
其中：

• 前三个一般混合使用，对Excel读写操作，适合旧版Excel，仅支持 xls 文件；
• win32com 库功能丰富，性能强大，适用于Windows；
• xlwings稍次于前者，但同样功能丰富；
• pandas适合处理大量数据；
• xlsxwriter 适合大量数据的写操作，支持图片/表格/图表/筛选/格式/公式等；
• openpyxl 读写均可，简单易用，功能广泛，可插入图表等，类似前者。

以下主要描述一下后两种（openpyxl、xlsxwriter）的简单使用

2.Excel库的使用

2.1.目标

2.2.openpyxl的使用

2.2.1.安装

pip install openpyxl

2.2.2.写入Excel

import os
from openpyxl import Workbook
from openpyxl.styles import Alignment, Font, colors, PatternFill
from openpyxl.utils import get_column_letter

FILE_PATH = os.path.join(os.path.dirname(__file__), 'files/')


def write_test():
    wb = Workbook()
    filename = FILE_PATH + '/openpyxl_test.xlsx'
    # 活动sheet
    ws1 = wb.active
    ws1.title = "Test-1"

    # 列表追加
    for row in range(1, 10):
        ws1.append(range(9))
    # 创建sheet
    ws2 = wb.create_sheet(title="Test-2")
    # 合并单元格
    ws2.merge_cells('F5:I5')
    # 拆分
    # ws2.unmerge_cells('F5:I5')
    # 单元赋值
    ws2['F5'] = 'hello world'
    # 居中
    ws2['F5'].alignment = Alignment(horizontal='center', vertical='center')
    # sheet标签颜色
    ws2.sheet_properties.tabColor = '1072BA'
    # 字体样式
    bold_itatic_12_font = Font(name='仿宋', size=12, italic=True, color=BLUE, bold=True)
    ws2['F5'].font = bold_itatic_12_font
    # 背景颜色
    bg_color = PatternFill('solid', fgColor='1874CD')
    ws2['F5'].fill = bg_color
    # 行高列宽
    ws2.row_dimensions[5].height = 40  # 第 5 行
    ws2.column_dimensions['F'].width = 30  # F 列

    ws3 = wb.create_sheet(title="Test-3")
    for row in range(10, 20):
        for col in range(10, 20):
            ws3.cell(column=col, row=row, value="{}".format(get_column_letter(col)))
    print(ws3['S10'].value)
    # 保存
    wb.save(filename)

2.2.3.读取Excel

from openpyxl import load_workbook


def read_test(filename):
    wb = load_workbook(filename)

    print('取得所有工作表的表名 :')
    print(wb.sheetnames, '\n')

    print('取得某张工作表 :')
    # sheet = wb['Sheet1']
    # sheet = wb.worksheets[0]
    sheet = wb[wb.sheetnames[0]]
    print(type(sheet))
    print('表名: ' + sheet.title, '\n')

    print('取得活动工作表 :')
    active_sheet = wb.active
    print('表名: ' + active_sheet.title, '\n')

    print('获取工作表的大小:')
    print('总行数: ' + str(active_sheet.max_row))
    print('总列数: ' + str(active_sheet.max_column))

    print('\n获取单元格数据:')
    for row in range(sheet.max_row):
        for col in range(sheet.max_column):
            print(f"第 {row + 1} 行 {col + 1} 列：", sheet.cell(row=row + 1, column=col + 1).value)

    print('\n获取行数据:')
    for i, cell_object in enumerate(list(sheet.rows)):
        cell_lst = [cell.value for cell in cell_object]
        print(f'第 {i + 1} 行：', cell_lst)

2.2.4.案例demo

• 数据源格式

# contents数据
contents=[
	{
      "uid": "1281948912",
      "group_name": "测试群-5",
      "domain": "ddos5.www.cn",
      "user_area": [
        {
          "num": 1024,
          "region": "中国",
          "percent": 33.33
        },
        {
          "num": 1022,
          "region": "中国香港",
          "percent": 33.33
        },
        {
          "num": 1021,
          "region": "新加坡", 
          "percent": 33.33
        }
      ],
      "gf_area": [
        {
          "num": 5680,
          "region": "中国香港",
          "percent": 97.8
        },
        {
          "num": 60,
          "region": "新加坡",
          "percent": 0.8
        },
        {
          "num": 55,
          "region": "美西",
          "percent": 0.8
        }
      ],
      "sip_area": {
        "waf_ip":["aliyunwaf.com.cn"],
        "sip":["13.75.120.253","18.163.46.57"],
        "isp_region":[
          {
            "country": "中国香港",
            "isp": "microsoft.com"
          },
          {
            "country": "中国香港",
            "isp": "amazon.com"
          }
        ]
      }
    },
]

• 写入Excel

import os
import time

from openpyxl import Workbook, load_workbook
from openpyxl.styles import Alignment, Font, colors, PatternFill

FILE_PATH = os.path.join(os.path.dirname(__file__), 'files/')
# 颜色
BLACK = colors.COLOR_INDEX[0]
WHITE = colors.COLOR_INDEX[1]
RED = colors.COLOR_INDEX[2]
DARKRED = colors.COLOR_INDEX[8]
BLUE = colors.COLOR_INDEX[4]
DARKBLUE = colors.COLOR_INDEX[12]
GREEN = colors.COLOR_INDEX[3]
DARKGREEN = colors.COLOR_INDEX[9]
YELLOW = colors.COLOR_INDEX[5]
DARKYELLOW = colors.COLOR_INDEX[19]


def export_gf_excel_test(filename=None, sheetName=None, contents=None):
    filename = filename if filename else 'openpyxl_Test.xlsx'
    sheetName = sheetName if sheetName else '测试'
    contents = contents if contents else []
    # 新建工作簿
    wb = Workbook()
    ws = wb.worksheets[0]
    # 设置sheet名称
    ws.title = sheetName
    # sheet标签颜色
    ws.sheet_properties.tabColor = '1072BA'
    # 居中
    pos_center = Alignment(horizontal='center', vertical='center')
    # 字体样式
    bold_12_font = Font(name='仿宋', size=12, italic=False,
                        color=BLACK, bold=True)
    # 背景颜色
    bg_color = PatternFill('solid', fgColor='4DCFF6')

    # 设置标题
    # 合并
    merge_lst = [
        'A1:A3', 'B1:B3', 'C1:C3', 'D1:R1', 'S1:AA1', 'AB1:AE1',
        'D2:F2', 'G2:I2', 'J2:L2', 'M2:O2', 'P2:R2', 'S2:U2', 'V2:X2',
        'Y2:AA2', 'AB2:AB3', 'AC2:AC3', 'AD2:AD3', 'AE2:AE3'
    ]
    [ws.merge_cells(c) for c in merge_lst]
    # 填充字段
    title_dic = {
        'A1': 'UID', 'B1': '钉钉群', 'C1': '域名',
        'D1': '用户区域', 'S1': '高防区域', 'AB1': '源站区域',
        'D2': 'TOP1', 'G2': 'TOP2', 'J2': 'TOP3', 'M2': 'TOP4', 'P2': 'TOP5',
        'S2': 'TOP1', 'V2': 'TOP2', 'Y2': 'TOP3',
        'AB2': 'WAF IP', 'AC2': '源站IP', 'AD2': '源站IP区域', 'AE2': '运营商'
    }
    line3_v = ['物理区域', '请求量', '占比'] * 8
    line3_k = [chr(i) + '3' for i in range(68, 91)] + ['AA3']
    title_dic.update(dict(zip(line3_k, line3_v)))
    for k, v in title_dic.items():
        ws[k].value = v
        ws[k].font = bold_12_font
        ws[k].alignment = pos_center
        ws[k].fill = bg_color

    # 列宽
    width_dic = {
        'A': 30, 'B': 30, 'C': 30,
        'AB': 16, 'AC': 16, 'AD': 16, 'AE': 16
    }
    for k, v in width_dic.items():
        ws.column_dimensions[k].width = v

    # 内容
    for i, dic in enumerate(contents):
        user_gf_mod = {'region': '', 'num': '', 'percent': ''}
        user_area = dic['user_area']
        gf_area = dic['gf_area']
        sip_area = dic['sip_area']
        # UID+域名
        data = [dic['uid'], dic['group_name'], dic['domain']]
        # 用户区域
        if not user_area:
            user_area = [user_gf_mod] * 5
        else:
            user_area = list(
                map(lambda item: {
                    'region': item['region'], 'num': item['num'], 'percent': item['percent']}, user_area)
            )
            [user_area.append(user_gf_mod) for _ in range(5 - len(user_area))]
        [data.extend(user_area[u].values()) for u in range(len(user_area))]
        # 高防区域
        if not gf_area:
            gf_area = [user_gf_mod] * 3
        else:
            gf_area = list(
                map(lambda item: {
                    'region': item['region'], 'num': item['num'], 'percent': item['percent']}, gf_area)
            )
            [gf_area.append(user_gf_mod) for _ in range(3 - len(gf_area))]
        [data.extend(gf_area[g].values()) for g in range(len(gf_area))]
        # 源站区域
        waf_ip = sip_area['waf_ip']
        sip = sip_area['sip']
        isp_region = sip_area['isp_region']
        data.append(','.join(waf_ip)) if waf_ip else data.append('')
        data.append(','.join(sip)) if sip else data.append('')
        if not isp_region:
            data.extend([''] * 2)
        else:
            try:
                country = ','.join(map(lambda item: item['country'], isp_region))
                isp = ','.join(map(lambda item: item['isp'] if item['isp'] else '暂未查到', isp_region))
                data.append(country)
                data.append(isp)
            except Exception as e:
                print(e)
                print(isp_region)

        # 写入Excel
        ws.append(data)

    # 保存文件
    wb.save(filename=filename)


if __name__ == "__main__":
	curTime = ''.join(map(lambda i: str(i) if len(str(i)) >= 2 else '%02d' % i, [i for i in time.localtime()[:-4]]))
    filename = os.path.join(FILE_PATH, 'openpyxl_Test_{}.xlsx'.format(curTime))
    export_gf_excel_test(filename, contents=contents)

2.3.xlsxwriter的使用

2.3.1.安装

pip install XlsxWriter

2.3.2.写入Excel

import os
import time
import json

import xlsxwriter

FILE_PATH = os.path.join(os.path.dirname(__file__), 'files/')


def export_gf_excel_test(filename=None, sheetName=None, contents=None):
    filename = filename if filename else 'xlsxwriter_Test.xlsx'
    sheetName = sheetName if sheetName else '测试'
    contents = contents if contents else []
    # 新建
    wb = xlsxwriter.Workbook(filename)
    ws = wb.add_worksheet(name=sheetName)
    # 设置风格
    style1 = wb.add_format({
        "bold": True,
        'font_name': '仿宋',
        'font_size': 12,
        # 'font_color': '#217346',
        'bg_color': '#4DCFF6',
        "align": 'center',
        "valign": 'vcenter',
        'text_wrap': 1
    })
    style2 = wb.add_format({
        # "bold": True,
        # 'font_name': '仿宋',
        'font_size': 11,
        'font_color': '#217346',
        'bg_color': '#E6EDEC',
        "align": 'center',
        "valign": 'vcenter',
        # 'text_wrap': 1
    })

    # 标题
    ws.set_column('A1:AE1', None, style1)
    # 合并单元格: first_row, first_col, last_row, last_col
    # 第 1 行
    ws.merge_range(0, 0, 2, 0, 'UID')
    ws.merge_range(0, 1, 2, 1, '钉钉群')
    ws.merge_range(0, 2, 2, 2, '域名')
    ws.merge_range(0, 3, 0, 17, '用户区域')
    ws.merge_range(0, 18, 0, 26, '高防区域')
    ws.merge_range(0, 27, 0, 30, '源站区域')
    # 第 2 行
    user_tl2 = ['TOP' + str(i) for i in range(1, 6)]
    gf_tl2 = user_tl2[:3]
    [ws.merge_range(1, 3 * (i + 1), 1, 3 * (i + 2) - 1, name) for i, name in enumerate(user_tl2 + gf_tl2)]
    # 第 3 行
    user_gf_tl3 = ['物理区域', '请求量', '占比'] * 8
    sip_tl3 = ['WAF IP', '源站IP', '源站IP区域', '运营商']
    [ws.write(2, 3 + i, name) for i, name in enumerate(user_gf_tl3)]
    [ws.merge_range(1, 27 + i, 2, 27 + i, name) for i, name in enumerate(sip_tl3)]

    # ws.write(11, 2, '=SUM(1:10)')  # 增加公式
    # ws.set_default_row(35)  # 设置默认行高
    # 设置列宽
    ws.set_column(0, 2, 30)
    ws.set_column(3, 26, 10)
    ws.set_column(27, 30, 16)

    # 内容
    for i, dic in enumerate(contents):
        user_gf_mod = {'region': '', 'num': '', 'percent': ''}
        user_area = dic['user_area']
        gf_area = dic['gf_area']
        sip_area = dic['sip_area']
        # UID+域名
        data = [dic['uid'], dic['group_name'], dic['domain']]
        # 用户区域
        if not user_area:
            user_area = [user_gf_mod] * 5
        else:
            user_area = list(
                map(lambda item: {
                    'region': item['region'], 'num': item['num'], 'percent': item['percent']}, user_area)
            )
            [user_area.append(user_gf_mod) for _ in range(5 - len(user_area))]
        [data.extend(user_area[u].values()) for u in range(len(user_area))]
        # 高防区域
        if not gf_area:
            gf_area = [user_gf_mod] * 3
        else:
            gf_area = list(
                map(lambda item: {
                    'region': item['region'], 'num': item['num'], 'percent': item['percent']}, gf_area)
            )
            [gf_area.append(user_gf_mod) for _ in range(3 - len(gf_area))]
        [data.extend(gf_area[g].values()) for g in range(len(gf_area))]
        # 源站区域
        waf_ip = sip_area['waf_ip']
        sip = sip_area['sip']
        isp_region = sip_area['isp_region']
        data.append(','.join(waf_ip)) if waf_ip else data.append('')
        data.append(','.join(sip)) if sip else data.append('')
        if not isp_region:
            data.extend([''] * 2)
        else:
            try:
                country = ','.join(map(lambda item: item['country'], isp_region))
                isp = ','.join(map(lambda item: item['isp'] if item['isp'] else '暂未查到', isp_region))
                data.append(country)
                data.append(isp)
            except Exception as e:
                print(e)
                print(isp_region)

        # 写入Excel
        ws.write_row('A' + str(i + 4), data, style2)

    # 保存关闭文件
    wb.close()


if __name__ == '__main__':
	curTime = ''.join(map(lambda i: str(i) if len(str(i)) >= 2 else '%02d' % i, [i for i in time.localtime()[:-4]]))
    filename = os.path.join(FILE_PATH, 'xlsxwriter_Test_{}.xlsx'.format(curTime))
    export_gf_excel_test(filename, contents=contents)

---

以上是两个库操作Excel的简单实现。对于一些复杂需求的处理，可以查看相关文档。