Excel external data source cached values

[Jeff_B]

Aspose is able to access data cached in local Excel files from previous connections to no longer available external data sources.

Per Workbook.UpdateLinkedDataSource Method I understand that the cached data will be used unless a specific call to UpdateLinkedDataSource is made.

Is there a way to check on the existence of that cache? Or access the cache directly?

I would like to test the Excel file to know if it contains cached values prior to converting it to a PDF. We generate HTML previews of Excel files by reading the raw datasets of each worksheet and these will display the cached values. We also create PDF’s using non-Aspose libraries depending on the user’s requirements. These PDF do not always reflect the cached values.

Thank you.

[Amjad_Sahi]

Hi,

Well, Aspose.Cells may update the formulas to get the calculated results in the template Excel file having external links or datasource etc., but it might not accurately get the calculated values though. I think you may try to use Workbook.UpdateLinkedDataSource () method and try opening your underlying source workbook in it, see the sample code below:

e.g

Sample code:

//Suppose this workbook has an external reference to “e:\test\source.xlsx” e.g it has some cell which as formula whose value is comming from this file.

Workbook wb = new Workbook(“e:\test\dest.xlsx”);

Workbook wbSrc = new Workbook(“e:\test\source.xlsx”);

wbSrc.Worksheets[0].Cells[“C8”].PutValue(10);

wbSrc.CalculateFormula();

MessageBox.Show(wbSrc.Worksheets[0].Cells[“C9”].StringValue);

// wb.Initialize();

wbSrc = new Workbook(“e:\test\source.xlsx”);

wb.UpdateLinkedDataSource(new Workbook[] { wbSrc });

wb.CalculateFormula();

MessageBox.Show(wb.Worksheets[0].Cells[“C10”].StringValue);

wb.Save(“e:\test\outdest.xlsx”);

If you do not use or call CalculateFormula() method it will use the existing values in the Workbook in the cells before all types of conversions e.g Excel to PDF, Sheet to Image etc. For example, you have formulas in the sheet cells, you update values for the source cells for the formulas, now if you do not call Workbook.CalculateFormula() before saving the Workbook to PDF, it will not calculate the formulas to get the updated values (for those formulas) in the rendered PDF file format.

Hope, you understand it now.

Thank you.

[Jeff_B]

Yes, but is there a way to directly access those cached values in the referencing workbook? In our situation the externally referenced data source is not available so we do not want to try and update the values. We want that cached value if it is available. Aspose renders the cached values when reading the data from the sheet into a datatable or when rendering a PDF. That is great. But I would like to identify if any values are being read from a cache so the user can be warned that some displayed values may not be consistent with what is shown on other renderings of the same workbook.

[Amjad_Sahi]

Hi,

Thanks for providing further details.

I have logged a ticket with an id “CELLSNET-42714” into our database for your issue/ requirements. We will check if we could support your desired feature, i.e., Retrieve cached value if it is available, Identify if a value is read from a cache. We will look into it soon.

Once we have any update on it, we will let you know here.

Thank you.

[Amjad_Sahi]

Hi,

We have evaluated your issue/ requirements a bit.

We think you do no need to access the cache directly.<o:p></o:p>

1) "But I would like to identify if any values are being read from a cache so the user can be warned that some displayed values may not be consistent with what is shown on other renderings of the same workbook."

Please use Cell.ContainsExternalLink property to check whether a cell contains external link.

2) " I would like to test the Excel file to know if it contains cached values prior to converting it to a PDF. We generate HTML previews of Excel files by reading the raw datasets of each worksheet and these will display the cached values. We also create PDF's using non-Aspose libraries depending on the user's requirements. These PDF do not always reflect the cached values."

It means that the values of the cells are correct if you can read the cached values by reading the raw datasets of the checck worksheet.

If the generated PDF file do not reflect the cached values, the formulas must be recalculated and incorrect values are returned.

Please check which libraries recalculated the file?

Thank you.