山内セミナーⅠ(2021/05/12)

関連サイトと資料

サンプルプログラム(4)

test10.py
import openpyxl
wb = openpyxl.load_workbook("山手線駅名.xlsx")
  
ws = wb['山手線駅名']
for y in range(1, 30+1):
    name = ws.cell(row=y, column=1).value
    print(name)
    remove_ws = wb[name]
    wb.remove(remove_ws)
  
wb.save("山手線駅名.xlsx")
    

test11.py
import openpyxl
  
wb = openpyxl.load_workbook('tec2_38.xlsx')
ws = wb['東京']
   
rng1 = ws['A1']
print(rng1)
   
rng2 = ws.cell(4, 1)
print(rng2)
    

test12.py
from pprint import pprint
import openpyxl
  
wb = openpyxl.load_workbook('tec2_38.xlsx')
ws = wb['東京']
rng = ws['A4:G8']
pprint(rng, width=20)
    

test13.py
import openpyxl
  
wb = openpyxl.load_workbook('tec2_38.xlsx')
ws = wb['東京']
rng = ws['A1']
print(rng.value)
    

test14.py
import openpyxl
  
wb = openpyxl.load_workbook('tec2_38.xlsx')
ws = wb['東京']
  
rng1 = ws['A1']
rng1.value = 123
  
print(rng1.value)
rng2 = ws.cell(4, 1, 'abc')
   
print(rng2.value)
    

test15.py
import openpyxl
  
wb = openpyxl.load_workbook('tec2_38.xlsx')
ws = wb['東京']
  
print('min_row={0}, min_column={1}, max_row={2}, max_column={3}'.format(ws.min_row, ws.min_column, ws.max_row, ws.max_column))
    

test16.py
from pprint import pprint
import openpyxl
  
wb = openpyxl.load_workbook('tec2_38.xlsx')
ws = wb['東京']
  
print("iter_cols")
[pprint(rng, width=20) for rng in 
 ws.iter_cols(min_col=ws.min_column, max_col=ws.max_column, min_row=ws.min_row, max_row=ws.max_row)]
  
print("iter_rows")
[pprint(rng, width=20) for rng in 
 ws.iter_rows(min_col=ws.min_column, max_col=ws.max_column, min_row=ws.min_row, max_row=ws.max_row)]
    

test17.py
import openpyxl
  
wb = openpyxl.load_workbook('tec2_38.xlsx')
ws = wb['東京']
  
#1行目に挿入
ws.insert_rows(1)
#1行目に3行挿入
ws.insert_rows(1, 3)
#1行目を削除
#ws.delete_rows(1)
#1行目から3行削除
#ws.delete_rows(1, 3)
   
wb.save('tec2_38_insert_del_rows.xlsx')
    

test18.py
import openpyxl
  
wb = openpyxl.load_workbook('tec2_38.xlsx')
ws = wb['東京']
  
#1列目に挿入
ws.insert_cols(1)
#1列目に3列挿入
ws.insert_cols(1, 3)
#1列目を削除
#ws.delete_cols(1)
#1列目から3列削除
#ws.delete_cols(1, 3)
   
wb.save('tec2_38_insert_del_cols.xlsx')
    

サンプルプログラム(5)

test19.py
import openpyxl
  
wb = openpyxl.load_workbook('test.xlsx')
ws = wb['test']
  
for y in range(2,10+1):
    ws.cell(y, 5).number_format = '0.000'
    ws.cell(y, 2).number_format = '#,##0'
   
wb.save('test_format.xlsx')
    

test20.py
import openpyxl
from openpyxl.styles import Font
  
wb = openpyxl.load_workbook('山手線駅名.xlsx')
ws = wb['山手線駅名']
  
font = Font(name="MS Pゴシック", bold=True, size=14)
  
for y in range(1, 5+1):
    ws.cell(y, 1).font = font
  
wb.save('山手線駅名_font.xlsx')
    

test21.py
import openpyxl
from openpyxl.styles.borders import Border, Side
  
wb = openpyxl.load_workbook('山手線駅名.xlsx')
ws = wb['山手線駅名']
  
#細線、色は自動
side = Side(style="thin")
border = Border(top=side, bottom=side, left=side, right=side)
ws.cell(1, 1).border = border
  
#普通線、色は赤
side = Side(style="medium", color="FF0000")
border = Border(top=side, bottom=side, left=side, right=side)
ws.cell(2, 1).border = border
  
wb.save('山手線駅名_罫線.xlsx')
    

test22.py
import openpyxl
from openpyxl.styles.borders import Border, Side
from openpyxl import utils
  
wb = openpyxl.load_workbook('tec2_38.xlsx')
ws = wb['東京']
  
ws.row_dimensions[1].height = 30 #ポイント
ws.column_dimensions["B"].width = 20 #文字数
ws.column_dimensions[utils.get_column_letter(3)].width = 30 #文字数
  
wb.save('tec2_38_wh.xlsx')