C#读写操作Excel文件，Excel进程资源回收代码

using System;
using System.IO;
using System.Text;
using System.Data;
using System.Reflection;
using System.Diagnostics;
using System.Collections;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data.Odbc;
using System.Collections.Generic;
using Microsoft.Office.Interop.Excel;
using System.Web;
using System.Data.OleDb;
//using cfg = System.Configuration;
namespace WX.Common
{
    /// <summary>
    /// Excel数据操作类
    /// </summary>
    public class ExcelHelper
    {
        //定义变量的缺省值
        private static object mValue = System.Reflection.Missing.Value;

        /// <summary>
        /// 导出CVS文件数据
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="path"></param>
        /// <returns></returns>
        public static DataSet ConnectCSVFile(string fileName, string path)
        {
            string strConn = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=";
            strConn += path;
            strConn += ";Extensions=asc,csv,tab,txt;HDR=Yes;Persist Security Info=False";
            OdbcConnection objConn = new OdbcConnection(strConn);
            DataSet ds = new DataSet();
            try
            {
                string strSql = "select * from " + fileName;  //fileName, For example: 1.csv
                OdbcDataAdapter odbcCSVDataAdapter = new OdbcDataAdapter(strSql, objConn);
                odbcCSVDataAdapter.Fill(ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                System.GC.Collect();
            }

            return ds;
        }

        /// <summary>
        /// 导出Excel文件
        /// </summary>
        /// <param name="filepath"></param>
        /// <param name="ExcelName"></param>
        /// <returns></returns>
        /// IMEX=1将强制混合数据转换为文本，HDR=NO将第一行作为内容，由于第一行Header都是文本，因此所有列的类型都将转换成文本。

        public static DataSet ExcelToDataTable(string filepath, string sheetName)
        {
            string strConn = string.Empty;
            string type = filepath.Substring(filepath.LastIndexOf('.') + 1, filepath.Length - filepath.LastIndexOf('.') - 1);
            if (type == "xlsx")
            {
                strConn = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + filepath + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";
            }
            else
            {
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;IMEX=1;HDR=YES'";
            }
            System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strConn);
            string strCom = "SELECT * FROM " + "[" + sheetName.Replace('#', '.').Replace("$", "").Replace("'","") + "$]";//读取Excel文件内容
            Conn.Open();
            System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn);
            DataSet ds = new DataSet();
            myCommand.Fill(ds, sheetName);
            Conn.Close();
            return ds;

        }

        /// <summary>
        /// 导入Excel文件
        /// </summary>
        /// <param name="tmpDataTable"></param>
        /// <param name="strFileName"></param>
        public static void DataTabletoExcel(System.Data.DataTable tmpDataTable, string strFileName)
        {
            //检查进程       
            List<Process> excelProcesses = GetExcelProcesses();
            if (excelProcesses.Count > 0)
            {
                //杀死进程
                KillTheExcel();
            }

            Excel.Application xlApp = new Excel.Application();
            Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
            try
            {
                if (tmpDataTable == null)
                    return;
                int rowNum = tmpDataTable.Rows.Count;
                int columnNum = tmpDataTable.Columns.Count;
                int rowIndex = 1;//如果需要导出列名，设置为1，否则设置为0
                int columnIndex = 0;

                xlApp.DefaultFilePath = "";
                xlApp.Visible = false;
                xlApp.DisplayAlerts = false;
                xlApp.SheetsInNewWorkbook = 1;

                Excel.Worksheet ExcelSheet = (Worksheet)xlBook.Worksheets[1];
                ExcelOperate operate = new ExcelOperate();
                operate.SetBold(ExcelSheet, ExcelSheet.Cells[1, 1], ExcelSheet.Cells[1, columnNum]);
                operate.SetHAlignCenter(ExcelSheet, ExcelSheet.Cells[1, 1], ExcelSheet.Cells[rowNum + 1, columnNum]);//居中
                ExcelSheet.Columns.EntireRow.AutoFit();
                ExcelSheet.Rows.EntireColumn.AutoFit();
                //将DataTable的列名导入Excel表第一行(如果需要可以加上)
                foreach (DataColumn dc in tmpDataTable.Columns)
                {
                    columnIndex++;
                    xlApp.Cells[rowIndex, columnIndex] = dc.ColumnName;
                }

//将DataTable中的数据导入Excel中
                for (int i = 0; i < rowNum; i++)
                {
                    rowIndex++;
                    columnIndex = 0;
                    for (int j = 0; j < columnNum; j++)
                    {
                        columnIndex++;
                        xlApp.Cells[rowIndex, columnIndex] = tmpDataTable.Rows[i][j].ToString();
                    }
                }

                //保存
                xlBook.SaveAs(strFileName, mValue, mValue, mValue, mValue, mValue, Excel.XlSaveAsAccessMode.xlShared
, mValue, mValue, mValue, mValue, mValue);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                xlApp.Workbooks.Close();
                //关闭进程，自动保存
                xlApp.Quit();
                System.GC.Collect();
            }
        }

        public static string ExcelSheetName(string filepath)
        {
            string al = string.Empty;
            string strConn;
            string type = filepath.Substring(filepath.LastIndexOf('.') + 1, filepath.Length - filepath.LastIndexOf('.') - 1);
            if (type == "xlsx")
            {
                strConn = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + filepath + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";
            }
            else
            {
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;IMEX=1;HDR=YES'";
            }
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            System.Data.DataTable sheetNames = conn.GetOleDbSchemaTable
            (System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            conn.Close();
            if (sheetNames.Rows.Count > 0)
            {
                al = sheetNames.Rows[0][2].ToString();
                if (!string.IsNullOrEmpty(al))
                {
                    al = al.Substring(0, al.Length - 1);
                }
            }
            //foreach (DataRow dr in sheetNames.Rows)
            //{
            //    al = dr[2].ToString();
            //    if (!string.IsNullOrEmpty(al))
            //    {
            //        al = al.Substring(0, al.Length - 1);
            //    }
            //}
            return al;
        }

        public void ExportExcel(System.Data.DataTable dt, StreamWriter w)
        {
            try
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    w.Write(dt.Columns[i]);
                    w.Write(' ');
                }
                w.Write(" ");

                object[] values = new object[dt.Columns.Count];
                foreach (DataRow dr in dt.Rows)
                {
                    values = dr.ItemArray;
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        w.Write(values[i]);
                        w.Write(' ');
                    }
                    w.Write(" ");
                }
                w.Flush();
                w.Close();
            }
            catch
            {
                w.Close();
            }
        }

        /// <summary>
        /// 获得进程
        /// </summary>
        /// <returns></returns>
        private static List<Process> GetExcelProcesses()
        {
            //获得当前计算机的进程列表
            Process[] processes = Process.GetProcesses();
            //保存Excel进程
            List<Process> ListProcess = new List<Process>();

            foreach (Process _pr in processes)
            {
                if (_pr.ProcessName.ToUpper().Equals("EXCEL"))
                {
                    ListProcess.Add(_pr);
                }
            }
            return ListProcess;
        }

        /// <summary>
        /// 销毁所有Excel进程
        /// </summary>
        public static void KillTheExcel()
        {
            List<Process> listProcess = GetExcelProcesses();
            foreach (Process _pr in listProcess)
            {
                _pr.Kill();
            }
        }

/// <summary>
        /// 释放内存
        /// </summary>
        public void Dispose(Excel._Worksheet CurSheet, Excel._Workbook CurBook, Excel._Application CurExcel)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(CurSheet);
                CurSheet = null;
                CurBook.Close(false, Missing.Value, Missing.Value);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(CurBook);
                CurBook = null;

                CurExcel.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(CurExcel);
                CurExcel = null;

                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
            catch (System.Exception ex)
            {
                HttpContext.Current.Response.Write("在释放Excel内存空间时发生了一个错误:" + ex);
            }
            finally
            {
                foreach (System.Diagnostics.Process pro in System.Diagnostics.Process.GetProcessesByName("Excel"))
                    pro.Kill();
            }
            System.GC.SuppressFinalize(this);
        }
    }
}