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

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

13 Answers 429 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Helen
Top achievements
Rank 1
Helen asked on 07 Oct 2010, 08:00 PM
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

13 Answers, 1 is accepted

Sort by
0
Cori
Top achievements
Rank 2
answered on 07 Oct 2010, 09:42 PM
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.

0
Daniel
Telerik team
answered on 07 Oct 2010, 09:42 PM
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
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
Helen
Top achievements
Rank 1
answered on 12 Oct 2010, 08:16 PM
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!
0
Daniel
Telerik team
answered on 15 Oct 2010, 10:15 PM
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
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
Helen
Top achievements
Rank 1
answered on 18 Oct 2010, 07:37 PM
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";
           }


0
Daniel
Telerik team
answered on 19 Oct 2010, 09:50 AM
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
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
Emmanuel
Top achievements
Rank 1
answered on 31 Jul 2012, 03:57 PM
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!!
0
Daniel
Telerik team
answered on 03 Aug 2012, 02:19 PM
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
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
Emmanuel
Top achievements
Rank 1
answered on 06 Aug 2012, 03:35 PM
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.
0
Emmanuel
Top achievements
Rank 1
answered on 07 Aug 2012, 04:07 PM
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!!.

0
Daniel
Telerik team
answered on 09 Aug 2012, 03:36 PM
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
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
Emmanuel
Top achievements
Rank 1
answered on 09 Aug 2012, 10:01 PM
I tried the first option ..no luck. I'll try the second option and let you know.

Thanks,
Emmanuel.
0
Daniel
Telerik team
answered on 14 Aug 2012, 03:03 PM
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
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.
Tags
Grid
Asked by
Helen
Top achievements
Rank 1
Answers by
Cori
Top achievements
Rank 2
Daniel
Telerik team
Helen
Top achievements
Rank 1
Emmanuel
Top achievements
Rank 1
Share this question
or