.Net导出数据到Excle
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.OracleClient;
using System.Text;
public partial class ExportExcle : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
//导出Excel按钮
protected void btnExport_Click(object sender, EventArgs e)
{
string connstring = "User Id=***;Password=***;Data Source=***";
//实例化OracleConnection对象
using (OracleConnection conn = new OracleConnection(connstring))
{
conn.Open();
OracleDataAdapter oda=new OracleDataAdapter();
DataSet ds = new DataSet();
//实例化OracleCommand对象
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from Table where rownum <=10";
oda.SelectCommand = cmd;
oda.Fill(ds,"table");
if (ds != null)
{
string CSVstring = ExportCSV(ds.Tables[0]);
System.Web.HttpContext.Current.Response.Clear();
System.Web.HttpContext.Current.Response.ClearHeaders();
System.Web.HttpContext.Current.Response.ClearContent();
System.Web.HttpContext.Current.Response.ContentType = "applicationshlnd.xls";
System.Web.HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=" + System.Web.HttpUtility.UrlEncode("Excel文件名", System.Text.Encoding.UTF8) + ".xls");
byte[] csvBytes = Encoding.Unicode.GetBytes(CSVstring);
System.Web.HttpContext.Current.Response.AddHeader("Content-Length", (csvBytes.Length + 2).ToString());
System.Web.HttpContext.Current.Response.BinaryWrite(new byte[] { 0xFF, 0xFE });
System.Web.HttpContext.Current.Response.BinaryWrite(csvBytes);
System.Web.HttpContext.Current.Response.Flush();
System.Web.HttpContext.Current.Response.End();
}
}
}
public string ExportCSV(DataTable table)
{
System.Text.StringBuilder output =new System.Text.StringBuilder();
string delim;
output.Append("<table border=\"1\" cellspacing=\"0\" cellpadding=\"0\">");
delim = "";
output.Append("<tr>");
foreach(DataColumn col in table.Columns)
{
output.Append("<td style=\"vnd.ms-excel.numberformat:@\">");
output.Append(col.ColumnName);
output.Append("</td>");
}
output.Append("</tr>");
foreach(DataRow row in table.Rows)
{
delim ="";
output.Append("<tr>");
foreach(Object value in row.ItemArray)
{
output.Append("<TD style=\"vnd.ms-excel.numberformat:@\">");
output.Append(value.ToString());
output.Append("</TD>");
}
output.Append("</tr>");
}
output.Append("</table>");
return output.ToString();
}
}