How to remove empty row in export excel file

7 posts, 0 answers
  1. york
    york avatar
    160 posts
    Member since:
    Jul 2012

    Posted 02 Jul 2011 Link to this post

    Hi,
    I want to remove the empty rows in exporting radgrid to excel file. See attached file. How to do it? Thanks.
  2. komathi priya
    komathi priya avatar
    30 posts
    Member since:
    Apr 2010

    Posted 02 Jul 2011 Link to this post

    hi
    can you give the coding you used, to debug.
  3. york
    york avatar
    160 posts
    Member since:
    Jul 2012

    Posted 04 Jul 2011 Link to this post

    Hi priya,
    Here is the code. Thanks. Also if you can also remove the white space in each cell following strings, it would be great.

    1.Order1.aspx

    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Order1.aspx.cs" Inherits="InnovativeWeb.Backend.Order1" %>
     
    <%@ Register Assembly="Telerik.Web.UI, Version=2010.1.323.35, Culture=neutral, PublicKeyToken=121fae78165ba3d4"
        Namespace="Telerik.Web.UI" TagPrefix="telerik" %>
     
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
     
    <head runat="server">
        <title></title>
        <link href="~/css/Site.css" rel="stylesheet" type="text/css" />
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
        <telerik:RadScriptManager ID="RadScriptManager1" runat="server"></telerik:RadScriptManager>
        <table style="width: 100%; z-index:0">       
            <tr>
                <td >
                    <div id="quick_search">  
                        <div class="report_header">
                            Quick Search
                        </div>
                        <telerik:RadComboBox ID="RadComboBox1" runat="server"
                            style="position:absolute; top:45px; left:20px; ">
                            <Items>
                                <telerik:RadComboBoxItem Text="Today" Value="Today" />
                                <telerik:RadComboBoxItem Text="Yesterday" Value="Yesterday" />
                                <telerik:RadComboBoxItem Text="Last 7 Days" Value="Last 7 Days" />
                                <telerik:RadComboBoxItem Text="This Month" Value="This Month" />
                                <telerik:RadComboBoxItem Selected="true" Text="This Year" Value="This Year" />
                                <telerik:RadComboBoxItem Text="All Time" Value="All Time" />
                            </Items>
                        </telerik:RadComboBox>
                        <asp:ImageButton ID="ImageButton1" runat="server"
                            style="position:absolute; top:45px; left:300px"
                            ImageUrl="~/images/ADMIN_Find_Bttn1.gif" onclick="ImageButton1_Click"/>
     
                        <p style="position:absolute; top:70px; left:20px; color:Black; font-weight:normal">Date Range:</p>
                        <telerik:RadDatePicker ID="RadDatePicker1" runat="server"
                            style="position:absolute; top:110px; left:20px" Height="15px" Width="120px">
                            <Calendar UseRowHeadersAsSelectors="False" UseColumnHeadersAsSelectors="False" ViewSelectorText="x"></Calendar>
     
                            <DateInput DisplayDateFormat="MM/dd/yyyy" DateFormat="MM/dd/yyyy" Height="10px"></DateInput>
     
                            <DatePopupButton ImageUrl="" HoverImageUrl=""></DatePopupButton>
                        </telerik:RadDatePicker>
                        <telerik:RadDatePicker ID="RadDatePicker2" runat="server"
                            style="position:absolute; top:110px; left:160px" Height="15px"
                            Width="120px">
                            <Calendar UseRowHeadersAsSelectors="False" UseColumnHeadersAsSelectors="False" ViewSelectorText="x"></Calendar>
     
                            <DateInput DisplayDateFormat="MM/dd/yyyy" DateFormat="MM/dd/yyyy" Height="10px"></DateInput>
     
                            <DatePopupButton ImageUrl="" HoverImageUrl=""></DatePopupButton>
                        </telerik:RadDatePicker>
     
                        <asp:ImageButton ID="ImageButton2" runat="server"
                            style="position:absolute; top:110px; left:300px"
                            ImageUrl="~/images/ADMIN_Find_Bttn1.gif" onclick="ImageButton2_Click"/>
                    </div>                          
                </td>
                <td style="width:10px"></td>
                <td>
                    <div id="quick_stats">  
                        <div class="report_header">
                            Quick Stats
                        </div>
                         
                        <asp:Label ID="Label1" runat="server" Text="" style="position:absolute; top:50px; left:30px" ></asp:Label>
                    </div>
                </td>
                <td style="width:10px"></td>
                <td>               
                    <div id="quick_export">  
                        <div class="report_header">
                            Quick Export
     
                            <asp:CheckBox ID="CheckBox1" Text="" runat="server" style="position:absolute; top:50px; left:15px; width:15px; height:15px" />
                            <p style="position:absolute; top:35px; left:40px; color:Black; font-weight:normal">Ignore Paging (Export all pages)</p>
                            <asp:CheckBox ID="CheckBox2" Text="" runat="server" style="position:absolute; top:80px; left:15px; width:15px; height:15px" />
                            <p style="position:absolute; top:65px; left:40px; color:Black; font-weight:normal">Open exported data in new browser</p>
     
                        </div>
                          
                        <asp:ImageButton ID="ImageButton3" runat="server"
                            style="position:absolute; top:120px; left:45px"
                            ImageUrl="~/images/ADMIN_ExportAllOrders_Bttn1.gif"
                            onclick="ImageButton3_Click"/>
                    </div>
                </td>
            </tr>
            </table>
     
            <div id="radgrid">
                <p style="padding-top:10px; margin-left:10px">Orders</p>
                <asp:Label ID="Label2" runat="server" Text="" style="margin-top:0px; margin-left:10px; margin-bottom:10px"></asp:Label>
     
                <telerik:RadGrid ID="RadGrid1" runat="server" style="margin-left:5px; margin-top:10px"
                    AllowPaging="True" AllowFilteringByColumn="True"  AllowSorting="True" EnableLinqExpressions="false"
                    OnItemDataBound="RadGrid1_ItemDataBound" OnExcelExportCellFormatting="RadGrid1_ExportCellFormatting"              
                    OnGridExporting="RadGrid1_GridExporting" DataSourceID="SqlDataSource1" GridLines="None">
     
                    <MasterTableView DataSourceID="SqlDataSource1" AutoGenerateColumns="False"
                         PagerStyle-Position="TopAndBottom"  
                         PagerStyle-AlwaysVisible="true" PagerStyle-Mode="NextPrevNumericAndAdvanced" PageSize="10">
                    <RowIndicatorColumn>
                    <HeaderStyle Width="20px"></HeaderStyle>
                    </RowIndicatorColumn>
                         
                    <ExpandCollapseColumn>
                    <HeaderStyle Width="20px"></HeaderStyle>
                    </ExpandCollapseColumn>
                        <Columns>
                            <telerik:GridBoundColumn DataField="OrderDate" DataType="System.DateTime"
                                HeaderText="Order Date" SortExpression="OrderDate"
                                CurrentFilterFunction="Contains" AutoPostBackOnFilter="true" UniqueName="OrderDate">
                            </telerik:GridBoundColumn>
                            <telerik:GridBoundColumn DataField="FirstName" HeaderText="First Name"
                                SortExpression="FirstName" CurrentFilterFunction="Contains" AutoPostBackOnFilter="true" UniqueName="FirstName">
                            </telerik:GridBoundColumn>
                            <telerik:GridBoundColumn DataField="Email" HeaderText="Email"
                                SortExpression="Email" CurrentFilterFunction="Contains" AutoPostBackOnFilter="true" UniqueName="Email">
                            </telerik:GridBoundColumn>
                            <telerik:GridBoundColumn DataField="GreetingCode" HeaderText="Greeting Code"
                                SortExpression="GreetingCode" CurrentFilterFunction="Contains" AutoPostBackOnFilter="true" UniqueName="GreetingCode">
                            </telerik:GridBoundColumn>
                            <telerik:GridBoundColumn DataField="ChildFirstName" HeaderText="Child's FirstName"
                                SortExpression="ChildFirstName" CurrentFilterFunction="Contains" AutoPostBackOnFilter="true" UniqueName="ChildFirstName">
                            </telerik:GridBoundColumn>
                            <telerik:GridBoundColumn DataField="ChildLastName" HeaderText="Child's LastName"
                                SortExpression="ChildLastName" CurrentFilterFunction="Contains" AutoPostBackOnFilter="true" UniqueName="ChildLastName">
                            </telerik:GridBoundColumn>
                            <telerik:GridBoundColumn DataField="ChildGender" HeaderText="Child's Gender"
                                SortExpression="ChildGender" CurrentFilterFunction="Contains" AutoPostBackOnFilter="true" UniqueName="ChildGender">
                            </telerik:GridBoundColumn>
                        </Columns>
                    </MasterTableView>
                </telerik:RadGrid>
     
                <div id="grid_bottom"></div>
            </div>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server"
                ConnectionString="Data Source=mssql150.uploadmysite.com;Initial Catalog=freepersonalized;Persist Security Info=True;User ID=iusr_freepers;Password=v$4EFB%ti#" providerName="System.Data.SqlClient"
                SelectCommand="SELECT [OrderDate], [FirstName], [Email], [GreetingCode], [ChildFirstName], [ChildLastName], [ChildGender] FROM [Orders] ORDER BY [OrderDate] DESC">
            </asp:SqlDataSource>   
        </div>
        </form>
    </body>
    </html>

    2.Order1.aspx.cs
     
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using Telerik.Web.UI;
     
    namespace InnovativeWeb.Backend
    {
        public partial class Order1 : System.Web.UI.Page
        {
            bool numDisplayed = false;
            DateTime minValue = DateTime.MaxValue;
            DateTime maxValue = DateTime.MinValue;
     
            protected void Page_Load(object sender, EventArgs e)
            {
                Label2.Text = "<i>Results:  </i>" + new DateTime(DateTime.Now.Year, 1, 1) + "<i>  through  </i>" + DateTime.Today.AddDays(1).AddSeconds(-1).ToString();
            }
     
            protected void RadGrid1_ItemDataBound(object sender, GridItemEventArgs e)
            {
                if (e.Item is GridPagerItem)
                {
                    if (numDisplayed == false)
                    {
                        Label1.Text = "Total Orders: " + (e.Item as GridPagerItem).Paging.DataSourceCount.ToString();
                        Label1.ForeColor = System.Drawing.Color.Black;
                        numDisplayed = true;
                    }
                }
     
                DateTime dateValue;
                if (e.Item is GridDataItem)
                {
                    dateValue = (DateTime)DataBinder.Eval(e.Item.DataItem, "OrderDate");
     
                    if (DateTime.Compare(minValue, dateValue) > 0)
                    {
                        minValue = dateValue;
                        if (DateTime.Compare(maxValue, dateValue) < 0)
                        {
                            maxValue = dateValue;
                        }
                    }
                    else
                    {
                        if (DateTime.Compare(maxValue, dateValue) < 0)
                        {
                            maxValue = dateValue;
                        }
                    }
                    //Label2.Text = "<i>Results:  </i>" + minValue.ToString() + "<i>  through  </i>" + maxValue.ToString();
                    ViewState["MaxDateTime"] = maxValue;
                    ViewState["MinDateTime"] = minValue;
                }
            }
     
            protected void RadGrid1_GridExporting(object sender, GridExportingArgs e)
            {
                if (e.ExportType == ExportType.Excel)
                {
                    string css = "<style> body { border:solid 0.1pt #dddddd; }</style>";
                    e.ExportOutput = e.ExportOutput.Replace("</head>", css + "</head>");
                }
            }
     
            protected void RadGrid1_ExportCellFormatting(object sender, ExcelExportCellFormattingEventArgs e)
            {
                GridDataItem item = e.Cell.Parent as GridDataItem;
                if (e.FormattedColumn.UniqueName == "OrderDate")
                {
                    e.Cell.Style["text-align"] = "left";
                    //e.Cell.Style["border"] = "thin solid black";
                }
                if (e.FormattedColumn.UniqueName == "FirstName")
                {
                    e.Cell.Style["text-align"] = "left";
                    //e.Cell.Style["border"] = "thin solid black";
                }
                if (e.FormattedColumn.UniqueName == "Email")
                {
                    e.Cell.Style["text-align"] = "left";
                    //e.Cell.Style["border"] = "thin solid black";
                }
                if (e.FormattedColumn.UniqueName == "GreetingCode")
                {
                    e.Cell.Style["text-align"] = "left";
                    //e.Cell.Style["border"] = "thin solid black";
                }
                if (e.FormattedColumn.UniqueName == "ChildFirstName")
                {
                    e.Cell.Style["text-align"] = "left";
                    //e.Cell.Style["border"] = "thin solid black";
                }
                if (e.FormattedColumn.UniqueName == "ChildLastName")
                {
                    e.Cell.Style["text-align"] = "left";
                    //e.Cell.Style["border"] = "thin solid black";
                }
                if (e.FormattedColumn.UniqueName == "ChildGender")
                {
                    e.Cell.Style["text-align"] = "left";
                    //e.Cell.Style["border"] = "thin solid black";
                }
     
                GridHeaderItem HeaderItem = (GridHeaderItem)RadGrid1.MasterTableView.GetItems(GridItemType.Header)[0];
                foreach (TableCell cell in HeaderItem.Cells)
                {
                    cell.Style["text-align"] = "left";
                    cell.Style["border"] = "thin solid black";
                    cell.Style["background-color"] = "#cccccc";
                    cell.Style["font-weight"] = "normal";
                }
            }
     
            protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
            {
                switch (RadComboBox1.SelectedValue)
                {
                    case "Today":
                        SetFilter("OrderDate >= '" + DateTime.Today.ToString() + "'");
                        Label2.Text = "<i>Results:  </i>" + DateTime.Today.ToString() + "<i>  through  </i>" + DateTime.Today.AddDays(1).AddSeconds(-1).ToString();
                        break;
                    case "Yesterday":
                        SetFilter("(OrderDate <= '" + DateTime.Today.AddSeconds(-1).ToString() + "') AND (OrderDate >= '" + DateTime.Today.AddDays(-1).ToString() + "')");
                        Label2.Text = "<i>Results:  </i>" + DateTime.Today.AddDays(-1).ToString() + "<i>  through  </i>" + DateTime.Today.AddSeconds(-1).ToString();
                        break;
                    case "Last 7 Days":
                        SetFilter("OrderDate >= '" + DateTime.Today.AddDays(-7).ToString() + "'");
                        Label2.Text = "<i>Results:  </i>" + DateTime.Today.AddDays(-7).ToString() + "<i>  through  </i>" + DateTime.Today.AddDays(1).AddSeconds(-1).ToString();
                        break;
                    case "This Month":
                        SetFilter("OrderDate >= '" + new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1) + "'");
                        Label2.Text = "<i>Results:  </i>" + new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1) + "<i>  through  </i>" + DateTime.Today.AddDays(1).AddSeconds(-1).ToString();
                        break;
                    case "This Year":
                        SetFilter("OrderDate >= '" + new DateTime(DateTime.Now.Year, 1, 1) + "'");
                        Label2.Text = "<i>Results:  </i>" + new DateTime(DateTime.Now.Year, 1, 1) + "<i>  through  </i>" + DateTime.Today.AddDays(1).AddSeconds(-1).ToString();
                        break;
                    case "All Time":
                        SetFilter("");
                        Label2.Text = "<i>Results:  </i>" + "anytime" + "<i>  through  </i>" + DateTime.Today.AddDays(1).AddSeconds(-1).ToString();
                        break;
                    default:
                        SetFilter("");
                        break;
                }
            }
     
            protected void SetFilter(string filterString)
            {
                RadGrid1.MasterTableView.FilterExpression = filterString;
                RadGrid1.Rebind();
            }
     
            protected void ImageButton2_Click(object sender, ImageClickEventArgs e)
            {
                DateTime t1;
                DateTime t2;
     
                if (RadDatePicker1.SelectedDate == null)
                {
                    t1 = DateTime.Now;
                }
                else
                {
                    t1 = (DateTime)RadDatePicker1.SelectedDate;
                }
     
                if (RadDatePicker2.SelectedDate == null)
                {
                    t2 = DateTime.Now;
                }
                else
                {
                    t2 = ((DateTime)RadDatePicker2.SelectedDate).AddDays(1).AddSeconds(-1);
                }
     
                if (DateTime.Compare(t1, t2) > 0)
                {
                    t2 = t1;
                }
     
                SetFilter("(OrderDate >= '" + t1.ToString() + "') AND (OrderDate <= '" + t2.ToString() + "')");
                Label2.Text = "<i>Results:  </i>" + t1.ToString() + "<i>  through  </i>" + t2.ToString();
            }
     
            protected void ImageButton3_Click(object sender, ImageClickEventArgs e)
            {
                ConfigureExport();
                RadGrid1.MasterTableView.ExportToExcel();
            }
     
            public void ConfigureExport()
            {
                RadGrid1.ExportSettings.IgnorePaging = CheckBox1.Checked;
                RadGrid1.ExportSettings.OpenInNewWindow = CheckBox2.Checked;
                RadGrid1.ExportSettings.ExportOnlyData = true;
     
                minValue = (DateTime)ViewState["MinDateTime"];
                maxValue = (DateTime)ViewState["MaxDateTime"];
     
                if (RadGrid1.ExportSettings.IgnorePaging == true)
                {
                    RadGrid1.ExportSettings.FileName = "FPG_Orders_All_Pages";
                }
                else
                {
                    RadGrid1.ExportSettings.FileName = "FPG_Orders_" + minValue.Month.ToString() + "_" + minValue.Day.ToString() + "_" + minValue.Year.ToString() + "_" +
                                                        "thru_" + maxValue.Month.ToString() + "_" + maxValue.Day.ToString() + "_" + maxValue.Year.ToString();
                }
            }
        }
    }
  4. Princy
    Princy avatar
    17421 posts
    Member since:
    Mar 2007

    Posted 04 Jul 2011 Link to this post

    Hello York,


    I also faced the same issue when I tried to export the Radgrid with footer and autogenerated button columns. This issue arises when exported excel file contains any AutoGeneratedEditColumn/DeleteColumn and when "ShowFooter" property is set to true. Try the following code snippet to hide these before exporting to excel.

    C#:
    protected void button1_Click(object sender, EventArgs e)
      {
          RadGrid1.ExportSettings.ExportOnlyData = true;
          RadGrid1.MasterTableView.ShowFooter = false;
          RadGrid1.MasterTableView.GetColumn("AutoGeneratedEditColumn").Visible = false;
          RadGrid1.MasterTableView.ExportToExcel();
      }

    Thanks,
    Princy.
  5. york
    york avatar
    160 posts
    Member since:
    Jul 2012

    Posted 04 Jul 2011 Link to this post

    Hi Princy,

    I don't use AutoGeneratedEditColumn in the page. After crash, I comment out AutoGeneratedEditColumn with only ShowFooter=false. But it is not working. 

    York

  6. york
    york avatar
    160 posts
    Member since:
    Jul 2012

    Posted 04 Jul 2011 Link to this post

    Hi Princy,

    I find that if RadGrid1.MasterTableView.AllowFilteringByColumn = false, then there is empty row only when exporting with IgnorePaging is set to be false. If IgnorePaging is set to be true, then no empty line occurs. However if RadGrid1.MasterTableView.AllowFilteringByColumn = true, there is empty row anyway. So what it means is that the empty row is reserved for search string. After I enter search string, it is displayed in the empty row in exported excel file.  Hopefully this will help.

    Thanks,
    York
  7. Flemming
    Flemming avatar
    17 posts
    Member since:
    Aug 2015

    Posted 26 Feb 2019 in reply to Princy Link to this post

    RadGrid1.MasterTableView.ShowFooter = false;

     

    worked for me when I had empty row below the header.

    I still have filter on the webpage afterwards. Win win.

Back to Top