Python で xlsx の CRUD

openpyxl の使用例です。

1. 共通に使うライブラリー
xlsx_io.py

#
#	xlsx_io.py
#
#						Jan/20/2019
#
# -------------------------------------------------------------------
import sys
import os

from openpyxl import Workbook
from openpyxl import load_workbook
# -------------------------------------------------------------------
def xlsx_read_proc (xlsx_file):
	dict_aa = {}
	wb = load_workbook (filename = xlsx_file)
	ws = wb.active

	print (ws.max_row)
	max_row = ws.max_row
	for row in ws.rows:
		unit_aa = {}
		key = row[0].value
		unit_aa['name'] = row[1].value
		unit_aa['population'] = int (row[2].value)
		unit_aa['date_mod'] = row[3].value
		dict_aa[key] = unit_aa
#
	wb.close()
#
	return	dict_aa
# -------------------------------------------------------------------
def xlsx_write_proc (xlsx_file,dict_aa):
	wb = Workbook()
	ws = wb.worksheets[0]
	ws.title = "Cities"
	print ("ws.max_row = %d" % ws.max_row)
#
	keys_list = list(dict_aa.keys())
	print ("len = %d\n" % len(keys_list)) 
#
	for it in range (len(keys_list)):
		jt = it + 1
		key = keys_list[it]
		unit_aa = dict_aa[key]
#
		ws['A%d' % jt] = key
		ws['B%d' % jt] = unit_aa['name']
		ws['C%d' % jt] = unit_aa['population']
		ws['D%d' % jt] = unit_aa['date_mod']
#
	wb.save (filename = xlsx_file)
	wb.close()
#
	os.chmod (xlsx_file,0o766)
# -------------------------------------------------------------------

2. Create
xlsx_create.py

#!/usr/bin/python
#
#	xlsx_create.py
#
#						Jan/20/2019
#
# -------------------------------------------------------------------
import sys
from xlsx_io import xlsx_write_proc
#
# -------------------------------------------------------------------
def dict_append_proc(dict_aa,key,name,population,date_mod):
	dict_aa[key] = {'name':name,'population':population,'date_mod':date_mod}
#
	return dict_aa
#
# -------------------------------------------------------------------
def data_prepare_proc():
	dict_aa = {}
	dict_aa = dict_append_proc(dict_aa,"t2971","奈良",729845,"2003-6-12")
	dict_aa = dict_append_proc(dict_aa,"t2972","大和高田",615973,"2003-9-15")
	dict_aa = dict_append_proc(dict_aa,"t2973","大和郡山",849371,"2003-8-17")
	dict_aa = dict_append_proc(dict_aa,"t2974","天理",245639,"2003-2-8")
	dict_aa = dict_append_proc(dict_aa,"t2975","橿原",372681,"2003-3-9")
	dict_aa = dict_append_proc(dict_aa,"t2976","桜井",837592,"2003-6-26")
	dict_aa = dict_append_proc(dict_aa,"t2977","五條",924638,"2003-8-23")
	dict_aa = dict_append_proc(dict_aa,"t2978","御所",481765,"2003-10-20")
	dict_aa = dict_append_proc(dict_aa,"t2979","生駒",647329,"2003-12-9")
#
	return	dict_aa
# -------------------------------------------------------------------
sys.stderr.write("*** 開始 ***\n")

xlsx_file = sys.argv[1]
print(xlsx_file)
#
dict_aa = data_prepare_proc()
#
xlsx_write_proc(xlsx_file,dict_aa)
#
sys.stderr.write("*** 終了 ***\n")
# -------------------------------------------------------------------

使用例

./xlsx_create.py cities.xlsx

3. Read
xlsx_read.py

#!/usr/bin/python
#
#	xlsx_read.py
#
#						Jan/20/2018
#
# -------------------------------------------------------------------
import sys
from xlsx_io import xlsx_read_proc

# -------------------------------------------------------------------
def dict_display_proc(dict_aa):
	for key in sorted(dict_aa.keys()):
		unit = dict_aa[key]
		name = unit['name']
		str_out = str(key) +"\t"+ str(name)
		str_out += "\t" + str(unit['population'])
		str_out += "\t" + str(unit['date_mod'])
		print(str_out)
# -------------------------------------------------------------------
sys.stderr.write("*** 開始 ***\n")

xlsx_file = sys.argv[1]
print(xlsx_file)
#
dict_aa = xlsx_read_proc(xlsx_file)

dict_display_proc(dict_aa)

sys.stderr.write("*** 終了 ***\n")
# -------------------------------------------------------------------

使用例

./xlsx_read.py cities.xlsx

4. Update
xlsx_update.py

#! /usr/bin/python
#
#	xlsx_update.py
#
#					Jan/20/2019
import	sys
import	datetime
#
# ---------------------------------------------------------------
from xlsx_io import xlsx_read_proc
from xlsx_io import xlsx_write_proc
# ---------------------------------------------------------------
def dict_update_proc(dict_in,id,population):
	key = str(id)
	if key in dict_in:
		dict_in[key]['population'] = population
		date_mod = datetime.date.today()
		dict_in[key]['date_mod'] = '%s' % date_mod
#
	return	dict_in
# ---------------------------------------------------------------
#
sys.stderr.write("*** 開始 ***\n")
#
xlsx_file = sys.argv[1]
key = sys.argv[2]
population_in = int(sys.argv[3])
print("%s\t%d" % (key, population_in))

dict_aa = xlsx_read_proc(xlsx_file)

dict_bb = dict_update_proc(dict_aa,key,population_in)

xlsx_write_proc(xlsx_file,dict_bb)
#
sys.stderr.write("*** 終了 ***\n")
# ---------------------------------------------------------------

使用例

./xlsx_update.py cities.xlsx t2972 17592300

5. Delete
xlsx_delete.py

#! /usr/bin/python
#
#	xlsx_delete.py
#
#					Jan/20/2019
import	sys
#
# ---------------------------------------------------------------
from xlsx_io import xlsx_read_proc
from xlsx_io import xlsx_write_proc
# ---------------------------------------------------------------
def dict_delete_proc(dict_in,key):
	if key in dict_in:
		del dict_in[key]
#
	return	dict_in
#
# ---------------------------------------------------------------
#
sys.stderr.write("*** 開始 ***\n")
#
xlsx_file = sys.argv[1]
key = sys.argv[2]
print("%s" % key)

dict_aa = xlsx_read_proc(xlsx_file)

dict_bb = dict_delete_proc(dict_aa,key)

xlsx_write_proc(xlsx_file,dict_bb)
#
sys.stderr.write("*** 終了 ***\n")
# ---------------------------------------------------------------

使用例

./xlsx_delete.py cities.xlsx t2978

確認したバージョン

$ python
Python 3.12.3 (main, Apr 10 2024, 05:33:47) [GCC 13.2.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import openpyxl
>>> openpyxl.__version__
'3.1.2'