导出DataTable数据到Word或者Excel

最新推荐文章于 2023-12-20 11:10:24 发布

qubernet

最新推荐文章于 2023-12-20 11:10:24 发布

阅读量183

点赞数

分类专栏： C# .Net 文章标签：导出 DataTable

本文链接：https://blog.csdn.net/qubernet/article/details/82920625

版权

.Net 同时被 2 个专栏收录

62 篇文章 1 订阅

订阅专栏

61 篇文章 6 订阅

订阅专栏

/// <summary>< xmlnamespace prefix ="o" ns ="urn:schemas-microsoft-com:office:office" />

/// 导出DataTable数据到Word或者Excel

/// </summary>

/// <param name="pPage">Page指令</param>

/// <param name="dt">DataTable数据表</param>

/// <param name="str_ExportTitle">导出Word或者Excel表格的名字</param>

/// <param name="str_ExportContentTitle">导出Word或者Excel表格中内容的标题</param>

/// <param name="str_ExportMan">导出Word或者Excel的人</param>

/// <param name="str_ExportType">导出类型（w：Word，e：Excel）</param>

public bool DataTableToExcel(Page pPage, DataTable dt, string str_ExportTitle, string str_ExportContentTitle, string str_ExportMan, string str_ExportType)

{

bool bl_Result = false;

string str_ExportTypeName = "word";//导出类型

string str_ExportFormat = ".doc";//导出类型的格式

if (str_ExportType.Equals("e"))

{

str_ExportTypeName = "excel";

str_ExportFormat = ".xls";

}

HttpResponse response = pPage.Response;

if (dt.Rows.Count > 0)

{

response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");

response.ContentType = "application/ms-" + str_ExportTypeName;

response.AppendHeader("Content-Disposition", "attachment;filename="

+ HttpUtility.UrlEncode(str_ExportTitle, System.Text.Encoding.UTF8).ToString() //该段需加，否则会出现中文乱码

+ str_ExportFormat);

//获取DataTable的总列数

int i_ColumnCount = dt.Columns.Count;

//定义变量存储DataTable内容

System.Text.StringBuilder builder = new System.Text.StringBuilder();

builder.Append("<html><head>\n");

builder.Append("<meta http-equiv=\"Content-Language\" content=\"zh-cn\">\n");

builder.Append("<meta http-equiv=\"Content-Type\" content=\"text/html; charset=gb2312\">\n");

builder.Append("</head>\n");

builder.Append("<table border='1' style='width:auto;'>");

if (!string.IsNullOrEmpty(str_ExportContentTitle))

{

builder.Append(string.Concat(new object[] { "<tr><td colspan=", (i_ColumnCount + 1),

" style='border:1px #7f9db9 solid;font-size:18px;font-weight:bold;'>",

str_ExportContentTitle,

"</td></tr>" }));

}

builder.Append("<tr><td colspan=" + (i_ColumnCount + 1) + " valign='middle' style='border:1px #7f9db9 solid;height:24px;'>");

builder.Append("导出人：【" + str_ExportMan + "】，导出时间：【" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "】</td></tr>");

builder.Append("<tr>\n");

builder.Append("<td style='border:1px #7f9db9 solid;bgcolor:#dee7f1;font-weight:bold;width:auto;'>序号</td>\n");

for (int i = 0; i < i_ColumnCount; i++)

{

if (dt.Columns[i].Caption.ToString().ToLower() != "id")

{

builder.Append("<td style='border:1px #7f9db9 solid;bgcolor:#dee7f1;width:auto;' align='center'><b>" + dt.Columns[i].Caption.ToString() + "</b></td>\n");

}

#region 此处没有在导出的数据列的最前面加一列（序号列）

//此处没有在导出的数据列的最前面加一列（序号列）

//foreach (DataRow row in dt.Rows)

//{

// builder.Append("<tr>");

// for (int j = 0; j < i_ColumnCount; j++)

// {

// if (dt.Columns[j].Caption.ToString().ToLower() != "id")

// {

// builder.Append("<td style='border:1px #7f9db9 solid;vnd.ms-excel.numberformat:@'>" + row[j].ToString() + "</td>");

// }