Export to .Xlsx from telerik grid

5 posts, 0 answers
  1. Saroj Kumar
    Saroj Kumar avatar
    5 posts
    Member since:
    May 2015

    Posted 28 May 2015 Link to this post

     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.

  2. Saroj Kumar
    Saroj Kumar avatar
    5 posts
    Member since:
    May 2015

    Posted 28 May 2015 in reply to Saroj Kumar Link to this post

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

     

     

  3. Kostadin
    Admin
    Kostadin avatar
    1733 posts

    Posted 01 Jun 2015 Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
  4. Saroj Kumar
    Saroj Kumar avatar
    5 posts
    Member since:
    May 2015

    Posted 03 Jun 2015 in reply to Kostadin Link to this post

    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.

     

  5. Kostadin
    Admin
    Kostadin avatar
    1733 posts

    Posted 05 Jun 2015 Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
Back to Top