如何将 DataTable 导出到 Excel

尝试此函数传递要导出的数据表和文件路径

public void CreateCSVFile(ref DataTable dt, string strFilePath)
{
try
{
// Create the CSV file to which grid data will be exported.
StreamWriter sw = new StreamWriter(strFilePath, false);
// First we will write the headers.
//DataTable dt = m_dsProducts.Tables[0];
int iColCount = dt.Columns.Count;
for (int i = 0; i < iColCount; i++)
{
sw.Write(dt.Columns[i]);
if (i < iColCount - 1)
{
sw.Write(",");
}
}
sw.Write(sw.NewLine);


// Now write all the rows.


foreach (DataRow dr in dt.Rows)
{
for (int i = 0; i < iColCount; i++)
{
if (!Convert.IsDBNull(dr[i]))
{
sw.Write(dr[i].ToString());
}
if (i < iColCount - 1)
{
sw.Write(",");
}
}


sw.Write(sw.NewLine);
}
sw.Close();
}
catch (Exception ex)
{
throw ex;
}
}

我建议 ClosedXML-

You can turn a DataTable into an Excel worksheet with some 非常 readable code:

XLWorkbook wb = new XLWorkbook();
DataTable dt = GetDataTableOrWhatever();
wb.Worksheets.Add(dt,"WorksheetName");

开发人员反应迅速，乐于助人，项目也得到了积极的开发，文档也是一流的。

尝试使用简单的代码，将 DataTable 转换为以 csv 格式显示的 excel 文件:

var lines = new List<string>();


string[] columnNames = dataTable.Columns
.Cast<DataColumn>()
.Select(column => column.ColumnName)
.ToArray();


var header = string.Join(",", columnNames.Select(name => $"\"{name}\""));
lines.Add(header);


var valueLines = dataTable.AsEnumerable()
.Select(row => string.Join(",", row.ItemArray.Select(val => $"\"{val}\"")));


lines.AddRange(valueLines);


File.WriteAllLines("excel.csv", lines);

这将把一个新文件 excel.csv写入到 current working directory中，这通常是。Exe 是或者从哪里启动它。

一个优雅的选择是为.net 框架的 DataTable 类编写扩展方法(见下文)。

这种扩展方法可称为:

using System;
using System.Collections.Generic;
using System.Linq;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data;
using System.Data.OleDb;


DataTable dt;
// fill table data in dt here
...


// export DataTable to excel
// save excel file without ever making it visible if filepath is given
// don't save excel file, just make it visible if no filepath is given
dt.ExportToExcel(ExcelFilePath);

DataTable 类的扩展方法:

public static class My_DataTable_Extensions
{


// Export DataTable into an excel file with field names in the header line
// - Save excel file without ever making it visible if filepath is given
// - Don't save excel file, just make it visible if no filepath is given
public static void ExportToExcel(this DataTable tbl, string excelFilePath = null) {
try {
if (tbl == null || tbl.Columns.Count == 0)
throw new Exception("ExportToExcel: Null or empty input table!\n");


// load excel, and create a new workbook
var excelApp = new Excel.Application();
excelApp.Workbooks.Add();


// single worksheet
Excel._Worksheet workSheet = excelApp.ActiveSheet;


// column headings
for (var i = 0; i < tbl.Columns.Count; i++) {
workSheet.Cells[1, i + 1] = tbl.Columns[i].ColumnName;
}


// rows
for (var i = 0; i < tbl.Rows.Count; i++) {
// to do: format datetime values before printing
for (var j = 0; j < tbl.Columns.Count; j++) {
workSheet.Cells[i + 2, j + 1] = tbl.Rows[i][j];
}
}


// check file path
if (!string.IsNullOrEmpty(excelFilePath)) {
try {
workSheet.SaveAs(excelFilePath);
excelApp.Quit();
MessageBox.Show("Excel file saved!");
}
catch (Exception ex) {
throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"
+ ex.Message);
}
} else { // no file path is given
excelApp.Visible = true;
}
}
catch (Exception ex) {
throw new Exception("ExportToExcel: \n" + ex.Message);
}
}
}

解决方案基于 Tuncalik(感谢想法)的文章，但是在大表的情况下工作速度要快得多(而且不太清楚)。

public static class My_DataTable_Extensions
{
/// <summary>
/// Export DataTable to Excel file
/// </summary>
/// <param name="DataTable">Source DataTable</param>
/// <param name="ExcelFilePath">Path to result file name</param>
public static void ExportToExcel(this System.Data.DataTable DataTable, string ExcelFilePath = null)
{
try
{
int ColumnsCount;


if (DataTable == null || (ColumnsCount = DataTable.Columns.Count) == 0)
throw new Exception("ExportToExcel: Null or empty input table!\n");


// load excel, and create a new workbook
Microsoft.Office.Interop.Excel.Application Excel = new Microsoft.Office.Interop.Excel.Application();
Excel.Workbooks.Add();


// single worksheet
Microsoft.Office.Interop.Excel._Worksheet Worksheet = Excel.ActiveSheet;


object[] Header = new object[ColumnsCount];


// column headings
for (int i = 0; i < ColumnsCount; i++)
Header[i] = DataTable.Columns[i].ColumnName;


Microsoft.Office.Interop.Excel.Range HeaderRange = Worksheet.get_Range((Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[1, 1]), (Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[1, ColumnsCount]));
HeaderRange.Value = Header;
HeaderRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
HeaderRange.Font.Bold = true;


// DataCells
int RowsCount = DataTable.Rows.Count;
object[,] Cells = new object[RowsCount, ColumnsCount];


for (int j = 0; j < RowsCount; j++)
for (int i = 0; i < ColumnsCount; i++)
Cells[j, i] = DataTable.Rows[j][i];


Worksheet.get_Range((Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[2, 1]), (Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[RowsCount + 1, ColumnsCount])).Value = Cells;


// check fielpath
if (ExcelFilePath != null && ExcelFilePath != "")
{
try
{
Worksheet.SaveAs(ExcelFilePath);
Excel.Quit();
System.Windows.MessageBox.Show("Excel file saved!");
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"
+ ex.Message);
}
}
else    // no filepath is given
{
Excel.Visible = true;
}
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: \n" + ex.Message);
}
}
}

关于 Tuncalik的答案，这个答案很棒，特别是如果你想稍微修改一下代码的话:)但是它把我的日期以美国格式输入到 Excel 中，例如2014年3月2日在英国是02/03/2014，而在美国是03/02/2014月1日，然后是一周后的第二天。我需要它的英国格式，任何想法请？

我已经检查了它是以英国格式存储在我的 DataTable 中，而且我的 Excel 也被设置为英国，但是由于某种原因，当它制作 Excel 文档时，它认为它是美国(这是因为微软是一家美国公司:)

我将尝试用文化代码进行实验，但还不确定放在哪里。尝试过，但没有效果。

附言。

我不得不修改一行，通过添加如下的“强制类型转换”来使其正常工作

// single worksheet
Excel._Worksheet workSheet = (Excel._Worksheet)excelApp.ActiveSheet;

更新: 我已经通过转换到 LongDateTime 格式实现了日期的英国格式化，不过这只是一个解决方案。

DateTime startDate = Convert.ToDateTime(myList[0].ToString());
string strStartDate = startDate.ToLongDateString();
DateTime endDate = Convert.ToDateTime(myList[myListTotalRows].ToString());
string strEndDate = endDate.ToLongDateString();

干杯。

可以使用 EasyXLS作为导出 Excel 文件的库。

检查这个代码:

DataSet ds = new DataSet();
ds.Tables.Add(dataTable);


ExcelDocument xls = new ExcelDocument();
xls.easy_WriteXLSFile_FromDataSet("datatable.xls", ds,
new ExcelAutoFormat(Styles.AUTOFORMAT_EASYXLS1), "DataTable");

另请参阅关于如何使用 export datatable to excel in C#的示例。

旧线索，但我觉得应该把我的代码放在这里。我写了一个小函数，在指定的路径(位置)上写一个数据表到一个新的 Excel 表中。此外，您还需要添加一个对 Microsoft Excel 14.0库的引用。

我从这个线程上写任何东西，以 Excel-如何写一些数据到 Excel 文件(. xlsx)

i used that to extrapolate how to write a datatable

在 catch 语句中注意，我有一个 errorHandler 静态类引用(可以忽略它们)

 using excel = Microsoft.Office.Interop.Excel;
using System.IO;
using System.Data;
using System.Runtime.InteropServices;


//class and namespace wrapper is not shown in this example


private void WriteToExcel(System.Data.DataTable dt, string location)
{
//instantiate excel objects (application, workbook, worksheets)
excel.Application XlObj = new excel.Application();
XlObj.Visible = false;
excel._Workbook WbObj = (excel.Workbook)(XlObj.Workbooks.Add(""));
excel._Worksheet WsObj = (excel.Worksheet)WbObj.ActiveSheet;


//run through datatable and assign cells to values of datatable
try
{
int row = 1; int col = 1;
foreach (DataColumn column in dt.Columns)
{
//adding columns
WsObj.Cells[row, col] = column.ColumnName;
col++;
}
//reset column and row variables
col = 1;
row++;
for (int i = 0; i < dt.Rows.Count; i++)
{
//adding data
foreach (var cell in dt.Rows[i].ItemArray)
{
WsObj.Cells[row, col] = cell;
col++;
}
col = 1;
row++;
}
WbObj.SaveAs(location);
}
catch (COMException x)
{
ErrorHandler.Handle(x);
}
catch (Exception ex)
{
ErrorHandler.Handle(ex);
}
finally
{
WbObj.Close();
}
}

互操作:

此方法可防止日期从 dd-mm-yyyy 翻转到 mm-dd-yyyy

public bool DataTableToExcelFile(DataTable dt, string targetFile)
{
const bool dontSave = false;
bool success = true;


//Exit if there is no rows to export
if (dt.Rows.Count == 0) return false;


object misValue = System.Reflection.Missing.Value;
List<int> dateColIndex = new List<int>();
Excel.Application excelApp = new Excel.Application();
Excel.Workbook excelWorkBook = excelApp.Workbooks.Add(misValue);
Excel.Worksheet excelWorkSheet = excelWorkBook.Sheets("sheet1");


//Iterate through the DataTable and populate the Excel work sheet
try {
for (int i = -1; i <= dt.Rows.Count - 1; i++) {
for (int j = 0; j <= dt.Columns.Count - 1; j++) {
if (i < 0) {
//Take special care with Date columns
if (dt.Columns(j).DataType is typeof(DateTime)) {
excelWorkSheet.Cells(1, j + 1).EntireColumn.NumberFormat = "d-MMM-yyyy;@";
dateColIndex.Add(j);
}
//else if ... Feel free to add more Formats


else {
//Otherwise Format the column as text
excelWorkSheet.Cells(1, j + 1).EntireColumn.NumberFormat = "@";
}
excelWorkSheet.Cells(1, j + 1) = dt.Columns(j).Caption;
}
else if (dateColIndex.IndexOf(j) > -1) {
excelWorkSheet.Cells(i + 2, j + 1) = Convert.ToDateTime(dt.Rows(i).ItemArray(j)).ToString("d-MMM-yyyy");
}
else {
excelWorkSheet.Cells(i + 2, j + 1) = dt.Rows(i).ItemArray(j).ToString();
}
}
}


//Add Autofilters to the Excel work sheet
excelWorkSheet.Cells.AutoFilter(1, Type.Missing, Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true);
//Autofit columns for neatness
excelWorkSheet.Columns.AutoFit();
if (File.Exists(exportFile)) File.Delete(exportFile);
excelWorkSheet.SaveAs(exportFile);
} catch {
success = false;
} finally {
//Do this irrespective of whether there was an exception or not.
excelWorkBook.Close(dontSave);
excelApp.Quit();
releaseObject(excelWorkSheet);
releaseObject(excelWorkBook);
releaseObject(excelApp);
}
return success;
}

如果您不关心翻转日期，那么使用查看链接，该链接显示如何在一行代码中填充 Excel 电子表格中的所有单元格:

Excel Interop - Efficiency and performance

CSV:

public string DataTableToCSV(DataTable dt, bool includeHeader, string rowFilter, string sortFilter, bool useCommaDelimiter = false, bool truncateTimesFromDates = false)
{
dt.DefaultView.RowFilter = rowFilter;
dt.DefaultView.Sort = sortFilter;
DataView dv = dt.DefaultView;
string csv = DataTableToCSV(dv.ToTable, includeHeader, useCommaDelimiter, truncateTimesFromDates);
//reset the Filtering
dt.DefaultView.RowFilter = string.Empty;
return csv;
}


public string DataTableToCsv(DataTable dt, bool includeHeader, bool useCommaDelimiter = false, bool truncateTimesFromDates = false)
{
StringBuilder sb = new StringBuilder();
string delimter = Constants.vbTab;
if (useCommaDelimiter)
delimter = ",";


if (includeHeader) {
foreach (DataColumn dc in dt.Columns) {
sb.AppendFormat("{0}" + Constants.vbTab, dc.ColumnName);
}


//remove the last Tab
sb.Remove(sb.ToString.Length - 1, 1);
sb.Append(Environment.NewLine);
}


foreach (DataRow dr in dt.Rows) {
foreach (DataColumn dc in dt.Columns) {
if (Information.IsDate(dr(dc.ColumnName).ToString()) & dr(dc.ColumnName).ToString().Contains(".") == false & truncateTimesFromDates) {
sb.AppendFormat("{0}" + delimter, Convert.ToDateTime(dr(dc.ColumnName).ToString()).Date.ToShortDateString());
} else {
sb.AppendFormat("{0}" + delimter, CheckDBNull(dr(dc.ColumnName).ToString().Replace(",", "")));
}
}
//remove the last Tab
sb.Remove(sb.ToString.Length - 1, 1);
sb.Append(Environment.NewLine);
}
return sb.ToString;
}


public enum enumObjectType
{
StrType = 0,
IntType = 1,
DblType = 2
}


public object CheckDBNull(object obj, enumObjectType ObjectType = enumObjectType.StrType)
{
object objReturn = null;
objReturn = obj;
if (ObjectType == enumObjectType.StrType & Information.IsDBNull(obj)) {
objReturn = "";
} else if (ObjectType == enumObjectType.IntType & Information.IsDBNull(obj)) {
objReturn = 0;
} else if (ObjectType == enumObjectType.DblType & Information.IsDBNull(obj)) {
objReturn = 0.0;
}
return objReturn;
}

最好最简单的方法

private void exportToExcel(DataTable dt)
{


/*Set up work book, work sheets, and excel application*/
Microsoft.Office.Interop.Excel.Application oexcel = new Microsoft.Office.Interop.Excel.Application();
try
{
string path = AppDomain.CurrentDomain.BaseDirectory;
object misValue = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Workbook obook = oexcel.Workbooks.Add(misValue);
Microsoft.Office.Interop.Excel.Worksheet osheet = new Microsoft.Office.Interop.Excel.Worksheet();




//  obook.Worksheets.Add(misValue);


osheet = (Microsoft.Office.Interop.Excel.Worksheet)obook.Sheets["Sheet1"];
int colIndex = 0;
int rowIndex = 1;


foreach (DataColumn dc in dt.Columns)
{
colIndex++;
osheet.Cells[1, colIndex] = dc.ColumnName;
}
foreach (DataRow dr in dt.Rows)
{
rowIndex++;
colIndex = 0;


foreach (DataColumn dc in dt.Columns)
{
colIndex++;
osheet.Cells[rowIndex, colIndex] = dr[dc.ColumnName];
}
}


osheet.Columns.AutoFit();
string filepath = "C:\\Temp\\Book1";


//Release and terminate excel


obook.SaveAs(filepath);
obook.Close();
oexcel.Quit();
releaseObject(osheet);


releaseObject(obook);


releaseObject(oexcel);
GC.Collect();
}
catch (Exception ex)
{
oexcel.Quit();
log.AddToErrorLog(ex, this.Name);
}
}

这样做的一种方法是使用 ACEOLEDB 提供程序(也参见 connection strings for Excel)。当然，您必须安装和注册提供程序。如果你已经安装了 Excel，你应该拥有它，但是这是你在部署应用程序时必须考虑的事情。

这是从 ExportHelper: ExportHelper.CreateXlsFromDataTable(myDataTable, @"C:\tmp\export.xls");调用 helper 方法的示例

使用 ACEOLEDB 导出到 Excel 文件的帮助器:

public class ExportHelper
{
private const string ExcelOleDbConnectionStringTemplate = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES\";";


/// <summary>
/// Creates the Excel file from items in DataTable and writes them to specified output file.
/// </summary>
public static void CreateXlsFromDataTable(DataTable dataTable, string fullFilePath)
{
string createTableWithHeaderScript = GenerateCreateTableCommand(dataTable);


using (var conn = new OleDbConnection(String.Format(ExcelOleDbConnectionStringTemplate, fullFilePath)))
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}


OleDbCommand cmd = new OleDbCommand(createTableWithHeaderScript, conn);
cmd.ExecuteNonQuery();


foreach (DataRow dataExportRow in dataTable.Rows)
{
AddNewRow(conn, dataExportRow);
}
}
}


private static void AddNewRow(OleDbConnection conn, DataRow dataRow)
{
string insertCmd = GenerateInsertRowCommand(dataRow);


using (OleDbCommand cmd = new OleDbCommand(insertCmd, conn))
{
AddParametersWithValue(cmd, dataRow);
cmd.ExecuteNonQuery();
}
}


/// <summary>
/// Generates the insert row command.
/// </summary>
private static string GenerateInsertRowCommand(DataRow dataRow)
{
var stringBuilder = new StringBuilder();
var columns = dataRow.Table.Columns.Cast<DataColumn>().ToList();
var columnNamesCommaSeparated = string.Join(",", columns.Select(x => x.Caption));
var questionmarkCommaSeparated = string.Join(",", columns.Select(x => "?"));


stringBuilder.AppendFormat("INSERT INTO [{0}] (", dataRow.Table.TableName);
stringBuilder.Append(columnNamesCommaSeparated);
stringBuilder.Append(") VALUES(");
stringBuilder.Append(questionmarkCommaSeparated);
stringBuilder.Append(")");
return stringBuilder.ToString();
}


/// <summary>
/// Adds the parameters with value.
/// </summary>
private static void AddParametersWithValue(OleDbCommand cmd, DataRow dataRow)
{
var paramNumber = 1;


for (int i = 0; i <= dataRow.Table.Columns.Count - 1; i++)
{
if (!ReferenceEquals(dataRow.Table.Columns[i].DataType, typeof(int)) && !ReferenceEquals(dataRow.Table.Columns[i].DataType, typeof(decimal)))
{
cmd.Parameters.AddWithValue("@p" + paramNumber, dataRow[i].ToString().Replace("'", "''"));
}
else
{
object value = GetParameterValue(dataRow[i]);
OleDbParameter parameter = cmd.Parameters.AddWithValue("@p" + paramNumber, value);
if (value is decimal)
{
parameter.OleDbType = OleDbType.Currency;
}
}


paramNumber = paramNumber + 1;
}
}


/// <summary>
/// Gets the formatted value for the OleDbParameter.
/// </summary>
private static object GetParameterValue(object value)
{
if (value is string)
{
return value.ToString().Replace("'", "''");
}
return value;
}


private static string GenerateCreateTableCommand(DataTable tableDefination)
{
StringBuilder stringBuilder = new StringBuilder();
bool firstcol = true;


stringBuilder.AppendFormat("CREATE TABLE [{0}] (", tableDefination.TableName);


foreach (DataColumn tableColumn in tableDefination.Columns)
{
if (!firstcol)
{
stringBuilder.Append(", ");
}
firstcol = false;


string columnDataType = "CHAR(255)";


switch (tableColumn.DataType.Name)
{
case "String":
columnDataType = "CHAR(255)";
break;
case "Int32":
columnDataType = "INTEGER";
break;
case "Decimal":
// Use currency instead of decimal because of bug described at
// http://social.msdn.microsoft.com/Forums/vstudio/en-US/5d6248a5-ef00-4f46-be9d-853207656bcc/localization-trouble-with-oledbparameter-and-decimal?forum=csharpgeneral
columnDataType = "CURRENCY";
break;
}


stringBuilder.AppendFormat("{0} {1}", tableColumn.ColumnName, columnDataType);
}
stringBuilder.Append(")");


return stringBuilder.ToString();
}
}

使用以下类

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using excel = Microsoft.Office.Interop.Excel;
using EL = ExcelLibrary.SpreadSheet;
using System.Drawing;
using System.Collections;
using System.Runtime.InteropServices;
using System.Windows.Forms;




namespace _basic
{
public class ExcelProcesser
{
public void WriteToExcel(System.Data.DataTable dt)
{
excel.Application XlObj = new excel.Application();
XlObj.Visible = false;
excel._Workbook WbObj = (excel.Workbook)(XlObj.Workbooks.Add(""));
excel._Worksheet WsObj = (excel.Worksheet)WbObj.ActiveSheet;
object misValue = System.Reflection.Missing.Value;




try
{
int row = 1; int col = 1;
foreach (DataColumn column in dt.Columns)
{
//adding columns
WsObj.Cells[row, col] = column.ColumnName;
col++;
}
//reset column and row variables
col = 1;
row++;
for (int i = 0; i < dt.Rows.Count; i++)
{
//adding data
foreach (var cell in dt.Rows[i].ItemArray)
{
WsObj.Cells[row, col] = cell;
col++;
}
col = 1;
row++;
}
WbObj.SaveAs(fileFullName, excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
WbObj.Close(true, misValue, misValue);
}
}
}

}

这个解决方案基本上是把 List<Object>数据推到 Excel，它使用 DataTable 来实现这一点，我实现了一个扩展方法，所以基本上有两件事情 需要。 1. 一种可拓方法。

public static class ReportHelper
{
public static string ToExcel<T>(this IList<T> data)
{
PropertyDescriptorCollection properties =
TypeDescriptor.GetProperties(typeof(T));
DataTable table = new DataTable();
foreach (PropertyDescriptor prop in properties)
{
//table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
if (prop.Attributes[typeof(FGMS.Entity.Extensions.ReportHeaderAttribute)] != null)
{
table.Columns.Add(GetColumnHeader(prop), Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
}
}


//So it seems like when there is only one row of data the headers do not appear
//so adding a dummy blank row which fixed the issues
//Add a blank Row - Issue # 1471
DataRow blankRow = table.NewRow();
table.Rows.Add(blankRow);


foreach (T item in data)
{
DataRow row = table.NewRow();
foreach (PropertyDescriptor prop in properties)
//row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
if (prop.Attributes[typeof(FGMS.Entity.Extensions.ReportHeaderAttribute)] != null)
{
row[GetColumnHeader(prop)] = prop.GetValue(item) ?? DBNull.Value;
}
table.Rows.Add(row);
}
table.TableName = "Results";
var filePath = System.IO.Path.GetTempPath() + "\\" + System.Guid.NewGuid().ToString() + ".xls";
table.WriteXml(filePath);


return filePath;
}


private static string GetColumnHeader(PropertyDescriptor prop)
{
return ((FGMS.Entity.Extensions.ReportHeaderAttribute)(prop.Attributes[typeof(FGMS.Entity.Extensions.ReportHeaderAttribute)])).ReportHeaderText;
}
}

2. 用属性 [ReportHeaderAttribute("Column Name")]装饰 DTO 类

public class UserDTO
{
public int Id { get; set; }
public int SourceId { get; set; }
public string SourceName { get; set; }


[ReportHeaderAttribute("User Type")]
public string UsereType { get; set; }


[ReportHeaderAttribute("Address")]
public string Address{ get; set; }


[ReportHeaderAttribute("Age")]
public int Age{ get; set; }


public bool IsActive { get; set; }


[ReportHeaderAttribute("Active")]
public string IsActiveString
{
get
{
return IsActive ? "Yes" : "No";
}
}}

Excel 中需要成为列的所有内容都必须用 [ReportHeaderAttribute("Column Name")]进行装饰

Then Simply

Var userList = Service.GetUsers() //Returns List of UserDTO;
var excelFilePath = userList.ToExcel();


HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK);
var stream = new FileStream(excelFilePath, FileMode.Open);
result.Content = new StreamContent(stream);
result.Content.Headers.ContentType =
new MediaTypeHeaderValue("application/vnd.ms-excel");
result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment") { FileName = "UserList.xls" };


return result;

若要将数据导出到 Excel，可以使用 Closed XMLReport 库(https://github.com/ClosedXML/ClosedXML.Report)。相信我，这是一个很棒的图书馆，对她来说很容易使用。库不需要 ExcelInterop。Closed XMLReport 基于可以使用任何格式在 Excel 中创建的模板生成 Excel 文件。例如:

    var template = new XLTemplate(@".\Templates\report.xlsx");


using (var db = new DbDemos())
{
var cust = db.customers.LoadWith(c => c.Orders).First();
template.AddVariable(cust);
template.Generate();
}


template.SaveAs(outputFile);

充分利用 CloseMXL.Excel库。 很简单，也很快。

同学们

private DataTable getAllList()
{
string constr = ConfigurationManager.ConnectionStrings["RConnection"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("SELECT EmpId, gender, EmpName, pOnHold FROM Employee  WHERE EmpId= '"+ AnyVariable + "' ORDER BY EmpName"))
{
using (SqlDataAdapter da = new SqlDataAdapter())
{
DataTable dt = new DataTable();
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
da.SelectCommand = cmd;
da.Fill(dt);
dt.Columns[0].ColumnName = "Employee Id";
dt.Columns[1].ColumnName = "Gender";
dt.Columns[2].ColumnName = "Employee Name";
dt.Columns[3].ColumnName = "On Hold";


return dt;
}
}
}
}

然后是另一种获取数据集的方法

public DataSet getDataSetExportToExcel()
{
DataSet ds = new DataSet();
DataTable dtEmp = new DataTable("CLOT List");
dtEmp = getAllList();
ds.Tables.Add(dtEmp);
ds.Tables[0].TableName = "Employee"; //If you which to use Mutliple Tabs
return ds;
}

现在您按钮点击事件

protected void btn_Export_Click(object sender, EventArgs e)
{
DataSet ds = getDataSetExportToExcel();


using (XLWorkbook wb = new XLWorkbook())
{
wb.Worksheets.Add(ds);
wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
wb.Style.Font.Bold = true;


Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename=EmployeeonHoldList.xlsx");


using (MemoryStream MyMemoryStream = new MemoryStream())
{
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(Response.OutputStream);


Response.Flush();
Response.End();
}
}
}

Private tmr As System.Windows.Forms.Timer


Private Sub TestExcel() Handles Button1.Click


'// Initial data: SQL Server table with 6 columns and 293000 rows.




'// Data table holding all data
Dim dt As New DataTable("F161")


'// Create connection
Dim conn As New SqlConnection("Server=MYSERVER;Database=Test;Trusted_Connection=Yes;")
Dim fAdapter As New SqlDataAdapter With
{
.SelectCommand = New SqlCommand($"SELECT * FROM dbo.MyTable", conn)
}


'// Fill DataTable
fAdapter.Fill(dt)


'// Create Excel application
Dim xlApp As New Excel.Application With {.Visible = True}


'// Temporarily disable screen updating
xlApp.ScreenUpdating = False


'// Create brand new workbook
Dim xlBook As Excel.Workbook = xlApp.Workbooks.Add()
Dim xlSheet As Excel.Worksheet = DirectCast(xlBook.Sheets(1), Excel.Worksheet)


'// Get number of rows
Dim rows_count = dt.Rows.Count
'// Get number of columns
Dim cols_count = dt.Columns.Count


'// Here 's the core idea: after receiving data
'// you need to create an array and transfer it to sheet.
'// Why array?
'// Because it's the fastest way to transfer data to Excel's sheet.
'// So, we have two tasks:
'// 1) Create array
'// 2) Transfer array to sheet


'// =========================================================
'// TASK 1: Create array
'// =========================================================
'// In order to create array, we need to know that
'// Excel's Range object expects 2-D array whose lower bounds
'// of both dimensions start from 1.
'// This means you can't use C# array.
'// You need to manually create such array.
'// Since we already calculated number of rows and columns,
'// we can use these numbers in creating array.
Dim arr = Array.CreateInstance(GetType(Object), {rows_count, cols_count}, {1, 1})


'// Fill array
For r = 0 To rows_count - 1
For c = 0 To cols_count - 1
arr(r + 1, c + 1) = dt.Rows(r)(c)
Next
Next


'// =========================================================
'// TASK 2: Transfer array to sheet
'// =========================================================
'// Now we need to transfer array to sheet.
'// So, how transfer array to sheet fast?
'//
'// THE FASTEST WAY TO TRANSFER DATA TO SHEET IS TO ASSIGN ARRAY TO RANGE.
'// We could, of course, hard-code values, but Resize property
'// makes this work a breeze:
xlSheet.Range("A1").Resize.Resize(rows_count, cols_count).Value = arr


'// If we decide to dump data by iterating over array,
'// it will take LOTS of time.
'// For r = 1 To rows_count
'//     For c = 1 To cols_count
'//         xlSheet.Cells(r, c) = arr(r, c)
'//     Next
'// Next


'// Here are time results:
'// 1) Assigning array to Range: 3 seconds
'// 2) Iterating over array: 45 minutes


'// Turn updating on
xlApp.ScreenUpdating = True
xlApp = Nothing
xlBook = Nothing
xlSheet = Nothing


'// Here we have another problem:
'// creating array took lots of memory (about 150 MB).
'// Using 'GC.Collect()', by unknown reason, doesn't help here.
'// However, if you run GC.Collect() AFTER this procedure is finished
'// (say, by pressing another button and calling another procedure),
'// then the memory is cleaned up.
'// I was wondering how to avoid creating some extra button to just release memory,
'// so I came up with the idea to use timer to call GC.
'// After 2 seconds GC collects all generations.
'// Do not forget to dispose timer since we need it only once.


tmr = New Timer()
AddHandler tmr.Tick,
Sub()
GC.Collect()
GC.WaitForPendingFinalizers()
GC.WaitForFullGCComplete()
tmr.Dispose()
End Sub
tmr.Interval = TimeSpan.FromSeconds(2).TotalMilliseconds()
tmr.Start()


End Sub

你可以使用我的 SwiftExcel库。当它将数据直接写入文件时，在性能和低内存使用方面表现得尤为出色:

using (var ew = new ExcelWriter("C:\\temp\\test.xlsx"))
{
for (var row = 1; row <= 100; row++)
{
for (var col = 1; col <= 10; col++)
{
ew.Write($"row:{row}-col:{col}", col, row);
}
}
}

要安装的 Nuget 命令:

Install-Package SwiftExcel

纯粹的示例代码(如果它可能帮助某人有一些想法) ，基于 Tomasz Wi Niewski的答案从这里: Https://stackoverflow.com/a/21079709/2717521

主窗口输出按钮:

public int counter;


public void SaveToExcel(object sender, RoutedEventArgs e)
{
counter = 1;
CountChecker();
}


public void CountChecker()
{
string filename = GlobalStrings.building_house_address;
string path = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\";




if (CurrentID != 0)
{
if (!File.Exists(path + filename + ".xlsx"))
{
DataGridParts.Export(path + filename);
MessageBoxEx.Show(this, "Shranjeno na namizje");
}
else
{
if (!File.Exists(path + "\\" + filename + " (" + (counter) + ")" + ".xlsx"))
{
DataGridParts.Export(path + filename + " (" + (counter) + ")");
MessageBoxEx.Show(this, "Shranjeno na namizje");
}
else
{
counter++;
CountChecker();
}
}
}
else
{
MessageBoxEx.Show(this, "Izbran ni noben naslov!");
}
}

ExportToExcel 类:

using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;


namespace CBUGIS
{
public static class ExportToExcel
{
/// <summary>
/// Export DataTable to Excel file
/// </summary>
/// <param name="DataTable">Source DataTable</param>
/// <param name="ExcelFilePath">Path to result file name</param>
public static void Export(this System.Data.DataTable DataTable, string ExcelFilePath = null)
{
int ColumnsCount;
int RowShift = 5;


ColumnsCount = DataTable.Columns.Count;


// load excel, and create a new workbook
Application Excel = new Application();
Excel.Workbooks.Add();


// single worksheet
_Worksheet Worksheet = Excel.ActiveSheet;
Excel.Sheets[1].Name = "CBUGIS";


Worksheet.Columns.NumberFormat = "@";
Worksheet.Columns.HorizontalAlignment = XlHAlign.xlHAlignLeft;


object[,] Title = new object[3, 1]; // Array Size


if (GlobalStrings.building_alterantive_addresses.Length == 0)
{
if (GlobalStrings.building_postcode.Length != 0)
{
Title[0, 0] = "NASLOV: " + GlobalStrings.building_house_street + " " + GlobalStrings.building_house_number + GlobalStrings.building_house_id + ", " + GlobalStrings.building_postcode + " " + GlobalStrings.building_area;
Title[1, 0] = "K.O.: " + GlobalStrings.building_cadastral_community + ", ŠT.STAVBE: " + GlobalStrings.building_building_number + ", ŠT.PARCELE: " + GlobalStrings.building_plot_number;
}
else
{
Title[0, 0] = "NASLOV: " + GlobalStrings.building_house_street + " " + GlobalStrings.building_house_number + GlobalStrings.building_house_id;
Title[1, 0] = "K.O.: " + GlobalStrings.building_cadastral_community + ", ŠT.STAVBE: " + GlobalStrings.building_building_number + ", " + GlobalStrings.building_plot_number;
}
}
else
{
if (GlobalStrings.building_postcode.Length != 0)
{
Title[0, 0] = "NASLOV: " + GlobalStrings.building_house_street + " " + GlobalStrings.building_house_number + GlobalStrings.building_house_id + ", " + GlobalStrings.building_postcode + " " + GlobalStrings.building_area;
Title[1, 0] = "K.O.: " + GlobalStrings.building_cadastral_community + ", ŠT.STAVBE: " + GlobalStrings.building_building_number + ", ŠT.PARCELE: " + GlobalStrings.building_plot_number;
Title[2, 0] = "GLEJ TUDI: " + GlobalStrings.building_alterantive_addresses;
}
else
{
Title[0, 0] = "NASLOV: " + GlobalStrings.building_house_street + " " + GlobalStrings.building_house_number + GlobalStrings.building_house_id;
Title[1, 0] = "K.O.: " + GlobalStrings.building_cadastral_community + ", ŠT.STAVBE: " + GlobalStrings.building_building_number + ", ŠT.PARCELE: " + GlobalStrings.building_plot_number;
Title[2, 0] = "GLEJ TUDI: " + GlobalStrings.building_alterantive_addresses;
}
}


Range TitleRange = Worksheet.get_Range((Range)(Worksheet.Cells[3, 1]), (Range)(Worksheet.Cells[1, 1]));
TitleRange.Value = Title;
TitleRange.Font.Bold = true;
TitleRange.Font.Size = 10;




object[] Header = new object[11]; // Number of Columns


Header[0] = "DEL";
Header[1] = "DELEŽ";
Header[2] = "CRP";
Header[3] = "LASTNIK";
Header[4] = "NASLOV";
Header[5] = "P.Š";
Header[6] = "OBMOČJE";
Header[7] = "DRŽAVA";
Header[8] = "EMŠO/MAT. ŠT.";
Header[9] = "OPIS";
Header[10] = "OPOMBA";


Range HeaderRange = Worksheet.get_Range((Range)(Worksheet.Cells[RowShift, 2]), (Range)(Worksheet.Cells[RowShift, 12]));
HeaderRange.Value = Header;
HeaderRange.Font.Bold = true;
HeaderRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);


// DataCells
int RowsCount = DataTable.Rows.Count;


object[,] Cells = new object[RowsCount, ColumnsCount];


for (int j = 0; j < RowsCount; j++)
for (int i = 0; i < ColumnsCount - 1; i++)
if (i > 1)
{
Cells[j, i - 2] = DataTable.Rows[j][i];
}


Range CellRange = Worksheet.get_Range((Range)(Worksheet.Cells[RowShift +1, 2]), (Range)(Worksheet.Cells[RowShift + RowsCount, 12]));
CellRange.Value = Cells;
CellRange.Borders.LineStyle = XlLineStyle.xlContinuous;


Worksheet.Columns.NumberFormat = "@";
Worksheet.Columns[1].ColumnWidth = 0.1;
for (int b = 1; b < 12; b++)
{
if (b > 1)
{
Worksheet.Columns[b].AutoFit();
}
}


Worksheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
Worksheet.PageSetup.TopMargin = 0.5;
Worksheet.PageSetup.BottomMargin = 0.5;
Worksheet.PageSetup.RightMargin = 0.5;
Worksheet.PageSetup.LeftMargin = 0.5;


// check fielpath
if (ExcelFilePath != null && ExcelFilePath != "")
{
Worksheet.SaveAs(ExcelFilePath);
Excel.Quit();
Marshal.FinalReleaseComObject(Worksheet);
Marshal.FinalReleaseComObject(TitleRange);
Marshal.FinalReleaseComObject(HeaderRange);
Marshal.FinalReleaseComObject(CellRange);
Marshal.FinalReleaseComObject(Excel);
}
else


// no filepath is given
{
Excel.Visible = true;
}
}
}
}

我之所以想要添加这个答案，是因为我花了大量时间寻找一种快速、可靠的方法来完成这项工作，而且在我能找到的任何地方，都没有为此目的使用 OpenXMLWriter 的完整示例。

首先，COM/Interop (其他许多应答使用的)在这方面是可以的，但它存在一些敏感性问题。我已经使用它几十年了，它基本上是稳定的，但是当为数百个用户实现一个数据仓库前端时，我发现它受到太多问题的影响，这取决于机器和用户做了什么，所以我转向了 OpenXML。OpenXMLDOM 在这方面相当不错，但是比使用 OpenXMLWriter 要慢。当您进入具有大量列的大型数据集(100K +)时，DOM 比 OpenXMLWriter 慢得多，所以我使用后者。下面的方法在不到30秒的时间内写入包含30个以上字段的420K + 行。

I hope the comments are sufficient to guide anyone through what it's doing. It is simplified, in that it writes all values to the file as strings, but you can implement logic to write various datatypes (and use various cell formats) based on the content of your data. You can also adapt this for use on a DataGridView (instead of a DataTable) by changing just a few things (namely the loops through columns/rows).

需要对 DocumentFormat. OpenXML (带有 OpenXML SDK 的 d/l)和 WindowsBase 的引用。

Imports DocumentFormat.OpenXml
Imports DocumentFormat.OpenXml.Spreadsheet
Imports DocumentFormat.OpenXml.Packaging


Public Sub ExportToExcelXML(ByRef dt As DataTable, filename As String)
Dim wbp As WorkbookPart, wsp As WorksheetPart
'If this DataTable has more rows in it than can fit in Excel, throw an exception
If dt.Rows.Count > 1048575 Then Throw New Exception("The DataTable is too large to export to Excel.")
'Delete any previous file of the same name that may exist.
File.Delete(filename)
'Create an OpenXML SpreadsheetDocument...
Using xls = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook)
'Add a WorkbookPart to the Spreadsheet Doc, then add a WorksheetPart to the WorkbookPart.
wbp = xls.AddWorkbookPart()
wsp = wbp.AddNewPart(Of WorksheetPart)
'Now we need to add the "StyleSheet" to the WorkbookPart (that we just added above). This will allow us to apply formatting to our Cells.
'Add the WbStylesPart and the StyleSheet.
Dim stp As WorkbookStylesPart = wbp.AddNewPart(Of WorkbookStylesPart)
Dim ss As New Stylesheet
'Create the only two Fonts we're going to use (Regular and Bold).
Dim fBold As New Font
fBold.Append(New Bold)
Dim fnts As New Fonts
fnts.Append(New Font) 'This creates the default (unmodified, regular) Font. It's added first, so its index is 0.
fnts.Append(fBold) 'This creates the Bold font. It's added second, so its index is 1.
'Create the default Fill/Border settings (these have to be here, even though I don't set any custom fills/borders).
Dim flls As New Fills
Dim brdrs As New Borders
flls.Append(New Fill)
brdrs.Append(New Border)
'Now I have to add formats (NumberFormat and CellFormat). First, you create a NumberFormat. This is basically the pattern of
'   the format (i.e. "@" for Text). For now, I only need a Text format, but I can add more patterns if needed.
'   I give the format an ID of 164, since 163 is where the built-in Excel formats end.
Dim nbrfmts As New NumberingFormats
nbrfmts.Append(New NumberingFormat With {.NumberFormatId = 164, .FormatCode = "@"})
'Create the first two CellFormats: Default, which will have an index of 0 and "Header" (Bold/Centered) with an index of 1.
Dim cellfmts As New CellFormats()
cellfmts.Append(New CellFormat() With {.FontId = 0, .NumberFormatId = 164, .FillId = 0, .BorderId = 0})
cellfmts.Append(New CellFormat() With {.FontId = 1, .NumberFormatId = 164,
.Alignment = New Alignment() With {.WrapText = True, .Horizontal = HorizontalAlignmentValues.Center}})
'Add all of the Fonts/Fills/Borders/etc to the StyleSheet and add it all to the WorkbookStylesPart.
ss.Append(fnts)
ss.Append(flls)
ss.Append(brdrs)
ss.Append(cellfmts)
ss.NumberingFormats = nbrfmts
stp.Stylesheet = ss
stp.Stylesheet.Save()
'Now create an OpenXMLWriter using the WorksheetPart to write the cells to the worksheet.
Using oxw As OpenXmlWriter = OpenXmlWriter.Create(wsp)
'Write the start element for the Worksheet and the Columns...
oxw.WriteStartElement(New Worksheet)
oxw.WriteStartElement(New Columns())
'Now I'm going to loop through the columns in the DataTable...
For c As Integer = 0 To dt.Columns.Count - 1
'Now we'll get the width for the column. To do this, we loop through all of the rows and measure the width of the text
'   using the default Excel Font (currently Font: Calibri Size: 11) and return the largest width (in pixels) to use below.
'   Why not do this loop below (when I loop through the rows to write the Cells)? Because you can't. You have to
'   write the Column XML first before writing the SheetData/Row/Cell XML (I confirmed this by trying it), so there's
'   no way (that I'm aware of) to avoid looping through all of the rows twice if you want to AutoFit.
'Setup vars we'll use for getting the column widths (below).
Dim g = System.Drawing.Graphics.FromHwnd(IntPtr.Zero)
Dim fnt = New System.Drawing.Font("Calibri", 11)
Dim wid As Double = 0
'Get the width of the header (because if this is wider than the widest value, we'll use the header text's width).
'   I found that adding 2 pixels to the width was necessary to get the column as wide as Excel would make it.
Dim tmp As Double = g.MeasureString(dt.Columns(c).ColumnName, New System.Drawing.Font(fnt, System.Drawing.FontStyle.Bold)).Width + 2
'Loop through the rows in the dt and get the width of the value in that row/col. If it's wider than the widest
'   width we've encountered thus far, use the new wider width as our basis.
For Each row As DataRow In dt.Rows
If tmp > wid Then wid = tmp
tmp = g.MeasureString(row(c).ToString, fnt).Width
Next
'Set the column attributes and write it to the file. The Width is set using a formula that converts from pixels to Excel's column width values.
Dim oxa As New List(Of OpenXmlAttribute) From {New OpenXmlAttribute("min", Nothing, c + 1), New OpenXmlAttribute("max", Nothing, c + 1),
New OpenXmlAttribute("width", Nothing, System.Math.Round((wid - 12 + 5) / 7D + 1, 2))}
oxw.WriteStartElement(New Column(), oxa)
oxw.WriteEndElement()
Next
'CLose out the Columns collection.
oxw.WriteEndElement()
'Write the start element for the SheetData...
oxw.WriteStartElement(New SheetData)
'Write the start element for the Header row.
oxw.WriteStartElement(New Row)
'Loop through the Columns in the dt.
For Each col As DataColumn In dt.Columns
'Write a cell for this column's Header. All Header cells are written with a DataType of String ("str").
'   I ALSO apply the "Header" CellFormat (StyleIndex 1) to all of the Header Cells. This makes them Bold and Centered.
WriteCell(oxw, col.ColumnName, "str", 1)
Next
'Close out the Header row.
oxw.WriteEndElement()
'Loop through all of the rows in the dt...
For Each row As DataRow In dt.Rows
'Write a StartElement for this row...
oxw.WriteStartElement(New Row)
'Loop through all of the columns in the dt...
For c As Integer = 0 To dt.Columns.Count - 1
'Write a value in this row/column to the Excel file. I use the datatype of "String" and the default CellFormat/StyleIndex.
WriteCell(oxw, row(c).ToString, "str", 0)
Next
'Close out this row.
oxw.WriteEndElement()
Next
'Close out the Worksheet and SheetData elements...
oxw.WriteEndElement()
oxw.WriteEndElement()
End Using
'Now we're going to create an OpenXMLWriter using the WorkbookPart (that we created above)...
Using oxw As OpenXmlWriter = OpenXmlWriter.Create(wbp)
'Add starting elements for the Workbook and Sheets collection.
oxw.WriteStartElement(New Workbook())
oxw.WriteStartElement(New Sheets())
'Add the Sheet (name the Sheet after the file name minus the extension).
oxw.WriteElement(New Sheet() With {.Name = Path.GetFileNameWithoutExtension(filename), .SheetId = 1, .Id = xls.WorkbookPart.GetIdOfPart(wsp)})
'Write End elements for the Workbook/Sheets
oxw.WriteEndElement()
oxw.WriteEndElement()
End Using
End Using


End Sub


'This Sub is used to write a value to a Cell using OpenXMLWriter.
Private Sub WriteCell(ByRef oxw As OpenXmlWriter, value As String, datatype As String, style As UInt32Value)
Dim oxa As New List(Of OpenXmlAttribute) From {New OpenXmlAttribute("t", Nothing, datatype), New OpenXmlAttribute("s", Nothing, style)}
oxw.WriteStartElement(New Cell(), oxa)
If value <> Nothing Then oxw.WriteElement(New CellValue(value))
oxw.WriteEndElement()
End Sub

与 EPPlus NuGet 软件包，很简单。

public class TestObject
{
public int Col1 { get; set; }
public int Col2 { get; set; }
public string Col3 { get; set; }
public DateTime Col4 { get; set; }
}


[TestMethod]
public void LoadFromCollection_MemberList_Test()
{
//https://stackoverflow.com/questions/32587834/epplus-loadfromcollection-text-converted-to-number/32590626#32590626


var TestObjectList = new List<TestObject>();
for (var i = 0; i < 10; i++)
TestObjectList.Add(new TestObject {Col1 = i, Col2 = i*10, Col3 = (i*10) + "E4"});


//Create a test file
var fi = new FileInfo(@"c:\temp\LoadFromCollection_MemberList_Test.xlsx");
if (fi.Exists)
fi.Delete();


using (var pck = new ExcelPackage(fi))
{
//Do NOT include Col1
var mi = typeof (TestObject)
.GetProperties()
.Where(pi => pi.Name != "Col1")
.Select(pi => (MemberInfo)pi)
.ToArray();


var worksheet = pck.Workbook.Worksheets.Add("Sheet1");
worksheet.Cells.LoadFromCollection(
TestObjectList
, true
, TableStyles.Dark1
, BindingFlags.Public| BindingFlags.Instance
, mi);


pck.Save();
}
}

注意，输出中没有 Col1:

使用 DocumentFormat.OpenXml nuget 包，我创建了一个单例类，它处理从 DataTable或 DataSet的出口。表将在工作簿中表示为单独的工作表。

主要班级

public sealed class OfficeOpenXML
{
private static Lazy<OfficeOpenXML> _instance = new Lazy<OfficeOpenXML>(() => new OfficeOpenXML());
private OfficeOpenXML()
{


}
public static OfficeOpenXML GetInstance()
{
return _instance.Value;
}


public MemoryStream GetExcelStream(DataSet ds, bool firstRowAsHeader = false)
{
if (ds == null || ds.Tables.Count == 0)
{
return null;
}


MemoryStream stream = new MemoryStream();
using (var excel = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
{
//create doc and workbook
WorkbookPart workbookPart = excel.AddWorkbookPart();
Workbook workbook = new Workbook();
Sheets sheets = new Sheets();
//loop all tables in the dataset
for (int iTable = 0; iTable < ds.Tables.Count; iTable++)
{
var table = ds.Tables[iTable];
//create sheet part
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
Worksheet worksheet = new Worksheet();
SheetData data = new SheetData();
List<Row> allRows = new List<Row>();


//setting header of the sheet
Row headerRow = new Row() { RowIndex = 1 };
for (int iColumn = 0; iColumn < table.Columns.Count; iColumn++)
{
var col = table.Columns[iColumn];
//if first row of table is not the header then set columns of table as header of sheet
if (!firstRowAsHeader)
{
headerRow.Append(new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(col.ColumnName)
});
}
else
{
headerRow.Append(new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(Convert.ToString(table.Rows[0][col]))
});
}
}
allRows.Add(headerRow);


//setting other data rows
if (table.Rows != null && table.Rows.Count != 0)
{
for (int iRow = firstRowAsHeader ? 1 : 0; iRow < table.Rows.Count; iRow++)
{
var row = table.Rows[iRow];
Row valueRow = new Row { RowIndex = (uint)(iRow + (firstRowAsHeader ? 1 : 2)) };


for (int iColumn = 0; iColumn < table.Columns.Count; iColumn++)
{
var col = table.Columns[iColumn];
valueRow.Append(new Cell
{
DataType = Format(col.DataType),
CellValue = new CellValue(Convert.ToString(row[col]))
});
}
allRows.Add(valueRow);
}
}


//add rows to the data
data.Append(allRows);
worksheet.Append(data);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();


//add worksheet to main sheets
sheets.Append(new Sheet
{
Name = string.IsNullOrWhiteSpace(table.TableName) ? "Sheet" + (iTable + 1) : table.TableName,
Id = workbookPart.GetIdOfPart(worksheetPart),
SheetId = (uint)iTable + 1
});
}//single table processing ends here


//add created sheets to workbook
workbook.Append(sheets);
excel.WorkbookPart.Workbook = workbook;
excel.WorkbookPart.Workbook.Save();




excel.Close();
}
stream.Seek(0, SeekOrigin.Begin);
stream.Capacity = (int)stream.Length;
return stream;




}
public MemoryStream GetExcelStream(DataTable dt, bool firstRowAsHeader = false)
{
DataSet ds = new DataSet();
ds.Tables.Add(dt);
return GetExcelStream(ds, firstRowAsHeader);
}






#region Excel Helpers


CellValues Format(Type t)
{


switch (t.ToString())
{


case "System.String":
return CellValues.String;
case "System.DateTime":
return CellValues.Date;
case "System.Boolean":
return CellValues.Boolean;
case "System.Int16":
return CellValues.Number;
case "System.Int32":
return CellValues.Number;
case "System.Int64":
return CellValues.Number;
case "System.UInt16":
return CellValues.Number;
case "System.UInt32":
return CellValues.Number;
case "System.UInt64":
return CellValues.Number;
case "System.Decimal":
return CellValues.Number;
case "System.Double":
return CellValues.Number;
case "System.Single":
return CellValues.Number;
default:
return CellValues.String;
}
}
#endregion
}

保存到文件

        var excelApp = OfficeOpenXML.GetInstance();
var dt = GetDataTableFromDB();
using (var stream = excelApp.GetExcelStream(dt, false))//use true to hide datatable columns from excel
{


using (FileStream fs = new FileStream(@"C:\Users\Public\myexcel.xlsx", FileMode.Create))
{
stream.CopyTo(fs);
fs.Flush();
}
}

在 MVC 应用程序中下载

public ActionResult DownloadReport()
{
var ds = GetDataSetFromDB();
var excelApp = OfficeOpenXML.GetInstance();
var file = excelApp.GetExcelStream(ds, false);
return File(file, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", Guid.NewGuid().ToString() + ".xlsx");
}

这里有一些聪明的回答(Tomasz Wi niewski，cuongle)。它们似乎分为两类，要么需要一个包装器/插件库，要么最终的数据写入需要逐行逐格地循环写入。如果使用后者，性能将会很慢，而且对于将 DataTable 导出到 Excel 这样的简单任务来说，前者可能不那么方便。我是这样做的，使用一个复制/粘贴速度更快(测试时800行为3毫秒) ，但我们必须稍微作弊。您需要做的是动态创建一个隐藏的 Form，其中包含 DataGridView。然后，当您将 DataTable 转储为 DataSource 时，可以调用 DataGridView 类的“ GetClipboardContent”方法，最后只需将其放置在 Excel 表中选定的 Range/Cell 中。示例代码:

private bool FncCopyDataTableToWorksheet(DataTable dt, ExcelUsing.Worksheet destWsh)
{
Form frm = new Form();
frm.Size = new Size(0, 0);
DataGridView dgv = new DataGridView();
DataObject obj;
ExcelUsing.Range rng;
int j = 0;


frm.Controls.Add(dgv);


bool result = false;


try
{
dgv.DataSource = dt;
dgv.RowHeadersVisible = false;
frm.Show();
dgv.SelectAll();
obj = dgv.GetClipboardContent();
frm.Hide();
dgv.ClearSelection();


if (obj != null)
{
Clipboard.SetDataObject(obj);


//Write Headers
foreach (DataGridViewColumn dgvc in dgv.Columns)
{
if (dgvc.Visible == true)
{
rng = (ExcelUsing.Range)destWsh.Cells[1, 1 + j];
rng.Value2 = dgvc.Name.ToString();
j++;
}
}


rng = (ExcelUsing.Range)destWsh.Cells[2, 1];
rng.Select();
destWsh.PasteSpecial(rng, _Missing, _Missing, _Missing, _Missing, _Missing, true);


result = true;


}
else { result = false; }
}
catch (Exception ex)
{
//Handle
}


rng = null;
obj = null;
dgv = null;
frm = null;


return result;
}

The modest way to export excel is using Microsoft CloseXml pakage. I wrote a simple function for exporting my query result as excel sheet (I wrote it for but can be used in windows application with minor changes):

using ClosedXML.Excel;
...
public ActionResult ToExcel(List<Dictionary<string, string>> data, Dictionary<string, string> columnMap, string fileName, string sheetName)
{
var dtDataBuffer = new System.Data.DataTable("buffer");


foreach (string col in columnMap.Values)
{
dtDataBuffer.Columns.Add(col, typeof(string));
}






foreach (var row in data)
{
List<string> rowData = new List<string> { };




foreach (string col in columnMap.Keys)
{
rowData.Add(row[col]);
}


dtDataBuffer.Rows.Add(rowData.ToArray());
}








var memoryStream = new MemoryStream();


using (var workbook = new XLWorkbook())
{
var worksheet = workbook.Worksheets.Add(dtDataBuffer, sheetName);
worksheet.Rows().Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Rows().Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
worksheet.Columns().AdjustToContents();
workbook.SaveAs(memoryStream);
}


return File(memoryStream.ToArray(), "application/vnd.ms-excel", fileName);
}

And this is a usage example (in practice I use my own class to run query and generate data. You can find it here for 神使 and SQL Server):

public ActionResult myReportExport(){
var data=List<Dictionary<string, string>>(){
\{\{"Column1_Index","Column1_Value"},{"Column2_Index","Column2_Value"},...}
...
};
    

    

return ToExcel(data, new Dictionary<string, string> {
{ "Column1_Index", "Column1 Title" } ,
{ "Column2_Index", "Column2 Title" } ,
...
},
"myFileName.xlsx",
"my sheet name"
);
}