This is a migrated thread and some comments may be shown as answers.

Wrapping text in an Excel cell via GridExcelBuilder

17 Answers 605 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Martillo
Top achievements
Rank 2
Martillo asked on 26 Apr 2010, 04:07 AM
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

17 Answers, 1 is accepted

Sort by
0
Daniel
Telerik team
answered on 26 Apr 2010, 09:24 PM
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.
0
Martillo
Top achievements
Rank 2
answered on 27 Apr 2010, 01:16 AM
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
0
Daniel
Telerik team
answered on 30 Apr 2010, 11:23 AM
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.
0
Martillo
Top achievements
Rank 2
answered on 30 Apr 2010, 06:54 PM
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
0
Daniel
Telerik team
answered on 04 May 2010, 10:03 AM
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.
0
Martillo
Top achievements
Rank 2
answered on 04 May 2010, 05:27 PM
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
0
Daniel
Telerik team
answered on 07 May 2010, 02:12 PM
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.
0
Martillo
Top achievements
Rank 2
answered on 07 May 2010, 04:15 PM
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.
0
Accepted
Daniel
Telerik team
answered on 13 May 2010, 09:33 AM
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.
0
Martillo
Top achievements
Rank 2
answered on 13 May 2010, 04:40 PM
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
0
Subramanyam Rompicherla
Top achievements
Rank 1
answered on 06 Dec 2012, 07:53 AM
Its working for me, and given base idea about GridExportExcelMLStyleCreatedArgs

ThanQ very much  :)

 

0
Radha
Top achievements
Rank 1
answered on 19 Dec 2012, 09:08 AM
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");
        }
0
Daniel
Telerik team
answered on 21 Dec 2012, 09:16 AM
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.
0
Radha
Top achievements
Rank 1
answered on 24 Dec 2012, 11:25 AM
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

0
Daniel
Telerik team
answered on 26 Dec 2012, 09:27 AM
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.
0
Vijender Reddy
Top achievements
Rank 1
answered on 01 Nov 2013, 08:57 PM
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.
0
Kostadin
Telerik team
answered on 06 Nov 2013, 12:19 PM
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.
Tags
Grid
Asked by
Martillo
Top achievements
Rank 2
Answers by
Daniel
Telerik team
Martillo
Top achievements
Rank 2
Subramanyam Rompicherla
Top achievements
Rank 1
Radha
Top achievements
Rank 1
Vijender Reddy
Top achievements
Rank 1
Kostadin
Telerik team
Share this question
or