读excel文件至DataTable

     今天是我注册博客园的日子，不知道写什么才好．就写下工作中遇到的事情．
　　在.net中读excel文件的代码网上是多之又多，当初在网上搜索了一段，用的是OLEDB方法，我也不例外，但客户多次向我公司反映，导入的数据，明明是有值的，但显示为空．说不能识别文本文字，只能识别数字类型．测试了又测试，在连接字符串中加上＂Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended　Properties=\"Excel 8.0;HDR=YES;IMEX=1＂，说是可以解决混合类型．但是，实际上，还是不能解决问题．后来，我发现，如果，第一行第一列的数据是数字类型，第二行第一列的数据是文本类型，这时，就会显示不出第二行第一列的数据，它是一个空值．若是第一行第一列的数据是文本类型，第二行第一列的数据是数字类型，将会正常显示．后来，我又发现，改HDR=ＮＯ;这时，不管是数字类型还是文本类型，都能正常读出．但是它第一行显示的格式是这样的：Ｆ１，Ｆ２．．第二行显示的才是列名．
　　　最后，网上很多说用 Excel.Application读，但是速度很慢，为了能读得出数据，不丢失数据，只好先用这个方法了．．
代码如下：

using System;
using System.Collections.Generic;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;
using System.Data;
namespace Baolida.Shared.Common
{
    public class ExcelOperate
    {
        [DllImport("User32.dll", CharSet = CharSet.Auto)]
        public static extern int GetWindowThreadProcessId(IntPtr hwnd, out   int ID);
        public System.Data.DataTable ReadExcelData(string path)
        {

            Excel.Application exc = null;
            Workbooks workBooks = null;
            Workbook workBook = null;
            Worksheet workSheet = null;
            string strValue = "";
            Excel.Range r = null;
            System.Data.DataTable dt = new System.Data.DataTable();
            DataRow myRow;
            object oMissing = System.Reflection.Missing.Value;
            try
            {

                exc = new Excel.Application();
                exc.UserControl = true;
                exc.Application.Workbooks.Open(path, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
                workBooks = exc.Workbooks;
                workBook = workBooks[1];
                workSheet = (Worksheet)workBook.Worksheets[1];

                int colCount = workSheet.UsedRange.Columns.Count; //获得列数
                int rowCount = workSheet.UsedRange.Rows.Count; //获得行数

                //获取字段名称
                for (int k = 1; k <= colCount; k++)
                {
                    r = (Excel.Range)workSheet.Cells[1, k];
                   
                    strValue = r.Text.ToString().Trim();
                    dt.Columns.Add(strValue, System.Type.GetType("System.String"));
                }

                //获取内容
                for (int i = 2; i <= rowCount; i++)
                {

                    myRow = dt.NewRow();
                    for (int j = 1; j <= colCount; j++)
                    {

                        //取excel单元格中的值
                        r = (Excel.Range)workSheet.Cells[i, j];
                        strValue = r.Text.ToString().Trim();
                        myRow[j - 1] = strValue;
                    }
               
                    dt.Rows.Add(myRow);
                }

                //关闭当前的excel进程
                exc.Application.Workbooks.Close();
                exc.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(exc);
                exc = null;
                IntPtr t = new IntPtr(exc.Hwnd);
                int kid = 0;
                GetWindowThreadProcessId(t, out kid);
                System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(kid);
                p.Kill();
            }
            catch { }
            return dt;
        }

    }
}