Python簡單讀寫Excel (xlwt, xlrd, xlutils) -开发者知识库

Python簡單讀寫Excel (xlwt, xlrd, xlutils) -开发者知识库,第1张

#!/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:

Python簡單讀寫Excel (xlwt, xlrd, xlutils) -开发者知识库,第2张

用函數readXlsAndChange處理后生成新的excel:

Python簡單讀寫Excel (xlwt, xlrd, xlutils) -开发者知识库,第3张

用函數copyAndRewrite處理后的excel:

Python簡單讀寫Excel (xlwt, xlrd, xlutils) -开发者知识库,第4张



最佳答案:

本文经用户投稿或网站收集转载,如有侵权请联系本站。

发表评论

0条回复