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