xlrd，xlwt操作Excel实例

把有合并单元格的信息读取出来，输出所在层数与位置

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

import xlrd

import xlwt

r=1

# shxrange = range(bk.nsheets)

wb = xlwt.Workbook(encoding='utf-8')#创建工作簿,设置字符编码

ws = wb.add_sheet('A TEST Sheet')#创建sheet

goods = xlrd.open_workbook('test3.xlsx')#打开文件

index = 0

for sheet_index in range(goods.nsheets):

	sh = goods.sheet_by_index(sheet_index)#返回第几页的对象

	#添加内容到row_list当中

	row_list = []

	for rx in range(sh.nrows):

		if sh.row(rx)[0].ctype:

			print sh.row(rx)

			row_list.append(sh.row_values(rx))

	# 取出有多少行

	row = len(row_list)

	# print row

	for r in range(row):

		weizhi =1

		for c in range(len(row_list[r])):

			if row_list[r][c]:

				ws.write(index, 0, row_list[r][c])

				ws.write(index,3,row)

				ws.write(index,4,weizhi)

				index = index + 1

		row =row-1

wb.save('example3.xls')

要处理的数据和上面差不多，不过稍微麻烦一点

要处理的数据分为两个sheet页

sheet页1



sheet页2

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

import xlrd

import xlwt

r=1

# shxrange = range(bk.nsheets)

wb = xlwt.Workbook(encoding='utf-8')#创建工作簿,设置字符编码

ws = wb.add_sheet('A TEST Sheet')#创建sheet

goods = xlrd.open_workbook('test10.xlsx')#打开文件

index = 0

sheet = 0

for sheet_index in range(goods.nsheets/2):

	sh = goods.sheet_by_index(sheet)#返回第1页的对象

	print sheet

	#添加内容到row_list当中

	row_list = []

	for rx in range(sh.nrows):

		#第一行为当前页的分类

		if sh.row(rx)[0].ctype:

			# print sh.row(rx)

			row_list.append(sh.row_values(rx))

		else:

			# print r

			r +=1

	# 取出有多少行

	row = len(row_list)

	sheet = sheet+1

	print sheet

	#将第二页的内容添加到con_list当中

	con_list = []

	sh = goods.sheet_by_index(sheet)#返回第几页的对象

	for rx in range(sh.nrows):

		#第一行为当前页的分类

		# if rx ==0:

		# 	fenglei = sh.row_values(rx)

		# else:

		if sh.row(rx)[0].ctype:

			# print sh.row(rx)

			con_list.append(sh.row_values(rx))

		else:

			print r

			r +=1

	sheet=sheet+1

	# print row

	for r in range(row):

		weizhi = 1

		for c in range(len(row_list[r])):

			if row_list[r][c]:

				ws.write(index, 0, row_list[r][c])

				# ws.write(index,10,fenglei)

				ws.write(index,3,row)

				ws.write(index,4,weizhi)

				try:

					ws.write(index,2,con_list[int(row_list[r][c].split("*")[0])-1][0])

					try:

						ws.write(index,5,con_list[int(row_list[r][c].split("*")[0])-1][1])

					except:

						ws.write(index,5,500)

					try:

						ws.write(index,6,con_list[int(row_list[r][c].split("*")[0])-1][2])

					except:

						ws.write(index,6,500)

					ws.write(index,7,row_list[r][c].split("*")[1])

					ws.write(index,8,row_list[r][c].split("*")[2])

					ws.write(index,9,'hgyingliao'+str(sheet))

				except:

					ws.write(index,5,500)

					ws.write(index,6,500)

					ws.write(index,7,500)

					ws.write(index,8,500)

					ws.write(index,9,'hgyingliao'+str(sheet))

				weizhi =weizhi+1

				index = index + 1

		row =row-1

wb.save('example3.xls')