C# reads all the contents of excel line by line
Using System
Using System.Collections.Generic
Using System.Linq
Using System.Text
Using NPOI.SS.UserModel
Using NPOI.HSSF.UserModel
Using System.IO
Namespace www.xinduofen.cn
{
Class NpoiOperateExcel
{
/ / /
/ / read all the contents of a worksheet within the valid range of a certain excel file line by line. If there is no content in a cell, it will fill in the corresponding position of List "string" in the form of an empty string, and judge whether it is empty in the form of string.IsNullOrEmpty (str).
/ / /
/ / represents the address saved in the excel table, including "file name .xls"
/ represents the index location of the sheet table to be read
/ / return "not empty" indicates read success, otherwise read failed; read data list "string" represents a row of data, and how many represent multiple rows of data (all rows are aligned to the left)
Public static List rowReadAll (string save_address, int sheet_number) / / read all the data of the corresponding worksheet in the excel table
{
List data = null
/ / if the input parameter is valid
If (! string.IsNullOrEmpty (save_address) & & sheet_number > 0) {
Int rowAllCnt = NpoiOperateExcel.rowORcolAllCount (save_address,sheet_number,true)
Int colAllCnt = NpoiOperateExcel.rowORcolAllCount (save_address,sheet_number,false)
Data = NpoiOperateExcel.rowReadSection (save_address, 1, rowAllCnt, 1, colAllCnt, sheet_number)
}
Return data
}
Public static int rowORcolAllCount (string save_address, int sheet_number, Boolean readFlag) / / read all the data of the corresponding worksheet in the excel table
{
Int rowORcolCnt =-1 position / initialize to-1
FileStream readfile = null
Try
{
/ / if the input parameter is valid
If (! string.IsNullOrEmpty (save_address) & & sheet_number > 0)
{
Readfile = new FileStream (save_address, FileMode.Open, FileAccess.Read)
HSSFWorkbook hssfworkbook = new HSSFWorkbook (readfile)
ISheet sheet = hssfworkbook.GetSheetAt (sheet_number-1)
If (sheet! = null)
{
If (readFlag) / / if you need to read 'valid rows'
{
RowORcolCnt = number of valid rows in sheet.LastRowNum+1;// (the number of valid rows read by NPOI does not include column headers, so add 1)
}
Else
{/ / if you need to read the maximum number of valid columns
For (int rowCnt = sheet.FirstRowNum; rowCnt rowORcolCnt)
{
RowORcolCnt = row.LastCellNum
}
}
}
}
}
}
Catch (Exception)
{
Console.WriteLine ("NpoiOperateExcel.rowOrColumnAllCount method generated an exception!")
}
Finally
{
If (readfile! = null) {readfile.Close ();}
}
Return rowORcolCnt
}
Public static List rowReadSection (string save_address, int start_row, int stop_row
Int sart_column, int stop_column, int sheet_number) / / read part of the data of the corresponding worksheet in the excel table
{
List data = null;// initialization is empty
FileStream readfile = null
Try
{
/ / if the input parameter is valid
If (! string.IsNullOrEmpty (save_address) & & start_row > 0 & & stop_row > 0 & & sart_column > 0 & & stop_column > 0 & & sheet_number > 0)
{
Readfile = new FileStream (save_address, FileMode.Open, FileAccess.Read)
HSSFWorkbook hssfworkbook = new HSSFWorkbook (readfile)
ISheet sheet = hssfworkbook.GetSheetAt (sheet_number-1)
If (sheet! = null)
{
For (int rowIndex = start_row-1; rowIndex < stop_row; rowIndex++)
{
IRow row = sheet.GetRow (rowIndex)
If (row! = null)
{
List oneRow = new List ()
For (int columnIndex = sart_column-1; columnIndex < stop_column; columnIndex++)
{
ICell cell = row.GetCell (columnIndex)
If (cell! = null)
{
OneRow.Add (cell.StringCellValue)
}
Else {
OneRow.Add (""); / / fill in empty data
}
}
If (data = = null)
{
Data = new List (); / / initialize
}
Data.Add (oneRow)
}
Else {
List oneRow = new List (); / / the software creates empty data rows in memory for blank rows in the corresponding location
For (int columnIndex = sart_column-1; columnIndex < stop_column; columnIndex++)
{
OneRow.Add (""); / / fill in empty data
}
If (data = = null)
{
Data = new List (); / / initialize
}
Data.Add (oneRow)
}
}
}
}
}
Catch (Exception)
{
Console.WriteLine ("NpoiOperateExcel.rowReadSection method generated an exception!")
}
Finally
{
If (readfile! = null) {readfile.Close ();}
}
Return data
}
}
}
Content ownership belongs to: Beijing Jihao Electronics (professional research physique testing equipment)
