Setting an existing tables data source to named range

Aspose.Cells Product Family
1

In EXCEL, I have the ability to set a named range and then set the Pivot Table Data Source to that named range.
The problem I am having is that I am able to programmatically update the named range using ASPOSE but the pre-existing PIVOT table has already inherited / accepted the data / values represented by the old named range and does not take the new named range values.

- How would I set the Pivot Table Data Source to point to that named range
In Excel -> PivotTable Tools -> Change Data Source -> Select a table or range -> Table/Range: MyNamedRange

2

Hi,


Please call pivotTable.RefreshData(); before saving the workbook. This may solve your problem. Thank you
3

This does not solve my problem.
I am attaching my sample solution. For some reason, this sample solution is causing the newly created EXCEL document to have a corrupted pivot table where in my much bigger solution the same code is creating a document without any problems.


4

Hi,


Thank you for the sample solution. We are looking into it, soon we will update you here.

Note: As you have written, I am getting a corrupt Pivot Table each time I run the solution.
5

Hi,


I have used your Template file [Template.xlsx] from your other post to get this scenario work. Please find below the sample source form your provided solution with modifications in BOLD. Also, I have attached the input and output files for your reference. Please feel free to write back if you have more queries.

C# Sample Code

string templatePath =Environment.CurrentDirectory+ ConfigurationManager.AppSettings[“MngfulDiffDocument”];

string exportFolder = ConfigurationManager.AppSettings[“ExportFolder”];

DataTable meaningfulDifferenceDataTable = null;

string dataFileLocation = ConfigurationManager.AppSettings[“DataDocument”];

string fullFilePath = Environment.CurrentDirectory + dataFileLocation;

Workbook wb = new Workbook(fullFilePath);

Worksheet worksheet = wb.Worksheets[0] ;

meaningfulDifferenceDataTable = worksheet.Cells.ExportDataTableAsString(0, 0, worksheet.Cells.MaxRow + 1, worksheet.Cells.MaxColumn + 1);

using (FileStream fstream = new FileStream(“C:\temp\template.xlsx”, FileMode.Open, FileAccess.Read))

{

Workbook workbook = new Workbook(fstream);

WorksheetCollection worksheets = workbook.Worksheets;

Worksheet dataFinalSheet = worksheets.GetSheetByCodeName(“DataFinal”);

dataFinalSheet.Cells.ImportDataTable(meaningfulDifferenceDataTable, true, “A1”);

dataFinalSheet.FreezePanes(1, 1, 1, 1);

string autoFilterRange = string.Format(“A1:{0}”, CellsHelper.CellIndexToName(1, dataFinalSheet.Cells.MaxDataColumn));

dataFinalSheet.AutoFilter.Range = autoFilterRange;

dataFinalSheet.IsVisible = true;

dataFinalSheet.Cells.DeleteBlankRows();

Worksheet pivotTableSheet = workbook.Worksheets.GetSheetByCodeName(“PVT”);

PivotTable pivotTable = pivotTableSheet.PivotTables[0];

pivotTable.IsAutoFormat = true;

PivotFieldCollection pivotFieldCollection = pivotTable.PageFields;

for (int i = 0; i < pivotFieldCollection.Count; i++)

{

pivotFieldCollection[i].IsAutoSort = true;

pivotFieldCollection[i].IsAscendSort = true;

pivotFieldCollection[i].AutoSortField = -1;

pivotFieldCollection[i].BaseField = 1;

pivotFieldCollection[i].BaseItemPostion = PivotItemPosition.Next;

}

pivotTable.ShowDrill = false;

pivotTable.RefreshDataFlag = true;

pivotTable.RefreshData();

pivotTable.SaveData = false;

pivotTable.RefreshDataOnOpeningFile = true;

workbook.Worksheets.ActiveSheetIndex = 0;

workbook.Save(“C:\temp\out.xlsx”, SaveFormat.Xlsx);

}


6

Thank you for your help Babar but this does not quite get me the solution I am looking for. Here is my dilemma.
In an EXCEL template file I manually create a named range using EXCEL:
MngfulDiffAnalysisDataSource
and set the MngfulDiffAnalysisDataSource named range to point to data in my DataFinal worksheet at a huge range:
=DataFinal!$A$1:$V$50000
I then set the pivot table data source to point to that named range:
MngfulDiffAnalysisDataSource

Within my application, when I insert data into the DataFinal sheet, I would like to update the named range to coordinate exactly with the size of my data.
I am able to update the named range and when I open the final document, I have successfully updated the MngfulDiffAnalysisDataSource
=DataFinal!$A$1:$V$7573

But when I open the pivot table data source, the value is now:
=DataFinal!$A$1:$V$50000

I wanted the pivot table data source to remain pointed to MngfulDiffAnalysisDataSource or at least =DataFinal!$A$1:$V$7573

7

Hi Pouya Yousefi,

We are sorry, your problem is still unresolved. We have forwarded it to our team and also logged it in our database. We will reply you as soon as possible.

This issue has been logged as CELLSNET-27956.

8

I am also intersted in this feature, please let me know when it is fixed.

9
Hi,

This issue is fixed in the latest Aspose.Cells for .NET version 5.3.3.3 [attached].
Please add this sample code in UpdatePivotTable method to clear the value in source cells .

private static void UpdatePivotTable(Workbook workbook)
{
Worksheet pivotTableSheet = workbook.Worksheets.GetSheetByCodeName("MeaningfulDifference");
PivotTable pivotTable = pivotTableSheet.PivotTables[0];
pivotTable.IsAutoFormat = true;
PivotFieldCollection pivotFieldCollection = pivotTable.PageFields;
for (int i = 0; i < pivotFieldCollection.Count; i++)
{
pivotFieldCollection[i].IsAutoSort = true;
pivotFieldCollection[i].IsAscendSort = true;
pivotFieldCollection[i].AutoSortField = -1;
pivotFieldCollection[i].BaseField = 1;
pivotFieldCollection[i].BaseItemPostion = PivotItemPosition.Next;
}
pivotTable.ShowDrill = false;
pivotTable.RefreshData();
//Please add this line
pivotTable.CalculateData();
pivotTable.RefreshDataFlag = true;
//SetPivotTablePrintArea(pivotTableSheet);
}

10

That did the job.
Thanks!

11

The issues you have found earlier (filed as 27956) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.