npoi" operation of ASP. NET (C #) Excel

1. First download "Npoi" to the Internet. DLL ". Reference.

2. Create a new operation class "ExcelHelper.cs":

Using system.collections.generic;using system.data;using system.io;using system.linq;using NPOI. HSSF. Usermodel;using Npoi. Ss. Usermodel;using Npoi. XSSF.        Usermodel;public class excelhelper{public class X2003 {#region Excel2003//<summary> Read the data in the Excel file into the DataTable (XLS)///</summary>//<param name= "file" ></param>/ <returns></returns> public static DataTable Exceltotableforxls (string file) {Data            Table dt = new DataTable (); using (FileStream fs = new FileStream (file, FileMode.Open, FileAccess.Read)) {Hssfworkbook HSSF                workbook = new Hssfworkbook (FS); Isheet sheet = Hssfworkbook.                Getsheetat (0); Table header IRow Header = sheet. GetRow (sheet.                Firstrownum);                list<int> columns = new list<int> (); for (int i = 0; I < header. Lastcellnum;      i++) {              Object obj = Getvaluetypeforxls (header.                    Getcell (i) as Hssfcell); if (obj = = null | | obj. ToString () = = string. Empty) {dt.                        Columns.Add (New DataColumn ("Columns" + i.tostring ()));                    Continue } else dt. Columns.Add (New DataColumn (obj.                    ToString ())); Columns.                ADD (i); }//data for (int i = sheet. Firstrownum + 1; I <= sheet. Lastrownum; i++) {DataRow dr = dt.                    NewRow ();                    BOOL HasValue = false; foreach (int j in columns) {Dr[j] = Getvaluetypeforxls (sheet. GetRow (i).                        Getcell (j) as Hssfcell); if (dr[j]! = null && dr[j]. ToString ()! = string.                        Empty) {HasValue = true;    }                } if (HasValue) {dt.                    Rows.Add (DR);        }}} return dt; }///<summary>//Export DataTable data to Excel file (XLS)///</summary>//<param name = "DT" ></param>///<param name= "file" ></param> public static void Tabletoexcelforxls (Da            Tatable dt, string file) {Hssfworkbook Hssfworkbook = new Hssfworkbook (); Isheet sheet = Hssfworkbook.            Createsheet ("Test"); Table header IRow row = sheet.            CreateRow (0); for (int i = 0; i < dt. Columns.count; i++) {Icell cell = row.                Createcell (i); Cell. Setcellvalue (dt. Columns[i].            ColumnName); }//data for (int i = 0; i < dt. Rows.Count; i++) {IRow row1 = sheet.                CreateRow (i + 1); for (int j = 0; J < dt. Columns.count; J + +) {Icell cell = Row1.                    Createcell (j); Cell. Setcellvalue (dt. ROWS[I][J].                ToString ());            }}//convert byte array MemoryStream stream = new MemoryStream (); Hssfworkbook.            Write (stream); var buf = stream.            ToArray ();                Save as Excel file using (FileStream fs = new FileStream (file, FileMode.Create, FileAccess.Write)) { Fs. Write (buf, 0, buf.                Length); Fs.            Flush (); }}///<summary>//Get the cell type (XLS)///</summary>//<param name= "cell" ></param>//<returns></returns> private static Object Getvaluetypeforxls (Hssfcell cel            L) {if (cell = = NULL) return null; Switch (cell.       Celltype) {case celltype.blank://blank:return null;         Case Celltype.boolean://boolean:return cell.                Booleancellvalue; Case Celltype.numeric://numeric:return cell.                Numericcellvalue; Case celltype.string://string:return cell.                Stringcellvalue; Case Celltype.error://error:return cell.                Errorcellvalue; Case Celltype.formula://formula:default:return "=" + cell.            Cellformula; }} #endregion} public class X2007 {#region Excel2007//<summary>// /Read data from Excel file into DataTable (XLSX)///</summary>//<param name= "file" ></param>// /<returns></returns> public static DataTable exceltotableforxlsx (string file) {Data            Table dt = new DataTable ();        using (FileStream fs = new FileStream (file, FileMode.Open, FileAccess.Read))    {Xssfworkbook Xssfworkbook = new Xssfworkbook (FS); Isheet sheet = Xssfworkbook.                Getsheetat (0); Table header IRow Header = sheet. GetRow (sheet.                Firstrownum);                list<int> columns = new list<int> (); for (int i = 0; I < header. Lastcellnum; i++) {Object obj = getvaluetypeforxlsx (header.                    Getcell (i) as Xssfcell); if (obj = = null | | obj. ToString () = = string. Empty) {dt.                        Columns.Add (New DataColumn ("Columns" + i.tostring ()));                    Continue } else dt. Columns.Add (New DataColumn (obj.                    ToString ())); Columns.                ADD (i); }//data for (int i = sheet. Firstrownum + 1; I <= sheet. Lastrownum; i++) {DataRow dr = dt.                    NewRow (); BooL HasValue = false; foreach (int j in columns) {Dr[j] = getvaluetypeforxlsx (sheet. GetRow (i).                        Getcell (j) as Xssfcell); if (dr[j]! = null && dr[j]. ToString ()! = string.                        Empty) {HasValue = true; }} if (HasValue) {dt.                    Rows.Add (DR);        }}} return dt; }///<summary>//Export DataTable data to an Excel file (xlsx)///</summary>//<param Nam e= "DT" ></param>///<param name= "file" ></param> public static void Tabletoexcelforxlsx (            DataTable dt, string file) {Xssfworkbook Xssfworkbook = new Xssfworkbook (); Isheet sheet = Xssfworkbook.            Createsheet ("Test"); Table header IRow row = sheet. CreAterow (0); for (int i = 0; i < dt. Columns.count; i++) {Icell cell = row.                Createcell (i); Cell. Setcellvalue (dt. Columns[i].            ColumnName); }//data for (int i = 0; i < dt. Rows.Count; i++) {IRow row1 = sheet.                CreateRow (i + 1); for (int j = 0; j < dt. Columns.count; J + +) {Icell cell = Row1.                    Createcell (j); Cell. Setcellvalue (dt. ROWS[I][J].                ToString ());            }}//convert byte array MemoryStream stream = new MemoryStream (); Xssfworkbook.            Write (stream); var buf = stream.            ToArray ();                Save as Excel file using (FileStream fs = new FileStream (file, FileMode.Create, FileAccess.Write)) { Fs. Write (buf, 0, buf.                Length); Fs.            Flush (); }}///<summary>//Get the cell type (xlsx)///</summary>//<param name= "cell" ></param>///<returns></returns> private S            Tatic Object Getvaluetypeforxlsx (Xssfcell cell) {if (cell = = NULL) return null; Switch (cell.                Celltype) {case celltype.blank://blank:return null; Case Celltype.boolean://boolean:return cell.                Booleancellvalue; Case Celltype.numeric://numeric:return cell.                Numericcellvalue; Case celltype.string://string:return cell.                Stringcellvalue; Case Celltype.error://error:return cell.                Errorcellvalue; Case Celltype.formula://formula:default:return "=" + cell.            Cellformula; }} #endregion} public static DataTable getdatatable (string filepath) {var dt = new Datata BLe ("xls"); if (filepath. Last () = = ' s ') {dt = x2003.        Exceltotableforxls (filepath); } else {dt = x2007.        EXCELTOTABLEFORXLSX (filepath);    } return DT; }}

3. Main code of the program background:

Using system;using system.collections.generic;using system.web;using system.web.ui;using System.Web.UI.WebControls; Using system.data;using System.linq;public partial class _default:system.web.ui.page {protected void Page_Load (Objec T sender, EventArgs e) {} protected void Btn_read_03_click (Object o, EventArgs e) {var dt = Excelhelp Er.        Getdatatable (Server.MapPath ("~/xls_tmp/2003.xls")); G1.        DataSource = DT; G1.    DataBind (); } protected void Btn_read_07_click (Object o, EventArgs e) {var dt = excelhelper.getdatatable (Server.MapPath (        "~/xls_tmp/2007.xlsx")); G1.        DataSource = DT; G1.    DataBind (); } protected void Btn_import_03_click (Object o, EventArgs e) {var name = DateTime.Now.ToString ("Yyyymmddhhmms S ") + new Random (DateTime.Now.Second).        Next (10000);        var path = Server.MapPath ("~/xls_down/" + name + ". xls");        var dt = new System.Data.DataTable (); var columns=enumerable.range (1, 10). Select (d= = new DataColumn ("a" +d.tostring (), typeof (String))).        ToArray (); Dt.        Columns.addrange (Columns); for (int i = 0; i < 33333; i++) {var id = guid.newguid ().            ToString (); Dt.        Rows.Add (ID, ID, ID, ID, ID, ID, ID, ID, ID, id);        } ExcelHelper.x2003.TableToExcelForXLS (dt, path);    DownloadFile (path); } protected void Btn_import_07_click (Object o, EventArgs e) {var name = DateTime.Now.ToString ("Yyyymmddhhmms S ") + new Random (DateTime.Now.Second).        Next (10000);        var path = Server.MapPath ("~/xls_down/" + name + ". xlsx");        var dt = new System.Data.DataTable (); var Columns = Enumerable.range (1, 10). Select (d = new DataColumn ("a" + d.tostring (), typeof (String))).        ToArray (); Dt.        Columns.addrange (Columns); for (int i = 0; i < 33333; i++) {var id = guid.newguid ().            ToString (); Dt.        Rows.Add (ID, ID, ID, ID, ID, ID, ID, ID, ID, id); } excelhelper.x2007.TABLETOEXCELFORXLSX (dt, path);    DownloadFile (path);        } void DownloadFile (String s_path) {System.IO.FileInfo file = new System.IO.FileInfo (S_path);        HttpContext.Current.Response.ContentType = "Application/ms-download";        HttpContext.Current.Response.Clear ();        HttpContext.Current.Response.AddHeader ("Content-type", "Application/octet-stream");        HttpContext.Current.Response.Charset = "Utf-8"; HttpContext.Current.Response.AddHeader ("Content-disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode (file.        Name, System.Text.Encoding.UTF8)); HttpContext.Current.Response.AddHeader ("Content-length", file.        Length.tostring ()); HttpContext.Current.Response.WriteFile (file.        FullName);        HttpContext.Current.Response.Flush ();        HttpContext.Current.Response.Clear ();    HttpContext.Current.Response.End (); }}

npoi" operation of ASP. NET (C #) Excel