Excel导入导出，生成和下载Excel报表、附件、数据库表等操作--ASP.NET
文件通用输出方法 文件流输出：Response.OutputStream.Write、Response.BinaryWrite、Response.WriteFile，并添加返回文件格式、编码等；
public class OutExcel { public static void OutExcel_bb(DataTable dt, string thepath, string temppath, int TitleNum, string Title1) { //通过调用Excel的查询来实现数据的导出，按固定格式 //ds：执行的记录集；thepath：模板文件的路径；sql：执行的SQL语句；FileName：保存的文件名 //TitleNum：表头列的行数 Excel.Application excel; Excel._Workbook xBk; Excel._Worksheet xSt; //string Conn = "OLEDB;Provider=SQLOLEDB.1;Data Source=(local);Initial Catalog=xdtz;User ID=sa;Password=sa;Max Pool Size = 512"; string path = thepath; excel = new Excel.Application(); excel.Visible = false; excel.UserControl = true; xBk = excel.Workbooks.Add(path); xSt = (Excel._Worksheet)xBk.Worksheets.get_Item(1); xSt.Cells[2, 1] = Title1; int StartRow = TitleNum; for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 0; j < dt.Columns.Count; j++) { xSt.Cells[StartRow + i, j + 1] = dt.Rows[i][j].ToString(); } } xBk.SaveAs(temppath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); excel.Visible = false; System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt); xSt = null; xBk.Close(false, null, null); System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk); xBk = null; excel.Workbooks.Close(); excel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); excel = null; System.GC.Collect(); } public static void OutExcel_bb(List<List<string>> lsts, string thepath, string temppath, string TitleName) { //通过调用Excel的查询来实现数据的导出，按固定格式 //ds：执行的记录集；thepath：模板文件的路径；sql：执行的SQL语句；FileName：保存的文件名 //TitleNum：表头列的行数 Excel.Application excel; Excel._Workbook xBk; Excel._Worksheet xSt; string path = thepath; excel = new Excel.Application(); excel.Visible = true; excel.UserControl = true; xBk = excel.Workbooks.Add(path); xSt = (Excel._Worksheet)xBk.Worksheets.get_Item(1); xSt.Cells[2, 1] = TitleName; int StartRow = 3; for (int i = 0; i < lsts.Count; i++) { List<string> lst = lsts[i]; int colspan = 0; for (int j = 0; j < lst.Count; j++) { int ri = StartRow + i, ci = (j + 1) + colspan; if (lst[j].Contains("colspan")) { int cp = Convert.ToInt16(lst[j].Split(':')[1]); xSt.get_Range(xSt.Cells[ri, ci - 1], xSt.Cells[ri, (ci - 1) + (cp - 1)]).Merge(true); colspan += cp - 2; } else { xSt.Cells[ri, ci] = lst[j]; } } } // xSt.get_Range(xSt.Cells[3, 1], xSt.Cells[3, 2]).Merge(true); // xSt.get_Range(xSt.Cells[4, 1], xSt.Cells[4, 2]).Merge(true); xBk.SaveAs(temppath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); excel.Visible = true; System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt); xSt = null; xBk.Close(false, null, null); System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk); xBk = null; excel.Workbooks.Close(); excel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); excel = null; System.GC.Collect(); } public static void OutExecl_cc(string Str,string Title) { StringWriter sw = new StringWriter(); sw.WriteLine(Title); sw.WriteLine(Str); sw.Close(); HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + System.Web.HttpUtility.UrlEncode(Title, System.Text.Encoding.UTF8) + ".xls");//中文 HttpContext.Current.Response.ContentType = "application/ms-excel"; HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); HttpContext.Current.Response.Write(sw); HttpContext.Current.Response.End(); } }
例：
protected void btnsearch_Click(object sender, EventArgs e) { AspNetPager1.CurrentPageIndex = 1; DataLoad(1); } protected void Button1_Click(object sender, EventArgs e) { if (TiaoJian == "") TiaoJian = " 1=1 ";// and state = 1 if (this.hiddept.Value != "") TiaoJian += " and deptid in(select id from dbo.Department where parentdept=" + this.hiddept.Value + ")"; if (this.txtname.Value != "") TiaoJian += " and username like '%" + this.txtname.Value + "%'"; if (this.drop1.SelectedValue != "") TiaoJian += " and state = " + this.drop1.SelectedValue + ""; DataSet ds = new BLL.Users().GetHMC(TiaoJian); string tick = DateTime.Now.ToString("yyyyMMddHHmmssff"); string thepath = "Upload/temp/hmcgs.xls"; string temppath = MapPath("Upload/temp/") + tick + ".xls"; OutExcel(ds, thepath, temppath,2,"花名册"); System.GC.Collect(); GetExcelFile(temppath,"花名册"); } public void OutExcel(DataSet ds, string thepath, string temppath, int TitleNum, string Title1) { //通过调用Excel的查询来实现数据的导出，按固定格式 //ds：执行的记录集；thepath：模板文件的路径；sql：执行的SQL语句；FileName：保存的文件名 //TitleNum：表头列的行数 Excel.Application excel; Excel._Workbook xBk; Excel._Worksheet xSt; //string Conn = "OLEDB;Provider=SQLOLEDB.1;Data Source=(local);Initial Catalog=xdtz;User ID=sa;Password=sa;Max Pool Size = 512"; string path = MapPath(thepath); excel = new Excel.Application(); excel.Visible = false; excel.UserControl = true; xBk = excel.Workbooks.Add(path); xSt = (Excel._Worksheet)xBk.Worksheets.get_Item(1); //xSt.Cells[2, 1] = Title1; int StartRow = TitleNum + 1; for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { xSt.Cells[StartRow + i, 1] = (i + 1); for (int j = 0; j < ds.Tables[0].Columns.Count; j++) { xSt.Cells[StartRow + i, j + 2] = ds.Tables[0].Rows[i][j].ToString(); } } xBk.SaveAs(temppath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); excel.Visible = false; System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt); xSt = null; xBk.Close(false, null, null); System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk); xBk = null; excel.Workbooks.Close(); excel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); excel = null; System.GC.Collect(); } private void GetExcelFile(string temppath, string FileName) { System.IO.FileInfo file = new System.IO.FileInfo(temppath); if (file.Exists) { Response.Clear(); Response.Charset = "GB2312"; Response.ContentEncoding = System.Text.Encoding.UTF8; // 添加头信息，为"文件下载/另存为"对话框指定默认文件名 Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(FileName + "(" + DateTime.Now.ToString("yyyyMMddHHmmss") + ").xls")); // 添加头信息，指定文件大小，让浏览器能够显示下载进度 Response.AddHeader("Content-Length", file.Length.ToString()); // 指定返回的是一个不能被客户端读取的流，必须被下载 Response.ContentType = "application/ms-excel"; // 把文件流发送到客户端 Response.WriteFile(file.FullName); // 停止页面的执行 Response.End(); } }
———————————————————————————————————————————————————————————————————————————
公共类：
public class ToExcell { /// <summary> /// 执行存储过程 /// </summary> /// <param name="PropName">存储过程名</param> /// <param name="_params">参数</param> /// <returns></returns> public DataSet GetDataSet(string PropName, SqlParameter[] _params,int Id) { return DBUtility.NewDbHelperSQL.RunProcedure(PropName, _params, "tb1", Id); } #region 下载报表 /// <summary> /// /// </summary> /// <param name="ds">查询出的数据</param> /// <param name="ExcelFileName">excel名称</param> public void MyXlsToExcelByDataSet(DataSet ds, string ExcelFileName) { DataTable dt = ds.Tables[0]; XlsDocument xlsDocument = new org.in2bits.MyXls.XlsDocument(); xlsDocument.FileName = HttpUtility.UrlEncode(System.Text.Encoding.UTF8.GetBytes(ExcelFileName)) + "_" + string.Format("{0:yyyyMMddHHmmss}", DateTime.Now) + ".xls"; Worksheet worksheet = xlsDocument.Workbook.Worksheets.Add("sheet1");//Excel工作表名称 Cells cells = worksheet.Cells; int columns = dt.Columns.Count; for (int i = 0; i < columns; i++)//列名 { cells.Add(1, (i + 1), dt.Columns[i].ColumnName.ToString().Trim()); } for (int j = 0; j < dt.Rows.Count; j++) { for (int k = 0; k < columns; k++) { cells.Add(j + 2, (k + 1), dt.Rows[j][k].ToString().Trim()); } } xlsDocument.Send(); } #endregion #region 下载报表 /// <summary> /// /// </summary> /// <param name="ds">查询出的数据</param> /// <param name="ExcelFileName">excel名称</param> /// <param name="rowName">对应的列名</param> public void MyXlsToExcelByDataSet(DataSet ds, string ExcelFileName, string[] rowName) { DataTable dt = ds.Tables[0]; XlsDocument xlsDocument = new org.in2bits.MyXls.XlsDocument(); xlsDocument.FileName = HttpUtility.UrlEncode(System.Text.Encoding.UTF8.GetBytes(ExcelFileName)) + "_" + string.Format("{0:yyyyMMddHHmmss}", DateTime.Now) + ".xls"; Worksheet worksheet = xlsDocument.Workbook.Worksheets.Add("sheet1");//Excel工作表名称 Cells cells = worksheet.Cells; int columns = dt.Columns.Count; for (int i = 0; i < columns; i++)//列名 { cells.Add(1, (i + 1), rowName[i].Trim()); } for (int j = 0; j < dt.Rows.Count; j++) { for (int k = 0; k < columns; k++) { cells.Add(j + 2, (k + 1), dt.Rows[j][k].ToString().Trim()); } } xlsDocument.Send(); } #endregion #region 附件下载 /// <summary> /// 附件下载方法 /// </summary> /// <param name="fileName">文件名</param> /// <param name="path">文件路径</param> public void DownLoadFile(string fileName, string path) { HttpContext.Current.Response.BufferOutput = false; HttpContext.Current.Response.Clear(); HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment; filename=" + DisposeFileName(fileName)); //HttpContext.Current.Server.UrlEncode(fileName));//防止中文名出现乱码 HttpContext.Current.Response.ContentType = "application/octstream"; HttpContext.Current.Response.CacheControl = "Private"; Stream stream = new FileStream(HttpContext.Current.Server.MapPath(path), FileMode.Open, FileAccess.Read, FileShare.Read); HttpContext.Current.Response.AppendHeader("Content-length", stream.Length.ToString()); BinaryReader br = new BinaryReader(stream); byte[] bytes; for (int n = 0; n < (br.BaseStream.Length / 4096 + 1); n++) { bytes = br.ReadBytes(4096); HttpContext.Current.Response.BinaryWrite(bytes); System.Threading.Thread.Sleep(5); //休息一下,防止耗用带宽太多。 } stream.Close(); } #endregion #region 文件下载时文件名处理 /// <summary> /// 文件下载时文件名处理 /// </summary> /// <param name="FileName"></param> /// <returns></returns> protected string DisposeFileName(string FileName) { FileName = FileName.Replace(" ", "");//去掉空格 return FileName; } #endregion }
.NET读取Excel文件内容--导入，简单示例：
<%-- 前台--%> <div> <%-- 文件上传控件 用于将要读取的文件上传 并通过此控件获取文件的信息--%> <asp:FileUpload ID="fileSelect" runat="server" /> <%-- 点击此按钮执行读取方法--%> <asp:Button ID="btnRead" runat="server" Text="ReadStart" /> </div>
后台获取
//声明变量（属性） string currFilePath = string.Empty; //待读取文件的全路径 string currFileExtension = string.Empty; //文件的扩展名 //Page_Load事件 注册按钮单击事件 protected void Page_Load(object sender, EventArgs e) { this.btnRead.Click += new EventHandler(btnRead_Click); } //按钮单击事件 //里面的3个方法将在下面给出 protected void btnRead_Click(object sender, EventArgs e) { Upload(); //上传文件方法 if (this.currFileExtension == ".xlsx" || this.currFileExtension == ".xls") { DataTable dt = ReadExcelToTable(currFilePath); //读取Excel文件（.xls和.xlsx格式） } else if (this.currFileExtension == ".csv") { DataTable dt = ReadExcelWidthStream(currFilePath); //读取.csv格式文件 } }
下面列出按钮单击事件中的3个方法
///<summary> ///上传文件到临时目录中 ///</ummary> private void Upload() { HttpPostedFile file = this.fileSelect.PostedFile; string fileName = file.FileName; string tempPath = System.IO.Path.GetTempPath(); //获取系统临时文件路径 fileName = System.IO.Path.GetFileName(fileName); //获取文件名（不带路径） this.currFileExtension = System.IO.Path.GetExtension(fileName); //获取文件的扩展名 this.currFilePath = tempPath + fileName; //获取上传后的文件路径 记录到前面声明的全局变量 file.SaveAs(this.currFilePath); //上传 } ///<summary> ///读取xls\xlsx格式的Excel文件的方法 ///</ummary> ///<param name="path">待读取Excel的全路径</param> ///<returns></returns> private DataTable ReadExcelToTable(string path) { //连接字符串 string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';"; // Office 07及以上版本 不能出现多余的空格 而且分号注意 //string connstring = Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';"; //Office 07以下版本 因为本人用Office2010 所以没有用到这个连接字符串 可根据自己的情况选择 或者程序判断要用哪一个连接字符串 using(OleDbConnection conn = new OleDbConnection(connstring)) { conn.Open(); DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new object[]{null,null,null,"Table"}); //得到所有sheet的名字 string firstSheetName = sheetsName.Rows[0][2].ToString(); //得到第一个sheet的名字 string sql = string.Format("SELECT * FROM [{0}],firstSheetName); //查询字符串 OleDbDataAdapter ada =new OleDbDataAdapter(sql,connstring); DataSet set = new DataSet(); ada.Fill(set); return set.Tables[0]; } } ///<summary> ///读取csv格式的Excel文件的方法 ///</ummary> ///<param name="path">待读取Excel的全路径</param> ///<returns></returns> private DataTable ReadExcelWithStream(string path) { DataTable dt = new DataTable(); bool isDtHasColumn = false; //标记DataTable 是否已经生成了列 StreamReader reader = new StreamReader(path,System.Text.Encoding.Default); //数据流 while(!reader.EndOfStream) { string meaage = reader.ReadLine(); string[] splitResult = message.Split(new char[]{','},StringSplitOption.None); //读取一行 以逗号分隔 存入数组 DataRow row = dt.NewRow(); for(int i = 0;i<splitResult.Length;i++) { if(!isDtHasColumn) //如果还没有生成列 { dt.Columns.Add("column" + i,typeof(string)); } row[i] = splitResult[i]; } dt.Rows.Add(row); //添加行 isDtHasColumn = true; //读取第一行后 就标记已经存在列 再读取以后的行时，就不再生成列 } return dt; }
示例完整代码，上传EXCEL并导入
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Text; using System.Data; using System.Data.OleDb; public partial class Tools_test : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } //资料导入点击事件 protected void Button1_Click(object sender, EventArgs e) { string clientid = Session["fileName"].ToString(); string CustTypeMax =""; string CustTypeMin = ""; string CustTypeSub = ""; string sbResult = ExeclData(clientid, CustTypeMax, CustTypeMin, CustTypeSub); alert("导入成功!"); } private void alert(string str) { Page.ClientScript.RegisterStartupScript(this.GetType(), " ", "<script>alert('" + HttpUtility.UrlDecode(str) + "')</script>"); } //上传文件 protected void Button2_Click(object sender, EventArgs e) { string strFileName = InputAffixFile.Value.Trim();//文件名 string strFileSize = (Convert.ToInt32(InputAffixFile.PostedFile.ContentLength.ToString()) / 1024).ToString();//文件大小 string strFileType = strFileName.Substring(strFileName.LastIndexOf(".") + 1).ToLower();//文件类型 string fileName = ""; string FilePath = ""; if (strFileName != "") { if (strFileType == "jpg" || strFileType == "psd" || strFileType == "swf" || strFileType == "gif " || strFileType == "bmp " || strFileType == "png " || strFileType == "xls" || strFileType == "doc" || strFileType == "pdf" || strFileType == "rar" || strFileType == "zip" || strFileType == "txt" || strFileType == "chm" || strFileType == "rtf" || strFileType == "docx" || strFileType == "wps" || strFileType == "xlsx" || strFileType == "et" || strFileType == "ppt" || strFileType == "pptx" || strFileType == "dps") { fileName = DateTime.Now.ToString("yyyMMddHHmmss") + "." + strFileType; ;//文件重命名 Session["fileName"] = fileName; FilePath = System.Web.HttpContext.Current.Server.MapPath("~") + "//Upload//file"; InputAffixFile.PostedFile.SaveAs(FilePath + "/" + fileName); Response.Write("<Script Language=JavaScript>alert(\"上传文件成功！\")</Script>"); } else { Response.Write("<Script Language=JavaScript>alert(\"上传文件失败！\")</Script>"); } } else { Response.Write("<Script Language=JavaScript>alert(\"请选择上传文件！\")</Script>"); } } //资料导入方法 public static string ExeclData(string clientid, string CustTypeMax, string CustTypeMin, string CustTypeSub) { { //获取上传的菜单名称和路径 string tempMenPath = System.Web.HttpContext.Current.Server.MapPath("~") + "Upload\\file\\" + clientid;// StringBuilder sbResult = new StringBuilder(""); string strconn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + tempMenPath + ";Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\""; //string strconn = "Provider=Microsoft.Jet.OleDb.4.0; Data Source=" + tempMenPath + "; Extended Properties=Excel 8.0;"; OleDbConnection conn = new OleDbConnection(strconn); conn.Open(); string sql; sql = "SELECT * FROM [Sheet1$]"; DataSet objDS = new DataSet(); OleDbDataAdapter objadp = new OleDbDataAdapter(sql, conn); objadp.Fill(objDS); DataTable MenDt = objDS.Tables[0]; conn.Close(); int result = 0; foreach (DataRow dr in MenDt.Select()) { try { Cms.BLL.C_article bllarticle = new Cms.BLL.C_article(); Cms.Model.C_article modelarticle = new Cms.Model.C_article(); modelarticle.parentId = Convert.ToInt32(dr[3].ToString());//栏目id modelarticle.title = dr[1].ToString();//标题 modelarticle.englishtitle = "";//英文标题 modelarticle.orderNumber = Convert.ToInt32(dr[13].ToString());//排序 modelarticle.artFrom = ""; modelarticle.photoUrl = dr[12].ToString();//缩略图 modelarticle.intro = dr[14].ToString();//简介 modelarticle.content = dr[5].ToString();//内容 modelarticle.seoTitle = dr[1].ToString();//seo标题 modelarticle.seoKeyword = dr[9].ToString();//seo关键词 modelarticle.seoDescription = dr[10].ToString();//seo描述 modelarticle.isRecommend = Convert.ToInt32(dr[11].ToString());//推荐 modelarticle.isChannel = "";//栏目推荐 modelarticle.isHidden = Convert.ToInt32(dr[8].ToString());//是否隐藏 modelarticle.isCheck = Convert.ToInt32(dr[8].ToString());//是否审核发布 modelarticle.isHot = Convert.ToInt32(dr[8].ToString());//是否热门文章 modelarticle.isTop = Convert.ToInt32(dr[8].ToString());//是否置顶 modelarticle.hits = Convert.ToInt32(dr[6].ToString());//点击量 modelarticle.expClass = "";// modelarticle.editTime = DateTime.Now;//最后编辑时间 modelarticle.updateTime = DateTime.Now;//添加时间 modelarticle.txtLinkUrl = "";//URL链接 modelarticle.txtsource = dr[2].ToString();//信息来源 modelarticle.txtauthor = dr[2].ToString();//文章作者 if (dr[0].ToString() == "") { result = bllarticle.Add(modelarticle); sbResult.Append("[" + dr[0].ToString() + "]增加成功 <br />"); } else { result = bllarticle.Add(modelarticle); if (result > 1) { sbResult.Append("增加成功 <br />"); } else { sbResult.Append("增加失败 <br />"); } } } catch { continue; } } return sbResult.ToString(); //更新到数据库中 } } }
