I want to use Spreadsheet control with Pivot table

[ASIM TAUSIF KHAN]

Can you please guide me how can i use spreadsheet with pivot table. For example is it possible to save spreadsheet data as json or csv in database not as file then open it in PIvot Table

[Sangeetha Priya Murugan]

Hi Asim,

You can save the spreadsheet data as JSON data by using the saveAsJson method and CSV by using the save method. Please refer to the below links.

https://ej2.syncfusion.com/aspnetcore/documentation/spreadsheet/open-save

https://ej2.syncfusion.com/javascript/documentation/api/spreadsheet/#saveasjson

https://ej2.syncfusion.com/javascript/documentation/api/spreadsheet/#save

Your requirement “To Load/Save the spreadsheet data as JSON in database” can be achievable in our spreadsheet by using the openFromJson and saveAsJson method. We have already demonstrated this in our knowledge base section itself. In this we have saved the spreadsheet data as jsonobject in database and open the saved jsonobject in spreadsheet. Please find the link below.

https://www.syncfusion.com/kb/11956/how-to-save-and-retrieve-the-spreadsheet-data-as-json-in-database

To Open/Save spreadsheet data as excel file the server.

https://www.syncfusion.com/kb/11970/how-to-open-and-save-an-excel-file-to-server-in-the-spreadsheet

Currently, we don’t have pivot table support in spreadsheet. However, we have already logged this feature request. You can track the status of this feature using the link below from our feedback portal. And it will be available in any of our upcoming releases. We appreciate your patience until then.

Feedback Portal Link: https://www.syncfusion.com/feedback/12651/pivot-table-support-in-ej2-spreadsheet

[ASIM TAUSIF KHAN]

Thank for you reply. I dont want pivot in spreadsheet. I want to know is there any method where i can save spreadsheet data in such manner into database that will also work in Pivot.

[Sangeetha Priya Murugan]

Hi Asim,

We suspect that you need to export the spreadsheet data as array of object (key value pair) and open that data in pivot table. And it can be achievable in our spreadsheet by using the getData method. Please refer to the below documentation to get the usedRange data as JSON object.

https://www.syncfusion.com/kb/13582/how-to-get-the-modified-data-as-key-value-pair-in-javascript-spreadsheet

https://ej2.syncfusion.com/documentation/api/spreadsheet/#getdata

You can save the spreadsheet data as CSV file using the File-> Save As -> .CSV option in toolbar. Or you can export it as CSV file using save method by specifying the file type as Csv in the argument. Please refer to the below link for your reference.

https://ej2.syncfusion.com/documentation/api/spreadsheet/#save

Please check the above link and get back to us, if you still need any further assistance on this.

[ASIM TAUSIF KHAN]

Can you make a sample project for me in .Net Core

[Sangeetha Priya Murugan]

Hi Asim,

Based on your requirement, we have prepared a sample that prints the modified spreadsheet data as JSON in the console window using the getData method using a button click event. And export the spreadsheet data as CSV using the save method in another button-click event, as shown below.

Code Block:

 

<button id="saveJson" class="e-btn">To get Data</button>

<button id="saveCSV" class="e-btn">To get CSV File</button>

<script>

    document.getElementById('saveCSV').addEventListener('click', function () {

        var ssObj = ej.base.getComponent(document.getElementById('spreadsheet'), 'spreadsheet');

        ssObj.save({ url: 'https://ej2services.syncfusion.com/production/web-services/api/spreadsheet/save', fileName: 'Worksheet', saveType: 'Csv' }); // Specifies the save URL, filename, file type need to be saved.

    }),

        document.getElementById('saveJson').addEventListener('click', function () {

            var ssObj = ej.base.getComponent(document.getElementById('spreadsheet'), 'spreadsheet');

            json = [];

            var obj = {};

            var ssObj = ej.base.getComponent(document.getElementById('spreadsheet'), 'spreadsheet');

            var usedRange = ssObj.getActiveSheet().usedRange;

            var selIndex = [1, 0, usedRange.rowIndex, usedRange.colIndex];

            var range =

                ssObj.getActiveSheet().name +

                "!" +

                ejs.spreadsheet.getRangeAddress([1, 0, selIndex[2] - 1, selIndex[3]]);

            ssObj.getData(range).then(

                (value) => {

                    (value).forEach(

                        (cell, key) => {

                            if (cell) {

                                // constructing the key value object

                                var indexes = ejs.spreadsheet.getRangeIndexes(key);

                                if (key.indexOf("A") > -1) {

                                    obj["employeeID"] = cell.value;

                                } else if (key.indexOf("B") > -1) {

                                    obj["lastName"] = cell.value;

                                } else if (key.indexOf("C") > -1) {

                                    obj["firstName"] = cell.value;

                                } else if (key.indexOf("D") > -1) {

                                    obj["title"] = cell.value;

                                } else if (key.indexOf("E") > -1) {

                                    obj["titleOfCourtesy"] = cell.value;

                                }

                                if (indexes[1] === selIndex[3]) {

                                    // row last index

                                    json.push(obj);

                                    obj = {};

                                }

 

                            }

                        }

                    );

                    console.log(json);

                })

        })

 

</script>

 

 

Please find the below attached sample

Attachment: coresample_b57a65dc.zip

[ASIM TAUSIF KHAN]

Thanks for the reply. what if i dont know the number of columns then how to do that.

[Sangeetha Priya Murugan]

Hi Asim,

In our previous update itself, we saved the JSON data for the whole sheet's range content. We have maintained the usedRange property in our sheet model that returns the used row index and column index. And we have created the sample based on that used range index only. So, you can use our above suggestion based on your requirement.

[ASIM TAUSIF KHAN]

Hi sangeetha,

                     Json data is ok in your sample.but you are making this data if you know the number or row and column.what if there is dynamic data i need to populate.then this sample is not helping.can you please guide me how to use the same sample with dynamic data

[Sangeetha Priya Murugan]

Hi Asim,

As we have stated earlier, the usedRange property returns the current used range in the sheet model. If you enter data dynamically, the usedRange property is also updated. So, before we proceed further, please share how you update data dynamically in the spreadsheet component. And please check the usedRange property after updating the data dynamically and kindly get back to us with those details. Or else, please replicate your issue in our previously shared sample and send it back to us. Based on that, we can be able to check and proceed further.

[ASIM TAUSIF KHAN]

I have concerned with attached file encircle code.how to do that part if i dont know the no off column .but i need the header in my json.

Attachment: Untitled_f9e9ef5f.7z

[Sangeetha Priya Murugan]

Hi Asim,

Based on your requirement, we have prepared the sample that construct the JSON data for the used range of cells as shown below.

      document.getElementById('saveJson').addEventListener('click', function () {

            var ssObj = ej.base.getComponent(document.getElementById('spreadsheet'), 'spreadsheet');

            json = [];

            var obj = {};

            var ssObj = ej.base.getComponent(document.getElementById('spreadsheet'), 'spreadsheet');

            var usedRange = ssObj.getActiveSheet().usedRange;

            var isFirstRow = true;

            var firstRowKeys = [];

            var selIndex = [0, 0, usedRange.rowIndex, usedRange.colIndex];

            var range =

                ssObj.getActiveSheet().name +

                "!" +

                ejs.spreadsheet.getRangeAddress([0, 0, selIndex[2], selIndex[3]]);

            ssObj.getData(range).then(

                (value) => {

                    (value).forEach(

                        (cell, key) => {

                            if (cell) {

                                // constructing the key value object

                                var indexes = ejs.spreadsheet.getRangeIndexes(key);

                                if (isFirstRow) {

                                    obj[cell.value] = cell.value;

                                    firstRowKeys.push(cell.value); // store the 1st row key values in a seperate array

                                } else obj[firstRowKeys[indexes[1]]] = cell.value;

                                if (indexes[1] === selIndex[3]) {

                                    // row last index

                                    json.push(obj);

                                    isFirstRow = false;

 

                                    obj = {};

                                }

                            }

                        }

                    );

                    console.log(json);

             })

Please find the attached sample below.

Attachment: coresample_69fb2a60.zip

[ASIM TAUSIF KHAN]

Thanks for the reply.One minor issue. date is not correct in json conversion.can you please check this.

[Sangeetha Priya Murugan]

Hi Asim,

We have maintained the parsed value for date formatted values in our cellModel and it is not an issue in our end. So, we would suggest you to use getDisplayText method to get the displayed value of the cell. For more details, please refer to the below link.

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