把excel数据导入到datatable

//把excel数据导出到datatable            文件全路径
        public DataTable importExcelToDataSet(string FilePath)
        {
            #region
            /*
             *  DataTable myDt = new DataTable();
             * string strConn;
              FileInfo file = new FileInfo(FilePath);
              if (!file.Exists)
              {
                  throw new Exception("文件不存在");
              }
              string extension = file.Extension;
              switch (extension)
              {
                  case ".xls":
                      strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties='Excel 8.0;'";
                      break;
                  case ".xlsx":
                      strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
                      break;
                  default:
                      strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
                      break;
              }
              // strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FilePath + ";Extended Properties=Excel 8.0;";
              OleDbConnection conn = new OleDbConnection(strConn);
              OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
              DataSet dataSet = new DataSet();
               try
              {
                 // myCommand.Fill(dataSet);
                 // dt = dataSet.Tables[0];

                  DataRow dr = dt.Rows[0];
                  for (int i = 0; i < dt.Columns.Count; i++)
                  {
                      myDt.Columns.Add(dr[i].ToString());
                  }
                  for (int i = 1; i < dt.Rows.Count; i++)
                  {
                      DataRow mydr = myDt.NewRow();
                      mydr = dt.Rows[i];
                      myDt.Rows.Add(mydr.ItemArray);
                  }
              }
              catch (Exception ex)
              {
                  throw new Exception("该Excel文件的工作表的名字不正确," + ex.Message);
              }
              return myDt;*/
            #endregion

            HSSFWorkbook hssfworkbook;
            #region//初始化信息
            try
            {
                using (FileStream file = new FileStream(FilePath, FileMode.Open, FileAccess.Read))
                {
                    hssfworkbook = new HSSFWorkbook(file);
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            #endregion

            NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
            DataTable dt = new DataTable();
            rows.MoveNext();
            rows.MoveNext();
            HSSFRow row = (HSSFRow)rows.Current;
            for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
            {
                //dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
                //将第一列作为列表头
                dt.Columns.Add(row.GetCell(j).ToString());
            }
            while (rows.MoveNext())
            {
                row = (HSSFRow)rows.Current;
                DataRow dr = dt.NewRow();
                for (int i = 0; i < row.LastCellNum; i++)
                {
                    NPOI.SS.UserModel.ICell cell = row.GetCell(i);
                    if (cell == null)
                    {
                        dr[i] = null;
                    }
                    else
                    {
                        dr[i] = cell.ToString();
                    }
                }
                dt.Rows.Add(dr);
            }
            return dt;

        }

8 个解决方案

#1

这是什么............

分享帖?

#2

oh,my god
oledb就不要再拿出来分享了

#3

分享好啊。还是用NPOI组件吧

#4

该回复于2014-11-12 09:56:27被管理员删除

#5

分享贴啊，我还以为是求助帖呢？

#6

#7

该回复于2014-12-19 23:30:01被版主删除

#8

ssis三分钟写个package路过。。。。

#1