NET operation Excel (Ultimate method Npoi)

Http://www.cnblogs.com/stone_w/archive/2012/08/02/2620528.html

NET operation Excel (Ultimate method Npoi) Preface

Asp.net/c# operation Excel has been Laosheng long talk, but the following I said this Npoi operation Excel, should be the best solution, not one, using Npoi can help developers to read and write office without installing Microsoft Office 97-2003 of files, supported file formats include XLS, DOC, ppt etc. Npoi is built on the POI 3.x version, which can read and write Word/excel documents without Office installation.

method

Go to the official website: http://npoi.codeplex.com/The download requires the introduction of a DLL (you can select the. net2.0 or . net4.0 dll), and then add references to the Web site.

ASP. NET Export code:

Npoi. HSSF. Usermodel.hssfworkbook book =NewNpoi. HSSF. Usermodel.hssfworkbook (); Npoi. Ss. Usermodel.isheet sheet = Book. Createsheet ("Test_01");//The first column NPOI.SS.UserModel.IRow row = sheet. CreateRow (0); row. Createcell (0). Setcellvalue ("First Column first row");//The second column NPOI.SS.UserModel.IRow row2 = sheet. CreateRow (1); Row2. Createcell (0). Setcellvalue ("Second column first row");// ... // write to client System.IO.MemoryStream ms = new System.IO.MemoryStream (); Write (MS); Response.AddHeader ( "content-disposition", string. Format ( "attachment; Filename={0}.xls" yyyymmddhhmmssfff "null;ms. Close (); Ms. Dispose ();                

ASP . NET Import Code:

Hssfworkbook Hssfworkbook;#regionPublic DataTable Importexcelfile (StringFilePath) {#region//Initializing informationTry{using (FileStream file =NewFileStream (FilePath, FileMode.Open, FileAccess.Read)) {Hssfworkbook =NewHssfworkbook (file); } }Catch(Exception e) {ThrowE }#endregionNpoi. Ss. Usermodel.sheet Sheet = Hssfworkbook. Getsheetat (0); System.Collections.IEnumerator rows =Sheet. Getrowenumerator (); DataTable dt =NewDataTable ();for (Int J =0; J < (sheet. GetRow (0). Lastcellnum); J + +) {dt. Columns.Add (Convert.tochar ((int‘A') +j). ToString ()); }While (hssfrow) rows. Current; DataRow Dr = dt. NewRow (); for (int i = 0; i < row. Lastcellnum; I++ row. Getcell (i); if (cell = = nullnull;} else {Dr[i] = cell. ToString (); }} dt. Rows.Add (DR); } return DT;  #endregion            

C # Export Excel:

PublicStaticvoid Writeexcel (DataTable DT,StringFilePath) {if (!String. IsNullOrEmpty (FilePath) &&Null! = DT && dt. Rows.Count >0) {Npoi. HSSF. Usermodel.hssfworkbook book =NewNpoi. HSSF. Usermodel.hssfworkbook (); Npoi. Ss. Usermodel.isheet sheet =Book. Createsheet (dt. TableName); Npoi. Ss. Usermodel.irow row = sheet. CreateRow (0);for (int i =0; i < dt. Columns.count; i++) {row. Createcell (i). Setcellvalue (dt. Columns[i]. ColumnName); }for (int i =0; i < dt. Rows.Count; i++) {Npoi. Ss. Usermodel.irow row2 = sheet. CreateRow (i +1);for (int j = 0; j < dt. Columns.count; J++// write to client using ( System.IO.MemoryStream ms = new System.IO.MemoryStream () ) {Book. Write (MS); using (FileStream fs = new FileStream (FilePath, FileMode.Create, FileAccess.Write)) {byte[] data = Ms. ToArray (); Fs. Write (data, 0null;}}}       

Conclusion

This is very simple to solve the operation of Excel, we can try, very easy to use, if you feel useful please recommend, thank you.

NET operation Excel (Ultimate method Npoi)