遇到格式化的Excel横表,需要将数据按宗写入,数据存放在mdb中的多个表中,初探修改Excel的数据内容并另存:
# -*- coding:utf8-*-
import xlwt
import xlrd
from xlutils.copy import copy
import arcpy
style = xlwt.easyxf('font:name SimSun,height 220;align: wrap on, vert centre, horiz center;')
x = xlrd.open_workbook('320507118111JC00068.xls', formatting_info=True)
Database_ZD = u'相城区(320507)工程文件(201712051041).mdb/ZD_K_320507'
Database_QLR = u'相城区(320507)工程文件(201712051041).mdb/ZD_QLR'
Database_DJDCB = u'相城区(320507)工程文件(201712051041).mdb/ZD_DJDCB'
Database_JZBSB = u'相城区(320507)工程文件(201712051041).mdb/ZD_JZBSB'
def ModifyExcel(djh,qlrxx,djdcb,jzbsbid,jzbsbjblx,jzbsbjzxcd):
new_excel = copy(x)
new_sheet = new_excel.get_sheet(0)
new_sheet.write(12, 8, djh, style)
new_sheet.write(24, 6, qlrxx[1], style)
if qlrxx[3] == '1':
new_sheet.write(24, 19, u'个人', style)
else:
new_sheet.write(24, 19, u'其他', style)
new_sheet.write(26, 19, qlrxx[2], style)
new_sheet.write(29, 4, djdcb[1], style)
new_sheet.write(39, 8, djdcb[2], style)
n = 57
for y in jzbsbid:
#序号
new_sheet.write(n, 1, jzbsbid[y-1])
#界标类型
if jzbsbjblx == '1':
new_sheet.write(n, 3, u'√')
elif jzbsbjblx == '2':
new_sheet.write(n, 4, u'√')
elif jzbsbjblx == '3':
new_sheet.write(n, 5, u'√')
else:
new_sheet.write(n, 6, u'√')
#界址线长度
new_sheet.write(n+1, 8, jzbsbjzxcd[y-1] )
n = n+2
new_sheet.write(n, 1, jzbsbid[0])
new_excel.save('f:/'+'test/'+djh+'.xls')
print 'save finish!'
# n=0
# for l in list:
# ModifyExcel(l,n)
# n=n+1
# print 'ok'
with arcpy.da.SearchCursor(Database_ZD, 'DJH') as cursor:
for r in cursor:
djh = str(r[0])
print djh
where = "DJH = " +"\'" +djh+"\'"
print where
DJDCB_ID = []
DJDCB_JBLX = []
DJDCB_JZXCD = []
for q in arcpy.da.SearchCursor(Database_QLR, ('DJH', 'QLRMC', 'QLRZJH', 'QLRLX', 'TXDZ'),
where):
for d in arcpy.da.SearchCursor(Database_DJDCB, ('DJH', 'TDZL', 'SZTFH','SYQLX','QSXZ','TDQSLYZMCL','ZDSZB',
'ZDSZN', 'ZDSZD','ZDSZX','TDYT','PZYT','ZDZMJ','JZZMJ',
'PZMJ'), where):
for j in arcpy.da.SearchCursor(Database_JZBSB, ('DJH', 'XH', 'JBLX', 'JZDLX', 'JZXWZ', 'JZXCD'),where):
DJDCB_ID.append(j[1])
DJDCB_JBLX.append(j[2])
DJDCB_JZXCD.append(j[5])
ModifyExcel(djh, q, d, DJDCB_ID, DJDCB_JBLX, DJDCB_JZXCD)
print DJDCB_JZXCD
问题:数据量较大时 数据溢出 可能需要多个模板进行解决