Aspose.Cells features

Aspose.Cells Product Family
1

Hi,


I am evaluating Aspose.Cells for .NET to calculate multiple Excel workbooks. I have following questions to determine whether or not Aspose.Cells is correct choice for me.

1) I created Excel workbook with simple pivot table. When source data is updated, I want to update Pivot table data. Following is code I am using to refresh Pivot table data using Aspose.Cells. But Pivot table data is not refreshing. Does Aspose.Cells support refreshing Pivot table data and how?

Worksheet sheet2 = workbook.Worksheets.First(w => w.Name == “Sheet2”);

PivotTable pivotTable = sheet2.PivotTables[0];

pivotTable.RefreshDataFlag = true;

pivotTable.RefreshData();

pivotTable.RefreshDataFlag = false;
2) I have multiple Excel workbooks and they reference each other's data. Is there way in Aspose.Cells to calculate all Workbooks collectively such way that Aspose.Cells handles data dependency?

Thanks for considering my questions! Please let me know if you need more clarification.

--Amit
2

Hi,


Thanks for your queries.

1) Aspose.Cells would refresh and calculate Pivot Tables whose data source is the current workbook (and not an external data source in other workbook(s), Aspose.Cells does not support to calculate PivotTables whose data source is external). You may try adding a line (in bold) to your code to see if it makes any difference, see the updated code segment:
e.g
Sample code:

Worksheet sheet2 = workbook.Worksheets.First(w => w.Name == “Sheet2”);
PivotTable pivotTable = sheet2.PivotTables[0];
pivotTable.RefreshDataFlag = true;
pivotTable.RefreshData();
pivotTable.CalculateData();
pivotTable.RefreshDataFlag = false;

2) Well, you may try Workbook.UpdateLinkedDataSource() method for your needs. The method specifies if workbook contains external links to other data source/workbook, Aspose.Cells
will try to retrieve the latest data. See the sample code segment below.
e.g
Sample code:

Workbook book = new Workbook(“e:\test2\Book1.xlsx”);
Workbook book2 = new Workbook(“e:\test2\Book2.xlsx”);
//Suppose some values are updated in a cell e.g M2
book.Worksheets[0].Cells[“M2”].PutValue(400);
book.CalculateFormula();
MessageBox.Show(book.Worksheets[0].Cells[“M5”].DoubleValue.ToString());

book2.UpdateLinkedDataSource(new Workbook[] { book });
book2.CalculateFormula();
MessageBox.Show(book2.Worksheets[0].Cells[“G16”].StringValue); //this has formulas that involves other workbook, e.g Book1’s worksheet’s cell(s)

Hope, this helps a bit.

Thank you.
3

Hi Amjad,


Thanks for quick reply!

1) I tried pivotTable.CalculateData() along with pivotTable.RefreshData(). But pivot still didn’t refresh. I have attached my Excel worksheet. Can you please try it?

2) Yes, I saw UpdateLinkedDataSource method for updating external data. But I will have to determine order of calculation. I was wondering if Aspose.Cells has API that determines and calculates multiple workbooks. It sounds like I will need to determine workbook dependency externally and call CalculateFormula() on each workbook. Do you agree?

–Amit
4

Hi,


1) Well, this might be due to the fact that your underlying PivotTable has external reference as its data source. I am afraid, as I told you before Aspose.Cells does not refresh/calculate data in the PivotTable whose data source is external, the data source should be in the current workbook sheet. When I tried to refresh the PivotTable manually in MS Excel, I got the error, see the screenshot for your reference:
<a rel=“nofollow” href=“(”[[BL]]http://prntscr.com/b8m4f3[[/BL]]“>(”[[BL]]http://prntscr.com/b8m4f3[[/BL]]

2) Yes, I think your understanding is correct. Aspose.Cells will use the previous data (cached in the file) if you do not call Workbook.CalculateFormula() method explicitly.

Thank you.

5

Thanks for reply!


I will look into datasource for 1st issue. And thanks for clarification on 2nd point.

–Amit
6

Hi Amjad,


Even though you are getting error while refreshing pivot table from MS Excel, I didn’t create pivot table with external data. It’s how MS Excel 2013 creates it. How does Pivot table need be created in order to work with Aspose.Cells? Also, if you don’t mind, can you please provide me a example workbook with Pivot table that works with Aspose.Cells?

–Amit
7

Hi,


Well, as I said earlier, the PivotTable’s data source should refer to the range in the current workbook. I think when this PivotTable was created either by some tool or in MS Excel manually, data source (for the PivotTable) was provided from some external file’s worksheet cells (although same data range is pasted in the other worksheet “Sheet1” in your provided file). So, you need to change the PivotTable data source range and make its data source refer to current workbook’s sheet range of cells (e.g “Sheet1!A1:C25”). see the screenshots below:
<a rel=“nofollow” href=“(”[[BL]]http://prntscr.com/b8pmks[[/BL]]“>(”[[BL]]http://prntscr.com/b8pmks[[/BL]]
<a rel=“nofollow” href=“(”[[BL]]http://prntscr.com/b8pn52[[/BL]]“>(”[[BL]]http://prntscr.com/b8pn52[[/BL]]

Also, see the documents in the section for your complete reference on how to create/manipulate PivotTables via Aspose.Cells APIs.
Pivot Tables

Hope, this helps a bit.

Thank you.

8

Got it. Thanks!


–Amit