C#实现EXCEL表格转DataTable

    c#代码实现把excel文件转化为datatable，根据excel的文件后缀名不同，用不同的方法来进行实现，下面通过根据excel文件的两种后缀名（*.xlsx和*.xls）分别来实现。获取文件后缀名的方法是：path.getextension(filename)方法，通过引用：using system.io;实现代码如下：（其中以下代码中出现的filename都是带盘符的绝对路径）

• 根据excel文件的后缀名不同调用的主方法

   private datatable filetodatatable(string filename)
      {
          datatable dt = new datatable();
          string extendname = path.getextension(filename);//获取文件的后缀名
          switch (extendname.tolower())
          {
              case ".xls":
                  dt = xlstodatatable(filename);
                  break;
              case ".xlsx":
                  dt = xlsxtodatatable(filename);
                  break;
              default:
                  break;
          }
          return dt;
      }

   

• xlstodatatable()

   private datatable xlstodatatable(string filename)
      {
          datatable datatable = new datatable();
          stream stream = null;
          try
          {
              stream = file.openread(filename);
              hssfworkbook hssfworkbook = new hssfworkbook(stream);
              hssfsheet hssfsheet = (hssfsheet)hssfworkbook.getsheetat(hssfworkbook.activesheetindex);
              hssfrow hssfrow = (hssfrow)hssfsheet.getrow(0);
              int lastcellnum = (int)hssfrow.lastcellnum;
              for (int i = (int)hssfrow.firstcellnum; i < lastcellnum; i++)
              {
                  datacolumn column = new datacolumn(hssfrow.getcell(i).stringcellvalue);
                  datatable.columns.add(column);
              }
              datatable.tablename = hssfsheet.sheetname;
              int lastrownum = hssfsheet.lastrownum;
              //列名后,从table第二行开始进行填充数据
              for (int i = hssfsheet.firstrownum + 1; i < hssfsheet.lastrownum; i++)//
              {
                  hssfrow hssfrow2 = (hssfrow)hssfsheet.getrow(i);
                  datarow datarow = datatable.newrow();
                  for (int j = (int)hssfrow2.firstcellnum; j < lastcellnum; j++)//
                  {
                      datarow[j] = hssfrow2.getcell(j);//
                  }
                  datatable.rows.add(datarow);
              }
              stream.close();
          }
          catch (exception ex)
          {
              scriptmanager.registerstartupscript(page, gettype(), "alertform", "alert(' xls to datatable: " + ex.message + "');", true);
          }
          finally
          {
              if (stream != null)
              {
                  stream.close();
              }
          }
          return datatable;
      }

   

• xlsxtodatatable()

  public datatable xlsxtodatatable(string vfilepath)
      {
          datatable datatable = new datatable();
          try
          {
              sldocument sldocument = new sldocument(vfilepath);
              datatable.tablename = sldocument.getsheetnames()[0];
              slworksheetstatistics worksheetstatistics = sldocument.getworksheetstatistics();
              int startcolumnindex = worksheetstatistics.startcolumnindex;
              int endcolumnindex = worksheetstatistics.endcolumnindex;
              int startrowindex = worksheetstatistics.startrowindex;
              int endrowindex = worksheetstatistics.endrowindex;
              for (int i = startcolumnindex; i <= endcolumnindex; i++)
              {
                  slrsttype cellvalueasrsttype = sldocument.getcellvalueasrsttype(1, i);
                  datatable.columns.add(new datacolumn(cellvalueasrsttype.gettext(), typeof(string)));
              }
              for (int j = startrowindex + 1; j <= endrowindex; j++)
              {
                  datarow datarow = datatable.newrow();
                  for (int i = startcolumnindex; i <= endcolumnindex; i++)
                  {
                      datarow[i - 1] = sldocument.getcellvalueasstring(j, i);
                  }
                  datatable.rows.add(datarow);
              }
          }
          catch (exception ex)
          {
              throw new exception("xlsx to datatable: \n" + ex.message);
          }
          return datatable;
      }