Getting named range value

[Umair Mirza]

When I convert the excel into json. 

I see these 

Objects of the defined namedRange.   (Workbook.definedNames) If this is just one cell, how do i get the value?

If there isnt such a method I would like to get the value of the cells in this namedRange?

[Umair Mirza]

core.mjs:10194 ERROR Error: Uncaught (in promise): TypeError: Cannot read properties of undefined (reading 'frozenRows')

TypeError: Cannot read properties of undefined (reading 'frozenRows')

    at Workbook.frozenRowCount

 namedRanges.forEach(async (namedRange: DefineName) => {

      const val = await this.spreadsheetObj.getData(namedRange.refersTo);

      console.log(val);

    })

When I try to read value like this I get this error

[Vasanth Ravi]

Hi Umair,

We have prepared a sample for your requirement. In the below sample, we have rendered the spreadsheet component with set of defined names initially. On button click, we will fetch the namedRanges from rendered sheets. After fetching the name ranged, you can get the cell address for specified name range using getCell method. By using the getDisplayText method , we displayed the cell value on the console window. Attached sample and code block for your reference.

CODE BLOCK:

<ejs-spreadsheet[definedNames]="namedRange"> </ejs-spreadsheet>

 

<button class="e-btn" id="button" (click)="getNamedRangeValue($event)"> Get Value </button>

 

// Assign defined names to the data being loaded.

  public namedRange: DefineNameModel[] = [

    { name: 'sheet1range', refersTo: '=Sheet1!F2:F4' },

    { name: 'sheet2range', refersTo: '=Sheet2!F2:F4' },

    { name: 'Multiple', refersTo: '=Sheet1!A1:B2' },

  ];

 

getNamedRangeValue() {

    // Get the total number of defined names used.

    let namedRangeCount: number = this.spreadsheetObj.definedNames.length;

    // Iteration to display the value in defined range.

    for (let i = 0; i < namedRangeCount; i++) {

      // Fetch the assigned fedined name.

      let name: string = this.spreadsheetObj.definedNames[i].name;

      // Fetch the address of the defined name.

      let range: string = this.spreadsheetObj.definedNames[i].refersTo;

      // Get the sheet name from range address.

      let sheetName: string = range.split('!')[0].split('=')[1];

      // To get the available sheets details on the component.

      // let sheets = this.spreadsheetObj.sheets;

      let sheet = this.spreadsheetObj.sheets[0];

      let rangeIndex: number[] = getRangeIndexes(range);

      for (

        let rowCount = rangeIndex[0];

        rowCount <= rangeIndex[2];

        rowCount++

      ) {

        for (

          let colCount = rangeIndex[1];

          colCount <= rangeIndex[3];

          colCount++

        ) {

          // Fetch the cell for the specified row and column.

          const cell: CellModel = getCell(rowCount, colCount, sheet);

          // Display the value in console.

          console.log(name + ':', this.spreadsheetObj.getDisplayText(cell));

        }

      }

    }

  }

 

Stackblitz Sample: https://stackblitz.com/edit/angular-bbafsd-ml29p4?file=src%2Fapp.component.ts 

Also attached API links for reference.

https://ej2.syncfusion.com/angular/documentation/api/spreadsheet/#definednames

https://ej2.syncfusion.com/angular/documentation/api/spreadsheet/#getdisplaytext

Get back to us if you need any other further assistance.

NOTE: If that post is helpful, please mark it as an answer so that other members can locate it more quickly.