聊聊python 办公自动化之 Excel(下)

作者:星安果

来源:AirPython

前面谈到python 处理 Excel 文件最常见的两种方式,即:xlrd/xlwt、openpyxl。其中,xlrd/xlwt 这一组合,xlrd 可以负责读取数据,而 xlwt 则负责写入数据,缺点是不支持 xlsx。openpyxl 同时支持对 Excel 文档的读取、写入操作,缺点是不支持 xls。本篇文章将继续聊聊python 操作 Excel 文档的其他几种方式。

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/f3b...')

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

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() 方法可以设置列宽,和 openpyxl 类似,有 2 种使用方式,分别是:字符串索引、列索引数字索引。

def set_column_width(sheet, index_start, index_end, width):

"""

设置列宽

:param sheet:

:param index_start: 开始位置,从1开始

:param index_end: 结束位置

:param width: 宽度

:return:

"""

方式二选一

self.current_sheet.set_column('A:C', width)

默认0代表第一列

sheet.set_column(index_start - 1, index_end - 1, width)

设置列宽度

设置第1列到第3列的宽度为:100

set_column_width(self.current_sheet, 1, 3, 100)

行高使用 set_row() 方法,传入行索引和高度即可。

def set_row_height(sheet, row_index, height):

"""

设置行高

:param sheet:

:param row_index: 行索引,从1开始

:param height:

:return:

"""

sheet.set_row(row_index - 1, height)

设置行高

set_row_height(self.current_sheet, 1, 50)

set_row_height(self.current_sheet, 2, 100)

写入数据完毕之后,将工作簿关闭,文件会自动保存到本地。

def teardown(self):

写入文件,并关闭文件

self.wb.close()

xlsxwriter 还支持插入图表,比如:条形图、柱状图、雷达图等,受限于篇幅,这部分内容就不展开说明了。

其他方式

还有一种比较常见的方式是:xlwings。xlwings 是一款开源免费的依赖库,同时支持 Excel 文件的读取、写入、修改。它功能非常强大,还可以和 Matplotlib、Numpy 和 Pandas 无缝连接,支持读写 Numpy、Pandas 数据类型;同时,xlwings 可以直接调用 Excel 文件中 VBA 程序。

需要注意的是,xlwings 依赖于 Microsoft Excel 软件,所以使用 WPS 的用户建议直接使用 openpyxl。

另外,还有一个操作 Excel 比较强大的方式,即:Pywin32。其中,Pywin32 相当于调用 Win 下的系统 API 来操作 Excel 文件。

优点是:可以处理复杂图表的数据表;

缺点也非常明显,包含:速度慢、占用 CPU 高,仅支持 Win 系统。

最后

综合发现,xlrd/xlwt、openpyxl、xlsxwriter 基本上可以满足大部分的日常 Excel 文档操作。

评论

暂无评论