python对excel操作

对sheet表进行读操作

import xlrd
workbook = xlrd.open_workbook(‘C:\Users\qq\Desktop\test.xlsx’)
worksheets = workbook.sheet_names()
worksheet1 = workbook.sheet_by_name(u’Sheet1’)
num_rows = worksheet1.nrows
num_cols = worksheet1.ncols

读取所有的行

for curr_row in range(num_rows):
row = worksheet1.row_values(curr_row)
print(‘row%s is %s’ %(curr_row,row))

读取所有的单元格

for rown in range(num_rows):
cell=”
for coln in range(num_cols):
cell += str(worksheet1.cell_value(rown,coln))+’\t’
print cell

——————————————————————————————————————————————————————————

对sheet表进行写操作,创建workbook和sheet对象

import xlwt
workbooks = xlwt.Workbook()
sheet1 = workbooks.add_sheet(‘sheet1’,cell_overwrite_ok=True)
sheet2 = workbooks.add_sheet(‘sheet2’,cell_overwrite_ok=True)

向sheet页中写入数据

sheet1.write(0,0,’bookname’)
sheet1.write(0,1,’bookprice’)
sheet1.write(0,2,’bookpages’)

sheet1.write(1,0,’java’)
sheet1.write(1,1,’33$’)
sheet1.write(1,2,’365’)

保存该excel文件,有同名文件时直接覆盖

注意不要使用.xlsx为文件的扩展名，否则文件打不开

workbooks.save(‘C:\Users\qq\Desktop\test2.xls’)
print u’创建excel文件完成！’