在之前的文章中我曾经使用xlwt将python处理的数据写入xls格式的表格文件。但是xlwt对样式的操作支持比较一般,所以我最近开始使用openpyxl。相比于xlwt,openpyxl写入更简单,样式操作更方便,而且读取功能也很棒。总而言之,使用openpyxl生成一张工作表就像打造一把魔法利剑,需要打造剑坯、注入灵魂、进行附魔、熔铸完成。
打造剑坯——首先,我们创建一个工作簿文件,然后切换到它的默认表,然后给默认表起个名。
使用openpyxl与xlwt在一开始的时候是大同小异的,现在我们使用openpyxl生成一个workbook,并切换到它的默认激活sheet:
wb = openpyxl.Workbook()
ws = wb.active
ws.title = "数据导出表"
然后,给这把剑注入灵魂——为表格添加数据
我们可以使用ws.append()方法向"数据导出表"这个表格中加入数据了。:
first = []
first_str = "这是a1格的数据"
first.append(first_str)
ws.append(first)
这样,就向我们的工作表中插入了第一行数据。在A1格中插入了“这是a1格的数据”。
openpyxl中,插入一行数据就像给一个二维数组添加一个一维数组元素。注意,ws.append()方法必须插入一个数组。
然后,进行附魔——编辑表格的样式。
一个格子,或者说一个openpyxl.cell.cell对象,包含五种属性对象:
PatternFill——填充,包括填充的颜色、图案、渐变色等
Border——边框,包括各个边框的粗细、线型、颜色等
Alignment——对齐,包括对齐方式、是否自动换行等
Protection——写保护,保护的效果是全局的。
Font——字体,包括文字的字体、颜色、下划线、删除线等等。
此外,还包括数据格式属性:number_format。
border还包含属性对象side,意为各个边框的属性。
所有属性使用的都是带有透明度的十六进制颜色(例:#00FFCCDD)。Black、White等等命名的颜色即将在未来几个版本的openpyxl中移除,不建议使用。
要使用属性对象对单元格进行附魔,必须先进行引用:
from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font
接下来是选定操作哪些单元格:
我们可以用ws[1][0]这样简单粗暴的方式每个格子进行操作,也可以使用ws.column_dimensions[‘A’]、ws.row_dimensions[1]这样的方式直接操作一行或者一列。不论是取格子,还是取一行,行号都是从1开始的,而非0.
在openpyxl中,对默认的、未添加过数据的表格是可以视为不存在的,操作这些不存在的表格会不报错直接跳过。为这样的列添加字体、对其样式,会激活这个表格,如果使用ws.append()方法直接添加,可能会导致新增的一行。所以如果要添加字体样式,应当先添加文字。
此外,还可以使用Comment对象对表格进行批注:
from openpyxl.comments import Comment
comment = Comment("ABCD", //批注的内容
"EFGH", //批注的标题
width=200, //批注框的宽度
height=20 * lines // 批注框的高度)
ws[row_i][coloum].comment = comment
效果如图:
熔铸完成:保存文件
和xlwt一样,可以把文件保存成相应路径/名字的.xlsx文件,也可以把文件直接保存到二进制流中
附:实际开发中,用字母取列过于麻烦,这里提供一个字典,用于方便地数字转字母,免去大家的重复打字之苦
# 表格列位置 按照《字母歌》顺序排列
column_place = {0: "A", 1: "B", 2: "C", 3: "D", 4: "E", 5: "F", 6: "G",
7: "H", 8: "I", 9: "J", 10: "K", 11: "L", 12: "M", 13: "N",
14: "O", 15: "P", 16: "Q",
17: "R", 18: "S", 19: "T",
20: "U", 21: "V", 22: "W", 23: "X", 24: "Y", 25: "Z",
26: "AA", 27: "AB", 28: "AC", 29: "AD", 30: "AE", 31: "AF", 32: "AG",
33: "AH", 34: "AI", 35: "AJ", 36: "AK", 37: "AL", 38: "AM", 39: "AN",
40: "AO", 41: "AP", 42: "AQ",
43: "AR", 44: "AS", 45: "AT",
46: "AU", 47: "AV", 48: "AW", 49: "AX", 50: "AY", 51: "AZ",
52: "BA", 53: "BB", 54: "BC", 55: "BD", 56: "BE", 57: "BF", 58: "BG",
59: "BH", 60: "BI", 61: "BJ", 62: "BK", 63: "BL", 64: "BM", 65: "BN",
66: "BO", 67: "BP", 68: "BQ",
69: "BR", 70: "BS", 71: "BT",
72: "BU", 73: "BV", 74: "BW", 75: "BX", 76: "BY", 77: "BZ",
}