Use NPOI to export the excel content, ignore the class capacity of the merged columns, and use npoiexcel

Use NPOI to export the excel content, ignore the class capacity of the merged columns, and use npoiexcel

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.

Using System;
Using System. Data;
Using System. IO;
Using NPOI. SS. UserModel;
Using NPOI. XSSF. UserModel;
Using NPOI. HSSF. UserModel;
Using System. Windows. Forms;

Namespace WindowsFormsApplication1
{
Class ExcelHelper
{
/// <Summary>
/// Import the excel file to the datatable
/// </Summary>
/// <Param name = "filePath"> excel path </param>
/// <Param name = "isColumnName"> whether the first row is a column name </param>
/// <Param name = "textBox"> used for checking </param>
/// <Returns> return datatable </returns>
Public static DataTable ExcelToDataTable (string filePath, bool isColumnName, TextBox textBox)
{
DataTable dataTable = null;
FileStream fs = null;
DataColumn column = null;
DataRow dataRow = null;
IWorkbook workbook = null;
ISheet sheet = null;
IRow row = null;
ICell cell = null;
Int startRow = 9;
Try
{
Using (fs = new FileStream (filePath, FileMode. Open, FileAccess. Read ))
{
// Version 2010
If (filePath. IndexOf (". xlsx")> 0)
Workbook = new XSSFWorkbook (fs );
// Version 2003
Else if (filePath. IndexOf (". xls")> 0)
Workbook = new HSSFWorkbook (fs );

If (workbook! = Null)
{
Sheet = workbook. GetSheetAt (0); // read the first sheet. Of course, each sheet can be read cyclically.
DataTable = new DataTable ();
If (sheet! = Null)
{
Int rowCount = sheet. LastRowNum; // The total number of rows.
If (rowCount> 0)
{
IRow firstRow = sheet. GetRow (1); // The first row
Int cellCount = firstRow. LastCellNum; // Number of Columns
// Construct a datatable Column
If (isColumnName)
{
StartRow = 1; // if the first row is a column name, it is read from the second row.
For (int I = firstRow. FirstCellNum; I <cellCount; ++ I)
{
Cell = firstRow. GetCell (I );
If (cell! = Null)
{
If (cell. StringCellValue! = Null)
{
Column = new DataColumn (cell. StringCellValue );
DataTable. Columns. Add (column );
}
}
}
}
Else
{
For (int I = firstRow. FirstCellNum; I <cellCount; ++ I)
{
Column = new DataColumn ("column" + (I + 1 ));
DataTable. Columns. Add (column );
}
}

// Fill the row
For (int I = startRow; I <= rowCount; ++ I)
{
Row = sheet. GetRow (I );
If (row = null) continue;
Int sign = 0; // used for counting
DataRow = dataTable. NewRow ();
For (int j = row. FirstCellNum; j <cellCount; ++ j)
{
If (j =-1) // when the number of rows with content exceeds the limit
Break;
If (! Sheet. IsColumnHidden (j) // you can check whether the row is hidden.
{
Cell = row. GetCell (j );
If (cell = null)
{
DataRow [sign] = "";
}
Else
{
// CellType (Unknown =-1, Numeric = 0, String = 1, Formula = 2, Blank = 3, Boolean = 4, Error = 5 ,)
Switch (cell. CellType)
{
Case CellType. Blank:
DataRow [sign] = "";
Break;
Case CellType. Numeric:
Short format = cell. CellStyle. DataFormat;
// Processing of time formats (such as 2015.12.5, 2015/12/5, and 2015-12-5)
If (format = 14 | format = 31 | format = 57 | format = 58)
DataRow [sign] = cell. DateCellValue;
Else
DataRow [sign] = cell. NumericCellValue;
Break;
Case CellType. String:
DataRow [sign] = cell. StringCellValue;
Break;
}
}
Sign ++;
}
}
DataTable. Rows. Add (dataRow );
}
}
}
}
}
Return dataTable;
}
Catch (Exception ex)
{
System. Windows. Forms. MessageBox. Show (ex. ToString ());
If (fs! = Null)
{
Fs. Close ();
}
Return null;
}
}

}
}