问如何将DataTable导出到Excel

EN

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);

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);
        }
    }
}

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;
    }
}

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);
        }
    }