C#把EXCEL數(shù)據(jù)轉換成DataTable

更新時間：2022年04月09日 16:44:01 作者：農碼一生

這篇文章介紹了C#把EXCEL數(shù)據(jù)轉換成DataTable的方法，文中通過示例代碼介紹的非常詳細。對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下

C#實現(xiàn)EXCEL表格轉DataTable

C#代碼實現(xiàn)把Excel文件轉化為DataTable，根據(jù)Excel的文件后綴名不同，用不同的方法來進行實現(xiàn)，下面通過根據(jù)Excel文件的兩種后綴名（*.xlsx和*.xls）分別來實現(xiàn)。獲取文件后綴名的方法是：Path.GetExtension(fileName)方法，通過引用：using System.IO;實現(xiàn)代碼如下：（其中以下代碼中出現(xiàn)的filename都是帶盤符的絕對路徑）

根據(jù)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第二行開始進行填充數(shù)據(jù)
            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;
    }

到此這篇關于C#把EXCEL數(shù)據(jù)轉換成DataTable的文章就介紹到這了。希望對大家的學習有所幫助，也希望大家多多支持腳本之家。

您可能感興趣的文章: