arcpy 生成界址点成果表Excel主要实现方法介绍

受邀研究了一下arcpy生成界址点成果表，如有需要定制工具或源码可以联系作者。

---

2020年11月补充

实现了一个新的模板，详见ArcGIS生成界址点成果表Excel的主要实现方法（arcpy） – 知乎 (zhihu.com)

---

以下是原文

工具与结果如下：

主要实现方式

1.通过getPointListOfPolygon方法获取一个面的节点信息集合

def getDistance(pointStartX,pointStartY,pointEndX,pointEndY):
    p1=numpy.array([pointStartX,pointStartY])
    p2=numpy.array([pointEndX,pointEndY])
    p3=p2-p1
    p4=math.hypot(p3[0],p3[1])
    return round(p4,2)

def getPointListOfPolygon(row):
    lst=[]
    startNum=1
    for part in row[0]:
        countNum=startNum
        for point in part:
            if point:
                if countNum==startNum:
                    lst.append([‘J%s‘%countNum,round(point.X,3),round(point.Y,3),None] )
                    countNum+=1
                else:
                    lastIndexOflst=len(lst)-1
                    beforeX=lst[lastIndexOflst][1]
                    beforeY=lst[lastIndexOflst][2]
                    thisX=round(point.X,3) 
                    thisY=round(point.Y,3)  
                    lst.append([‘J%s‘%countNum,thisX,thisY,getDistance(beforeX,beforeY,thisX,thisY)] )
                    countNum+=1
            else:
                lastIndexOflst=len(lst)-1
                lst[lastIndexOflst][0]=‘J%s‘%startNum
                countNum-=1
                startNum=countNum
        lastIndexOflst=len(lst)-1
        lst[lastIndexOflst][0]=‘J%s‘%startNum
    return lst

 2.通过COM读取Excel模板表，进行写入另存操作，Excel操作类如下。

通过COM操作Excel需要安装pywin32，coder要对Excel的对象模型比较熟悉，建议研究一下Excel-VBA或者Excel-C#以熟悉。

class ExcelWriter:
    def __init__(self,filename=None):
        if os.path.exists(filename):
            self.xlApp=win32com.client.DispatchEx(‘Excel.Application’)
            self.filename=filename
            self.xlWb=self.xlApp.Workbooks.open(filename)
    def setCurrentSheet(self,index):
        self.xlWs=self.xlWb.Worksheets(index)
    def createNewSheetFromSheet1(self,nameofcopy):
        lastSheet=self.xlWb.Worksheets(self.xlWb.Worksheets.Count)
        templateSheet=self.xlWb.Worksheets(1)
        templateSheet.Copy(None,lastSheet)
        newSheet=self.xlWb.Worksheets(self.xlWb.Worksheets.Count)
        newSheet.Name=nameofcopy
    def deleteSheetsExceptSheet1(self):
        self.xlApp.DisplayAlerts = False
        sheetsCount=self.xlWb.Worksheets.Count
        if sheetsCount>1:
            for i in range(sheetsCount,1,-1):
                self.xlWb.Worksheets(i).Delete()
    def clearContentsOfSheet(self,index):
        self.xlWb.Worksheets(index).Range(“A5:G53”).ClearContents()
    def setCell(self,row,col,val):
        self.xlWs.Cells(row,col).Value=val
    def mergeCell(self,startRow,startCol,endRow,endCol):
        self.xlWs.Range(self.xlWs.Cells(startRow,startCol),self.xlWs.Cells(endRow,endCol)).Merge()
    def saveAs(self,savePath):
        self.xlWb.SaveAs(savePath.decode(‘utf-8’))
    def close(self):
        self.xlWb.Close()
        self.xlApp.Quit()