How to update a PivotTable data source using C #?

Question

I would like to know how to update an existing pivot table data source. I use Microsoft.Office.Interop.Excel and target users to Excel 2010.

I can currently update the pivot table, which works fine, however, when more rows are added, I want to include these rows in the pivot table data source. For example, the pivot table data source when viewed in Excel is DataSourceWorksheet!$A$2:$U$26 , and I would like it to be changed to DataSourceWorksheet!$A$2:$U$86 (60 more rows) after my excel update / update file was running.

Here is a simplified version of my code

 Application excelApplication = new Application(); Workbooks workbooks = excelApplication.Workbooks; wrkBook = workbooks.Open(EXCEL_DOCUMENT_PATH, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); /* update data source worksheet code here (omitted for example) */ Worksheet pivotWorkSheet = (Worksheet)wrkBook.Sheets[PIVOT_SHEET_NAME]; PivotTable pivot = (PivotTable)pivotWorkSheet.PivotTables(pivotTableName); /* I would like to update the pivot tables data source here... I could use Range object from data source worksheet... Basically just want to tell pivot table, use these cells now... */ pivot.RefreshTable(); /* cleanup code here (omitted for example) */ 

A solution that might work would be to restore the wrkBook.PivotTableWizard( ... ) table again using wrkBook.PivotTableWizard( ... ) . However, this will not work for my situation, as users will prefer to change their pivot table by changing the selected fields, rows, columns, etc. It just needs to be updated when the data source worksheet changes.

Answer 1

The solution to this problem was to use the Dynamic named range in excel for the Pivot Table data source. The Dynamic named Range formula uses a combination of the OFFSET function and the COUNTA function as follows:

 =OFFSET(DataSourceWorksheet!$A$2,0,0,COUNTA(DataSourceWorksheet!$A$2:$A$1000),1) 

The above formula will result in a range of cells inside A2: A1000 that have data. In my case, it took me more than one column and I have more row ranges to validate the data.

Now in C # to create a named range, I used the following:

 Names wrkBookNames = wrkBook.Names; string dynamicRangeFormula = "=OFFSET(DataSourceWorksheet!$A$2,0,0,COUNTA(DataSourceWorksheet!$A$2:$A$1000),1)"; wrkBookNames.Add("MyNamedRange", dynamicRangeFormula ); 

and then specify this named range as the data source when creating a new pivot table, I just simply pass the string value of the range name to the PivotTableWizard method:

 //get range for pivot table destination Range pivotDestinationRange = pivotWorkSheet.get_Range("A1", Type.Missing); wrkBook.PivotTableWizard( XlPivotTableSourceType.xlDatabase, "MyNamedRange", pivotDestinationRange, pivotTableName, true, true, true, true, Type.Missing, Type.Missing, false, false, XlOrder.xlDownThenOver, 0, Type.Missing, Type.Missing ); 

Now that rows are added or deleted in the DataSourceWorkSheet, the pivot table will only reference cells with data values ​​using the Named Range. Updating the pivot table now works as expected by calling RefreshTable:

 PivotTable pivot = (PivotTable)pivotWorkSheet.PivotTables(pivotTableName); pivot.RefreshTable(); 

In general, this will occur as part of the method for creating a new Excel workbook or updating an existing workbook. The call to this method for an existing workbook is now based on the fact that the existing pivot table was created using a dynamic named range and will be updated accordingly.

Answer 2

its very simple ..

 pivot.SourceData = "SheetName!R1C1:R18C18" pivot.RefreshTable(); 

That's all..

Remember that you always need to specify the format of rows and columns.

eg.

 "SheetName!" + R + Rowstartingnumber + C + Column StartingNumber : R + Rowendnumber + C + Column Endnumber. 

those. "SheetName!R1C1:R12C13" .

If you give "SheetName!$A$1:$Q$18" like this, this will not work.