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