###### tags: `python` # Excel 檔案讀寫 ## 安裝 1. 開始/cmd 2. pip install openpyxl ## 參考資料 * [openpyxl官方網站](https://openpyxl.readthedocs.io/en/default/) ## 儲存excel檔案 ```python= from openpyxl import Workbook workbook = Workbook() # 實作一個excel物件 sheet = workbook.active # 將目前工作表指到sheet變數 sheet["A1"] = "hello" # 在A1儲存格放入指定內容 sheet["B1"] = "world!" workbook.save(filename="hello_world.xlsx") # 儲存檔案 ``` ## 讀取excel中工作表的名稱 先建立一個excel檔(.xlsx)，修改工作表名稱 ```python= from openpyxl import load_workbook workbook = load_workbook(filename="hello_world.xlsx") sheetnames = workbook.sheetnames # 取得所有工作表物件 print(sheetnames) sheet = workbook.active print(sheet) print(sheet.title) # 取得工作表名稱 print(sheet["A1"]) # 取得該儲存格物件 print(sheet["A1"].value) # 取得該儲存格的內容 print(sheet.cell(row=1, column=2)) # 取得該儲存格物件 print(sheet.cell(row=1, column=2).value) # 取得該儲存格的內容 ``` ## 開檔模式 唯讀模式：讀取大檔 ```python= from openpyxl import load_workbook workbook = load_workbook(filename="hello_world.xlsx", read_only=True) sheet = workbook.active sheet["A1"].value = 'test' workbook.save(filename="hello_world.xlsx") ``` 資料模式：讀取公式計算結果 ```python= from openpyxl import load_workbook workbook = load_workbook(filename="hello_world.xlsx", data_only=True) sheet = workbook.active print(sheet["A1"].value) print(sheet["B1"].value) ``` ## 讀取某一範圍的資料 ```python= from openpyxl import load_workbook workbook = load_workbook(filename="hello_world.xlsx", data_only=True) sheet = workbook.active # 讀取某一範圍 rangearea = sheet["A1:C2"] print(rangearea, '\n') for row in rangearea: for cell in row: print(cell, cell.value) # 讀取整欄 rangearea = sheet["A"] # 取得該欄所有有資料的內容 print(rangearea, '\n') for cell in rangearea: print(cell, cell.value) # 讀取幾欄 rangearea = sheet["A:B"] print(rangearea, '\n') for row in rangearea: for cell in row: print(cell, cell.value) # 讀取整列 rangearea = sheet[1] # 取得該列所有有資料的內容 print(rangearea, '\n') for cell in rangearea: print(cell, cell.value) # 讀取幾列 rangearea = sheet["1:2"] print(rangearea, '\n') for row in rangearea: for cell in row: print(cell, cell.value) ``` ## 循列讀取 ```python= from openpyxl import load_workbook workbook = load_workbook(filename="hello_world.xlsx", data_only=True) sheet = workbook.active for row in sheet.iter_rows(min_row=1, max_row=2, min_col=1, max_col=3): for cell in row: print(cell, cell.value) ``` ## 循欄讀取 ```python= from openpyxl import load_workbook workbook = load_workbook(filename="hello_world.xlsx", data_only=True) sheet = workbook.active for row in sheet.iter_cols(min_row=1, max_row=2, min_col=1, max_col=3): for cell in row: print(cell, cell.value) ``` ## 循欄讀取值 ```python= from openpyxl import load_workbook workbook = load_workbook(filename="hello_world.xlsx", data_only=True) sheet = workbook.active for row in sheet.iter_cols(min_row=1, max_row=2, min_col=1, max_col=3, values_only=True): for cell in row: print(cell) ``` ## 循列/循欄讀取所有資料 ```python= from openpyxl import load_workbook workbook = load_workbook(filename="hello_world.xlsx", data_only=True) sheet = workbook.active for row in sheet.rows: # 循列讀取所有資料 for cell in row: print(cell, cell.value) for row in sheet.columns: # 循列讀取所有資料 for cell in row: print(cell, cell.value) ``` ## 建立工作表、命名工作表、寫入儲存格、儲存檔案 ```python= from openpyxl import Workbook from openpyxl.compat import range from openpyxl.utils import get_column_letter wb = Workbook() # 開啟一個空白的excel檔，以wb代表它 ws1 = wb.active # 把使用中的工作表以ws1代表 ws1.title = "range names" # 將工作表命名為range names for row in range(1, 40): # 執行39次迴圈 ws1.append(range(600)) # 每次塞入599筆資料到該工作表 ws2 = wb.create_sheet(title="Pi") # 新增一個工作表叫Pi ws2['F5'] = 3.14 # 將這個工作表第F5位置塞入3.14資料 ws3 = wb.create_sheet(title="Data") data = [ ['Apples', 10000, 5000, 8000, 6000], ['Pears', 2000, 3000, 4000, 5000], ['Bananas', 6000, 6000, 6500, 6000], ['Oranges', 500, 300, 200, 700], ] # 建立一個叫data的二維陣列，內容如上 ws3.append(["Fruit", "2011", "2012", "2013", "2014"]) # 在工作表3塞入一列（一個陣列） for row in data: ws3.append(row) # 依序讀取data中每筆一維陣列，並塞入第3工作表 print(ws3['A10'].value) # 讀取並列印工作表3中A10位置的內容 wb.save(filename = 'sample2.xlsx') # 將以上變更寫入sample2.xlsx ``` ### 參考資料 * [Openpyxl tutorial](http://zetcode.com/articles/openpyxl/) * [Openpyxl Example](https://cran.r-project.org/web/packages/openxlsx/vignettes/formatting.pdf) * [A Guide to Excel Spreadsheets in Python With openpyxl](https://realpython.com/openpyxl-excel-spreadsheets-python/) * [A Guide to Excel Spreadsheets in Python With openpyxl](https://realpython.com/openpyxl-excel-spreadsheets-python/#before-you-begin)