來 源：數(shù)據(jù)人創(chuàng)作者聯(lián)盟
人生苦短，我用Python。
相信做過報(bào)表的都對(duì)其煩不勝煩，周報(bào)，月報(bào)，季報(bào)；一期期的報(bào)表，一次次的心酸淚，煩不勝煩。至于小編是怎么知道的，因?yàn)槲乙彩沁@個(gè)苦逼報(bào)表大軍的一員。
是這樣的，當(dāng)時(shí)參與公司的一個(gè)項(xiàng)目，我的任務(wù)是出報(bào)表，聽到任務(wù)時(shí)，心中頓時(shí)樂開了花，嗚呼，這個(gè)簡(jiǎn)單，不就是出個(gè)報(bào)表嗎。So easy！可拿到歷史數(shù)據(jù)做成的表格頓時(shí)就不淡定了，一共是6個(gè)excel，每個(gè)excel是4-5個(gè)sheet，每個(gè)sheet里還有一堆花花綠綠的表格需要填寫，心里頓時(shí)不淡定了。完成整個(gè)任務(wù)之后，唯一感覺到的是，痛苦麻木。
之后通過網(wǎng)絡(luò)查詢資料，發(fā)現(xiàn)這個(gè)報(bào)表居然可以自動(dòng)化。接著花了一個(gè)星期的時(shí)間將報(bào)表自動(dòng)化，當(dāng)最后一個(gè)表格自動(dòng)化代碼寫完后，打開Python，運(yùn)行程序，不得不說，一個(gè)字爽，再也不用一點(diǎn)一點(diǎn)的往sheet里弄數(shù)據(jù)了。
好了，接下來就為大家介紹今天的主角——xlwings。
先簡(jiǎn)單的看一下最終生成的表格效果吧。
下面我們就來看看這個(gè)案例吧。
以下是我們的原始數(shù)據(jù)，一共以三個(gè)sheet，每個(gè)sheet，這三個(gè)sheet分別是原煤，原油，天然氣的數(shù)據(jù)。指標(biāo)有產(chǎn)量當(dāng)期值，產(chǎn)量累計(jì)值，產(chǎn)量同比增長(zhǎng)，產(chǎn)量累計(jì)增長(zhǎng)。
這些數(shù)據(jù)都是可以在國家統(tǒng)計(jì)局里下載出來的，有興趣的小伙伴可以自行下載。這個(gè)案例是讓我們將數(shù)據(jù)以上表格的形式輸出，指標(biāo)名稱是白色，單元格是黑色，此外數(shù)據(jù)中，紅色是大于平均值進(jìn)行得標(biāo)注，藍(lán)色是小于平均值進(jìn)行的標(biāo)注，表格字體為宋體。
首先呢，先導(dǎo)入相關(guān)庫，用python讀取原始數(shù)據(jù)。
import xlwings as xwraw_coal=pd.read_excel(r'統(tǒng)計(jì)局?jǐn)?shù)據(jù).xlsx',sheet_name='原煤')crude_oil=pd.read_excel(r'統(tǒng)計(jì)局?jǐn)?shù)據(jù).xlsx',sheet_name='原油')natural_gas=pd.read_excel(r'統(tǒng)計(jì)局?jǐn)?shù)據(jù).xlsx',sheet_name='天然氣')data=pd.merge(raw_coal,crude_oil,on='指標(biāo)')data=pd.merge(data,natural_gas,on='指標(biāo)')finally_data=data[['指標(biāo)','原煤產(chǎn)量當(dāng)期值(萬噸)','原油產(chǎn)量當(dāng)期值(萬噸)','天然氣產(chǎn)量當(dāng)期值(億立方米)']]print(finally_data)就數(shù)據(jù)而言，已經(jīng)離我們要的最終表格差的不遠(yuǎn)了，就差一點(diǎn)點(diǎn)細(xì)節(jié)了。
是時(shí)候上我們的主角xlwings，xlwings能夠非常方便的讀寫excel文件中的數(shù)據(jù)，最重要的是它可以對(duì)單元的格式進(jìn)行修改，可以與pandas無縫連接。
使用xlwings庫創(chuàng)建一個(gè)excel工作簿，在工作簿中創(chuàng)建一個(gè)表，表的名稱為finally_data。
然后將上面利用pandas整合的數(shù)據(jù)復(fù)制到finally_data表格中，當(dāng)然了將數(shù)據(jù)復(fù)制到表格中，在此看來有三種方式。
第一種：將一個(gè)數(shù)據(jù)看成一個(gè)單位，一個(gè)一個(gè)寫入創(chuàng)建的表格中，此時(shí)需要注意的是，每一個(gè)數(shù)據(jù)在excel的位置和在dataframe表格中的位置，以免出現(xiàn)錯(cuò)誤。
第二種：將一行數(shù)據(jù)看成一個(gè)單位，此時(shí)需要注意的是，每行數(shù)據(jù)的第一個(gè)在excel中的位置，參考復(fù)制粘貼形式。
第三種：將一張表的數(shù)據(jù)看成一個(gè)單位，本質(zhì)上與第二種沒什么區(qū)別，都是切片式傳入數(shù)據(jù)，但是第三種方法是一二維數(shù)組的形式寫入。
wb=xw.Book()sht=wb.sheets['Sheet1']sht.name='finally_data'columns=list(finally_data.columns)##得到列名sht.range('A1').value = columns####在第一行復(fù)制列名##第一種方式，將一個(gè)數(shù)據(jù)為單位，一個(gè)個(gè)寫入創(chuàng)建的表格中# for row in range(2,11):# for col in range(1,5):# sht.range(row,col).value =finally_data.iloc[row-2,col-1]##第二中方式，將一行數(shù)據(jù)為單位，一行一行的寫入創(chuàng)建的表格中# for i in range(0,len(finally_data)):# data_row=list(finally_data.iloc[i,:])# row=i+2# row_clo='A'+str(row)# sht.range(row_clo).value =data_row#第三種方式，將一張表格為單位，直接寫入創(chuàng)建的表格中finally_data1=finally_data.valuessht.range('A2').value = finally_data1
三者均能達(dá)到我們想要結(jié)果，各有優(yōu)劣，小編喜歡的是第三種。達(dá)到這一步的時(shí)候，剩下的就是對(duì)表格內(nèi)單元格的格式進(jìn)行修改了。
再對(duì)單元格進(jìn)行修改之前，我們要先求出來原煤產(chǎn)量當(dāng)期值，原油產(chǎn)量長(zhǎng)期值，天然氣產(chǎn)量當(dāng)期值，這三列數(shù)據(jù)中大于平均值和小于平均值的數(shù)據(jù)在Dataframe的位置，同時(shí)得出該數(shù)據(jù)在excel的位置，方便在進(jìn)行單元格的格式修改。
describe=finally_data.describe()avg=list(describe.loc['mean',:])##計(jì)算大于均值的數(shù)在excel的位置red_原煤=list(finally_data.index[finally_data['原煤產(chǎn)量當(dāng)期值(萬噸)']>avg[0]])red_position1=['B'+str(i+2) for i in red_原煤 ]red_原油=list(finally_data.index[finally_data['原油產(chǎn)量當(dāng)期值(萬噸)']>avg[1]])red_position2=['C'+str(i+2) for i in red_原油 ]red_天然氣=list(finally_data.index[finally_data['天然氣產(chǎn)量當(dāng)期值(億立方米)']>avg[2]])red_position3=['D'+str(i+2) for i in red_天然氣 ]red=red_position1+red_position2+red_position3##計(jì)算小于均值的數(shù)在excel的位置blue_原煤=list(finally_data.index[finally_data['原煤產(chǎn)量當(dāng)期值(萬噸)']<avg[0]])< p=''>blue_position1=['B'+str(i+2) for i in blue_原煤 ]blue_原油=list(finally_data.index[finally_data['原油產(chǎn)量當(dāng)期值(萬噸)']<avg[1]])< p=''>blue_position2=['C'+str(i+2) for i in blue_原油 ]blue_天然氣=list(finally_data.index[finally_data['天然氣產(chǎn)量當(dāng)期值(億立方米)']<avg[2]])< p=''>blue_position3=['D'+str(i+2) for i in blue_天然氣 ]blue=blue_position1+blue_position2+blue_position3print(red)print(blue終于所有的條件全部滿足了，最后可以對(duì)表格的格式進(jìn)行修改了。
首先就是將字體全部改成宋體，同時(shí)在表格中有數(shù)據(jù)的區(qū)域加上邊框。
#區(qū)域內(nèi)字體改變成宋體,加上邊框a_range = f'A1:D10'#區(qū)域sht.range(a_range).api.Font.Name='宋體' #字體sht.range(a_range).api.Borders(8).LineStyle = 1 #上邊框sht.range(a_range).api.Borders(9).LineStyle = 1 #下邊框sht.range(a_range).api.Borders(7).LineStyle = 1 #左邊框sht.range(a_range).api.Borders(10).LineStyle = 1 #右邊框sht.range(a_range).api.Borders(12).LineStyle = 1 #內(nèi)橫邊框sht.range(a_range).api.Borders(11).LineStyle = 1 #內(nèi)縱邊框
第二步就是將第一行的字體變成白色，單元格填充黑色。
#區(qū)域內(nèi)字體顏色成白色，單元格變成黑色b_range = f'A1:D1'#區(qū)域第一行sht.range(b_range).api.Font.Color = 0xffffffsht.range(b_range).color=(0, 0, 0)最后一步就是將大于均值的數(shù)據(jù)字體改成紅色，小于均值的字體改成藍(lán)色。然后在進(jìn)行保存。
#######在excel 表格里改變字體顏色for i in red:sht.range(i).api.Font.Color = 0x0000fffor i in blue:sht.range(i).api.Font.Color = 0xFF0000wb.save('結(jié)果數(shù)據(jù).xlsx')wb.close()
結(jié)果出來后，符合我們的要求。
本次案例完整結(jié)束，當(dāng)然了真正入手一個(gè)完整的自動(dòng)化報(bào)表項(xiàng)目，遠(yuǎn)不止這么簡(jiǎn)單，中間還會(huì)出現(xiàn)一下別的問題。
聯(lián)系客服
