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(); } public static void GetExcelFile(string temppath, string FileName) { System.IO.FileInfo file = new System.IO.FileInfo(temppath); if (file.Exists) { HttpContext.Current.Response.Clear(); HttpContext.Current.Response.Charset = "GB2312"; HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8; // 添加头信息，为"文件下载/另存为"对话框指定默认文件名 //if (TxtTime2.Visible == true) //{ // HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(FileName + "(" + TxtTime2.Text + ").xls")); //} //else //{ // HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(FileName + "(" + System.DateTime.Now.ToShortDateString() + ").xls")); //} HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + ".xls"); // 添加头信息，指定文件大小，让浏览器能够显示下载进度 HttpContext.Current.Response.AddHeader("Content-Length", file.Length.ToString()); // 指定返回的是一个不能被客户端读取的流，必须被下载 HttpContext.Current.Response.ContentType = "application/ms-excel"; // 把文件流发送到客户端 HttpContext.Current.Response.WriteFile(file.FullName,true); // 停止页面的执行 file.Delete(); 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(); } }
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 }
从数据库中导出数据到Excel操作--ASP.NET,布布扣,bubuko.com
原文：http://www.cnblogs.com/elves/p/3578449.html