程序导入Excel的三种方式

来源：互联网发布：供应链金融软件系统编辑：程序博客网时间：2024/06/14 11:30

第一种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;            }        }

0 0