之前已经用openpyxl去打开已有excel文件，读取信息：
【已解决】python解析excel文件并读取其中的sheet和row和column的值
现在需要新建类似于这样的excel：
并且创建对应的第一行和第二行的表头，且部分单元格是合并的
然后再一点点把之前已有的excel中的内容：
保存进来。
目的是统一格式。
去写代码并调试。
openpyxl 新建excel
openpyxl new excel
【openpyxl】openpyxl对Excel表格的创建与写操作例程 – CSDN博客
openpyxl – A Python library to read/write Excel 2010 xlsx/xlsm files — openpyxl 2.5.3 documentation
Openpyxl tutorial – read, write Excel xlsx files in Python
Manipulating a workbook in memory — openpyxl 2.5.3 documentation
用：
<code>from openpyxl import Workbook, load_workbook
from openpyxl.styles import Alignment
# create new output unified format excel file
wbOut = Workbook()
logging.info("wbOut=%s", wbOut)
wsStorybook = wbOut.create_sheet(StorybookSheetTitle)
wsSong = wbOut.create_sheet(SongSheetTitle)
logging.info("wsStorybook=%s, wsSong=%s", wsStorybook, wsSong)
# init headers
def initOutputExcelHeaders(ws):
logging.info("ws=%s", ws)
</code>nameCell = ws[“A1”]
nameCell.value = “名称”
nameCell.alignment = Alignment(horizontal=’center’, vertical=’center’)
ws.merge_cells(‘A1:A2’)
initOutputExcelHeaders(wsStorybook)
initOutputExcelHeaders(wsSong)
wbOut.save(OutputUnifiedFormatExcelFilename)
可以生成所需要的excel了：
然后此处接着要：
给单元格设置背景色
【已解决】openpyxl中给一个范围内的单元格批量设置样式：居中对齐和背景色
另外，再去优化一下：
加上cell的边框的边线
Working with styles — openpyxl 2.5.0 documentation
以及发现批量设置，可以用到：
去试试
最后用：
<code>from openpyxl import Workbook, load_workbook
from openpyxl.styles import Alignment
from openpyxl.styles import PatternFill, colors, Color
from openpyxl.styles import NamedStyle, Font, Border, Side
# create new output unified format excel file
wbOut = Workbook()
logging.info("wbOut=%s", wbOut)
# wsStorybook = wbOut.create_sheet(StorybookSheetTitle)
wsStorybook = wbOut.active
wsStorybook.title = StorybookSheetTitle
wsSong = wbOut.create_sheet(SongSheetTitle)
logging.info("wsStorybook=%s, wsSong=%s", wsStorybook, wsSong)
# common style
alignmentCenter = Alignment(horizontal='center', vertical='center')
fontYahei12 = Font(name=u'微软雅黑', bold=True, size=12)
thinSide = Side(style='thin', color=colors.BLACK)
allBlackThinBorder = Border(left=thinSide, top=thinSide, right=thinSide, bottom=thinSide)
# several type of headers style
NamedStyleCommonHeader = "CommonHeader"
NamedStyleStorybookHeader = "StorybookHeader"
NamedStyleSongHeader = "SongHeader"
NamedStyleFileHeader = "FileHeader"
commonBkgColorHex = "AACF91"
commonFill = PatternFill(start_color=commonBkgColorHex, end_color=commonBkgColorHex, fill_type="solid")
...
commonStyleHeader = NamedStyle(name=NamedStyleCommonHeader)
commonStyleHeader.font = fontYahei12
commonStyleHeader.border = allBlackThinBorder
commonStyleHeader.alignment = alignmentCenter
commonStyleHeader.fill = commonFill
wbOut.add_named_style(commonStyleHeader)
...
# init headers
def initOutputExcelHeaders(ws):
logging.info("ws=%s", ws)
nameCell = ws["A1"]
nameCell.value = "名称"
ws.merge_cells('A1:A2')
contentAbstractCell = ws["B1"]
contentAbstractCell.value = "内容简介"
ws.merge_cells('B1:B2')
resTypeCell = ws["C1"]
resTypeCell.value = "资源类型"
ws.merge_cells('C1:C2')
isSeriesCell = ws["D1"]
isSeriesCell.value = "是否是系列"
ws.merge_cells('D1:D2')
seriesCell = ws["E1"]
seriesCell.value = "系列"
ws.merge_cells('E1:F1')
seriesNumCell = ws["E2"]
seriesNumCell.value = "序号"
seriesNameCell = ws["F2"]
seriesNameCell.value = "名称"
for eachCommonRow in ws.iter_rows("A1:L2"):
logging.info("eachCommonRow=%s", eachCommonRow)
for eachCellInRow in eachCommonRow:
logging.info("eachCellInRow=%s", eachCellInRow)
eachCellInRow.style = NamedStyleCommonHeader
initOutputExcelHeaders(wsStorybook)
initOutputExcelHeaders(wsSong)
wbOut.save(OutputUnifiedFormatExcelFilename)
</code>可以实现效果：
注意：
<code>fontYahei12 = Font(name=u'微软雅黑', bold=True, size=12) </code>
中，如果name不是unicode，则会报错，直接导致excel打不开 -》 估计就是font字体找不到，所以报错。
所以记得(Python2)要用u’微软雅黑’，表示是微软雅黑的字体：
