Pivot Table corrupted after passing through ASPOSE in order to update data sheet

Aspose.Cells Product Family
1

I have an EXCEL 2007 template workbook (Template.xlsx) with 3 named worksheets: pivot table (PVT), data sheet (DataFinal), notes (Notes). The Pivot table datasource has a pre-existing named range. That named range points to the DataFinal sheet. My ASPOSE code writes a datatable to the DataFinal worksheet and set the range and refreshes the data:

Worksheet pivotTableSheet = workbook.Worksheets.GetSheetByCodeName("Pvt");
PivotTable pivotTable = pivotTableSheet.PivotTables[0];
Range range = dataFinalSheet.Cells.CreateRange(0, 0, dataFinalSheet.Cells.MaxDataRow + 1, dataFinalSheet.Cells.MaxColumn);
range.Name = "MngfulDiffAnalysisDataSource";
pivotTable.RefreshData();
workbook.Save(filePath);
When I open the final output: Final.xlsx, I get the following errors which also removes the pivot table:



- <recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">






  <logFileName>error104520_04.xml</logFileName> 



  <summary>Errors were detected in file 'C:\Users\Pyousefi\Desktop\MngfulDiffAnalysis2011_5_16_2011.xlsx'</summary> 





- <removedParts summary="Following is a list of removed parts:">






  <removedPart>Removed Part: /xl/pivotTables/pivotTable2.xml part. (PivotTable view)</removedPart> 



  </removedParts>










- <removedRecords summary="Following is a list of removed records:">






  <removedRecord>Removed Records: Workbook properties from /xl/workbook.xml part (Workbook)</removedRecord> 



  </removedRecords>








  </recoveryLog>




 
I think that this has something to do with creating a range with the name of a pre-existing range. 


How would I modify the pre-existing named range if that is the case?
2

Hi,

Well, MS Excel will not allow you to create a named range with existing name, you have to provide unique name for the named range. I think you for your need, you may try to rename the existing named range before creating the new named range with the same name, if it works fine.

E.g

PivotTable pivotTable = pivotTableSheet.PivotTables[0];

Range oldrange = workbook.Worksheets.GetRangeByName(“MngfulDiffAnalysisDataSource”);
oldrange.Name = “oldMngfulDiffAnalysisDataSource”;

Range range = dataFinalSheet.Cells.CreateRange(0, 0, dataFinalSheet.Cells.MaxDataRow + 1, dataFinalSheet.Cells.MaxColumn);

range.Name = “MngfulDiffAnalysisDataSource”;
Thank you.
3

Would I be able to do this?

	worksheets.RemoveAt(“MngfulDiffAnalysisDataSource”);

Range range = dataFinalSheet.Cells.CreateRange(0, 0, dataFinalSheet.Cells.MaxDataRow + 1, dataFinalSheet.Cells.MaxColumn);

range.Name = “MngfulDiffAnalysisDataSource”;
Doesn’t seem like I can.
Also, your suggestion does not seem to work either. I still get the same error.
4

Hi,

Kindly try our latest version v5.3.3:
http://www.aspose.com/community/files/51/.net-components/aspose.cells-for-.net/entry303217.aspx

If you still find any issue, do create a sample console application, zip it and post it here to reproduce the issue on our end, we will check it soon.

Thank you.

5

Hi,


We have just released Aspose.Cells for .NET v5.3.3.2 [attached]. Can you please test your scenario with this assembly and let us know of your feedback.

Thank you.
6

This works now but it does not accomplish my goal:
I just posted another question regarding my intent: #306631