使用 C# 将 DataTable 导出到 Excel 并带格式
4.83/5 (30投票s)
将 DataTable 导出到 Excel 文件，并在写入 Excel 文件时为内容添加格式。
引言
在本技巧中，我们将学习如何将 DataTable 导出到 Excel 文件，并在写入 Excel 文件时为内容添加格式。
步骤 1：创建一个 Web 应用程序，并添加一个具有以下属性的类 Student
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Reflection;
namespace ExportToExcelFromDataTable
{
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
}
public class Student
{
public string Name { get; set; }
public int StudentId { get; set; }
public int Age { get; set; }
}
}
步骤 2：我添加了 Gridview_Result。在 page_load 事件中创建一个学生列表。添加一个类型为 DataTable 的属性 dt。将 DataTable 绑定到 GridView，方法是将 List 转换为 DataTable。转换类在下一步中描述。
protected void Page_Load(object sender, EventArgs e)
{
List<Student> Students = new List<Student>(){
new Student() { Name = "Jack", Age = 15, StudentId = 100 },
new Student() { Name = "Smith", Age = 15, StudentId = 101 },
new Student() { Name = "Smit", Age = 15, StudentId = 102 }
};
ListtoDataTableConverter converter = new ListtoDataTableConverter();
dt = converter.ToDataTable(Students);
GridView_Result.DataSource = Students;
GridView_Result.DataBind();
}
步骤 3：现在我们将把这个 List 对象转换为 DataTable。为此，我们需要创建一个新类和一个转换方法，如下所示。
public class ListtoDataTableConverter
{
public DataTable ToDataTable<T>(List<T> items)
{
DataTable dataTable = new DataTable(typeof(T).Name);
//Get all the properties
PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach (PropertyInfo prop in Props)
{
//Setting column names as Property names
dataTable.Columns.Add(prop.Name);
}
foreach (T item in items)
{
var values = new object[Props.Length];
for (int i = 0; i < Props.Length; i++)
{
//inserting property values to datatable rows
values[i] = Props[i].GetValue(item, null);
}
dataTable.Rows.Add(values);
}
//put a breakpoint here and check datatable
return dataTable;
}
}
上述方法会将属性名称设置为 DataTable 的列名，对于列表中的每个对象，它将在 DataTable 中创建一个新行并插入值。
步骤 4：我编写了以下方法，它将 DataTable 转换为 Excel 文件。在此方法中，我添加了字体，使标题加粗，并添加了边框。您可以根据需要自定义该方法。
private void ExporttoExcel(DataTable table)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.ContentType = "application/ms-excel";
HttpContext.Current.Response.Write(@"<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.0 Transitional//EN"">");
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=Reports.xls");
HttpContext.Current.Response.Charset = "utf-8";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("windows-1250");
//sets font
HttpContext.Current.Response.Write("<font style='font-size:10.0pt; font-family:Calibri;'>");
HttpContext.Current.Response.Write("<BR><BR><BR>");
//sets the table border, cell spacing, border color, font of the text, background, foreground, font height
HttpContext.Current.Response.Write("<Table border='1' bgColor='#ffffff' " +
"borderColor='#000000' cellSpacing='0' cellPadding='0' " +
"style='font-size:10.0pt; font-family:Calibri; background:white;'> <TR>");
//am getting my grid's column headers
int columnscount = GridView_Result.Columns.Count;
for (int j = 0; j < columnscount; j++)
{ //write in new column
HttpContext.Current.Response.Write("<Td>");
//Get column headers and make it as bold in excel columns
HttpContext.Current.Response.Write("<B>");
HttpContext.Current.Response.Write(GridView_Result.Columns[j].HeaderText.ToString());
HttpContext.Current.Response.Write("</B>");
HttpContext.Current.Response.Write("</Td>");
}
HttpContext.Current.Response.Write("</TR>");
foreach (DataRow row in table.Rows)
{//write in new row
HttpContext.Current.Response.Write("<TR>");
for (int i = 0; i < table.Columns.Count; i++)
{
HttpContext.Current.Response.Write("<Td>");
HttpContext.Current.Response.Write(row[i].ToString());
HttpContext.Current.Response.Write("</Td>");
}
HttpContext.Current.Response.Write("</TR>");
}
HttpContext.Current.Response.Write("</Table>");
HttpContext.Current.Response.Write("</font>");
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
}
步骤 4：添加一个按钮，并在按钮单击事件中，通过传递参数调用上述方法。
protected void Btn_Export_Click(object sender, EventArgs e)
{
ExporttoExcel(dt);
}
有关完整的源代码，请参阅随附的解决方案。