Inserting Excel Tables into Data Driven Pages

8779
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
DavidWatkins
Esri Contributor
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
0 Kudos
WilliamContreras
New Contributor
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/
0 Kudos
KarynMcDonald
New Contributor
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
0 Kudos
JeffBarrette
Esri Regular Contributor
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
0 Kudos
EricMcPhee
New Contributor III
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?
0 Kudos
JeffBarrette
Esri Regular Contributor
0 Kudos
KateNewell
New Contributor III
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
0 Kudos
JeffBarrette
Esri Regular Contributor
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
0 Kudos
BillLotz
Occasional Contributor II
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......
0 Kudos