Simple TS/JS utility for xlsx

XlsxUtils.ts

/**
 * XLSX Utilities | Abhinav Kulshreshtha | Unlicense
 *
 * A collection of utility functions for working with XLSX files in Node.js. These utilities facilitate common tasks such as converting row/column indices to cell addresses, extracting headers from sheets, and converting arrays of objects into worksheets. Feel free to use, modify, and distribute under the Unlicense.
 *
 * ## Contents:
 * - `rowIndexColToCellAddress`: Converts row and column indices to an Excel cell address in A1 notation.
 * - `cellAddressToIndices`: Convert Excel cell address (A1 Notation) to row and column indices
 * - `getHeaders`: Extracts headers from the first row of an Excel sheet.
 * - `objectsToArrayWorksheet`: Converts an array of objects into a worksheet.
 *
 *
 * ## License:
 * This code is licensed under the Unlicense. You are free to use, modify, and distribute this code without any restrictions.
 * Read full license at https://unlicense.org/ , You can also get a gist of it at https://en.wikipedia.org/wiki/Unlicense
 */

import * as XLSX from 'xlsx';

/**
 * Converts row and column indices to an Excel cell address in A1 notation.
 *
 * @param {number} row - The row index (starting from 1).
 * @param {number} col - The column index (starting from 1).
 * @returns {string} The cell address in A1 notation (e.g., "A1", "B2").
 */
export function rowIndexColToCellAddress(row: number, col: number): string {
  const zeroBasedRow = row - 1;
  const zeroBasedCol = col - 1;
  let cellAddress = String.fromCharCode(65 + zeroBasedCol) + (zeroBasedRow + 1).toString();
  return cellAddress;
}

/**
 * Converts an Excel cell address in A1 notation to row and column indices.
 *
 * @param {string} cellAddress - The cell address in A1 notation (e.g., "A1", "B2").
 * @returns {{row: number, col: number}} An object containing the row and column indices.
 */
export function cellAddressToIndices(cellAddress: string): { row: number, col: number } {
  // Extract the column letter and row number from the cell address
  const colLetter = cellAddress[0].toUpperCase(); // Column letter (e.g., "A")
  const rowNumber = parseInt(cellAddress.slice(1)); // Row number (e.g., "1")

  // Convert the column letter to a zero-based index
  const zeroBasedCol = colLetter.charCodeAt(0) - 65;

  // Since row numbers in Excel are 1-indexed but our indices are 0-indexed, subtract 1
  const row = rowNumber - 1;

  return { row, col: zeroBasedCol };
}


/**
 * Extracts headers from the first row of an Excel sheet.
 *
 * @param {XLSX.WorkSheet} sheet - The worksheet from which to extract headers.
 * @returns {string[]} An array of strings representing the headers extracted from the first row.
 */
export function getHeaders(sheet: XLSX.WorkSheet): string[] {
  const headers: string[] = [];
  const range = XLSX.utils.decode_range(sheet['ref'] as string);
  const C = range.s.c;
  const R = range.s.r;
  for (let c = C; c <= range.e.c; ++c) {
    const cell_address = { c, r: R };
    const cell_ref = XLSX.utils.encode_cell(cell_address);
    const cell = sheet[cell_ref];
    const hdr = cell?.t? XLSX.utils.format_cell(cell) : `UNKNOWN ${c}`;
    headers.push(hdr);
  }
  return headers;
}

/**
 * Converts an array of objects into a worksheet.
 *
 * Each object represents a row in the worksheet, and the keys of the objects represent column names.
 *
 * @param {Array<Object>} data - The array of objects to convert into a worksheet.
 * @param {string} sheetName - The name of the worksheet.
 * @returns {XLSX.WorkSheet} The generated worksheet.
 */
export function objectsToArrayWorksheet(data: Array<Object>, sheetName: string): XLSX.WorkSheet {
  const ws: XLSX.WorkSheet = XLSX.utils.json_to_sheet(data);
  ws.name = sheetName;
  return ws;
}