Access Result data / Export Result Data to json format

[akash-goel]

Hi Team,

we want to convert the table generated by Pivot Table into Json Structure and want to save that data in the server.

Can you please guide , how we can access the result/output data or convert the output data into json form .

Regards, Akash

[luizppa]

I would also like to have access to the output data.

I've been using document.querySelector('td.pvtOutput') to get the table and then turning that into a workbook. The problem is that it only gives me the values currently being displayed on the table, I would like to have access to the entire data of the rows that are present after applying the filters.

[luizppa]

I realized that you can use the PivotData class to achieve that. I experimented using two available functions: forEachMatchingRecord and forEachRecord.

forEachMatchingRecord takes a criteria that is based on a key value structure, so you can't really apply the same filters you might be applying to the pivot table — as far as I can tell, at least — which is why you might wanna use forEachRecord.

Here is what I got:

const [tableState, setTableState] = useState(uiProps);

return (
  <PivotTableUI
    data={data}
    onChange={(s) => {
      setTableState(s);
    }}
    // whatever else you need
    {...tableState}
  />
);

and then the function:

getFilteredRecords = () => {
  const filtered = [];
  if (tableState?.data) {
    PivotData.forEachRecord(
      tableState.data,
      tableState.derivedAttributes,
      (record) => {
        const match = Object.keys(tableState.valueFilter).every((key) => {
          // We are using !== true because PivotTable for some reason
          // makes its filters in a way that the items included in the
          // filter are the ones that are not included in the result
          const recordValue = record[key];
          return (
            recordValue &&
            tableState.valueFilter[key][recordValue] !== true
          );
        });
        if (match) {
          filtered.push(record);
        }
      },
    );
  }
  return filtered;
}

The return value will be an array with the data and derived attributes with filters applied.

Not sure if it is the correct or the best way to do it, but worked for me. PivotData seems to be pretty useful, would be nice if it was covered on the docs.

Of course, if you just want to get whatever is on the table (like the results of the aggregators) you can use the strategy I mentioned above of turning the table into a workbook using something like xlsx and just handle the data with the using the workbook. I did it like this:

import { utils } from 'xlsx';

const getWorkbook = (selector) => {
  const viewElement = document.querySelector('td.pvtOutput');
  const table = viewElement?.firstElementChild;
  if (table) {
    return utils.table_to_book(table);
  }
  return utils.book_new();
};