I am using AngularJS to read and display the contents of an excel file. When the excel file contains a row with a value such as: "Finance Committee will need to meet prior to the board meeting on February 16"

The value gets converted into a date 2/16/01

After this piece of code gets executed (ProcessExcel)

$scope.DisplayFile = function () {
    var regex = /^[a-z0-9][-a-z0-9\x20_!()\.:,]*\.xlsx?$/i;
    if (regex.test($scope.SelectedFile.name)) {
        if (typeof (FileReader) !== "undefined") {
            var reader = new FileReader();
            //For Browsers other than IE.
            if (reader.readAsBinaryString) {
                reader.onload = function (e) {
                    $scope.ProcessExcel(e.target.result);
                };
                reader.readAsBinaryString($scope.SelectedFile);
            } else {
                //For IE Browser.
                reader.onload = function (e) {
                    var data = "";
                    var bytes = new Uint8Array(e.target.result);
                    for (var i = 0; i < bytes.byteLength; i  ) {
                        data  = String.fromCharCode(bytes[i]);
                    }
                    *$scope.ProcessExcel(data)*; --I think the issue comes from this function, I might be incorrect

    $scope.ProcessExcel = function (data) {

        //file data
        var workbook = XLSX.read(data, {
            type: 'binary'
        });

        //fetch first sheet
        var firstSheet = workbook.SheetNames[0];

        //put sheet into array excelRows
        *excelRows = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[firstSheet]);* -- By the time this assignment occurs, the value of the excel array for that row, is already 2/16/01

Can someone please help me understand how to force reading of dates from Excel as character strings?

Thank you, Erasmo.

CodePudding user response：

The issue likely comes from the fact that the library you are using to read the excel file, XLSX, is interpreting the text "Finance Committee will need to meet prior to the board meeting on February 16" as a date and converting it to 2/16/01.

One solution could be to specify that the cell containing the text should be treated as a string, rather than a date, when reading the excel file.

You can do this by adding the cellDates: false option to the XLSX.read function.

var workbook = XLSX.read(data, {
    type: 'binary',
    cellDates: false
});

This will tell the library to not interpret cell values as dates when reading the excel file.

Another option would be to convert the excel values to string before storing them in excelRows array, you can do this by using XLSX.utils.encode_cell method on each of the cell values, and then use XLSX.utils.sheet_to_row_object_array method to convert the sheet to row object array.

var sheet = workbook.Sheets[firstSheet];
for (var i = sheet['!ref'].split(':')[0]; i <= sheet['!ref'].split(':')[1]; i  ) 
{
var cell = sheet[i];
if (cell && cell.t === 'n' && cell.w) cell.t = 's';
}
excelRows = XLSX.utils.sheet_to_row_object_array(sheet);

This way all the values will be treated as strings and will not be converted to date format. Hope this will work. Thankyou

CodePudding user response：

You can use the raw option of the XLSX.utils.sheet_to_row_object_array() method to retain the original cell values as strings, instead of converting them to JavaScript data types. Here's how to do it:

excelRows = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[firstSheet], {raw: true});

By setting raw: true, all the cell values will be treated as strings, including dates, and they won't be automatically converted to JavaScript data types.