Export to .Xlsx from telerik grid

[Saroj Kumar]

 Hello,

I am trying to export telerik radgrid into excel 2007 i.e. .xlsx format.  I am using VS 2013.Though its exported, but column formats are not coming correctly. Its working perfectly in .xls format.

Code for .xls is working correctly :

        if (gvAttendanceDetails.Items.Count > 0)
        {
            isExport = true;
            gvAttendanceDetails.ExportSettings.ExportOnlyData = true;
            gvAttendanceDetails.ExportSettings.IgnorePaging = true;
            gvAttendanceDetails.ExportSettings.OpenInNewWindow = true;
            gvAttendanceDetails.ExportSettings.FileName = Guid.NewGuid().ToString();
            gvAttendanceDetails.MasterTableView.Caption = lblSummary.Text + "<br/>" + lbllDepartment.Text + ":" + lblDepartemnt.Text + "<br/>" + lbllManager.Text + ":" + lblManager.Text + "<br/>" +
                 lbllNoOfWrk.Text + ":" + lblNoOfWrk.Text + "<br/>" + lbllDaysPrsnt.Text + ":" + lblDaysPrsnt.Text + "<br/>" +
                    lbllDaysAbsent.Text + ":" + lblDaysAbsent.Text + "<br/>" + lbllLeaveDuty.Text + ":" + lbllLeaveDuty.Text + "<br/>" +
                    lbllSelfService.Text + ":" + lblSelfService.Text + "<br/>" + lbllWeekend.Text + ":" + lblWeekend.Text + "<br/>";
            gvAttendanceDetails.MasterTableView.ExportToExcel();
        }
        else
        {
            RadWindowManager1.RadAlert("There are no Details available", null, null, "Error", "");
        }

 -------------------------------------------------------

The only line highlighted below i have added to export into .xlsx

             if(gvAttendanceDetails.Items.Count > 0)
             {
isExport = true;
gvAttendanceDetails.ExportSettings.ExportOnlyData = true;
gvAttendanceDetails.ExportSettings.IgnorePaging = true;
gvAttendanceDetails.ExportSettings.OpenInNewWindow = true;
gvAttendanceDetails.ExportSettings.Excel.Format = Telerik.Web.UI.GridExcelExportFormat.Xlsx;

gvAttendanceDetails.ExportSettings.FileName = DateTime.Now.ToString().Replace(":", "");
gvAttendanceDetails.MasterTableView.Caption = lblSummary.Text + "<br/>" + lbllDepartment.Text + ":" + lblDepartemnt.Text + "<br/>" + lbllManager.Text + ":" + lblManager.Text + "<br/>" +
lbllNoOfWrk.Text + ":" + lblNoOfWrk.Text + "<br/>" + lbllDaysPrsnt.Text + ":" + lblDaysPrsnt.Text + "<br/>" +
lbllDaysAbsent.Text + ":" + lblDaysAbsent.Text + "<br/>" + lbllLeaveDuty.Text + ":" + lbllLeaveDuty.Text + "<br/>" +
lbllSelfService.Text + ":" + lblSelfService.Text + "<br/>" + lbllWeekend.Text + ":" + lblWeekend.Text + "<br/>";

gvAttendanceDetails.MasterTableView.ExportToExcel();
}else

{

}

 

In the attached file i have highlighted in yellow color. Head format is coming correctly, its coming in a single line. And in time column is coming incorrectly.

Can anyone help on this.

Thanks in advance.

[Saroj Kumar]

Adding to my above query post, the header format is shown incorrectly and intime column is coming incorrectly.

 

 

[Kostadin]

Hello Saroj,

Note that Xlsx export format create a binary type and as such all html elements will be rendered as text. Nevertheless if you need to carried the text onto the next line you can use "/n" instead <br/> tag. Also you need to enable TextWrap property for this cell by hooking OnInfrastructureExporting exporting event handler. Please check out the following code snippet.

protected void RadGrid1_InfrastructureExporting(object sender, Telerik.Web.UI.GridInfrastructureExportingEventArgs e)
{
    Telerik.Web.UI.ExportInfrastructure.Table tbl = e.ExportStructure.Tables[0];

    var cell1 = tbl.Cells[1, 1];
    cell1.TextWrap = true;
}

Please give it a try and let me know about the result.

Regards,
Kostadin
Telerik

[Saroj Kumar]

Thanks for the reply Kostadin.
I made changes with the code.

 Now after export, i found header row in excel sheet is coming in a single line but after expand it, i am able to see wrapped lines. I am attaching the exported sheet here. After export the excel format is not coming with border also.

to make border, i have written below code :

protected void gvAttendanceDetails_ExcelExportCellFormatting(object sender, ExcelExportCellFormattingEventArgs e)
{

GridDataItem item = e.Cell.Parent as GridDataItem;
e.Cell.Style["border"] = "solid 0.1pt #000000";

}

One column is also not coming in proper format.

 

Thanks in advance.

 

[Kostadin]

Hello Saroj,

Could you please provide a small runnable sample where the issue can be observed in order to investigate it further? I examined the provided image and I noticed that both text are not the same. Did you change them manually on each export? Also note that only the custom numeric format strings will work because in most scenarios they match the Microsoft Excel’s custom numeric format. The built-in .NET standard numeric format strings  can still be used, but the output will not be as expected.
Regards the borders note that you need to apply them on each cell by using the border properties. More information can be fould in the following help article. The way you try to add a border is applicable only when using HTML based export format.

Regards,
Kostadin
Telerik