XLSX Manager

A powerful TypeScript library for Excel file manipulation with advanced table detection, formatting, and data extraction capabilities.

Features

• Smart Table Detection - Automatically detect table boundaries and structure
• Multi-level Header Support - Handle complex headers with merged cells
• Schema-based Data Extraction - Transform Excel data to JSON with custom schemas
• Conditional Formatting - Preserve and reapply Excel conditional formatting rules
• Cell Operations - Get/set cells by value, index, or address
• Row/Column Operations - Read, clear, and manipulate rows and columns
• Meta Data Extraction - Extract related cell values (left/right neighbors)
• Merge Cell Handling - Detect and work with merged cells

Installation

npm install xlsx-manager

Quick Start

import { XlsxTable } from 'xlsx-manager';

// Load an Excel file
const xlsx = new XlsxTable();
await xlsx.loadXlsx('data.xlsx');

// Detect table structure starting from a cell
const table = xlsx.detectTable('A1');
console.log(table.range); // { startRow, endRow, startCol, endCol }

// Convert to JSON with auto-detection
const data = xlsx.toJson('A1');
console.log(data);

API Reference

XlsxTable

The main class for table operations.

Constructor

new XlsxTable(options?: DetectTableOptions)

Options:

• maxEmptyRows (default: 2) - Max consecutive empty rows before table end
• maxEmptyCols (default: 1) - Max consecutive empty columns before table end
• maxRows (default: 1000) - Maximum rows to scan
• maxCols (default: 100) - Maximum columns to scan
• initialRowScan (default: 20) - Initial rows to scan for quick boundary detection

Methods

loadXlsx(source)

Load an Excel file from path or File object.

await xlsx.loadXlsx('path/to/file.xlsx');
// or
await xlsx.loadXlsx(fileObject);

detectTable(startCell)

Detect table structure from a starting cell.

const table = xlsx.detectTable('A1');
// Returns: { range, headerRows, dataStartRow, columns }

toJson(startCell, schema?)

Convert table data to JSON array.

// Auto-detect structure
const data = xlsx.toJson('A1');

// With custom schema
const schema = {
  studentId: "Student ID",
  name: "Name",
  grades: ["Subject", "Grade"]  // Nested array
};
const data = xlsx.toJson('A1', schema);

Schema Format:

• Simple mapping: { outputKey: "Header Name" }
• Nested arrays: { outputKey: ["GroupHeader", "ItemHeader"] }

Xlsx (XlsxManager)

Lower-level Excel operations.

Cell Operations

// Get cell by address
const cell = xlsx.getCell('A1');

// Get cell by value
const cell = xlsx.getCell('Student Name');

// Get cell with merge info
const cell = xlsx.getCell('A1', { includeMergeInfo: true });

Row Operations

// Get row by index
const row = xlsx.getRow(1);

// Get row by value
const row = xlsx.getRow('Student Name');

// Get row values only
const values = xlsx.getRowValues(1);

// Clear row
xlsx.clearRow(1);

Column Operations

// Get column by letter
const col = xlsx.getCol('A');

// Get column by index
const col = xlsx.getCol(1);

// Get column values
const values = xlsx.getColValues('A');

// Clear column
xlsx.clearCol('A');

Meta Data Extraction

Extract values from neighboring cells:

// Get value to the right
const value = xlsx.meta('Label:');  // Returns value next to "Label:"

// Get value to the left
const value = xlsx.metaR('Value');

// With custom distance
const value = xlsx.meta('Label:', { maxDistance: 10 });

Range Operations

// Get range of cells
const range = xlsx.getRange('A1:C10');
range.eachCell((cell) => {
  console.log(cell.value);
});

XlsxCore

Core worksheet operations.

// Check if row/column is empty
const isEmpty = xlsx.isRowEmpty(1);
const isEmpty = xlsx.isColEmpty('A');

// Check within range
const isEmpty = xlsx.isRowEmptyInRange(1, 1, 5);

XlsxFormatting

Handle Excel formatting and styles.

// Save with formatting preserved
await xlsx.saveXlsx('output.xlsx');

// Get buffer
const buffer = await xlsx.writeBuffer();

Utility Functions

import {
  isCellAddress,
  isColumnLetter,
  extractRowNumber,
  extractColNumber,
  parseRange,
  toCamelCase,
  nStr
} from 'xlsx-manager';

// Check if string is a cell address
isCellAddress('A1');  // true

// Extract components
extractRowNumber('A10');  // 10
extractColNumber('AB5');  // 28

// Parse range
parseRange('A1:C10', rowCount, colCount);

// Utilities
toCamelCase('Student Name');  // "studentName"
nStr('  text  ');  // "text" (normalized)

Advanced Examples

Complex Table with Nested Headers

const xlsx = new XlsxTable();
await xlsx.loadXlsx('grades.xlsx');

const schema = {
  studentId: "Student ID",
  name: "Name",
  birthDate: "Birth Date",
  grades: ["Subject", "Score"]  // Will create array of {Subject, Score}
};

const students = xlsx.toJson('A1', schema);
// Output:
// [
//   {
//     studentId: "001",
//     name: "John Doe",
//     birthDate: "2000-01-01",
//     grades: [
//       { Subject: "Math", Score: 95 },
//       { Subject: "English", Score: 88 }
//     ]
//   }
// ]

Custom Table Detection

const xlsx = new XlsxTable({
  maxEmptyRows: 3,
  maxEmptyCols: 2,
  maxRows: 5000,
  initialRowScan: 50
});

await xlsx.loadXlsx('large-file.xlsx');
const table = xlsx.detectTable('B5');  // Start from specific cell

Working with Merged Cells

const cell = xlsx.getCell('A1', { includeMergeInfo: true });
if (cell.isMerged) {
  console.log(`Spans ${cell.rowSpan} rows and ${cell.colSpan} columns`);
  console.log(`Is top-left: ${cell.isTopLeft}`);
}

Preserving Conditional Formatting

// Load file
await xlsx.loadXlsx('formatted.xlsx');

// Modify data
xlsx.getCell('A1').value = 'New Value';

// Save with formatting preserved
await xlsx.saveXlsx('output.xlsx');

TypeScript Support

Full TypeScript support with comprehensive type definitions:

import type {
  TableRange,
  DetectTableOptions,
  ColumnInfo,
  HeaderStructure,
  CellInfo,
  MergeInfo
} from 'xlsx-manager';

License

MIT

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.