xlrd excel读
import xlrd
from datetime import date, datetime
workbook = xlrd.open_workbook(r'F:\demo.xlsx')
workbook.sheet_names()
sheet2 = workbook.sheet_names()[1]
sheet2 = workbook.sheets()[1]
sheet2 = workbook.sheet_by_index(1)
sheet2 = workbook.sheet_by_name('sheet2')
print sheet2.name,sheet2.nrows,sheet2.ncols
rows = sheet2.row_values(3)
cols = sheet2.col_values(2)
print sheet2.cell(1,0).ctype
print sheet2.cell(1,0).value.encode('utf-8')
print sheet2.cell_value(1,0).encode('utf-8')
print sheet2.row(1)[0].value.encode('utf-8')
ctype : 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error
sheet2.cell(2,2).ctype
if (sheet.cell(row,col).ctype == 3):
date_value = xlrd.xldate_as_tuple(sheet2.cell_value(rows,col), workbook.datemode)
date_tmp = date(*date_value[:3]).strftime('%Y/%m/%d')
workbook = xlrd.open_workbook(r'F:\demo.xlsx',formatting_info=True)
sheet2 = workbook.sheets()[0]
merge = []
for (rlow,rhigh,clow,chigh) in sheet2.merged_cells:
merge.append([rlow,clow])
for index in merge:
print sheet2.cell_value(index[0],index[1])
xlwt excel写
import xlwt
def set_style(name,height,bold=False):
style = xlwt.XFStyle()
font = xlwt.Font()
font.name = name
font.height = height
font.bold = bold
font.color_index = 4
style.font = font
return style
f = xlwt.Workbook()
sheet1 = f.add_sheet(u'sheet1',cell_overwrite_ok=True)
row0 = [u'业务',u'状态',u'北京',u'上海',u'广州',u'深圳',u'状态小计',u'合计']
column0 = [u'机票',u'船票',u'火车票',u'汽车票',u'其它']
status = [u'预订',u'出票',u'退票',u'业务小计']
for i in range(0,len(row0)):
sheet1.write(0, i, row0[i], set_style('Times New Roman', 220, True))
i, j = 1, 0
while i < 4*len(column0) and j < len(column0):
sheet1.write_merge(i,i+3,0,0,column0[j],set_style('Arial',220,True))
sheet1.write_merge(i,i+3,7,7)
i += 4
j += 1
f.save(file)
openpyxl
from openpyxl import load_workbook
file_name = r'E:\test.xlsx'
worksheet = load_workbook(file_name)
sheet2 = worksheet.get_sheet_by_name("Sheet2")
sheet2.get_highest_column()
sheet2.get_highest_row()
for row in sheet2.rows:
for i in row:
print(i.value)
sheet2.get_cell_collection()
sheet2.cell('A1').value
sheet2.cell(row=1, column=1).value
sheet2.cell(row=1, column=2).value = 'sssssss'
sheet2.append({'A':'新增列A'})
sheet2.append({1:'新增行'})
worksheet.save(file)