不能直接对xlsx文件进行修改,需要讲原来的xlsx文件内容读出来,修改之后在写到新的xlsx文件中去。读取xlsx文件用xlrd模块,写xlsx文件用xlsxwriter模块。
1、安装
pip install xlrd
pip install xlsxwriter
2、举例:读取原来xlsx文件中的内容,循环多次构成10000条数据写入到新的xlsx文件中。
#encoding=utf-8
import time
import xlsxwriter
import xlrd
import random
if __name__=="__main__":
tm=time.strftime("%Y%m%d", time.localtime())
workbook = xlsxwriter.Workbook(tm+u'-发布模板.xlsx')
worksheet = workbook.add_worksheet('Sheet0')
bold_format = workbook.add_format({'bold':True})
data_format = workbook.add_format({'num_format':'0.00'})
date_format =workbook.add_format({'num_format':'####-##'})
text_format=workbook.add_format()
text_format.set_pattern(9)
data=xlrd.open_workbook("20170721 .xlsx")
table = data.sheet_by_index(0)
nrows = table.nrows
ncols = table.ncols
worksheet.write('A1', u'盟友人员编号')
worksheet.write('B1', u'月份')
worksheet.write('C1', u'云闪付交易金额')
worksheet.write('D1', u'云闪付可分润金额')
worksheet.write('E1', u'拓展奖交易金额')
worksheet.write('F1', u'拓展奖可分润金额')
worksheet.write('G1', u'推广奖交易金额')
worksheet.write('H1', u'推广奖可分润金额')
worksheet.write('I1', u'园丁奖交易金额')
worksheet.write('J1', u'园丁奖可分润金额')
worksheet.write('K1', u'导师奖交易金额')
worksheet.write('L1', u'导师奖可分润金额')
for m in xrange(12000/nrows):
for i in xrange(nrows):
if i==0:
i+=1
for j in xrange(ncols):
cell_value = table.cell_value(i,j)
if m==0:
worksheet.write(m*nrows+i,j,cell_value)
else:
worksheet.write(m*(nrows-1)+i,j,cell_value)
workbook.close()