众所周知，操作Excel一般分两种，其一就是用类似连接数据库的方式，将EXCEL文件当作小型数据库操作，这种方式我想大多数程序员都很喜欢；另一种就是用Microsoft.Office.Interop.Excel组件,引用组件则需要using Microsoft.Office.Interop.Excel。具体实现如下所示：
#region 方法一(其是将页面的内容直接输出，是html格式) /// <summary> /// 方法一(其是将页面的内容直接输出，是html格式) /// </summary> /// <param name="dt"></param> /// <param name="dd"></param> /// <param name="Name"></param> public void Export(System.Data.DataTable dt, string[] dd, string Name) { Response.ContentEncoding = Encoding.GetEncoding("GB2312"); //不是导出的excel文件名乱码是你自己把文件名编码了,是你用UrlEncode把文件名编码了,用HttpUtility.UrlDecode把文件名解码即可 Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(Name, Encoding.UTF8).ToString() + ".xls"); Response.ContentType = "application/ms-excel"; this.EnableViewState = false; StringBuilder productHeader = new StringBuilder(@"<table border='1' cellpadding='1'><tr>"); StringBuilder productRow = new StringBuilder(""); DataRow[] dr = dt.Select("1=1"); int itemCount = dt.Rows.Count; int columnCount = dt.Columns.Count; string[] cc = dd; for (int i = 0; i < cc.Length; i++) { productHeader.Append("<td >" + cc[i] + "</td>"); } productHeader.Append("</tr>"); Response.Write(productHeader.ToString()); for (int j = 0; j < itemCount; j++) { productRow.Append("<tr>"); for (int k = 0; k < columnCount; k++) { if (k == columnCount - 2) { productRow.Append(@"<td style='text-align:left;width:75%;mso-number-format:'\@''>" + dr[j][k].ToString() + "</td>"); } else { if (k == 1) { productRow.Append(@"<td style='text-align: left;vnd.ms-excel.numberformat:@;'>" + dr[j][k].ToString() + "</td>"); } else { productRow.Append("<td style='text-align: left;'>" + dr[j][k].ToString() + "</td>"); } } } productRow.Append("</tr>"); } productRow.Append("</table>"); Response.Write("<meta http-equiv=\"content-type\" content=\"application/ms-excel; charset=GB2312\"/>" + productRow); Response.End(); } #endregion #region 方法二 /// <summary> /// 方法二 /// </summary> /// <param name="dt"></param> /// <param name="dd"></param> /// <param name="FileName"></param> public void CreateExcel(System.Data.DataTable dt, string[] dd, string FileName) { HttpResponse resp; resp = Page.Response; resp.ContentEncoding = Encoding.GetEncoding("GB2312"); resp.ContentType = "application/ms-excel"; this.EnableViewState = false; resp.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString() + ".xls"); string colHeaders = "", ls_item = ""; int i = 0; DataRow[] myRow = dt.Select("1=1"); string[] cc = dd; for (i = 0; i < cc.Length; i++) { if (i == cc.Length - 1) { colHeaders += cc[i].ToString() + "\n"; } else { colHeaders += cc[i].ToString() + "\t"; } } resp.Write(colHeaders); foreach (DataRow row in myRow) { for (i = 0; i < dt.Columns.Count; i++) { if (i == dt.Columns.Count - 1) { if (i == 1) { ls_item += "'" + row[i].ToString() + "\n"; } else { ls_item += row[i].ToString() + "\n"; } } else { ls_item += "'" + row[i].ToString() + "\t"; } } resp.Write(ls_item); ls_item = ""; } //写缓冲区中的数据到HTTP头文件中 resp.End(); } #endregion导入Excel需要固定的模板，再导入方法界面初始化的时候写绑定FORM提交事件，然后直接读取Excel文件即可，具体实现如下所以：
#region 获取Excel文件 /// <summary> /// 获取Excel文件 /// </summary> /// <param name="context"></param> /// <param name="file"></param> /// <param name="dt"></param> private void UploadFormData(HttpContext context, HttpPostedFile file, ref DataTable dt) { if (file != null) { string fileName = DateTime.Now.ToString("yyyyMMddHHmmssfff") + file.FileName.Substring(file.FileName.LastIndexOf('.'));//file.FileName; string savePath = Server.MapPath("~/file/"); file.SaveAs(savePath + fileName); DataOperator(fileName, savePath, ref dt); if (File.Exists(savePath + fileName)) { File.Delete(savePath + fileName); } } } #endregion #region 数据操作 /// <summary> /// 数据操作 /// </summary> /// <param name="fileName"></param> /// <param name="savePath"></param> /// <param name="dt"></param> private void DataOperator(string fileName, string savePath, ref DataTable dt) { //2003（Microsoft.Jet.Oledb.4.0） string myString = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + savePath + fileName + ";Extended Properties=Excel 8.0"; //2010（Microsoft.ACE.OLEDB.12.0） // string myString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + savePath + fileName + ";Extended Properties='Excel 12.0;"; OleDbConnection oconn = new OleDbConnection(myString); oconn.Open(); DataSet ds = new DataSet(); OleDbDataAdapter oda = new OleDbDataAdapter("select * from [Sheet1$]", oconn); oda.Fill(ds); oconn.Close(); dt = ds.Tables[0]; } #endregion
