Shulou(Shulou.com)06/02 Report--

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)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.