程序导入Excel的三种方式

第一种OLEDB方式（若sheet名关联文件名，则打开会失败）

 public static DataSet ImportExcel(string v_FilePath)
 {
   try
   {
     string strCon;
     strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + v_FilePath + "; Extended Properties='Excel 8.0; HDR=No; IMEX=1'";
     OleDbConnection olecon = new OleDbConnection(strCon);

     olecon.Open();

     System.Data.DataTable dtSheetName = olecon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });

     olecon.Close();

     string sheetName = "SELECT * FROM [" + dtSheetName.Rows[0]["TABLE_NAME"].ToString() + "]";
     OleDbDataAdapter myda = new OleDbDataAdapter(sheetName, strCon);
     DataSet myds = new DataSet();
     myda.Fill(myds);
     return myds;
    }
    catch (Exception )
    {
       return null;
    }
 }

第二种COM组件方式（速度慢)

 public static System.Data.DataTable ImportExcelToDataTable(string v_FilePath,ref string v_strErr)
 {
   try
   {
      //创建EXCEL对象
      Microsoft.Office.Interop.Excel.Application objExcel = new Microsoft.Office.Interop.Excel.Application();

      //打开一个工作薄
     Workbook objBook = objExcel.Workbooks.Open(v_FilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
   Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

     //打开一个工作表
     Worksheet objSheet = (Worksheet)objBook.Worksheets.get_Item(1);

     System.Data.DataTable dt = new System.Data.DataTable();

     Range range;

     for (int i = 0; i < objSheet.UsedRange.Columns.Count; i++)
     {
        dt.Columns.Add();
     }

     for (int i = 1; i <= objSheet.UsedRange.Rows.Count; i++)
     {
       try
       {
         DataRow dr = dt.NewRow();
         for (int j = 1; j <= objSheet.UsedRange.Columns.Count; j++)
         {
           try
           {
              range = (Range)objSheet.Cells[i, j];
              dr[j - 1] = (range.Value2 == null) ? "" : range.Text.ToString();
           }
           catch (Exception err)
           {
             v_strErr = err.ToString();
           }
         }
         dt.Rows.Add(dr);
       }
       catch (Exception err)
       {
          v_strErr = err.ToString();
       }
                   
     }
     objExcel.Workbooks.Close();
     return dt;
   }
   catch (Exception err)
   {
      v_strErr += err.ToString();
      return null;
   }
}

第三种NPOI方式（速度快，分为xls和xlsx格式）

public static System.Data.DataTable ImportExcelToDataTableByNPOI(string v_FilePath, ref string v_strErr)
        {
            IWorkbook wk = null;
            string extension = System.IO.Path.GetExtension(v_FilePath);
            try
            {
                FileStream fs = File.OpenRead(v_FilePath);
                if (extension.Equals(".xls"))
                {
                    //把xls文件中的数据写入wk中
                    wk = new HSSFWorkbook(fs);
                }
                else
                {
                    //把xlsx文件中的数据写入wk中
                    wk = new XSSFWorkbook(fs);
                }

                fs.Close();
                //读取当前表数据
                ISheet sheet = wk.GetSheetAt(0);

                System.Data.DataTable dtResultTable = new System.Data.DataTable();

                IRow TitleRow = sheet.GetRow(0);
                for (int i = 0; i < TitleRow.LastCellNum; i++)
                {
                    dtResultTable.Columns.Add(TitleRow.GetCell(i).ToString());
                }

                IRow row = sheet.GetRow(0);  //读取当前行数据
             
                //LastRowNum 是当前表的总行数-1（注意）
                for (int i = 0; i <= sheet.LastRowNum; i++)
                {
                    row = sheet.GetRow(i);  //读取当前行数据

                    if (row != null)
                    {
                        DataRow newRow = dtResultTable.NewRow();

                        //LastCellNum 是当前行的总列数！
                        for (int j = 0; j < row.LastCellNum; j++)
                        {
                            //读取该行的第j列数据
                            if (row.GetCell(j) != null)
                            {
                                string value = row.GetCell(j).ToString();
                                newRow[j] = value;
                            }

                        }
                        dtResultTable.Rows.Add(newRow);
                    }
                }
                return dtResultTable;
            }
            catch (Exception err)
            {
                v_strErr += err.ToString();
               
                return null;
            }
        }