excel导入c# datatable方法收集

excel导入c# datatable方法收集


excel To Datatable 实现思路

将excel 导入进DataTable 的思路为
通过插件的方式读取excel文件,并将excel文件遍历放入dataTable中。


excel To Datatable 实现方式一

利用ole对象读取excel对象


  public DataTable excelToDataTable(string path)  
        {  
            DataTable tb = new DataTable();  
	    //调用   Microsoft.ACE.OLEDB.12.0 链接对象 生成 读取excel文件链接
            string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";  
  
            //申明ole对象
            OleDbConnection conn = new OleDbConnection(strConn);  
            try  
            {  
                conn.Open();   
                //获取Excel中所有的sheet  
                DataTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            }  
            catch (Exception)  
            {   
                throw;  
            }  
            finally  
            {  
                if (conn.State!=ConnectionState.Closed)  
                {  
                    conn.Close();  
                }  
            }
	    return tb;
        }  


excel To Datatable 实现方式二

利用NPOI读取excel文件

 public DataTable ExcelSheetImportToDataTable(string filePath, string sheetName,int startRow)
   {
            DataTable dt = new DataTable();
            List listColumns = new List();  
            //1 验证文件必须为  xls后缀 
            if (!filePath.Contains(".xls")) return null; //文件类型验证失败
         
                using (FileStream f = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    IWorkbook wk = new HSSFWorkbook(f);
                    ISheet sheet = wk.GetSheet(sheetName);
                    IRow headerRow = sheet.GetRow(startRow);                   
                    #region  生成 表头 
                    for (int j = 0; j < headerRow.LastCellNum; j++)
                    {
                        //生成列名
                        ICell cell = headerRow.GetCell(j);
                        if (cell != null)
                        {
                            if (cell.StringCellValue != "")
                            {

                                dt.Columns.Add(cell.StringCellValue); //生成列 
                                listColumns.Add(j);
                            }
                        }
                    }
                    #endregion
                     

                    #region 生成数据
                    for (int i = startRow + 1; i < sheet.PhysicalNumberOfRows; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row.Cells[0].StringCellValue == "") break;
                        #region 插入行数据
                        DataRow dr = dt.NewRow();
                        for (int ii = 0; ii < listColumns.Count; ii++)
                        {
                            switch (row.Cells[listColumns[ii]].CellType)
                            {
                                case CellType.STRING:
                                    dr[ii] = row.Cells[listColumns[ii]].StringCellValue;
                                    break;
                                case CellType.BOOLEAN:
                                    dr[ii] = row.Cells[listColumns[ii]].BooleanCellValue;
                                    break;
                                case CellType.NUMERIC:
                                    dr[ii] = row.Cells[listColumns[ii]].NumericCellValue;
                                    break;
                                default:
                                    dr[ii] = "0";
                                    break;
                            }
                        }
                        dt.Rows.Add(dr); //插入excel行数据

                        #endregion
                    }
                    #endregion
                }            

            return dt;
        }