0

I need to separate all the headers of Excel sheet. code that i implemented is working but only text format is working. kindly help.

const xlsx = require('xlsx');

const workbook = xlsx.readFile('./SMALL.xlsx');
const worksheet = workbook.Sheets[workbook.SheetNames[0]];

const workbookHeaders = xlsx.readFile('./SMALL.xlsx', { sheetRows: 1 });
const columnsArray = xlsx.utils.sheet_to_json(workbookHeaders.Sheets[workbook.SheetNames[0]], { header: 1 })[0];


console.log(columnsArray);

Response

My console should print ['Task', '1/2022', '2/2022' ]. instead its printing [ 'Task', 44562, 44593 ]

Image of console/node output and spreadsheet cells

phuzi
  • 12,078
  • 3
  • 26
  • 50
Anesly Manoj
  • 3
  • 3
  • Not exactly sure what you mean by _"but only text format is working"_. Please give as much detail about what is not working for you, what are you expecting the result to be and how does it differ from what you actually get? – phuzi Aug 25 '22 at 10:18
  • Sorry. i have modified a bit. please read now – Anesly Manoj Aug 25 '22 at 10:19
  • Right... Date values are stored as a serial number of days since 31st December 1899 - this is the value you are getting. The `01/2022` is the result of formatting this serial number and not the actual stored value. – phuzi Aug 25 '22 at 10:22
  • See https://stackoverflow.com/questions/61896377/javascript-convert-short-date-number-to-javascript-date for more info on converting 44562 to a JavaScript Date. – phuzi Aug 25 '22 at 10:24

1 Answers1

0

Looks like you can specify a date format string in the options parameter to the sheet_to_json(worksheet, opts) method.

The option property name appears to be: dateNF

https://www.npmjs.com/package/xlsx#parsing-options

Bret Lipscomb
  • 480
  • 2
  • 7