C ා code implementation converts excel file into DataTable. According to the different suffix names of Excel files, it is implemented in different ways. Next, it is implemented by two suffixes of Excel file (*. xlsx and *. xls). The method to get the file suffix name is: Path.GetExtension(fileName) method, with reference to: using System.IO; the implementation code is as follows: (the filename in the following code is the absolute path with drive letter)

• The main method called according to the suffix of Excel file

   private DataTable FileToDataTable(string fileName)
      {
          DataTable dt = new DataTable();
          string extendName = Path.GetExtension(fileName);//Get the suffix of the file
          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;
              //After column names,from TABLE The second line starts to fill in the data
              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;
      }