Grid - Excel Export

[Matias Puzanowski]

Hello Syncfusion team,

When exporting to Excel, is it possible to set a cell on a specific type? Previously we were using some controls from another company and it was possible.

We have some columns with time (HH:mm) that were exported on 'Custom' format and now they are exported as 'Text' and it was very useful for our customers to export to Excel and directly sum this columns. 

Thanks in advance.

[Balaji Sekar]

Hi Matias, 

 

Greetings from Syncfusion forum. 

 

We have validated and achieved your requirement using excelQueryCellInfo and getDateFormat method based on our assumptions. 

 

Query: it possible to set a cell on a specific type?  

 

Yes, wou can set column in specific type using  ‘type’ property in column level. 

 

https://ej2.syncfusion.com/javascript/documentation/api/grid/column/#type. 

 

We have applied the “Applied” Column as in HH: mm Custom format and are shown in that format. The getDateFormat method that returns a function that formats a date object based on the specified DateFormatOptions. Using this format in excelQueryCellInfo, we can export the value as in text with corresponding format options. Using this text value we can perform whatever we want. 

 

Reference links. 

 

Link  - https://ej2.syncfusion.com/documentation/common/internationalization/?no-cache=1#formatting 

 

Link 2 - https://ej2.syncfusion.com/documentation/api/base/dateFormatOptions/ 

 

Please refer to the below code and sample link. 

 

var intl = new ej.base.Internationalization() 

var grid = new ej.grids.Grid({ 

dataSource: data, 

  allowExcelExport: true, 

 .. . . . 

  columns: [ 

. . . . 

    { 

      field: "Applied", 

      headerText: "Order Date", 

      width: 130, 

      format: {type:'date', format:'HH:mm'}, 

      textAlign: "Right" 

    } 

  ], 

  excelQueryCellInfo: function(e) {debugger; 

  if(e.column.field === 'Applied'){ 

    var format = e.column.format; 

    var intl = new ej.base.Internationalization(); 

    var dFormatter = intl.getDateFormat(format); 

    e.value = dFormatter(e.value); 

    console.log(e.value); // returns text value  

  } 

  } 

}); 

grid.appendTo("#Grid"); 

 

Sample link : https://stackblitz.com/edit/mataw1-chu1oz?file=index.js 

 

Still, facing the issue please share the below details. 

 

1. Structural view of your dataSource
2. Pictorial representation of your requirement.
3. Explain your requirement briefly.

 

Please get back to us, if you need any further assistance. 

 

Regards, 

Balaji Sekar. 

[Matias Puzanowski]

Hello!

Thanks a lot for your response.

I'm providing an example from an Excel export of our previous grid control (Selecting 'HH:mm' cells, you are able to SUM)

And we cannot achieve this with the solution you provided:

[Thiyagu Subramani]

Hi Matias ,  

Thanks for your update. 

We have checked issue with your reported information. In previous update value for the column “OrderDate” is passed as string(text) values instead of date time object as per your requirement. Because of this, Excel export serializes the values as string. So, the SUM action in Excel is not performed. Its default behavior of excel . So, we suggest you to set the cell value as Date time instance to make the Excel perform SUM action. In this below sample, once we reach the edit state to the corresponding exported text value cell , its act as the  default excel cell format like below image. 

 

 

Sample link:  https://stackblitz.com/edit/mataw1-9eh7h1?file=index.js  

   

Please let us know if you have any queries.  

Regards,  

Thiyagu S.