Python简单读写Excel (xlwt, xlrd, xlutils)
来源:互联网 发布:mac唇部打底好用么 编辑:程序博客网 时间:2024/05/17 23:19
#!/usr/bin/env python#coding=utf8######################################################################################此程序是为了将excel中的一列单元格的中的某些数字不够三位的补零,如cell: abc_12_cd -> abc_012_cd#涉及读写整个excel表,和改写excel部分单元格的内容#####################################################################################import xlwt #Need install xlwt, create excel and writeimport xlrd #Need install xlrd, read excelfrom datetime import datetime #date covertfrom 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, wsdef 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:
0 0
- Python简单读写Excel (xlwt, xlrd, xlutils)
- Python读写Excel文件,xlrd ,xlwt ,xlutils
- 使用python读写操作同一个excel(xlrd,xlwt,xlutils)
- Python xlrd、xlwt、xlutils修改Excel文件
- Python xlrd、xlwt、xlutils修改Excel文件
- Python xlrd、xlwt、xlutils修改Excel文件
- Python.xlrd&xlwt -- Excel读写
- Python xlrd xlwt读写Excel
- Python操作excel(需xlrd,xlwt,xlutils模块)
- python写excel文件,openpyxl, xlrd, xlwt,xlutils
- Python xlrd、xlwt、xlutils读取、修改Excel文件
- Python处理Excel文档(xlrd, xlwt, xlutils)
- 【初识python】用xlrd,xlwt,xlutils操作excel学习笔记
- 用python读写excel(xlrd、xlwt)
- Python操作Excel读写--xlrd、xlwt模块
- python操作Excel读写--使用xlrd/xlwt
- Python读写Excel:xlrd与xlwt
- 用python读写excel(xlrd、xlwt)
- TranslateAnimation移动动画Demo
- Struts2自定义拦截器实例—登陆权限验证
- Android中保存数据的四种方法
- github学习
- SQL总结 基本查询
- Python简单读写Excel (xlwt, xlrd, xlutils)
- 最疯狂的闹钟
- (centos)mysql的安装及基本操作
- Android Mediaplayer设置静音和恢复声音
- jquery的extend和fn.extend
- C++派生类的构造函数
- 直方图均衡化—图像增强
- php批量更新或插入数据
- phpunit jenkins error