我正在编写一个脚本,从access表获取一些数据并将其写入excel表。在此过程中,我试图格式化excal表。我已经将excel表的输出粘贴到脚本下面。在
在下面的脚本中,我从access表中提取数据并将其放入excel表中。它将第一列中的所有值分组(即所有M1、所有M20a等)。第二列是它们的相关面积(公顷)。第三列是每个分组值的面积之和。在
除最后一组(ZWA)外,所有面积总和都正确且位置正确。不是写那个值的总和。我觉得我需要设置脚本,以便它检测表中的最后一行。在
import arcpy, xlwt, sys, traceback, datetime
from arcpy import env
from xlwt import *
from itertools import groupby
from collections import defaultdict
from time import strftime
# Set workspace for the file to be used
env.workspace = "Z:\TestFolder"
env.overwriteOutput = True
# Stores access table into a variable and sorts the SMU field ascendingly
cur = arcpy.SearchCursor("Access Table", "", "", "SMU", "SMU A")
# Create excel workbook
book = Workbook()
sheet1 = book.add_sheet('Sheet 1')
# Create a dictionary
col_counts = defaultdict(int)
# Set a varialb eto be used in looping through rwos and detecting when the value in the cell is different from the last
last_value = object()
# Set the start of 2 counters. rowx is to count rows in the access table. rowadd is to add the values in a field alled row.SHAPE_Area
rowx = 3
rowadd = 0
# Loop through the access table
for row in cur:
# Ask if the current value is not equal to the last value in the row.SMU column
if row.SMU != last_value:
last_value = row.SMU
# if the current value doesn't equal the last value, then place the sum of the row.SHAPE_Area field for the last value in a new cell in a different column.
sheet1.write(int(rowx+1),3,rowadd/10000)
# Reset counter to 0
rowadd = 0
# Add 2 to the counter to create a space between groups of values in the excel table
rowx += 2
else:
# Else only add 1 to the counter if the value in the cell reamisn the same as the last one
rowx += 1
# if the value of the row is the same as the last one, then add the values for a second column together and write to the excel sheet
if row.SMU == str(last_value):
rowadd += row.SHAPE_Area
print rowadd
sheet1.write(int(rowx),0,row.SMU)
sheet1.write(int(rowx),1,row.SHAPE_Area/10000)
# Set the counter to += the last value in the (col_counts[last_value]) variable and start over again
rowx += (col_counts[last_value])
# Save the workbook
book.save("Z:\TestFolder\simple.xls")
下面是excel的输出表。请注意,我没有得到第三列中的ZWA总面积。有人能告诉我为什么会这样吗?我也希望删除第三列第一行中的0。在
^{pr2}$
你需要一些类似(未经测试)的代码。在
相关问题 更多 >
编程相关推荐