Python中xlrd、xlwt、win32com模块对xls文件的读写操作

 # -*- coding: utf-8 -*-

#xlrd和xlwt只支持xls文件读写，openpyxl只支持xlsx文件的读写操作

import xlrd

import xlwt

import win32com

from win32com.client import constants,Dispatch

from openpyxl import load_workbook

from openpyxl import Workbook

from openpyxl.writer.excel import ExcelWriter

#--------------------------------使用win32com模块批量读入要处理的log文件--------------#

#注意表的计数是从1开始的和openpyxl类似

def win32ReadExcel(inputFile,num):

	#存放数据字典

	data_dic = {}

	line = 0

	for i in range(1,num+1):

		readfile = inputFile + str(i) + '.xls'

		print (readfile)

		xlApp = win32com.client.Dispatch('Excel.Application')

		wb = xlApp.Workbooks.Open(readfile)

		#取第一张表

		ws = wb.Worksheets(1)

		info = ws.UsedRange

		rows = info.Rows.Count

		print (rows)

		# 建立存储数据的字典

		# t = ws.Cells(1, 1).Value

		# print (t)

		for rx in range(0,rows-1):

			temp_list = []

			w1 = ws.Cells(rx+2, 1).Value

			w2 = ws.Cells(rx+2, 2).Value

			w3 = ws.Cells(rx+2, 3).Value

			w4 = ws.Cells(rx+2, 4).Value

			w5 = ws.Cells(rx+2, 5).Value

			temp_list = [w1,w2,w3,w4,w5]

			data_dic[line] = temp_list

			line = line + 1

		# for i in range(rows):

		# 	data_dic[line] = ws.Rows[i+1].Cells

		# 	line = line + 1

	print (data_dic)

	return data_dic

#------------------------------使用xlrd模块批量读入要处理的log文件--------------#

def readExcel(inputFile,num):

	#存放数据字典

	data_dic = {}

	line = 0

	for i in range(1,num+1):

		readfile = inputFile + str(i) + '.xls'

		print (readfile)

		wb = xlrd.open_workbook(readfile)

		#取第一张表

		ws = wb.sheets()[0]

		rows = ws.nrows

		print (rows)

		# 建立存储数据的字典

		for rx in range(0,rows-1):

			# print (rx)

			data_dic[line] = ws.row_values(rx+1)

			line = line + 1

	return data_dic

#-------------------------生成的xls格式的错误日志信息---------------------#

#注意xls格式的表示从0开始计数的

def exportXlsExcel(outputFile,data_log):

	file = xlwt.Workbook(encoding='utf-8',style_compression=0)

	table = file.add_sheet('sheet1',cell_overwrite_ok=True)

	table.write(0,0,'1')

	table.write(0,1,'2')

	table.write(0,2,'3')

	table.write(0,3,'4')

	table.write(0,4,'5')

	#读取过滤后的日志信息并写入xls文件中

	for i in range(0,len(data_log)):

		for j in range(0,5):

			table.write(i+1,j,data_log[i][j])

	file.save(outputFile);

#---------------------------------程序入口---------------------------------#

if __name__ == '__main__':

	data_dic = {}

	data_dic = win32ReadExcel('H:\Practice\j730_5_',3)

	#data_log = dealLog(data_dic)

	#exportXlsxExcel('bbbb3.xlsx',data_log)

	exportXlsExcel('bbbb.xls',data_log)

from: https://blog.csdn.net/revitalizing/article/details/47423427