Telerik Forums / UI for ASP.NET AJAX
This is a migrated thread and some comments may be shown as answers.

How to remove empty row in export excel file

6 Answers 1125 Views
Grid
This is a migrated thread and some comments may be shown as answers.
york
Top achievements
Rank 1
york asked on 02 Jul 2011, 07:45 AM
Export-excel.jpg
Hi,
I want to remove the empty rows in exporting radgrid to excel file. See attached file. How to do it? Thanks.

6 Answers, 1 is accepted

Sort by
0
komathi priya
Top achievements
Rank 1
answered on 02 Jul 2011, 09:11 AM
hi
can you give the coding you used, to debug.
0
york
Top achievements
Rank 1
answered on 04 Jul 2011, 06:07 AM
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();
            }
        }
    }
}
0
Princy
Top achievements
Rank 2
answered on 04 Jul 2011, 06:54 AM
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.
0
york
Top achievements
Rank 1
answered on 04 Jul 2011, 07:44 AM
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

0
york
Top achievements
Rank 1
answered on 04 Jul 2011, 08:50 AM
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
0
Flemming
Top achievements
Rank 1
Veteran
answered on 26 Feb 2019, 07:05 PM

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.

Tags
Grid
Asked by
york
Top achievements
Rank 1
Answers by
komathi priya
Top achievements
Rank 1
york
Top achievements
Rank 1
Princy
Top achievements
Rank 2
Flemming
Top achievements
Rank 1
Veteran
Share this question
or