C# Com读取Excel数据

using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading;
using System.Windows.Forms;

namespace SYS_TEST.BaseClass
{
    //Com组件的方式
    //通过添加 Microsoft.Office.Interop.Excel引用实现
    //优点：能够非常灵活的读取Excel中的数据，用户可以灵活的调用各种函数进行处理。
    //缺点：基于单元格的处理，读取速度较慢，对于数据量较大的文件最好不要使用此种方式读取。
    //需要添加相应的DLL引用，必须存在此引用才可使用，如果是Web站点部署在IIS上时，还需要服务器机子已安装了Excel，有时候还需要为配置IIS权限。
    public class COMClass
    {
        /// <summary>
        /// Excel取其中部分单元格数据
        /// </summary>
        /// <param name="fileName"></param>
        public static void GetExcelData(string fileName)
        {
            object missing = System.Reflection.Missing.Value;
            Excel.Application excel = new Excel.Application();
            if (excel == null)
            {
                MessageBox.Show("Can't access excel");
                return;
            }
            else
            {
                excel.Visible = false;
                excel.UserControl = true;
                //以只读的形式打开EXCEL文件
                Excel.Workbook wb = excel.Application.Workbooks.Open(fileName, missing, true, missing, missing, missing,
                 missing, missing, missing, true, missing, missing, missing, missing, missing);
                //取得第一个工作薄
                Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets.get_Item(1);
                //取得总记录行数(包括标题列)
                int rowsint = ws.UsedRange.Cells.Rows.Count;//得到行数
                int columnsint = ws.UsedRange.Cells.Columns.Count;//得到列数
                //取得数据范围区域 (不包括标题列) 
                Excel.Range rng1 = ws.Cells.get_Range("B2", "B" + rowsint);//item
                Excel.Range rng2 = ws.Cells.get_Range("K2", "K" + rowsint);//Customer
                object[,] arryItem = (object[,])rng1.Value2;
                object[,] arryCus = (object[,])rng2.Value2;
                //将新值赋给一个数组
                string[,] arry = new string[rowsint - 1, 2];
                for (int i = 1; i <= rowsint - 1; i++)
                {
                    //Item_Code列
                    arry[i - 1, 0] = arryItem[i, 1].ToString();
                    //Customer_Name列
                    arry[i - 1, 1] = arryCus[i, 1].ToString();
                }
            }
            excel.Quit();
            excel = null;
            Process[] procs = Process.GetProcessesByName("excel");
            foreach (Process pro in procs)
            {
                pro.Kill();//没有更好的方法,只有杀掉进程
            }
            GC.Collect();
        }

        private static Stopwatch wath = new Stopwatch();
        /// <summary>
        /// 使用COM进行Excel导入
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public static DataTable ExcelToDataTableByCom(string fileName)
        {
            Excel.Application app = new Excel.Application();
            Excel.Sheets sheets;
            Excel.Workbook workbook = null;
            object oMissiong = System.Reflection.Missing.Value;
            DataTable dt = new DataTable();
            wath.Start();
            try
            {
                if (app == null)
                {
                    return null;
                }
                workbook = app.Workbooks.Open(fileName, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong,
                    oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);
                //将数据读入到DataTable中——Start   
                sheets = workbook.Worksheets;
                Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);//读取第一张表
                if (worksheet == null)
                    return null;
                string cellContent;
                int iRowCount = worksheet.UsedRange.Rows.Count;
                int iColCount = worksheet.UsedRange.Columns.Count;
                Excel.Range range;
                //负责列头Start
                DataColumn dc;
                int ColumnID = 1;
                range = (Excel.Range)worksheet.Cells[1, 1];
                while (range.Text.ToString().Trim() != "")
                {
                    dc = new DataColumn();
                    dc.DataType = System.Type.GetType("System.String");
                    dc.ColumnName = range.Text.ToString().Trim();
                    dt.Columns.Add(dc);

                    range = (Excel.Range)worksheet.Cells[1, ++ColumnID];
                }
                //End
                for (int iRow = 2; iRow <= iRowCount; iRow++)
                {
                    DataRow dr = dt.NewRow();
                    for (int iCol = 1; iCol <= iColCount; iCol++)
                    {
                        range = (Excel.Range)worksheet.Cells[iRow, iCol];
                        cellContent = (range.Value2 == null) ? "" : range.Text.ToString();
                        dr[iCol - 1] = cellContent;
                    }
                    dt.Rows.Add(dr);
                }
                wath.Stop();
                TimeSpan ts = wath.Elapsed;
                //将数据读入到DataTable中——End
                return dt;
            }
            catch
            {
                return null;
            }
            finally
            {
                workbook.Close(false, oMissiong, oMissiong);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                workbook = null;
                app.Workbooks.Close();
                app.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
                app = null;
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
        }
        /// <summary>
        /// 使用COM进行Excel导入（多线程）
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public DataTable ThreadReadExcel(string fileName)
        {
            Excel.Application app = new Excel.Application();
            Excel.Sheets sheets = null;
            Excel.Workbook workbook = null;
            object oMissiong = System.Reflection.Missing.Value;
            System.Data.DataTable dt = new System.Data.DataTable();
            wath.Start();
            try
            {
                if (app == null)
                {
                    return null;
                }
                workbook = app.Workbooks.Open(fileName, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong,
                    oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);
                //将数据读入到DataTable中——Start   
                sheets = workbook.Worksheets;
                Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);//读取第一张表
                if (worksheet == null)
                    return null;
                string cellContent;
                int iRowCount = worksheet.UsedRange.Rows.Count;
                int iColCount = worksheet.UsedRange.Columns.Count;
                Excel.Range range;
                //负责列头Start
                DataColumn dc;
                int ColumnID = 1;
                range = (Excel.Range)worksheet.Cells[1, 1];
                while (iColCount >= ColumnID)
                {
                    dc = new DataColumn();
                    dc.DataType = System.Type.GetType("System.String");
                    string strNewColumnName = range.Text.ToString().Trim();
                    if (strNewColumnName.Length == 0) strNewColumnName = "_1";
                    //判断列名是否重复
                    for (int i = 1; i < ColumnID; i++)
                    {
                        if (dt.Columns[i - 1].ColumnName == strNewColumnName)
                            strNewColumnName = strNewColumnName + "_1";
                    }
                    dc.ColumnName = strNewColumnName;
                    dt.Columns.Add(dc);
                    range = (Excel.Range)worksheet.Cells[1, ++ColumnID];
                }
                //End
                //数据大于500条，使用多进程进行读取数据
                if (iRowCount - 1 > 500)
                {
                    //开始多线程读取数据
                    //新建线程
                    int b2 = (iRowCount - 1) / 10;
                    DataTable dt1 = new DataTable("dt1");
                    dt1 = dt.Clone();
                    //SheetOptions应该是额外的类型方法（此处未提供）
                    //SheetOptions sheet1thread = new SheetOptions(worksheet, iColCount, 2, b2 + 1, dt1);
                    //Thread othread1 = new Thread(new ThreadStart(sheet1thread.SheetToDataTable));
                    //othread1.Start();
                    阻塞 1 毫秒，保证第一个读取 dt1
                    //Thread.Sleep(1);
                    //DataTable dt2 = new DataTable("dt2");
                    //dt2 = dt.Clone();
                    //SheetOptions sheet2thread = new SheetOptions(worksheet, iColCount, b2 + 2, b2 * 2 + 1, dt2);
                    //Thread othread2 = new Thread(new ThreadStart(sheet2thread.SheetToDataTable));
                    //othread2.Start();
                    //DataTable dt3 = new DataTable("dt3");
                    //dt3 = dt.Clone();
                    //SheetOptions sheet3thread = new SheetOptions(worksheet, iColCount, b2 * 2 + 2, b2 * 3 + 1, dt3);
                    //Thread othread3 = new Thread(new ThreadStart(sheet3thread.SheetToDataTable));
                    //othread3.Start();
                    //DataTable dt4 = new DataTable("dt4");
                    //dt4 = dt.Clone();
                    //SheetOptions sheet4thread = new SheetOptions(worksheet, iColCount, b2 * 3 + 2, b2 * 4 + 1, dt4);
                    //Thread othread4 = new Thread(new ThreadStart(sheet4thread.SheetToDataTable));
                    //othread4.Start();
                    主线程读取剩余数据
                    //for (int iRow = b2 * 4 + 2; iRow <= iRowCount; iRow++)
                    //{
                    //    DataRow dr = dt.NewRow();
                    //    for (int iCol = 1; iCol <= iColCount; iCol++)
                    //    {
                    //        range = (Excel.Range)worksheet.Cells[iRow, iCol];
                    //        cellContent = (range.Value2 == null) ? "" : range.Text.ToString();
                    //        dr[iCol - 1] = cellContent;
                    //    }
                    //    dt.Rows.Add(dr);
                    //}
                    //othread1.Join();
                    //othread2.Join();
                    //othread3.Join();
                    //othread4.Join();
                    将多个线程读取出来的数据追加至 dt1 后面
                    //foreach (DataRow dr in dt.Rows)
                    //    dt1.Rows.Add(dr.ItemArray);
                    //dt.Clear();
                    //dt.Dispose();
                    //foreach (DataRow dr in dt2.Rows)
                    //    dt1.Rows.Add(dr.ItemArray);
                    //dt2.Clear();
                    //dt2.Dispose();
                    //foreach (DataRow dr in dt3.Rows)
                    //    dt1.Rows.Add(dr.ItemArray);
                    //dt3.Clear();
                    //dt3.Dispose();
                    //foreach (DataRow dr in dt4.Rows)
                    //    dt1.Rows.Add(dr.ItemArray);
                    //dt4.Clear();
                    //dt4.Dispose();
                    //return dt1;
                }
                else
                {
                    for (int iRow = 2; iRow <= iRowCount; iRow++)
                    {
                        DataRow dr = dt.NewRow();
                        for (int iCol = 1; iCol <= iColCount; iCol++)
                        {
                            range = (Excel.Range)worksheet.Cells[iRow, iCol];
                            cellContent = (range.Value2 == null) ? "" : range.Text.ToString();
                            dr[iCol - 1] = cellContent;
                        }
                        dt.Rows.Add(dr);
                    }
                }
                wath.Stop();
                TimeSpan ts = wath.Elapsed;
                //将数据读入到DataTable中——End
                return dt;
            }
            catch
            {
                return null;
            }
            finally
            {
                workbook.Close(false, oMissiong, oMissiong);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
                workbook = null;
                app.Workbooks.Close();
                app.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
                app = null;
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
        }
    }
}