openpyxl 모듈 활용 연습3

파이썬 프로그래밍으로 지루한 작업 자동화하기 학습중..

openpyxl모듈은 교재의 내용과 최신 release된 문서와의 내용 차이가 커서 더 이상 진행하기 어려웠습니다.  https://openpyxl.readthedocs.org의 문서를 참고하는게 더 좋을 것 같습니다.

## 엑셀 문서 만들고 저장하기

import openpyxl, os
wb = openpyxl.Workbook()
print(wb.get_sheet_names())
sheet = wb.active
print(sheet.title)
sheet.title = 'SOSAGE BEAN ERR Sheet'
print(wb.get_sheet_names())

wb = openpyxl.load_workbook('example.xlsx')
sheet = wb.active
sheet.title = 'Sosage Sosage Sosage'
wb.save('example_copy.xlsx')

wb = openpyxl.Workbook()
print(wb.get_sheet_names())
wb.create_sheet()
print(wb.get_sheet_names())
wb.create_sheet(index = 0, title = 'FIRST SHEET')
print(wb.get_sheet_names())
wb.create_sheet(index = 0, title = 'middle SHEET')
print(wb.get_sheet_names())
wb.remove_sheet(wb.get_sheet_by_name('middle SHEET'))
wb.remove_sheet(wb.get_sheet_by_name('Sheet1'))
print(wb.get_sheet_names())

## 셀에 값을 쓰기
wb = openpyxl.Workbook()
print(wb.get_sheet_names())
sheet = wb.get_sheet_by_name('Sheet')
sheet['A1'] = 'Hello Python!'
print(sheet['A1'].value)

wb = openpyxl.load_workbook(('produceSales.xlsx'))
sheet = wb.get_sheet_by_name('Sheet')

## 업데이트할 정보의 데이터 구조 정하기
import openpyxl

wb = openpyxl.load_workbook('produceSales.xlsx')
sheet = wb.get_sheet_by_name('Sheet')
PRICE_UPDATE = {'Garlic' : 3.07, 'Cellery' : 1.19, 'Lemon' : 1.27}
for rowNum in range(2, sheet.max_row) :
    produceName = sheet.cell(row = rowNum, column = 1).value
    # print(produceName)
    if produceName in PRICE_UPDATE :
        sheet.cell(row = rowNum, column = 2).value = PRICE_UPDATE[produceName]
        # print(produceName)
wb.save('updateProduceSales.xlsx')

## 수식
import openpyxl

wb = openpyxl.Workbook()
sheet = wb.active
sheet['A1'] = 200
sheet['A2'] = 300
sheet['A3'] = '=SUM(A1:A2)'
wb.save('writeFormula.xlsx')

## 행과 열을 조절하기
import openpyxl
wb = openpyxl.Workbook()
sheet = wb.active
sheet['A1'] = 'Tall row'
sheet['B2'] = 'Wide column'
sheet.row_dimensions[1].height = 70
sheet.column_dimensions['B'].width = 20
wb.save('dimension.xlsx')

## 셀 병합 및 분할

import openpyxl

wb = openpyxl.Workbook()
sheet = wb.active
sheet.merge_cells('A1:D3')
sheet['A1'] = 'Twelve cells merged together'
sheet.merge_cells('C5:D5')
sheet['C5'] = 'Two merged cells'
wb.save('merged.xlsx')

import openpyxl

wb = openpyxl.load_workbook('merged.xlsx')
sheet = wb.active
sheet.unmerge_cells('A1:D3')
sheet.unmerge_cells('C5:D5')
wb.save('unmerged.xlsx')

# 틀고정
# sheet.freeze_panes = 'A2' 1행
# sheet.freeze_panes = 'B1' A열
# sheet.freeze_panes = 'C1' A열 B열
# sheet.freeze_panes = 'C2' 1행과 A열 B열
# sheet.freeze_panes = 'A1' 틀 고정 없음