Inserting Excel Tables into Data Driven Pages

8786
15
05-18-2011 05:08 PM
KarynMcDonald
New Contributor
Hi,

I am creating a map series and would like to insert an excel table for each page. (in ArcMap10) It would be great to have the excel spreadsheet to dynamically update for each map page but I haven't been able to work out how to do this.   The way I am going about it (and probably not the most efficient) is by creating a table in excel for each map page.  I then copy and paste the excel table into word and save.  In ArcMap, I am then going Insert>Object and inserting the word document into the map (as a link). The table comes in correctly.  (I have tried to link directly to excel but it never works properly as it won't recognise the data range I am interested in).

When I insert the tables I insert them into data View rather than layout view in a logical location for each page.  This means that when I print the series, the tables will be different for each page.  I am however now having issues with my workspace and I am guessing that having lots of linked excel tables in an MXD is causing problems.  I could go back and copy and paste the tables as an image rather than a link but perhaps that will cause the similar problems. There are approximately 150 maps in each series (and we are doing about 17 series).  Not every map page has an attribute table, only about half.   Has anyone done something similar successfully or have a better solution?

Thanks very much for your help!
0 Kudos
15 Replies
KenLucas
Occasional Contributor
Jeff,
  I'm with the dept of water resources, state of Nv. We should just got set up with the Map Production Extension to access the add graphic element option. We've spent hours experimenting with the add graphic element option we now have avail and just are not getting the results we need.
We have two questions for you:
1. You made an earlier reply to this thread, saying that, once you have a table built on the DDP, you can just populate it with dynamic text. We'd like you clarify this statement. We understand you to mean we should use the Insert - add dynamic text option on the top toolbar, with the DDP enabled, and reference an attribute table from our index layer. However, this would require us to edit that field in the attribute table if we want to make updates to the data inserted on the DDP.
     Our question is: can you give us arcpy code to reference, or link, to a specific excel table for a specific DDP; in other words we'd like to have many excel tables linked to specific cells in the tables we build on many DDPs. We know we can set up a dynmaic link to one excel table for multiple DDPs, but how to do we set up a diff link to each of our DDPs?
2. We created an empty table structure, on a DDP, using the production mapping - add graphic element option, but we can't get it to link with an excel table that we have in our ToC for that DDP MXD. We've tried adjusted the Source tab in the graphic table properties. How do we link a graphic table element to a specific excel sheet for a specific page?
  Our goal is to have a seperate excel sheet assigned for each of our DDPs, so that we can quickly update specific cell data. We want to avoid having to edit many attribute fields.
Ken
0 Kudos
JeffBarrette
Esri Regular Contributor
Ken,

Data Driven Pages do not support dynamic tables.  This was something we hoped to accomplish in the UI for 10.1 (if time allowed).  But at 10.1 we were able to expand the arcpy.mapping API that allows us to build truly dynamic tables (graphically) using the new cloning capabilities.   The idea is to use scripting logic to dynamically build the graphic table by cloning horizontal and vertical lines that ultimately construct a table.  This logic is executed for each page in a series and therefore, each page can have a table that has a different number of rows, etc.  The text in the table is basic inserted text elements (not dynamic text in the sense of DDP but dynamic in the sense that you can alter it dynamically through code).  I typically have one text element for each column in a table.  Then using scripting logic, I populate that column of data by reading rows in a data source and concatenate each value with a new line in between each value.

The best and newest sample available can be found at:
http://www.arcgis.com/home/item.html?id=3a525b986b774a3f9cbbd8daf2435852

This method does not care what the data source is (e.g., DBF, excel).  If you can read the table in ArcMap, then you can write the information to the graphic table.

Download it and take a look at the code.

I really hope this helps,
Jeff
0 Kudos
KenLucas
Occasional Contributor
Jeff,
  Thanks for hanging in with my supervisor & I on this problem. We're really interested to emply the arcpy script you provided earlier to this initial thread to build the tables on a DDP and to check the source coding in your last reply. We just tried the link you gave us and it did not lead us to a useful page. Can you try another way to hook us up with the page?  Maybe, you could tell me the path I should follow into the ESRI website from my ESRI user log in "kltrooper".
Ken
0 Kudos
JeffBarrette
Esri Regular Contributor
One of the common issues people have with AGO content is that they need to change their "show" setting from "Web Content" to "All.

Try this, this will get you to the arcpy.mapping group where you can see many samples posted:

Go to: http://www.arcgis.com/home/
Then click the Groups link
In the search box, type "arcpy.mapping
Click on the one resulting link.
Make sure you change "Show" to all - it alone the top of the screen.
You should see all the (9) samples
The one you want is called "DDPwithDynamicTablesAndGraphs_10.1_v1"

I really hope this works for you,
Jeff
0 Kudos
KenLucas
Occasional Contributor
Jeff,
  Thanks for the pathway; it worked, we downloaded the data, and will work thru it, along with trying the source code you already gave us in our DDP. I noted your tip about setting show = all content
Ken
0 Kudos
JeffBarrette
Esri Regular Contributor
Ken,

Contact me at jbarrette@esri.com.  Maybe we can come up with a new sample to place on ArcGIS online.

Jeff
0 Kudos