Inserting Excel Tables into Data Driven Pages

[mcdka004]

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!

[davidwatkins]

Hi Karyn,

Do you have access to the Production Mapping extension? There is a dynamic table called the Graphic Table Element that is part of this extension that works well in conjunction with data driven pages. It makes it possible to include a table on your map layout that updates based on the content of the current page extent.

David

[Publiguias]

Please refer to the following links for more details regarding GTE:

What is a graphic table element?
http://help.arcgis.com/en/arcgisdesk...009n000000.htm

Creating an interactive table
http://help.arcgis.com/en/arcgisdesk...000006v000000/

[mcdka004]

Hi, Sorry for the delayed response. Thanks very much for your replies.

We don't have the Production Mapping extension but I am downloading the free trial and will have a play with it.

Is there any way of doing this without the production mapping extension?
Thanks again,
Karyn

[jbarrette]

Not easily. It can be done with arcpy.mapping but it would require that you author a table outline in ArcMap using line graphics. Then you would populate dynamic text to fill the columns in the table. I'm working on getting a sample out on the resource center. Once I get data permissions, I'll put it up as soon as I can.

Attached is a static table graphic (of grouped line elements). There are actually 3 tables, each with 3 columns. I have a total of 3 text elements, one for each column in each table. I read the rows from a GDB table and dynamically populate the text element with line breaks (to create the rows). I can fit up to 15 rows of data onto each table. If there are more than 15 rows, I populate the next table, etc.

Here is the code that populates the table:

for row in allRows:
    if count < 15:
      tab1Col1Txt.text = tab1Col1Txt.text + row.getValue("DATE") +"\n"
      tab1Col2Txt.text = tab1Col2Txt.text + row.getValue("CHANGE") + "\n"
      tab1Col3Txt.text = tab1Col3Txt.text + row.getValue("MADE_BY") + "\n"
    if count >= 15 and count < 30:
      tab2Col1Txt.text = tab2Col1Txt.text + row.getValue("DATE") + "\n"
      tab2Col2Txt.text = tab2Col2Txt.text + row.getValue("CHANGE") + "\n"
      tab2Col3Txt.text = tab2Col3Txt.text + row.getValue("MADE_BY") + "\n"
    if count >= 30 and count < 45:
      tab3Col1Txt.text = tab3Col1Txt.text + row.getValue("DATE") + "\n"
      tab3Col2Txt.text = tab3Col2Txt.text + row.getValue("CHANGE") + "\n"
      tab3Col3Txt.text = tab3Col3Txt.text + row.getValue("MADE_BY") + "\n"

Jeff

[slcemcphee33]

I would really like to see a sample of this on the Resource Center. I do not have the Production Mapping extension either, but I have a need to update a table in a layout based upon my data driven pages. Can you post your entire python code?

[jbarrette]

A sample has been placed onto the Resource Center.

http://resources.arcgis.com/gallery/...8-6AA61BF57894

Jeff

[newell.kate]

Is there a way to insert a word document based on a specific page in a map document?

Not easily. It can be done with arcpy.mapping but it would require that you author a table outline in ArcMap using line graphics. Then you would populate dynamic text to fill the columns in the table. I'm working on getting a sample out on the resource center. Once I get data permissions, I'll put it up as soon as I can.

Attached is a static table graphic (of grouped line elements). There are actually 3 tables, each with 3 columns. I have a total of 3 text elements, one for each column in each table. I read the rows from a GDB table and dynamically populate the text element with line breaks (to create the rows). I can fit up to 15 rows of data onto each table. If there are more than 15 rows, I populate the next table, etc.

Here is the code that populates the table:

for row in allRows:
    if count < 15:
      tab1Col1Txt.text = tab1Col1Txt.text + row.getValue("DATE") +"\n"
      tab1Col2Txt.text = tab1Col2Txt.text + row.getValue("CHANGE") + "\n"
      tab1Col3Txt.text = tab1Col3Txt.text + row.getValue("MADE_BY") + "\n"
    if count >= 15 and count < 30:
      tab2Col1Txt.text = tab2Col1Txt.text + row.getValue("DATE") + "\n"
      tab2Col2Txt.text = tab2Col2Txt.text + row.getValue("CHANGE") + "\n"
      tab2Col3Txt.text = tab2Col3Txt.text + row.getValue("MADE_BY") + "\n"
    if count >= 30 and count < 45:
      tab3Col1Txt.text = tab3Col1Txt.text + row.getValue("DATE") + "\n"
      tab3Col2Txt.text = tab3Col2Txt.text + row.getValue("CHANGE") + "\n"
      tab3Col3Txt.text = tab3Col3Txt.text + row.getValue("MADE_BY") + "\n"

Jeff

[jbarrette]

I don't know of a way to do this with Python. OLE objects don't have data source properties. A possible alternative would be to save the DOC as a high resolution time and insert it as a picture element. Picture elements have a .sourceImage property that can be changed as you go from page to page.

Jeff

[knoxgis]

Hi Karyn,

Do you have access to the Production Mapping extension? There is a dynamic table called the Graphic Table Element that is part of this extension that works well in conjunction with data driven pages. It makes it possible to include a table on your map layout that updates based on the content of the current page extent.

David

This is ridiculous, the cost of software, both initial and ongoing, and you want us to purchase yet another extension.

Looking for a way out of the ESRI quicksand......

[kltrooper]

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

[jbarrette]

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...bbd8daf2435852

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

[kltrooper]

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

[jbarrette]

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

[kltrooper]

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

[jbarrette]

Ken,

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

Jeff