Accessing Workbook Name Manager Entries with SpreadJS

Accessing Workbook Name Manager Entries with SpreadJS

Posted by: luisdaniel.madrigal on 8 July 2025, 12:35 pm EST

• 

  luisdaniel.madrigal

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 8 July 2025, 12:35 pm EST - Updated 8 July 2025, 12:47 pm EST

  Hi Mescius Support Team,

  I’m working with SpreadJS and TypeScript, and I’m trying to get all the named ranges defined at the workbook level, not just from individual worksheets.

  Specifically, I need to:

  • Get the name of each named range.
  • Retrieve the corresponding range reference, ideally in A1-style format (e.g.,

    $C$25:$H$27

    ).

  I wrote the following code to extract the info from

  getCustomNames()

  and manually calculate the cell reference:

  function getWorkbookNameInfos() : WorkbookNameRangeInfo[] | null {
      if(!_workbook ){
          return null;
      }
      const nameInfos = _workbook.getCustomNames();
      const workbookNameInfos: WorkbookNameRangeInfo[] = nameInfos.map((nameInfo: GC.Spread.Sheets.NameInfo) => {
          const rangeExpression = nameInfo.getExpression();
          return {
              //@ts-ignore
              sheetName: rangeExpression.source.getName(),
              //@ts-ignore
              rangeName: nameInfo.getName(),
              //@ts-ignore
              dimensions: {rows: rangeExpression.getRange().rowCount, columns: rangeExpression.getRange().colCount},
              //@ts-ignore
              startRow: rangeExpression.row,
              //@ts-ignore
              startColumn: rangeExpression.column,
              //@ts-ignore
              endRow: rangeExpression.endRow,
              //@ts-ignore
              endColumn: rangeExpression.endColumn,
              //@ts-ignore
              startRowRelative: rangeExpression.rowRelative,
              //@ts-ignore
              startColumnRelative: rangeExpression.columnRelative,
              //@ts-ignore
              endRowRelative: rangeExpression.endRowRelative,
              //@ts-ignore
              endColumnRelative: rangeExpression.endColumnRelative,
              //@ts-ignore
              cells: calculatesWorkbookCellRange(
                  rangeExpression.row,
                  rangeExpression.column,
                  rangeExpression.endRow,
                  rangeExpression.endColumn,
                  rangeExpression.rowRelative,
                  rangeExpression.columnRelative,
                  rangeExpression.endRowRelative,
                  rangeExpression.endColumnRelative
              ),
          };
      });
  
      return workbookNameInfos;
  }
  
  function calculatesWorkbookCellRange(startRow: number, startColumn: number, endRow: number, endColumn: number, startRowRelative: boolean, startColumnRelative: boolean, endRowRelative: boolean, endColumnRelative: boolean): string {
      if(!_workbook ){
          return '';
      }
      function getColumnLetter(col: number): string {
          let letter = '';
          col += 1;
          while (col > 0) {
              let rem = (col - 1) % 26;
              letter = String.fromCharCode(65 + rem) + letter;
              col = Math.floor((col - 1) / 26);
          }
          return letter;
      }
  
      const startCell = `${getColumnLetter(startColumn)}${startRow + 1}`;
      const endCell = `${getColumnLetter(endColumn)}${endRow + 1}`;
      return startCell === endCell ? startCell : `${startCell}:${endCell}`;
  }
  

  This approach works, but I had to use a lot of

  @ts-ignore

  due to some internal properties not being exposed in the typings. I’m also calculating the A1-style range string manually.

  My question:

  Is there an official or built-in method in SpreadJS to directly get the A1-style range reference from a named range or expression, without having to manually calculate it?
  
  And is there a TypeScript-safe way to access these properties?

  Thanks again for the support!

  Best regards,
  
  Luis

• 

  priyam.kushwaha

  

  • Post Options:
  • Link
    Copy

  Posted 9 July 2025, 4:59 am EST

  Hi,

  Regarding “Is there an official or built-in method in SpreadJS to directly get the A1-style range reference from a named range or expression, without having to manually calculate it?”, you can use the rangeToFormula method to convert a Range object into an A1-style reference. See the example below:

  GC.Spread.Sheets.CalcEngine.rangeToFormula(
    new GC.Spread.Sheets.Range(startRow, startColumn, endRow, endColumn)
  ); // Returns a string like "$A$1:$B$2"

  Regarding “Is there a TypeScript-safe way to access these properties?”,
  
  getExpression API only retrieves an expression instance, which may not necessarily be a range—it could be any expression type and does not guarantee a specific format, so it has no type defined. You could use typecasting Expression as any.

  Regards,
  
  Priyam

• 

  luisdaniel.madrigal

  • Post Options:
  • Link
    Copy

  Posted 9 July 2025, 10:53 am EST

  Thank you so much for the clear and helpful response — it works perfectly!
  
  Really appreciate the support and quick guidance.

  Best regards,