Export in Excel keeping columns' format the same as a grid

9 posts, 0 answers
  1. Victoria F
    Victoria F avatar
    46 posts
    Member since:
    May 2010

    Posted 12 Oct 2011 Link to this post

    Hello guys,

    I'm exporting hierarchical grid to Excel.
    The problem is that while exporting the grid it's loosing the formatting of the columns.
    Is there any property to set that keep format the same as grid has?
    I have more then 50 columns with a different format .. do I have to format it twice for the grid view and for the Excel ?

    Can I use ExcelCellFormatting event to copy format of the grid cells to Excel?        

    Thank you ,
    Victoria.
  2. Emanuel Varga
    Emanuel Varga avatar
    1336 posts
    Member since:
    May 2010

    Posted 13 Oct 2011 Link to this post

    Hello Victoria,

    Yes, you have to use ExcelCellFormatting and you have to perform some kind of mapping of the actual grid formatting to excel style formatting.

    Hope this helps, if you have any other questions or comments, please let me know,

    Best Regards,
    Emanuel Varga

    Telerik WinForms MVP
  3. UI for WinForms is Visual Studio 2017 Ready
  4. Victoria F
    Victoria F avatar
    46 posts
    Member since:
    May 2010

    Posted 13 Oct 2011 Link to this post

    Hello Emanuel,
    Do you have any example of mapping of the formats between grid and Excel file.
    For example I have formatted grid with :
    radGridView_LayersSummary.Columns["A"].Width = 90;
    radGridView_LayersSummary.Columns["A"].FormatString = "{0:N0}";
    radGridView_LayersSummary.Columns["B"].FormatString = "{0:N4}";
    radGridView_AcctSummary.Columns["C"].FormatString = "{0: M/d/yyyy}";
    What should I write in ExcelCellFormatting event ?
    Please, give me an example of this event.

    Thank you ,
    Victoria.
  5. Martin Vasilev
    Admin
    Martin Vasilev avatar
    1061 posts

    Posted 17 Oct 2011 Link to this post

    Hello Victoria,

    Thank you for writing.

    To format the exported data in the appropriate way, you can use grid column's properties ExcelExportType and ExportFormatString:
    this.radGridView1.Columns["Date"].ExcelExportType = DisplayFormatType.Custom;
    this.radGridView1.Columns["Date"].ExcelExportFormatString = "dddd, dd.MM.yyyy";

    Column's width, background and foreground colors usually are automatically transfered even if they are applied by grid's CellFormatting event. ExcelCellFormatting event can be used for any additional and specific export formatting. More details you can find in our product documentation.

    Hope this helps. Let me know if you have any additional questions.

    Greetings,
    Martin Vasilev
    the Telerik team

    Q2’11 SP1 of RadControls for WinForms is available for download (see what's new); also available is the Q3'11 Roadmap for Telerik Windows Forms controls.

  6. Victoria F
    Victoria F avatar
    46 posts
    Member since:
    May 2010

    Posted 26 Oct 2011 Link to this post

    Martin,

    Thank you for this example. It works very good for a simple grid.
    When I started to use export from the grid I counted that formatting of the grid somehow automatically can be apply to the Excel while exporting..
    I have multilevel grid. So if I set column "C" as a Date and my child template suppose to be mapped to column "C" also as a decimal field with a different formatting.
    What should I do in this case ?  How can I format the Excel file?

    Thank you,
    Victoria.
  7. Martin Vasilev
    Admin
    Martin Vasilev avatar
    1061 posts

    Posted 31 Oct 2011 Link to this post

    Hello Victoria F,

    Thank you for getting back to me.

    I confirm that it is possible to have issues with formatting in export hierarchy scenarios, because MS Excel do not natively support hierarchical data and child rows data can get into columns with different type of data.

    You can work-around this limitation by manually specifying the value in ExcelCellFormatting event. For example, you can consider the following code:
    void exporter_ExcelCellFormatting(object sender, ExcelCellFormattingEventArgs e)
    {
        if (e.GridCellInfo.ViewTemplate == this.radGridView1.MasterTemplate.Templates[0]
            && e.GridCellInfo.RowInfo is GridViewDataRowInfo
            && e.GridColumnIndex == 1)
        {
            e.ExcelCellElement.Data.DataType = DataType.String;
            e.ExcelCellElement.Data.DataItem = "my formated value";
        }
    }

    Do not hesitate to contact me again if you have any other questions.

    Greetings,
    Martin Vasilev
    the Telerik team

    Q2’11 SP1 of RadControls for WinForms is available for download (see what's new); also available is the Q3'11 Roadmap for Telerik Windows Forms controls.

  8. Srinivas
    Srinivas avatar
    2 posts
    Member since:
    Sep 2013

    Posted 11 Mar 2014 Link to this post

    Cell data is jumping to next row if data  is  more 
  9. Srinivas
    Srinivas avatar
    2 posts
    Member since:
    Sep 2013

    Posted 12 Mar 2014 in reply to Srinivas Link to this post

    Can  u please reply to above post ,How to remove the cell wrap to only one Column
  10. Anton
    Admin
    Anton avatar
    167 posts

    Posted 14 Mar 2014 Link to this post

    Hello Srinivas,

    Thank you for writing.

    To achieve that you should subscribe to the ExelCellFormatting event and to set the WrapText property to false for each cell in desired column.
    e.ExcelStyleElement.AlignmentElement.WrapText = false;

    You can find more information for exporting to excel in the following documentation article:
    http://www.telerik.com/help/winforms/gridview-exporting-data-export-to-excel-via-excelml-format.html

    I hope this helps.

    Regards,
    Anton
    Telerik
     

    DevCraft Q1'14 is here! Watch the online conference to see how this release solves your top-5 .NET challenges. Watch on demand now.

     
Back to Top
UI for WinForms is Visual Studio 2017 Ready