折腾:
【已解决】用openpyxl去新建excel文件并保存数据和设置单元格样式
期间,想要实现,针对:
中的表头的绿色部分,批量设置单元格的样式:
背景色都是绿色和单元格(包括合并后的)都是左右居中对齐,上下垂直居中对齐。
而之前是针对单个的cell去设置的:
aligmentCenter = Alignment(horizontal=’center’, vertical=’center’)
commonBackgroundColorHex = "AACF91"
commonFill = PatternFill(start_color=commonBackgroundColorHex, end_color=commonBackgroundColorHex, fill_type="solid")
nameCell = ws["A1"]
nameCell.value = "名称"
nameCell.alignment = aligmentCenter
# nameCell.fill = commonFill
ws.merge_cells(‘A1:A2’)
contentAbstractCell = ws["B1"]
contentAbstractCell.value = "内容简介"
contentAbstractCell.alignment = aligmentCenter
# contentAbstractCell.fill = commonFill
ws.merge_cells(‘B1:B2’)
resTypeCell = ws["C1"]
resTypeCell.value = "资源类型"
resTypeCell.alignment = aligmentCenter
# resTypeCell.fill = commonFill
ws.merge_cells(‘C1:C2’)
isSeriesCell = ws["D1"]
isSeriesCell.value = "是否是系列"
isSeriesCell.alignment = aligmentCenter
# isSeriesCell.fill = commonFill
ws.merge_cells(‘D1:D2’)
seriesCell = ws["E1"]
seriesCell.value = "系列"
seriesCell.alignment = aligmentCenter
# seriesCell.fill = commonFill
ws.merge_cells(‘E1:F1’)
seriesNumCell = ws["E2"]
seriesNumCell.value = "序号"
seriesNumCell.alignment = aligmentCenter
# seriesNumCell.fill = commonFill
seriesNameCell = ws["F2"]
seriesNameCell.value = "名称"
seriesNameCell.alignment = aligmentCenter
# seriesNameCell.fill = commonFill
想到提高效率,应该去批量设置。
起初想到的是用ragne去实现
参考:
结果发现:
commonCellRange = ws["A1:L2"]
logging.info("commonCellRange=%s", commonCellRange)
commonCellRange.alignment = aligmentCenter
commonCellRange.fill = commonFill
出错:
“ commonCellRange.alignment = aligmentCenter
AttributeError: ‘tuple’ object has no attribute ‘alignment’”
所以放弃。
【总结】
最后是每个单元格循环去设置:
aligmentCenter = Alignment(horizontal=’center’, vertical=’center’)
commonBackgroundColorHex = "AACF91"
commonFill = PatternFill(start_color=commonBackgroundColorHex, end_color=commonBackgroundColorHex, fill_type="solid")
for eachCommonRow in ws.iter_rows("A1:L2"):
logging.info("eachCommonRow=%s", eachCommonRow)
for eachCellInRow in eachCommonRow:
logging.info("eachCellInRow=%s", eachCellInRow)
eachCellInRow.alignment = aligmentCenter
eachCellInRow.fill = commonFill
才实现了我想要的效果:
注意到其中很多cell,是合并后的
-》此处的效果也是我希望的:即使是合并后的cell,也要是水平和垂直都是居中对齐的。