javascriptexceldatealasql

Unable to perform date filter after exporting JSON date to excel using javascript function

I'm using below function to export JSON with dates(DD/MM/YYYY) into excel using alasql

function exportToExcel() {
    var loginInfo=[
      {
        "Login Date": "5/3/2018"
      },
      {
        "Login Date": "15/4/2018"
      },
      {
        "Login Date": "12/8/2018"
      },
      {
        "Login Date": "10/7/2018"
      }
    ];
    alasql("SELECT * INTO XLSX('test.xlsx',{headers:true}) FROM ? ",[loginInfo]);
 }
exportToExcel();

Upon exporting data to excel, since the date column is a string I'm not able to directly apply date filter in excel. Is there any way which I can make the dates appear as dates while parsing using JavaScript keeping the same format in excel further exporting so that I can filter dates ?Thanks

enter image description here enter image description here

enter image description here

Solution

  • You need to have a Date type response from json. I have used alasql for export.

    {
    columnid: 'feffectiveDate', title: 'Effective Date',
    style: 'background:rgb(175,235,58);font-size:15px',
    cell: {
        format: function (value) {
            if (value !== "") {
                var effectiveDate = new Date(value);
                var month = effectiveDate.getMonth() + 1;
                var date = effectiveDate.getDate();
                var year = effectiveDate.getFullYear();
                return year + "-" + month + "-" + date;
            } else {
                return ""
            }
        }
    }
},