python excel文件操作
1. 读取excel文件
(1). 打开excel文件,例:
import xlrd
data = xlrd.open_workbook(u'C:\\Users\\83382\\Desktop\\毕业论文文献\\程序与数据\\air\\TEST.xlsx' )
(2). 获取文件内容信息,例:
mysheets = data.sheets()
sheet0 = mysheets[0 ]
sheet1 = data.sheet_by_index(1 )
sheet2 = data.sheet_by_name('sheet2' )
rows = sheet0.nrows
cols = sheet0.ncols
myrow = sheet0.row(1 )
myrow_values = sheet0.row_values(1 )
mycol = sheet0.col(1 )
mycol_values = sheet0.col_values(1 )
mycell = sheet0.cell
mycell_value = sheet0.cell_value
mycell_11 = mycell(1 ,1 )
mycell_value_11 = mycell_value(1 ,1 )
2. 写excel文件
(1). 创建工作簿和工作表,例:
myworkbook = xlwt.Workbook()
sheet1 = myworkbook.add_sheet("Test" )
(2). 设置字体,例:
font0 = xlwt.Font()
font0.name = 'Times New Roman'
font0.colour_index = 0
font0.bold = False
mystyle = xlwt.XFStyle()
mystyle.font = font0
(3). 在各个表格中写入对应的信息,例:
sheet1.write(0 ,0 , 'xiaoming' , mystyle)
sheet1.write(0 ,1 , '123456' , mystyle)
sheet1.write(0 ,4 , u'男' , mystyle)
(4). 保存文件,例:
myworkbook.save("C:\\Users\\Administrator\\Desktop\\TEST1.xlsx" )
3. 修改excel文件
1. 需要用到xlutils.copy模块,例:
from xlutils.copy import copy
myfile = xlrd.open_workbook("C:\\Users\\Administrator\\Desktop\\TEST1.xlsx" , formatting_info=True )
myfile1 = copy(myfile)
sheetm0 = myfile1.get_sheet(0 )
sheetm0.write(1 ,0 , 'xiaoming' , mystyle)
sheetm1 = myfile1.add_sheet('Test1' )
sheetm1.write(0 ,0 , 'EFFORT' , mystyle)
myfile1.save("C:\\Users\\Administrator\\Desktop\\TEST2.xlsx" )