Exporting to excel with null as date, fails

[Amos]

workbook.js file, the following methods fails when year/month/day are NAN because the date is null since I don't want to show any date.

    Workbook.prototype.dateToTicks = function (year, month, day) {

        var ticksPerDay = 10000 * 1000 * 60 * 60 * 24;
        var daysToMonth365 = [0, 31, 59, 90, 120, 151, 181, 212, 243, 273, 304, 334, 365];
        var daysToMonth366 = [0, 31, 60, 91, 121, 152, 182, 213, 244, 274, 305, 335, 366];
        if (year >= 1 && year <= 9999 && month >= 1 && month <= 12) {
            var days = this.isLeapYear(year) ? daysToMonth366 : daysToMonth365;
            var y = year - 1;
            var n = y * 365 + ((y / 4) | 0) - ((y / 100) | 0) + ((y / 400) | 0) + days[month - 1] + day - 1;
            return n * ticksPerDay;
        }
        throw new Error('Not a valid date');
    };

Another check should be added above or before calling this method in case the date is null/invalid. In the meantime, is there a quick fix for this from my side?

Thanks

[Manivel Sellamuthu]

Hi Amos, 

Greetings from Syncfusion support. 

We have checked the reported issues by providing null, undefined and empty string values. But the excel export is working fine without errors and the provided values are shown as empty data only. 

Please refer the below code example and sample for more information. 

<template> 

  <div id="app"> 

    <ejs-grid 

      ref="grid" 

      id="Grid" 

      :dataSource="data" 

. . . 

        <e-column field="OrderDate" headerText="Order Date" type="date" format="yMd" width="150"></e-column> 

      </e-columns> 

    </ejs-grid> 

  </div> 

</template> 

<script> 

import Vue from "vue"; 

import { 

  GridPlugin, 

  Page, 

  Toolbar, 

  ExcelExport 

} from "@syncfusion/ej2-vue-grids"; 

import { gridData } from "./data"; 

 

Vue.use(GridPlugin); 

 

export default { 

  data() { 

    return { 

      data: [ 

        { 

          OrderID: 10248, 

          CustomerID: "VINET", 

          OrderDate: new Date(8364186e5), 

          ShipCountry: "France", 

          Freight: 32.38 

        }, 

        { 

          OrderID: 10249, 

          CustomerID: "TOMSP", 

          OrderDate: new Date(836505e6), 

          ShipCountry: "Germany", 

          Freight: 11.61 

        }, 

        { 

          OrderID: 10250, 

          CustomerID: "HANAR", 

          OrderDate: null, 

          ShipCountry: "Brazil", 

          Freight: 65.83 

        }, 

        { 

          OrderID: 10251, 

          CustomerID: "VICTE", 

          OrderDate: undefined, 

          ShipCountry: "France", 

          Freight: 41.34 

        }, 

        { 

          OrderID: 10252, 

          CustomerID: "SUPRD", 

          OrderDate: "", 

          ShipCountry: "Belgium", 

          Freight: 51.3 

        } 

      ], 

. . . 

Sample: https://codesandbox.io/s/155849-export-with-date-as-null-gm52r?file=/src/App.vue 

Could you please share the below details, which will be helpful for us to validate further about issue. 

1. Share the Syncfusion package version
2. Share the complete Grid code and dataSource example
3. If possible, please try to replicate the reported issue

 

Regards, 

Manivel 

[Amos]

OK, the problem was me trying to

new Date(this.gridData[item]["dateField"]);

when the field was an empty string (not null)

Just a thought: just like the grid managed to handle "invalid date" and simply showed an empty value, maybe you can add such "check" also when exporting.

[Manivel Sellamuthu]

Hi Amos, 

Thanks for your update. 

As we suggested in our previous update, we suggest you to provide the empty string value directly instead of providing them through new Date, for the date values that you don’t want to show any data. 

If we misunderstood your query, or this is not your exact query please get back to us with the more details for further assistance. 

Regards, 

Manivel 

[Amos]

It's just cosmetics.

I get date info (as string) from the server and loop it with

new Date(this.gridData[item]["dateField"]);

As a fix I added a check and if it's an empty string I don't do the above conversion so now it's working.

Since the grid itself, handled my bug correctly and showed an empty value, I just thought maybe the same can be applied when exporting

to excel. In other words, surround the function that handles the date for the export with a condition so if the date is invalid,

put an empty value and continue.

Again, just a thought.

[Manivel Sellamuthu]

Hi Amos, 

Thanks for your update. 

In Excel library When the date value is incorrect, we throw an exception to indicate that the value is incorrect. We cannot replace empty values in the cell. This is the behavior of the Excel export library. In Grid we have handled it while displaying the cell values. So we suggest you to replace the empty values for incorrect date value in JSON construction for Grid to Excel export. 

Please let us know, if you need further assistance. 

Regards, 

Manivel