项目链接：https://gitee.com/xyjtysk/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