docs.sheetjs.com/docz/static/mui/dg/App.tsx

118 lines
4.5 KiB
TypeScript
Raw Permalink Normal View History

2023-05-11 06:17:10 +00:00
import React, { useCallback, useEffect, useState, ChangeEvent } from "react";
import { DataGrid, GridColDef, GridRowModel } from "@mui/x-data-grid";
2022-09-03 10:02:45 +00:00
import { read, utils, WorkSheet, writeFile } from "xlsx";
import './App.css';
type DataSet = { [index: string]: WorkSheet; };
type Row = any[];
type RowCol = { rows: Row[]; columns: GridColDef[]; };
function arrayify(rows: any[]): Row[] {
return rows.map(row => {
if(Array.isArray(row)) return row;
var length = Object.keys(row).length;
for(; length > 0; --length) if(row[length-1] != null) break;
return Array.from({length, ...row});
});
}
/* this method returns `rows` and `columns` data for sheet change */
const getRowsCols = ( data: DataSet, sheetName: string ): RowCol => ({
rows: utils.sheet_to_json<Row>(data[sheetName], {header:1}).map((r,id) => ({...r, id})),
columns: Array.from({
length: utils.decode_range(data[sheetName]["!ref"]||"A1").e.c + 1
}, (_, i) => ({ field: String(i), headerName: utils.encode_col(i), editable: true }))
});
export default function App() {
const [rows, setRows] = useState<Row[]>([]); // data rows
const [columns, setColumns] = useState<GridColDef[]>([]); // columns
const [workBook, setWorkBook] = useState<DataSet>({} as DataSet); // workbook
const [sheets, setSheets] = useState<string[]>([]); // list of sheet names
const [current, setCurrent] = useState<string>(""); // selected sheet
/* called when sheet dropdown is changed */
function selectSheet(name: string) {
/* update workbook cache in case the current worksheet was changed */
workBook[current] = utils.aoa_to_sheet(arrayify(rows));
/* get data for desired sheet and update state */
const { rows: new_rows, columns: new_columns } = getRowsCols(workBook, name);
setRows(new_rows);
setColumns(new_columns);
setCurrent(name);
}
/* this method handles refreshing the state with new workbook data */
async function handleAB(file: ArrayBuffer): Promise<void> {
/* read file data */
const data = read(file);
/* update workbook state */
setWorkBook(data.Sheets);
setSheets(data.SheetNames);
/* select the first worksheet */
const name = data.SheetNames[0];
const { rows: new_rows, columns: new_columns } = getRowsCols(data.Sheets, name);
setRows(new_rows);
setColumns(new_columns);
setCurrent(name);
}
/* called when file input element is used to select a new file */
async function handleFile(ev: ChangeEvent<HTMLInputElement>): Promise<void> {
const file = await ev.target.files?.[0]?.arrayBuffer();
if(file) await handleAB(file);
}
/* when page is loaded, fetch and processs worksheet */
useEffect(() => { (async () => {
2024-04-26 04:16:13 +00:00
const f = await fetch("https://docs.sheetjs.com/pres.numbers");
2022-09-03 10:02:45 +00:00
await handleAB(await f.arrayBuffer());
})(); }, []);
/* method is called when one of the save buttons is clicked */
function saveFile(ext: string): void {
console.log(rows);
/* update current worksheet in case changes were made */
workBook[current] = utils.aoa_to_sheet(arrayify(rows));
/* construct workbook and loop through worksheets */
const wb = utils.book_new();
sheets.forEach((n) => { utils.book_append_sheet(wb, workBook[n], n); });
/* generate a file and download it */
writeFile(wb, "SheetJSMUIDG." + ext);
}
2023-05-11 06:17:10 +00:00
const processRowUpdate = useCallback((rowNew: GridRowModel, rowOld: GridRowModel) => {
for(var j = 0; j < columns.length; ++j) if(rowNew[j] != null) rows[rowNew.id][j] = isNaN(+rowNew[j]) ? rowNew[j] : +rowNew[j];
setRows(rows);
return rowNew;
}, [columns, rows]);
2022-09-03 10:02:45 +00:00
return (
<>
<h3>SheetJS × MUI Data Grid Demo</h3>
<input type="file" onChange={handleFile} />
{sheets.length > 0 && ( <>
<p>Use the dropdown to switch to a worksheet:&nbsp;
<select onChange={async (e) => selectSheet(sheets[+(e.target.value)])}>
{sheets.map((sheet, idx) => (<option key={sheet} value={idx}>{sheet}</option>))}
</select>
</p>
<div className="flex-cont"><b>Current Sheet: {current}</b></div>
<div style={{width:"100%", height:400}}>
2023-05-11 06:17:10 +00:00
<DataGrid columns={columns} rows={rows} processRowUpdate={processRowUpdate} />
2022-09-03 10:02:45 +00:00
</div>
<p>Click one of the buttons to create a new file with the modified data</p>
<div className="flex-cont">{["xlsx", "xlsb", "xls"].map((ext) => (
<button key={ext} onClick={() => saveFile(ext)}>export [.{ext}]</button>
))}</div>
2023-02-28 11:40:44 +00:00
</> )}
2022-09-03 10:02:45 +00:00
</>
);
}