一、
调用 dstoexcel(getds(), "people.xls");
/// <param name="dtSource">参数是填充有数据表的DataSet， </param>
/// <param name="strFileName">文件名是全名，包括后缀名，如Excel2006.xls</param>
protected void dstoexcel(DataSet ds, string FileName)
{
HttpResponse resp;
resp = Page.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName);
string colHeaders = "", ls_item = "";
DataTable dt = ds.Tables[0];
DataRow[] myRow = dt.Select();//查询
int i = 0;
int cl = dt.Columns.Count;
//取得数据表各列标题，各标题之间以 分割，最后一个列标题后加
for (i = 0; i < cl; i++)
{
if (i == cl-1)
{
colHeaders += dt.Columns[i].Caption.ToString() + "
";
}
else
{
colHeaders += dt.Columns[i].Caption.ToString() + " ";
}
}
resp.Write(colHeaders);//向HTTP输出流中写入取得的数据信息
//逐行处理数据
foreach (DataRow row in myRow)
{
//当前行数据写入HTTP输出流，并且置空ls_item以便下行数据
for (i = 0; i < cl; i++)
{
if (i == (cl - 1))//最后一列，加
{
ls_item += row[i].ToString() + "
";
}
else
{
ls_item += row[i].ToString() + " ";
}
}
resp.Write(ls_item);
ls_item = "";
}
resp.End();
}
二、
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Response.Clear();
string content = "";
string filename = "";
sqlClass1 sc = new sqlClass1();
DataTable dt = sc.datasourceds();//数据源 dt
content = getExcelContent(dt);
if (dt.Rows.Count > 0)
{
filename = "信息统计表.xls";
}
string css = ".firstTR td{color:blue;100px;}.secondTR td{color:blue;100px;}";//可以设置复杂的样式
CommonTool.ExportToExcel(filename, content, css);
}
}
//内容很好理解，只需当成Table来拼字符串即可
private string getExcelContent(DataTable dt)
{
StringBuilder sb = new StringBuilder();
sb.Append("<table borderColor='black' border='1' >");
sb.Append("<thead><tr><th colSpan='5' bgColor='#ccfefe'>" + "信息表</th></tr>");
sb.Append("<tr><th bgColor='#ccfefe' width='135'>编号</th><th bgColor='#ccfefe'>姓名</th><th bgColor='#ccfefe'>年龄</th><th bgColor='#ccfefe'>性别</th></tr></thead>");
sb.Append("<tbody>");
foreach (DataRow DR in dt.Rows)
{
sb.Append("<tr class='firstTR'><td>" + DR["ID"].ToString() + "</td>" +
"<td>" + DR["name"].ToString() + "</td>" +
"</tr>");
}
sb.Append("</tbody></table>");
return sb.ToString();
}
public class CommonTool
{
/// <summary>
/// 以流的形式，可以设置很丰富复杂的样式
/// </summary>
/// <param name="content">Excel中内容(Table格式)</param>
/// <param name="filename">文件名</param>
/// <param name="cssText">样式内容</param>
public static void ExportToExcel(string filename, string content, string cssText)
{
var res = HttpContext.Current.Response;
content = String.Format("<style type='text/css'>{0}</style>{1}", cssText, content);
res.Clear();
res.Buffer = true;
res.Charset = "UTF-8";
res.AddHeader("Content-Disposition", "attachment; filename=" + filename);
res.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
res.ContentType = "application/ms-excel;charset=UTF-8";
res.Write(content);
res.Flush();
res.End();
}
}