Wrapping text in an Excel cell via GridExcelBuilder

18 posts, 1 answers
  1. Martillo
    Martillo avatar
    29 posts
    Member since:
    Dec 2006

    Posted 25 Apr 2010 Link to this post

    When using the GridExcelBuilder to add and format a new row when exporting from the RadGrid, is there a way to set a cell within the row to wrap text? This would give the same effect as if you invoked the "Alignment > Text Control > Wrap Text" option in the Format Cells property sheet in Excel. Hopefully the code snippet below will give an idea of what I'm trying to do.

    protected void Grid_ExcelMLExportRowCreated ( object source, Telerik.Web.UI.GridExcelBuilder.GridExportExcelMLRowCreatedArgs e ) 
        if ( e.RowType == GridExportExcelMLRowType.HeaderRow ) 
        { 
            RowElement myNewRow = new RowElement(); 
            CellElement myNewCell = new CellElement(); 
            myNewCell.StyleValue = "myNewStyle"
            myNewCell.MergeAcross = e.Row.Cells.Count - 1; 
            //now call the hypothetical property which doesn't seem to exist... 
            myNewCell.WrapText = true
            //etc... 
        } 

    Thanks,

    Keith

  2. Daniel
    Admin
    Daniel avatar
    4753 posts
    Member since:
    Sep 2012

    Posted 26 Apr 2010 Link to this post

    Hello Martillo,

    You should use a style to enable the WordWrap feature (disabled by default):

    protected void RadGrid1_ExcelMLExportRowCreated(object source, GridExportExcelMLRowCreatedArgs e)
    {     
        if (e.RowType == GridExportExcelMLRowType.DataRow)
        {
            ...
            cell.StyleValue = "myStyle";
            ...
        }
    }

    protected void RadGrid1_ExcelMLExportStylesCreated(object source, GridExportExcelMLStyleCreatedArgs e)
    {
        StyleElement myStyle = new StyleElement("myStyle");
        myStyle.AlignmentElement.Attributes["ss:WrapText"] = "1";
        e.Styles.Add(myStyle);
    }

    Kind regards,
    Daniel
    the Telerik team

    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 Public Issue Tracking system and vote to affect the priority of the items.

  3. Martillo
    Martillo avatar
    29 posts
    Member since:
    Dec 2006

    Posted 26 Apr 2010 Link to this post

    Thanks Daniel,

    That worked perfectly! The Excel file was correctly generated with Wrap Text set to "true" for the cells where the style in question had been applied.

    However, when the content was sufficiently long that it wrapped into multiple lines within the cell, the height of the row did not adjust to accomodate the content, so initially you only see a single line of the multi-line content.

    Is there a way to set the row height to adjust automatically to fit the contents? This would emulate the "Auto-fit row height" functionality explained in this MS help page.

    Thanks,

    Keith

  4. Daniel
    Admin
    Daniel avatar
    4753 posts
    Member since:
    Sep 2012

    Posted 30 Apr 2010 Link to this post

    Hello Martillo,

    There is a demo attached to this post - I hope it will help you implement the desired functionality in your project.

    1) ExcelMLExportRowCreated:

    protected void RadGrid1_ExcelMLExportRowCreated(object source, GridExportExcelMLRowCreatedArgs e)
    {
        if (e.RowType == GridExportExcelMLRowType.DataRow)
        {
            foreach (GridColumn col in RadGrid1.MasterTableView.Columns)
            {
                ...
                CellElement xlCell = e.Row.Cells.GetCellByName(col.UniqueName);
                xlCell.StyleValue = "myStyle";
                e.Row.Attributes.Add("ss:AutoFitHeight", "1");
            }
        }
    }

    2) ExcelMLExportStylesCreated
    protected void RadGrid1_ExcelMLExportStylesCreated(object source, GridExportExcelMLStyleCreatedArgs e)
    {
        StyleElement style = new StyleElement("myStyle");
        style.AlignmentElement.Attributes.Add("ss:WrapText", "1");
        e.Styles.Add(style);
    }

    Best regards,
    Daniel
    the Telerik team

    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 Public Issue Tracking system and vote to affect the priority of the items.

  5. Martillo
    Martillo avatar
    29 posts
    Member since:
    Dec 2006

    Posted 30 Apr 2010 Link to this post

    Thanks Daniel,

    Your example works perfectly... except that the rows where I needed to set AutoFitHeight to true contained merged cells. Not being an Excel expert, I didn't realize that once you merge cells, Excel (through version 2007 at least) no longer automatically adjusts the row height. Otherwise my problem would have been solved once I set Wrap Text to true, since Excel automatically adjusts the row height when Wrap Text is true.

    It looks like there may not be a way around this without VBA coding. However, an acceptable workaround in my situation would be to center horizontally across a selection of cells (the selection being the cells that otherwise would have been merged)  by setting the StyleElement.AlignmentElement.HorizontalAlignment to HorizontalAlignmentType.CenterAcrossSelection. However, after searching through the forums and looking through the GridExcelBuilder documentation I couldn't find a way to apply this, which I assume would require flagging a range of cells as "selected". Do you have anything in your bag of tricks that could help me out?

    Thanks,

    Keith

  6. Daniel
    Admin
    Daniel avatar
    4753 posts
    Member since:
    Sep 2012

    Posted 04 May 2010 Link to this post

    Hello Keith,

    Yes, you are correct about the default behavior. Unfortunately not all alignment types are supported by XMLSS Fill, Justify, CenterAcrossSelection to name a few.

    There is no easy way to sidestep this limitation. One option could be to increase the row height manually. The default row height is 12.75 units so you can double the height this way:

    double defaultHeight = 12.75;
    e.Row.Attributes.Add("ss:Height", (defaultHeight * 2).ToString());

    Best regards,
    Daniel
    the Telerik team

    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 Public Issue Tracking system and vote to affect the priority of the items.

  7. Martillo
    Martillo avatar
    29 posts
    Member since:
    Dec 2006

    Posted 04 May 2010 Link to this post

    Thanks Daniel. i may try your workaround.

    Would HTML export offer a solution to this particular predicament?

    Since I need to add addtional header rows to the Excel worksheet above the exported data from the RadGrid, I think I'm probably better off sticking with the ExcelML format, but I would be interested to hear what you think.

    Thanks,

    Keith

  8. Daniel
    Admin
    Daniel avatar
    4753 posts
    Member since:
    Sep 2012

    Posted 07 May 2010 Link to this post

    Hello Keith,

    As far as I know there is no way to set Center Across Selection alignment in the HTML format - I tried several different approaches but unfortunately to no avail.

    In my opinion, ExcelML format would be the better choice for most scenarios but of course, the HTML format also has its own advantages - for example:
    - supports aggregates and calculated/template columns out-of-the-box
    - easier for troubleshooting (plain HTML code inside)
    - partially supported in OpenOffice and other non-Microsoft products (since it is HTML based)
    - supports CSS code (if you consider this as an advantage)

    Kind regards,
    Daniel
    the Telerik team


    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 Public Issue Tracking system and vote to affect the priority of the items.

  9. Martillo
    Martillo avatar
    29 posts
    Member since:
    Dec 2006

    Posted 07 May 2010 Link to this post

    Hi Daniel,

    Thank you for doing the additional research, and also for the information about the advantages of the Excel HTML format. The out-of-the box support for aggregates and calculated columns is definitely an advantage.

    I've been thinking that a pros/cons decision chart of the ExcelML vs HTML formats would help in making a decision about which format to implement. I was thinking about entering a PITS feature request  to suggest a decision chart as a part of the next documentation update, but I'll just mention it here and let it go at that.

    One last question: I need to add a multi-row header above the exported grid. The header includes information that is not displayed on the web page. Thanks to your help, I've been able to do this with ExcelML. When exporting to HTML, is it possible to inject additional rows into the output without doing triple backflips in your code?

    Thanks again,

    Keith

    BTW, I would generally consider CSS support to be an advantage.

  10. Answer
    Daniel
    Admin
    Daniel avatar
    4753 posts
    Member since:
    Sep 2012

    Posted 13 May 2010 Link to this post

    Hello Keith,

    Yes, it is possible to insert additional information when using HTML Excel but in the most cases you will have to modify the HTML code in code-behind (on GridExporting event), which is not very elegant approach (nor convenient) in my opinion. Let me know if you need a sample project.

    As to the comparison chart - we will consider the option to add such chart in the near future. For now we will focus our efforts on the documentation, online demos and last but not least onto the new features.

    Regards,
    Daniel
    the Telerik team


    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 Public Issue Tracking system and vote to affect the priority of the items.

  11. Martillo
    Martillo avatar
    29 posts
    Member since:
    Dec 2006

    Posted 13 May 2010 Link to this post

    Hi Daniel,

    Thanks for the information. Thank you also for the offer of a sample project, but for now I think I will be able to satisfy my project's requirements usinig ExcelML, so I'll stick with that for now.

    Regards,

    Keith

  12. Subramanyam Rompicherla
    Subramanyam  Rompicherla avatar
    1 posts
    Member since:
    Jul 2009

    Posted 06 Dec 2012 Link to this post

    Its working for me, and given base idea about GridExportExcelMLStyleCreatedArgs

    ThanQ very much  :)

     

  13. Radha
    Radha avatar
    19 posts
    Member since:
    Dec 2012

    Posted 19 Dec 2012 Link to this post

    Hello Daniel,
    I am not able to acess RadGrid1.MasterTableView data as it is showing as null even though I am setting the datasource of RadGrid1 in NeedDatasource event.Could you please tell me what would be the reason.         
    foreach
    (GridColumn col in RadGrid1.MasterTableView.Columns)

            {
                ...
                CellElement xlCell = e.Row.Cells.GetCellByName(col.UniqueName);
                xlCell.StyleValue = "myStyle";
                e.Row.Attributes.Add("ss:AutoFitHeight", "1");
            }

  14. Daniel
    Admin
    Daniel avatar
    4753 posts
    Member since:
    Sep 2012

    Posted 21 Dec 2012 Link to this post

    Hello Radha,

    I'm not completely sure what could be the problem. Are you creating the control programatically? Do you have something specific in this case? Please paste your code here so that I can take a look.

    Kind regards,
    Daniel
    the Telerik team

    If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.

  15. Radha
    Radha avatar
    19 posts
    Member since:
    Dec 2012

    Posted 24 Dec 2012 Link to this post

    Hello Daniel,

         No we are not creating the control programatically but still i could not able to access mastertableview data.Instead of, mastertable view i used the following lines of code to apply cell style which worked perfectly.

     

    protected void grdReport_ExcelMLExportRowCreated(object sender, GridExportExcelMLRowCreatedArgs e)

     

    {


     

    if (e.RowType == GridExportExcelMLRowType.HeaderRow)

     

    {

     

    foreach (CellElement cell in e.Row.Cells)

     

    {

    cell.StyleValue =

    "wstyle";

     

    }

    }

    if (e.RowType == GridExportExcelMLRowType.DataRow)

     

    {

     

    foreach (CellElement cell in e.Row.Cells)

     

    {

    cell.StyleValue =

    "myCustomStyle";

     

    }

    }
    }
    Thanks
    Radha

  16. Daniel
    Admin
    Daniel avatar
    4753 posts
    Member since:
    Sep 2012

    Posted 26 Dec 2012 Link to this post

    Hello Radha,

    You should not have a problem to access the MasterTableView object in normal circumstances.
    That said, I'm glad to hear that you have found a solution for your case, but if you want me to examine the problem for you, please isolate it in a simple working demo so that I can debug it on my end.

    Kind regards,
    Daniel
    the Telerik team

    If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.

  17. Vijender Reddy
    Vijender Reddy avatar
    3 posts
    Member since:
    Jul 2012

    Posted 01 Nov 2013 Link to this post

    Hello,
        I am getting e.Row.Cells.GetCellByName always null, I have column name correctly but it is unable to get the cell and is always null, its a bit urgent please reply as early as you could. My grid is binding in codebehind in needdatasource by getting datatable from DB.

  18. Kostadin
    Admin
    Kostadin avatar
    1009 posts
    Member since:
    Jul 2012

    Posted 06 Nov 2013 Link to this post

    Hello Vijender,

    I tried to replicate the issue on my side but to no avail. Could you please provide your code declaration and the related code behind in order to investigate the issue further? Additionally I prepared a small sample and attached it to this forum thread.

    Regards,
    Kostadin
    Telerik

    If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to the blog feed now.

Back to Top