python xlutils教程_Python简单读写Excel (xlwt, xlrd, xlutils)

#!/usr/bin/env python

#coding=utf8

####################################################################################

#

#此程序是为了将excel中的一列单元格的中的某些数字不够三位的补零,如cell: abc_12_cd -> abc_012_cd

#涉及读写整个excel表，和改写excel部分单元格的内容

#

####################################################################################

import xlwt #Need install xlwt, create excel and write

import xlrd #Need install xlrd, read excel

from datetime import datetime #date covert

from xlutils.copy import copy #此module是打开excel文件后直接写入要改写cell,need install xlutils(relay on xlwt,xlrd)

style_backGreen_fontBlack_boldYes = xlwt.easyxf('align: horz center; font: name Times New Roman, color-index black, bold on, height 250; pattern: pattern solid, fore_colour light_green; borders: left 1, right 1, top 1, bottom 1;')

style_backGreen_fontBlack_boldYes_header = xlwt.easyxf('align: horz center; font: name Times New Roman, color-index black, bold on, height 300; pattern: pattern solid, fore_colour bright_green; borders: left 1, right 1, top 1, bottom 1;')

style_backYellow_fontBlack_boldNo = xlwt.easyxf('align: horz center; font: name Times New Roman, color-index black, bold off, height 250; pattern: pattern solid, fore_colour light_yellow; borders: left 1, right 1, top 1, bottom 1;')

style_backGray25_fontBlack_boldNo = xlwt.easyxf('align: horz center; font: name Times New Roman, color-index black, bold off, height 250; pattern: pattern solid, fore_colour gray25; borders: left 1, right 1, top 1, bottom 1;')

style_backwhite_fontBlack_boldNo = xlwt.easyxf('align: horz center; font: name Times New Roman, color-index black, bold off, height 250; pattern: pattern solid, fore_colour white; borders: left 1, right 1, top 1, bottom 1;')

def createExcelHandler(sheetName):

wb = xlwt.Workbook()

ws = wb.add_sheet(sheetName, cell_overwrite_ok=True)

return wb, ws

def readXlsAndChange(readFileName, sheetName):

mWorkBook = xlrd.open_workbook(readFileName, formatting_info = False) #打开一个excel表,并保持格式

(wb, ws) = createExcelHandler(sheetName) #创建一个excel表，建立一个sheet

#mSheet = mWorkBook.sheets()[0] #取一个excel表的第一个sheet

#mRowData = mSheet.row_values(1) #获取一行excel数据

#mRowDataCell = mRowData[1].split('_') ＃获取第二个cell的值

mSheet = mWorkBook.sheets()[0]

mStationChange = []

mStationChange.append('Station')

for index, station in enumerate(mSheet.col_slice(1, 0, None)): #从第二列，第一行往下读

#print station

if index == 0: #第一行保存的是"station"使用split后会造成后续的list range out

continue

mStationChange.append(station)

mStationChangeCell = mStationChange[index].value.encode('utf-8').split('_') #获取单元格的内容 .value.encode('utf-8').split('_')

#print mStationChangeCell

mStationChangeCell[2] = mStationChangeCell[2].zfill(3) #29->029

mStationChange[index] = '_'.join(mStationChangeCell)

#print mStationChange[index]

for rowIndex, mRowData in enumerate(mSheet.get_rows()):

#print mRowData

for colIndex, cellData in enumerate(mRowData):

if (cellData.ctype == 3): # 日期type为3， ctype : 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error

date_value = xlrd.xldate_as_tuple(cellData.value, mWorkBook.datemode)

#print date_value #(2016, 1, 10, 3, 53, 23)

date_tmp = datetime(*date_value[:6]) #2016-01-10 03:59:01

#date_tmp = datetime(*date_value[:6]).isoformat() #2016-01-10T03:59:01

#print date_tmp

ws.write(rowIndex, colIndex, date_tmp, style_backwhite_fontBlack_boldNo) #日期不转换的话可以自己使用excel中的日期调节

elif colIndex == 1:

ws.write(rowIndex, colIndex, mStationChange[rowIndex], style_backwhite_fontBlack_boldNo)

else:

ws.write(rowIndex, colIndex, cellData.value, style_backwhite_fontBlack_boldNo)

print '/'.join(readFileName.split('/')[0:-1]) + '/new_' + readFileName.split('/')[-1]

wb.save('/'.join(readFileName.split('/')[0:-1]) + '/new_' + readFileName.split('/')[-1].split('.')[0] + '.xls') #不知为何只能保存.xls格式的excel，xlsx格式创建后打不开

print "Function readXlsAndChange Done!"

#print string.zfill(int(mRowData[1].split('_')[2], base=10), 3) #变换为数字

def copyAndRewrite(readFileName):

mWorkBook = xlrd.open_workbook(readFileName, formatting_info = False) #打开一个excel表,并保持格式

msheetNo1 = mWorkBook.sheets()[0]

newWorkBook = copy(mWorkBook) #复制一个workbook,是可以重写的

newSheetNo1 = newWorkBook.get_sheet(0) #得到一个sheet，然后写入指定位置，其它不变

mStationChange = []

mStationChange.append('Station')

for index, station in enumerate(msheetNo1.col_slice(1, 0, None)): #从第二列，第一行往下读

#print station

if index == 0: #第一行保存的是"station"使用split后会造成后续的list range out

continue

mStationChange.append(station)

mStationChangeCell = mStationChange[index].value.encode('utf-8').split('_') #获取单元格的内容 .value.encode('utf-8').split('_')

#print mStationChangeCell

mStationChangeCell[2] = mStationChangeCell[2].zfill(3) #29->029

mStationChange[index] = '_'.join(mStationChangeCell)

#print mStationChange[index]

for rowIndex, mCellData in enumerate(mStationChange):

newSheetNo1.write(rowIndex, 1, mCellData)

newWorkBook.save('/'.join(readFileName.split('/')[0:-1]) + '/new_' + readFileName.split('/')[-1].split('.')[0] + '.xls') #不知为何只能保存.xls格式的excel，xlsx格式创建后打不开

print "Function copyAndRewrite Done!"

if __name__ == "__main__":

readXlsAndChange("./abc/testZero.xlsx", "Retest_Item")

#copyAndRewrite("./abc/testZero.xlsx")

原excel:

用函数readXlsAndChange处理后生成新的excel:

用函数copyAndRewrite处理后的excel:

来源：https://www.cnblogs.com/v-BigdoG-v/p/7398546.html