Sheetjs: Excel sheet to JSON - Date Issue yyyy changed to yy

[contactvm]

I am using js-xlsx/dist/xlsx.full.min.js

Using function XLSX.utils.sheet_to_json to convert excel to json array, while doing this date format changes from mm/dd/yyyy to mm/dd/yy. Can i give any format while converting?

[SheetJSDev]

The dateNF option lets you control the output date format. It is explained in the README: https://github.com/sheetjs/js-xlsx#json

If the original file had a date format of mm/dd/yyyy then it's a read bug -- can you share the original file and the snippet of code that you used to read the file?

[contactvm]

Hi,

It is simple excel file, where i have given date in format MM/DD/YYYY. I am using below code -

$scope.handleFile = function () {
                var file = $scope.selectedFile;
                if (file) {
                    var reader = new FileReader();
                    reader.onload = function (e) {
                        var data = e.target.result;
                        var workbook = XLSX.read(data, {
                            type: 'binary'
                        });
                        var first_sheet_name = workbook.SheetNames[0];

                        var dataObjects = XLSX.utils.sheet_to_json(workbook.Sheets[first_sheet_name]);
                        //console.log(excelData);  
                        if (dataObjects.length > 0) {
                            debugger;
                            $scope.save(dataObjects);
                        } else {
                            $scope.msg = "Error : Something Wrong !";
                        }
                    }
                    reader.onerror = function (ex) {}
                    reader.readAsBinaryString(file);
                }
            }

Can you please tell me how should i use DateNF option>

[dkloke]

@contactvm when reading the buffer into XLSX.read, try adding the following options:
{type: 'binary', cellDates: true, dateNF: 'yyyy/mm/dd;@'}

[reviewher]

@contactvm if you can share an offending file, we can reopen the issue.

[india-rituraj]

Hi Team ,

I am struggling with format too .

100264.xlsx

find my xlsx file in the attachment and i am using below code in node js to read the file but the date is one day before . Also numeric field are not coming properly . please suggest ho to handle these issues

const XLSX = require('xlsx');

var workbook = XLSX.readFile('100264.xlsx',{type: 'binary', cellDates: true, dateNF: 'mm/dd/yyyy;@'});
//var workbook = XLSX.readFile('100264.xlsx',{type: 'binary', cellDates: true});
//var workbook = XLSX.readFile('100264.xlsx',{type: 'binary',cellDates: true});
let active_sheet_name = workbook.SheetNames[0];
let fileData = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[active_sheet_name]);

console.log(fileData);

the output of console.log is given below.

[ { 'Sales Date': 2018-01-30T18:30:00.000Z },
{ 'Sales Date': 2018-01-30T18:30:00.000Z },
{ 'Sales Date': 2018-01-30T18:30:00.000Z },
{ 'Sales Date': 2018-01-30T18:30:00.000Z },
{ 'Sales Date': 2018-01-30T18:30:00.000Z },
{ 'Sales Date': 2018-01-30T18:30:00.000Z },
{ 'Sales Date': 2018-01-30T18:30:00.000Z },
{ 'Sales Date': 2018-01-30T18:30:00.000Z },
{ 'Sales Date': 2018-01-30T18:30:00.000Z },
{ 'Sales Date': 2018-01-30T18:30:00.000Z },
{ 'Sales Date': 2018-01-30T18:30:00.000Z },
{ 'Sales Date': 2018-01-30T18:30:00.000Z },
{ 'Sales Date': 2018-01-30T18:30:00.000Z },
{ 'Sales Date': 2018-01-30T18:30:00.000Z },
{ 'Sales Date': 2018-01-30T18:30:00.000Z },
{ 'Sales Date': 2018-01-30T18:30:00.000Z },
{ 'Sales Date': 2018-01-30T18:30:00.000Z },
{ 'Sales Date': 2018-03-01T18:30:00.000Z } ]

[reviewher]

@india-rituraj set the option raw: false:

XLSX.utils.sheet_to_row_object_array(wb.Sheets[wb.SheetNames[0]], {raw: false})

[manojmkv]

Hi,
In my excel file date format is dd/mm/yyyy but FileReader object is returning mm/dd/yyyy. I tried all fixes provided by you above but it did not work. I feel there is an issue with File reader. Here is my code snippet:

var cfb = XLSX.read(binary, {
type: 'binary',
cellDates: true,
dateNF: 'dd/mm/yyyy'
});
var sCSV = '';
var sCSVData = '';
if (cfb.SheetNames.length > 1) {
$scope.sheetExcess = true;
}
cfb.SheetNames.forEach(function (sheetName) {
sCSV = XLSX.utils.sheet_to_row_object_array(cfb.Sheets[sheetName], {raw: false});
sCSVData = XLSX.utils.sheet_to_json(cfb.Sheets[sheetName], {
header: 1
});
var xlData = sCSV;

Let me know if you see anything missing in my code.

[Somnathp10]

when reading the buffer into XLSX.read, try adding the following options:
{type: 'binary', cellDates: true, dateNF: 'yyyy/mm/dd;@'}

[Somnathp10]

and set the option
XLSX.utils.sheet_to_row_object_array(wb.Sheets[wb.SheetNames[0]], {raw: false})