using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.IO;
using System.Web;
using Microsoft.Office.Interop.Excel;
/*
* 开发人员：jiede
* 时间：2009年11月17日
* 功能：将数据导出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;
}
application excel = 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;
}
Application excel = 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;
}
Application excel = 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("<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>");
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>");
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(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;
}
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>");
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;
}
}
