Xlsx 转 DataTable

Xlsx 转 DataTable

 

public DataTable XlsxToDataTable(string vFilePath, string vSheetName)
{
    DataTable dataTable = new DataTable();
    try
    {
        SLDocument sldocument = new SLDocument(vFilePath, vSheetName);
        dataTable.TableName = vSheetName;
        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;
}

 

public DataTable XlsToDataTable(string vFilePath, string vSheetName, int vJumpToRow)
{
    DataTable dataTable = new DataTable();
    Stream stream = null;
    try
    {
        stream = File.OpenRead(vFilePath);
        HSSFWorkbook hssfworkbook = new HSSFWorkbook(stream);
        HSSFSheet hssfsheet = (HSSFSheet)hssfworkbook.GetSheet(vSheetName);
        HSSFRow hssfrow = (HSSFRow)hssfsheet.GetRow(vJumpToRow - 1);
        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 = vSheetName;
        int lastRowNum = hssfsheet.LastRowNum;
        for (int i = hssfsheet.FirstRowNum + vJumpToRow; i <= hssfsheet.LastRowNum; i++)
        {
            HSSFRow hssfrow2 = (HSSFRow)hssfsheet.GetRow(i);
            DataRow dataRow = dataTable.NewRow();
            for (int j = (int)hssfrow2.FirstCellNum; j < lastCellNum; j++)
            {
                if (hssfrow2.GetCell(j) == null)
                {
                    dataRow[j] = "";
                }
                else
                {
                    dataRow[j] = hssfrow2.GetCell(j).ToString();
                }
            }
            dataTable.Rows.Add(dataRow);
        }
        stream.Close();
    }
    catch (Exception ex)
    {
        throw new Exception("Xls to DataTable: \n" + ex.Message);
    }
    finally
    {
        if (stream != null)
        {
            stream.Close();
        }
    }
    return dataTable;
}