none
Not able to add more than 65472 named ranges RRS feed

  • Question

  • Hi Guys,

    I was creating a lot of Named Ranges in a workbook using OpenXML SDK 2.0.

    But it only allows me to add up to 65472 ranges.

     

    Here's the code snippet I'm using to add the named ranges to my excel file.

     

     

     

    If I add more than 65472 rows. the excel gets generated but i get an error when i open the generated excel file.

    Is this the limit to the number of named ranges(65472) i can add in an Excel workbook? Or, has it got something to do with the Open XML SDK 2.0 code?

    Thanks,

    Manish Mahajan

    using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))
    
    
    
    {
    
    
    
    int rangeIndex; 
    
    
    
    WorkbookPart workbook = spreadSheet.WorkbookPart;
    
    
    
    DefinedNames names = new DefinedNames();
    
    
    
    //max number of ranges allowed = 65472
    
    
    
    for (rangeIndex = 1; rangeIndex <= 65472; rangeIndex++)
    
    
    
    names.Append(new DefinedName("\'" + SHEETNAME + "\'!$A$" + rangeIndex.ToString()) { Name = FIXEDRANGENAME + "A" + rangeIndex.ToString(), LocalSheetId = (UInt32Value)0U });
    
    
    
    workbook.Workbook.InsertBefore<DefinedNames>(names, workbook.Workbook.ChildElements.First<CalculationProperties>());
    
    
    
    spreadSheet.WorkbookPart.Workbook.Save();
    
    
    
     
    
    
    
    }
    
    
    
    
    • Edited by manish Mahajan Wednesday, June 17, 2009 8:39 AM The Title reads better
    Wednesday, June 17, 2009 8:22 AM

All replies

  • Hi Manish,
    I'm investigating it. that might be a deviation of the spec from Offfice constraint.
    Wednesday, June 17, 2009 9:46 AM
  • Hi goodol,

    Any update on this issue?

    It is required of us to generate really large workbooks which will contain several ranges and formulas. we are planning to use OOXML for this. If Excel 2007 has a limitation of being able to add only 65472 named ranges at a worksheet level then that could create some issues.

    Regards,
    Shamanth
    Thursday, June 18, 2009 6:57 PM
  • I have the same problem.

    Monday, October 17, 2011 10:24 PM