《用Python處理Excel表格》下篇來啦！

身為工作黨或?qū)W生黨的你，平日里肯定少不了與Excel表格打交道的機(jī)會。當(dāng)你用Excel處理較多數(shù)據(jù)時，還在使用最原始的人工操作嗎？現(xiàn)在教你如何用Python處理Excel，從此處理表格再也不加班，時間縮短數(shù)十倍！

上篇我們進(jìn)行了一些事前準(zhǔn)備，目的是用Python提取Excel表中的數(shù)據(jù)。而這一篇便是在獲取數(shù)據(jù)的基礎(chǔ)上，對Excel表格的實操處理。

操作

創(chuàng)建新的excel

第9行代碼用來指定創(chuàng)建的excel的活動表的名字：·不寫第9行，默認(rèn)創(chuàng)建sheet·寫了第9行，創(chuàng)建指定名字的sheet表

import osimport openpyxlpath = r”C:UsersasukaDesktop”os.chdir(path) # 修改工作路徑workbook = openpyxl.Workbook()sheet = workbook.activesheet.title = ‘1號sheet’workbook.save(‘1.xlsx’)

修改單元格、excel另存為

第9行代碼，通過給單元格重新賦值，來修改單元格的值第9行代碼的另一種寫法sheet[‘B1’].value = ‘age’第10行代碼，保存時如果使用原來的（第7行）名字，就直接保存；如果使用了別的名字，就會另存為一個新文件

import osimport openpyxlpath = r”C:UsersasukaDesktop”os.chdir(path) # 修改工作路徑workbook = openpyxl.load_workbook(‘test.xlsx’) # 返回一個workbook數(shù)據(jù)類型的值sheet = workbook.active # 獲取活動表sheet[‘A1’] = ‘name’workbook.save(‘test.xlsx’)

添加數(shù)據(jù)

插入有效數(shù)據(jù)使用append()方法，在原來數(shù)據(jù)的后面，按行插入數(shù)據(jù)

import osimport openpyxlpath = r”C:UsersasukaDesktop”os.chdir(path) # 修改工作路徑workbook = openpyxl.load_workbook(‘test.xlsx’) # 返回一個workbook數(shù)據(jù)類型的值sheet = workbook.active # 獲取活動表print(‘當(dāng)前活動表是：’ + str(sheet))data = [ [‘素子’,23], [‘巴特’,24], [‘塔奇克馬’,2]]for row in data: sheet.append(row) # 使用append插入數(shù)據(jù)workbook.save(‘test.xlsx’)

插入空行空列

·insert_rows(idx=數(shù)字編號, amount=要插入的行數(shù))，插入的行數(shù)是在idx行數(shù)的下方插入·insert_cols(idx=數(shù)字編號, amount=要插入的列數(shù))，插入的位置是在idx列數(shù)的左側(cè)插入

import osimport openpyxlpath = r”C:UsersasukaDesktop”os.chdir(path) # 修改工作路徑workbook = openpyxl.load_workbook(‘test.xlsx’) # 返回一個workbook數(shù)據(jù)類型的值sheet = workbook.active # 獲取活動表print(‘當(dāng)前活動表是：’ + str(sheet))sheet.insert_rows(idx=3, amount=2)sheet.insert_cols(idx=2, amount=1)workbook.save(‘test.xlsx’)

刪除行、列

·delete_rows(idx=數(shù)字編號, amount=要刪除的行數(shù))·delete_cols(idx=數(shù)字編號, amount=要刪除的列數(shù))

import osimport openpyxlpath = r”C:UsersasukaDesktop”os.chdir(path) # 修改工作路徑workbook = openpyxl.load_workbook(‘test.xlsx’) # 返回一個workbook數(shù)據(jù)類型的值sheet = workbook.active # 獲取活動表print(‘當(dāng)前活動表是：’ + str(sheet))sheet.delete_rows(idx=10) # 刪除第10行sheet.delete_cols(idx=1, amount=2) # 刪除第1列，及往右共2列workbook.save(‘test.xlsx’)

移動指定區(qū)間的單元格(move_range)

move_range(“數(shù)據(jù)區(qū)域”,rows=,cols=)：正整數(shù)為向下或向右、負(fù)整數(shù)為向左或向上

import osimport openpyxlpath = r”C:UsersasukaDesktop”os.chdir(path) # 修改工作路徑workbook = openpyxl.load_workbook(‘test.xlsx’) # 返回一個workbook數(shù)據(jù)類型的值sheet = workbook.active # 獲取活動表print(‘當(dāng)前活動表是：’ + str(sheet))sheet.move_range(‘D11:F12’,rows=0,cols=-3) # 移動D11到F12構(gòu)成的矩形格子workbook.save(‘test.xlsx’)

字母列號與數(shù)字列號之間的轉(zhuǎn)換核心代碼

from openpyxl.utils import get_column_letter, column_index_from_string# 根據(jù)列的數(shù)字返回字母print(get_column_letter(2)) # B# 根據(jù)字母返回列的數(shù)字print(column_index_from_string(‘D’)) # 4

舉個例子：

import osimport openpyxlfrom openpyxl.utils import get_column_letter, column_index_from_stringpath = r”C:UsersasukaDesktop”os.chdir(path) # 修改工作路徑workbook = openpyxl.load_workbook(‘2.xlsx’) # 返回一個workbook數(shù)據(jù)類型的值sheet = workbook.active # 獲取活動表print(‘當(dāng)前活動表是：’ + str(sheet))# 根據(jù)列的數(shù)字返回字母print(get_column_letter(2)) # B# 根據(jù)字母返回列的數(shù)字print(column_index_from_string(‘D’)) # 4

字體樣式查看字體樣式

import osimport openpyxlimport openpyxl.stylespath = r”C:UsersasukaDesktop”os.chdir(path) # 修改工作路徑workbook = openpyxl.load_workbook(‘test.xlsx’) # 返回一個workbook數(shù)據(jù)類型的值sheet = workbook.active # 獲取活動表print(‘當(dāng)前活動表是：’+str(sheet))cell = sheet[‘A1’]font = cell.fontprint(‘當(dāng)前單元格的字體樣式是’)print(font.name, font.size, font.bold, font.italic, font.color)”’當(dāng)前活動表是：當(dāng)前單元格的字體樣式是等線 11.0 False False Parameters:rgb=None, indexed=None, auto=None, theme=1, tint=0.0, type=’theme””

修改字體樣式

openpyxl.styles.Font(name=字體名稱,size=字體大小,bold=是否加粗,italic=是否斜體,color=字體顏色)其中，字體顏色中的color是RGB的16進(jìn)制表示

import osimport openpyxlimport openpyxl.stylespath = r”C:UsersasukaDesktop”os.chdir(path) # 修改工作路徑workbook = openpyxl.load_workbook(‘test.xlsx’) # 返回一個workbook數(shù)據(jù)類型的值sheet = workbook.active # 獲取活動表print(sheet)cell = sheet[‘A1’]cell.font = openpyxl.styles.Font(name=”微軟雅黑”, size=20, bold=True, italic=True, color=”FF0000″)workbook.save(‘test.xlsx’)

再者，可以使用for循環(huán)，修改多行多列的數(shù)據(jù)，在這里介紹了獲取的方法

import osimport openpyxlimport openpyxl.stylespath = r”C:UsersasukaDesktop”os.chdir(path) # 修改工作路徑workbook = openpyxl.load_workbook(‘test.xlsx’) # 返回一個workbook數(shù)據(jù)類型的值sheet = workbook.active # 獲取活動表print(sheet)cell = sheet[‘A’]for i in cell: i.font = openpyxl.styles.Font(name=”微軟雅黑”, size=20, bold=True, italic=True, color=”FF0000″)workbook.save(‘test.xlsx’)

設(shè)置對齊格式

Alignment(horizontal=水平對齊模式,vertical=垂直對齊模式,text_rotation=旋轉(zhuǎn)角度,wrap_text=是否自動換行)水平對齊：‘distributed’，‘justify’，‘center’，‘left’， ‘centerContinuous’，’right，‘general’垂直對齊：‘bottom’，‘distributed’，‘justify’，‘center’，‘top’

import osimport openpyxl.stylespath = r”C:UsersasukaDesktop”os.chdir(path) # 修改工作路徑workbook = openpyxl.load_workbook(‘test.xlsx’) # 返回一個workbook數(shù)據(jù)類型的值sheet = workbook.active # 獲取活動表print(‘當(dāng)前活動表是：’ + str(sheet))cell = sheet[‘A1’]alignment = openpyxl.styles.Alignment(horizontal=”center”, vertical=”center”, text_rotation=0, wrap_text=True)cell.alignment = alignmentworkbook.save(‘test.xlsx’)

當(dāng)然，你仍舊可以調(diào)用for循環(huán)來實現(xiàn)對多行多列的操作

import osimport openpyxl.stylespath = r”C:UsersasukaDesktop”os.chdir(path) # 修改工作路徑workbook = openpyxl.load_workbook(‘test.xlsx’) # 返回一個workbook數(shù)據(jù)類型的值sheet = workbook.active # 獲取活動表print(‘當(dāng)前活動表是：’ + str(sheet))cell = sheet[‘A’]alignment = openpyxl.styles.Alignment(horizontal=”center”, vertical=”center”, text_rotation=0, wrap_text=True)for i in cell: i.alignment = alignment workbook.save(‘test.xlsx’)

設(shè)置行高列寬

設(shè)置行列的寬高：·row_dimensions[行編號].height = 行高·column_dimensions[列編號].width = 列寬

import osimport openpyxlimport openpyxl.stylespath = r”C:UsersasukaDesktop”os.chdir(path) # 修改工作路徑workbook = openpyxl.load_workbook(‘test.xlsx’) # 返回一個workbook數(shù)據(jù)類型的值sheet = workbook.active # 獲取活動表print(‘當(dāng)前活動表是：’ + str(sheet))# 設(shè)置第1行的高度sheet.row_dimensions[1].height = 50# 設(shè)置B列的卷度sheet.column_dimensions[‘B’].width = 20workbook.save(‘test.xlsx’)

設(shè)置所有單元格（顯示的結(jié)果是設(shè)置所有，有數(shù)據(jù)的單元格的）

from openpyxl import load_workbookfrom openpyxl.utils import get_column_letterimport osos.chdir(r’C:UsersasukaDesktop’)workbook = load_workbook(‘1.xlsx’)print(workbook.sheetnames) # 打印所有的sheet表ws = workbook[workbook.sheetnames[0]] # 選中最左側(cè)的sheet表width = 2.0 # 設(shè)置寬度height = width * (2.2862 / 0.3612) # 設(shè)置高度print(“row:”, ws.max_row, “column:”, ws.max_column) # 打印行數(shù)，列數(shù)for i in range(1, ws.max_row + 1): ws.row_dimensions[i].height = heightfor i in range(1, ws.max_column + 1): ws.column_dimensions[get_column_letter(i)].width = widthworkbook.save(‘test.xlsx’)

合并、拆分單元格

合并單元格有下面兩種方法，需要注意的是，如果要合并的格子中有數(shù)據(jù)，即便python沒有報錯，Excel打開的時候也會報錯。merge_cells(待合并的格子編號)merge_cells(start_row=起始行號，start_column=起始列號，end_row=結(jié)束行號，end_column=結(jié)束列號)

import osimport openpyxlimport openpyxl.stylespath = r”C:UsersasukaDesktop”os.chdir(path) # 修改工作路徑workbook = openpyxl.load_workbook(‘test.xlsx’) # 返回一個workbook數(shù)據(jù)類型的值sheet = workbook.active # 獲取活動表print(‘當(dāng)前活動表是：’ + str(sheet))# 方法1：sheet.merge_cells(‘A12:B13’)# 方法2：sheet.merge_cells(start_row=12, start_column=3, end_row=13, end_column=4)# 加一個居中對齊cell = sheet[‘A12’]alignment = openpyxl.styles.Alignment(horizontal=”center”, vertical=”center”, text_rotation=0, wrap_text=True)cell.alignment = alignmentcell = sheet[‘C12’]alignment = openpyxl.styles.Alignment(horizontal=”center”, vertical=”center”, text_rotation=0, wrap_text=True)cell.alignment = alignmentworkbook.save(‘test.xlsx’)

拆分單元格的方法同上unmerge_cells(待合并的格子編號)unmerge_cells(start_row=起始行號，start_column=起始列號，end_row=結(jié)束行號，end_column=結(jié)束列號)

sheet表創(chuàng)建新的sheet(create_sheet)

create_sheet(“新的sheet名”)：創(chuàng)建一個新的sheet表

import osimport openpyxlpath = r”C:UsersasukaDesktop”os.chdir(path) # 修改工作路徑workbook = openpyxl.load_workbook(‘test.xlsx’) # 返回一個workbook數(shù)據(jù)類型的值sheet = workbook.active # 獲取活動表print(‘當(dāng)前活動表是：’ + str(sheet))workbook.create_sheet(‘3號sheet’) # 創(chuàng)建新的sheet表print(workbook.sheetnames) # 查看所有的sheet表workbook.save(‘test.xlsx’)”’當(dāng)前活動表是：[‘Sheet1’, ‘Sheet2’, ‘3號sheet’]”’

修改sheet名字(title)

第11行，使用title修改sheet表的名字

import osimport openpyxlpath = r”C:UsersasukaDesktop”os.chdir(path) # 修改工作路徑workbook = openpyxl.load_workbook(‘test.xlsx’) # 返回一個workbook數(shù)據(jù)類型的值sheet = workbook.active # 獲取活動表print(‘當(dāng)前活動表是：’ + str(sheet))sheet.title = ‘1號sheet’ # 修改sheet表workbook.save(‘test.xlsx’)

復(fù)制sheet表(copy_worksheet)

在“操作”>“修改單元格、excel另存為”中提到了另存為，其實復(fù)制sheet表就是一個另存為的過程，你要是在12行代碼保存的時候使用第7行的文件名，那么復(fù)制的sheet表就保存到自己身上，內(nèi)容跟copy.xlsx一樣。

import osimport openpyxlpath = r”C:UsersasukaDesktop”os.chdir(path) # 修改工作路徑workbook = openpyxl.load_workbook(‘test.xlsx’) # 返回一個workbook數(shù)據(jù)類型的值sheet = workbook.active # 獲取活動表print(‘當(dāng)前活動表是：’ + str(sheet))workbook.copy_worksheet(sheet)# 復(fù)制sheet表workbook.save(‘copy.xlsx’)

刪除sheet表(remove)

remove(“sheet名”)：刪除某個sheet表要刪除某sheet表，需要激活這個sheet表，即：將其作為活動表（關(guān)于活動表的定義請看前面文章開頭寫的有）下面8~11行代碼展示了原始活動表與手動更換活動表，第13行代碼刪掉活動表

import osimport openpyxlpath = r”C:UsersasukaDesktop”os.chdir(path) # 修改工作路徑workbook = openpyxl.load_workbook(‘test.xlsx’) # 返回一個workbook數(shù)據(jù)類型的值sheet = workbook.active # 獲取活動表print(‘當(dāng)前活動表是：’ + str(sheet))sheet = workbook[‘3號sheet’] # 手動切換到要刪除的sheet表，一旦切換，這張表就是活動表print(‘當(dāng)前活動表是：’ + str(sheet))workbook.remove(sheet) # 刪除當(dāng)前活動表print(workbook.sheetnames)workbook.save(‘test.xlsx’)”’當(dāng)前活動表是：當(dāng)前活動表是：[‘Sheet1’, ‘Sheet2′]”’

操作多個Excel表

背景知識numpy與pandasNumPy是 Python 語言的一個擴(kuò)展程序庫，支持大量的維度數(shù)組與矩陣運算，此外也針對數(shù)組運算提供大量的數(shù)學(xué)函數(shù)庫；pandas 是基于NumPy 的一種工具，該工具是為解決數(shù)據(jù)分析任務(wù)而創(chuàng)建的，我們需要利用Pandas進(jìn)行Excel的合并1.下面的代碼生成了一個5行3列的包含15個字符的嵌套列表（注意，第4行代碼：15是等于35的，如果是15對應(yīng)43，或者16對應(yīng)5*3都會報錯）（注意，第5行代碼，雖然5行3列是15個數(shù)據(jù)，但是可以指定數(shù)據(jù)從1開頭，到16結(jié)束）

import numpy as npimport pandas as pdxx = np.arange(15).reshape(5, 3)yy = np.arange(1, 16).reshape(5, 3)print(xx)print(yy)”'[[ 0 1 2] [ 3 4 5] [ 6 7 8] [ 9 10 11] [12 13 14]][[ 1 2 3] [ 4 5 6] [ 7 8 9] [10 11 12] [13 14 15]]”’

2.添加表頭使用pandas庫的DataFrame來添加表頭。關(guān)于打印的結(jié)果，把最左側(cè)的一列去掉之后會發(fā)現(xiàn)結(jié)果很和諧，這是因為最左側(cè)的一列代表行號。此時xx變量的類型是

import numpy as npimport pandas as pdxx = np.arange(15).reshape(5, 3)yy = np.arange(1, 16).reshape(5, 3)xx = pd.DataFrame(xx, columns=[“語文”, “數(shù)學(xué)”, “外語”])yy = pd.DataFrame(yy, columns=[“語文”, “數(shù)學(xué)”, “外語”])print(xx)print(yy)”’結(jié)果： 語文 數(shù)學(xué) 外語0 0 1 21 3 4 52 6 7 83 9 10 114 12 13 14 語文 數(shù)學(xué) 外語0 1 2 31 4 5 62 7 8 93 10 11 124 13 14 15”’

xlsxwriter

xlsxwriter模塊一般是和xlrd模塊搭配使用的，xlsxwriter：負(fù)責(zé)寫入數(shù)據(jù)，xlrd：負(fù)責(zé)讀取數(shù)據(jù)。1.創(chuàng)建一個工作簿

import xlsxwriterimport ospath = r”C:UsersasukaDesktop”os.chdir(path)# 這一步相當(dāng)于創(chuàng)建了一個新的”工作簿”；# “demo.xlsx”文件不存在，表示新建”工作簿”；# “demo.xlsx”文件存在，表示新建”工作簿”覆蓋原有的”工作簿”；workbook = xlsxwriter.Workbook(“demo.xlsx”)# close是將”工作簿”保存關(guān)閉，這一步必須有，否則創(chuàng)建的文件無法顯示出來。workbook.close()

2.創(chuàng)建sheet表

import xlsxwriterimport ospath = r”C:UsersasukaDesktop”os.chdir(path)workbook = xlsxwriter.Workbook(“cc.xlsx”) # 創(chuàng)建一個名為cc.xlsx的文件worksheet = workbook.add_worksheet(“2018年銷售量”) # 創(chuàng)建一個名為“2018年銷售量”的sheet表workbook.close()

3.寫入數(shù)據(jù)

import xlsxwriterimport ospath = r”C:UsersasukaDesktop”os.chdir(path)# 創(chuàng)建一個名為【demo.xlsx】工作簿；workbook = xlsxwriter.Workbook(“demo.xlsx”)# 創(chuàng)建一個名為【2018年銷售量】工作表；worksheet = workbook.add_worksheet(“2018年銷售量”)# 使用write_row方法，為【2018年銷售量】工作表，添加一個表頭；headings = [‘產(chǎn)品’, ‘銷量’, “單價”]worksheet.write_row(‘A1’, headings)# 使用write方法，在【2018年銷售量】工作表中插入一條數(shù)據(jù)；# write語法格式：worksheet.write(行,列,數(shù)據(jù))data = [“蘋果”, 500, 8.9]for i in range(len(headings)): worksheet.write(1, i, data[i])workbook.close()