python操作excel的方法

from openpyxl import load_workbook
wb2 = load_workbook('test.xlsx')
print wb2.get_sheet_names()
# ['Sheet2', 'New Title', 'Sheet1']

from openpyxl import Workbook
wb = Workbook()
workbook创建后至少至少要创建一个worksheet。使用 openpyxl.workbook.Workbook.active()
ws = wb.active

ws1 = wb.create_sheet("new_sheet_1") # 新建sheet,插入到最后(默认)
ws2 = wb.create_sheet("new_sheet_2", 0) # 插入到最开始的位置

ws3 = wb.create_sheet()

ws3.title = "new_sheet_3"

print wb.get_sheet_names()
print(wb.sheetnames)

for sheet in wb:
print(sheet.title)

ws3.sheet_properties.tabColor = "1072BA"

ws3 = wb["new_sheet_3"]

wb._active_sheet_index = 1 # 获取第二个sheet

source = wb.active
target = wb.copy_worksheet(source)

c = ws['A3'] # 访问单元格,不存在则会自动创建一个
print c.value
ws['A4'] = 'a4 value' # 指定单元格的值
print ws['A4'].value
d = ws.cell(row=4, column=2, value='通过cell指定') # 通过row column数字指定
print d.value

for i in range(1, 10):
for j in range(1, 10):
ws.cell(row=i, column=j)

cell_range = ws['A1':'C2']

print ws['C']
print ws['C:D']
print ws[10]
print ws[5:10]

for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):
for c in row:
print(c)
iter_cols() 指定列->列，截止行
for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):
for c in row:
print(c)

ws['C9'] = 'hello world'
tuple(ws.rows) # 转化成tuple方便for in操作
tuple(ws.columns)

wb = Workbook()
wb.save("assets/sample.xlsx")

wb = load_workbook('document.xlsx')
wb.template = True
wb.save('document_template.xltx')

wb = load_workbook('document_template.xltx')
wb.template = False
wb.save('document.xlsx', as_template=False)

wb = load\_workbook\('assets/sample.xlsx'\)
ws = wb.active
ws\["A5"\] = "=SUM\(2, 1\)"
wb.save\("assets/sample.xlsx"\)