Wrapping text in an Excel cell via GridExcelBuilder

[Martillo]

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

[Daniel]

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.

[Martillo]

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

[Daniel]

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.

[Martillo]

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

[Daniel]

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.

[Martillo]

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

[Daniel]

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.

[Martillo]

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.

[Daniel]

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.

[Martillo]

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

[Subramanyam Rompicherla]

Its working for me, and given base idea about GridExportExcelMLStyleCreatedArgs

ThanQ very much  :)

 

[Radha]

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");

        }

[Daniel]

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

[Radha]

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

[Daniel]

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

[Vijender Reddy]

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.

[Kostadin]

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