|
pandas 讀寫 Excel，可以用于將重復的數據加工工作交給 pandas，節(jié)省手工勞動，使用起來也比較方便，但輸出的格式并不太美觀。本文介紹 read_excel() 和 to_excel() 的部分細節(jié)，同時探討如何輸出一個較為美觀的 Excel 工作表。
pandas 讀取 Excel 文件
語法
DataFrame.read_excel() 的語法：
pandas.read_excel(io,
sheet_name=0,
header=0,
names=None,
index_col=None,
parse_cols=None,
usecols=None,
squeeze=False,
dtype=None,
engine=None,
converters=None,
true_values=None,
false_values=None,
skiprows=None,
nrows=None,
na_values=None,
keep_default_na=True,
verbose=False,
parse_dates=False,
date_parser=None,
thousands=None,
comment=None,
skip_footer=0,
skipfooter=0,
convert_float=True,
mangle_dupe_cols=True,
**kwds)
參數和返回值的說明請參考 pandas 文檔 。
最簡單的用法，只需要指定文件名參數，支持 xls 文和 xlsx 文件格式，函數的返回值為 DataFrame 類型的對象。比如讀取 D 盤根目錄下的 source.xlsx 文件：
import pandas as pd
df1 = pd.read_excel(r'D:/source.xlsx)
如果想讀取 py 文件所在目錄下的某個 Excel 文件，可以參考下面的代碼：
import pandas as pd
import os
# get path of current directory
curr_path = os.path.dirname(os.path.abspath(__file__))
fname = os.path.join(curr_path, 'users.xlsx')
df2 = pd.read_excel(fname)
指定要讀取的工作表
對于有多個工作表的 Excel 文件，pandas 默認讀取第一個工作表( sheet_name=0 ）。通過如下兩種方法可以指定要讀取的工作表：
# 方法一：通過 index 指定工作表
df3 = pd.read_excel(file_name, sheet_name=0)
# 方法二：指定工作表名稱
df4 = pd.read_excel(file_name, sheet_name='Sheet1')
導入指定列
如果只想導入指定的列，通過 usecols 參數，比如想導入 A:D 和 H 這 4 列，有如下兩種方法：
df6 = pd.read_excel(r'D:/source.xlsx', usecols='A:D,H')
# 或者
df6 = pd.read_excel(r'D:/source.xlsx', usecols=[0,1,2,3,7])
指定表頭
默認情況下，pandas 假定第一行為表頭 (header)，如果 Excel 不是從第一行開始，header 參數用于指定將哪一行作為表頭，表頭在 DataFrame 中變成列索引 (column index) ，header 參數從 0 開始，比如第二行作為 header，則：
df = pd.read_excel(file_name, header=1)
pandas 寫入 Excel
語法
DataFrame.to_excel() 的語法：
DataFrame.to_excel(excel_writer,
sheet_name='Sheet1',
na_rep='',
float_format=None,
columns=None,
header=True,
index=True,
index_label=None,
startrow=0, startcol=0,
engine=None,
merge_cells=True,
encoding=None,
inf_rep='inf',
verbose=True,
freeze_panes=None)
參數和返回值的說明請參考 pandas 文檔 。
數據寫入 Excel，需要首先安裝一個 engine，由 engine 負責將數據寫入 Excel，pandas 使用 openpyx 或 xlsxwriter 作為寫入引擎。
要將單一對象寫入 Excel，只需要指定 file name 即可：
import pandas as pd
import os
path = os.path.dirname(os.path.abspath(__file__))
source_file = os.path.join(path, 'source.xlsx')
output_file = os.path.join(path, 'output.xlsx')
df = pd.read_excel(source_file, sheet_name=0)
df.to_excel(output_file)
如果 output.xlsx 文件已經存在，to_excel()先刪除 output.xlsx 文件，然后重新生成一個新的文件，并且默認添加一個索引列，索引為從 0 到 n 的整數。
不使用索引
導出 Excel，一般不需要索引，將 index 參數設為 False 即可:
df.to_excel(output_file, index=False)
多工作表導出
導出多個工作表需要明確給出 excel writer engine，然后調用 DataFrame.to_excel()方法：
import pandas as pd
import os
path = os.path.dirname(os.path.abspath(__file__))
source_file = os.path.join(path, 'source.xlsx')
output_file = os.path.join(path, 'output.xlsx')
df1 = pd.read_excel(source_file, sheet_name=0)
df2 = pd.read_excel(source_file, sheet_name=0, usecols='A:D,H')
with pd.ExcelWriter(output_file, engine='xlsxwriter') as writer:
df1.to_excel(writer, sheet_name='Sheet1', index=False)
df2.to_excel(writer, sheet_name='Sheet2', index=False)
工作表美化
pandas 導出的工作表并不美觀，如果想對工作表進行美化的話，可在 to_excel()方法之后，通過Excel writer engine 的格式設置的功能來設置格式。根據測試， to_excel() 因為先刪除文件，所以也不能使用 Template 來保存預定義格式。所以如果需要導出有格式的 Excel 文件，比如作為報表輸出，可考慮 Template + Excel writer engine 手工代碼的方式。
Creating Advanced Excel Workbooks with Python 這篇文章講到了一個方法，使用 xlsxwriter 的 add_table() 方法，在 Excel 中創(chuàng)建一個 Table 對象(中文經常被稱為智能表格)，然后選擇一個預定義的格式。我對代碼進行了加工，使之更具普適性：
import pandas as pd
import os
def get_col_widths(dataframe):
return [max([len(str(s)) for s in dataframe[col].values]
+ [len(col)]) for col in dataframe.columns]
def fmt_excel(writer, sheetname, dataframe):
# Get the workbook and the summary sheet so we can add the formatting
workbook = writer.book
worksheet = writer.sheets[sheetname]
col_count = dataframe.shape[1]
row_count = dataframe.shape[0]
col_names = []
for i in range(0, col_count):
col_names.append({'header': dataframe.columns[i]})
# rng = 'A1:H{}'.format(row_count + 1)
worksheet.add_table(0, 0, row_count,col_count-1, {
'columns': col_names,
'style': 'Table Style Medium 20'
})
# auto column size
col_widths = get_col_widths(dataframe)
for i, width in enumerate(col_widths):
worksheet.set_column(i, i, width)
path = os.path.dirname(os.path.abspath(__file__))
source_file = os.path.join(path, 'source.xlsx')
output_file = os.path.join(path, 'output.xlsx')
df = pd.read_excel(source_file, sheet_name=0)
writer = pd.ExcelWriter(output_file, engine='xlsxwriter')
df.to_excel(writer, 'Sheet1', index=False)
fmt_excel(writer, 'Sheet1', df)
writer.save()
參考
|
