python操作Excel，你覺(jué)得哪個(gè)庫(kù)更好呢？

對(duì)比學(xué)習(xí)python，更高效~

Excel數(shù)據(jù)的類型及組織方式

每一個(gè)Excel數(shù)據(jù)文件從上至下分為三個(gè)層級(jí)的對(duì)象：
workbook：每一個(gè)Excel文件就是一個(gè)workbook。
sheet：每一個(gè)workbook中可以包含多個(gè)sheet，具體就對(duì)應(yīng)Excel中我們?cè)谧笙履_所看到的“sheet1”,“sheet2”等。
cell：每一個(gè)sheet就是我們通常所看到的一個(gè)表格，可以含有m行，n列，每個(gè)確定的行號(hào)，列號(hào)所對(duì)應(yīng)的一個(gè)格子就是一個(gè)cell。

Excel模塊對(duì)比

平臺(tái)：Mac
工具：PyCharm
Python：python3.6
Excel行數(shù)：64543

從一個(gè)既有的xlsx文件中讀取數(shù)據(jù)，按照Excel文件的三個(gè)層級(jí)，分別來(lái)看一下以上三個(gè)庫(kù)的操作方式。

xlrd & xlwt & xlutils

xlrd負(fù)責(zé)讀取Excel文件，支持xls、xlsx
xlwt負(fù)責(zé)寫入Excel文件，只支持xls格式。對(duì)于現(xiàn)在已經(jīng)Office2019了，還只支持2003版的格式難怪會(huì)被人拋棄。
xlutils是一個(gè)工具模塊，我們一般只用到copy模塊，用于修改已存在的Excel文件

open_workbook

param: encoding_override ?讀取Excel文件時(shí)默認(rèn)使用ASCII編碼。此處可指定編碼。
param: formatting_info ?
設(shè)置是否保存Excel格式，比如加粗。一般只對(duì)簡(jiǎn)單的格式起作用，而且xlsx格式文件不支持。
這可以節(jié)省內(nèi)存。在這種情況下，通過(guò)忽略文件BLANK和MULBLANK記錄，“空白”單元格（它們具有自己的格式信息但沒(méi)有數(shù)據(jù)）會(huì)被視為空白

param: on_demand
按需加載工作表。默認(rèn)加載False，加載所有的工作表。設(shè)置True時(shí)，調(diào)用book.sheet_by_name或book.sheet_by_index才會(huì)加載工作表。book.sheets加載所有工作表。book.sheet_loaded判斷表單是否已加載。book.unload_sheet卸載已加載表單。

1. 打開(kāi)workbook：
   import xlrd
   book = xlrd.open_workbook("myfile.xls") #book就賦值為一個(gè)Excel文件了
   注：
   Book 類的方法、屬性等：即就可以對(duì)上面的book進(jìn)行操作了
   book.nsheets: 在Book對(duì)象中的文件有多少個(gè)worksheet。
   book.sheet_by_index(sheetx): 根據(jù)提供的sheetx索引來(lái)獲取我們需要的sheet表，返回的是一個(gè)Sheet類的實(shí)例。
   book.sheet_by_name(sheet_name): 根據(jù)提供的sheet_name來(lái)獲取對(duì)應(yīng)名稱的sheet類對(duì)象，返回的也是一個(gè)Sheet類的對(duì)象。
   book.sheet_names(): 在Book對(duì)象中的所有sheet表的名稱列表。
   book.sheets(): 返回在Book對(duì)象中所有的Sheet對(duì)象實(shí)例列表。

2. 打開(kāi)所需的sheet：
   sheet = book.sheet_by_index(0) #獲得一個(gè)sheet,也可以使名字獲得
   print sheet.name, sheet.nrows, sheet.ncols
   注：
   Sheet類方法、屬性等：
   sheet.cell(rowx, colx): 根據(jù)給出的行和列的參數(shù)獲取得到cell類，返回一個(gè)Cell類實(shí)例對(duì)象。
   sheet.cell_type(rowx, colx): 返回對(duì)應(yīng)的cell對(duì)象的Type類型。
   sheet.cell_value(rowx, colx): 返回對(duì)應(yīng)的cell對(duì)象的value值。
   sheet.col(colx): 返回指定列的所有cell類對(duì)象序列。
   sheet.name: 返回sheet對(duì)象的名稱。
   sheet.ncols: 返回在sheet對(duì)象中的列的數(shù)目。
   sheet.nrows: 返回在sheet對(duì)象中的行的數(shù)目。
   sheet.row(rowx): 返回指定的行的所有cell對(duì)象的序列。
   sheet.row_values(rowx, start_colx=0, end_colx=None):獲取行內(nèi)容。
   sheet.col_values(colx, start_rowx=0, end_rowx=None):獲取列內(nèi)容。

3. 獲取對(duì)應(yīng)cell的值：
   cell=sheet.cell(rowx=29, colx=3) #根據(jù)給出的行和列的參數(shù)獲取得到cell類，返回一個(gè)Cell類實(shí)例對(duì)象.
   sh.cell_value(rowx=29, colx=3)
   注：
   Cell類的屬性、方法如下：
   Cell類對(duì)象有3種屬性：ctype, value, xf_index
   如果在excel文件打開(kāi)的時(shí)候，formatting_info未啟用的時(shí)候，xf_index是為None
   下面列出了cell的類型，以及他們?cè)趐ython中所代表的值
   type symbol type number python value
   XL_CELL_EMPTY 0 空的字符串''
   XL_CELL_TEXT 1 unicode字符串
   XL_CELL_NUMBER 2 float
   XL_CELL_DATE 3 float
   XL_CELL_BOOLEAN 4 int;1 --- True,0 --- False
   XL_CELL_ERROR 5 int代表是一個(gè)excel內(nèi)部錯(cuò)誤碼；
   XL_CELL_BLANK 6 空的字符串'', 注意：這個(gè)類型僅僅會(huì)出現(xiàn)，當(dāng)函數(shù)open_workbook(..,formatting_info=True)這樣設(shè)置的時(shí)候。

Excel單元格內(nèi)容存在不同的格式，可使用cell.ctype獲取類型判斷。
ctype : 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error
日期類型可以使用xldate_as_tuple模塊處理：

value?=?sheet.cell_value(row,?col)
if?sheet.cell(row,?col).ctype?==?3:
????date_value?=?xlrd.xldate_as_tuple(value,?book.datemode)?#輸出元組?(2018,?2,?23,?0,?0,?0)

openpyxl?

openpyxl支持可讀可寫操作，不支持xls文件的讀寫??

. 一個(gè)簡(jiǎn)單創(chuàng)建例子?

from?openpyxl?import?Workbook?
wb?=?Workbook()??
ws?=?wb.active??
ws['A1']?=?42??
ws.append([1,?2,?3])??
import?datetime?
ws['A3']?=?datetime.datetime.now().strftime("%Y-%m-%d")??
wb.save("sample.xlsx")?

通過(guò)openpyxl.workbook.Workbook.active()得到worksheet.?
ws = wb.active?
注意：?
該方法使用_active_sheet_index屬性, 默認(rèn)會(huì)設(shè)置0，也就是第一個(gè)worksheet。除非手動(dòng)修改，否則使用active方法得到都是第一個(gè)worksheet。?
你也可以創(chuàng)建worksheets，通過(guò) openpyxl.workbook.Workbook.create_sheet() 方法：?
ws1 = wb.create_sheet("Mysheet") #插入到最后(default)?
或
ws2 = wb.create_sheet("Mysheet", 0) #插入到最開(kāi)始的位置??

創(chuàng)建的sheet的名稱會(huì)自動(dòng)創(chuàng)建，按照sheet，sheet1，sheet2自動(dòng)增長(zhǎng)，通過(guò)title屬性可以修改其名稱。?
ws.title = "New Title"?

默認(rèn)的sheet的tab是白色的，可以通過(guò) RRGGBB顏色來(lái)修改sheet_properties.tabColor屬性從而修改sheet tab按鈕的顏色:?
ws.sheet_properties.tabColor = "1072BA"?
當(dāng)你設(shè)置了sheet的名稱，可以將其看成workbook中的一個(gè)key。?

工作表有兩種獲取方式?
ws3 = wb["New Title"] --推薦?
ws4 = wb.get_sheet_by_name("New Title")

查看workbook中的所有worksheets名稱：book.sheetnames???
遍歷worksheets：?

for?sheet?in?book:?
????print?sheet.title?

. 操作數(shù)據(jù)?

單元格可以看作是worksheet的key，通過(guò)key去訪問(wèn)和設(shè)置單元格中的數(shù)據(jù)?
value = ws["A4"]?
ws["A4"] = data??
注意：?

1. 當(dāng)worksheet在內(nèi)存中被創(chuàng)建時(shí),是沒(méi)有包含cells的，cells是在首次訪問(wèn)時(shí)創(chuàng)建.?

2. 可以循環(huán)在內(nèi)存中創(chuàng)建cells，這時(shí)不指定他們的值也會(huì)創(chuàng)建該cells些：(創(chuàng)建100x100cells)?

for?i?in?range(1,101):?
????for?j?in?range(1,101):?
????????ws.cell(row=i,?column=j)?

通過(guò)切片Ranges指定許多cells?
cell_range = ws['A1': 'C2']?
同樣也可以Ranges行或者列:?
colC = ws['C']?
col_range = ws['C:D']?
rwo10 = ws[10]?
rwo_range = ws[5:10]??

也可以使用 openpyxl.worksheet.Worksheet.iter_rows() 方法:(需要指定行->行，截止列)?

for?row?in?ws.iter_rows(min_col=1,?min_row=1,?max_col=3,?max_row=2):?
????for?cell?in?row:?
????????print?cell.value?

如果你需要遍歷所有文件的行或列,可以使用openpyxl.worksheet.Worksheet.rows() 屬性:tuple(ws.rows)或者 openpyxl.worksheet.Worksheet.columns() 屬性:tuple(ws.columns)??

最簡(jiǎn)單最安全的方法保存workbook是使用openpyxl.workbook.Workbook對(duì)象的 openpyxl.workbook.Workbook.save()方法:?
wb = Workbook()?
wb.save('balances.xlsx')?

保存的默認(rèn)位置在python的根目錄下。?
注意：會(huì)自動(dòng)覆蓋已經(jīng)存在文件名的文件。??
我們可以導(dǎo)入一個(gè)文件進(jìn)行讀或者寫，像寫一樣我們可以導(dǎo)入openpyxl.load_workbook()已經(jīng)存在的workbook:?

from?openpyxl?import?load_workbook?
wb2?=?load_workbook('test.xlsx')?
print?wb2.get_sheet_names()?

append函數(shù)?

可以一次添加多行數(shù)據(jù)，從第一行空白行開(kāi)始（下面都是空白行）寫入?
如果需要整列進(jìn)行添加數(shù)據(jù)：?

lst?=?["test1",?"teset2",?"test3",?"test4"]?
for?row?in?range(1,?40):?
????ws1.append(lst)??
#?添加多行??
rows?=?[?['Number',?'data1',?'data2'],?[2,?40,?30],?[3,?40,?25],?[4,?50,?30],?[5,?30,?10],?[6,?25,?5],?[7,?50,?10]]?
map(ws1.append,?rows)?

由于append函數(shù)只能按行寫入。如果我們想按列寫入呢？??
寫入例子：?

#?workbook相關(guān)??
from?openpyxl?import?Workbook?
from?openpyxl.compat?import?range?
from?openpyxl.utils?import?get_column_letter??
wb?=?Workbook()??
dest_filename?=?'empty_book.xlsx'??
ws1?=?wb.active?
ws1.title?=?"range?names"??
for?row?in?range(1,?40):?
????ws1.append(["test%s"?%?i?for?i?in?range(6)])??
ws2?=?wb.create_sheet(title="Pi")??
ws2['F5']?=?3.14??
ws3?=?wb.create_sheet(title="Data")?
for?row?in?range(10,?20):?
????for?col?in?range(27,?54):?
????????_?=?ws3.cell(column=col,?row=row,?value="{0}".format(get_column_letter(col)))?
print(ws3['AA10'].value)?
wb.save(filename=dest_filename)?

使用公式?

from?openpyxl?import?Workbook?
wb?=?Workbook()?
ws?=?wb.active?

#?add?a?simple?formula??
for?i?in?range(1,?20):?
????ws.append([i?*?num?for?num?in?range(1,?4)])?
for?i?in?range(1,?20):?
????ws["D%s"?%?i]?=?"=SUM(A{0},?C{0})".format(i)?
wb.save("formula.xlsx")?

. 設(shè)置單元格風(fēng)格--Style??

先導(dǎo)入需要的類from openpyxl.styles import Font, colors, Alignment?
分別可指定字體相關(guān)，顏色，和對(duì)齊方式。?

字體?

bold_itatic_24_font?=?Font(name='等線',?size=24,?italic=True,?color=colors.RED,?bold=True)?
sheet['A1'].font?=?bold_itatic_24_font?

上面的代碼指定了等線24號(hào)加粗斜體，字體顏色紅色。直接使用cell的font屬性，將Font對(duì)象賦值給它??。

對(duì)齊方式?

也是直接使用cell的屬性aligment，這里指定垂直居中和水平居中。除了center，還可以使用right、left等等參數(shù)。?

#?設(shè)置B1中的數(shù)據(jù)垂直居中和水平居中??
sheet['B1'].alignment?=?Alignment(horizontal='center',?vertical='center')?

設(shè)置行高和列寬?

有時(shí)候數(shù)據(jù)太長(zhǎng)顯示不完，就需要拉長(zhǎng)拉高單元格。

#?第2行行高??
sheet.row_dimensions[2].height?=??40???

#?C列列寬??
sheet.column_dimensions['C'].width?=??30?

合并和拆分單元格?

所謂合并單元格，即以合并區(qū)域的左上角的那個(gè)單元格為基準(zhǔn)，覆蓋其他單元格使之稱為一個(gè)大的單元格。?
相反，拆分單元格后將這個(gè)大單元格的值返回到原來(lái)的左上角位置。?

#?合并單元格，?往左上角寫入數(shù)據(jù)即可??
sheet.merge_cells('B1:G1')?#?合并一行中的幾個(gè)單元格??
sheet.merge_cells('A1:C3')?#?合并一個(gè)矩形區(qū)域中的單元格?

合并后只可以往左上角寫入數(shù)據(jù)，也就是區(qū)間中:左邊的坐標(biāo)。?
如果這些要合并的單元格都有數(shù)據(jù)，只會(huì)保留左上角的數(shù)據(jù)，其他則丟棄。換句話說(shuō)若合并前不是在左上角寫入數(shù)據(jù)，合并后單元格中不會(huì)有數(shù)據(jù)。??

以下是拆分單元格的代碼。拆分后，值回到A1位置。?
sheet.unmerge_cells('A1:C3')

xlsxwriter?

只支持寫入，不支持讀取，也不支持查詢，不支持xls格式??

. Workbook類?

Workbook類定義：Workbook(filename[,options]) ?
該類可以實(shí)現(xiàn)創(chuàng)建一個(gè)XlsxWriter的Worbook對(duì)象。Workbook類表示整個(gè)電子表格文件。??

• 參數(shù)filename（String類型）為創(chuàng)建的Excel文件存儲(chǔ)路徑；?

• 參數(shù)options（Dict類型）為可選的Workbook參數(shù)，作為初始化表格內(nèi)容；??
  例如：{'strings_to_numbers' : True}表示使用worksheet.write()方法時(shí)激活字符串轉(zhuǎn)換數(shù)字。?

add_Worksheet([sheetname])方法?

添加一個(gè)新的工作表，參數(shù)sheetname(String類型)為可選參數(shù)，默認(rèn)為sheet1；比如說(shuō)按照表單順序該工作表為第五張，則默認(rèn)名為sheet5。?

#?-*-?coding:?utf-8?-*-???
import?xlsxwriter??
workbook?=?xlsxwriter.Workbook('Demo2.xlsx')?#?創(chuàng)建一個(gè)名為Dome2.xlsx的表格??
worksheet1?=?workbook.add_worksheet()?#?添加第一個(gè)表單，默認(rèn)為sheet1??
worksheet2?=?workbook.add_worksheet('Files')?#?添加一個(gè)名為File的表單??
worksheet3?=?workbook.add_worksheet()?#?添加一個(gè)表單，默認(rèn)為sheet3??
workbook.close()?

add_format([properties])方法?

在工作表中創(chuàng)建一個(gè)新的格式對(duì)象來(lái)格式化單元格。參數(shù)properties（Dict類型）為指定一個(gè)格式屬性的字典。?

bold?=?workbook.add_format()??
bold.set_bold()?#?設(shè)置為加粗?

add_chart(options)方法?

在工作表中創(chuàng)建一個(gè)圖表對(duì)象，內(nèi)部通過(guò)insert_chart()方法來(lái)實(shí)現(xiàn)，參數(shù)options(dict類型)為圖標(biāo)指定一個(gè)字典屬性。?

#?設(shè)置線條類型的圖表對(duì)象?
chart?=?workbook.add_chart({'type'?:?'line'})?

close()方法?

#?關(guān)閉工作表文件?
workbook.close()?

. Worksheet類?

Worksheet代表了一個(gè)Excel工作表，是XlsxWriter模塊操作Excel內(nèi)容最核心的一個(gè)類，例如將數(shù)據(jù)寫入到單元格或者工作表格式布局等。?
Workbook對(duì)象通過(guò)add_worksheet()方法來(lái)創(chuàng)建。?

write(row, col, *args)方法?

其作用是將普通數(shù)據(jù)寫入到工作表單元格。?
參數(shù)row為行標(biāo)，col為列標(biāo)，坐標(biāo)檢索起始值為0；?
*args無(wú)名字參數(shù)為數(shù)據(jù)內(nèi)容，可以是數(shù)字、公式、字符串或格式對(duì)象。?
write方法已經(jīng)作為其它更加具體數(shù)據(jù)類型方法的別名。如以下幾種：?

• write_string()，寫入字符串類型數(shù)據(jù)；?

• write_number()，寫入數(shù)據(jù)類型數(shù)據(jù)；?

• write_blank()，寫入空類型數(shù)據(jù)；?

• write_formula()，寫入公式型數(shù)據(jù)；?

• write_datetime()，寫入日期類型數(shù)據(jù)；?

• wrtie_boolean()，寫入邏輯類型數(shù)據(jù)；?

• write_url()，寫入超鏈接類型數(shù)據(jù)。?

import?xlsxwriter??
workbook?=?xlsxwriter.Workbook('Demo2.xlsx')?#?創(chuàng)建一個(gè)名為Dome2.xlsx的表格??
worksheet1?=?workbook.add_worksheet()?#?添加第一個(gè)表單，默認(rèn)為sheet1??
worksheet2?=?workbook.add_worksheet('Files')?#?添加一個(gè)名為File的表單??
worksheet3?=?workbook.add_worksheet()?#?添加一個(gè)表單，默認(rèn)為sheet3??
worksheet1.write(0,?0,?'Hello')?#?write_string()??
worksheet1.write(1,?0,?1.23)?#?write_number()??
worksheet1.write(2,?0,?'')?#?write_blank()??
worksheet1.write(3,?0,?None)?#?write_blank()??
worksheet1.write(4,?0,?'=SIN(PI()/4)')?#?write_formula()??
workbook.close()?

set_row(row, height, cell_format, options)方法?

其作用是設(shè)置行單元格屬性。?

• row（int類型），指定行位置，起始下標(biāo)為0；?

• height（float類型），設(shè)置行高，單位為像素；?

• cell_format（format類型）指定格式對(duì)象；?

• options（dict類型）設(shè)置行hidden（隱藏）、level（組合分級(jí)）、collapsed（折疊）。

import?xlsxwriter??
#?創(chuàng)建一個(gè)名為Dome2.xlsx的表格
workbook?=?xlsxwriter.Workbook('Demo2.xlsx')???
#?添加第一個(gè)表單，默認(rèn)為sheet1??
worksheet1?=?workbook.add_worksheet()?
#?在單元格A1寫入‘Hello’字符串??
worksheet1.write('A1',?'Hello')?
#?定一個(gè)加粗的格式對(duì)象??
cell_format?=?workbook.add_format({'bold':?True})?
#?第一行單元格高度為40px，且引用加粗格式對(duì)象?
worksheet1.set_row(1,?None,?None,?{'hidden':?True})?
#?隱藏第2行單元格??
worksheet1.set_row(0,?40,?cell_format)?workbook.close()?

set_column(first_col, last_col, cell_format, options)方法?

同上一個(gè)方法類似，這個(gè)方法是用于設(shè)置一列或多列單元格屬性。?

insert_image(row, col, image[,options])方法?

其作用是插入圖片到指定單元格，支持PNG\JPG\BMP等圖片格式 ?

• row為行坐標(biāo)，col為列坐標(biāo)，坐標(biāo)索引起始位置為0；?

• image（string類型）為圖片路徑；?

• options（dict類型）為可選參數(shù)，指定圖片的位置、比例、鏈接url等信息。?

import?xlsxwriter??
workbook?=?xlsxwriter.Workbook('Demo2.xlsx')?#?創(chuàng)建一個(gè)名為Dome2.xlsx的表格??
worksheet1?=?workbook.add_worksheet()?#?添加第一個(gè)表單，默認(rèn)為sheet1??
worksheet1.insert_image(2,?2,?'blog.png')?#?在第三行第三列插入一張圖片??
workbook.close()?

. 添加圖表示例?

#!/usr/bin/python?
#coding:?utf-8??
import?xlsxwriter??
workbook?=?xlsxwriter.Workbook('chart.xlsx')?
worksheet?=?workbook.add_worksheet()??
#新建圖標(biāo)對(duì)象?
chart?=?workbook.add_chart({'type':?'column'})??
#向?excel?中寫入數(shù)據(jù)，建立圖標(biāo)時(shí)要用到?
data?=?[?
????[1,?2,?3,?4,?5],?
????[2,?4,?6,?8,?10],?
????[3,?6,?9,?12,?15],?
]??
worksheet.write_column('A1',?data[0])?
worksheet.write_column('B1',?data[1])?
worksheet.write_column('C1',?data[2])??
#向圖表中添加數(shù)據(jù)，例如第一行為：將A1~A5的數(shù)據(jù)轉(zhuǎn)化為圖表?
chart.add_series({'values':?'=Sheet1!$A$1:$A$5'})?
chart.add_series({'values':?'=Sheet1!$B$1:$B$5'})?
chart.add_series({'values':?'=Sheet1!$C$1:$C$5'})??
#將圖標(biāo)插入表單中?
worksheet.insert_chart('A7',?chart)??
workbook.close()?

微信公眾號(hào)二維碼：