RadGrid Export to Excel Formatting

[Marvin]

We bought the complete license of Telerik controls, I've been using the Radgrid mainly to do the Export to Excel.

I have two problems that needs to be solved:

1. How do I manually add text or header to Row 1 of the excel file and then start all bound items from the Dataset on Row 2 downwards?
(see attachment1.png)
2. How do I remove the filering/sorting that is automatically added to the columns when the excel file is exported. I need to retain the colors I defined but I don't want the dropdown button for each column.
(see attachment2.png)

Thanks,

Here are my codes:
codebehind

bool isConfigured = false;
        protected void RadGrid1_ExcelMLExportRowCreated(object source, GridExportExcelMLRowCreatedArgs e)
        {

            if (e.RowType == GridExportExcelMLRowType.HeaderRow)
            {

                //Add custom styles to the desired cells
                CellElement cell = e.Row.Cells.GetCellByName("Number");
                cell.StyleValue = "normalHeaderStyle";

                cell = e.Row.Cells.GetCellByName("Name");
                cell.StyleValue = "normalHeaderStyle";

                cell = e.Row.Cells.GetCellByName("Weight in Grams");
                cell.StyleValue = "normalHeaderStyle";

                cell = e.Row.Cells.GetCellByName("Energie");
                cell.StyleValue = "perTotalHeaderStyle";

                cell = e.Row.Cells.GetCellByName("Eiweiss");
                cell.StyleValue = "perTotalHeaderStyle";

                cell = e.Row.Cells.GetCellByName("Fett");
                cell.StyleValue = "perTotalHeaderStyle";

                cell = e.Row.Cells.GetCellByName("KH");
                cell.StyleValue = "perTotalHeaderStyle";

                cell = e.Row.Cells.GetCellByName("Nahrungsfasern, total");
                cell.StyleValue = "perTotalHeaderStyle";



                cell = e.Row.Cells.GetCellByName("Energie1");
                cell.StyleValue = "per100HeaderStyle";

                cell = e.Row.Cells.GetCellByName("Eiweiss1");
                cell.StyleValue = "per100HeaderStyle";

                cell = e.Row.Cells.GetCellByName("Fett1");
                cell.StyleValue = "per100HeaderStyle";

                cell = e.Row.Cells.GetCellByName("KH1");
                cell.StyleValue = "per100HeaderStyle";

                cell = e.Row.Cells.GetCellByName("Nahrungsfasern, total1");
                cell.StyleValue = "per100HeaderStyle";



                cell = e.Row.Cells.GetCellByName("Energie CAL.");
                cell.StyleValue = "perTotalHeaderStyle";
            }



            if (e.RowType == GridExportExcelMLRowType.DataRow)
            {


                //Add custom styles to the desired cells
                CellElement cell = e.Row.Cells.GetCellByName("Number");
                cell.StyleValue = cell.StyleValue == "itemStyle" ? "itemNormalStyle" : "itemNormalStyle";

                cell = e.Row.Cells.GetCellByName("Name");
                cell.StyleValue = cell.StyleValue == "itemStyle" ? "itemNormalStyle" : "itemNormalStyle";

                cell = e.Row.Cells.GetCellByName("Weight in Grams");
                cell.StyleValue = cell.StyleValue == "itemStyle" ? "itemNormalStyle" : "itemNormalStyle";

                cell = e.Row.Cells.GetCellByName("Energie");
                cell.StyleValue = cell.StyleValue == "itemStyle" ? "itemNutrientStyle" : "itemNutrientStyle";

                cell = e.Row.Cells.GetCellByName("Eiweiss");
                cell.StyleValue = cell.StyleValue == "itemStyle" ? "itemNutrientStyle" : "itemNutrientStyle";

                cell = e.Row.Cells.GetCellByName("Fett");
                cell.StyleValue = cell.StyleValue == "itemStyle" ? "itemNutrientStyle" : "itemNutrientStyle";

                cell = e.Row.Cells.GetCellByName("KH");
                cell.StyleValue = cell.StyleValue == "itemStyle" ? "itemNutrientStyle" : "itemNutrientStyle";

                cell = e.Row.Cells.GetCellByName("Nahrungsfasern, total");
                cell.StyleValue = cell.StyleValue == "itemStyle" ? "itemNutrientStyle" : "itemNutrientStyle";

                cell = e.Row.Cells.GetCellByName("Energie1");
                cell.StyleValue = cell.StyleValue == "itemStyle" ? "itemNutrientStyle" : "itemNutrientStyle";

                cell = e.Row.Cells.GetCellByName("Eiweiss1");
                cell.StyleValue = cell.StyleValue == "itemStyle" ? "itemNutrientStyle" : "itemNutrientStyle";

                cell = e.Row.Cells.GetCellByName("Fett1");
                cell.StyleValue = cell.StyleValue == "itemStyle" ? "itemNutrientStyle" : "itemNutrientStyle";

                cell = e.Row.Cells.GetCellByName("KH1");
                cell.StyleValue = cell.StyleValue == "itemStyle" ? "itemNutrientStyle" : "itemNutrientStyle";

                cell = e.Row.Cells.GetCellByName("Nahrungsfasern, total1");
                cell.StyleValue = cell.StyleValue == "itemStyle" ? "itemNutrientStyle" : "itemNutrientStyle";

                cell = e.Row.Cells.GetCellByName("Energie CAL.");
                cell.StyleValue = cell.StyleValue == "itemStyle" ? "itemNutrientStyle" : "itemNutrientStyle";

                if (!isConfigured)
                {
                    //Set Worksheet name
                    e.Worksheet.Name = "Nährwerte";

                    //Set Column widths
                    foreach (ColumnElement column in e.Worksheet.Table.Columns)
                    {
                        if (e.Worksheet.Table.Columns.IndexOf(column) == 1)
                            column.Width = Unit.Point(180); //set width 180 to ProductName column
                        else
                            column.Width = Unit.Point(80); //set width 80 to the rest of the columns
                    }

                    //Set Page options
                    PageSetupElement pageSetup = e.Worksheet.WorksheetOptions.PageSetup;
                    pageSetup.PageLayoutElement.IsCenteredVertical = true;
                    pageSetup.PageLayoutElement.IsCenteredHorizontal = true;
                    pageSetup.PageMarginsElement.Left = 0.5;
                    pageSetup.PageMarginsElement.Top = 0.5;
                    pageSetup.PageMarginsElement.Right = 0.5;
                    pageSetup.PageMarginsElement.Bottom = 0.5;
                    pageSetup.PageLayoutElement.PageOrientation = PageOrientationType.Landscape;

                    //Freeze panes
                    //e.Worksheet.WorksheetOptions.AllowFreezePanes = true;
                    //e.Worksheet.WorksheetOptions.LeftColumnRightPaneNumber = 3;
                    //e.Worksheet.WorksheetOptions.TopRowBottomPaneNumber = 1;
                    //e.Worksheet.WorksheetOptions.SplitHorizontalOffset = 1;
                    //e.Worksheet.WorksheetOptions.SplitVerticalOffest = 1;

                    //e.Worksheet.WorksheetOptions.ActivePane = 4;
                    isConfigured = true;
                }
            }
        }

        protected void RadGrid1_ExcelMLExportStylesCreated(object source, GridExportExcelMLStyleCreatedArgs e)
        {
            //Add currency and percent styles
            StyleElement pertotalHeaderStyle = new StyleElement("perTotalHeaderStyle");
            pertotalHeaderStyle.FontStyle.Color = System.Drawing.Color.Black;
            pertotalHeaderStyle.FontStyle.Bold = true;
            pertotalHeaderStyle.FontStyle.FontName = "Calibri";
            pertotalHeaderStyle.FontStyle.Size = 10.0;
            e.Styles.Add(pertotalHeaderStyle);

            StyleElement per100HeaderStyle = new StyleElement("per100HeaderStyle");
            per100HeaderStyle.FontStyle.Color = System.Drawing.Color.Black;
            per100HeaderStyle.FontStyle.Bold = true;
            per100HeaderStyle.FontStyle.FontName = "Calibri";
            per100HeaderStyle.FontStyle.Size = 10.0;
            e.Styles.Add(per100HeaderStyle);

            StyleElement normalHeaderStyle = new StyleElement("normalHeaderStyle");
            normalHeaderStyle.FontStyle.Color = System.Drawing.Color.Black;
            normalHeaderStyle.FontStyle.Bold = true;
            normalHeaderStyle.FontStyle.FontName = "Calibri";
            normalHeaderStyle.FontStyle.Size = 10.0;
            e.Styles.Add(normalHeaderStyle);

            StyleElement itemStyle = new StyleElement("itemNormalStyle");
            itemStyle.FontStyle.Color = System.Drawing.Color.Black;
            itemStyle.FontStyle.Bold = false;
            itemStyle.FontStyle.FontName = "Calibri";
            itemStyle.FontStyle.Size = 10.0;
            e.Styles.Add(itemStyle);

            StyleElement itemNutrientStyle = new StyleElement("itemNutrientStyle");
            itemNutrientStyle.FontStyle.Color = System.Drawing.Color.Black;
            itemNutrientStyle.NumberFormat.FormatType = NumberFormatType.Standard;
            itemNutrientStyle.FontStyle.Bold = false;
            itemNutrientStyle.FontStyle.FontName = "Calibri";
            itemNutrientStyle.FontStyle.Size = 10.0;
            e.Styles.Add(itemNutrientStyle);


            //Apply background colors
            foreach (StyleElement style in e.Styles)
            {
                if (style.Id == "perTotalHeaderStyle")
                {
                    style.InteriorStyle.Pattern = InteriorPatternType.Solid;
                    style.InteriorStyle.Color = System.Drawing.Color.Yellow;

                }

                if (style.Id == "per100HeaderStyle")
                {
                    style.InteriorStyle.Pattern = InteriorPatternType.Solid;
                    style.InteriorStyle.Color = System.Drawing.Color.YellowGreen;
                }

                if (style.Id == "normalHeaderStyle")
                {
                    style.InteriorStyle.Pattern = InteriorPatternType.Solid;
                    style.InteriorStyle.Color = System.Drawing.Color.Gray;
                }

                if (style.Id == "itemNormalStyle")
                {
                    style.InteriorStyle.Pattern = InteriorPatternType.None;
                }

                if (style.Id == "itemNutrientStyle")
                {
                    style.InteriorStyle.Pattern = InteriorPatternType.None;
                }
            }

        }

aspx

<telerik:RadAjaxPanel ID="RadAjaxPanel1" runat="server" LoadingPanelID="RadAjaxLoadingPanel1" ClientEvents-OnRequestStart="requestStart">
            <telerik:RadGrid ID="RadGrid1"
                Width="97%"
                AllowSorting="False"
                AllowPaging="True"
                PageSize="30"
                AllowMultiRowSelection="True"
                AllowFilteringByColumn="False"
                runat="server"
                Gridlines="Horizontal"
                AutoGenerateColumns="false"
                DataSourceID="SqlDataSource1"
                Skin="Vista"
                >
                <MasterTableView DataKeyNames="Number,Name"
                    CommandItemDisplay="Top"
                    AllowFilteringByColumn="false" >
                    <Columns>
                        <telerik:GridBoundColumn UniqueName="Number"
                            SortExpression="Number" HeaderText="Number" DataField="Number">
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn UniqueName="Name"
                            SortExpression="Name" HeaderText="Name" DataField="Name">
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn FooterText="Weight in Grams footer" UniqueName="Weight in Grams"
                            SortExpression="Weight in Grams" HeaderText="Weight in Grams" DataField="Weight in Grams">
                        </telerik:GridBoundColumn>

                        <%--Per Serving--%>
                        <telerik:GridBoundColumn UniqueName="Energie"
                            SortExpression="Energie" HeaderText="Energie" DataField="Energie" DataFormatString="{0:N2}">
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn UniqueName="Eiweiss"
                            SortExpression="Eiweiss" HeaderText="Eiweiss" DataField="Eiweiss" DataFormatString="{0:N2}">
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn UniqueName="Fett"
                            SortExpression="Fett" HeaderText="Fett" DataField="Fett" DataFormatString="{0:N2}">
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn UniqueName="KH"
                            SortExpression="KH" HeaderText="KH" DataField="KH" DataFormatString="{0:N2}">
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn UniqueName="Nahrungsfasern, total"
                            SortExpression="Nahrungsfasern, total" HeaderText="Nahrungsfasern, total" DataField="Nahrungsfasern, total" DataFormatString="{0:N2}">
                        </telerik:GridBoundColumn>

                        <%--Per 100--%>
                        <telerik:GridBoundColumn UniqueName="Energie1"
                            SortExpression="Energie1" HeaderText="Energie" DataField="Energie1" DataFormatString="{0:N2}">
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn UniqueName="Eiweiss1"
                            SortExpression="Eiweiss1" HeaderText="Eiweiss" DataField="Eiweiss1" DataFormatString="{0:N2}">
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn UniqueName="Fett1"
                            SortExpression="Fet1" HeaderText="Fett" DataField="Fett1" DataFormatString="{0:N2}">
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn UniqueName="KH1"
                            SortExpression="KH1" HeaderText="KH" DataField="KH1" DataFormatString="{0:N2}">
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn UniqueName="Nahrungsfasern, total1"
                            SortExpression="Nahrungsfasern, total1" HeaderText="Nahrungsfasern, total" DataField="Nahrungsfasern, total1" DataFormatString="{0:N2}">
                        </telerik:GridBoundColumn>

                        <telerik:GridBoundColumn UniqueName="Energie CAL."
                            SortExpression="Energie CAL." HeaderText="Energie CAL." DataField="Energie CAL." DataFormatString="{0:N2}">
                        </telerik:GridBoundColumn>
                    </Columns>
                    <CommandItemSettings ShowExportToExcelButton="true"
                        ShowAddNewRecordButton="false" />
                </MasterTableView>
                <ExportSettings
                    Excel-Format="ExcelML"
                    HideStructureColumns="true"
                    ExportOnlyData="true"
                    IgnorePaging="true"
                    OpenInNewWindow="true">
                </ExportSettings>
                <PagerStyle Mode="NextPrevAndNumeric" Position="TopAndBottom" />
            </telerik:RadGrid>
        </telerik:RadAjaxPanel>

[Marvin]

Bump...

[Princy]

Hello Marvin,

Please take a look at the following forum which discusses how to show custom row on top of ExcelSheet.
Export To pdf and Excel

And in order to hide the dropdown button in exported file, try the following code snippet.

C#:

protected void RadGrid1_ExcelMLExportRowCreated(object sender, Telerik.Web.UI.GridExcelBuilder.GridExportExcelMLRowCreatedArgs e)
   {
       if (e.RowType == Telerik.Web.UI.GridExcelBuilder.GridExportExcelMLRowType.HeaderRow)
       {
          e.Worksheet.AutoFilter.Range = "";
        }
   }

Thanks,
Princy.

[Marvin]

Thanks, it worked for us. Problems are solved now.

[Abhinav]

For the excel layout, how i can set margin left,right,top and bottom while exporting the excel from the telerik grid.

Also, please suggest how i can set the column width for the inner grids.

[Kostadin]

Hello Abhinav,

I am afraid that setting a margin to the Excel document is not supported. Regards your second approach which export format you are using? Also are you exporting a hierarchical grid and you need to set a width of the detail table columns? Generally you can try setting a width by using the approach from Resizing/Aligning Columns section of the following help article.

Regards,
Kostadin
Telerik