Python實(shí)現(xiàn)數(shù)據(jù)寫(xiě)入 Excel 的三種模塊
導(dǎo)讀：本文說(shuō)明如何使用 xlsxwriter、pandas、openpyxl 三個(gè)庫(kù)來(lái)實(shí)現(xiàn)數(shù)據(jù)寫(xiě)入 Excel。
import?xlsxwriter?as?xw
import?pandas?as?pd
import?openpyxl?as?op
"-------------數(shù)據(jù)用例-------------"
orderIds?=?[1,2,3]
items?=?['A','B','C']
myData?=?[10,20,30]
testData?=?[orderIds,items,myData]
filename1?=?'測(cè)試1.xlsx'
filename2?=?'測(cè)試2.xlsx'
filename3?=?'測(cè)試3.xlsx'
"--------------執(zhí)行----------------"
xw_toexcel(testData,filename1)
pd_toexcel(testData,filename2)
op_toexcel(testData,filename3)
01 xlsxwriter
def?xw_toexcel(data,filename):?#?xlsxwriter庫(kù)儲(chǔ)存數(shù)據(jù)到excel
????workbook?=?xw.Workbook(filename)?#?創(chuàng)建工作簿
????worksheet1?=?workbook.add_worksheet("sheet1")?#?創(chuàng)建子表
????worksheet1.activate()?#?激活表
????title?=?['序號(hào)','項(xiàng)目','數(shù)據(jù)']?#?設(shè)置表頭
????worksheet1.write_row('A1',title)?#?從A1單元格開(kāi)始寫(xiě)入表頭
????i?=?2?#?從第二行開(kāi)始寫(xiě)入數(shù)據(jù)
????for?j?in?range(len(data)):
????????insertData?=?[data[0][j],data[1][j],data[2][j]]
????????row?=?'A'?+?str(i)
????????worksheet1.write_row(row,?insertData)
????????i?+=?1
????workbook.close()?#?關(guān)閉表
02 pandas
def?pd_toexcel(data,filename):?#?pandas庫(kù)儲(chǔ)存數(shù)據(jù)到excel
????dfData?=?{?#?用字典設(shè)置DataFrame所需數(shù)據(jù)
????????'序號(hào)':data[0],
????????'項(xiàng)目':data[1],
????????'數(shù)據(jù)':data[2]
????}
????df?=?pd.DataFrame(dfData)?#?創(chuàng)建DataFrame
????df.to_excel(filename,index=False)?#?存表，去除原始索引列（0,1,2...）
03 openpyxl
def?op_toexcel(data,filename):?#?openpyxl庫(kù)儲(chǔ)存數(shù)據(jù)到excel
????wb?=?op.Workbook()?#?創(chuàng)建工作簿對(duì)象
????ws?=?wb['Sheet']?#?創(chuàng)建子表
????ws.append(['序號(hào)','項(xiàng)目','數(shù)據(jù)'])?#?添加表頭
????for?i?in?range(len(data[0])):
????????d?=?data[0][i],?data[1][i],?data[2][i]
????????ws.append(d)?#?每次寫(xiě)入一行
????wb.save(filename)
小結(jié)
本文完整源碼下載：
干貨直達(dá)??
評(píng)論
圖片
表情