엉뚱뽀짝

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

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' 틀 고정 없음

import openpyxl, pprint, os

wb = openpyxl.load_workbook('censuspopdata.xlsx')
sheet = wb.get_sheet_by_name('Population by Census Tract')
countryData = {}
print('Reading rows...')
for row in range(2, sheet.max_row + 1) :
    state = sheet['B' + str(row)].value
    county = sheet['C' + str(row)].value
    pop = sheet['D' + str(row)].value
    # print(state, county, pop)
    countryData.setdefault(state, {})
    countryData[state].setdefault(county, {'tracts' : 0, 'pop' : 0})
    countryData[state][county]['tracts'] += 1
    countryData[state][county]['pop'] += int(pop)
print(countryData['AK']['Anchorage']['tracts'])
print('Writing resuts...')
resultFile = open('census20.py', 'w')
resultFile.write('allData = ' + pprint.pformat(countryData))
resultFile.close()
print('Done')
import census20
os.chdir('C:\\Users\\yyiis\\Desktop')
print(census20.allData['AK']['Anchorage']['pop'])