We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date
  1. Home
  2. Forum
  3. ASP.NET Web Forms
  4. Export grid excel with numeric format

Export grid excel with numeric format

Hi,

I can export a grid in excel, but I need that some columns have a numeric cell style.

For example:

Complementaria Column has cell format numeric with thousand separator (I set it manually) but Anomalias column has general format. How can I set the numeric format (in function of region's user) when I export a grid?



Thanks

3 Replies
SS Seeni Sakthi Kumar Seeni Raj Syncfusion Team August 29, 2016 09:56 AM UTC

Hi Manolo, 

We suspect that you would like to apply locale and follow your preferred culture for the Numeric column while exporting the Grid.  

This can be achieved by mentioning the Locale (any preferred culture) to the Grid and defining the numeric format for the corresponding column. Refer to the following code example and the exported Grid. 

    <ej:Grid ID="OrdersGrid" runat="server" Locale="ca-ES" OnServerWordExporting="OrdersGrid_ServerWordExporting" OnServerPdfExporting="OrdersGrid_ServerPdfExporting" OnServerExcelExporting="OrdersGrid_ServerExcelExporting" AllowPaging="True"> 
        <ToolbarSettings ShowToolbar="true" ToolbarItems="excelExport,wordExport,pdfExport"></ToolbarSettings> 
        <Columns> 
            <ej:Column Field="OrderID" HeaderText="Order ID" /> 
            <ej:Column Field="CustomerID" HeaderText="Customer ID" /> 
            <ej:Column Field="EmployeeID" HeaderText="Employee ID" /> 
            <ej:Column Field="Freight" HeaderText="Freight" Format="{0:N}" /> 
        </Columns> 
    </ej:Grid> 
 
        protected void OrdersGrid_ServerExcelExporting(object sender, Syncfusion.JavaScript.Web.GridEventArgs e) 
        { 
            ExcelExport exp = new ExcelExport(); 
            exp.Export(OrdersGrid.Model, (IEnumerable)OrdersGrid.DataSource, "Export.xlsx", ExcelVersion.Excel2010, true, true, "flat-lime"); 
        } 

 

Note: To use the Localization in Grid, you have to refer the preferred ej culture script in your application as shown in the following table. 

<head runat="server"> 
    <link rel='nofollow' href="Content/ej/web/flat-lime/ej.widgets.all.min.css" rel="stylesheet" /> 
           . . .  
    <script src="Scripts/ej/ej.web.all.min.js"></script> 
    <script src="Scripts/ej/ej.culture.ca-ES.min.js"></script> 
    <script src="Scripts/ej/ej.webform.min.js"></script> 
 
</head> 

We have prepared a sample that can be downloaded from the following location. 


Refer to the following Help Documents. 


Regards, 
Seeni Sakthi Kumar S. 


MA Manolo August 29, 2016 10:26 AM UTC

Hi,

I'm using the syncfusion version 14.2451.0.26. In your example, when I export, the thousand separator is not applied.



I need update or set other config?

Thanks

SS Seeni Sakthi Kumar Seeni Raj Syncfusion Team August 30, 2016 08:38 AM UTC

Hi Manolo, 

We suspect the cause of the problem is difference in time zone and location settings of the machine. To overcome this problem we suggest to format the cell value using the following code example. 

        protected void OrdersGrid_ServerExcelExporting(object sender, Syncfusion.JavaScript.Web.GridEventArgs e) 
        { 
            ExcelExport exp = new ExcelExport(); 
            OrdersGrid.Model.ServerExcelQueryCellInfo = querycell; 
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("ca-ES");  
            exp.Export(OrdersGrid.Model, (IEnumerable)OrdersGrid.DataSource, "Export.xlsx", ExcelVersion.Excel2010, true, true, "flat-lime"); 
        } 
        public void querycell(object sender) 
        { 
            var data = (IRange)sender; 
            if (data.Column == 3) //Frieght or numeric column 
            { 
                data.NumberFormat = "#.##0,00"; 
            } 
        } 

ServerExcelQueryCellInfo is the side server-side events where you can format the value of the cells. 

We have also modified the sample that can be downloaded from the following location. 


Refer to the Help document for the Export Server events.    


Regards, 
Seeni Sakthi Kumar S. 


Loader.
Up arrow icon