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,
The 65536 limit is a limit of Excel. A single Excel worksheet cannot have more rows than that.
I beleive this limit has been raised in Excel2007 format, but it's possible there's a bug in v9.1 of the Infragistics Excel assembly that is not allowing this limitation to be exceeded.
So my first recommendation to you would be to get the latest service release and see if that corrects the exception.
How to get the latest service release - Infragistics Community
If that does not help, you could try updating to a newer version of NetAdvantage (you could download a trial version to make sure it fixes the issue).
Or another option would be for you to post a small sample project demonstrating the exception so we can check it out.
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...
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.