实现信息在excel中输出-优快云博客

本文介绍三种将HTML数据转换为Excel文件的方法，包括直接利用DataTable生成Excel、通过GridView构建表格输出及利用DataTable导出数据。这些方法适用于不同场景，能够帮助开发者高效地完成数据导出任务。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

方法1:实现html输出到excel的方法
public static void RunHtmlToExcel(System.Data.DataTable dt, string ExcelHeaderName, string ExcelFieldName, string ExcelName, System.Web.UI.Page resp)
{
int excelColumnCount = ExcelHeaderName.Split(',').Length;
string[] arrayExcelHeaderName = ExcelHeaderName.Split(',');
System.Text.StringBuilder html = new System.Text.StringBuilder();
html.AppendLine("<table width=/"750/" border=/"1/" style=/"font-size:12px/">");

html.AppendLine("<tr><td colspan=/"" + excelColumnCount + "/" style=/"font-size:25px/" align=/"center/"><strong>" + ExcelName + "</strong></td></tr>");
html.AppendLine("<tr align=/"center/">");
for (int i = 0; i < excelColumnCount; i++)
{
html.AppendLine("<td >" + arrayExcelHeaderName[i] + "</td>");
}
html.AppendLine("</tr>");

int dtRowCount = dt.Rows.Count;
string[] columnName = ExcelFieldName.Split(',');
for (int j = 0; j < dtRowCount; j++)
{
html.Append("<tr align=/"center/">");
for (int n = 0; n < excelColumnCount; n++)
{
html.AppendLine("<td >" + dt.Rows[j][columnName[n]] + "</td>");
}
html.AppendLine("</tr>");

}
html.AppendLine("</table>");

//Http输出流对象
System.Web.HttpResponse httpResponse = resp.Response; //this.Page.Response;
//resp.ClientScript.RegisterClientScriptBlock(resp.GetType(), "getest", "<script type='text/javascript' language='javascript'>HidDivLoading();</script>");
httpResponse.Clear();
httpResponse.Write("<meta http-equiv=Content-Type content=text/html;charset=utf-8>");
resp.Response.AppendHeader("content-disposition", "attachment;filename=/"" + HttpUtility.UrlEncode("" + ExcelName + "[" + DateTime.Now.ToString("yyyy-MM-dd") + "]", System.Text.Encoding.UTF8) + ".xls/"");
httpResponse.Write(html.ToString());
httpResponse.End();
}

protected void Button3_Click(object sender, EventArgs e)
{

string connectionString= SQLHelper.connectionString;
OleDbConnection mconnection = new OleDbConnection(connectionString);
mconnection.Open();
string sql = "select * from view_log";
OleDbCommand myCommand = new OleDbCommand(sql, mconnection);
OleDbDataAdapter DA = new OleDbDataAdapter();
DA.SelectCommand = myCommand;
mconnection.Close();
DataSet ds = new DataSet();
DA.Fill(ds, "view_log");
System.Data.DataTable dt = new System.Data.DataTable();
dt = ds.Tables["view_log"];

//WorkLog w1 = new WorkLog();
//System.Data.DataTable dt1 = w1.GetTable(string.Empty);
RunHtmlToExcel(dt, "用户名,标题,时间,内容", "userName,logTitle,AddDate,LogContent", "日志汇总", this);
}

方法二:Gridview输出到excel(自已后台构建一个Gridview)

public void getExcel(string strWhere1)
{
/***********************/

Response.Clear();
Response.BufferOutput = true;
//设定输出的字符集

Response.Charset = "UTF-8";
Response.ContentEncoding = System.Text.Encoding.UTF8;

//假定导出的文件名为FileName.xls
Response.AppendHeader("Content-Disposition", "attachment;filename=FileName.xls");
Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");

//设置导出文件的格式
Response.ContentType = "application/ms-excel";

//关闭ViewState
EnableViewState = false;

System.Globalization.CultureInfo cultureInfo = new System.Globalization.CultureInfo("ZH-CN", true);
System.IO.StringWriter stringWriter = new System.IO.StringWriter(cultureInfo);
System.Web.UI.HtmlTextWriter textWriter = new System.Web.UI.HtmlTextWriter(stringWriter);

backStrWhere();//这个函数据返回strWhere1

DataSet ds1 = new LogCategory().GetList1(strWhere1);
grid1.DataSource = ds1;
grid1.DataBind();

grid1.RenderControl(textWriter);
把HTML写回浏览器
Response.Write(stringWriter.ToString());
Response.End();
}

方法二补充(把页面上的GridView输出到Excel)

#region aa
//Response.Clear();
//Response.BufferOutput = true;
设定输出的字符集

//Response.Charset = "UTF-8";
//Response.ContentEncoding = System.Text.Encoding.UTF8;

假定导出的文件名为FileName.xls
//Response.AppendHeader("Content-Disposition", "attachment;filename=FileName.xls");
//Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");

Response.ContentType = "application/vnd.xls";

设置导出文件的格式
//Response.ContentType = "application/ms-excel";

关闭ViewState
//EnableViewState = false;

//System.Globalization.CultureInfo cultureInfo = new System.Globalization.CultureInfo("ZH-CN", true);
//System.IO.StringWriter stringWriter = new System.IO.StringWriter(cultureInfo);
//System.Web.UI.HtmlTextWriter textWriter = new System.Web.UI.HtmlTextWriter(stringWriter);

当导出时取消分页(处理分页问题),设定哪些字段是你需要的就让其显示

//gridLogList.AllowPaging = false;

//gridLogList.Columns[2].Visible = false;
//gridLogList.Columns[4].Visible = false;
//gridLogList.Columns[5].Visible = false;
gridLogList.Columns[6].Visible = true;
//BindData();

//gridLogList.RenderControl(textWriter);
把HTML写回浏览器
//Response.Write(stringWriter.ToString());
//Response.End();

导出完成后继续分页

//gridLogList.AllowPaging = true;

gridLogList.Columns[2].Visible = true;
gridLogList.Columns[4].Visible = true;
gridLogList.Columns[5].Visible = true;
gridLogList.Columns[6].Visible = false;
//BindData();

方法三

/// <summary>
/// DataTable To Excel
/// </summary>
/// <param name="dt">DataTable Name</param>
/// <param name="typeid">1，Excel 2，XML</param>
/// <param name="FileName">文件名</param>
public void CreateExcel(System.Data.DataTable dt, string typeid, string FileName)
{
HttpResponse resp;
resp = Page.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
resp.ContentType = "application/ms-excel";
resp.AddHeader("Content-Disposition",
"attachment; filename=" + System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + ".xls");
this.EnableViewState = false;
string colHeaders = "", Is_item = "";
int i = 0;
//定义表对象与行对象，同时使用DataSet对其值进行初始化
DataRow[] myRow = dt.Select("");
//typeid=="1"时导出为Excel格式文件;typeid=="2"时导出为XML文件
if (typeid == "1")
{
//取得数据表各列标题，标题之间以/t分割，最后一个列标题后加回车符
for (i = 0; i < dt.Columns.Count; i++)
{
colHeaders += dt.Columns[i].Caption.ToString() + "/t";
}
colHeaders += "/n";
resp.Write(colHeaders);
//逐行处理数据
foreach (DataRow row in myRow)
{
//在当前行中，逐列取得数据，数据之间以/t分割，结束时加回车符/n
for (i = 0; i < dt.Columns.Count; i++)
{
Is_item += row[i].ToString() + "/t";
}
Is_item += "/n";
resp.Write(Is_item);
Is_item = "";
}
}
else
{
if (typeid == "2")
{
//从DataSet中直接导出XML数据并且写到HTTP输出流中
resp.Write(dt.DataSet.GetXml());
}
}
//写缓冲区中的数据到HTTP头文件中
resp.End();
}