最新消息:20210816 当前crifan.com域名已被污染,为防止失联,请关注(页面右下角的)公众号

【已解决】openpyxl中给一个范围内的单元格批量设置样式:居中对齐和背景色

Python crifan 14810浏览 0评论

折腾:

【已解决】用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去实现

参考:

OpenPyXL的使用教程(一) – 简书

openpyxl – 操作Excel文件 – CSDN博客

结果发现:

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,也要是水平和垂直都是居中对齐的。

转载请注明:在路上 » 【已解决】openpyxl中给一个范围内的单元格批量设置样式:居中对齐和背景色

发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
82 queries in 0.165 seconds, using 22.11MB memory