Npoi Operations on Excel (sheet to DataTable, list<t>)

The operation of Excel is performed through Npoi, which is mainly read. Encapsulated into the Excelhelper action class.

  1 using System.Collections.Generic; 2 using Npoi. HSSF.  Usermodel; 3 using Npoi. Ss.  Usermodel; 4 using Npoi. XSSF.  Usermodel;  5 using System.IO;  6 using System.Data;  7 using System;        8 9 Namespace Commonhelper {One public class Excelhelper () {15, Public excelhelper ()} {16 <summary> 17///File Stream initialization objects///</summary>//<param name= "stream" >& Lt;/param> public Excelhelper (Stream stream) {_iworkbook = Createworkbook (stream); *//<summary> 26//Incoming file name +//</summary>//<param N Ame= "filename" ></param> public excelhelper (string filename) (Filestrea M fileStream = new FileStream (FileName, FileMode.Open, FileAccess.Read)) {_iworkbook = Cr Eateworkbook (FileStream); +/-<summarY> 38///working thin///</summary> Iworkbook _iworkbook; //<summary> 43//Create Workbook objects from//</summary>//<param name= "Strea M "></param>//<returns></returns> iworkbook Createworkbook (Stream Strea m) (Xssfworkbook) (stream), {* * * * *;//07 52} Hssfworkbook (Stream),//03 56} 57 5        8}//<summary> 61///Convert data in sheet to DataTable///</summary> 63 <param name= "Sheet" ></param>//<returns></returns> + private DataTable Ex            Porttodatatable (isheet sheet) (70): 68 69//default, the first row is the field IRow Headrow = sheet. GetRow (0);  71 72          Set the DataTable field to (int i = headrow.firstcellnum, len = headrow.lastcellnum; i < Len; i++) 74 {dt. Columns.Add (Headrow.cells[i]. Stringcellvalue); 76} 77//Traverse data line for (int i = (sheet). Firstrownum + 1), Len = sheet. Lastrownum + 1; i < Len; i++) IRow Temprow = sheet. GetRow (i); Bayi DataRow datarow = dt. NewRow (); 82 83//traverse each cell of a row with a for (int r = 0, j = temprow.firstcellnum, Len2 = Temprow.lastcellnum ; J < Len2; J + +, r++) (Icell cell = Temprow.getcell (j); L! = null) * (cell.                                Celltype) (celltype.string:94) DATAROW[R] = cell. Stringcellvalue; a break;            96                Case celltype.numeric:97 Datarow[r] = cell. Numericcellvalue; 98 break; celltype.boolean:100 Datarow[r] = cell.                                 booleancellvalue;101 break;102 Default:datarow[r] = "ERROR"; 103                break;104}105}106}107 Dt. Rows.Add (DataRow); 108}109 return dt;110}111///<summary>113/Sh The data in the </summary>115 is converted to the list collection./////<param name= "sheet" ></param>116//<p Aram name= "Fields" ></param>117//<returns></returns>118 private ilist<t> Export Tolist<t> (Isheet sheet,string[] fields) where T:class,new () 119 {ilist<t> list = new Lis T&lT T> (); 121 122//traverse each row of data 123 for (int i = sheet. Firstrownum + 1, len = sheet. Lastrownum + 1; i < Len; i++) 124 {t=new T () 126 IRow row = sheet. GetRow (i); 127 (Int j = 0, len2 = fields). Length; J < Len2; J + +) 129 {Icell Cell=row. Getcell (j); 131 Object cellvalue= null;132 133 switch (cell. Celltype) 134 {135 Case celltype.string://Text 136 cell Value = cell.                            stringcellvalue;137 break;138 Case Celltype.numeric://value 139 Cellvalue =convert.toint32 (cell. Numericcellvalue);//double converted to int140 break;141 case Celltype.boolean://b ool142 Cellvalue = cell.     booleancellvalue;143 break;144                   Case Celltype.blank://blank 145 cellvalue = ""; 146 b reak;147 default:cellvalue = "ERROR"; 148 break;149} 151 typeof (T). GetProperty (Fields[j]). SetValue (t,cellvalue,null);}153 list. ADD (t); 154}155 156 return list;157}158 159//<summary>160//Get First The value of the first row of a sheet, the Y column. Starting point is 1161//</summary>162//<param name= "X" > Line </param>163//<param name= "Y" &        gt; column </param>164//<returns></returns>165 public string getcellvalue (int X, int Y) 166 {167 Isheet sheet = _iworkbook.getsheetat (0); 168 169 IRow row = sheet. GetRow (X-1); 171 return row. Getcell (Y-1).     ToString (); 172}173 174//<summary>175//Get all data on one line 176   </summary>177//<param name= "x" > X row </param>178//<returns></returns&gt ; 179 public string[] Getcells (int X) [181 list<string> List = new list<string> (); 182 183 Isheet sheet = _iworkbook.getsheetat (0); 184 185 IRow row = sheet. GetRow (X-1); 186 187 for (int i = 0, len = row. Lastcellnum; i < Len; i++) 188 {189 list. ADD (row. Getcell (i). Stringcellvalue);//The data format conversion is not considered here, bug190}191 return list appears. ToArray (); 192}193 194//<summary>195//First sheet data, converted to DataTable196//</summary&            gt;197//<returns></returns>198 public DataTable exportexceltodatatable () 199 {200 Return exporttodatatable (_iworkbook.getsheetat (0)) 201}202 203//<summary>204//sh Eetindex table data, converted to DataTable205//</summary>206///<param name= "Sheetindex" > First few sheet, starting from 1 </param>207//<returns></returns>208 Publi C DataTable exportexceltodatatable (int sheetindex) 209 {return exporttodatatable (_iworkbook.getsheet at (sheetIndex-1)) 211}212 213 214//<summary>215//Excel default first sheet export to collection 216//&LT ;/summary>217//<param name= "Fields" >excel each column, in turn, the name of the object field to be converted </param>218//<returns>             </returns>219 public ilist<t> exceltolist<t> (string[] fields) where t:class,new () 220 {221 Return exporttolist<t> (_iworkbook.getsheetat (0), fields) 222}223 224//<summary>22 5///sheet exported to collection 226///</summary>227//<param name= "Sheetindex" > First sheet, starting from 1 </param>228//<param name= "Fields" >excel each column, in turn the Object field name to be converted </param>229//<returns&gt    ; </returns>230    Public ilist<t> exceltolist<t> (Int. sheetindex,string[] fields) where t:class,new () 231 {232 Return exporttolist<t> (_iworkbook.getsheetat (sheetIndex-1), fields); 233}234 235}236}

Npoi Operations on Excel (sheet to DataTable, list<t>)