如何在C#中将DataTable
导出到Excel?我正在使用Windows窗体。DataTable
与DataGridView
控件关联。我必须将DataTable
的记录导出到Excel。
发布于 2012-12-20 21:29:35
一个不错的选择是为.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);
}
}
}
发布于 2011-11-21 18:27:52
尝试此函数,将数据表和文件路径传递到要导出的位置
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;
}
}
发布于 2015-08-27 03:59:34
最好最简单的方法
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);
}
}
https://stackoverflow.com/questions/8207869
复制相似问题