最新消息:20190529 VPS服务器已从腾讯云香港换为Vultr新加坡,主题仍用朋友推荐的大前端D8

【已解决】用openpyxl去新建excel文件并保存数据和设置单元格样式

Excel crifan 709浏览 0评论

之前已经用openpyxl去打开已有excel文件,读取信息:

【已解决】python解析excel文件并读取其中的sheet和row和column的值

现在需要新建类似于这样的excel:

并且创建对应的第一行和第二行的表头,且部分单元格是合并的

然后再一点点把之前已有的excel中的内容:

保存进来。

目的是统一格式。

去写代码并调试。

openpyxl 新建excel

openpyxl new excel

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

Python操作Excel新版本xlsx文件 | 不懂真人

【openpyxl】openpyxl对Excel表格的创建与写操作例程 – CSDN博客

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中给单元格设置背景色

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

另外,再去优化一下:

加上cell的边框的边线

Working with styles — openpyxl 2.5.0 documentation

以及发现批量设置,可以用到:

Named Styles

去试试

最后用:

<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’微软雅黑’,表示是微软雅黑的字体:

转载请注明:在路上 » 【已解决】用openpyxl去新建excel文件并保存数据和设置单元格样式

发表我的评论
取消评论

表情

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

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
65 queries in 0.082 seconds, using 18.79MB memory