Python 使用openpyxl處理Excel文件詳情
前言
安裝openpyxl模塊:
pip install openpyxl
導入模塊:
import openpyxl
官方文檔:
1. Excel窗口
- 工作簿(workbook):Excel的文件
- 工作表(worksheet):一個工作簿由多個工作表組成
- 列(column):工作表的列名為A、B、C等的大寫字母
- 行(row):工作表的行名稱為1、2、3等的數字
- 單元格(cell):工作表中的每個格子稱為單元格,用(列名,行名)表示
2. 讀取Excel文件
# author:mlnt # createdate:2022/8/16 import openpyxl from openpyxl.utils import get_column_letter, column_index_from_string # 1.打開文件 # 使用openpyxl.load_workbook()方法打開Excel文件 filename = 'data.xlsx' work_book = openpyxl.load_workbook(filename=filename) # 加載Excel文件 # 2.獲取工作表名稱 """ - Excel文件對象.sheetnames:獲取工作簿文件的所有工作表,以列表數據類型返回 - Excel文件對象.active:獲取當前工作表的名稱 """ # 獲取所有工作表的名稱 work_sheets = work_book.sheetnames print(f'工作表列表:{work_sheets}') # 工作表列表:['Sheet1', 'Sheet2', 'Sheet3'] # 獲取當前工作表的名稱 current_sheet = work_book.active print(f'當前工作表:{current_sheet}') # 當前工作表:<Worksheet "Sheet1"> # 獲取當前工作表的內容 title = current_sheet.title print(f'當前工作表標題:{title}') # 當前工作表標題:Sheet1 # 3.切換工作表 work_sheet = work_book['Sheet2'] # 返回名稱相應的工作表 print(f'當前工作表:{work_sheet.title}') # 當前工作表:Sheet2 work_sheet = work_book['Sheet1'] # 返回名稱相應的工作表 print(f'當前工作表:{work_sheet.title}') # 當前工作表:Sheet1 # 4.獲取工作表的內容 print(f'單元格A1: {work_sheet["A1"].value}') print(f'單元格B1: {work_sheet["B1"].value}') print(f'單元格C1: {work_sheet["C1"].value}') print(f'單元格D1: {work_sheet["D1"].value}') print(f'單元格E1: {work_sheet["E1"].value}') print(f'單元格F1: {work_sheet["F1"].value}') # 單元格A1: 姓名 # 單元格B1: 字 # 單元格C1: 號 # 單元格D1: 所處時代 # 單元格E1: 別稱 # 單元格F1: 代表作 # 獲取單元格相對位置信息 # column:列,row:行,coordinate:坐標 print(f'單元格A1: {work_sheet["A1"].column}, {work_sheet["A1"].row}, {work_sheet["A1"].coordinate}') print(f'單元格B1: {work_sheet["B1"].column}, {work_sheet["B1"].row}, {work_sheet["B1"].coordinate}') print(f'單元格C1: {work_sheet["C1"].column}, {work_sheet["C1"].row}, {work_sheet["C1"].coordinate}') print(f'單元格D1: {work_sheet["D1"].column}, {work_sheet["D1"].row}, {work_sheet["D1"].coordinate}') print(f'單元格E1: {work_sheet["E1"].column}, {work_sheet["E1"].row}, {work_sheet["E1"].coordinate}') print(f'單元格F1: {work_sheet["F1"].column}, {work_sheet["F1"].row}, {work_sheet["F1"].coordinate}') # 單元格A1: 1, 1, A1 # 單元格B1: 2, 1, B1 # 單元格C1: 3, 1, C1 # 單元格D1: 4, 1, D1 # 單元格E1: 5, 1, E1 # 單元格F1: 6, 1, F1 # 5.獲取工作表內容的列數和行數 print(f'工作表列數:{work_sheet.max_column}') print(f'工作表行數:{work_sheet.max_row}') # 工作表列數:6 # 工作表行數:20 # 6.獲取單元格內容 # cell(column=n, row=m) for j in range(1, work_sheet.max_row + 1): for i in range(1, work_sheet.max_column + 1): print(work_sheet.cell(column=i, row=j).value, end=' ') print() # 7.工作表對象的rows和columns """ 創建工作表對象成功後,會自動產生數據產生器(generators): rows: 工作表數據產生器以行方式包裹,每一行用一個Tuple包裹; columns:工作表數據產生器以列方式包裹,每一列用一個Tuple包裹。 """ print(type(work_sheet.rows)) # <class 'generator'> print(type(work_sheet.columns)) # <class 'generator'> for cell in list(work_sheet.columns)[0]: print(cell.value) for cell in list(work_sheet.rows)[1]: print(cell.value, end=' ') # 逐行遍歷 print('逐行遍歷開始...') for row in work_sheet.rows: for cell in row: print(cell.value, end=' ') print() print('逐行遍歷結束...') # 逐列遍歷 print('逐列遍歷開始...') for column in work_sheet.columns: for cell in column: print(cell.value, end=' ') print() print('逐列遍歷結束...') # 8.用整數取代域名 """ get_column_letter(數值):將數值轉成字母 column_index_from_string(字母):將字母轉成數值 """ print(f'列數:{get_column_letter(work_sheet.max_column)}') print(f"3 --> {get_column_letter(3)}") print(f"26 --> {get_column_letter(26)}") print(f"39 --> {get_column_letter(39)}") print(f"46 --> {get_column_letter(46)}") print(f"120 --> {get_column_letter(120)}") # 列數:F # 3 --> C # 26 --> Z # 39 --> AM # 46 --> AT # 120 --> DP print(f"A --> {column_index_from_string('A')}") print(f"F --> {column_index_from_string('F')}") print(f"AB --> {column_index_from_string('AB')}") print(f"BBC --> {column_index_from_string('BBC')}") print(f"CNN --> {column_index_from_string('CNN')}") # A --> 1 # F --> 6 # AB --> 28 # BBC --> 1407 # CNN --> 2406 # 9.切片 # 使用切片的概念讀取某區間數據 # 逐行讀取 for row in work_sheet['A3':'F4']: for cell in row: print(cell.value, end=' ') print() # 白居易 樂天 香山居士 唐朝 詩魔、詩王 《長恨歌》、《賣炭翁》、《琵琶行》 # 杜甫 子美 少陵野老 唐朝 詩聖 《春望》、《茅屋為秋風所破歌》、《登高》、《望嶽》
data.xlsx:
3. 寫入Excel文件
import openpyxl # 1.創建空白工作簿 work_book = openpyxl.Workbook() # 2.保存Excel文件 work_book.save('new_workbook.xlsx')
4. 復制Excel文件
import openpyxl filename = 'data.xlsx' work_book = openpyxl.load_workbook(filename=filename) # 開啟工作簿 backup_name = filename[:filename.find('.xlsx')] + '-backup.xlsx' work_book.save(backup_name)
效果:
5. 創建工作表
# author:mlnt # createdate:2022/8/16 import openpyxl # 1.創建空白工作簿 work_book = openpyxl.Workbook() print(f'工作表列表:{work_book.sheetnames}') # 工作表列表:['Sheet'] # 2.創建新的工作表 work_book.create_sheet() print(f'工作表列表:{work_book.sheetnames}') # 工作表列表:['Sheet', 'Sheet1'] work_sheet = work_book.active # 獲取當前工作表 print(f'當前工作表:{work_sheet.title}') # 當前工作表:Sheet """ 在創建工作表時,預設的工作表名稱為”SheetN“,N為數字編號,以遞增方式顯示; 新建的工作表放在工作表列的最右邊。 可以通過在create_sheet()中添加參數title和index設置新工作表的名稱及位置(工作表位置從0開始) """ work_book.create_sheet(index=0, title='工作表1') work_book.create_sheet(index=2, title='工作表3') print(f'工作表列表:{work_book.sheetnames}') # 工作表列表:['工作表1', 'Sheet', '工作表3', 'Sheet1'] # 3.刪除工作表 # 刪除”工作表3“ work_book.remove(work_book['工作表3']) print(f'工作表列表:{work_book.sheetnames}') # 工作表列表:['工作表1', 'Sheet', 'Sheet1'] # 刪除”Sheet“ del work_book['Sheet'] print(f'工作表列表:{work_book.sheetnames}') # 工作表列表:['工作表1', 'Sheet1'] # 4.寫入單元格 work_sheet = work_book.active # 獲取當前工作表 print(f'當前工作表:{work_sheet.title}') # 當前工作表:Sheet rows = [ ['姓名', '年齡', '聯系方式', '學歷'], ['張三', '18', '18888886666', '大專'], ['王二狗', '28', '18888888888', '研究生'], ['茍恭芝', '38', '18888889999', '博士'], ['李華', '20', '18888887777', '本科'], ['曹亠強', '18', '18888883333', '大專'] ] for row in rows: work_sheet.append(row) # 保存Excel文件 work_book.save('my_workbook.xlsx')
6. 設置單元格字體及顏色
# author:mlnt # createdate:2022/8/16 import openpyxl from openpyxl.styles import Font wb = openpyxl.Workbook() # 創建空白工作簿 ws = wb.active # 獲得當前工作表 """ bold:加粗,值為True時表示粗體 italic:斜體,值為True時設置斜體 strike:刪除線,值為True時設置刪除線 name:字體名稱,如:Arial size:字號 color:字體顏色,color='FFFFFF' """ fontTitle1 = Font(name='微軟雅黑', size=24) ws['A1'].font = fontTitle1 ws['A1'] = '勿謂言之不預' fontTitle2 = Font(name='楷體', size=18, bold=True) ws['A2'].font = fontTitle2 ws['A2'] = '山不在高,有仙則名' # 設置字體及顏色 # RGB顏色對照表:https://www.917118.com/tool/color_3.html fontTitle3 = Font(name='Arial', size=20, italic=True, color='00FF7F') ws['A3'].font = fontTitle3 ws['A3'] = 'The early bird catches the worm.' # 保存Excel文件 wb.save('設置單元格字體.xlsx')
效果:
7. 數學公式的使用
常用的數學公式:
- SUM():總和,如:SUM(A1:A3)
- AVERAGE():平均值,如:AVERAGE(A1:A3)
- MAX():最大值,如:MAX(A1:A3)
- MIN():最小值,如:MIN(A1:A3)
import openpyxl wb = openpyxl.Workbook() # 創建空白工作簿 ws = wb.active # 獲得當前工作表 rows = [ ['學號', '姓名', '語文', '數學', '英語', '物理', '化學', '生物', '總分'], ['1001', '張三', 90, 98, 106, 80, 85, 78, '=SUM(C2:H2)'], ['1002', 'Tom', 93, 100, 96, 84, 75, 68, '=SUM(C3:H3)'], ['1003', 'Jack', 89, 80, 108, 70, 65, 88, '=SUM(C4:H4)'], ['1004', 'Mary', 110, 88, 88, 68, 68, 64, '=SUM(C5:H5)'], ['1005', 'Jane', 98, 78, 86, 56, 95, 72, '=SUM(C6:H6)'] ] for row in rows: # 將數據添加到工作表 ws.append(row) ws['B7'] = '總分' ws['C7'] = '=SUM(C2:C6)' ws['D7'] = '=SUM(D2:D6)' ws['E7'] = '=SUM(E2:E6)' ws['F7'] = '=SUM(F2:F6)' ws['G7'] = '=SUM(G2:G6)' ws['H7'] = '=SUM(H2:H6)' ws['B8'] = '平均分' ws['C8'] = '=AVERAGE(C2:C6)' ws['D8'] = '=AVERAGE(D2:D6)' ws['E8'] = '=AVERAGE(E2:E6)' ws['F8'] = '=AVERAGE(F2:F6)' ws['G8'] = '=AVERAGE(G2:G6)' ws['H8'] = '=AVERAGE(H2:H6)' ws['B9'] = '最高分' ws['C9'] = '=MAX(C2:C6)' ws['D9'] = '=MAX(D2:D6)' ws['E9'] = '=MAX(E2:E6)' ws['F9'] = '=MAX(F2:F6)' ws['G9'] = '=MAX(G2:G6)' ws['H9'] = '=MAX(H2:H6)' ws['B10'] = '最低分' ws['C10'] = '=MIN(C2:C6)' ws['D10'] = '=MIN(D2:D6)' ws['E10'] = '=MIN(E2:E6)' ws['F10'] = '=MIN(F2:F6)' ws['G10'] = '=MIN(G2:G6)' ws['H10'] = '=MIN(H2:H6)' wb.save('數學公式的使用.xlsx')
效果:
8. 設置單元格寬高
單元格預設的高度為12.75pt,72pt等於1英寸,使用column_dimensions屬性可以設置行高;單元格默認寬度為8.43個英文字符寬度,可使用row_dimensions設置單元格的寬度如果將寬度或高度設置為0,則具有隱藏單元格的效果。
import openpyxl wb = openpyxl.Workbook() # 創建空白工作簿 ws = wb.active # 獲得當前工作表 ws['A1'] = '海內存知己' ws['A2'] = '天涯若比鄰' ws['B2'] = 'Hello world' ws.row_dimensions[1].height = 30 # 設置高度為30pt ws.column_dimensions['B'].width = 30 # 設置寬度為30個英文字符寬 wb.save('設置單元格寬高.xlsx')
效果:
9. 設置單元格對齊方式
使用Alignment()方法,需設置2個參數:
horizontal(水平方向):
- left:靠左
- right: 靠右
- center: 居中
vertical(垂直方向):
- top:靠上
- center:居中
- bottom:靠下
import openpyxl from openpyxl.styles import Alignment wb = openpyxl.Workbook() # 創建空白工作簿 ws = wb.active # 獲得當前工作表 ws['A1'] = '測試1' ws['B1'] = '測試2' ws['C1'] = '測試3' ws.row_dimensions[1].height = 30 # 設置高度為40pt ws.column_dimensions['B'].width = 20 # 設置寬度為20個字符寬 ws['A1'].alignment = Alignment(horizontal='left', vertical='top') # 居左靠上 ws['B1'].alignment = Alignment(horizontal='center', vertical='center') # 水平居中,垂直居中 ws['C1'].alignment = Alignment(horizontal='right', vertical='bottom') # 靠右居下 # 保存excel文件 wb.save('設置單元格對齊方式.xlsx')
效果:
10. 合並與取消單元格合並
合並單元格:
使用merge_cells()合並單元格,可以合並同一行(row)、同一列(column)或一個區域的單元格
取消合並單元格:
unmerge_cells()
# author:mlnt # createdate:2022/8/16 import openpyxl from openpyxl.styles import Alignment wb = openpyxl.Workbook() # 創建空白工作簿 ws = wb.active # 獲得當前工作表 """ 1.合並單元格 使用merge_cells()合並單元格,可以合並同一行(row)、同一列(column)或一個區域的單元格 """ ws['A1'] = '早起的鳥兒有蟲吃' ws['A2'] = 'The early bird catches the worm.' ws['A3'] = '人生如戲' ws['C4'] = 'Where there is a will there is a way.' ws.merge_cells('A1:D1') # 合並A1:D1單元格 ws.merge_cells('A3:A8') # 合並A3:A8單元格 ws.merge_cells('C4:G6') # 合並C4:G6單元格 ws['A1'].alignment = Alignment(horizontal='center') ws['A3'].alignment = Alignment(vertical='center') ws['C3'].alignment = Alignment(horizontal='center', vertical='center') # 2.取消合並單元格 # unmerge_cells() ws.unmerge_cells('A3:A8') # 取消合並A3:A8單元格 wb.save('合並與取消單元格合並.xlsx')
效果:
11. 創建圖表
11.1 柱狀圖
# author:mlnt # createdate:2022/8/16 """ BarChart:柱狀圖 BarChart3D:3D柱狀圖 PieChart:餅圖 PieChart:3D餅圖 BubleChart:泡泡圖 AreaChart:分區圖 AreaChart3D:3D分區圖 LineChart:折線圖 LineChart3D:3D折線圖 RedarChart:雷達圖 StockChart:股票圖 """ import openpyxl from openpyxl.chart import BarChart, Reference wb = openpyxl.Workbook() # 創建空白工作簿 ws = wb.active # 獲得當前工作表 rows = [ ['學號', '姓名', '語文', '數學', '英語', '物理', '化學', '生物'], ['1001', '張三', 90, 98, 106, 80, 85, 78], ['1002', 'Tom', 93, 100, 96, 84, 75, 68], ['1003', 'Jack', 89, 80, 108, 70, 65, 88], ['1004', 'Mary', 110, 88, 88, 68, 68, 64], ['1005', 'Jane', 98, 78, 86, 56, 95, 72] ] for row in rows: # 將數據添加到工作表 ws.append(row) chart = BarChart() # 直方圖 chart.title = '2022某班某小組學生成績表' # 圖表標題 chart.y_axis.title = '分數' # y軸標題 chart.x_axis.title = '學員' # x軸標題 data = Reference(ws, min_col=3, max_col=8, min_row=1, max_row=6) # 圖表數據 chart.add_data(data, titles_from_data=True) # 建立圖表 x_title = Reference(ws, min_col=2, min_row=2, max_row=6) # x軸標記名稱 chart.set_categories(x_title) # 設置x軸標記名稱 ws.add_chart(chart, 'J1') # 放置圖標位置 wb.save('柱狀圖.xlsx')
效果:
11.2 餅圖
# author:mlnt # createdate:2022/8/16 import openpyxl from openpyxl.chart import PieChart, Reference wb = openpyxl.Workbook() # 創建空白工作簿 ws = wb.active # 獲得當前工作表 rows = [ ['科目', '分數'], ['語文', 90], ['數學', 98], ['英語', 106], ['物理', 80], ['化學', 85], ['生物', 78] ] for row in rows: ws.append(row) chart = PieChart() # 餅圖 chart.title = '某學員成績分析表' data = Reference(ws, min_col=2, min_row=1, max_row=7) # 圖表數據 chart.add_data(data, titles_from_data=True) # 建立圖表 labels = Reference(ws, min_col=1, min_row=2, max_row=7) # 標簽名稱 chart.set_categories(labels) # 設置標簽名稱 ws.add_chart(chart, 'D1') wb.save('餅圖.xlsx')
效果:
到此這篇關於Python 使用openpyxl處理Excel文件詳情的文章就介紹到這瞭,更多相關Python 處理Excel文件 內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!
推薦閱讀:
- Python 操作 Excel 之 openpyxl 模塊
- 詳解Python操作Excel之openpyxl
- python之openpyxl模塊的安裝和基本用法(excel管理)
- Python Excel處理庫openpyxl詳解
- python使用openpyxl庫讀寫Excel表格的方法(增刪改查操作)