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

How to remove empty row in export excel file

6 Answers 368 Views

Grid

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

6 Answers, 1 is accepted

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">

<html xmlns="http://www.w3.org/1999/xhtml">

<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();
            }
        }
    }
}

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.