RadGrid Export to Excel Formatting

6 posts, 0 answers
  1. Marvin
    Marvin avatar
    5 posts
    Member since:
    Feb 2011

    Posted 16 Feb 2011 Link to this post

    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>

  2. Marvin
    Marvin avatar
    5 posts
    Member since:
    Feb 2011

    Posted 17 Feb 2011 Link to this post

    Bump...
  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. Princy
    Princy avatar
    17421 posts
    Member since:
    Mar 2007

    Posted 18 Feb 2011 Link to this post

    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.
  5. Marvin
    Marvin avatar
    5 posts
    Member since:
    Feb 2011

    Posted 21 Feb 2011 Link to this post

    Thanks, it worked for us. Problems are solved now.
  6. Abhinav
    Abhinav avatar
    5 posts
    Member since:
    Feb 2014

    Posted 05 Feb 2015 Link to this post

    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.
  7. Kostadin
    Admin
    Kostadin avatar
    1708 posts

    Posted 10 Feb 2015 Link to this post

    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
     

    Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

     
Back to Top
UI for ASP.NET Ajax is Ready for VS 2017