Python商務(wù)辦公——使用python庫進行Excel讀寫

Four兄 2019-08-23

展開全文

寫在前面

新的一周，各位辛苦了，那就繼續(xù)辛苦三天吧！先前系列的文章中，小編和大家初步講解了一些怎么使用Python中的相關(guān)庫去繪制各種商務(wù)圖表，這絕對是我們工作中相當(dāng)重要的一個環(huán)節(jié)。但是，在我們平時工作中，除了可視化的需求之外，我們還需要返回一些整理好的Excel表格，因此，善良的小編怎么能忽視各位的這種潛在需求呢，本期文章，小編就和大家探討一下怎么使用python去操作Excel表格。對于Excel表格的操作主要包括三部分：讀入Excel表格、處理Excel表格以及寫入Excel表格。這三者中最重要以及最需要時間投入的就是Excel表格數(shù)據(jù)的分析與處理，這也將是筆者寫作的一大主題，后續(xù)文章中會逐步進行介紹；而對于Excel表格的讀入，在python中也存在著一些相對比較高效與實用的方法，這部分內(nèi)容小編會在后期通過一篇文章進行專門講解。排除了不講的，剩下的自然就是本期文章將要進行介紹的了，本期文章將和大家一起探討一下如何使用python去寫入Excel表格。使用python寫入Excel存在多種方法，但是本期文章小編將主要介紹三種方法：使用pandas庫、使用xlwt庫以及使用xlsxwriter庫。所以下文主要分成三部分，分別圍繞這三種方法，不過小編會將重點放在第三部分也就是xlsxwriter庫的介紹中，在那里小編會給出一個詳細的操作示例來對這一庫進行一個直觀性認識，而對其余兩部分的介紹小編會盡量只給出常用操作命令，下面我們正式進入主題。

pandas庫與Excel表格寫入

pandas庫，相信大家都不再陌生了吧，小編已經(jīng)在多處地方進行了介紹，并且一再告訴大家這個庫是進行數(shù)據(jù)分析的絕對王者，所以在今后的數(shù)據(jù)處理與分析系列文章中我們將會再一次不斷地接觸到它。但是盡管pandas庫在進行數(shù)據(jù)分析時相當(dāng)給力，但是不可否認的是pandas庫在對Excel表格文件進行寫入操作這一塊相對不是很優(yōu)秀，雖然pandas可以很方便地將數(shù)據(jù)寫入進Excel表格，但是這種寫入?yún)s是無法進行Excel格式設(shè)置的寫入，所以在很多時候我們必須對pandas導(dǎo)出的Excel表格進行二次修改，以便使得表格顯得美觀可讀。需要跟隨練習(xí)的朋友請下載練習(xí)文件exercise（可以到小編博客下載）。

小編默認各位都已經(jīng)在電腦上正確地安裝了pandas庫，如果還沒有進行安裝，那你可以參考小編前期的文章進行安裝，然后使用pandas庫導(dǎo)入剛剛下載的練習(xí)文件（請記住一點，pandas庫不僅可以寫入Excel文件，更加方便的是它能夠讀取Excel文件，這相對于接下來介紹的另外兩個庫是極具優(yōu)勢的）。

import pandas as pd
exercise = pd.read_excel('exercise.xlsx')
exercise.head()

第一行代碼導(dǎo)入了pandas庫，第二行代碼讀取了練習(xí)文件exercise.xlsx，第三行我們來看一下這一數(shù)據(jù)集的大致構(gòu)成，見下圖：

數(shù)據(jù)表共有10列，后三列分別表示1-3月的銷售額，而倒數(shù)第四列的quota列表示的報價總額。既然小編的目的是導(dǎo)出Excel表格，所以直接的方式就是原數(shù)據(jù)原路導(dǎo)出，不過小編可不是那種無聊的人，在導(dǎo)出之前進行些許的操作也不枉導(dǎo)入之累了，順便給大家介紹一個pandas庫中的超好用函數(shù)assign。小編打算在原數(shù)據(jù)表中增加一列匯總列去加總前三個月的銷售額，其次小編還想計算一下銷售總而占報價總額的比例，為了達到這一目的，小編使用以下代碼。

exercise = exercise.assign(total=(exercise['Jan']+exercise['Feb']+exercise['Mar']))
exercise = exercise.assign(pct=(exercise['total']/exercise['quota']))
exercise.head()

當(dāng)然你如果不習(xí)慣使用assign函數(shù)的話，還是可以采用之前小編所講解的方法，即通過使用下面的命令：

exercise['total'] = exercise['Jan']+exercise['Feb']+exercise['Mar']
exerciese['pct'] = exercise['total']/exercise['quota']

不過小編強烈推薦你使用assign命令，既然已經(jīng)完成了數(shù)據(jù)集的調(diào)整，那么接下來就是需要把Excel表格導(dǎo)出，這及時一條命令的事情。

exercise.to_excel('調(diào)整后練習(xí)數(shù)據(jù).xlsx',index = False)

這樣便在工作目錄下創(chuàng)建了一個名為調(diào)整后練習(xí)數(shù)據(jù)的Excel文件，至于在導(dǎo)出命令中加上index=False是因為要禁止pandas將數(shù)據(jù)的索引好也導(dǎo)出，下面看一下導(dǎo)出后的Excel文件：

對此筆者只想說，什麼仇什麼怨，這種格式的數(shù)據(jù)表如果發(fā)給老總，估計你可以直接卷鋪蓋走人了。所以說pandas庫雖然提供了一項極其便利的Excel文件寫入操作，但是這種便利是以犧牲美觀可讀為代價的，這就是下面將要介紹的兩個庫橫空出世的原因。

xlwt庫與Excel文件寫入

上面介紹了使用pandas庫快速讀寫Excel文件，可以看出使用pandas庫可以很方便快速導(dǎo)出Excel文件，但是這種便利伴隨著你每天吃魷魚的風(fēng)險（雖然小編最近酷愛燒汁魷魚須，但是真的不想被炒呀）。所以有需求就有創(chuàng)造，xlwt庫即刻誕生，這個庫可以很方便地對Excel文件進行格式化調(diào)整，因此可以輸出干凈整潔美觀可讀的Excel文件。不過這個庫的是服務(wù)于office2003及之前的Excel文檔的，雖然它也可以出來之后的文檔，但是在這方面要稍稍遜色與后面將講到的xlsxwriter庫。所以小編這里不將對其進行詳細講解，只對其最基本的命令進行介紹，對這個庫有特殊偏好的朋友可以參看其官方文檔。

在使用這個庫之前，你必須確保你已經(jīng)安裝了這個庫，所以請在命令行中使用以下代碼進行xlwt庫的安裝。

pip install xlwt
>>> import xlwt
>>> xlwt.__VERSION__
1.2.0

小編的xlwt庫版本是1.2.0，所以在上面的命令中自然會輸出這個數(shù)字序列，你也可以通過這條命令去檢查一下自己是否已經(jīng)正確地安裝了xlwt庫?，F(xiàn)在假設(shè)你已經(jīng)完成了安裝，那么下面就開始使用這個庫進行Excel文件的寫入吧。

import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('xlwt示例')
worksheet.write(0,0,'xlwt操作測試')
workbook.save('xlwt示例.xls')

這樣就在當(dāng)前目錄下創(chuàng)建了一個名為xlwt示例的Excel03文件，可以看一下輸出效果：

需要注意的是xlwt中對于行列的編號是從0,0開始的，這和python是一樣的，所以還沒有習(xí)慣Python序列編號的朋友當(dāng)注意這一點，下面在用一個進行格式化設(shè)置的例子來結(jié)束對于xlwt庫的介紹。

import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('xlwt格式化示例')
style = xlwt.XFStyle() # 初始化樣式
font = xlwt.Font() # 為樣式創(chuàng)建字體
font.name = 'Times New Roman' 
font.bold = True # 黑體
font.underline = True # 下劃線
font.italic = True # 斜體字
style.font = font # 設(shè)定樣式
worksheet.write(0, 0, 'xlwt操作測試（非格式化）') # 不帶樣式的寫入
worksheet.write(1, 0, 'xlwt操作測試（格式化）', style) # 帶樣式的寫入
workbook.save('xlwt格式化示例.xls') # 保存文件

可以看出相對于A1單元格中的內(nèi)容，A2單元格中內(nèi)容進行了加黑加粗添加下劃線以及字體斜體的格式化設(shè)置。這相對于pandas庫的輸出是不是要好看太多了，雖然中間使用的代碼可能相對較多，但是你不要忘記代碼的存在不是為了解決一個問題，而是為了解決同類型的一系列問題的，所以這點前期的代碼寫作投入成本幾乎是可以忽略不計的。

正如小編在文章開頭所講到的那樣，對于使用python進行Excel文件寫入，小編最喜歡的一個庫就是xlsxwriter了，所以下面小編重點來講一下xlsxwriter庫。

xlsxwriter庫與Excel文件寫入

“千呼萬喚始出來，xlsxwriter閃亮登場”！下面小編開始講述python中進行Excel表格文件寫入操作神器般的操作庫xlsxwriter（雖然這里說重點講解，但是小編也不會對這個庫的各種操作細節(jié)進行講解，本期主要是對其最基本的命令進行一下介紹，同時通過一個示例說明它的“變態(tài)”，更加詳細的介紹小編會在后續(xù)文章中使用5-7篇文章進行逐步介紹。）。如果一直閱讀小編文章的朋友絕對會對這個庫的名字有一個特別深刻的了解，xlsxwriter=xlsx+writer，還需要小編進一步解釋嗎？所以這個庫主要針對的是Excel2007版及之后的版本，而對于Excel03版本等缺少支持，不過我現(xiàn)在在想，大家誰還在使用Excel2003呢。當(dāng)然你如果一定要告訴我，你偏偏喜歡Excel2003，那么小編只能對你說，xlwt庫比較適合你。

xlsxwriter的安裝與基本操作

要使用xlsxwriter，你必須確保你先安裝了這個庫，為了讓各位朋友都能成功地安裝這個庫，小編這里提供四種安裝方式，我相信總要一款適合你。

pip install xlsxwriter  # 使用pip方式進行安裝
easy_install xlsxwriter # 使用easy_install進行安裝

第三種方式是你首先在官網(wǎng)下載xlsxwriter庫的壓縮包，然后將工作目錄改變到setup.py所在的文件夾，繼而運行以下命令：

pip setup.py install

第四種方式是通過github進行安裝，見下面命令：

git clone https://github.com/jmcnamara/XlsxWriter.git
cd XlsxWriter
python setup.py install

所以小編還是建議你不要折騰了，直接使用第一種安裝方式進行安裝吧，只要你不是上輩子毀了全宇宙，你就基本能夠安裝成功。既然安裝成功了，那就讓我們稍微嘚瑟一下來適應(yīng)一下它的語法命令吧。

import xlsxwriter
workbook = xlsxwriter.Workbook('xlsxwriter操作示例.xlsx')
worksheet = workbook.add_worksheet()
worksheet.write('A1', 'Hello xlsxwriter')
workbook.close()

第一行代碼導(dǎo)入xlsxwriter庫；第二行代碼創(chuàng)建一個名為xlsxwriter操作示例的工作簿；第三行代碼在這個工作簿創(chuàng)建了一個工作表；第四行是在新創(chuàng)建的工作表的A1單元格中輸入“Hello xlsxwriter”這個字符串；第五行用來關(guān)閉工作簿。這就是xlsxwriter庫用來進行Excel表格寫入時最基本的幾條命令，更多格式化命令以及高級命令小編會在后續(xù)文章中進行介紹，諸公不必著急。下面，小編以一個格式化的例子來完成對于xlsxwriter庫以及本期文章的講解。

xlsxwriter操作Excel文件示例

這里小編以文章開頭給出的Excel數(shù)據(jù)集為例來進行演示，考慮到xlsxwriter庫無法讀入Excel文件（請一定要記住，xlsxwriter是無法讀入Excel的），這里使用pandas配合xlsxwriter完成對于數(shù)據(jù)集的格式化設(shè)置。直接上代碼：

# 導(dǎo)入相關(guān)的庫
import numpy as np
import pandas as pd
from xlsxwriter.utility import xl_rowcol_to_cell
import xlsxwriter

# 讀入exercise文件，并增加兩列
df = pd.read_excel('exercise.xlsx')
number_rows = len(df.index)
df = df.assign(total=(df['Jan'] + df['Feb'] + df['Mar']))
df = df.assign(pct=(df['total']/df['quota']))

# 采用xlsxwriter內(nèi)核，使用pandas創(chuàng)建一個Excel文件xlsxwriter格式化示例
writer = pd.ExcelWriter('xlsxwriter格式化示例.xlsx', engine='xlsxwriter')
df.to_excel(writer, index=False, sheet_name='report')

# 激活工作簿和工作表以便進行下面寫入
workbook = writer.book
worksheet = writer.sheets['report']

# 設(shè)置工作表的窗口
worksheet.set_zoom(90)

# 增加一個貨幣格式，以便使用到銷售額上面
money_fmt = workbook.add_format({'num_format': '$#,##0', 'bold': True})

# 增加一個百分比格式，以便使用到pct上面
percent_fmt = workbook.add_format({'num_format': '0.0%', 'bold': True})

# 對于工作表的總體格式設(shè)置
total_fmt = workbook.add_format({'align': 'right', 'num_format': '$#,##0','bold': True, 'bottom':6})
# 對于表格中百分比數(shù)字的總體格式設(shè)置
total_percent_fmt = workbook.add_format({'align': 'right', 'num_format': '0.0%','bold': True, 'bottom':6})

# 對數(shù)據(jù)表列寬以及數(shù)值格式進行設(shè)置
worksheet.set_column('B:D', 20)
worksheet.set_column('E:E', 5)
worksheet.set_column('F:F', 10)
worksheet.set_column('G:K', 12, money_fmt)
worksheet.set_column('L:L', 12, percent_fmt)

# 在數(shù)據(jù)表中增加一行匯總行
for column in range(6, 11):
    cell_location = xl_rowcol_to_cell(number_rows+1, column)
    start_range = xl_rowcol_to_cell(1, column)
    end_range = xl_rowcol_to_cell(number_rows, column)
    formula = '=SUM({:s}:{:s})'.format(start_range, end_range)
    worksheet.write_formula(cell_location, formula, total_fmt)

# 給匯總行增加一個總計標(biāo)簽
worksheet.write_string(number_rows+1, 5, 'Total',total_fmt)
percent_formula = '=1+(K{0}-G{0})/G{0}'.format(number_rows+2)
worksheet.write_formula(number_rows+1, 11, percent_formula, total_percent_fmt)

# 顏色設(shè)置
color_range = 'L2:L{}'.format(number_rows+1)
format1 = workbook.add_format({'bg_color': '#FFC7CE',
                               'font_color': '#9C0006'})
format2 = workbook.add_format({'bg_color': '#C6EFCE',
                               'font_color': '#006100'})
worksheet.conditional_format(color_range, {'type': 'top',
                                           'value': '5',
                                           'format': format2})
worksheet.conditional_format(color_range, {'type': 'bottom',
                                           'value': '5',
                                           'format': format1})
writer.save()

相比最先開始直接由pandas庫導(dǎo)出的Excel表格，這個表格是不是更加美觀與可讀了。我想你發(fā)送這樣的表格給你老板應(yīng)該不至于被炒魷魚吧。更多的設(shè)置技巧要在實踐中根據(jù)自己的創(chuàng)意一步步進行提升，關(guān)鍵是你的思維與創(chuàng)意！講到這里就暫時完成了xlsxwriter庫的初步探討，更多內(nèi)容會在后續(xù)繼續(xù)更新。

后記

本文講到這里就暫告一段落了，本期文章介紹了三種用于操作Excel寫入的相關(guān)庫，通過介紹，小編最終將重點置于pandas+xlsxwriter上。本文只是對這種組合進行了初步探討，后續(xù)會進一步加大對這一主題的講解。下期文章小編準(zhǔn)備接著回到matplotlib庫上，講解這一庫的另外一種繪圖語法，敬請期待！再次感謝你們的支持與鼓勵，你們的陪伴是小編前進的動力！