How does excel read all data column by column
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!
