topic Re: Run a script to generate a report and export to excel in Python Questions

Run a script to generate a report and export to excel

VictoriaWyeth — Mon, 09 Dec 2013 10:29:35 GMT

Hello

I wonder if someone can help me please.

I have a feature class which I want to export each record individually into a report (see attached) and then export the report into excel. Each of the reports generated will be in the same format but need to be placed into separate excel spreadsheets.

This is the script I have created:

# Import arcpy module
import arcpy

# Local variables:
Wells = "Wells"

# Process: Select Layer By Attribute

mxd = arcpy.mapping.MapDocument(r"C:\test.mxd")
df = arcpy.mapping.ListDataFrames(mxd, "Layers")[0]
lyr = arcpy.mapping.ListLayers(mxd, "Wells", df)[0]
arcpy.mapping.ExportReport(lyr,
                           r"C:\CAD tables backup\CAD tables working\Reports\Wells.rlf",
                           r"C:\CAD tables backup\CAD tables working\CAD tables\Wells.xls")

mxd = arcpy.mapping.MapDocument(r"C:\test.mxd")
df = arcpy.mapping.ListDataFrames(mxd, "Layers")[0]
lyr = arcpy.mapping.ListLayers(mxd, "Wells", df)[0]
arcpy.mapping.ExportReport(lyr,
                           r"C:\CAD tables backup\CAD tables working\Reports\Wells2.rlf",
                           r"C:\CAD tables backup\CAD tables working\CAD tables\Wells2.xls")

mxd = arcpy.mapping.MapDocument(r"C:\test.mxd")
df = arcpy.mapping.ListDataFrames(mxd, "Layers")[0]
lyr = arcpy.mapping.ListLayers(mxd, "Wells", df)[0]
arcpy.mapping.ExportReport(lyr,
                           r"C:\CAD tables backup\CAD tables working\Reports\Wells3.rlf",
                           r"C:\CAD tables backup\CAD tables working\CAD tables\Wells3.xls")

mxd = arcpy.mapping.MapDocument(r"C:\test.mxd")
df = arcpy.mapping.ListDataFrames(mxd, "Layers")[0]
lyr = arcpy.mapping.ListLayers(mxd, "Wells", df)[0]
arcpy.mapping.ExportReport(lyr,
                           r"C:\CAD tables backup\CAD tables working\Reports\Wells4.rlf",
                           r"C:\CAD tables backup\CAD tables working\CAD tables\Wells4.xls")

del mxd

However, when I run the script I get the following error message:

Executing: Script2
Start Time: Mon Dec 09 12:26:51 2013
Running script Script2...

Traceback (most recent call last):
File "C:\New_script.py", line 15, in <module>
    r"C:\CAD tables backup\CAD tables working\CAD tables\Wells.xls")
File "c:\program files (x86)\arcgis\desktop10.2\arcpy\arcpy\utils.py", line 181, in fn_
    return fn(*args, **kw)
File "c:\program files (x86)\arcgis\desktop10.2\arcpy\arcpy\mapping.py", line 515, in ExportReport
    return report_source._arc_object.ExportReport(*gp_fixargs((report_layout_file, output_file, dataset_option, report_title, starting_page_number, page_range, report_definition_query, extent, field_map), True))
IOError: Could not open report template

Failed to execute (Script2).
Failed at Mon Dec 09 12:26:51 2013 (Elapsed Time: 0.19 seconds)

I hope someone can help me please.

Thanks

Re: Run a script to generate a report and export to excel

StacyRendall1 — Sun, 12 Dec 2021 16:44:36 GMT

I have a few guesses:

C:\CAD tables backup\CAD tables working\Reports\Wells.rlf file doesn't exist

C:\CAD tables backup\CAD tables working\CAD tables\ folder doesn't exist

C:\CAD tables backup\CAD tables working\CAD tables\Wells.xls file already exists

I also note that your code attempts to export the exact same thing four times; shouldn't the index increase, like so:

mxd = arcpy.mapping.MapDocument(r"C:\test.mxd")
df = arcpy.mapping.ListDataFrames(mxd, "Layers")[0]
lyr = arcpy.mapping.ListLayers(mxd, "Wells", df)[0]
arcpy.mapping.ExportReport(lyr,
r"C:\CAD tables backup\CAD tables working\Reports\Wells.rlf",
r"C:\CAD tables backup\CAD tables working\CAD tables\Wells.xls")

###
lyr = arcpy.mapping.ListLayers(mxd, "Wells", df)[1]
arcpy.mapping.ExportReport(lyr,
r"C:\CAD tables backup\CAD tables working\Reports\Wells2.rlf",
r"C:\CAD tables backup\CAD tables working\CAD tables\Wells2.xls")

###
lyr = arcpy.mapping.ListLayers(mxd, "Wells", df)[2]
arcpy.mapping.ExportReport(lyr,
r"C:\CAD tables backup\CAD tables working\Reports\Wells3.rlf",
r"C:\CAD tables backup\CAD tables working\CAD tables\Wells3.xls")

###
lyr = arcpy.mapping.ListLayers(mxd, "Wells", df)[3]
arcpy.mapping.ExportReport(lyr,
r"C:\CAD tables backup\CAD tables working\Reports\Wells4.rlf",
r"C:\CAD tables backup\CAD tables working\CAD tables\Wells4.xls")

Re: Run a script to generate a report and export to excel

VictoriaWyeth — Tue, 10 Dec 2013 05:34:42 GMT

Thanks for your reply.

In answer to your guesses:

1). I have checked and Wells.rlf does exist and in the correct location.
2). The folder does exist
3). The Wells.xls file doesn't exist at this location.

So none of these are the answer unfortunately. However, I have edited the code so the index increases like you mentioned. My new code reads

# Import arcpy module
import arcpy

# Local variables:
Wells = "Wells"

# Process: Select Layer By Attribute

mxd = arcpy.mapping.MapDocument(r"C:\test.mxd")
df = arcpy.mapping.ListDataFrames(mxd, "Layers")[0]
lyr = arcpy.mapping.ListLayers(mxd, "Wells", df)[0]
arcpy.mapping.ExportReport(lyr,
                           r"C:\CAD tables backup\CAD tables working\Reports\Wells.rlf",
                           r"C:\CAD tables backup\CAD tables working\CAD tables\Wells.xls")

###
lyr = arcpy.mapping.ListLayers(mxd, "Wells", df)[1]
arcpy.mapping.ExportReport(lyr,
                           r"C:\CAD tables backup\CAD tables working\Reports\Wells2.rlf",
                           r"C:\CAD tables backup\CAD tables working\CAD tables\Wells2.xls")

###
lyr = arcpy.mapping.ListLayers(mxd, "Wells", df)[2]
arcpy.mapping.ExportReport(lyr,
                           r"C:\CAD tables backup\CAD tables working\Reports\Wells3.rlf",
                           r"C:\CAD tables backup\CAD tables working\CAD tables\Wells3.xls")

###
lyr = arcpy.mapping.ListLayers(mxd, "Wells", df)[3]
arcpy.mapping.ExportReport(lyr,
                           r"C:\CAD tables backup\CAD tables working\Reports\Wells4.rlf",
                           r"C:\CAD tables backup\CAD tables working\CAD tables\Wells4.xls")

I now receive a new error message which reads:

Traceback (most recent call last):
File "C:\New_script.py", line 18, in <module>
    lyr = arcpy.mapping.ListLayers(mxd, "Wells", df)[1]
IndexError: list index out of range

Do you have any ideas?

Thanks for your help

Re: Run a script to generate a report and export to excel

StacyRendall1 — Tue, 10 Dec 2013 21:52:32 GMT

The index increasing was to get the next layer for your report to be created on, previously you were creating the exact same report four times, you need to change one of the inputs to the report export tool, my guess is that it was the layer. It may be the data frame which changes...?

Can you run the process manually in Arc? I mean, open the tool, select the same inputs and output locations and run it. Does that work correctly? If it does, open the Results window (Geoprocessing menu > Reults), right click on the just-completed operation and select Copy as Python snippet. You can then paste this into your editor and check that it looks the same as what you have...

If that doesn't work, hopefully someone who has experience with this tool sees this post, I am out of ideas!

Re: Run a script to generate a report and export to excel

T__WayneWhitley — Sun, 12 Dec 2021 03:54:17 GMT

It's your 2nd ListLayers command that failed, apparently at least 2 layers were not fetched, in other words the layer you are expecting to list at [1] does not exist and for this particular run of the script, that is what the error means (whenever you see 'IndexError: list index out of range', this means you're trying to call from the results in the list by index a member that is not there hence 'out of range').

First of all, repeated calls to ListLayers is bad form - if you make one call, you get the entire list of layer objects from the map at once. So, it may make more sense to reference the individual layers something like this:

lyrs = arcpy.mapping.ListLayers(mxd, "Wells", df)

# did you fetch anything?:
print 'Number of layers successfully fetched: ' + str(len(lyrs))

# print the lyr names you just fetched, if any:
for lyr in lyrs:
     print lyr.name

# now access them individually for further processing:
for i in range(len(lyrs)):
     lyr = lyrs  # this sets the lyr variable to layer index i in the range
     print lyr.name + ' can now be processed.'
     # you can add code for further processing here

Hope that helps, at least in troubleshooting the return on ListLayers.

Wayne