asp. net implements the method of exporting DataTable data to Word or Excel
- 2021-08-05 09:36:39
- OfStack
This article illustrates the asp. net implementation of exporting DataTable data to Word or Excel. Share it for your reference, as follows:
/// <summary>< xmlnamespace prefix ="o" ns ="urn:schemas-microsoft-com:office:office" />
/// Export DataTable Data to Word Or Excel
/// </summary>
/// <param name="pPage">Page Instruction </param>
/// <param name="dt">DataTable Data table </param>
/// <param name="str_ExportTitle"> Export Word Or Excel The name of the form </param>
/// <param name="str_ExportContentTitle"> Export Word Or Excel The title of the content in the table </param>
/// <param name="str_ExportMan"> Export Word Or Excel People of </param>
/// <param name="str_ExportType"> Export type ( 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";// Export type
string str_ExportFormat = ".doc";// Format of export type
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() // This paragraph needs to be added, otherwise Chinese garbled characters will appear
+ str_ExportFormat);
// Get DataTable Total number of columns of
int i_ColumnCount = dt.Columns.Count;
// Define variable storage DataTable Content
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(" Exporter: " " + str_ExportMan + " ", export time:" " + 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;'> Serial number </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 There is no appended to the front of the exported data column here 1 Column (sequence number column)
// There is no appended to the front of the exported data column here 1 Column (sequence number column)
//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>");
// }
// }
// builder.Append("</tr>\n");
//}
#endregion
#region Added to the front of the exported data column 1 Serial number column (note: not DataTable Sequence number of data)
// Added to the front of the exported data column 1 Serial number column (note: not DataTable Sequence number of data)
for (int m = 0; m < dt.Rows.Count; m++)
{
builder.Append("<tr>");
for (int j = 0; j < i_ColumnCount; j++)
{
if (dt.Columns[j].Caption.ToString().ToLower() != "id")
{
if (j == 0)
{
builder.Append("<td style='border:1px #7f9db9 solid;width:auto;' align='center'>" + (m + 1) + "</td>");
}
if (j > 0)
{
builder.Append("<td style='border:1px #7f9db9 solid;width:auto;vnd.ms-excel.numberformat:@' align='left'>" + dt.Rows[m][j - 1].ToString() + "</td>");
}
if (j == dt.Columns.Count - 1)
{
builder.Append("<td style='border:1px #7f9db9 solid;width:auto;vnd.ms-excel.numberformat:@' align='left'>" + dt.Rows[m][j].ToString() + "</td>");
}
}
}
builder.Append("</tr>\n");
}
#endregion
builder.Append("<tr><td colspan=" + (i_ColumnCount + 1) + " valign='middle' style='border:1px #7f9db9 solid;height:24px;' align='left'>");
builder.Append(" Total: Total " <font color='red'><b>" + dt.Rows.Count + "</b></font> "Record </td></tr>");
builder.Append("<tr>\n");
builder.Append("</table>");
response.Write(builder.ToString());
response.End();
bl_Result = true;
}
return bl_Result;
}
For more readers interested in asp. net, please check the topics of this site: "Summary of asp. net Operation json Skills", "Summary of asp. net String Operation Skills", "Summary of asp. net Operation XML Skills", "Summary of asp. net File Operation Skills", "Summary of asp. net ajax Skills" and "Summary of asp. net Cache Operation Skills".
I hope this article is helpful to everyone's asp. net programming.