RadGrid Export to Excel Nuances

[Andrew]

Hi, I am using the ExcelML export format and there are a few issues that I am encountering and looking for help to resolve.

1. While exporting in the ExcelMLExportRowCreated event handler, I am trying to add indentation to a cell text by adding spaces but my spaces are being trimmed. How can I add a tab or spaces in front of the cell text?

2. I don't see a way to hide the workbook gridlines. If there is not a way, I could manipulate the XML before it renders and add <DoNotDisplayGridlines/> to the WorksheetOptions node. Where/how, can I manipulate the XML output?

3. Is there a way to autosize the columns or execute a script to do it on open or calculate the correct size after the worksheet is built and adjust the size of the columns?

Thanks for your help.
 

[Andrew]

#1 is resolved:
StyleElement styleElement = new StyleElement("TextStyleIndented");
styleElement.FontStyle.FontName = "Calibri";
styleElement.FontStyle.Size = 10.0;
styleElement.AlignmentElement.Attributes.Add("ss:Indent", "2");
e.WorkBook.Styles.Add(styleElement);

[Kostadin]

Hello Andrew,

Note that by default the Excel document does not have a borders around the cells. Could you please let me know whether you have add a border before exporting the grid? Also you can try setting the GridLines property to None when export command is fired.

protected void RadGrid1_ItemCommand(object sender, Telerik.Web.UI.GridCommandEventArgs e)
{
    if (e.CommandName == RadGrid.ExportToExcelCommandName)
    {
        RadGrid1.GridLines = GridLines.None;
    }
}

Regards your second issue I am afraid you can not autosize the columns. Nevertheless you can set a Width of the columns when export to Excel command is fired.

protected void RadGrid1_ItemCommand(object sender, Telerik.Web.UI.GridCommandEventArgs e)
{
    if (e.CommandName == RadGrid.ExportToExcelCommandName)
    {
        RadGrid1.GridLines = GridLines.None;
        foreach (GridColumn col in RadGrid1.MasterTableView.Columns)
        {
            col.HeaderStyle.Width = Unit.Pixel(100);
        }
    }
}

Regards,
Kostadin
Telerik

 

 

[Andrew]

Hi and thanks for the reply.

The grid is not exporting with borders, I actually want to hide the gridlines in Excel that show by default. In Excel we can hide them in the PageLayout->Sheet Options->Gridlines and uncheck View

For the column size question, is good to know that I can change the column width. However, I's like to size them to the size of the contents. Do you know how to measure the contents based on length and format?

Thanks
​

[Kostadin]

Hi Andrew,

I am afraid there isn't a setting that allows you to remove the Excel borders. Regards the second question such measuring is quite difficult since there can be many different scenarios. Nevertheless a possible solution is to check the number of characters of the cell text and based on it to set a width.

Regards,
Kostadin
Telerik