/// <summary> /// 导出功能 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btn_export_Click(object sender, EventArgs e) { try { string strSql = CreateStrWhere(); DataTable dst = ShDonBLL.GetListForEXport(strSql).Tables[0]; if (dst.Rows.Count<=0) { Jscript.Alert(this, "查询数据为空,没有数据导出,请重新选择条件!"); return; } DataTable ds = UpdateDataTable(dst); for (int i = 0; i < ds.Rows.Count; i++) { DataColumn dc = ds.Columns[1];//i就是第几列或者用列名也可以 dc.DataType = typeof(String); string eid = ds.Rows[i]["装置名称"].ToString(); string tyid = ds.Rows[i]["类别"].ToString(); if (!string.IsNullOrEmpty(eid)) { ds.Rows[i]["装置名称"] = GetEqui(eid); } if (!string.IsNullOrEmpty(tyid)) { ds.Rows[i]["类别"] = GetZG(tyid); } } //DataTableExcel(ds,"测试0",""); } catch (Exception ex) { throw ex; } } /// <summary> /// 修改数据表DataTable某一列的类型和记录值(正确步骤:1.克隆表结构,2.修改列类型,3.修改记录值,4.返回希望的结果) /// </summary> /// <param name="argDataTable">数据表DataTable</param> /// <returns>数据表DataTable</returns> private DataTable UpdateDataTable(DataTable argDataTable) { DataTable dtResult = new DataTable(); //克隆表结构 dtResult = argDataTable.Clone(); foreach (DataColumn col in dtResult.Columns) { if (col.ColumnName == "装置名称" || col.ColumnName == "类别") { //修改列类型 col.DataType = typeof(String); } } foreach (DataRow row in argDataTable.Rows) { DataRow rowNew = dtResult.NewRow(); rowNew["序号"] = row["序号"]; rowNew["装置名称"] = row["装置名称"]; rowNew["停工时间"] = row["停工时间"]; rowNew["开工时间"] = row["开工时间"]; rowNew["正常时间"] = row["正常时间"]; rowNew["停工原因"] = row["停工原因"]; rowNew["类别"] = row["类别"]; rowNew["停工时间小时"] = row["停工时间小时"]; rowNew["年度"] = row["年度"]; dtResult.Rows.Add(rowNew); } return dtResult; } #region DataTable导出到Excel /// <summary> /// DataTable导出到Excel /// </summary> /// <param name="pData">DataTable</param> /// <param name="pFileName">导出文件名</param> /// <param name="pHeader">导出标题以|分割</param> public static void DataTableExcel(System.Data.DataTable pData, string pFileName, string pHeader) { System.Web.UI.WebControls.DataGrid dgExport = null; // 当前对话 System.Web.HttpContext curContext = System.Web.HttpContext.Current; // IO用于导出并返回excel文件 System.IO.StringWriter strWriter = null; System.Web.UI.HtmlTextWriter htmlWriter = null; if (pData != null) { string UserAgent = curContext.Request.ServerVariables["http_user_agent"].ToLower(); if (UserAgent.IndexOf("firefox") == -1)//火狐浏览器 pFileName = HttpUtility.UrlEncode(pFileName, System.Text.Encoding.UTF8); curContext.Response.AddHeader("Content-Disposition", "attachment; filename=" + pFileName + ".xls"); curContext.Response.ContentType = "application/vnd.ms-excel"; strWriter = new System.IO.StringWriter(); htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter); // 为了解决dgData中可能进行了分页的情况,需要重新定义一个无分页的DataGrid dgExport = new System.Web.UI.WebControls.DataGrid(); dgExport.DataSource = pData.DefaultView; dgExport.AllowPaging = false; dgExport.ShowHeader = true;//显示标题 dgExport.DataBind(); string[] arrHeader = pHeader.Split('|'); string strHeader = "<table border=\"1\" style=\"background-color:Gray;font-weight:bold;\"><tr>"; foreach (string j in arrHeader) { strHeader += "<td>" + j.ToString() + "</td>"; } strHeader += "</tr></table>"; // 返回客户端 dgExport.RenderControl(htmlWriter); string strMeta = "<meta http-equiv=\"content-type\" content=\"application/ms-excel; charset=UTF-8\"/>"; curContext.Response.Write(strMeta + strHeader + strWriter.ToString()); curContext.Response.End(); } }