How to set excel sheet name in Radgrid Export to excel function?

[Helen]

Hi All,

I am new to radgrid, currently I have one issue when trying to export to excel using radgrid.

Seems when you use radgrid.ExportSettings.FileName to set up excel file name, this name will automatically become work sheet name as well. The problem is our requirement need a very long file name, which will be cut off as excel sheet name.We want to set up sheet name short, this way it won't show up as cut off name of excel file name.  Is there anyway we can set up the work sheet name during export process?

Thanks a lot, really appreciate it,
Helen

[Cori]

Hello Helen,

You'll need to set the Excel export type to ExceML and handle the ExcelMLExportRowCreated event. In that event you can set the worksheet name like so:

e.Worksheet.Name = "Some Worksheet Name";

I hope that helps.

[Daniel]

Hello Helen,

You can set the worksheet name in ExcelML this way:

protected void RadGrid1_ExcelMLExportRowCreated(object sender, GridExportExcelMLRowCreatedArgs e)
{
    e.Worksheet.Name = "Name";
}

I'm afraid it is not possible to change the sheet name out-of-the-box when using the HTML format. This functionality will be added in a future version of RadControls for ASP.NET AJAX.

Regards,
Daniel
the Telerik team

[Helen]

Thanks for the reply. I tried the approach you suggested, However, the wired thing happened, The work sheet name is changed to "Name" correctly, but all the data are gone. I end up have empty rows with no data inside.Does this method conflicts with OnGridExporting ? or Did I miss anything?


The code added in the aspx file:
onexcelmlexportrowcreated="rgSummaryReport_ExcelMLExportRowCreated"

The coded I added in cs file:
 rgSummaryReport.ExportSettings.Excel.Format = GridExcelExportFormat.ExcelML;
                rgSummaryReport.ExportSettings.ExportOnlyData = true;
                rgSummaryReport.ExportSettings.IgnorePaging = true;
                rgSummaryReport.ExportSettings.OpenInNewWindow = true;
rgSummaryReport.MasterTableView.ExportToExcel();

and added event handler:
protected void rgSummaryReport_ExcelMLExportRowCreated(object sender, GridExportExcelMLRowCreatedArgs e)
        {
            e.Worksheet.Name = "Name";        
        }


Thanks!

[Daniel]

Hello Helen,

Yes, this could happen if you modify the output in the GridExporting handler. Can you please post your code here?

Regards,
Daniel
the Telerik team

[Helen]

Hi,

Thanks for the reply. Original code are list below. Actually if I moved the OnGridExporting and  OnExcelExportCellFormatting events and only keep onexcelmlexportrowcreated in the aspx. The issue is still the same ---only change the work sheet name but data are gone. So I don't quite understand why this would happen.

Any suggestion is appreciated, thanks in advance!

1. ASPX page:

<div id="divSummaryReport">
        <telerik:RadGrid
            runat="server"
            ID="rgSummaryReport"
            AllowPaging="false"
            CssClass="GridLines"
            AllowSorting="false"
            AllowFilteringByColumn="false"
            Visible="false"
            AllowMultiRowSelection="false"
            Enabled="true"
            AutoGenerateColumns="false"
            OnGridExporting="rgSummaryReport_GridExporting"
            OnExcelExportCellFormatting ="rgSummaryReport_ExcelExportCellFormatting"
            onexcelmlexportrowcreated="rgSummaryReport_ExcelMLExportRowCreated">
            <HeaderStyle HorizontalAlign="Center" Font-Bold="true" />
            <ItemStyle HorizontalAlign="Center" />
            <exportsettings>
                <excel format="ExcelML" />
            </exportsettings>
            <AlternatingItemStyle HorizontalAlign="Center" />
            <MasterTableView>
                <Columns>
                    <telerik:GridBoundColumn DataField="CountyState" HeaderText="County State"></telerik:GridBoundColumn>
                    <telerik:GridTemplateColumn HeaderText="State County Code">
                        <ItemTemplate>
                            <div style="mso-number-format:\@;"><%# Eval("CountyCode")%></div>
                        </ItemTemplate>
                    </telerik:GridTemplateColumn>
                    <telerik:GridBoundColumn DataField="CountyDesignation" HeaderText="County Designation"></telerik:GridBoundColumn>
                    <telerik:GridBoundColumn DataField="SpecialtyType" HeaderText="Specialty Type"></telerik:GridBoundColumn>
                    <telerik:GridTemplateColumn HeaderText="Specialty Code">
                        <ItemTemplate>
                            <div style="mso-number-format:\@;"><%# Eval("SpecialtyCode")%></div>
                        </ItemTemplate>
                    </telerik:GridTemplateColumn>                   
                    <telerik:GridBoundColumn DataField="Status" HeaderText="Status"></telerik:GridBoundColumn>
                </Columns>
            </MasterTableView>
        </telerik:RadGrid>
    </div>
2. CS file:
        protected void rgSummaryReport_GridExporting(object source, Telerik.Web.UI.GridExportingArgs e)
        {
            string title = _reportCriteria.AppIsFinalSubmitted ? " Final-Submission Report" : "Pre-Submission Report";
            StringBuilder strHeader = new StringBuilder("<h3><center>" + title + "</center></h3><br>");
            strHeader.Append("<h5><b>Contract ID: " + Session["contract_id"] + "<br>");
            strHeader.Append("Contract Name: " + Session["contract_name"] + "<br><br>");
            strHeader.Append("Report data is valid as of " + lblReportTimestamp.Text + "</b></h5>");

            e.ExportOutput = e.ExportOutput.Replace("<head>", "<head><style type=\"text/css\">td {border: 0.1pt solid #000000;}</style>");
            e.ExportOutput = e.ExportOutput.Replace("<body>", "<body>" + strHeader.ToString());
            e.ExportOutput = e.ExportOutput.Replace("</body>", "Report Downloaded: " + DateTime.Now.ToString() + "</body>");

        }



        protected void rgSummaryReport_ExcelExportCellFormatting(object source, ExcelExportCellFormattingEventArgs e)
        {
            e.Cell.Style["text-align"] = "center";
        }
 protected void rgSummaryReport_ExcelMLExportRowCreated(object sender, GridExportExcelMLRowCreatedArgs e)
        {
            e.Worksheet.Name = "Name";
           }

[Daniel]

Hello Helen,

I noticed that you mix HTML and ExcelML-specific code. For example, ExcelExportCellFormatting event will be fired only when using the HTML format. On the other hand the ExcelMLExportRowCreated and ExcelMLExportStylesCreated events are triggered for ExcelML only.

I also noticed that the GridExporting handler contains HTML-specific code. This event will fire for all export formats so you should be careful not to break the generated output.
If you use both formats you should differentiate them using the e.ExportType property

protected void RadGrid1_GridExporting(object sender, GridExportingArgs e)
{
    if (e.ExportType == ExportType.Excel)
    {
        //...
    }
    else if (e.ExportType == ExportType.ExcelML)
    {
        //...
    }
}

Please examine the following links for more information about the differences between HTML and ExcelML formats:
Word/Excel export (HTML-based)
ExcelML basics

Best regards,
Daniel
the Telerik team

[Emmanuel]

I have the same issue . When I add the code RadGrid1.ExportSettings.Excel.Format = GridExcelExportFormat.ExcelML;  and specify my sheet name like below

protected

 

 

void RadGrid1_ExcelMLExportRowCreated(object sender, GridExportExcelMLRowCreatedArgs e)

 

{

e.Worksheet.Name =

 

"MySheetName";

 

}

all the data are disappread from excel. Once I remove above lines of code everything works perfectly.

Here is my code snipplet

protected void btnExport_Click(object sender, EventArgs e)
{
 RadGrid1.ExportSettings.Excel.Format = GridExcelExportFormat.ExcelML;
 RadGrid1.ExportSettings.ExportOnlyData = true;
 RadGrid1.ExportSettings.HideStructureColumns = true;
 RadGrid1.ExportSettings.IgnorePaging = true;
 RadGrid1.MasterTableView.CommandItemDisplay = GridCommandItemDisplay.None;
 RadGrid1.MasterTableView.ExportToExcel();
}

Any Ideas!!

[Daniel]

Hello Emmanuel,

Let me know how to reproduce this issue, please. I'm unable to recreate your scenario locally as you can see in the attached demo.

Daniel,
the Telerik team

[Emmanuel]

Thank you for looking into this. I've a different  work around(time being that works). I tried your sample it works fine. I will try to replciate my scenario in your project and send to you soon.

Thanks,
Emmanuel.

[Emmanuel]

I've a RadGrid with scroll option enabled by below settings

<ClientSettings>

 <Scrolling AllowScroll="True" UseStaticHeaders="true" SaveScrollPosition="false" >

</Scrolling>

</ClientSettings>

and need to insert a record(with editform template) performing some validations. Once the validation check is over , grid automatically creates a new row same as previous row. When we try to hit the save button it throws below error:

Microsoft JScript runtime error:
Sys.WebForms.PageRequestManagerServerErrorException: Invalid JSON primitive: .

Attached screenshot for reference.. Contents in red color box is created automatically by grid after the validations at server side.

When I remove the Scroll settings and perform insert operation everything works fine. No idea when scroll option is enabled grid behave differently.

Any ideas!!.

[Daniel]

Hello Emmanuel,

I have two suggestions for you:
- try to disable the StaticHeaders only (e.g. do not disable the scrolling, just the static headers)
- disable AJAX temporarily (EnableAjax="false")

Let me know what happens when you try these.

Best regards,
Daniel
the Telerik team

[Emmanuel]

I tried the first option ..no luck. I'll try the second option and let you know.

Thanks,
Emmanuel.

[Daniel]

Hello Emmanuel,

If it happens that my suggestions does not affect the problem, please post your code so that I can try to reproduce this behavior on my end.

Regards,
Daniel
the Telerik team