最全总结 | 聊聊 Python 办公自动化之 Excel（下）

1. 前言

前面谈到 Python 处理 Excel 文件最常见的两种方式，即：xlrd/xlwt、openpyxl

​其中，

xlrd/xlwt 这一组合，xlrd 可以负责读取数据，而 xlwt 则负责写入数据，缺点是不支持 xlsx

openpyxl 同时支持对 Excel 文档的读取、写入操作，缺点是不支持 xls

本篇文章将继续聊聊 Python 操作 Excel 文档的其他几种方式

2. xlsxwriter

xlsxwriter 主要用于将数据、图表写入到 Excel 文件中，可以配置使用较小的内存快速写入数据

它的缺点是：无法读取、修改已有的 Excel 文件；如果需要读取修改 Excel 文件，只能搭配其他依赖库使用，比如：xlrd

首先安装 xlsxwriter 的依赖包

# 安装依赖包 pip3 install xlsxwriter

xlsxwriter 提供了 Workbook(filename) 方法，用于创建一个工作簿对象

使用工作簿对象的 add_worksheet(sheet_name) 函数，就可以在工作簿中创建 Sheet 了

def create_workbook_and_worksheet(filename, worksheet_names): """ 创建工作簿和Sheet :param filename: 文件名称 :param worksheet_names: sheet名称列表 :return: """ wb = xlsxwriter.Workbook(filename) sheets = [] # 新增sheet for worksheet_name in worksheet_names: sheets.append(wb.add_worksheet(worksheet_name)) return wb, sheets

接着，就可以往某个 Sheet 单元格中写入数据了

如果需要定制单元格的样式，比如：字体大小、字体、颜色、背景、是否加粗等，可以使用工作簿对象的 add_format() 方法创建一个样式

def create_format_styles(wb, format_stuyles): """ 创建一个样式，包含：字体大小、字体、颜色、背景、是否加粗等 :param wb: :param format_stuyles: :return: """ return wb.add_format(format_stuyles) # 单元格字体样式 self.title_style = {'bold': True, 'bg_color': '#B0C4DE', 'font_size': 10,'font_name': 'Microsoft yahei'} # 创建标题字体样式 title_font_style = create_format_styles(self.wb, self.title_style)

Sheet 对象的 write(...) 函数用于向单元格中写入数据，参数包含：行索引、列索引、值、字体样式等

需要注意的是，默认 xlsxwriter 的行索引、列索引都是从 0 开始，即： 0 代表第一行

写入数据的同时配置单元格样式的写法如下：

def write_to_cell(sheet, row_index, column_index, value, format_styles=None): """ 往单元格中写入数据 :param row_index: 行索引，1：第一行 :param column_index: 列索引，1：第一列 :param format_styles 字体样式 :return: """ if row_index < 1 or column_index < 1: print('参数输入不正确，写入失败！') else: # 注意：默认xlsxwriter的行索引、列索引从0开始 sheet.write(row_index - 1, column_index - 1, value, format_styles) # 往worksheet中写入数据 # 第一行 write_to_cell(self.current_sheet, 1, 1, "姓名", title_font_style) write_to_cell(self.current_sheet, 1, 2, "年龄", title_font_style) # 第二行 write_to_cell(self.current_sheet, 2, 1, 'xingag') write_to_cell(self.current_sheet, 2, 2, 23)

xlsxwriter 同样支持在单元格中插入图片，包含：本地图片和网络图片

使用的方法是：insert_image()

参数包含：单元格行索引（索引从 0 开始）、单元格列索引、图片文件、可选参数（图片位置、缩放、url 超链接、image_data 图片字节流等）

以插入一张网络图片为例

首先，定义一个图片展示可选参数，指定图片的缩放比、url 超链接

def create_image_options(x_offset=0, y_offset=0, x_scale=1, y_scale=1, url=None, tip=None, image_data=None, positioning=None): """ 插入图片的参数配置 包含：偏移量、缩放比、网络图片链接、超链接、悬停提示灯 :param x_offset: :param y_offset: :param x_scale: :param y_scale: :param url: :param tip: :param image_data: :param positioning: :return: """ image_options = { 'x_offset': x_offset, 'y_offset': y_offset, 'x_scale': x_scale, 'y_scale': y_scale, 'url': url, 'tip': tip, 'image_data': image_data, 'positioning': positioning, } return image_options image_options = create_image_options(x_scale=0.5, y_scale=0.5, url='https://www.jianshu.com/u/f3b476549169')

接着，将网络图片转为字节流

from io import BytesIO import ssl def get_image_data_from_network(url): """ 获取网络图片字节流 :param url: 图片地址 :return: """ ssl._create_default_https_context = ssl._create_unverified_context # 获取网络图片的字节流 image_data = BytesIO(urlopen(url).read()) return image_data

最后，将图片插入到单元格中

def insert_network_image(sheet, row_index, column_index, url, filepath, image_options=None): """ 插入网络图片 :param sheet: :param row_index: :param column_index: :param url: :param filepath: :param image_options: :return: """ if row_index < 1 or column_index < 1: return "参数输入有误，插入失败！" # 获取图片字节流 image_data = get_image_data_from_network(url) if image_options: image_options['image_data'] = image_data print(image_options) sheet.insert_image(row_index - 1, column_index - 1, filepath, image_options) insert_network_image(self.current_sheet, 1, 1, url, '1.png', image_options4)

使用 set_column() 方法可以设置列宽