GridView 导出成Excel 死活找不到问题所在

[simhare]

导出的excel在Header下面无缘无故多出来一行
关键方法
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
e.Row.Cells[0].Attributes.Add("style", "vnd.ms-excel.numberformat:@;");
}
switch (e.Row.RowType)
{
case DataControlRowType.Header:
//第一行的表头
TableCellCollection tcHeader = e.Row.Cells;//retrun TablecellCollection get a collection of TableCell object that represents the cells of a row (in a Table Control)
tcHeader.Clear();
tcHeader.Add(new TableHeaderCell());
tcHeader[0].Attributes.Add("colspan", "4"); //跨Column
tcHeader[0].Text = "CheckList for AMS Information -LCL";

tcHeader.Add(new TableHeaderCell());
tcHeader[1].Attributes.Add("colspan", "1");
tcHeader[1].Text = "Date:" + DateTime.Now.ToString("MMM.dd", new System.Globalization.CultureInfo("en-GB")) + "</th></tr><tr>";// + "</th></tr><tr>";加？
break;
case DataControlRowType.DataRow:

TableCell tc = new TableCell();


//tc.Attributes.Add("colspan", "2");
tc.Attributes.Add("colspan", "3");//
//tc.AssociatedHeaderCellID = ;
tc.Text = "Vessel/Voyage";


e.Row.Attributes.Add("onmouseover", "this.style.backgroundColor='#FFC080'");

e.Row.HorizontalAlign =HorizontalAlign.Center;

e.Row.Attributes.Add("onmouseout", "this.style.backgroundColor='#ffffff'");


break;

}//Switch End()
}//GridView1_RowDataBound End()

[simhare]

createDataSource() 做数据源
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
GridView1.BorderWidth = Unit.Pixel(2);
GridView1.BorderColor = System.Drawing.Color.DarkOrange;
GridView1.DataSource = CreateDataSource();
GridView1.DataBind();

}
}

[simhare]

CreateDataSource：
public ICollection CreateDataSource()
{
string strVesselVoyage = string.Empty;
string strCarrier = string.Empty;
string strHBL = string.Empty;
string strClosingDate = string.Empty;
string strOnboardDate = string.Empty;
string strShipper = string.Empty;
string strConsignee = string.Empty;
string strDescription = string.Empty;
string strBookingNO = string.Empty;
string strSO = string.Empty;

string strConn = ConfigurationManager.ConnectionStrings["dsn"].ConnectionString;
SqlConnection conn = new SqlConnection(strConn);
string strSql = @"SELECT dbo.Hou.Vessel_Voyage, dbo.CompanyDetails.Company_Name AS Carrier,
dbo.Hou.Closing_Date, dbo.Hou.Loading_ATD
FROM dbo.Hou LEFT OUTER JOIN
dbo.CompanyDetails ON dbo.Hou.Carrier = dbo.CompanyDetails.Account_ID
WHERE dbo.Hou.Hou_id =" + Request.QueryString["hou_id"];
SqlCommand comm = new SqlCommand(strSql, conn);
conn.Open();
SqlDataReader sdr = comm.ExecuteReader();
while (sdr.Read())
{
strVesselVoyage = sdr["Vessel_Voyage"].ToString();
strCarrier = sdr["Carrier"].ToString();
strClosingDate = sdr["Closing_Date"].ToString();
strOnboardDate = sdr["loading_ATD"].ToString();

}
sdr.Close();
conn.Close();
DataTable dt = new DataTable();
//没有column 无法定位dr
DataColumn dc;
DataRow dr;
dc = new DataColumn("VesselVoyage", typeof(String));
dt.Columns.Add(dc);
dc = new DataColumn("strVesselVoyage", typeof(String));
dt.Columns.Add(dc);
dc = new DataColumn("Carrier", typeof(String));
dt.Columns.Add(dc);
dc = new DataColumn("strCarrier", typeof(String));
dt.Columns.Add(dc);

dr = dt.NewRow();
dr["VesselVoyage"] = "Vessel/Voyage:";
dr["strVesselVoyage"] = strVesselVoyage;
dr["Carrier"] = "Carrier:";
dr["strCarrier"] = strCarrier;
dt.Rows.Add(dr);
/*-------------------------------------------------------second line---------------*/

dc = new DataColumn("ClosingDate", typeof(String));
dt.Columns.Add(dc);
dc = new DataColumn("strClosingDate", typeof(String));
dt.Columns.Add(dc);
dc = new DataColumn("onBoardDate", typeof(String));
dt.Columns.Add(dc);
dc = new DataColumn("stronBoardDate", typeof(String));
dt.Columns.Add(dc);

dr = dt.NewRow();
dr[0] = "Closing Date:";
dr[1] = strClosingDate;
dr[2] = "On Board Date:";
dr[3] = strOnboardDate;
dt.Rows.Add(dr);

/*-----------------------------------------------------third line-----------------*/

dc = new DataColumn("S/O", typeof(String));
dt.Columns.Add(dc);
dc = new DataColumn("HB/LNO", typeof(String));
dt.Columns.Add(dc);
dc = new DataColumn("Shipper", typeof(String));
dt.Columns.Add(dc);
dc = new DataColumn("Consignee", typeof(String));
dt.Columns.Add(dc);
dc = new DataColumn("Description", typeof(String));
dt.Columns.Add(dc);

dr = dt.NewRow();
dr[0] = "S/O#.";
dr[1] = "HB/L NO.";
dr[2] = "SHIPPER";
dr[3] = "CONSIGNEE";
dr[4] = "DESCRIPTION";
dt.Rows.Add(dr);
/*-----------------------------------------------------记录列表------------*/
//再次建立连接，读取记录条数

strSql = @"SELECT dbo.Hou.Booking_NO, dbo.Hou.House_B_L_NO,
tblShipper.Company_Name AS Shipper, tblConsignee.Company_Name AS Consignee,
dbo.Description_of_Package.Description_of_Package
FROM dbo.Hou LEFT OUTER JOIN
dbo.Description_of_Package ON
dbo.Hou.Description_Drop = dbo.Description_of_Package.Description_of_Package_id LEFT
OUTER JOIN
dbo.CompanyDetails tblConsignee ON
dbo.Hou.Consignee = tblConsignee.Account_ID LEFT OUTER JOIN
dbo.CompanyDetails tblShipper ON dbo.Hou.Shipper = tblShipper.Account_ID
WHERE dbo.Hou.Hou_id =" + Request.QueryString["hou_id"];

conn = new SqlConnection(strConn);
conn.Open();
comm = new SqlCommand(strSql, conn);
sdr = comm.ExecuteReader();

dt.Columns.Add(new DataColumn("BookingNO", typeof(string)));
dt.Columns.Add(new DataColumn("HBLNO", typeof(string)));
dt.Columns.Add(new DataColumn("ShipperList", typeof(string)));
dt.Columns.Add(new DataColumn("ConsigneeList", typeof(string)));
dt.Columns.Add(new DataColumn("DescriptionList", typeof(string)));

while (sdr.Read())
{
DataRow drRecord;
drRecord = dt.NewRow();

strSO = sdr["booking_NO"].ToString();
strHBL = sdr["House_B_L_NO"].ToString();
strShipper = sdr["Shipper"].ToString();
strConsignee = sdr["Consignee"].ToString();
strDescription = sdr["Description_of_Package"].ToString();

drRecord[0] = strSO;
drRecord[1] = strHBL;
drRecord[2] = strShipper;
drRecord[3] = strConsignee;
drRecord[4] = strDescription;

dt.Rows.Add(drRecord);

}
sdr.Close();
conn.Close();
DataView dv = new DataView(dt);
return dv;

}

[孟子E章]

多出的一行有数据吗？
是不是GridView1设置了SHowFooter=true了？

[simhare]

“导出到Excel”按钮的事件：
protected void Button1_Click(object sender, EventArgs e)
{
GridView1.AllowPaging = false;
GridView1.AllowSorting = false;

Response.Clear();
Response.Buffer = true;
Response.Charset = "GB2312";
Response.AppendHeader("Content-Disposition", "attachment;filename=FileName.xls");

Response.ContentEncoding = System.Text.Encoding.UTF7;//return encoding get or set the http charater set of the output stream
Response.ContentType = "application/ms-excel";//get or set the http MIME type of the output stream

StringWriter oStringWriter = new StringWriter();
HtmlTextWriter oHtmlWriter = new HtmlTextWriter(oStringWriter);

this.GridView1.RenderControl(oHtmlWriter);
Response.Output.Write(oStringWriter.ToString());

Response.Flush();
Response.End();

}

[simhare]

没有数据是空的一行

[simhare]

没有设置ShowFooter=true