c#读取Excel表数据

1，使用OLEDB可以对excel文件进行读取
.
.

private DataSet ReadExcelToDataSet(string path)
        {
            //连接字符串
            /* 备注：
            	添加 IMEX=1 表示将所有列当做字符串读取，实际应该不是这样，
            	系统默认会查看前8行如果有字符串，则该列会识别为字符串列。
            	如果前8行都是数字，则还是会识别为数字列，日期也一样；
            	如果你觉得8行不够或者太多了，则只能修改注册表HKEY_LOCAL_MACHINE/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows，
            	如果此值为0，则会根据所有行来判断使用什么类型，通常不建议这麽做，除非你的数据量确实比较少
            */
            string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 8.0;IMEX=1';";
			
            using (OleDbConnection conn = new OleDbConnection(connstring))
            {
                conn.Open();
                DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });//存放所有的sheet
                DataSet set = new DataSet();
                for (int i = 0; i < sheetsName.Rows.Count; i++)
                {
                    string sheetName = sheetsName.Rows[i][2].ToString();
                    string sql = string.Format("SELECT * FROM [{0}]", sheetName);
                    OleDbDataAdapter ada = new OleDbDataAdapter(sql, connstring);

                    ada.Fill(set);
                    set.Tables[i].TableName = sheetName;
                }

                return set;

            }
        }

.
.
返回的dataset 为：

.

从图中可以看出把每个sheet都存成了一个table。
所用时间为：

.
.
.
2 ，使用Office自带的库
前提是本机须安装office才能运行，且不同的office版本之间可能会有兼容问题，从Nuget下载 Microsoft.Office.Interop.Excel

  public System.Data.DataSet GetExcelData(string excelFilePath)
        {
            Excel.Application app = new Excel.Application();
            Excel.Sheets sheets;
            Excel.Workbook workbook = null;
            object oMissiong = System.Reflection.Missing.Value;
            
            DataSet dataSet = new DataSet();
            string cellContent;
            try
            {
                if (app == null)
                {
                    return null;
                }
                workbook = app.Workbooks.Open(excelFilePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong,
                    oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);
               
                sheets = workbook.Worksheets;
                for (int p = 1; p <= sheets.Count; p++)
                {
                    System.Data.DataTable dt = new System.Data.DataTable();
                    Excel.Worksheet workSheet = (Excel.Worksheet)sheets.get_Item(p);//读取第一张表
                    
                    for (int j = 1; j <= workSheet.UsedRange.Columns.Count; j++)
                    {

                        Range _range = (Excel.Range)workSheet.Cells[1, j];
                        if (_range.Text.ToString().Trim() == "")
                            dt.Columns.Add("EQUIPMENT");
                        else
                            dt.Columns.Add(_range.Text.ToString().Trim());

                    }
                    for (int i = 2; i <= workSheet.UsedRange.Rows.Count; i++)
                    {
                        DataRow dr = dt.NewRow();
                        for (int j = 1; j <= workSheet.UsedRange.Columns.Count; j++)
                        {
                            Range _range = (Excel.Range)workSheet.Cells[i, j];
                            cellContent = (_range.Value2 == null) ? "" : _range.Text.ToString().Trim();
                            dr[j - 1] = cellContent;
                        }
                        dt.Rows.Add(dr);
                    }
                    dataSet.Tables.Add(dt);
                }                
            }
            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();
            }
            return dataSet;
        }

.
.

• 所用时间：2分+ ，速度感人，不知道是我代码的问题，还是Excel.Application这种方式本来就慢，欢迎大佬指教。

.
.

.
.
.

3，利用ExcelDataReader 读取excel文件

• 它分为两个部分，一个是ExcelDataReader ，ExcelDataReader.DataSet 。可以在Nuget安装

.
.

• 代码部分：
  .

private DataSet ReadExcelToDataSet(string fileNmaePath)
        {         
            FileStream stream = null;
            IExcelDataReader excelReader = null;
            DataSet dataSet = null;
            try
            {
                //stream = File.Open(fileNmaePath, FileMode.Open, FileAccess.Read);
                stream= new FileStream(fileNmaePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
            }
            catch
            {              
                return null;
            }
            string extension = Path.GetExtension(fileNmaePath);

            if (extension.ToUpper() == ".XLS")
            {
                excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
            }
            else if (extension.ToUpper() == ".XLSX")
            {
                excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
            }
            else
            {
                MessageBox.Show("格式错误");
                return null;

            }
            //dataSet = excelReader.AsDataSet();//第一行当作数据读取
            dataSet = excelReader.AsDataSet(new ExcelDataSetConfiguration()
            {
                ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
                {
                    UseHeaderRow = true
                }
            });//第一行当作列名读取
            excelReader.Close();          
            return dataSet;
        }

.

• 返回的dataset为：

• 所用时间：