項(xiàng)目鏈接：https://gitee.com/duyang2903/quotationTools

通過(guò)Controller和Model的配合，我們已經(jīng)獲得了最后我們來(lái)討論一下，如何生成Excel。這在MVC模式中就相當(dāng)于View，也就是展示層。

這一層的代碼放在framework/libs/view/XlsWriterClass.py中。

總體思想

同樣，我們希望每個(gè)sheet的格式（顏色，寬度）什么的都不一樣，所以可以把這些配置參數(shù)都單列出來(lái)。

我們它們放到tpl這個(gè)文件夾下。

準(zhǔn)備工作

首先需要把tpl中的模板加載進(jìn)來(lái)

我們可以先看一下tpl中模板，比如說(shuō)下面的為價(jià)格明細(xì)清單的

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等多種類型，同時(shí)因?yàn)檎劭哿行枰褂?，所以單獨(dú)區(qū)分開(kāi)了。而且很有規(guī)律，是site+off

可以使用如下的函數(shù)進(jìn)行加載

   # ***************加載format*****************
   def addFormat(self):
       # 默認(rèn)在tpl文件中設(shè)置每個(gè)sheet的格式
       tpl = __import__("tpl." + self.view)
       sheet = getattr(tpl , self.view);
       types = getattr(sheet , "types");
       typeDict = {};
       # # 對(duì)所列出來(lái)的顏色類型的名稱一一的列舉
       for type in types:
           typeDict [type] = self.workbook.add_format(getattr(sheet , type ));
       return typeDict;

同樣可以使用assign把要傳遞進(jìn)去的變量搞進(jìn)去

    def assign(self, lists, outputKeys, sheetName, view):
        self.lists = lists;
        self.outputKeys = outputKeys;
        self.sheetName = sheetName;
        self.view = view;
        # 從文件中讀取format的類型，獲得format數(shù)組
        self.dFormat = {};
        self.dFormat = self.addFormat();
        # 將要輸出的outputKeys與每一列的序號(hào)組成dict，方便后面調(diào)度
        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));
        

打印數(shù)組

然后是調(diào)用XlsxWriter進(jìn)行打印。

首先需要獲取要打印的sheet名稱

    # 如果sheetName對(duì)應(yīng)的sheet還沒(méi)建立則建立，若已建立則獲取
    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);

然后就是把傳進(jìn)來(lái)的list遍歷

按照預(yù)先設(shè)定的輸出Excel列進(jìn)行打印

之前不是說(shuō)過(guò)折扣列格式不同嗎？而且折扣列的格式一般為"xxxOff"這種，我們可以拼接在一起

    # 定義全局變量
    col = 0;
    arr = self.lists;
for row in range(len(arr)):
    # 依次取每個(gè)要輸出的每一列對(duì)應(yīng)的key值
    for outkey in self.outputKeys:
        formatKey = "";
        # 若outkey等于discount,rate則單獨(dú)設(shè)置格式
        if outkey == "discount" or outkey == 'rate':
            formatKey = arr[row]["colorTag"] + "Off";
        else :
            formatKey = arr[row]["colorTag"];
        
        # 打印每個(gè)單元格
        worksheet.write(row , col , arr[row][outkey] , self.dFormat[formatKey])
        col += 1;

        col = 0;

設(shè)置其他行寬，列寬

打印完成以后還可以設(shè)置行寬，列寬

# *************設(shè)置每列的寬度*************
def setColumn(self):
    # 列序號(hào)
    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]);
# *************設(shè)置哪些列進(jìn)行隱藏*************
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});
        
# *************設(shè)置自動(dòng)篩選*************
def setAutofilter(self):
    worksheet = self.workbook.get_worksheet_by_name(self.sheetName);
    begin = "A1";
    # 末尾的列序號(hào)是outputKeys的最后一位
    endColumn = self.colIndexes[self.outputKeys[-1]];
    worksheet.autofilter("A1:" + endColumn + str(len(self.lists)));
    
# *************凍結(jié)首行*************
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']);

如何調(diào)用

同樣可以在Controller中進(jìn)行調(diào)用

xlswriter = XlsWriter(outputPath);
# —————————————————————————打印價(jià)格明細(xì)—————————————————————————
xlswriter.assign(lists,list(outputParam.keys()),sheetName,"totalConfigformat");
# xlswriter.setHidden(hideCols);
xlswriter.display();    
info("成功打印價(jià)格明細(xì)")

image.png