Using Infragistics3.Excel.v9.1 hotfix 2094, .NET 4.0
I am writing a spreadsheet that can exceed the Excel 2003 limit of 65536. The workbook is set to WorkbookFormat.Excel2007. It has some formulas in it, written like this:
var netDueFormula = Formula.Parse( String.Format( "=G{0}+H{0}", row + 1 ), CellReferenceMode.A1 ); netDueFormula.ApplyTo( currCell );
As soon as I exceed that limit, saving fails with the below error and stack trace. Covering any portion of the data used - as long as I am below 65536 rows - works. Any suggestions?
The name is not a valid named reference name.Parameter name: value (Infragistics3.Excel.v9.1)------------------------------Program Location: at Infragistics.Excel.NamedReferenceBase.SetNameInternal(String value, String paramName, WorkbookFormat currentFormat) at Infragistics.Excel.NamedReferenceBase.set_Name(String value) at Infragistics.Excel.Serialization.CurrentWorkbookReference.CreateNamedReference(String name, Object scope, Boolean hidden) at Infragistics.Excel.Serialization.WorkbookReferenceBase.GetNamedReference(String name, Object scope, Boolean hidden, Boolean createIfNotPresent) at Infragistics.Excel.FormulaUtilities.Tokens.NameToken.ResolveReferences(WorkbookSerializationManager manager) at Infragistics.Excel.Formula.ResolveReferences(WorkbookSerializationManager manager) at Infragistics.Excel.Serialization.WorkbookSerializationManager.InitializeReferences() at Infragistics.Excel.Serialization.Excel2007.Excel2007WorkbookSerializationManager.InitializeReferences() at Infragistics.Excel.Serialization.WorkbookSerializationManager.Save() at Infragistics.Excel.Workbook.SaveXLSXFile(Stream stream, IPackageFactory packageFactory) at Infragistics.Excel.Workbook.SaveHelper(Stream stream, IPackageFactory packageFactory) at Infragistics.Excel.Workbook.Save(String fileName, IPackageFactory packageFactory) at Infragistics.Excel.Workbook.Save(String fileName)
Hi,
bkavanaugh said:Removing the formulas does work, but then makes it a static worksheet.
Okay. I wasn't suggesting that as a solution to the problem, I just wanted to test to see if the problem is related to formulas.
I just tested this out by applying a formula to rows between 65530 and 65540 in v9.1 and I am getting the same exception.So this is obviously a bug in that version.
The bad news is that v9.1 has been retired and is no longer being updated. And I don't see any way to work around this bug.
The good news is that the problem does not occur in the latest version of NetAdvantage (10.3). So this was apparently fixed somewhere along the way. If you need formulas in rows past 65536, it looks like you will need to upgrade to the latest version.
Removing the formulas does work, but then makes it a static worksheet.
This isn't a grid export. It's a hand-generated worksheet from a DataSet. I can remove the formulas and put the already summed up numbers in the cell instead, but that kind of defeats the purpose of making it a spreadsheet.
Just as a test, try setting ExportFormulas on the UltraGridExcelExporter to False and see if the problem goes away.
I have the latest service release. There hasn't been one since April 2010. Yes, Excel 2007 raised the limit significantly, which is why I need to use this format.
I will see if I can trim this down to a sample project...