Sheets in DanfoJS

SheetJS is a JavaScript library for reading and writing data from spreadsheets.

DanfoJS is a library for processing structured data. It uses SheetJS under the hood for reading and writing spreadsheets.

This demo covers details elided in the official DanfoJS documentation.

Tested Deployments

This example was last tested on 2024 April 25 against DanfoJS 1.1.2.

Browser integration

The live demos on this page include the DanfoJS browser bundle:

  <script src="https://cdn.jsdelivr.net/npm/danfojs@1.1.2/lib/bundle.min.js"></script>

There are known issues with the documentation generator. If a demo explicitly prints "RELOAD THIS PAGE", please reload or refresh the page.

DataFrames and Worksheets

The DanfoJS DataFrame¹ represents two-dimensional tabular data. It is the starting point for most DanfoJS data processing tasks. A DataFrame typically corresponds to one SheetJS worksheet².

Spreadsheet	DanfoJS DataFrame
	╔════╤═══════════════╤═══════╗ ║ │ Name │ Index ║ ╟────┼───────────────┼───────╢ ║ 0 │ Bill Clinton │ 42 ║ ╟────┼───────────────┼───────╢ ║ 1 │ GeorgeW Bush │ 43 ║ ╟────┼───────────────┼───────╢ ║ 2 │ Barack Obama │ 44 ║ ╟────┼───────────────┼───────╢ ║ 3 │ Donald Trump │ 45 ║ ╟────┼───────────────┼───────╢ ║ 4 │ Joseph Biden │ 46 ║ ╚════╧═══════════════╧═══════╝

DanfoJS SheetJS Integration

The official documentation inconsistently names the library object danfo and dfd. Since dfd is the browser global, the demos use the name dfd.

Methods to read and write spreadsheets are attached to the main dfd object.

Importing DataFrames

readExcel³ accepts two arguments: source data and options.

The source data must be a string or File object. Strings are interpreted as URLs while File objects are treated as data.

Selecting a Worksheet

DanfoJS will generate a dataframe from one worksheet. The parser normally uses the first worksheet. The sheet property of the options object controls the selected worksheet. It is expected to be a zero-indexed number:

const first_sheet  = await dfd.readExcel(url, {sheet: 0});
const second_sheet = await dfd.readExcel(url, {sheet: 1});

More Parsing Options

The parsingOptions property of the options argument is passed directly to the SheetJS read method⁴.

For example, the sheetRows property controls how many rows are extracted from larger worksheets. To pull 3 data rows, sheetRows must be set to 4:

const first_three_rows = await dfd.readExcel(url, { parsingOptions: {
  // see https://docs.sheetjs.com/docs/api/parse-options for details
  sheetRows: 4
} });

URL source

The following example fetches a test file, parses with SheetJS and generates a DanfoJS dataframe.

function DanfoReadExcelURL() {
  const [text, setText] = React.useState("");
  React.useEffect(() => { (async() => {
    if(typeof dfd === "undefined") return setText("RELOAD THIS PAGE!");
    const df = await dfd.readExcel("https://docs.sheetjs.com/pres.xlsx");
    setText("" + df.head());
  })(); }, []);
  return (<pre>{text}</pre>);
}

File source

The following example uses a file input element. The "File API"⁵ section of the "Local File Access" demo covers the browser API in more detail.

function DanfoReadExcelFile() {
  const [text, setText] = React.useState("Select a spreadsheet");

  return (<><pre>{text}</pre><input type="file" onChange={async(e) => {
    if(typeof dfd === "undefined") return setText("RELOAD THIS PAGE!");

    /* get first file */
    const file = e.target.files[0];

    /* create dataframe and pretty-print the first 10 rows */
    const df = await dfd.readExcel(file);
    setText("" + df.head());
  }}/></>);
}

Exporting DataFrames

toExcel⁶ accepts two arguments: dataframe and options. Under the hood, it uses the SheetJS writeFile method⁷.

Exported File Name

The relevant property for the file name depends on the platform:

Platform	Property
NodeJS	filePath
Browser	fileName

The exporter will deduce the desired file format from the file extension.

Worksheet Name

The sheetName property specifies the name of the worksheet in the workbook:

dfd.toExcel(df, {
  fileName: "test.xlsx", // generate `test.xlsx`
  sheetName: "Export"    // The name of the worksheet will be "Export"
});

The DanfoJS integration forces the .xlsx file extension. Exporting to other file formats will require low-level operations.

More Writing Options

The writingOptions property of the options argument is passed directly to the SheetJS writeFile method⁸.

For example, the compression property enables ZIP compression for XLSX and other formats:

dfd.toExcel(df, {fileName: "export.xlsx", writingOptions: {
  // see https://docs.sheetjs.com/docs/api/write-options for details
  compression: true
}});

Export to File

The following example exports a sample dataframe to a XLSX spreadsheet.

function DanfoToExcel() {
  if(typeof dfd === "undefined") return (<b>RELOAD THIS PAGE</b>);
  /* sample dataframe */
  const df = new dfd.DataFrame([{Sheet:1,JS:2},{Sheet:3,JS:4}]);
  return ( <><button onClick={async() => {
    /* dfd.toExcel calls the SheetJS `writeFile` method */
    dfd.toExcel(df, {fileName: "SheetJSDanfoJS.xlsx", writingOptions: {
      compression: true
    }});
  }}>Click to Export</button><pre>{"Data:\n"+df.head()}</pre></> );
}

Low-Level Operations

DanfoJS and SheetJS provide methods for processing arrays of objects.

flowchart LR
  ws((SheetJS\nWorksheet))
  aoo[[array of\nobjects]]
  df[(DanfoJS\nDataFrame)]
  ws --> |sheet_to_json\n\n| aoo
  aoo --> |\njson_to_sheet| ws
  df --> |\ndfd.toJSON| aoo
  aoo --> |new DataFrame\n\n| df

Creating DataFrames

The DataFrame constructor⁹ creates DataFrame objects from arrays of objects. Given a SheetJS worksheet object, the sheet_to_json method¹⁰ generates compatible arrays of objects:

function ws_to_df(ws) {
  const aoo = XLSX.utils.sheet_to_json(ws);
  return new dfd.DataFrame(aoo);
}

Generating Files

toJSON¹¹ accepts two arguments: dataframe and options.

The format key of the options argument dictates the result layout. The column layout generates an array of objects in row-major order. The SheetJS json_to_sheet¹² method can generate a worksheet object from the result:

function df_to_ws(df) {
  const aoo = dfd.toJSON(df, { format: "column" });
  return XLSX.utils.json_to_sheet(aoo);
}

The SheetJS book_new method creates a workbook object from the worksheet¹³ and the writeFile method¹⁴ will generate the file:

const ws = df_to_ws(df);
const wb = XLSX.utils.book_new(ws, "Export");
XLSX.writeFile(wb, "SheetJSDanfoJS.xlsb", { compression: true });

The following demo exports a sample dataframe to XLSB. This operation is not supported by the DanfoJS toExcel method since that method enforces XLSX.

function DanfoToXLS() {
  if(typeof dfd === "undefined") return (<b>RELOAD THIS PAGE</b>);
  /* sample dataframe */
  const df = new dfd.DataFrame([{Sheet:1,JS:2},{Sheet:3,JS:4}]);
  return ( <><button onClick={async() => {
    /* generate worksheet */
    const aoo = dfd.toJSON(df, { format: "column" });
    const ws = XLSX.utils.json_to_sheet(aoo);

    /* generate workbook */
    const wb = XLSX.utils.book_new(ws, "Export");

    /* write to XLS */
    XLSX.writeFile(wb, "SheetJSDanfoJS.xlsb", { compression: true });
  }}>Click to Export</button><pre>{"Data:\n"+df.head()}</pre></> );
}

Footnotes

1. See "Dataframe" in the DanfoJS documentation ↩

2. See "Sheet Objects" ↩

3. See "danfo.readExcel" in the DanfoJS documentation. ↩

4. See "Reading Files" for the full list of parsing options. ↩

5. See "File API" in "Local File Access" for more details. ↩

6. See "danfo.toExcel" in the DanfoJS documentation. ↩

7. See writeFile in "Writing Files" ↩

8. See "Writing Files" for the full list of writing options. ↩

9. See "Creating a DataFrame" in the DanfoJS documentation. ↩

10. See sheet_to_json in "Utilities" ↩

11. See "danfo.toJSON" in the DanfoJS documentation. ↩

12. See json_to_sheet in "Utilities" ↩

13. See book_new in "Utilities" ↩

14. See writeFile in "Writing Files" ↩