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...
Just as a test, try setting ExportFormulas on the UltraGridExcelExporter to False and see if the problem goes away.
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.
Removing the formulas does work, but then makes it a static worksheet.