Use NPOI to read Excel

This article mainly provides a class for reading the actual value of an Excel file using NPOI. The NPOI version used in this article is 1.2.5. NPOI. dll can be downloaded from the NPOI official website http://npoi.codeplex.com. If something is not properly written, I hope you can get an axe.

 

View Code using System;
Using System. Collections. Generic;
Using System. Text;

Using System. Data;
Using System. IO;
Using NPOI. HSSF. UserModel;
Using NPOI. SS. UserModel;

Namespace Common. Excel
{
Public static class NPOIHandler
{
Public static DataSet ExcelToDataSet (string excelPath)
{
Return ExcelToDataSet (excelPath, true );
}

Public static DataSet ExcelToDataSet (string excelPath, bool firstRowAsHeader)
{
Int sheetCount;
Return ExcelToDataSet (excelPath, firstRowAsHeader, out sheetCount );
}

Public static DataSet ExcelToDataSet (string excelPath, bool firstRowAsHeader, out int sheetCount)
{
Using (DataSet ds = new DataSet ())
{
Using (FileStream fileStream = new FileStream (excelPath, FileMode. Open, FileAccess. Read ))
{
HSSFWorkbook workbook = new HSSFWorkbook (fileStream );

HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator (workbook );

SheetCount = workbook. NumberOfSheets;

For (int I = 0; I <sheetCount; ++ I)
{
HSSFSheet sheet = workbook. GetSheetAt (I) as HSSFSheet;
DataTable dt = ExcelToDataTable (sheet, evaluator, firstRowAsHeader );
Ds. Tables. Add (dt );
}

Return ds;
}
}
}

Public static DataTable ExcelToDataTable (string excelPath, string sheetName)
{
Return ExcelToDataTable (excelPath, sheetName, true );
}

Public static DataTable ExcelToDataTable (string excelPath, string sheetName, bool firstRowAsHeader)
{
Using (FileStream fileStream = new FileStream (excelPath, FileMode. Open, FileAccess. Read ))
{
HSSFWorkbook workbook = new HSSFWorkbook (fileStream );

HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator (workbook );

HSSFSheet sheet = workbook. GetSheet (sheetName) as HSSFSheet;

Return ExcelToDataTable (sheet, evaluator, firstRowAsHeader );
}
}

Private static DataTable ExcelToDataTable (HSSFSheet sheet, HSSFFormulaEvaluator evaluator, bool firstRowAsHeader)
{
If (firstRowAsHeader)
{
Return ExcelToDataTableFirstRowAsHeader (sheet, evaluator );
}
Else
{
Return ExcelToDataTable (sheet, evaluator );
}
}

Private static DataTable ExcelToDataTableFirstRowAsHeader (HSSFSheet sheet, HSSFFormulaEvaluator evaluator)
{
Using (DataTable dt = new DataTable ())
{
HSSFRow firstRow = sheet. GetRow (0) as HSSFRow;
Int cellCount = GetCellCount (sheet );

For (int I = 0; I <cellCount; I ++)
{
If (firstRow. GetCell (I )! = Null)
{
Dt. Columns. Add (firstRow. GetCell (I). StringCellValue ?? String. Format ("F {0}", I + 1), typeof (string ));
}
Else
{
Dt. Columns. Add (string. Format ("F {0}", I + 1), typeof (string ));
}
}

For (int I = 1; I <= sheet. LastRowNum; I ++)
{
HSSFRow row = sheet. GetRow (I) as HSSFRow;
DataRow dr = dt. NewRow ();
FillDataRowByHSSFRow (row, evaluator, ref dr );
Dt. Rows. Add (dr );
}

Dt. TableName = sheet. SheetName;
Return dt;
}
}

Private static DataTable ExcelToDataTable (HSSFSheet sheet, HSSFFormulaEvaluator evaluator)
{
Using (DataTable dt = new DataTable ())
{
If (sheet. LastRowNum! = 0)
{
Int cellCount = GetCellCount (sheet );

For (int I = 0; I <cellCount; I ++)
{
Dt. Columns. Add (string. Format ("F {0}", I), typeof (string ));
}

For (int I = 0; I <sheet. FirstRowNum; ++ I)
{
DataRow dr = dt. NewRow ();
Dt. Rows. Add (dr );
}

For (int I = sheet. FirstRowNum; I <= sheet. LastRowNum; I ++)
{
HSSFRow row = sheet. GetRow (I) as HSSFRow;
DataRow dr = dt. NewRow ();
FillDataRowByHSSFRow (row, evaluator, ref dr );
Dt. Rows. Add (dr );
}
}

Dt. TableName = sheet. SheetName;
Return dt;
}
}

Private static void FillDataRowByHSSFRow (HSSFRow row, HSSFFormulaEvaluator evaluator, ref DataRow dr)
{
If (row! = Null)
{
For (int j = 0; j <dr. Table. Columns. Count; j ++)
{
HSSFCell cell = row. GetCell (j) as HSSFCell;

If (cell! = Null)
{
Switch (cell. CellType)
{
Case CellType. BLANK:
Dr [j] = DBNull. Value;
Break;
Case CellType. BOOLEAN:
Dr [j] = cell. BooleanCellValue;
Break;
Case CellType. NUMERIC:
If (DateUtil. IsCellDateFormatted (cell ))
{
Dr [j] = cell. DateCellValue;
}
Else
{
Dr [j] = cell. NumericCellValue;
}
Break;
Case CellType. STRING:
Dr [j] = cell. StringCellValue;
Break;
Case CellType. ERROR:
Dr [j] = cell. ErrorCellValue;
Break;
Case CellType. FORMULA:
Cell = evaluator. EvaluateInCell (cell) as HSSFCell;
Dr [j] = cell. ToString ();
Break;
Default:
Throw new NotSupportedException (string. Format ("Catched unhandle CellType [{0}]", cell. CellType ));
}
}
}
}
}

Private static int GetCellCount (HSSFSheet sheet)
{
Int firstRowNum = sheet. FirstRowNum;

Int cellCount = 0;

For (int I = sheet. FirstRowNum; I <= sheet. LastRowNum; ++ I)
{
HSSFRow row = sheet. GetRow (I) as HSSFRow;

If (row! = Null & row. LastCellNum> cellCount)
{
CellCount = row. LastCellNum;
}
}

Return cellCount;
}
}
}