Hi,
I'm trying to open an excel file with external references, modify some cells and then save as a new file on disk. When doing this, formulas with external references get modified with a bad path/filename.
You'll see attached a sample project and in the two next posts sample excel files exhibiting the issue. Place the excel files into the project folder (along with the "add excel files here.txt" file) and run the project (BankOrder.xlsx is referencing equities.xlsx)
For example cell B2 is being modified from:
=RECHERCHEV($A2;'C:\Projets\Tests\Excel Export\Infragistics\InfragisticsExcelExport\InfragisticsExcelExport\[equities.xlsx]Equities'!$A2:$D$10000;2)
to:
=RECHERCHEV($A2;'C:\Projets\Tests\Excel Export\Infragistics\InfragisticsExcelExport\InfragisticsExcelExport\bin\Debug\[1]Equities'!$A2:$D$10000;2)
I'm using version 11.2.20112.2050 of Infragistics components.
I'm also experiencing the issue described here : http://community.infragistics.com/forums/t/53201.aspx (some cells become black) but I can't reproduce it with a simple file/project, I'll post another message about this once I find a working sample, unless you already have a solution for it
I'm answering to myself; using the latest version 2012.1 seems to solve the issue.
But I noticed something strange which still remains (in version 2011.2 and 2012.1) : The file size of the saved file is a lot smaller than the original one. This doesn't happen when running the same code with Microsoft's Excel Interop. And the cells containing external data show "#N/A" until updating the link to external references.
Could this mean that Excel is maintaining a cache of these external values (explaining the big size of the original file) and Infragistics' Excel gets rid of this cache thus reducing the file size but making Excel unable to retrieve cached data (and consequently could lead to the inability to reload this saved file in another process for further processing until it passes through Excel and gets the cache filled back).
Regards,
There are a number of reasons why the file size could be different. Most likely, we are using a different zipping algorithm and compression strength than Excel uses. Also, any files in the zip structure for which we don't have round-trip support would be lost, such as pivot table related files.
However, we do maintain the cached value when round-tripping. In fact, we regenerate the value with our own formula solving engine and save that out. So chances are we calculated an #N/A value and saved that out as the cached value. Can you access the cell in question through the Excel library's object model and retrieve the value? Is it also #N/A?
Whether it is calculated correctly or not, there is either a bug when calculating or when saving the cached value, so I have forwarded this post to the Developer Support Manager and a DS engineer will be contacting you about this issue.