tessl/npm-xlsx

SheetJS Spreadsheet data parser and writer for multiple formats including Excel, CSV, and OpenDocument

—

Pending

Quality

Pending

Does it follow best practices?

Impact

Pending

No eval scenarios have been run

Securityby

Pending

The risk profile of this skill

Overview

Eval results

Files

XLSX

Name: tessl/npm-xlsx
Author: tessl

XLSX (SheetJS) is a comprehensive spreadsheet data parser and writer that supports multiple formats including Excel (XLSX, XLSM, XLSB, XLS), OpenDocument (ODS), CSV, and many others. It provides a unified JavaScript API for reading, manipulating, and generating spreadsheet files with support for advanced features like formulae, charts, pivot tables, styles, and cell formatting.

Package Information

Package Name: xlsx
Package Type: npm
Language: JavaScript/TypeScript
Installation: npm install xlsx
Repository: https://github.com/SheetJS/sheetjs
Documentation: https://docs.sheetjs.com/

Core Imports

ES Modules:

import { read, readFile, write, writeFile, utils, WorkBook, WorkSheet } from "xlsx";

CommonJS:

const XLSX = require("xlsx");
const { read, readFile, write, writeFile, utils } = XLSX;

Browser (via CDN):

<script src="https://unpkg.com/xlsx/dist/xlsx.full.min.js"></script>
<!-- XLSX is now available as a global -->

Basic Usage

import { readFile, writeFile, utils } from "xlsx";

// Read a spreadsheet file
const workbook = readFile("input.xlsx");

// Get the first worksheet
const sheetName = workbook.SheetNames[0];
const worksheet = workbook.Sheets[sheetName];

// Convert worksheet to JSON
const jsonData = utils.sheet_to_json(worksheet);
console.log(jsonData);

// Create a new workbook
const newWorkbook = utils.book_new();

// Convert JSON to worksheet and add to workbook
const newWorksheet = utils.json_to_sheet([
  { Name: "Alice", Age: 25, City: "New York" },
  { Name: "Bob", Age: 30, City: "San Francisco" }
]);
utils.book_append_sheet(newWorkbook, newWorksheet, "People");

// Write workbook to file
writeFile(newWorkbook, "output.xlsx");

Architecture

XLSX is built around several key components:

Core I/O: Primary read/write functions for file operations and data parsing
Utils Namespace: Comprehensive utility functions for data conversion, cell manipulation, and workbook management
Stream Interface: Node.js streaming support for large datasets (Node.js only)
Type System: Full TypeScript support with comprehensive type definitions
Format Support: Extensive format compatibility covering 30+ spreadsheet and data formats

Capabilities

Core I/O Operations

Primary functions for reading and writing spreadsheet data across multiple formats. Supports both file operations (Node.js) and in-memory data processing (all environments).

function read(data: any, opts?: ParsingOptions): WorkBook;
function readFile(filename: string, opts?: ParsingOptions): WorkBook;
function write(data: WorkBook, opts: WritingOptions): any;
function writeFile(data: WorkBook, filename: string, opts?: WritingOptions): any;
function writeFileXLSX(data: WorkBook, filename: string, opts?: WritingOptions): any;
function writeXLSX(data: WorkBook, opts: WritingOptions): any;
function writeFileAsync(filename: string, data: WorkBook, opts: WritingOptions | (() => void), cb?: () => void): any;
function set_fs(fs: any): void;
function set_cptable(cptable: any): void;

Core I/O Operations

Data Import and Export

Utilities for converting between different data formats including JSON, CSV, HTML tables, and arrays. Essential for data pipeline integration and format transformation.

// Import functions (to WorkSheet)
function json_to_sheet<T>(data: T[], opts?: JSON2SheetOpts): WorkSheet;
function aoa_to_sheet<T>(data: T[][], opts?: AOA2SheetOpts): WorkSheet;
function table_to_sheet(data: any,  opts?: Table2SheetOpts): WorkSheet;
function table_to_book(data: any,  opts?: Table2SheetOpts): WorkBook;

// Export functions (from WorkSheet)  
function sheet_to_json<T>(worksheet: WorkSheet, opts?: Sheet2JSONOpts): T[];
function sheet_to_csv(worksheet: WorkSheet, options?: Sheet2CSVOpts): string;
function sheet_to_html(worksheet: WorkSheet, options?: Sheet2HTMLOpts): string;
function sheet_to_txt(worksheet: WorkSheet, options?: Sheet2CSVOpts): string;
function sheet_to_formulae(worksheet: WorkSheet): string[];
function sheet_to_dif(worksheet: WorkSheet, options?: Sheet2HTMLOpts): string;
function sheet_to_slk(worksheet: WorkSheet, options?: Sheet2HTMLOpts): string;
function sheet_to_eth(worksheet: WorkSheet, options?: Sheet2HTMLOpts): string;

Data Import and Export

Cell and Range Operations

Low-level utilities for working with individual cells, cell addresses, and ranges. Includes address encoding/decoding, cell formatting, and value manipulation.

// Cell address utilities
function encode_cell(cell: CellAddress): string;
function encode_row(row: number): string;
function encode_col(col: number): string;
function encode_range(range: Range): string;
function encode_range(s: CellAddress, e: CellAddress): string;
function decode_cell(address: string): CellAddress;
function decode_row(row: string): number;
function decode_col(col: string): number;
function decode_range(range: string): Range;

// Cell manipulation
function format_cell(cell: CellObject, v?: any, opts?: any): string;
function cell_set_number_format(cell: CellObject, fmt: string | number): CellObject;
function cell_set_hyperlink(cell: CellObject, target: string, tooltip?: string): CellObject;
function cell_set_internal_link(cell: CellObject, target: string, tooltip?: string): CellObject;
function cell_add_comment(cell: CellObject, text: string, author?: string): void;

Cell and Range Operations

Workbook and Worksheet Management

Functions for creating, modifying, and managing workbooks and worksheets. Includes sheet visibility, data insertion, and workbook structure manipulation.

function book_new(): WorkBook;
function book_append_sheet(workbook: WorkBook, worksheet: WorkSheet, name?: string, roll?: boolean): void;
function book_set_sheet_visibility(workbook: WorkBook, sheet: number | string, visibility: number): void;
function sheet_add_json<T>(ws: WorkSheet, data: T[], opts?: SheetJSONOpts): WorkSheet;
function sheet_add_aoa<T>(ws: WorkSheet, data: T[][], opts?: SheetAOAOpts): WorkSheet;
function sheet_add_dom(ws: WorkSheet, data: any, opts?: Table2SheetOpts): WorkSheet;
function sheet_set_array_formula(ws: WorkSheet, range: Range | string, formula: string, dynamic?: boolean): WorkSheet;

Workbook and Worksheet Management

Streaming Operations

Node.js streaming interface for processing large datasets without loading entire files into memory. Ideal for server-side applications processing large spreadsheets.

function to_csv(sheet: WorkSheet, opts?: Sheet2CSVOpts): Readable;
function to_html(sheet: WorkSheet, opts?: Sheet2HTMLOpts): Readable;
function to_json(sheet: WorkSheet, opts?: Sheet2JSONOpts): Readable;

Streaming Operations

Additional Exports

/** Version string */
const version: string;

/** SSF Formatter Library */
const SSF: any;

/** CFB Library */
const CFB: any;

/** Utility constants */
const utils: {
  consts: {
    SHEET_VISIBLE: 0;
    SHEET_HIDDEN: 1;
    SHEET_VERYHIDDEN: 2;
  };
};

/** Stream utilities (Node.js only) */
const stream: {
  to_csv(sheet: WorkSheet, opts?: Sheet2CSVOpts): Readable;
  to_html(sheet: WorkSheet, opts?: Sheet2HTMLOpts): Readable;
  to_json(sheet: WorkSheet, opts?: Sheet2JSONOpts): Readable;
  set_readable(Readable: any): void;
};

Core Types

interface WorkBook {
  SheetNames: string[];
  Sheets: { [sheet: string]: WorkSheet };
  Props?: FullProperties;
  Custprops?: object;
  Workbook?: WBProps;
  vbaraw?: any;
}

interface WorkSheet {
  [address: string]: CellObject | any;
  "!ref"?: string;
  "!margins"?: MarginInfo;
  "!merges"?: Range[];
  "!protect"?: ProtectInfo;
  "!autofilter"?: AutoFilterInfo;
  "!cols"?: ColInfo[];
  "!rows"?: RowInfo[];
}

interface CellObject {
  /** The raw value of the cell */
  v?: any;
  /** Formatted text (if applicable) */
  w?: string;
  /** Cell data type */
  t?: ExcelDataType;
  /** Cell formula (if applicable) */
  f?: string;
  /** Range of enclosing array formula (if applicable) */
  F?: string;
  /** Rich text encoding (if applicable) */
  r?: string;
  /** HTML rendering of the rich text (if applicable) */
  h?: string;
  /** Comments associated with the cell */
  c?: Comments;
  /** Number format string (if requested) */
  z?: NumberFormat;
  /** Cell hyperlink object (if applicable) */
  l?: Hyperlink;
  /** Cell style/theme (if applicable) */
  s?: any;
}

interface CellAddress {
  /** Row number (0-indexed) */
  r: number;
  /** Column number (0-indexed) */
  c: number;
}

interface Range {
  /** Start cell */
  s: CellAddress;
  /** End cell */
  e: CellAddress;
}

type ExcelDataType = "b" | "n" | "e" | "s" | "d" | "z";
type BookType = "xlsx" | "xlsm" | "xlsb" | "xls" | "xla" | "biff8" | "biff5" | "biff2" | "xlml" | "ods" | "fods" | "csv" | "txt" | "sylk" | "slk" | "html" | "dif" | "rtf" | "prn" | "eth" | "dbf";

interface CommonOptions {
  WTF?: boolean;
  bookVBA?: boolean;
  cellDates?: boolean;
  sheetStubs?: boolean;
  cellStyles?: boolean;
  password?: string;
}

interface ParsingOptions extends CommonOptions {
  type?: "base64" | "binary" | "buffer" | "file" | "array" | "string";
  codepage?: number;
  cellFormula?: boolean;
  cellHTML?: boolean;
  cellNF?: boolean;
  cellText?: boolean;
  dateNF?: string;
  FS?: string;
  sheetRows?: number;
  bookDeps?: boolean;
  bookFiles?: boolean;
  bookProps?: boolean;
  bookSheets?: boolean;
  sheets?: number | string | Array<number | string>;
  raw?: boolean;
  xlfn?: boolean;
  dense?: boolean;
  PRN?: boolean;
}

interface WritingOptions extends CommonOptions {
  type?: "base64" | "binary" | "buffer" | "file" | "array" | "string";
  bookSST?: boolean;
  bookType?: BookType;
  compression?: boolean;
  ignoreEC?: boolean;
  Props?: Properties;
  numbers?: string;
  sheet?: string;
}

interface Properties {
  Title?: string;
  Subject?: string;
  Author?: string;
  Manager?: string;
  Company?: string;
  Category?: string;
  Keywords?: string;
  Comments?: string;
  LastAuthor?: string;
  CreatedDate?: Date;
}

interface FullProperties extends Properties {
  ModifiedDate?: Date;
  Application?: string;
  AppVersion?: string;
  DocSecurity?: string;
  HyperlinksChanged?: boolean;
  SharedDoc?: boolean;
  LinksUpToDate?: boolean;
  ScaleCrop?: boolean;
  Worksheets?: number;
  SheetNames?: string[];
  ContentStatus?: string;
  LastPrinted?: string;
  Revision?: string | number;
  Version?: string;
  Identifier?: string;
  Language?: string;
}

interface NumberFormat {
  [key: string]: string;
}

interface Hyperlink {
  Target: string;
  Tooltip?: string;
}

interface Comment {
  a?: string;
  t: string;
  T?: boolean;
}

interface Comments extends Array<Comment> {
  hidden?: boolean;
}

interface ColInfo {
  hidden?: boolean;
  width?: number;
  wpx?: number;
  wch?: number;
  level?: number;
  MDW?: number;
}

interface RowInfo {
  hidden?: boolean;
  hpx?: number;
  hpt?: number;
  level?: number;
}

interface MarginInfo {
  left?: number;
  right?: number;
  top?: number;
  bottom?: number;
  header?: number;
  footer?: number;
}

interface ProtectInfo {
  password?: string;
  selectLockedCells?: boolean;
  selectUnlockedCells?: boolean;
  formatCells?: boolean;
  formatColumns?: boolean;
  formatRows?: boolean;
  insertColumns?: boolean;
  insertRows?: boolean;
  insertHyperlinks?: boolean;
  deleteColumns?: boolean;
  deleteRows?: boolean;
  sort?: boolean;
  autoFilter?: boolean;
  pivotTables?: boolean;
  objects?: boolean;
  scenarios?: boolean;
}

interface AutoFilterInfo {
  ref: string;
}