今天接到同事的一个求助,需求呢是把一个Excel的资料重新整理。具体操作很简单,把其中列的中文名和英文名分开,在后面一列增加英文名的列,然后把同类的物品属性也做一个行合并,具体如下图。
修改之后变成这样
so easy!但是总共接近3000条,难道要一条一条手动修改吗?_(:з」∠)_……
还好,我们使用Python写一个脚本可以很简单的帮我们解决这个问题,话不多说上Code
import xlrd
import xlwt
import re
import sys
from xlutils.copy import copy
def read03Excel(path):
workbook = xlrd.open_workbook(path)
wb = copy(workbook)
sheets = workbook.sheet_names()
worksheet = workbook.sheet_by_name(sheets[0])
sheet = wb.get_sheet(0)
tempStr = ''
for i in range(0, worksheet.nrows):
row = worksheet.row(i)
for j in range(0, worksheet.ncols):
if j == 3:
if contain_zh(worksheet.cell_value(i, j)) :
print(worksheet.cell_value(i, j), "\t", end="")
else:
if contain_zh(worksheet.cell_value(i-1, j)):
sheet.write(i-1, j+1, worksheet.cell_value(i, j))
else:
sheet.write(i-2, j+1, worksheet.cell_value(i-2, j+1)+worksheet.cell_value(i, j))
sheet.write(i, j, '')
if j == 9:
print(i)
print(checkFeature(i,worksheet))
if checkFeature(i,worksheet) == i:
tempStr = str(worksheet.cell_value(i, j))
if not tempStr.endswith(',') and tempStr is None:
tempStr = tempStr + ','
else:
tempStr = str(tempStr) + str(worksheet.cell_value(i, j))
sheet.write(i, j, '')
sheet.write(checkFeature(i,worksheet), j, tempStr)
wb.save('Documents/demo/target.xls')
print()
def checkFeature(index,worksheet):
if index==0:
return 0
if index==1:
return 1
if index > 1:
if worksheet.cell_value(index, 2) == worksheet.cell_value(index - 1, 2):
return checkFeature(index-1,worksheet)
else:
return index
zh_pattern = re.compile(u'[\u4e00-\u9fa5]+')
def contain_zh(word):
global zh_pattern
match = zh_pattern.search(word)
return match
file_test = 'Documents/demo/test.xls'
write03Excel(file_target)
首先环境都要配好,另外2003的Excel和2007以后的Excel的格式不一样,需要区分处理,因为我需要处理的是xls格式的,所以就直接用2003之前的处理方法啦。