openpyxlに関するコード集です。コピペして使ってください。
ブックのオープンやセルの取得
ブックのオープン
Python
# Import the required libraries.
import openpyxl
# Load the Excel workbook from the specified path
wb = openpyxl.load_workbook(filename = "/content/drive/MyDrive/Excel_Files/売上管理.xlsx")
# Check by printing
print(wb)シートの取得 方法その１ シート名で取得
Python
# Import the required libraries.
import openpyxl
# Load the Excel workbook from the specified path
wb = openpyxl.load_workbook(filename = "/content/drive/MyDrive/Excel_Files/売上管理.xlsx")
# Access the worksheet named "売上" from the loaded workbook
sh = wb["売上"]
# Check by printing
print(sh)シートの取得 方法その2 「n枚目」で取得
Python
# Import the required libraries.
import openpyxl
# Load the Excel workbook from the specified path
wb = openpyxl.load_workbook(filename = "/content/drive/MyDrive/Excel_Files/売上管理.xlsx")
# Get the worksheet from the number
sh = wb.worksheets[0]
# Check by printing
print(sh)セルの取得 方法その１ 「A1」で指定
Python
# Import the openpyxl library
import openpyxl
# Load the Excel workbook from the specified path
wb = openpyxl.load_workbook(filename = "/content/drive/MyDrive/Excel_Files/売上管理.xlsx")
# Get the worksheet from the sheet name
sh = wb["売上"]
# Access the cell at address "A1" in the "売上" worksheet
cell = sh["A1"]
# Check by printing
print(cell)セルの取得 方法その2 「1,1」で指定
Python
# Import the required libraries.
import openpyxl
# Load the Excel workbook from the specified path
wb = openpyxl.load_workbook(filename = "/content/drive/MyDrive/Excel_Files/売上管理.xlsx")
# Get the worksheet from the sheet name
sh = wb["売上"]
# Access the cell by (1,1)
cell = sh.cell(1,1)
# Check by printing
print(cell)セルの範囲取得
これには別解は無いようです。cells = sh[(1,6):(6,23)] と書いてもよい気がしますがエラーになります。
Python
# Import the required libraries.
import openpyxl
# Load the Excel workbook from the specified path
wb = openpyxl.load_workbook(filename = "/content/drive/MyDrive/Excel_Files/売上管理.xlsx")
# Get the worksheet from the sheet name
sh = wb["売上"]
# Access the cell by ("A1:B1") style ※This is a tuple
cells = sh["A6:F23"]
# Check by printing
for row_cells in cells:
print(row_cells)取得したセルの操作(二次元タプルの操作)
取得したセルは二次元タプルになります。
フィルター
Python
# Import the required libraries.
import openpyxl
# Load the Excel workbook from the specified path
wb = openpyxl.load_workbook(filename = "/content/drive/MyDrive/Excel_Files/売上管理.xlsx")
# Get the worksheet from the sheet name
sh = wb["売上"]
# Access the cell by ("A1:B1") style ※This is a tuple
cells = sh["A6:F23"]
# Specify target cell
target_cell = sh["B3"]
# filltering cells by target_cell
new_cells = list(filter(lambda x:x[1].value == target_cell.value, cells))
# Check by printing
for row_cells in new_cells:
print(row_cells)セルを範囲取得すると、二次元タプルになっています。これをエクセルのテーブルのフィルターのように扱うには下記のようにします。少しややこしいので、解説は別記事にしています。
取得したセル範囲の列削除
Python
# Import the required libraries.
import openpyxl
# Load the Excel workbook from the specified path
wb = openpyxl.load_workbook(filename = "/content/drive/MyDrive/Excel_Files/売上管理.xlsx")
# Get the worksheet from the sheet name
sh = wb["売上"]
# Access the cell by ("A1:B1") style ※This is a tuple
cells = sh["A6:F23"]
# Delete the column ※Spacyfy the Column to "if i != 1"
new_cells = [tuple(x for i, x in enumerate(t) if i != 1) for t in cells]
# Check by printing
for row_cells in new_cells:
print(row_cells)セル操作
セルに今日の日付を挿入
Python
# Import the required libraries.
import openpyxl
from datetime import datetime
#Specified the excel file path
xl_file = "/content/drive/MyDrive/Excel_Files/売上管理.xlsx"
# Load the Excel workbook from the specified path
wb = openpyxl.load_workbook(filename = xl_file)
# Get the worksheet from the sheet name
sh = wb["請求書"]
# Get today in serial number
today = datetime.now()
excel_date_offset = datetime(1899,12,30)
delta = today - excel_date_offset
# Insert day into cell
sh["E3"] = float(delta.days)
# save
wb.save(filename = xl_file)セルの転記
Python
# Import the required libraries.
import openpyxl
#Specified the excel file path
xl_file = "/content/drive/MyDrive/Excel_Files/売上管理.xlsx"
# Load the Excel workbook from the specified path
wb = openpyxl.load_workbook(filename = xl_file)
# Get the worksheet from the sheet name
sh = wb["売上"]
# Access the cell by ("A1:B1") style ※This is a tuple
cells = sh["A6:E10"]
# Specify target work sheet
result_sh = wb["請求書"]
# Copying cell value
for i,row in enumerate(cells):
for j,cell in enumerate(row):
result_sh.cell(7+i, 1+j).value = cell.value
# save
wb.save(filename = xl_file)セルの値削除(VBA：ClearContents)
Python
# Import the required libraries.
import openpyxl
#Specified the excel file path
xl_file = "/content/drive/MyDrive/Excel_Files/売上管理.xlsx"
# Load the Excel workbook from the specified path
wb = openpyxl.load_workbook(filename = xl_file)
# Specify target work sheet
result_sh = wb["請求書"]
# Clear contents
for row in result_sh["A7:E13"]:
for cell in row:
cell.value = None
# save
wb.save(filename = xl_file)
コメント