Get cell's value by defined name's range in React Spreadsheet

[Dawid Sibinski]

Hello,

I'm using the React Spreadsheet component.

I have some defined names added for some cells.

I can get the defined name by its name using the following code:

const definedName = editorInstance.definedNames.find((n) => n.name === nameOfTheDefinedName)!;

Now I can get its `refersTo` property:

const referenceForCellWithDefinedName = definedName.refersTo;

which returns the cell's address in the following form: '=Sheet1!$J$5'

How can I get the cell's value, having its address as '=Sheet1!$J$5'? I didn't find any API method for achieving that.

The only method accepting cell's address in this format I found is getData, but it returns a JSON object. I need the text value of the cell.

Thanks for your help!

[Vasanth Ravi]

Hi Dawid,

We have prepared a sample to achieve your requirement. We bind a function with a button click, and within it, we have fetched the index value of the address got from defined name ( as mentioned ). Using the getCell() method, we fetched the cell and displayed the value using the getDisplayText() method. Please find the code block and sample attached below.

CODE BLOCK:

<button onClick={getCellValue.bind(this)}>Get Cell Value</button>

 

function getCellValue() {

        let cellIdx = getRangeIndexes("Car Sales Report!$A$8");

        let cell = getCell(cellIdx[0], cellIdx[1], spreadsheet.getActiveSheet());

        alert(spreadsheet.getDisplayText(cell));

    }

 

Stackblitz Sample: https://stackblitz.com/edit/react-pq9rpr?file=index.js

Attached api link for reference,

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

Get back to us if you need further assistance regarding.

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

[Dawid Sibinski]

Hi,

thank you, it's helpful, but not entirely. In this line:

let cell = getCell(cellIdx[0], cellIdx[1], spreadsheet.getActiveSheet());

you are providing the sheet as spreadsheet.getActiveSheet(), while the reference might point to the cell in another sheet. In your example with "Car Sales Report!$A$8" it doesn't work if the current active sheet is not "Car Sales Report".

[Vasanth Ravi]

Hi, David,

We have prepared a sample based on your requirement. Initially, we have rendered three sheets and stored them in a variable. We have just displayed the text in the active cell in the first sheet. You can modify it as per your requirement. Please find the code block and sample below.

CODE BLOCK:

function getCellValue() {

        let sheetNames = spreadsheet.sheets;

        var actCell = sheetNames[0].activeCell;

        let cellIdx = getRangeIndexes(actCell);

        let cell = getCell(cellIdx[0], cellIdx[1], sheetNames[0]);

        alert(spreadsheet.getDisplayText(cell));

}

Stackblitz Sample : https://stackblitz.com/edit/react-pq9rpr-n8vrca?file=index.js

Please get back to us if you need further assistance.

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

[Dawid Sibinski]

Hi @Vasanth Ravi,

I know this solution, but it doesn't do the job. Why would I need to take the sheet's name from `spreadsheet.sheets`? The sheet name is contained within the string `Car Sales Report!$A$8`. What's the way of getting the cell's display value having only a string `Car Sales Report!$A$8, independently of currently active sheet?

Are you suggesting that I need to manually parse the sheet's name out from the string `Car Sales Report!$A$8`?

I suppose the API methods should be handling that.

[Vasanth Ravi]

Hi Dawid,

On the previously shared solution –

function getCellValue() {

        let sheetNames = spreadsheet.sheets; //Get the sheet names loaded into spreadsheet.

        var actCell = sheetNames[0].activeCell; //Get active cell.

        let cellIdx = getRangeIndexes(actCell); //Fetch the index of the active cell.

        let cell = getCell(cellIdx[0], cellIdx[1], sheetNames[0]); //Fetch cellModel of active cell.

        alert(spreadsheet.getDisplayText(cell)); //Displays the cell text.

The getDisplayText() method accepts only the cellModel as a parameter. Therefore, we must get the sheet names from the loaded one and fetch the index value of the active cell of the sheet in which we need to display the text.

With the help of the fetched index value, we can get the cell model using the getCell() method. Once the cell model is fetched, it can be passed to the getDisplayText() method.

Please refer to the sample attached below.

Sample Link: https://stackblitz.com/edit/react-pq9rpr-n8vrca?file=index.js

API Link: https://ej2.syncfusion.com/react/documentation/api/spreadsheet/#getdisplaytext

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

[Dawid Sibinski]

Hi Vasanth,

I understand that it works like that currently.

However, in my example, the only thing that I have is this reference text: `Car Sales Report!$A$8`

This reference string is dynamic, I don't know what exactly I will receive. I don't know in which sheet this cell is located. However, all the needed information is in this string: `Car Sales Report!$A$8`:

- "Car Sales Report" - name of the sheet

- "!" - separator

- "$A$8" - cell reference

What I mean is that it would be great if there is an API method which would accept a string like `Car Sales Report!$A$8` and return a cellModel.

This format of a cell's reference (`Car Sales Report!$A$8`) is often used in Spreadsheet. I think it would be very useful and even necessary to provide API methods accepting it.

Currently, the only "workaround" is to split this string by the separator ("!"), so I can get a sheet's name and cell reference. Then I can use the method you provided above.

Let me emphasize it again - this request is to add - or direct me towards it if already exists - an API method accepting a string in format `Car Sales Report!$A$8` and returning a cellModel or cell's display value.

[Vasanth Ravi]

Hi Dawid,

We have prepared a sample that displays the cell value at the specified address. Initially, we have assigned the address to a variable statically. (As you have been getting the address on the defined names, you can pass it to a function and assign it dynamically.)

We have fetched the entire sheet model from the imported file and stored it in a variable. With the help of the getSheetNameFromAddress() method, we have fetched the sheet name and, using the getRangeIndexes() method, we fetched the index of the cell. 

Using a looping condition, we iterated between the sheets and, using a condition, we checked whether the sheet is present in the file or not. If it is present, we have fetched the cell model using the getCell method and displayed the value inside it.

Please refer to the code block and sample for your kind reference.

CODE BLOCK:

<button onClick={getCellValue.bind(this)}>Get Cell Value</button>

 

function getCellValue() {

        //Assigned the address that mentioned.

        let address = "Car Sales Report!$A$8";

        //Fecthed the entire list of sheets rendered into the spreadsheet.

        let entireSheet = spreadsheet.sheets;

        //Get the sheet name from the address. 

        let sheetName = getSheetNameFromAddress(address);

        //Get index of the address to fetch the value.

        let cellIdx = getRangeIndexes(address);

        //Looping between the sheets.

        for(let i = 0; i < entireSheet.length; i++){

            //Condition to check whethe the provided sheet is present in the list of rendered or not.

            if(entireSheet[i].name === sheetName){   

                //Get the cell model.

                let cell = getCell(cellIdx[0], cellIdx[1], entireSheet[i]);

                alert(cell.value);

            }

        }

    }

Stackblitz Sample: https://stackblitz.com/edit/react-pq9rpr-5yiklj?file=index.js

In additional to the sample, we also attached the video demonstration for better clarification. Please make a note of it.

Video Link: https://www.syncfusion.com/downloads/support/directtrac/general/ze/Forum_Video1497134873

Get back to us if you need further assistance regarding.

[Dawid Sibinski]

Hi Vasanth Ravi,

this works and does the job. Thank you for your help 

[Vasanth Ravi]

You're welcome, Dawid. We are glad to hear that the provided information helped you.