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;
      }