如何使用C#更新数据透视表数据源?

问题描述

我想知道如何更新现有的数据透视表数据源.我正在使用 Microsoft.Office.Interop.Excel ，并使用Excel 2010定位用户.

I would like to know how to update an existing pivot table data source. I am using Microsoft.Office.Interop.Excel and targeting users with Excel 2010.

我目前能够刷新枢纽分析表，但工作正常，但是当添加更多行时，我想将这些行包含在枢纽分析表数据源中.例如，在Excel中查看数据透视表数据源为 DataSourceWorksheet！$ A $ 2:$ U $ 26 ，我希望将其更改为 DataSourceWorksheet！$ A $ 2:$ U $ 86(另外60行)在我的更新/刷新excel文件代码运行之后.

I am currently able to refresh 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 update/refresh excel file code has run.

这是我的代码的简化版本

Here's 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) */

一个可行的解决方案是使用 wrkBook.PivotTableWizard(...)再次重新生成数据透视表.但是，这不适用于我的情况，因为用户希望通过更改选定的字段，行，列等来修改其数据透视表.只需在数据源工作表更改时进行更新即可.

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

推荐答案

非常简单.

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

仅此而已.

请记住，总是需要提供行和列的格式.

Remember, always we need to give Row and Column format.

例如

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

即"SheetName！R1C1:R12C13" 这样.

如果您这样输入"SheetName！$ A $ 1:$ Q $ 18" ，它将无法正常工作.

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

这篇关于如何使用C#更新数据透视表数据源?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！