Finding cell format when using workbook.csv.writeFile map(value, index) ?

[ajmas]

I am wanting to format the date in the output CSV according to the cell's numFmt value, but I am not sure how to do this?

My code is as follows, based on example in the readMe:

import ExcelJS, { CsvWriteOptions, Worksheet } from 'exceljs';
import { DateTime } from 'luxon';

async function toCsv (filePath: string, worksheetName: string) {
  const workbook = new ExcelJS.Workbook();
  await workbook.xlsx.readFile(filePath);

  const writeFileOptions: Partial<CsvWriteOptions> = {
    sheetName: worksheetName,
    map(value, index) {
      switch (index) {
        case 1:
          // column 1 is string
          return value;
        case 2:
          // column 2 is a date
          console.log((value.numFmt));
          return DateTime.fromJSDate(value).toFormat('yyyy-MM-dd');
        case 3:
          // column 3 is a formula, write just the result
          return value.result;
        default:
          // the rest are numbers
          return value;
      }
    },
    // https://c2fo.github.io/fast-csv/docs/formatting/options
    formatterOptions: {
      delimiter: '\t',
      quote: false,
    },
  };
}

I was hoping value would be of use, but it does not represent cell object. From what I can tell, it just represents a primitive type?

The only alternative I am seeing is to process the sheet myself and then use a package such as csv-stringify to write the file:

import fs from 'fs/promises';
import { stringify } from 'csv-stringify/sync';
import ExcelJS, { CsvWriteOptions, Worksheet } from 'exceljs';

function worksheetToArray (worksheet: Worksheet): string[][] {
  const cellValues: string[][] = [];

  worksheet.eachRow(row => {
    const values: string[] = [];
    row.eachCell(cell => {
      if (cell.type === ExcelJS.ValueType.Date) {
        console.log('TODO handle date format', cell.numFmt);
        values.push((cell.value as Date).toISOString());
      } else {
        values.push(cell.text);
      }
    });
    cellValues.push(values);
  });

  return cellValues;
}

async function toCsv (filePath: string, worksheetName: string, outputPath: string, options?: Partial<CsvWriteOptions>) {
  const values = await loadWorksheet(filePath, worksheetName);

  if (!values) {
    throw new Error(`No worksheet values found in ${worksheetName}`);
  }

  const output = stringify(values, { delimiter: '\t' });
  await fs.writeFile(outputPath, output, 'utf-8');
}

Note, using exceljs 4.4.0.