XLSX 转 DataSet

XLSX 转 DataSet

 

public DataSet XlsToDataSet(string vFilePath)
{
    DataSet dataSet = new DataSet();
    Stream stream = null;
    try
    {
        stream = File.OpenRead(vFilePath);
        HSSFWorkbook hssfworkbook = new HSSFWorkbook(stream);
        int numberOfSheets = hssfworkbook.NumberOfSheets;
        for (int i = 0; i < numberOfSheets; i++)
        {
            HSSFSheet hssfsheet = (HSSFSheet)hssfworkbook.GetSheetAt(i);
            DataTable dataTable = new DataTable();
            dataTable.TableName = hssfsheet.SheetName;
            HSSFRow hssfrow = (HSSFRow)hssfsheet.GetRow(0);
            int lastCellNum = (int)hssfrow.LastCellNum;
            for (int j = (int)hssfrow.FirstCellNum; j < lastCellNum; j++)
            {
                DataColumn column = new DataColumn(hssfrow.GetCell(j).StringCellValue);
                dataTable.Columns.Add(column);
            }
            int lastRowNum = hssfsheet.LastRowNum;
            for (int j = hssfsheet.FirstRowNum + 1; j < hssfsheet.LastRowNum; j++)
            {
                HSSFRow hssfrow2 = (HSSFRow)hssfsheet.GetRow(j);
                DataRow dataRow = dataTable.NewRow();
                for (int k = (int)hssfrow2.FirstCellNum; k < lastCellNum; k++)
                {
                    dataRow[k] = hssfrow2.GetCell(k).ToString();
                }
                dataTable.Rows.Add(dataRow);
            }
            dataSet.Tables.Add(dataTable);
        }
    }
    catch (Exception ex)
    {
        throw new Exception("Xls to DataSet: \n" + ex.Message);
    }
    finally
    {
        if (stream != null)
        {
            stream.Close();
        }
    }
    return dataSet;
}