最近的项目中有用到数据导出到Excel中,在网上也看了,几乎都是一些危言耸听的话,于是我把自己的代码和大家分享一下,供学习使用。
首先添加对Excel的引用 NOPI.DLL
///<summary> /// 导出Excel ///</summary> ///<param name="Page">导出Excel时的页面</param> ///<param name="parames">在Excel中增加的数据</param> ///<param name="dg">需要导出的数据源控件</param> ///<param name="fileName">导出Excel的文件名</param> ///<param name="typeName">导出的文件类型</param> 此处类型为:application/vnd.xls
public static void Export(System.Web.UI.Page Page,string parames, System.Web.UI.WebControls.DataGrid dg, string fileName, string typeName) { System.Web.HttpResponse httpResponse = Page.Response; httpResponse.AppendHeader("Content-Disposition", "attachment;filename=" HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8)); httpResponse.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); httpResponse.ContentType = typeName; System.IO.StringWriter tw = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw); dg.RenderControl(hw); tw.WriteLine("<b>" parames "</b>"); string filePath = Page.Server.MapPath("..") fileName; System.IO.StreamWriter sw = System.IO.File.CreateText(filePath); sw.Write(tw.ToString()); sw.Close(); DownFile(httpResponse, fileName, filePath); httpResponse.End(); }
/// <summary> /// 下载问价 /// </summary> /// <param name="Response">文件流</param> /// <param name="fileName">文件名</param> /// <param name="fullPath">文件路径</param> /// <returns></returns> public static bool DownFile(System.Web.HttpResponse Response, string fileName, string fullPath) { bool res = false; System.IO.FileStream fs = System.IO.File.OpenRead(fullPath); try { Response.ContentType = "application/octet-stream"; Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) + ";charset=GB2312"); long fLen = fs.Length; int size = 102400;//每100K同时下载数据 byte[] readData = new byte[size];//指定缓冲区的大小 if (size > fLen) size = Convert.ToInt32(fLen); if (size == 0) throw new Exception("文件生成失败!"); long fPos = 0; bool isEnd = false; while (!isEnd) { if ((fPos + size) > fLen) { size = Convert.ToInt32(fLen - fPos); readData = new byte[size]; isEnd = true; } fs.Read(readData, 0, size);//读入一个压缩块 if (readData.Length > 0) { Response.BinaryWrite(readData); fPos += size; } } res = true; } catch { res = false; } finally { fs.Close(); System.IO.File.Delete(fullPath); } return res; }
//读取excel填充到dataset
public DataSet ExcelToDS(string Path) { string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties='Excel 8.0;HDR=YES;IMEX=1;'"; OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); string strExcel = ""; OleDbDataAdapter myCommand = null; DataSet ds = null; strExcel = "select * from [Sheet1$]"; myCommand = new OleDbDataAdapter(strExcel, strConn); ds = new DataSet(); myCommand.Fill(ds, "table1"); return ds; }
excel 2003 使用的驱动是:
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
Excel 2010,2007 使用的驱动
strConn = "Provider= Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
//HDR=Yes 表示Excel表的第一行为标题行,No则表示为数据行。
//IMEX=1;”通知驱动程序始终将“互混”数据列作为文本读取。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)