Skip to content

GeoProcessor / Command / WriteTableToExcel

Overview

The WriteTableToExcel command writes a Table to an Excel file.

  • The Table is written as a worksheet in an Excel workbook file.
  • Can write to a new Excel workbook file or to an existing Excel workbook file.
  • An Excel worksheet will be overwritten if the OutputWorksheet parameter value is the same as an existing worksheet name.
  • Can specify which Table columns to write to the output Excel file.
  • Can specify whether to include or exclude the Table's index column from the output Excel file.
  • Can write to .xlsx or .xls file format.

Command Editor

The following dialog is used to edit the command and illustrates the command syntax.

WriteTableToExcel

WriteTableToExcel Command Editor (see full-size image)

Command Syntax

The command syntax is as follows:

WriteTableToExcel(Parameter="Value",...)

Command Parameters

Parameter                      Description Default          
TableID
required		 The identifier of the Table to write. None - must be specified.
OutputFile
required		 The name of the Excel workbook to write to (relative or absolute path). ${Property} syntax is recognized.

Can be an existing or non-existing Excel file. If non-existing, the Excel workbook file (.xlsx) is created.		 None - must be specified.
OutputWorksheet
required		 The name of the worksheet that the Table will be written to. Can be an existing or non-existing worksheet. If existing, the worksheet will be overwritten with the Table data. None - must be specified.
ColumnsToInclude A comma-separated list of the glob-sytle patterns filtering which columns to include in the Excel workbook file.

See Determining Which Columns to Write.		 *

All columns are written.
ColumnsToExclude A comma-separated list of the glob-sytle patterns filtering which columns to exclude in the Excel workbook file.

See Determining Which Columns to Write.		 All columns are written.
WriteIndexColumn Indicate whether to write an index column:
  • True - the Table's index column is included in the output Excel file.
  • False - the Table's index column is not included in the output Excel file.
 True

Determining Which Columns to Write

  • The ColumnsToInclude parameter is always processed first. TheColumnsToExclude parameter is always processed second.
    • The ColumnsToInclude selects all of the Table's columns that follow the given patterns. By default (*) all of the Table's columns are included.
    • The ColumnsToExclude removes all of the columns previously selected from the ColumnsToInclude parameter that follow the given patterns.

Examples

See the automated tests.

The following Table data are used in the examples. The examples assume that the ExampleTable1 and ExampleTable2 Tables have already been read into the GeoProcessor.

Example Table Data

Table ID
ExampleTable1
ExampleTable2

ExampleFolder

Filename File Type Worksheets
ExampleFile1.xlsx Excel Workbook Clients, Products

Example 1: Write a Table to an Existing Excel Workbook (New Worksheet)

WriteTableToExcel(TableID = "ExampleTable1", OutputFile = "ExampleFolder/ExampleFile1.xlsx", OutputWorksheet = "Locations")

After running the command, the following Excel workbook files are within the ExampleFolder.

Filename File Type Worksheets
ExampleFile1.xlsx Excel Workbook Clients, Products, Locations

Example 2: Write a Table to an Existing Excel Workbook (Existing Worksheet)

WriteTableToExcel(TableID = "ExampleTable2", OutputFile = "ExampleFolder/ExampleFile1.xlsx", OutputWorksheet = "Products")

After running the command, the following Excel workbook files exist in the ExampleFolder. Note that the Products worksheet is overwritten with the data from the ExampleTable2 table.

Filename File Type Worksheets
ExampleFile1.xlsx Excel Workbook Clients, Products

Example 3: Write a Table to an New Excel Workbook

WriteTableToExcel(TableID = "ExampleTable1", OutputFile = "ExampleFolder/ExampleFile2.xlsx", OutputWorksheet = "Locations")

After running the command, the following Excel workbook files exist in the ExampleFolder.

Filename File Type Worksheets
ExampleFile1.xlsx Excel Workbook Clients, Products
ExampleFile2.xlsx Excel Workbook Locations

Troubleshooting

At the current time, you cannot write a table to an existing Excel workbook file in .xls format. You can write a table to an existing Excel workbook file in .xlsx format. The capability to write a table to an existing Excel workbook file in .xls format will be enabled in the future.

See Also