Access Result data / Export Result Data to json format #154

Activity

1. luizppa commented on Feb 13, 2024

   

   luizppa

   on Feb 13, 2024

   More actions

   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.
2. luizppa commented on Feb 14, 2024

   

   luizppa

   on Feb 14, 2024

   Last edited by luizppa

   More actions

   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();
   };