c#将dataTable中的数据导出到Excel表中

开发中，经常会遇到将数据导出到Excel中的需求，下面简单介绍将dataTable中的数据导出到Excel中。

方法：

第一，添加引用：

第二，代码实现，建类：

using System;
using System.Collections;
using System.Text;
using System.Data;
using System.Threading;
using System.Windows.Forms;
using System.Collections.Generic;
using Excel = Microsoft.Office.Interop.Excel;

public class ExportToExcel
{
    public Excel.Application m_xlApp = null;

    public void OutputAsExcelFile(DataTable dtTable, String numStr)
    {
        string filePath = "";
        SaveFileDialog s = new SaveFileDialog();
        s.Title = "保存Excel文件";
        s.Filter = "Excel文件(*.xls)|*.xls";
        s.FilterIndex = 1;
        if (s.ShowDialog() == DialogResult.OK)
            filePath = s.FileName;
        else
            return;
        //导出dataTable到Excel  
        dtTable.Columns.Add("原因");
        long rowNum = dtTable.Rows.Count;//行数  
        int columnNum = dtTable.Columns.Count;//列数 
        String[] numArr = numStr.Split(',');
        Excel.Application m_xlApp = new Excel.Application();
        m_xlApp.DisplayAlerts = false;//不显示更改提示  
        m_xlApp.Visible = false;

        Excel.Workbooks workbooks = m_xlApp.Workbooks;
        Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
        Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1  

        try
        {
            string[,] datas = new string[rowNum + 1, columnNum];
            for (int i = 0; i < columnNum; i++) //写入字段  
                datas[0, i] = dtTable.Columns[i].Caption;
            //Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]);  
            Excel.Range range = m_xlApp.Range[worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]];
            range.Interior.ColorIndex = 15;//15代表灰色  
            range.Font.Bold = true;
            range.Font.Size = 10;

            int r = 0;
            for (r = 0; r < numArr.Length - 1; r++)
            {
                int numRow = int.Parse(numArr[r]) - 1;
                for (int i = 0; i < columnNum; i++)
                {
                    object obj;
                    if (i == columnNum - 1)
                    {
                        obj = "价格格式不符合要求。";
                    }
                    else {
                        obj = dtTable.Rows[numRow][dtTable.Columns[i].ToString()];
                    }
                    datas[r + 1, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式
                    Console.WriteLine(datas[r + 1,i]);
                }
                System.Windows.Forms.Application.DoEvents();
                //添加进度条  
            }
            //Excel.Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]);  
            Excel.Range fchR = m_xlApp.Range[worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]];
            fchR.Value2 = datas;

            worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。  
            //worksheet.Name = "dd";  

            //m_xlApp.WindowState = Excel.XlWindowState.xlMaximized;  
            m_xlApp.Visible = false;

            // = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]);  
            range = m_xlApp.Range[worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]];

            //range.Interior.ColorIndex = 15;//15代表灰色  
            range.Font.Size = 9;
            range.RowHeight = 14.25;
            range.Borders.LineStyle = 1;
            range.HorizontalAlignment = 1;
            workbook.Saved = true;
            workbook.SaveCopyAs(filePath);
        }
        catch (Exception ex)
        {
            MessageBox.Show("导出异常：" + ex.Message, "导出异常", MessageBoxButtons.OK, MessageBoxIcon.Warning);
        }
        finally
        {
            EndReport();
        }

        m_xlApp.Workbooks.Close();
        m_xlApp.Workbooks.Application.Quit();
        m_xlApp.Application.Quit();
        m_xlApp.Quit();
        MessageBox.Show("导出成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
    private void EndReport()
    {
        object missing = System.Reflection.Missing.Value;
        try
        {}
        catch { }
        finally
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp.Workbooks);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp.Application);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp);
                m_xlApp = null;
            }
            catch { }
            try
            {
                //清理垃圾进程  
                this.killProcessThread();
            }
            catch { }
            GC.Collect();
        }
    }

    private void killProcessThread()
    {
        ArrayList myProcess = new ArrayList();
        for (int i = 0; i < myProcess.Count; i++)
        {
            try
            {
                System.Diagnostics.Process.GetProcessById(int.Parse((string)myProcess[i])).Kill();
            }
            catch { }
        }
    }
}

第三，调用：

 ExportToExcel dtTable = new ExportToExcel();
 dtTable.OutputAsExcelFile(dt, str);