topic Re: Tool to Select Multiple Layers, Export to Single Excel with multiple worksheets in Web AppBuilder Custom Widgets Questions

Tool to Select Multiple Layers, Export to Single Excel with multiple worksheets

smevans88 — Wed, 20 Jul 2022 17:49:40 GMT

Hello All,

We have created a gp tool (using Python) to select features from multiple layers, export them to individual CSV files, then combine them as separate worksheets in an Excel workbook. Works great locally in ArcMap, then after publishing, and bringing into WAB for Poral, it errors out due to limitations of the WAB Portal environment.

Is this a functionality that can be used in the Enhanced Search widget?

If not, is this easy enough to do in JavaScript? Below is the code for a Python solution, which is very simple:

##Import Modules
import arcpy
import glob, os
import pandas as pd
import xlsxwriter
from pandas import DataFrame, ExcelWriter
import csv

##Allow outputs to be overwritten
arcpy.env.overwriteOutput = True

# CSV Locations folder path
csv_Output_dir = arcpy.env.scratchFolder

## Get names of layers that have been selected
## If features from multiple layers are selected, a List is returned, separated by semicolon
in_featureclassInputs = arcpy.GetParameterAsText(0)
arcpy.AddMessage("Input Layer Names: " + str(in_featureclassInputs+"\n"))

## Loop through each layer in List to create individual CSV files
for FeatClass in in_featureclassInputs.split(';'):

## Strip out unnecessary quotes and slashes
FeatClassStripped = FeatClass.replace("'","")
CSV_Name = FeatClassStripped.split("\\")[-1]+".csv"

##Arcpy table conversion tool being used here
arcpy.conversion.TableToTable(in_rows=FeatClassStripped, out_path=csv_Output_dir, out_name=CSV_Name)

### Create Excel File and Combine CSVs into it
Excel_workbook = pd.ExcelWriter(arcpy.env.scratchFolder+"\GIS_Asset_List.xlsx", engine='xlsxwriter')
for csv_file in glob.glob(os.path.join(arcpy.env.scratchFolder, '*.csv')):
read_file = pd.read_csv(csv_file)
sheetTitle = csv_file.split("\\")[-1].replace(".csv","")
read_file.to_excel (Excel_workbook, sheet_name=sheetTitle)

## Save Excel Workbook and create link for Excel download
Excel_workbook.save()
arcpy.SetParameterAsText(1, arcpy.env.scratchFolder+"\GIS_Asset_List.xlsx"

Re: Tool to Select Multiple Layers, Export to Single Excel with multiple worksheets

JamesCrandall — Mon, 08 Aug 2022 18:22:27 GMT

One idea/option is to publish the Tool you've created as a new Geoprocessing Service onto your ArcGIS Server site, at that point it's just a REST endpoint that you can execute from just about anything that has web access. We do this in some of our WAB widgets with the javascript api's Geoprocessor task

Re: Tool to Select Multiple Layers, Export to Single Excel with multiple worksheets

smevans88 — Wed, 10 Aug 2022 17:41:24 GMT

Thanks James. This actually seems doable. I will start looking at this solution and provide updates. Thanks!

Re: Tool to Select Multiple Layers, Export to Single Excel with multiple worksheets

ahargreaves_FW — Tue, 03 Jan 2023 14:21:52 GMT

@JamesCrandall I am attempting to do what you say with a simple GP model that exports features to shapefiles. It works fine in Pro, and allows me to publish it as a service. However, when I use that service in the WAB GP tool my input and output parameters fail to show. How do I expose these?