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

Next, please follow the editor to study!

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 column by column. 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 column of data, and how many represent multiple columns of data (aligned on all columns)

Public static List colReadAll (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.colReadSection (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 colReadSection (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 columnIndex = sart_column-1; columnIndex < stop_column; columnIndex++) {

List oneCol= new List ()

For (int rowIndex = start_row-1; rowIndex < stop_row; rowIndex++) {

IRow row = sheet.GetRow (rowIndex)

If (row! = null)

{

ICell cell = row.GetCell (columnIndex)

If (cell! = null)

{

OneCol.Add (cell.StringCellValue)

}

Else

{

OneCol.Add (""); / / fill in empty data

}

}

Else {

OneCol.Add (""); / / fill in empty data

}

}

If (data = = null)

{

Data = new List (); / / initialize

}

Data.Add (oneCol)

}

}

}

}

Catch (Exception)

{

Console.WriteLine ("NpoiOperateExcel.colReadSection method generated an exception!")

}

Finally

{

If (readfile! = null) {readfile.Close ();}

}

Return data

}

}

}

At this point, the study on "how excel reads all the data column by column" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.