-1

Suppose I have the following Excel pivot table:

Excel pivot table

The definition of the pivot table from pivotTable1.xml contains the following relevant snippets:

<location ref="I8:M26" firstHeaderRow="1" firstDataRow="2" firstDataCol="1" rowPageCount="1" colPageCount="1"/>

<dataFields count="1">
<dataField name="Custom Sum of Amount" fld="3" baseField="1" baseItem="0"/>
</dataFields>

Given these pieces of data, together with the rest of the definition, is it possible to accurately determine the cell (I8) where the "Custom Sum of Amount" is placed? Unfortunately I couldn't find something helpful in the SpreadsheetML specification and I don't want to rely on unbacked assumptions about its location.

Also, it would very helpful for me if I could also determine the cell location for the other auto-generated captions, such as "Custom Row Labels Caption", "Custom Column Labels Caption" and "Custom Grand Total Caption".

Gabriel S.
  • 1,347
  • 11
  • 31
  • Can I ask why you want to identify it? Just want to see if there is another way to achieve what it is you want to do. – jeffreyweir Oct 06 '16 at 19:29
  • @jeffreyweir I'm working on an application that allows users to modify the contents of a cell in a pivot table. After they do it, the table will be auto-refreshed in the new Excel file (using the refreshOnLoad attribute in pivot cache definition). However, i want to retain the value written by users in the cells corresponding to data field names so that i can update the name attribute of the dataField tag besides auto-refreshing the table. Otherwise the new value will be lost. – Gabriel S. Oct 07 '16 at 07:42

1 Answers1

-1

Work out the Values field that you want the info for, then use: pf.LabelRange.Address

jeffreyweir
  • 4,668
  • 1
  • 16
  • 27