openpyxl 读写Excel Workbook

http://blog.csdn.net/hxsyewlink/article/details/6265790


注意，截止2016-02-01 openpyxl仍不支持读取表格和图像，所以如果打开又以同样的名称保存，表格和图像将会丢失。


（不过似乎支持插入图像）


workbook in memory
from openpyxl import Workbook 
wb = Workbook()


ws = wb.active
create worksheet


ws1 = wb.create_sheet() # insert at the end (default) 
or 
ws2 = wb.create_sheet(0) # insert at first position
wb.get_sheet_names()


with data:
(1) for one cell


c=ws[‘A4’]


c = ws.cell(‘A4’)


单元格格式


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 h:mm:ss’


set percentage using a string followed by the percent sign


ws[‘B1’] = ‘3.14%’


ws[‘B1’].value 
0.031400000000000004


ws[‘B1’].number_format 
‘0%’
编辑公式 
ws[“A1”] = “=SUM(1, 1)”


merge cells


ws.merge_cells(‘A1:B1’) 
ws.unmerge_cells(‘A1:B1’)
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)
折叠列