使用Python做一张满足基本样式要求的Excel表格

Excel电子表格在实际使用中，最常规的需求基本上包括定义标题、定义表头和写入具体记录行三部分内容。使用借助库（）就可以轻松实现了。参考如下小脚本，同时实现了合并列重复单元格和自动模糊适应列宽。

# !/usr/bin/python
# -*- coding: utf-8 -*-"""
create_author : 蛙鳜鸡鹳狸猿
create_time   : 2017-04-21
program       : *_* Write Excel *_*
"""import re
import openpyxlclass ExcelHandler:"""Handler of general demand to write Excel file."""def __init__(self, writer="writer.xlsx"):"""Set an Excel file.:param writer: string, default : an Excel file named "writer.xlsx" at CWDfull directory of the Excel file to write out."""self.wkbook = openpyxl.Workbook()self.writer = writerdef __repr__(self):printer = 'o(>﹏<)o ......Excel old driver write {0} out...... o(^o^)o'.format(self.writer)return printerdef createsheet(self, sheetname):"""Create an Excel work sheet.:param sheetname: stringExcel work sheet name.:return: sheet object."""try:del self.wkbook["Sheet"]except:passwsheet = self.wkbook.create_sheet(sheetname)return wsheetdef definetitle(self, wsheet, title, cellspace, heighter=None,name='Calibri', size=12, bold=True, italic=False, underline='none', color="0070C0",fill_type=None, fgColor="FFFFFF",horizontal='center', vertical='center'):"""Define an Excel sheet title.:param wsheet: sheet objectFunction createsheet() returned.:param title: int, string and other formats Excel supportExcel sheet title.:param cellspace: stringExcel cell intervals the "title" to write seporated by ':'(eg : "A1 : C11").:param heighter: int, default : none(simply write)Excel row height.:param name: see " http://openpyxl.readthedocs.io/en/default/styles.html ".:param size: see " http://openpyxl.readthedocs.io/en/default/styles.html ".:param bold: see " http://openpyxl.readthedocs.io/en/default/styles.html ".:param italic: see " http://openpyxl.readthedocs.io/en/default/styles.html ".:param underline: see " http://openpyxl.readthedocs.io/en/default/styles.html ".:param color: see " http://openpyxl.readthedocs.io/en/default/styles.html ".:param fill_type: see " http://openpyxl.readthedocs.io/en/default/styles.html ".:param fgColor: see " http://openpyxl.readthedocs.io/en/default/styles.html ".:param horizontal: see " http://openpyxl.readthedocs.io/en/default/styles.html ".:param vertical: see " http://openpyxl.readthedocs.io/en/default/styles.html ".:return: none."""font = openpyxl.styles.Font(name=name, size=size, bold=bold, italic=italic, underline=underline, color=color)fill = openpyxl.styles.PatternFill(fill_type=fill_type, fgColor=fgColor)alig = openpyxl.styles.Alignment(horizontal=horizontal, vertical=vertical)cell = cellspace.split(':')[0]wsheet.merge_cells(cellspace)wsheet[cell] = titlewsheet[cell].font = fontwsheet[cell].alignment = aligif heighter:row = re.findall(r"\d+", cellspace)wsheet.row_dimensions[int(row[0])].height = heighterwsheet.row_dimensions[int(row[1])].height = heighterdef setheader(self, wsheet, header, beginer, heighter=None, wider=None,name='Calibri', size=12, bold=False, italic=False, underline='none', color="FF000000",fill_type="solid", fgColor="FFFF00",horizontal='center', vertical='center'):"""Set an Excel sheet title(column names).:param wsheet: sheet objectFunction createsheet() returned.:param header: listExcel sheet title(column names).:param beginer: listPosition([row number, column number]) of the sheet title(column names) starts to write.:param heighter: int, default : none(simply write)Excel row height.:param wider: list, default : none(auto fit by cell data length)Excel column width.:param name: see " http://openpyxl.readthedocs.io/en/default/styles.html ".:param size: see " http://openpyxl.readthedocs.io/en/default/styles.html ".:param bold: see " http://openpyxl.readthedocs.io/en/default/styles.html ".:param italic: see " http://openpyxl.readthedocs.io/en/default/styles.html ".:param underline: see " http://openpyxl.readthedocs.io/en/default/styles.html ".:param color: see " http://openpyxl.readthedocs.io/en/default/styles.html ".:param fill_type: see " http://openpyxl.readthedocs.io/en/default/styles.html ".:param fgColor: see " http://openpyxl.readthedocs.io/en/default/styles.html ".:param horizontal: see " http://openpyxl.readthedocs.io/en/default/styles.html ".:param vertical: see " http://openpyxl.readthedocs.io/en/default/styles.html ".:return: none."""font = openpyxl.styles.Font(name=name, size=size, bold=bold, italic=italic, underline=underline, color=color)fill = openpyxl.styles.PatternFill(fill_type=fill_type, fgColor=fgColor)alig = openpyxl.styles.Alignment(horizontal=horizontal, vertical=vertical)rownber = beginer[0]colnber = beginer[1]counter = len(header)stor = list(zip(range(colnber, counter + 2), header))for i, j in stor:wsheet.cell(row=rownber, column=i).value = jwsheet.cell(row=rownber, column=i).font = fontwsheet.cell(row=rownber, column=i).fill = fillwsheet.cell(row=rownber, column=i).alignment = aligif heighter:wsheet.row_dimensions[rownber].height = heighterif wider:colitem = list(zip(range(len(header)), wider))for col, wid in colitem:colth = openpyxl.utils.get_column_letter(col + colnber)wsheet.column_dimensions[colth].width = widelif not wider:for i in range(len(header)):col = openpyxl.utils.get_column_letter(i + colnber)wid = len(str(header[i])) + 5wsheet.column_dimensions[col].width = widdef insertrecord(self, wsheet, record):"""Insert record into an Excel sheet row by row.:param wsheet: sheet objectFunction createsheet() returned.:param record: listExcel data records, [[...], [...], ...].:return: none."""for row in record:wsheet.append(row)def mergecolcellwrite(self, wsheet, record, beginer, mercols=None, isautoFitwid=False):"""Optimized function of writing Excel record: merge same data cells by column, auto fit column width.:param wsheet: sheet objectFunction createsheet() returned.:param record: listExcel data records, [[...], [...], ...].:param beginer: listPosition([row number, column number]) of the sheet "record" starts to write.:param mercols: list, default : none(simply write)Columns to merge same data cells when write.:param isautoFitwid: bool, default : False(simply write)Whether to auto fit column width by cell data length.:return: none."""rownber = beginer[0]colnber = beginer[1]if isautoFitwid:for i in range(len(record[0])):col = openpyxl.utils.get_column_letter(colnber + i)sto = []for j in record:sto.append(len(str(j[i])))wid = max(sto) + 3wsheet.column_dimensions[col].width = widfor colindex in range(len(record[0])):store = []for rowindex in range(len(record)):value = record[rowindex][colindex]if not mercols:wsheet.cell(row=rownber, column=colnber, value=value)elif mercols:for mergcol in mercols:if mergcol != colnber:wsheet.cell(row=rownber, column=colnber, value=value)elif mergcol == colnber:try:if rowindex == 0:store.append([rownber, value])elif rowindex == len(record) - 1:if store[-1][1] != value:precell = openpyxl.utils.get_column_letter(colnber) + str(store[0][0])sufcell = openpyxl.utils.get_column_letter(colnber) + str(store[-1][0])wsheet.merge_cells("{0}:{1}".format(precell, sufcell))wsheet.cell(row=store[0][0], column=colnber, value=store[0][1])wsheet.cell(row=rownber, column=colnber, value=value)elif store[-1][1] == value:store.append([rownber, value])precell = openpyxl.utils.get_column_letter(colnber) + str(store[0][0])sufcell = openpyxl.utils.get_column_letter(colnber) + str(store[-1][0])wsheet.merge_cells("{0}:{1}".format(precell, sufcell))wsheet.cell(row=store[0][0], column=colnber, value=value)elif store[-1][1] == value:store.append([rownber, value])elif store[-1][1] != value:try:precell = openpyxl.utils.get_column_letter(colnber) + str(store[0][0])sufcell = openpyxl.utils.get_column_letter(colnber) + str(store[-1][0])wsheet.merge_cells("{0}:{1}".format(precell, sufcell))wsheet.cell(row=store[0][0], column=colnber, value=store[0][1])except:passfinally:store = []store.append([rownber, value])except:passrownber += 1colnber += 1rownber = beginer[0]def savexcel(self):"""Save out Excel file.:return: none."""self.wkbook.save(self.writer)# self test
if __name__ == "__main__":MyEH = ExcelHandler("/home/student/wb.xlsx")title = "Hello World\nHello Handler"header = ['name', 'rank', 'logo']record = [["Linux", 0, "penguin"],["MySQL", 1, "dolphin"],["MySQL", 1, "hawk"],["Python", 2, "python"],["Python", 2, "mouse"],["Python", 2, "rabbit"]]wsheet_l = MyEH.createsheet("Hello World")MyEH.definetitle(wsheet_l, title, "A32:C33", heighter=23)MyEH.setheader(wsheet_l, header, [36, 1], heighter=23, wider=[11, 23, 11])MyEH.mergecolcellwrite(wsheet_l, record, [37, 1], [1, 2])wsheet_r = MyEH.createsheet("Hello Handler")MyEH.definetitle(wsheet_r, title, "A32:C33", heighter=23)MyEH.setheader(wsheet_r, header, [36, 1], heighter=23, wider=[11, 23, 11])MyEH.insertrecord(wsheet_r, record)MyEH.savexcel()

以下是测试输出（后注：这个MySQL的应该是啊，大象海豚傻傻分不清楚，233……）。