엑셀 읽고 쓰기
openpyxl - 읽기/쓰기
장점 : 문서화도 잘되어있고, 예제 위주로 따라하다 보면 익힐 수 있음.
단점 : 너무 많은 메모리를 사용.
모듈 설치하기
pip install openpyxl
or
easy_install openpyxl
Write a workbook
>>> from openpyxl import Workbook
>>> from openpyxl.compat import range
>>> from openpyxl.cell import get_column_letter
>>>
>>> wb = Workbook()
>>>
>>> dest_filename = 'empty_book.xlsx'
>>>
>>> ws = wb.active
>>>
>>> ws.title = "range names"
>>>
>>> for col_idx in range(1, 40):
... col = get_column_letter(col_idx)
... for row in range(1, 600):
... ws.cell('%s%s'%(col, row)).value = '%s%s' % (col, row)
>>>
>>> ws = wb.create_sheet()
>>>
>>> ws.title = 'Pi'
>>>
>>> ws['F5'] = 3.14
>>>
>>> wb.save(filename = dest_filename)
Read an existing workbook
>>> from openpyxl import load_workbook
>>> wb = load_workbook(filename = 'empty_book.xlsx')
>>> sheet_ranges = wb['range names']
>>> print(sheet_ranges['D18'].value)
Using number formats
>>> import datetime
>>> from openpyxl import Workbook
>>> wb = Workbook(guess_types=True)
>>> ws = wb.active
>>> # set date using a Python datetime
>>> ws['A1'] = datetime.datetime(2010, 7, 21)
>>>
>>> ws['A1'].number_format
'yyyy-mm-dd'
>>>
>>> # set percentage using a string followed by the percent sign
>>> ws['B1'] = '3.14%'
>>>
>>> ws['B1'].value
0.031400000000000004
>>>
>>> ws['B1'].number_format
'0%'
Using formular
>>> from openpyxl import Workbook
>>> wb = Workbook()
>>> ws = wb.active
>>> # add a simple formula
>>> ws["A1"] = "=SUM(1, 1)"
>>> wb.save("formula.xlsx")
Merge / Unmerge cells
>>> from openpyxl.workbook import Workbook
>>>
>>> wb = Workbook()
>>> ws = wb.active
>>>
>>> ws.merge_cells('A1:B1')
>>> ws.unmerge_cells('A1:B1')
>>>
>>> # or
>>> ws.merge_cells(start_row=2,start_column=1,end_row=2,end_column=4)
>>> ws.unmerge_cells(start_row=2,start_column=1,end_row=2,end_column=4)