Pivot Table values in Spreadsheet
Hi Nick,
We understand that you want to render the
Syncfusion Spreadsheet component using Pivot Table data upon a button click. To
achieve this, you can export the Pivot Table as an Excel document through the
toolbar UI, capture the blob data using the excelComplete event, and then use that
data to render the Spreadsheet component.
In the
code example below, enable the engine exporting using the allowEngineExport property within the dataBound event to process the blob
data. Then, upon button click, the excelExport() method
is triggered with the fourth parameter set to true to retrieve the blob data.
The blob data is then captured in the exportComplete() event,
and the Spreadsheet component is rendered using this data.
Code example:
|
dataBound(): void { // Enable engine Exporting here. this.pivotObj.allowEngineExport = true; } exportComplete(args: any) { if (args.promise !== null) { args.promise.then((e) => { // Store the blob data of pivot table here. this.base64String = e.blobData; // Render the spreedsheet with the pivot blob data here. let file: File = new File([this.base64String], 'Sample.xlsx'); this.spreadsheetObj.open({ file: file }); }); } } exportToSpreadSheet() { // Need to pass "isBlob" (4th parameter of this method) as true to get the blob data. this.pivotObj.excelExport({}, false, null, true); } |
Output screenshot:
Meanwhile, we have prepared a sample for your reference. In the below sample, click the "Export to SPREADSHEET" button to render the pivot values within Spreadsheet.
Please let us know if you have any concerns.
Regards,
Angelin Faith Sheeba.
Hi Nick,
Thanks for the patience. Please find the response below for your queries.
|
Query |
Comments |
||
|
However, I have one more question for you. In this way the excel export is done and the content of the exported file is loaded inside the spreadsheet. Is there any way to be able to take only the values from the table so that I can then place it from a cell of my choice?
I'll try to explain further. In my project I generate a Pivot Table and export its data inside a spreadsheet. Next, I generate a second Pivot Table and I would like the exported table to be put inside the same spreadsheet where the values from the first Pivot Table are also present. Basically, I would like, for example, the data from the first Pivot Table to be placed starting from cell B3, while the data from the second Pivot Table, for example, would be placed starting from cell H5. In what way could I achieve this? |
We understand that you need to display multiple pivot table values on the
same spreadsheet page. To achieve this, you can use the pivotValues property,
which holds the pivot cell value information. In the following code example,
we use an external button click to retrieve the pivot cell information from
the first pivot table (accessed via the pivot instance,
Code example:
Output Screenshots: Pivot Table:
Spreadsheet
Meanwhile, we have prepared a sample for your reference. You can find the sample at the following link.
|
||
|
I take this opportunity to ask one last thing. Is it possible then to make the entire table uneditable except for a random row chosen during configuration? So keep the entire spreadsheet editable except for the cells that make up the exported table, but one row in the table will have to be editable.
Taking the example from the previous answer, then have the ability to edit only the cells associated with the "France" row and not the others. But still leaving the ability to edit the empty cells that make up the spreadsheet (e.g. A8, A9, B8, B9. |
We understand that your requirement is to make only one row editable while keeping the remaining cells in the pivot table non-editable. If this is the case, you can achieve it by using the drillThrough event to prevent cell editing based on a specific condition. In the code example below, we have restricted editing for all pivot table rows except the France row. Please refer to the code example below for your reference.
Code example:
You can also restrict the editing of spreadsheet cells by using the isReadOnly property to prevent editing based on specific conditions when loading the pivot table values into the spreadsheet. In the code example below, we have restricted editing for all spreadsheet rows rendered by the pivot table values, except for the France row, while keeping the remaining spreadsheet rows (empty cells) editable. Please refer to the code example below for reference.
Code example:
Please refer to the below UG document to know more about to make
spreadsheet cells read-only.
Meanwhile, we have prepared a sample for your reference. You can find the sample at the following link.
|
Additionally, If you have any further requirements, you can use the spreadsheet functionality to customize the provided sample. For more information about the spreadsheet component and its feature, please refer to the documentation below.
Document: https://ej2.syncfusion.com/javascript/documentation/spreadsheet/overview
Please let us know if you have any concerns.
Regards,
Mohanraj Elumalai.
Hi Nick,
As we mentioned in our previous response, you can restrict the editing of
spreadsheet cells based on specific conditions by using the isReadOnly property
when loading pivot table values into the spreadsheet. We have already provided
a solution with an sample in the second query of our last response. However, we
are sharing the sample again for your reference.
Sample: https://stackblitz.com/edit/angular-mg76sh-ha5yv6?file=src%2Fapp.component.ts,src%2Fapp.component.html,src%2Fapp%2Fapp.config.ts
In the meantime, if you want to restrict cell editing to specific rows while
loading the Spreadsheet component with the exported Excel blob data from the
pivot table, you can use the beforeCellRender event in the
Spreadsheet component. In the code example below, within the beforeCellRender event,
we verify if the current cell is part of the "France" row. If
this condition is met, we prevent editing of the spreadsheet cell by setting
"args.cell.isReadOnly" to true.
Code example:
|
beforeCellRender(args) { if(args.row) { // Here we obtain the row header name (i.e., France) var rowHeader = args.row.querySelectorAll('td')[0].innerText; if(rowHeader == "France") { // If it does we restrict the cell editing args.cell.isReadOnly = true; } } } |
Output GIF Image:
Meanwhile, we have prepared a sample for your
reference. You can find the sample at the following link.
Sample: https://stackblitz.com/edit/angular-mg76sh-ghm8ko?file=src%2Fapp.component.ts,package.json,src%2Fapp.component.html
Please let us know if you have any concerns.
Regards,
Sridhar Karunakaran
