python读写excel之xlrd、xlwt、xlutils
简介:
xlrd主要用来读excel,针对.xls格式;
xlwt主要用来写excel,针对.xls格式,超出excel 的单格内容长度上限32767,就会报错;
xlutils结合xlrd可以达到修改excel文件目的,需要注意的是你必须同时安装这三个库;
1、xlrd、xlwt、xlutils (只能操作.xls
,不能操作.xlsx
)
使用xlrd读取excel:
1 # 导入模块 2 import xlrd 3 #读取excel操作 4 def read_excel(path): 5 all = [] 6 workbook = xlrd.open_workbook(path) 7 sheet = workbook.sheet_by_index(1) 8 #逐行读取,并添加进list 9 for i in range(sheet.nrows): 10 rows = sheet.row_values(i) 11 #print(rows) 12 all.append(rows) 13 return all 14 15 #程序运行 16 if __name__ == '__main__': 17 path = r'D:\WorkHome\python\excel_parse\71.xls' 18 value = read_excel(path); 19 for i in value: 20 print(i) 21 print('读取成功')
excel读写实现
1 import xlrd 2 import xlwt 3 from xlutils.copy import copy 4 5 def read_data(path): 6 excel_data = xlrd.open_workbook(path) 7 sheet_name = excel_data.sheet_names()[1] 8 sheet = excel_data.sheet_by_index(1) 9 #print(sheet.) 10 for i in range(sheet.nrows): 11 rows = sheet.row_values(i) 12 #rowval = rows. 13 #print(rows) 14 yield rows ####如果不考虑内存损耗的话可以不用在这里使用迭代器 15 ''' 16 .................................................................. 17 在这里放弃使用xlwt,是由于xlwt只能创建一个全新的excel文件, 18 然后对这个文件进行写入内容以及保存。但是大多数情况下是读 19 入一个excel文件,然后进行修改或追加,这个时候就需要xlutils了。 20 .................................................................. 21 def write_excel_xls(wpath,sheet_name,row ,nrow,ncol): 22 # 2. 创建Excel工作薄 23 myWorkbook = xlwt.Workbook(wpath) 24 # 3. 添加Excel工作表 25 mySheet = myWorkbook.add_sheet(sheet_name) 26 # 4. 写入数据 27 for j in range(ncol): 28 mySheet.write(nrow, j, row[j]) 29 # 5. 保存 30 myWorkbook.save(wpath) 31 print('写入成功') 32 ''' 33 def write_excel_xls_append(wpath, sheet_name, rowvalue, nrow, ncol): 34 workbook = xlrd.open_workbook(wpath) 35 ''' 36 ###在这里是考虑到往文件中追加内容,而非覆盖 37 #worksheet = workbook.sheet_by_index(0) 38 #rows_old = worksheet.nrows 39 ''' 40 new_workbook = copy(workbook) 41 new_worksheet = new_workbook.get_sheet(0) 42 for j in range(ncol): 43 new_worksheet.write(nrow, j, rowvalue[j]) 44 new_workbook.save(wpath) # 保存工作簿 45 print("写入数据成功!") 46 47 def main(): 48 path = r'D:\WorkHome\python\excel_parse\71.xls' 49 wpath = r'D:\WorkHome\python\excel_parse\71_new.xls' 50 sheet_name = xlrd.open_workbook(path).sheet_names()[1] 51 nrow = 0 52 #nrow = xlrd.open_workbook(path).sheet_by_index(1).nrows 53 ncol = xlrd.open_workbook(path).sheet_by_index(1).ncols 54 read_data(path) 55 for rowvalue in read_data(path): 56 #print(row) 57 write_excel_xls_append(wpath, sheet_name, rowvalue, nrow, ncol) 58 nrow += 1 59 60 if __name__ == '__main__': 61 main()