asp.net 导出excel

     有个b/s项目要导出数据到excel里，自己找了个导出通用类，写好了，客户使用后发现不正确，因为客户要把导出的数据再导入到其他的c/s文件里，在导入时发现b/s导出的excel格式不正确，客户反馈，我花了好久时间进行了修改，现在把代码发出来，给大家共享下

    

    先说下我开始用的方式。开始做的时候是通过流写出excel，然后保存，基本按照用流写txt格式的文件，然后再重命名成xls，这样导出的execl可是是和自由创建的excel有区别的，把这种方式贴出来让大家pp。

   int id = StringDeal.ToInt(Request.QueryString["id"]); string p = Request.QueryString["p"].ToString().Trim(); DataSet dst = new DataSet(); if (p == "0") { //string sql = "select c.title,d.company,b.renming,b.tel,b.phone,b.fex,b.mail from tb_order b,tb_menu c,tb_u_user d,tb_orderlist a where a.orderid=b.id and b.userid=d.id and b.itemid=c.id and c.id="+id+""; string sql = "select c.title,a.company,b.renming,b.tel,b.phone,b.fex,b.mail from tb_U_user a,tb_menu c,tb_order b where b.itemid=c.[id] and a.[id]=b.userid and c.[id]="+id+""; dst = XHR.DBUtility.SQLHelper.ExecuteDataset(sql); ExportHelper.ExportToExcel(0,dst, new string[] { "title", "company", "renming", "tel", "phone", "fex", "mail" }, new string[] { "招标批次", "投标单位名称", "联系人", "固定电话", "手机", "传真", "邮箱" }); } else { string sqlt = "select b.title, a.BagNum,a.title as atit,a.title1,a.prices,a.posttime from tb_U_info a,tb_menu b where b.[id]=a.classid and b.[id]=95"; dst = new DataSet(); dst = XHR.DBUtility.SQLHelper.ExecuteDataset(sqlt); ExportHelper.ExportToExcel(1,dst, new string[] { "title", "BagNum", "atit", "title1", "prices", "posttime" }, new string[] { "招标批次", "包号", "招标编号", "工程名称", "单价", "发布日期" }); }

   #region excel导出 // public class ExportHelper { // public string dcid= public static void ExportToExcel(int intp,DataSet dataList, string[] fields, string[] headTexts, string title) { GridView gvw = new GridView(); int ColCount, i; //如果筛选的字段和对应的列头名称个数相对的情况下只导出指定的字段 if (fields.Length != 0 && fields.Length == headTexts.Length) { ColCount = fields.Length; gvw.AutoGenerateColumns = false; for (i = 0; i < ColCount; i++) { BoundField bf = new BoundField(); bf.DataField = fields[i]; bf.HeaderText = headTexts[i]; gvw.Columns.Add(bf); } } else { gvw.AutoGenerateColumns = true; } SetStype(gvw); DataTable table = dataList.Tables[0]; if (intp == 1) { for (int j = 0; j < table.Rows.Count; j++) { DataRow dr = table.Rows[j]; dr["BagNum"] = "'" + dr["BagNum"].ToString(); } } gvw.DataSource = table;//dataList; gvw.DataBind(); ExportToExcel(gvw, title); } /// <summary> /// 导出数据到Excel /// </summary> /// <param name="DataList">IList Data</param> /// <param name="Fields">要导出的字段</param> /// <param name="HeadName">字段对应显示的名称</param> public static void ExportToExcel(int intp,DataSet dataList, string[] fields, string[] headTexts) { ExportToExcel(intp,dataList, fields, headTexts, string.Empty); } /// <summary> /// 设置样式 /// </summary> /// <param name="gvw"></param> private static void SetStype(GridView gvw) { gvw.Font.Name = "Verdana"; gvw.BorderStyle = System.Web.UI.WebControls.BorderStyle.Solid; gvw.HeaderStyle.BackColor = System.Drawing.Color.LightCyan; gvw.HeaderStyle.ForeColor = System.Drawing.Color.Black; gvw.HeaderStyle.HorizontalAlign = System.Web.UI.WebControls.HorizontalAlign.Center; gvw.HeaderStyle.Wrap = false; gvw.HeaderStyle.Font.Bold = true; gvw.HeaderStyle.Font.Size = 10; gvw.RowStyle.Font.Size = 10; } /// <summary> /// 导出GridView中的数据到Excel /// </summary> /// <param name="gvw"></param> /// <param name="DataList"></param> private static void ExportToExcel(GridView gvw, string title) { string fileName; HttpContext.Current.Response.Buffer = true; HttpContext.Current.Response.ClearContent(); HttpContext.Current.Response.ClearHeaders(); fileName = string.Format("Export-File {0:yyyy-MM-dd_HH_mm}.xls", DateTime.Now); HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName); HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"; System.IO.StringWriter tw = new System.IO.StringWriter(); HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw); gvw.RenderControl(hw); if (!string.IsNullOrEmpty(title)) { HttpContext.Current.Response.Write("<b><center><font size=3 face=Verdana color=#0000FF>" + title + "</font></center></b>"); } HttpContext.Current.Response.Write(tw.ToString()); HttpContext.Current.Response.Flush(); HttpContext.Current.Response.Close(); HttpContext.Current.Response.End(); gvw.Dispose(); tw.Dispose(); hw.Dispose(); gvw = null; tw = null; hw = null; } } #endregion

 

   再说下修改后的方式：

   通过模板，复制一个excel文件，把内容插入到新的excel文件里，这种方式也是w从网上找的方式，但是之前的代码效果是，模板xls必须预留一个表，通过代码复制一个xls，复制后再在新的xls里添加一个新表并不能删除到模板里的第一个表，造成新导出的xls里多了第一表，但是网上的具有很好的通用性，不用使用多个模板，只要一个模板就可以了，根据客户需要，我对这个代码进行了修改，在复制新xls时，不用新建表，使用第一表就可以了，但是这样会造成通用性不是很好，要每个导出都需要一个模板，有点小瑕疵，

   写的有点乱，不如把代码贴出来让大家pp

网上找的代码

public class ExcelCtrl { static OleDbType GetOledbType(System.Type DataType) { OleDbType ctype = OleDbType.VarWChar; if (DataType == System.Type.GetType("System.Int32") || DataType == System.Type.GetType("System.Int16") || DataType == System.Type.GetType("System.Int64")) { ctype = OleDbType.Integer; } else if (DataType == System.Type.GetType("System.String")) { ctype = OleDbType.VarChar; } else if (DataType == System.Type.GetType("System.Boolean")) { ctype = OleDbType.Boolean; } else if (DataType == System.Type.GetType("System.DateTime")) { ctype = OleDbType.Date; } else if (DataType == System.Type.GetType("System.Guid")) { ctype = OleDbType.Guid; } else { ctype = OleDbType.VarChar; } return ctype; } static private string GetExcelType(DataColumn col) { OleDbType ctype = GetOledbType(col.DataType); string clen = col.MaxLength == -1 ? "" : ("(" + (ctype != OleDbType.BSTR ? col.MaxLength.ToString() : "-1") + ")"); return " " + Enum.GetName(typeof(OleDbType), ctype) + clen; } /// <summary> /// DataSet导出到Excel文件 /// </summary> /// <param name="ds">数据集</param> /// <param name="Excelfile">Excel文件 FullPath</param> /// <returns></returns> public static bool DataSetToExcelFile(DataSet ds,string Excelfile){ if (ds.Tables.Count ==0) { return false; } string TemplateFile = System.Web.HttpContext.Current.Server.MapPath("~/Excel/Template.xls"); if (System.IO.File.Exists (Excelfile)) { System.IO.File.Delete(Excelfile); } System.IO.File.Copy(TemplateFile, Excelfile); string strConn; strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + Excelfile + "; Extended Properties=Excel 8.0;"; using (OleDbConnection conn = new OleDbConnection(strConn)) { conn.Open(); foreach (DataTable tempTable in ds.Tables) { if (tempTable.Columns.Count ==0 ) { break; } OleDbCommand myInsertCommand = new OleDbCommand("", conn); StringBuilder cmdtxt = new StringBuilder("Create Table ["); cmdtxt.Append(tempTable.TableName); cmdtxt.Append("] ("); StringBuilder insertCmdtxt = new StringBuilder("Insert into ["); insertCmdtxt.Append(tempTable.TableName); insertCmdtxt.Append("] ("); StringBuilder insertValues = new StringBuilder(" Values("); for (int i = 0; i < tempTable.Columns.Count; i++) { myInsertCommand.Parameters.Add( new OleDbParameter("@" + tempTable.Columns[i].ColumnName, GetOledbType(tempTable.Columns[i].DataType )) ); cmdtxt.Append("[" + tempTable.Columns[i].ColumnName + "]"); cmdtxt.Append(GetExcelType(tempTable.Columns[i])); insertCmdtxt.Append(tempTable.Columns[i].ColumnName); insertValues.Append("@" + tempTable.Columns[i].ColumnName); if (i != tempTable.Columns.Count - 1) { cmdtxt.Append(","); insertCmdtxt.Append(","); insertValues.Append(","); } } cmdtxt.Append(")"); insertCmdtxt.Append(")"); insertValues.Append(")"); insertCmdtxt.Append(insertValues); //添加数据表 OleDbCommand myCommand = new OleDbCommand(cmdtxt.ToString(), conn); myCommand.ExecuteNonQuery(); //删除模版中数据表Sheet1，占时不能成功？？。。。 myCommand.CommandText = "drop table [Sheet1$]"; myCommand.ExecuteNonQuery(); //插入行 myInsertCommand.CommandText = insertCmdtxt.ToString(); for (int i = 0; i < tempTable.Rows.Count; i++) { for (int j = 0; j < tempTable.Columns.Count; j++) { myInsertCommand.Parameters[j].Value = tempTable.Rows[i][j]; } myInsertCommand.ExecuteNonQuery(); } } conn.Close(); } return true; } /// <summary> /// Excel文件导出到DataSet /// </summary> /// <param name="Excelfile">Excel文件 FullPath</param> /// <returns></returns> public static DataSet ExcelFileToDataSet(string Excelfile) { string strConn; strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + Excelfile + "; Extended Properties=Excel 8.0;"; using (OleDbConnection conn = new OleDbConnection(strConn)) { conn.Open(); string[] res=null ; DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (null != dt) { res = new string[dt.Rows.Count]; for (int i = 0; i < dt.Rows.Count; i++) { res[i] = dt.Rows[i]["TABLE_NAME"].ToString(); // res[i] = res[i].Substring(0, res[i].Length - 1); } DataSet retDS = new DataSet(); StringBuilder selectCmdtxt = new StringBuilder(); for (int i = 0; i < res.Length; i++) { DataTable tempdt = new DataTable(); // "SELECT * FROM [" +SheetName +"$" + StartCell +":"+ EndCell + "]"; OleDbDataAdapter ap = new OleDbDataAdapter("SELECT * FROM [" + res[i] + "] ", conn); ap.Fill(tempdt); retDS.Tables.Add(tempdt); } conn.Close(); return retDS; } } return null; } }

   修改后的代码

public class ExcelCtrl { static OleDbType GetOledbType(System.Type DataType) { OleDbType ctype = OleDbType.VarWChar; if (DataType == System.Type.GetType("System.Int32") || DataType == System.Type.GetType("System.Int16") || DataType == System.Type.GetType("System.Int64")) { ctype = OleDbType.Integer; } else if (DataType == System.Type.GetType("System.String")) { ctype = OleDbType.VarChar; } else if (DataType == System.Type.GetType("System.Boolean")) { ctype = OleDbType.Boolean; } else if (DataType == System.Type.GetType("System.DateTime")) { ctype = OleDbType.Date; } else if (DataType == System.Type.GetType("System.Guid")) { ctype = OleDbType.Guid; } else { ctype = OleDbType.VarChar; } return ctype; } static private string GetExcelType(DataColumn col) { OleDbType ctype = GetOledbType(col.DataType); string clen = col.MaxLength == -1 ? "" : ("(" + (ctype != OleDbType.BSTR ? col.MaxLength.ToString() : "-1") + ")"); return " " + Enum.GetName(typeof(OleDbType), ctype) + clen; } /// <summary> /// DataSet导出到Excel文件 /// </summary> /// <param name="ds">数据集</param> /// <param name="Excelfile">Excel文件 FullPath</param> /// <returns></returns> public static bool DataSetToExcelFile(DataSet ds, string Excelfile, string TemplateFile) { if (ds.Tables.Count == 0) { return false; } if (System.IO.File.Exists(Excelfile)) { System.IO.File.Delete(Excelfile); } System.IO.File.Copy(TemplateFile, Excelfile); string strConn; strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + Excelfile + "; Extended Properties=Excel 8.0;"; using (OleDbConnection conn = new OleDbConnection(strConn)) { conn.Open(); DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); // dt.Rows.RemoveAt(0); for (int index = 0; index < ds.Tables.Count; index++) { DataTable tempTable = ds.Tables[index]; if (tempTable.Columns.Count == 0) { break; } OleDbCommand myInsertCommand = new OleDbCommand("", conn); StringBuilder cmdtxt = new StringBuilder(""); if (dt.Rows.Count > index) { cmdtxt.Append("ALTER Table ["); cmdtxt.Append(dt.Rows[index]["TABLE_NAME"].ToString()); cmdtxt.Append("] ADD COLUMN "); //tempTable.TableName = dt.Rows[index]["TABLE_NAME"].ToString(); // HttpContext.Current.Response.Write(tempTable.TableName.ToString()); //HttpContext.Current.Response.End(); } else { cmdtxt.Append("Create Table ["); cmdtxt.Append(tempTable.TableName); cmdtxt.Append("] ("); } //cmdtxt.Append("Create Table ["); //cmdtxt.Append(tempTable.TableName); //cmdtxt.Append("] ("); StringBuilder insertCmdtxt = new StringBuilder("Insert into ["); insertCmdtxt.Append(tempTable.TableName); insertCmdtxt.Append("] ("); StringBuilder insertValues = new StringBuilder(" Values("); for (int i = 0; i < tempTable.Columns.Count; i++) { myInsertCommand.Parameters.Add( new OleDbParameter("@" + tempTable.Columns[i].ColumnName, GetOledbType(tempTable.Columns[i].DataType)) ); cmdtxt.Append("[" + tempTable.Columns[i].ColumnName + "]"); cmdtxt.Append(GetExcelType(tempTable.Columns[i])); insertCmdtxt.Append(tempTable.Columns[i].ColumnName); insertValues.Append("@" + tempTable.Columns[i].ColumnName); if (i != tempTable.Columns.Count - 1) { cmdtxt.Append(","); insertCmdtxt.Append(","); insertValues.Append(","); } } if (dt.Rows.Count > index) { cmdtxt.Append(""); } else { cmdtxt.Append(")"); } insertCmdtxt.Append(")"); insertValues.Append(")"); insertCmdtxt.Append(insertValues); //添加数据表 if (index == 0) { } else { OleDbCommand myCommand = new OleDbCommand(cmdtxt.ToString(), conn); myCommand.ExecuteNonQuery(); } //删除模版中数据表Sheet1，占时不能成功？？。。。 // myCommand.CommandText = "drop table [Sheet1$]"; // myCommand.ExecuteNonQuery(); //插入行 // HttpContext.Current.Response.Write(insertCmdtxt.ToString()); // HttpContext.Current.Response.End(); myInsertCommand.CommandText = insertCmdtxt.ToString(); for (int i = 0; i < tempTable.Rows.Count; i++) { for (int j = 0; j < tempTable.Columns.Count; j++) { myInsertCommand.Parameters[j].Value = tempTable.Rows[i][j]; } myInsertCommand.ExecuteNonQuery(); } } conn.Close(); } return true; } /// <summary> /// DataSet导出到Excel文件 /// </summary> /// <param name="ds">数据集</param> /// <param name="Excelfile">Excel文件 FullPath</param> /// <returns></returns> public static bool DataSetToExcelFile(DataSet ds, string Excelfile) { string TemplateFile = System.Web.HttpContext.Current.Server.MapPath("tt.xls"); return DataSetToExcelFile(ds, Excelfile, TemplateFile); } /// <summary> /// Excel文件导出到DataSet /// </summary> /// <param name="Excelfile">Excel文件 FullPath</param> /// <returns></returns> public static DataSet ExcelFileToDataSet(string Excelfile) { string strConn; strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + Excelfile + "; Extended Properties=Excel 8.0;"; using (OleDbConnection conn = new OleDbConnection(strConn)) { conn.Open(); string[] res = null; DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (null != dt) { res = new string[dt.Rows.Count]; for (int i = 0; i < dt.Rows.Count; i++) { res[i] = dt.Rows[i]["TABLE_NAME"].ToString(); // res[i] = res[i].Substring(0, res[i].Length - 1); } DataSet retDS = new DataSet(); StringBuilder selectCmdtxt = new StringBuilder(); for (int i = 0; i < res.Length; i++) { DataTable tempdt = new DataTable(); // "SELECT * FROM [" +SheetName +"$" + StartCell +":"+ EndCell + "]"; OleDbDataAdapter ap = new OleDbDataAdapter("SELECT * FROM [" + res[i] + "] ", conn); ap.Fill(tempdt); retDS.Tables.Add(tempdt); } conn.Close(); return retDS; } } return null; } }

调用方式

string sql = "select c.title as 批次,a.company as 投标人名称,b.renming as 联系人,b.tel as 固定电话,b.phone as 手机,b.fex as 传真,b.mail as Email from tb_U_user a,tb_menu c,tb_order b where b.itemid=c.[id] and a.[id]=b.userid and c.[id]=" + id + ""; dst = XHR.DBUtility.SQLHelper.ExecuteDataset(sql); dst.Tables[0].TableName = "Sheet1$"; if (Class1.ExcelCtrl.DataSetToExcelFile(dst, Server.MapPath("daochu/toubiaoren.xls"), Server.MapPath("daochu/toubiaorenmb.xls"))) { Msg("导出成功！", "gourl", GetUrl("/adminmanage/menu/daochu/toubiaoren.xls")); } else { XHR.Common.StringDeal.Alter("导出错误！"); }