[Solved] Export to Excel Issues

[Paul J]

I'm having a variety of issues with exporting data from a radGrid to Excel:
I'm using the default excel export format.

1. The spreadsheet generated does not show any excel default gridlines (i.e. the ones you see when you create a new, blank spreadsheet. the light blue ones). is there a way to retain these?

2. I have a column in my Grid that shows Address information (string value) for display purposes to the web page screen, I put a <br /> in between the Address street line and the city, state line so that there's always a line break before it shows the city and state information. This html line break is causing excel to put the contents into a new cell, instead of keep the street address and city and state information in the same cell and just wrap it. How can I fix it? (Maybe i could intercept the contents/value of this column and replace the <br /> with a blank, if so, what code can I use to do such a thing?

3. I was also interested in checking out the Excel Format ExcelML, but when I turn it on, I get a blank spreadsheet. Where are there good, quality, demos on how to use the ExcelML option for formatting and exporting of Grid data?

my current export code:

 rgFailedRecords.ExportSettings.ExportOnlyData = True
        rgFailedRecords.ExportSettings.IgnorePaging = True
        rgFailedRecords.ExportSettings.OpenInNewWindow = True

        rgFailedRecords.MasterTableView.ExportToExcel()

I'm also setting some formatting stuff within rgFailedRecords_ExcelExportCellFormatting; just some cell style width's, style, etc.

[Daniel]

Hello Paul,

Straight onto your questions:

1) This is limitation of the HTML Excel format - you can mimic the grid lines if you put this CSS class in the head tag of the exported file:

body
{
    border: solid 0.1pt #CCCCCC;
}

Word/Excel export (HTML-based)

2) This behavior could be overridden using another CSS class:

br
{
    mso-data-placement: same-cell;
}

 
3) Please try to set UseAllDataFields="true" and try advanced data-binding

<MasterTableView UseAllDataFields="true" ...

ExcelML export help topic

Regards,
Daniel
the Telerik team

Watch a video on how to optimize your support resource searches and check out more tips on the blogs.
Follow the status of features or bugs in PITS and vote for them to affect their priority.

[Paul J]

Thanks for the tips. definitely a help. I'm still having issues with #2 above though. I'm trying the following in vb.net within the _ExcelExportCellFormatting Sub:

 item.Style("br") = "mso-data-placement:same-cell"

which is not right. I also tried:  item.Style("mso-data-placement") = "same-cell" which also did not work. How do i specifically target the br tag? (please specify the actual code. thanks)

[Daniel]

Hello Paul,

You should put this CSS class in the head tag:

protected void RadGrid1_GridExporting(object source, GridExportingArgs e)
{
    if (e.ExportType == ExportType.Excel)
    {
        string css = "<style type='text/css'> br { mso-data-placement: same-cell; } </style>";
        e.ExportOutput = e.ExportOutput.Replace("</head>", css + "</head>");
    }
}

Protected Sub RadGrid1_GridExporting(source As Object, e As GridExportingArgs)
    If e.ExportType = ExportType.Excel Then
        Dim css As String = "<style type='text/css'> br { mso-data-placement: same-cell; } </style>"
        e.ExportOutput = e.ExportOutput.Replace("</head>", css + "</head>")
    End If
End Sub

Best regards,
Daniel
the Telerik team

Watch a video on how to optimize your support resource searches and check out more tips on the blogs.
Follow the status of features or bugs in PITS and vote for them to affect their priority.

[Paul J]

Ah, okay, so that's how you add global styles to the export.

Thanks a billion. As always, telerik's support is impeccable.

- Paul