Asp.net (C #) NPOI & amp; quot; Excel operations

Asp.net (C #) for NPOI & quot; Excel operations

1. First download "NPOI. DLL" from the Internet and reference it.

2. Create an 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 ////// Read the data in the Excel file to the able (xls )/////////
 Public static DataTable ExcelToTableForXLS (string file) {DataTable dt = new DataTable (); using (FileStream fs = new FileStream (file, FileMode. open, FileAccess. read) {HSSFWorkbook hssfworkbook = new HSSFWorkbook (fs); ISheet sheet = hssfworkbook. getSheetAt (0); // header IRow header = sheet. getRow (sheet. firstRowNum); List
 
  
Columns = new List
  
   
(); For (int I = 0; I /// Export the DataTable data to an Excel file (xls )//////
   ///
   Public static void TableToExcelForXLS (DataTable dt, string file) {HSSFWorkbook hssfworkbook = new HSSFWorkbook (); ISheet sheet = hssfworkbook. createSheet ("Test"); // 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 to the byte array MemoryStream = new MemoryStream (); hssfworkbook. write (stream); var buf = stream. toArray (); // save as an Excel file using (FileStream fs = new FileStream (file, FileMode. create, FileAccess. write) {fs. write (buf, 0, buf. length); fs. flush ();}}///
   /// Obtain the cell type (xls )//////
   ///
   Private static object GetValueTypeForXLS (HSSFCell 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 class x2007 {# region Excel2007 ///
   /// Read the data in the Excel file to the DataTable (xlsx )//////
   ///
   Public static DataTable ExcelToTableForXLSX (string file) {DataTable dt = new DataTable (); using (FileStream fs = new FileStream (file, FileMode. open, FileAccess. read) {XSSFWorkbook xssfworkbook = new XSSFWorkbook (fs); ISheet sheet = xssfworkbook. getSheetAt (0); // header IRow header = sheet. getRow (sheet. firstRowNum); List
   
    
Columns = new List
    
     
(); For (int I = 0; I /// Export the DataTable data to an Excel file (xlsx )//////
     ///
     Public static void TableToExcelForXLSX (DataTable dt, string file) {XSSFWorkbook xssfworkbook = new XSSFWorkbook (); ISheet sheet = xssfworkbook. createSheet ("Test"); // 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 to the byte array MemoryStream = new MemoryStream (); xssfworkbook. write (stream); var buf = stream. toArray (); // save as an Excel file using (FileStream fs = new FileStream (file, FileMode. create, FileAccess. write) {fs. write (buf, 0, buf. length); fs. flush ();}}///
     /// Obtain the cell type (xlsx )//////
     ///
     Private static 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 DataTable ("xls"); if (filepath. last () = 's') {dt = x2003.ExcelToTableForXLS (filepath);} else {dt = x2007.ExcelToTableForXLSX (filepath);} return dt ;}}
    
   
  
 

3. Main Program background code:

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(object sender, EventArgs e)    {    }    protected void btn_read_03_click(object o, EventArgs e)    {        var dt = ExcelHelper.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("yyyyMMddhhmmss") + 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("yyyyMMddhhmmss") + 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();    }}