项目链接：https://gitee.com/duyang2903/quotationTools
通过Controller和Model的配合，我们已经获得了最后我们来讨论一下，如何生成Excel。这在MVC模式中就相当于View，也就是展示层。
这一层的代码放在framework/libs/view/XlsWriterClass.py中。
总体思想
同样，我们希望每个sheet的格式（颜色，宽度）什么的都不一样，所以可以把这些配置参数都单列出来。
我们它们放到tpl这个文件夹下。
准备工作
首先需要把tpl中的模板加载进来
我们可以先看一下tpl中模板，比如说下面的为价格明细清单的
header={'num_format':'#,##0','font_name':'Arial','font_size':10,'font_color':'#000000','bg_color':'#c0c0c0','bold':True,'bottom':0}
headerOff={'num_format':'#,##0','font_name':'Arial','font_size':10,'font_color':'#000000','bg_color':'#c0c0c0','bold':True,'bottom':0}
site={'num_format':'0','font_name':'Arial','font_size':10,'font_color':'#ffffff','bg_color':'#339966','bold':False,'bottom':0}
siteOff={'num_format':'0.00%','font_name':'Arial','font_size':10,'font_color':'#ffffff','bg_color':'#339966','bold':False,'bottom':0}
subtotal={'num_format':'#,##0','font_name':'Arial','font_size':10,'font_color':'#000000','bg_color':'#c0c0c0','bold':True,'bottom':1}
subtotalOff={'num_format':'0.00%','font_name':'Arial','font_size':10,'font_color':'#000000','bg_color':'#c0c0c0','bold':True,'bottom':1}
total={'num_format':'#,##0','font_name':'Arial','font_size':10,'font_color':'#000000','bg_color':'#c0c0c0','bold':True,'bottom':1}
totalOff={'num_format':'0.00%','font_name':'Arial','font_size':10,'font_color':'#000000','bg_color':'#c0c0c0','bold':True,'bottom':1}
general={'num_format':'#,##0','font_name':'Arial','font_size':10,'font_color':'#000000','bold':False,'bottom':0}
generalOff={'num_format':'0.00%','font_name':'Arial','font_size':10,'font_color':'#000000','bold':False,'bottom':0}
同样我们把行分为site,total,general,subtotal等多种类型，同时因为折扣列需要使用%，所以单独区分开了。而且很有规律，是site+off
可以使用如下的函数进行加载
# ***************加载format*****************
def addFormat(self):
# 默认在tpl文件中设置每个sheet的格式
tpl = __import__("tpl." + self.view)
sheet = getattr(tpl , self.view);
types = getattr(sheet , "types");
typeDict = {};
# # 对所列出来的颜色类型的名称一一的列举
for type in types:
typeDict [type] = self.workbook.add_format(getattr(sheet , type ));
return typeDict;
同样可以使用assign把要传递进去的变量搞进去
def assign(self, lists, outputKeys, sheetName, view):
self.lists = lists;
self.outputKeys = outputKeys;
self.sheetName = sheetName;
self.view = view;
# 从文件中读取format的类型，获得format数组
self.dFormat = {};
self.dFormat = self.addFormat();
# 将要输出的outputKeys与每一列的序号组成dict，方便后面调度
colOrdinal = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M',
'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'];
# 先组合成为dict
self.colIndexes = dict(zip(self.outputKeys, colOrdinal));
打印数组
然后是调用XlsxWriter进行打印。
首先需要获取要打印的sheet名称
# 如果sheetName对应的sheet还没建立则建立，若已建立则获取
if self.workbook.get_worksheet_by_name(sheetName) is None:
worksheet = self.workbook.add_worksheet(sheetName);
else:
worksheet = self.workbook.get_worksheet_by_name(sheetName);
然后就是把传进来的list遍历
按照预先设定的输出Excel列进行打印
之前不是说过折扣列格式不同吗？而且折扣列的格式一般为"xxxOff"这种，我们可以拼接在一起
# 定义全局变量
col = 0;
arr = self.lists;
for row in range(len(arr)):
# 依次取每个要输出的每一列对应的key值
for outkey in self.outputKeys:
formatKey = "";
# 若outkey等于discount,rate则单独设置格式
if outkey == "discount" or outkey == 'rate':
formatKey = arr[row]["colorTag"] + "Off";
else :
formatKey = arr[row]["colorTag"];
# 打印每个单元格
worksheet.write(row , col , arr[row][outkey] , self.dFormat[formatKey])
col += 1;
col = 0;
设置其他行宽，列宽
打印完成以后还可以设置行宽，列宽
# *************设置每列的宽度*************
def setColumn(self):
# 列序号
worksheet = self.workbook.get_worksheet_by_name(self.sheetName);
tpl = __import__("tpl." + self.view)
sheet = getattr(tpl , self.view);
colBreath = getattr(sheet , "colbreath");
for outputKey in self.outputKeys:
worksheet.set_column(self.colIndexes[outputKey] + ":" + self.colIndexes[outputKey], colBreath[outputKey]);
# *************设置哪些列进行隐藏*************
def setHidden(self, hideCols):
worksheet = self.workbook.get_worksheet_by_name(self.sheetName);
debug(self.sheetName)
debug(self.workbook)
debug(worksheet);
# 取出hideCols与outputKeys中重合的部分
hideColumns = [i for i in hideCols if i in self.outputKeys];
for hideCol in hideColumns:
worksheet.set_column(self.colIndexes[hideCol] + ":" + self.colIndexes [hideCol], None,None, {'hidden': 1});
# *************设置自动筛选*************
def setAutofilter(self):
worksheet = self.workbook.get_worksheet_by_name(self.sheetName);
begin = "A1";
# 末尾的列序号是outputKeys的最后一位
endColumn = self.colIndexes[self.outputKeys[-1]];
worksheet.autofilter("A1:" + endColumn + str(len(self.lists)));
# *************冻结首行*************
def freezeTopRow(self):
worksheet = self.workbook.get_worksheet_by_name(self.sheetName);
worksheet.freeze_panes(1, 0)
return
# *************打印URL*************
def writeURL(self):
worksheet = self.workbook.get_worksheet_by_name(self.sheetName);
arr = self.lists;
for i in range(1, len(arr) - 1):
cellIndex = self.colIndexes['description'] + str(i + 1);
worksheet.write_url(cellIndex, arr[i]['url'], self.dFormat['link'],arr[i]['description']);
如何调用
同样可以在Controller中进行调用
xlswriter = XlsWriter(outputPath);
# —————————————————————————打印价格明细—————————————————————————
xlswriter.assign(lists,list(outputParam.keys()),sheetName,"totalConfigformat");
# xlswriter.setHidden(hideCols);
xlswriter.display();
info("成功打印价格明细")
image.png
