C# 生成Excel 并保存 包含web和winform调用方法

C# 生成Excel 并保存 包含web和winform调用方法

using System;

using System.Collections.Generic;using System.Text;using System.Data;using System.IO;using System.Web;using Microsoft.Office.Interop.Excel;namespace XT.LiTree.Logic{public class ExcelExport{/// <summary>/// 直接导出Excel/// </summary>/// <param name="ds">数据源DataSet</param>/// <param name="fileName">保存文件名(例如：E:/a.xls)</param>/// <returns></returns>public bool DoExport(DataSet ds, string fileName){if (ds.Tables.Count == 0 || fileName == string.Empty){return false;}applicationexcel= new ApplicationClass();int rowindex = 1;int colindex = 0;Workbook work = excel.Workbooks.Add(true);//Worksheet sheet1 = (Worksheet)work.Worksheets[0];System.Data.DataTable table = ds.Tables[0];foreach (DataColumn col in table.Columns){colindex++;excel.Cells[1, colindex] = col.ColumnName;}foreach (DataRow row in table.Rows){rowindex++;colindex = 0;foreach (DataColumn col in table.Columns){colindex++;excel.Cells[rowindex, colindex] = row[col.ColumnName].ToString();}}excel.Visible = false;//((Worksheet)work.Sheets[0]).Name = "sss";excel.ActiveWorkbook.SaveAs(fileName, XlFileFormat.xlExcel9795, null, null, false, false, XlSaveAsaccessMode.xlNoChange, null, null, null, null, null);excel.Quit();excel = null;GC.Collect();return true;}/// <summary>/// 直接导出Excel/// </summary>/// <param name="ds">数据源DataSet</param>/// <param name="columns">列名数组,允许为空(columns=null),为空则表使用默认数据库列名 </param>/// <param name="fileName">保存文件名(例如：E:/a.xls)</param>/// <returns></returns>public bool DoExport(DataSet ds, string[] columns, string fileName){if (ds.Tables.Count == 0 || fileName == string.Empty){return false;}Applicationexcel= new ApplicationClass();int rowindex = 1;int colindex = 0;Workbook work = excel.Workbooks.Add(true);//Worksheet sheet1 = (Worksheet)work.Worksheets[0];System.Data.DataTable table = ds.Tables[0];if (columns != null){for (int i = 0; i < columns.Length; i++){colindex++;if (columns[i] != null && columns[i] != ""){excel.Cells[1, colindex] = columns[i];}else{excel.Cells[1, colindex] = table.Columns[i].ColumnName;}}}else{foreach (DataColumn col in table.Columns){colindex++;excel.Cells[1, colindex] = col.ColumnName;}}foreach (DataRow row in table.Rows){rowindex++;colindex = 0;foreach (DataColumn col in table.Columns){colindex++;excel.Cells[rowindex, colindex] = row[col.ColumnName].ToString();}}excel.Visible = false;//((Worksheet)work.Sheets[0]).Name = "sss";excel.ActiveWorkbook.SaveAs(fileName, XlFileFormat.xlExcel9795, null, null, false, false, XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);excel.Quit();excel = null;GC.Collect();return true;}/// <summary>/// 直接导出Excel/// </summary>/// <param name="sql">SQL查询语句</param>/// <param name="columns">列名数组</param>/// <param name="fileName">保存文件名(例如：E:/a.xls)</param>/// <returns></returns>public bool DoExport(string sql, string[] columns, string fileName){Dao.DataBase db = new XT.LiTree.Dao.DataBase();DataSet ds = db.GetDS(sql);if (ds.Tables.Count == 0 || fileName == string.Empty){return false;}Applicationexcel= new ApplicationClass();int rowindex = 1;int colindex = 0;Workbook work = excel.Workbooks.Add(true);//Worksheet sheet1 = (Worksheet)work.Worksheets[0];System.Data.DataTable table = ds.Tables[0];if (columns != null){for (int i = 0; i < columns.Length; i++){colindex++;if (columns[i] != null && columns[i] != ""){excel.Cells[1, colindex] = columns[i];}else{excel.Cells[1, colindex] = table.Columns[i].ColumnName;}}}else{foreach (DataColumn col in table.Columns){colindex++;excel.Cells[1, colindex] = col.ColumnName;}}foreach (DataRow row in table.Rows){rowindex++;colindex = 0;foreach (DataColumn col in table.Columns){colindex++;excel.Cells[rowindex, colindex] = row[col.ColumnName].ToString();}}excel.Visible = false;//((Worksheet)work.Sheets[0]).Name = "sss";excel.ActiveWorkbook.SaveAs(fileName, XlFileFormat.xlExcel9795, null, null, false, false, XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);excel.Quit();excel = null;GC.Collect();return true;}/// <summary>/// 通过流导出Excel/// </summary>/// <param name="ds">数据源DataSet</param>/// <param name="fileName">保存文件名(例如：a.xls)</param>/// <returns></returns>public bool StreamExport(DataSet ds, string fileName){if (ds.Tables.Count == 0 || fileName == string.Empty){return false;}System.Data.DataTable dt = ds.Tables[0];StringBuilder content = new StringBuilder();StringBuilder strtitle = new StringBuilder();content.Append("<htmlxmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns='http://www.w3.org/TR/REC-html40'>");content.Append("<head><title></title><meta http-equiv='Content-Type' content=/"text/html; charset=gb2312/"></head><body><table x:str cellspacing='0' rules='all' border='1' id='title1' style="border-collapse:collapse;" mce_style="border-collapse:collapse;">");content.Append("<tr>");for (int j = 0; j < dt.Columns.Count; j++){content.Append("<td>" + dt.Columns[j].ColumnName + "</td>");}content.Append("</tr>/n");for (int j = 0; j < dt.Rows.Count; j++){content.Append("<tr>");for (int k = 0; k < dt.Columns.Count; k++){content.Append("<td>" + dt.Rows[j][k].ToString() + "</td>");}content.Append("</tr>/n");}content.Append("</table></body></html>");content.Replace(" ", "");//fileContent = (byte[])System.Text.Encoding.Default.GetBytes(content.ToString());//System.Web.UI.WebControls.System.Web.HttpContext.Current.Response.Clear();System.Web.HttpContext.Current.Response.Buffer = true;System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";System.Web.HttpContext.Current.Response.Charset = "GB2312";HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");System.Web.HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName);//HttpUtility.UrlEncode(fileName));//HttpUtility.UrlEncode(fileName,Encoding.UTF8);//System.Web.HttpContext.Current.Response.ContentEncoding=System.Text.Encoding.Default;System.Web.HttpContext.Current.Response.Write(content.ToString());System.Web.HttpContext.Current.Response.End();return true;}/// <summary>/// 通过流导出Excel/// </summary>/// <param name="ds">数据源DataSet</param>/// <param name="columns">列名数组,允许为空(columns=null),为空则表使用默认数据库列名</param>/// <param name="fileName"></param>/// <returns></returns>public bool StreamExport(DataSet ds, string[] columns, string fileName){if (ds.Tables.Count == 0 || fileName == string.Empty){return false;}System.Data.DataTable dt = ds.Tables[0];StringBuilder content = new StringBuilder();StringBuilder strtitle = new StringBuilder();content.Append("<html xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns='http://www.w3.org/TR/REC-html40'>");content.Append("<head><title></title><meta http-equiv='Content-Type' content=/"text/html; charset=gb2312/"></head><body><table x:str cellspacing='0' rules='all' border='1' id='title1' style="border-collapse:collapse;" mce_style="border-collapse:collapse;">");content.Append("<tr>");if (columns != null){for (int i = 0; i < columns.Length; i++){if (columns[i] != null && columns[i] != ""){content.Append("<td>" + columns[i] + "</td>");}else{content.Append("<td>" + dt.Columns[i].ColumnName + "</td>");}}}else{for (int j = 0; j < dt.Columns.Count; j++){content.Append("<td>" + dt.Columns[j].ColumnName + "</td>");}}content.Append("</tr>/n");for (int j = 0; j < dt.Rows.Count; j++){content.Append("<tr>");for (int k = 0; k < dt.Columns.Count; k++){content.Append("<td>" + dt.Rows[j][k].ToString() + "</td>");}content.Append("</tr>/n");}content.Append("</table></body></html>");co