protected void GridToExcel_Click(object sender, ImageClickEventArgs e)
{
string strHTML = GetString1(table);//得到字符串
string FileName = "个人得分状况" + DateTime.Now.ToString();
CreateExcel(FileName, strHTML.ToString(), Response);
}
//拼HTML串
private string GetString1(DataTable dt)
{
DateTime fromDate = PickerFrom.SelectedDate;
DateTime toDate = PickerTo.SelectedDate;
StringBuilder str = new StringBuilder();
str.Append("<center><table style=\"width: 100%;\"><tr><td colspan=\"" + 8 +
"\" style=\"text-align: center; font-size: 16pt; height:80px;\"><b>个人得分状况</b></td></tr>");
str.Append("<td colspan=\"" + 8 + "\" style=\"text-align: right; font-size: 11pt;\">日期:" + fromDate.ToString("yyyy年MM月dd日")
+ "-" + toDate.ToString("yyyy年MM月dd日") + "</td></tr></table>");
str.Append("<table border=\"1\" cellpadding=\"0\" cellspacing=\"0\" style=\"text-align: center; border-color: Black;width: 100%;\">");
str.Append("<tr><td style=\"font-size: 9pt; font-weight: bold; text-align: center;\">项目部</td>");
str.Append("<td style=\"font-size: 9pt; font-weight: bold; text-align: center;\">班组</td>");
str.Append("<td style=\"font-size: 9pt; font-weight: bold; text-align: center;\">姓名</td>");
str.Append("<td style=\"font-size: 9pt; font-weight: bold; text-align: center;\">日期</td>");
str.Append("<td style=\"font-size: 9pt; font-weight: bold; text-align: center;\">路段</td>");
str.Append("<td style=\"font-size: 9pt; font-weight: bold; text-align: center;\">工作项目</td>");
str.Append("<td style=\"font-size: 9pt; font-weight: bold; text-align: center;\">得分</td>");
str.Append("<td style=\"font-size:9pt; font-weight: bold; text-align: center;\">金额</td></tr>");
foreach (DataRow row in dt.Rows)
{
str.Append("<tr><td style=\"font-size: 9pt;text-align: center;\">" + row["FDepartmentName"] + "</td>");
str.Append("<td style=\"font-size: 9pt;text-align: center;\">" + row["DepartmentName"] + "</td>");
str.Append("<td style=\"font-size: 9pt; text-align: center;\">" + row["WorkerName"] + "</td>");
str.Append("<td style=\"font-size: 9pt;text-align: center;\">" + row["WorkDate"] + "</td>");
str.Append("<td style=\"font-size: 9pt; text-align: center;\">" + row["Location"] + "</td>");
str.Append("<td style=\"font-size: 9pt;text-align: center;\">" + row["ConservationName"] + "</td>");
str.Append("<td style=\"font-size: 9pt; text-align: center;\">" + row["Score"] + "</td>");
str.Append("<td style=\"font-size: 9pt; text-align: center;\">" + row["SMoney"] + "</td></tr>");
}
str.Append("</table></center>");
return str.ToString();
}
//导出方法
public static void CreateExcel(string FileName, string html, HttpResponse Response)
{
StringBuilder sbPrint = new StringBuilder();
sbPrint.Append(html);
if (sbPrint != null)
{
Response.Clear();
Response.Buffer = true;
Response.Charset = "GB2312";
Response.AppendHeader("Content-Disposition", "attachment;filename=" +
HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8)
+ ".xls");//HttpUtility.UrlEncode(FileName, System.Text.Encoding.GetEncoding("GB2312")) + ".xls");
// 如果设置为 GetEncoding("GB2312");导出的文件将会出现乱码!!!
Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
Label lbExport = new Label();
lbExport.Text = sbPrint.ToString();
lbExport.RenderControl(oHtmlTextWriter);
Response.Output.Write(oStringWriter.ToString());
Response.Flush();
Response.End();
}
}
============================================================================
导出方式2:
protected void GridToExcel_Click(object sender, ImageClickEventArgs e)
{
DataTable endTable = new DataTable();
endTable.Columns.Add("项目部", typeof(System.String));
endTable.Columns.Add("班组", typeof(System.String));
endTable.Columns.Add("姓名", typeof(System.String));
endTable.Columns.Add("日期", typeof(System.DateTime));
endTable.Columns.Add("路段", typeof(System.String));
endTable.Columns.Add("工作项目", typeof(System.String));
endTable.Columns.Add("得分", typeof(System.String));
endTable.Columns.Add("金额", typeof(System.String));
foreach (DataRow row in table.Rows)
{
DataRow endrow = endTable.NewRow();
endrow["项目部"] = row["FDepartmentName"].ToString();
endrow["班组"] = row["DepartmentName"];
endrow["姓名"] = row["WorkerName"].ToString();
endrow["日期"] = row["WorkDate"].ToString();
endrow["路段"] = row["Location"].ToString();
endrow["工作项目"] = row["ConservationName"].ToString();
endrow["得分"] = row["Score"].ToString();
endrow["金额"] = row["SMoney"].ToString();
endTable.Rows.Add(endrow);
}
Export(this.Page, endTable);
}
//导出方法
public static void Export(System.Web.UI.Page page, System.Data.DataTable tab)
{
string FileName = DateTime.Now.ToString("yyyyMMdd-hhmmss") + ".xls";
System.Web.HttpResponse httpResponse = page.Response;
System.Web.UI.WebControls.DataGrid dataGrid = new System.Web.UI.WebControls.DataGrid();
dataGrid.DataSource = tab.DefaultView;
dataGrid.AllowPaging = false;
dataGrid.HeaderStyle.BackColor = System.Drawing.Color.White;
dataGrid.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
dataGrid.HeaderStyle.Font.Bold = true;
dataGrid.DataBind();
httpResponse.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8)); //filename="*.xls";
httpResponse.ContentEncoding = System.Text.Encoding.UTF8;//.GetEncoding("GB2312");
httpResponse.ContentType = "application/ms-excel";
System.IO.StringWriter tw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
dataGrid.RenderControl(hw);
httpResponse.Write(tw.ToString());
httpResponse.End();
}