'''
python d:/ez/python/src/excel.py

https://note.nkmk.me/python-openpyxl-usage/
'''


import openpyxl
import pprint

wb = openpyxl.load_workbook('D:/OneDrive/ez/media/excel/QRcode.xlsm')
sheet = wb['BC']
cell = sheet['A1']
print(cell.value)



wb = openpyxl.load_workbook('D:/OneDrive/ez/media/excel/test.xlsx')

'''
####################################################################################
# Excelファイルの読み込み

print(type(wb))
# <class 'openpyxl.workbook.workbook.Workbook'>

print(wb.sheetnames)
# ['sheet1', 'sheet2']

sheet = wb['Sheet1']

print(type(sheet))
# <class 'openpyxl.worksheet.worksheet.Worksheet'>

cell = sheet['A2']

print(type(cell))
# <class 'openpyxl.cell.cell.Cell'>

print(cell.value)
# かきくけこ

cell = sheet.cell(row=2, column=1)

print(type(cell))
# <class 'openpyxl.cell.cell.Cell'>

print(cell.value)
# かきくけこ

pprint.pprint(sheet['A2:C4'])
# ((<Cell 'sheet1'.A2>, <Cell 'sheet1'.B2>, <Cell 'sheet1'.C2>),
#  (<Cell 'sheet1'.A3>, <Cell 'sheet1'.B3>, <Cell 'sheet1'.C3>),
#  (<Cell 'sheet1'.A4>, <Cell 'sheet1'.B4>, <Cell 'sheet1'.C4>))

g = sheet.iter_rows(min_row=2, max_row=4, min_col=1, max_col=3)

print(type(g))
# <class 'generator'>

pprint.pprint(list(g))
# [(<Cell 'sheet1'.A2>, <Cell 'sheet1'.B2>, <Cell 'sheet1'.C2>),
#  (<Cell 'sheet1'.A3>, <Cell 'sheet1'.B3>, <Cell 'sheet1'.C3>),
#  (<Cell 'sheet1'.A4>, <Cell 'sheet1'.B4>, <Cell 'sheet1'.C4>)]

def get_value_list(t_2d):
    return([[cell.value for cell in row] for row in t_2d])

l_2d = get_value_list(sheet['A1:C2'])

pprint.pprint(l_2d, width=40)
# [['あいうえお', 'アイウエオ', None],
#  ['かきくけこ', None, None]]

def get_list_2d(sheet, start_row, end_row, start_col, end_col):
    return get_value_list(sheet.iter_rows(min_row=start_row,
                                          max_row=end_row,
                                          min_col=start_col,
                                          max_col=end_col))

l_2d = get_list_2d(sheet, 2, 4, 1, 3)

pprint.pprint(l_2d, width=40)
# [['one', 11.0, 12.0],
#  ['two', 21.0, 22.0],
#  ['three', 31.0, 32.0]]

g_all = sheet.values

print(type(g_all))
# <class 'generator'>

pprint.pprint(list(g_all), width=40)
# [(None, 'A', 'B', 'C'),
#  ('one', 11.0, 12.0, 13.0),
#  ('two', 21.0, 22.0, 23.0),
#  ('three', 31.0, 32.0, 33.0)]
'''

'''
####################################################################################
# セルの編集・追記

sheet = wb['Sheet2']

sheet['C1'] = 'XXX'
sheet['E1'] = 'new'

pprint.pprint(list(sheet.values), width=40)
# [(None, 'A', 'XXX', 'C', 'new'),
#  ('one', 11.0, 12.0, 13.0, None),
#  ('two', 21.0, 22.0, 23.0, None),
#  ('three', 31.0, 32.0, 33.0, None)]
'''

sheet = wb['テスト']
sheet['A1'] = '勉強会'
print(sheet['A1'].value)

wb.save('D:/OneDrive/ez/media/excel/test.xlsx')