Hi there,
I've got a reasonably complicated Excel spreadsheet that I want to automatically calculate a whole load of values for me.
I've got code similar to the following which does this:
var workbook = Workbook.Load(@"c:\temp\workbook.xlsx");
workbook.SuspendCalculations();
FillDataRow(workbook.Worksheets[0], dataReader);
workbook.CalculationMode = CalculationMode.Automatic;
workbook.ResumeCalculations();
workbook.Recalculate();
// code to extract calculated values
workbook.Save(@"c:\temp\workbook - results.xlsx);
Hi Michael,
I've done a bit of investigation and can confirm that the problem definitely exists with named ranges. A single named cell works fine, so if A2 is named as Tax_Rate, Infragistics will calculate = A1 * Tax_Rate quite happily.
However, you can name a whole range rather than a cell e.g. A1..Z2 as Phonetic_Alphabet. But Infragistics doesn't like = VLookUp(H1, Phonetic_Alphabet, 2, False) - it simply returns #N/A
This is at the route of the problem. Fixing this should solve this part of my problem.
There are also other problems whereby it removes huge amounts of formatting - particularly foreground colours and conditional formatting for no apparent reason. And I also have huge issues with exporting large UltraGrids to Excel. But I guess I will raise those as separate concerns elsewhere.
Yes I will, but I've been snowed under due to a) having spent far too long trying to get the Infragistics stuff to work, and then b) far too long rewriting (successfully) using the Excel interop.
Coupled with the fact that the information within the spreadsheet his highly sensitive and needs obfuscating - meaning a sample may take a while.
Sorry, but I've got much higher priorities right now - but I will post something!
Cheers,
James
Are you going to be able to post a sample here?
The spreadsheet contains VLookup, DGet and formulae that use named ranges. They may be the cause of the problem.
Unfortunately much of the spreadsheet contains confidential information, and as such I am currently unable to post anything as is on a public forum.
Furthermore, I have had to completely re-engineer the code to use Excel Automation via COM as I have been under tight deadlines to get it operational.
However, I will do my best to recreate a sample for you over the next few days.
I have tested this myself and I am not seeing the behaviour you have described. Could you tell me in more detail what kind of calculations you are doing so that I can look into this further? Would it be possible for you to post here a simple sample application that reproduces this issue?