Ability to download data to an excel file/csv

[husamrahmanh2o]

Is it possible to download the raw data thats in the table to an excel file/csv? Thank you!

[jassmith]

It would be easy enough to create an additional package to do this in the repo. Would you be willing to pitch in and help?

[husamrahmanh2o]

Appreciate the quick response :) Not sure I'd be the best person to take this on but the team might be able to pitch in. We are currently evaluating data grid offerings and plan to move forward on Monday with a solution. Love what you guys have built and all the features that are available. Are you free for a couple of minutes for a sync?

[jassmith]

Sure, email me at [email protected] and we will set something up.

[sitch]

I'm in the same boat as @husamrahmanh2o

[jassmith]

None of this is terribly hard, the basic plan is as follows

1. Create a new package glide-data-grid-export
2. Dep on xlsx
3. Create a hook which takes in your getCellContent or getCellsForSelection (Ideally the latter) and returns to you { exportToCsv: () => Promise<void>; exportToExcel: () => Promise<void>; }
4. Whenever you decide to export, just call those functions.

Honestly all the hard work has already been done by the xlsx folks, we don't really have to do anything too difficult. The hardest part will be if you want to export hundreds of thousands of cells at once we are going to have memory issues. We can do some work to scale up to tens of thousands without resorting to streaming but eventually you need a stream writer and for that we can't use xlsx.

For that we could use something like this: https://www.npmjs.com/package/xlsx-stream-writer

However that hasn't seen an update in 3 years and is clearly not maintained, and I don't really trust it. Bringing it into the project probably means maintaining it, so I think a better option is to actually have 2 packages.

1. glide-data-grid-export which is the normal export package using the much heavier but much more complex xlsx.
2. glide-data-grid-export-large which supports steaming exports of large data streams, but depends on this xlsx-stream-writer and is overall more likely to experience weird compatibility issues.

[dbnar2]

Has there been any updates on this by any chance?

[jassmith]

The original team interested in this seems to have moved along. I’m not planning to put effort to this until there are more interested parties.

On Sun, Oct 16, 2022 at 1:14 AM Narutooo @.***> wrote:

Has there been any updates on this by any chance?

— Reply to this email directly, view it on GitHub https://github.com/glideapps/glide-data-grid/issues/367#issuecomment-1279909569, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAAHN2Z2NA52QDQVKYRHYEDWDOTONANCNFSM5YHZPEYA . You are receiving this because you commented.Message ID: @.***>

[timuric]

We are using glide grid at saleor.io and one of the most requested features are export to csv and xls. We are probably going to build it ourselves, but if you would like a PR please let us know.

[jassmith]

A PR would be incredibly welcome.

[SheetJSDev]

First off, @jassmith awesome work with glide-data-grid!

Given that GDG isn't managing the underlying data source, import / export from specific sources are probably out of scope for the library. A small demo would suffice.

"Getting Started" uses a "flat array of objects". The export method would create a CSV or XLSX file from the array of objects directly. Following the docs convention (data array of objects outside of the FC, columns is an array of GridColumn objects in the FC):

/* imports */
import { utils, writeFileXLSX } from 'xlsx';

/* in the FC */
const exportXLSX = React.useCallback(() => {
  // generate worksheet using data with the order specified in the columns array
  const ws = utils.json_to_sheet(data, {header: columns.map(c => c.id || c.title), dense: true});
  // rewrite header row with titles
  utils.sheet_add_aoa(ws, [columns.map(c => c.title || c.id)], {origin: "A1"});
  // create workbook
  const wb = utils.book_new();
  utils.book_append_sheet(wb, ws, "Export"); // replace with sheet name
  // download file
  writeFileXLSX(wb, "output.xlsx");
});

/* in the render function */
  ( <button onClick={exportXLSX}>Export to XLSX</button> )

Exporting from a dataset with streaming updates should work the same way, since the first step in "Streaming Data" is "Update the data backing store".

Testing against a CRA blank app, the final gzipped size increases by 42KB.

Note: for large datasets, exporting from a web worker would avoid blocking the UI, but support is currently limited to Chromium browsers.

[jassmith]

I'll update my demo if you'll updates yours... waggles eyebrows

[SheetJSDev]

Review and we'll add it when we refresh the data grids page.

Project Setup (click to show)

a) Create a new project with npm create vite@latest. When prompted:

• Project name: … sheetjs-gdg
• Select a framework: › React
• Select a variant: › TypeScript

b) Install dependencies:

cd sheetjs-gdg
npm i
npm i --save https://cdn.sheetjs.com/xlsx-latest/xlsx-latest.tgz
npm i --save @glideapps/glide-data-grid lodash marked
npm run dev

Replace src/App.tsx with:

import { useState, useCallback, useEffect, useRef, ChangeEvent } from 'react'
import { utils, read, writeFileXLSX, WorkBook } from 'xlsx'
import { DataEditor, GridCellKind, GridCell, GridColumn, Item, DataEditorRef, EditableGridCell } from '@glideapps/glide-data-grid'
import "@glideapps/glide-data-grid/dist/index.css"

// this will store the raw data objects
let data: any[] = [];
// this will store the header names
let header: string[] = [];

function App() {
  const [cols, setCols] = useState<GridColumn[]>([]); // gdg column objects
  const [rows, setRows] = useState<number>(0); // number of rows
  const ref = useRef<DataEditorRef>(null); // gdg ref

  // read/write between gdg and the backing data store
  const getContent = useCallback((cell: Item): GridCell => {
    const [col, row] = cell;
    return {
      kind: GridCellKind.Text,
      allowOverlay: true,
      readonly: false,
      displayData: String(data[row]?.[header[col]]??""),
      data: data[row]?.[header[col]],
    };
  }, []);
  const onCellEdited = useCallback((cell: Item, newValue: EditableGridCell) => {
    const [ col, row ] = cell;
    data[row][header[col]] = newValue.data;
  }, []);

  // update the data store from a workbook object
  const parse_wb = (wb: WorkBook) => {
    const sheet = wb.Sheets[wb.SheetNames[0]];
    data = utils.sheet_to_json<any>(sheet);
    const range = utils.decode_range(sheet["!ref"]??"A1"); range.e.r = range.s.r;
    header = utils.sheet_to_json<string[]>(wb.Sheets[wb.SheetNames[0]], {header: 1, range})[0];
    setCols(header.map(h => ({title: h, id: h} as GridColumn)));
    setRows(data.length);
    if(data.length > 0) {
      let cells = data.map(
        (_,R) => Array.from({length:header.length}, (_,C) => ({cell: ([C,R] as Item)}))
      ).flat();
      ref.current?.updateCells(cells)
    }
  };
  // file input element onchange event handler
  const onChange = useCallback(async (e: ChangeEvent<HTMLInputElement>) => {
    if(!e.target?.files) return;
    parse_wb(read(await e.target.files[0].arrayBuffer()));
  }, []);
  // when the component loads, fetch and display a sample workbook
  useEffect(() => {
    (async() => {
      parse_wb(read(await (await fetch("https://sheetjs.com/pres.numbers")).arrayBuffer()));
    })();
  }, []);

  // export data
  const exportXLSX = useCallback(() => {
    // generate worksheet using data with the order specified in the columns array
    const ws = utils.json_to_sheet(data, {header: cols.map(c => c.id ?? c.title)});
    // rewrite header row with titles
    utils.sheet_add_aoa(ws, [cols.map(c => c.title ?? c.id)], {origin: "A1"});
    // create workbook
    const wb = utils.book_new();
    utils.book_append_sheet(wb, ws, "Export"); // replace with sheet name
    // download file
    writeFileXLSX(wb, "sheetjs-gdg.xlsx");
  }, []);

  return ( <>
    <input type="file" onChange={onChange} />
    <button onClick={exportXLSX}><b>Export XLSX!</b></button>
    <div className="App">
      <DataEditor getCellContent={getContent} columns={cols} rows={rows} onCellEdited={onCellEdited} ref={ref}/>
    </div>
    <div id="portal"></div>
    </>
  )
}

export default App

To test, load the page and you will see something like:

Suppose you disagree with the counts (President Cleveland should have been de-duped). You can manually update the indices with the editor.

Clicking "Export XLSX!" will attempt to download a file named sheetjs-gdg.xlsx based on the updated sheet data:

sheetjs-gdg.xlsx

[jassmith]

We will get a GDG side demo posted shortly and I will also review yours with my team.

[SheetJSDev]

https://docs.sheetjs.com/docs/demos/grid/#glide-data-grid first cut, including explanations for each piece of the puzzle.

The storybook/docs should be revisited. For example, the examples in "Getting Started" use a data store outside of the component. "Editing Data" actually creates a ref with useRef, but that is not explained in the body text.

Please feel free to leave feedback at the docs repo