Python中操作excel有xlrd,xlwt,xlutils模塊,openpyxl模塊,xlsxwriter等模塊,這裡先介紹xlrd,xlwt,xlutils模塊。
xlrd是讀excel的庫,支持.xls,.xlsx文件的讀;
xlwt是寫excel的庫,支持寫.xls文件;
xlutils提供其他功能:如複製一份excel;
1:xlrd,xlwt,xlutils庫的安裝
pip install xlrd
pip install xlwt
pip install xlutils
2:查看模塊的幫助文檔
拿到一個新的模塊,還是首先看看它裡面的幫助文檔,以及這個模塊中所提供的方法。
<code># encoding=gbk import xlrd print(xlrd.__doc__) print(dir(xlrd)) for item in dir(xlrd): print(item) print(help(xlrd.open_workbook)) # import xlwt # 自己寫吧...../<code>
從上面的打印結果可以看到,xlrd除了能操作excel之外,還能操作zipfile文件,這裡只練習對excel的操作。
3:xlrd模塊中的常用方法
3.1:數據格式
xlrd所讀取的excel數據可能的格式有七種:
0 empty(空的)
1 text(string)
2 number,
3 date,
4 boolean,
5 error,
blank(空白表格)
3.2:獲取工作表
<code># encoding=gbk import xlrd # 打開Book工作簿(即excel工作簿,包含所有工作表) workBook = xlrd.open_workbook('test.xlsx') # 查看工作簿對象中所有的屬性與方法 #print(help(workBook)) # 工作表的總數 print(workBook.nsheets) # 獲取所有的工作表名,返回一個列表 print(workBook.sheet_names()) # 獲取工作表 # 獲取所有的工作表,返回一個列表 print(workBook.sheets()) # 遍歷所有的工作表 # for sheet in workBook.sheets(): # print(sheet) # 獲取某個工作表 # 方法1:通過索引順序獲取,注意下標不要越界,最大值為workBook.nsheets - 1 workSheet = workBook.sheets()[0] # 方法2:通過索引順序獲取, 最大值為workBook.nsheets - 1 # workSheet = workBook.sheet_by_index(0)) # 方法3: 通過工作表名稱獲取;所有的工作表名:通過 workBook.sheet_names() 獲取 # workSheet = workBook.sheet_by_name('Sheet1') # 以上三個函數都會返回一個xlrd.sheet.Sheet()對象 # # workBook.sheet_loaded(sheet_name or indx) # 檢查某個工作表是否導入完畢/<code>
3.3:行操作
<code># encoding=gbk import xlrd # 打開Book工作簿(即excel工作簿,包含所有工作表) workBook = xlrd.open_workbook('test.xlsx') # 獲取第一個工作表 workSheet = workBook.sheets()[0] # 查看工作表對象中的所有方法 # print(help(workSheet)) nrows = workSheet.nrows #獲取該工作表中的有效行數 print(nrows) # 獲取第n行,包括單元格數據類型與單元格數據值 print(workSheet.row(3) ) #返回由該行中所有的單元格對象組成的列表 #上面輸出: [empty:'', number:20201601.0, number:108.0, number:96.0, number:102.0, number:92.0, number:95.0, number:93.0, number:88.0, number:90.0, empty:''] # 獲取第n行,包括單元格數據類型與單元格數據值 print(workSheet.row_slice(3)) #返回由該列中所有的單元格對象組成的列表 #上面輸出:[empty:'', number:20201601.0, number:108.0, number:96.0, number:102.0, number:92.0, number:95.0, number:93.0, number:88.0, number:90.0, empty:''] # 獲取第n行單元格數據類型編號, print(workSheet.row_types(3, start_colx=0, end_colx=None) ) #返回由該行中所有單元格的數據類型組成的列表 #上面輸出:array('B', [0, 2, 2, 2, 2, 2, 2, 2, 2, 2, 0]) # 獲取第n行,單元格數據值 print(workSheet.row_values(3, start_colx=0, end_colx=None) ) #返回由該行中所有單元格的數據組成的列表 #上面輸出:['', 20201601.0, 108.0, 96.0, 102.0, 92.0, 95.0, 93.0, 88.0, 90.0, ''] print(workSheet.row_len(3)) #返回該行的有效單元格的長度/<code>
3.4:列操作
<code># encoding=gbk import xlrd # 打開Book工作簿(即excel工作簿,包含所有工作表) workBook = xlrd.open_workbook('test.xlsx') # 獲取第一個工作表 workSheet = workBook.sheets()[0] # 查看工作表對象中的所有方法 # print(help(workSheet)) #獲取工作表的有效列數 ncols = workSheet.ncols print(ncols) #返回由該列中所有的單元格對象組成的列表:類型+數據 print(workSheet.col(1, start_rowx=0, end_rowx=None)) #返回由該列中所有的單元格對象組成的列表:類型+數據 print(workSheet.col_slice(1, start_rowx=0, end_rowx=None)) #返回由該列中所有單元格的數據類型組成的列表:類型編號 print(workSheet.col_types(1, start_rowx=0, end_rowx=None)) #返回由該列中所有單元格的數據組成的列表:數據 print(workSheet.col_values(1, start_rowx=0, end_rowx=None)) /<code>
3.5:單元格操作
<code># encoding=gbk import xlrd # 打開Book工作簿(即excel工作簿,包含所有工作表) workBook = xlrd.open_workbook('test.xlsx') # formatting_info 默認為False # workBook = xlrd.open_workbook('test.xls',formatting_info=True) # formatting_info=True 格式化信息,只支持.xls文件 # 查看工作簿對象中所有的屬性與方法 # print(help(workBook)) # print(help(xlrd.open_workbook)) # 獲取第一個工作表 workSheet = workBook.sheets()[0] # 查看工作表對象中的所有方法 # print(help(workSheet)) #返回單元格對象 print(workSheet.cell(3,3)) print(workSheet.cell(3,3).value ) # 結果:number:96.0 # 結果:96.0 #返回單元格中的數據類型編號 print(workSheet.cell_type(3,3) ) # 結果 2 #返回單元格中的數據 print(workSheet.cell_value(3,3)) # 行,列 # 結果:96.0 # 單元格的格式化信息,需要formatting_info=True 才能用,即 xlrd.open_workbook('test.xls',formatting_info=True) # print(workSheet.cell_xf_index(3, 3)) # print(help(workSheet.cell_xf_index)) /<code>
4:xlwt模塊中的常用方法
4.1:寫入excel文件
<code># encoding=gbk import xlwt # 創建一個excel工作簿, 設置編碼為utf-8 workbook = xlwt.Workbook(encoding = 'utf-8') # 創建一個工作表,名稱為"測試sheet" worksheet = workbook.add_sheet('測試sheet',cell_overwrite_ok=True) # 寫入excel # 參數對應 行, 列, 值 worksheet.write(1,0, label = 'this is test') worksheet.write(1,0, '測試') # 給相同的單元格賦值:需要在調用workbook.add_sheet 函數時加上 cell_overwrite_ok=True worksheet.write(1,1, '999中國333') # 保存,如果文件存在,就會覆蓋。 workbook.save('xlwt_test1.xls') #只能保存為xls文件/<code>
結果:
4.2:設置字體樣式
<code># encoding=gbk import xlwt # 創建一個excel工作簿, 設置編碼為utf-8 workbook = xlwt.Workbook(encoding = 'utf-8') # 創建一個工作表,名稱為"測試sheet" worksheet = workbook.add_sheet('測試sheet',cell_overwrite_ok=True) style = xlwt.XFStyle() # 初始化樣式 # 設置字體 font = xlwt.Font() # 為樣式創建字體 font.name = 'Times New Roman' font.bold = True # 黑體 font.underline = True # 下劃線 font.italic = True # 斜體字 font.colour_index = 2 # 字體顏色 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow , almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray, the list goes on... style.font = font # 設定樣式 worksheet.write(1, 0, 'Unformatted value') # 不帶樣式的寫入 worksheet.write(2, 0, 'Formatted value', style) # 帶樣式的寫入/<code>
結果:
4.3:設置單元格寬度
<code># encoding=gbk import xlwt # 創建一個excel工作簿, 設置編碼為utf-8 workbook = xlwt.Workbook(encoding = 'utf-8') # 創建一個工作表,名稱為"測試sheet" worksheet = workbook.add_sheet('測試sheet',cell_overwrite_ok=True) # 設置單元格寬度 worksheet.col(0).width = 5000 worksheet.write(1, 0, '88888_ixusy88_1') worksheet.write(2, 1, '88888_ixusy88_2',) workbook.save('xlwt_test1.xls')/<code>
結果:
4.4:輸入一個日期到單元格
<code># encoding=gbk import xlwt import datetime # 創建一個excel工作簿, 設置編碼為utf-8 workbook = xlwt.Workbook(encoding = 'utf-8') # 創建一個工作表,名稱為"測試sheet" worksheet = workbook.add_sheet('測試sheet',cell_overwrite_ok=True) style = xlwt.XFStyle() style.num_format_str = 'YYYY-MM-DD h:mm:ss' # Other options: M/D/YY,D-MMM-YY, D-MMM, MMM-YY, h:mm, h:mm:ss, h:mm, h:mm:ss, M/D/YY h:mm, mm:ss, [h]:mm:ss, mm:ss.0 # 設置單元格寬度 worksheet.col(1).width = 5000 worksheet.write(1, 1, datetime.datetime.now(), style) workbook.save('xlwt_test1.xls')/<code>
4.5:向單元格添加一個超鏈接
<code># encoding=gbk import xlwt # 創建一個excel工作簿, 設置編碼為utf-8 workbook = xlwt.Workbook(encoding = 'utf-8') # 創建一個工作表,名稱為"測試sheet" worksheet = workbook.add_sheet('測試sheet',cell_overwrite_ok=True) # 設置單元格寬度 worksheet.col(1).width = 5000 worksheet.write(0, 0, xlwt.Formula('HYPERLINK("https://www.baidu.com/";"百度")')) workbook.save('xlwt_test1.xls')/<code>
4.6:向單元格添加一個公式
<code># encoding=gbk import xlwt # 創建一個excel工作簿, 設置編碼為utf-8 workbook = xlwt.Workbook(encoding = 'utf-8') # 創建一個工作表,名稱為"測試sheet" worksheet = workbook.add_sheet('測試sheet',cell_overwrite_ok=True) worksheet.write(1, 1, 15) worksheet.write(1, 2, 3) worksheet.write(1, 3, 4) worksheet.write(1, 4, 5) worksheet.write(1, 5, 6) worksheet.write(2, 1, xlwt.Formula('B2*C2')) worksheet.write(2, 2, xlwt.Formula('SUM(B2,C2)')) worksheet.write(2, 5, xlwt.Formula('SUM(B2:F2)')) workbook.save('xlwt_test1.xls')/<code>
結果:
4.7:設置單元格內容的對齊方式
<code># encoding=gbk import xlwt # 創建一個excel工作簿, 設置編碼為utf-8 workbook = xlwt.Workbook(encoding = 'utf-8') # 創建一個工作表,名稱為"測試sheet" worksheet = workbook.add_sheet('測試sheet',cell_overwrite_ok=True) alignment = xlwt.Alignment() # Create Alignment alignment.horz = xlwt.Alignment.HORZ_CENTER # May be: HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED, HORZ_JUSTIFIED, HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTED alignment.vert = xlwt.Alignment.VERT_CENTER # May be: VERT_TOP, VERT_CENTER, VERT_BOTTOM, VERT_JUSTIFIED, VERT_DISTRIBUTED style = xlwt.XFStyle() # Create Style style.alignment = alignment # Add Alignment to Style # 設置單元格寬度 worksheet.col(1).width = 6000 worksheet.write(1, 1, '中國', style) worksheet.write(1, 2, '中國') workbook.save('xlwt_test1.xls')/<code>
結果:
4.8:單元格添加邊框
<code># encoding=gbk import xlwt # 創建一個excel工作簿, 設置編碼為utf-8 workbook = xlwt.Workbook(encoding = 'utf-8') # 創建一個工作表,名稱為"測試sheet" worksheet = workbook.add_sheet('測試sheet',cell_overwrite_ok=True) borders = xlwt.Borders() # Create Borders borders.left = xlwt.Borders.DASHED # DASHED虛線 NO_LINE沒有 THIN實線 # May be: NO_LINE, THIN, MEDIUM, DASHED, DOTTED, THICK, DOUBLE, HAIR, MEDIUM_DASHED, THIN_DASH_DOTTED, MEDIUM_DASH_DOTTED, THIN_DASH_DOT_DOTTED, MEDIUM_DASH_DOT_DOTTED, SLANTED_MEDIUM_DASH_DOTTED, or 0x00 through 0x0D. borders.right = xlwt.Borders.DASHED borders.top = xlwt.Borders.DASHED borders.bottom = xlwt.Borders.DASHED borders.left_colour = 0x40 borders.right_colour = 0x40 borders.top_colour = 0x40 borders.bottom_colour = 0x40 style = xlwt.XFStyle() # Create Style style.borders = borders # Add Borders to Style # 設置單元格寬度 worksheet.col(1).width = 6000 worksheet.write(1, 1, 'ixusy88', style) workbook.save('xlwt_test1.xls')/<code>
結果:
4.9:單元格設置背景色
<code># encoding=gbk import xlwt # 創建一個excel工作簿, 設置編碼為utf-8 workbook = xlwt.Workbook(encoding = 'utf-8') # 創建一個工作表,名稱為"測試sheet" worksheet = workbook.add_sheet('測試sheet',cell_overwrite_ok=True) pattern = xlwt.Pattern() # Create the Pattern pattern.pattern = xlwt.Pattern.SOLID_PATTERN # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12 pattern.pattern_fore_colour = 5 # May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow , almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray, the list goes on... style = xlwt.XFStyle() # Create the Pattern style.pattern = pattern # Add Pattern to Style # 設置單元格寬度 worksheet.col(1).width = 6000 worksheet.write(1, 1, 'ixusy88', style) workbook.save('xlwt_test1.xls')/<code>
4.10:合併單元格(行和列合併)
<code># encoding=gbk import xlwt # 創建一個excel工作簿, 設置編碼為utf-8 workbook = xlwt.Workbook(encoding = 'utf-8') # 創建一個工作表,名稱為"測試sheet" worksheet = workbook.add_sheet('測試sheet',cell_overwrite_ok=True) # 設置單元格寬度 worksheet.col(1).width = 6000 # write_merge(self, r1, r2, c1, c2, label="", style=Style.default_style): 開始行,結束行,開始列,結束列 # 單元格(0,0) 到 單元格(0,3) 合併 worksheet.write_merge(0, 0, 0, 3, 'First Merge') font = xlwt.Font() # Create Font font.bold = True # Set font to Bold style = xlwt.XFStyle() # Create Style style.font = font # Add Bold Font to Style # write_merge(self, r1, r2, c1, c2, label="", style=Style.default_style): # 單元格(1,0) 到 單元格(2,3) 合併 worksheet.write_merge(1, 2, 0, 3, 'Second Merge', style) workbook.save('xlwt_test1.xls')/<code>
結果:
5:xlutils模塊
xlrd模塊提供讀操作,
xlwt模塊提供寫操作,但是無法複用excel格式。
xlutils模塊提供了複製一份excel的功能,代碼如下:
<code># encoding=gbk from xlutils.copy import copy import xlrd # 打開Book工作簿(即excel工作簿,包含所有工作表) workBook = xlrd.open_workbook('test.xlsx') #複製一份,將xlrd的對象轉化為xlwt的對象 new_workBook = copy(workBook) # 獲取第一個工作表 workSheet = new_workBook.get_sheet(0) # 在這裡對複製的那一份進行處理,操作與xlwt對象操作一樣 workSheet.write(1, 0, '中國') new_workBook.save('xlwt_test1_copy.xls') #只能保存為xls文件/<code>