. Net MVC Import Export Excel Summary (three export Excel methods, one import Excel method)

. Net MVC Import Export Excel Summary (three export Excel methods, one import Excel method)export imported Excel files via MVC controller (available for Java SSH schemas)

public class Excelcontroller:controller {////GET:/excel/models.zbwxglentities mymdl = new M        Odels.zbwxglentities ();            <summary>//////The first method, read/write with file output stream///</summary> public void Outexcel () {            DataTable dtdata = (DataTable) session["DataTable"];            String shtnl = "";            SHTNL = "<table border= ' 1 ' cellspacing= ' 1 ' cellpadding= ' 1 ' >";            SHTNL = shtnl + "<thead>"; for (int j = 0; J < DtData.Columns.Count; J + +) {SHTNL = Shtnl + "<th>" + j + "</th&            gt; ";}            SHTNL = shtnl + "</thead><tbody>";                for (int i = 0; i < DtData.Rows.Count; i++) {shtnl = Shtnl + "<tr>"; for (int j = 0; J < DtData.Columns.Count; J + +) {SHTNL = Shtnl + "<td>" + D                TDATA.ROWS[I][J] + "</td>"; }                SHTNL = shtnl + "</tr>";            } SHTNL = Shtnl + "</tbody></table>";        exportToExcel ("Application/x-excel", "123.xls", SHTNL); } public void exportToExcel (string FieldType, String FileName, String dt) {System.Web.HttpContex            T.current.response.charset = "Utf-8"; System.Web.HttpContext.Current.Response.AppendHeader ("Content-disposition", "attachment;filename=" + Httputility.urlencode (FileName, System.Text.Encoding.UTF8).            ToString ());            System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding ("GB2312");            System.Web.HttpContext.Current.Response.ContentType = FieldType;            StringWriter tw = new StringWriter ();            System.Web.HttpContext.Current.Response.Output.Write (DT);            System.Web.HttpContext.Current.Response.Flush ();        System.Web.HttpContext.Current.Response.End ();   }//<summary>     The second method, take advantage of Microsoft's own plug-in//</summary>//<returns></returns> public ActionResult                DownloadFile () {try {datatable dt = (DataTable) session["DataTable"];                String strdate = DateTime.Now.ToString ("Yyyymmddhhmmss"); String str = Server.HTMLEncode (Request.physicalapplicationpath).                ToString () + "content\\downloadtest\\" + session["Yonghuid"] + strdate + "Excel.xls"; if (System.IO.File.Exists (str)) {//if present, delete System.IO.File.Delete (str                );                } converthelper myconverthelper = new Converthelper ();                Datatabletoexcel (dt, str);                System.Threading.Thread.Sleep (5000);            return File (str, "application/vnd.ms-excel", Strdate + "Excel.xls");                } catch {datatable dt = new DataTable (); List<dictionary<string, object>> Listreturn = converthelper.dttolist (DT);            Return Json (Listreturn, jsonrequestbehavior.allowget); }} public void Datatabletoexcel (DataTable datas, string p) {MICROSOFT.OFFICE.INTEROP.EXC El.            Application app = new Microsoft.Office.Interop.Excel.Application (); App.            SheetsInNewWorkbook = 1; App.            Workbooks.Add (); Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet) app.            ACTIVEWORKBOOK.WORKSHEETS[1]; for (int i = 0; i < datas. Columns.count; i++) {sheet. Cells[1, i + 1] = datas. Columns[i].            ColumnName; } for (int i = 0; i < datas. Rows.Count; i++) {for (int j = 0; J < Datas. Columns.count; J + +) {sheet. Cells[2 + I, j + 1] = datas. ROWS[I][J].                ToString (); }} app.        Visible = true;    System.Threading.Thread.Sleep (500); try {app.            ActiveWorkbook.SaveAs (P); } catch {} app.        Quit (); }///<summary>//third method, using Npoi plugin///</summary>//<returns></returns&        Gt Public Fileresult Downloadexceljizuchaxungenrenxiaofeijilu () {DataTable dt = (DataTable) session["Datata Ble "];//Gets the DataTable data that needs to be exported//the object Npoi to create the Excel file. HSSF. Usermodel.hssfworkbook book = new Npoi. HSSF.            Usermodel.hssfworkbook (); Add a sheet Npoi. Ss. Usermodel.isheet Sheet1 = Book.            Createsheet ("Sheet1"); Add the header title Npoi of the first line to Sheet1. Ss. Usermodel.irow Row1 = Sheet1.            CreateRow (0); Row1.            Rowstyle.fillbackgroundcolor = ""; for (int i = 0; i < dt. Columns.count; i++) {Row1. Createcell (i). Setcellvalue (dt. Columns[i].            ColumnName); }//Gradually write data to sheet1 individual lines for (int i = 0; i < dt. Rows.Count; i++) {Npoi. Ss. Usermodel.irow rowtemp = Sheet1.                CreateRow (i + 1); for (int j = 0; j < dt. Columns.count; J + +) {rowtemp. Createcell (j). Setcellvalue (dt. ROWS[I][J]. ToString ().                Trim ());             }} String strdate = DateTime.Now.ToString ("Yyyymmddhhmmss");//Get current time//write to Client            System.IO.MemoryStream ms = new System.IO.MemoryStream (); Book.            Write (MS); Ms.            Seek (0, Seekorigin.begin);        Return File (MS, "application/vnd.ms-excel", Strdate + "Excel.xls");        }///<summary>//Excel Import//</summary>//<returns></returns> Public ActionResult Gettablefromexcel () {//filestream file = new FileStream (Server.HTMLEncode (reque St. Physicalapplicationpath). ToString () + "excel\\123.xlsx", FileMode.Open, FileAccess.Read);           HttpPostedFileBase fostfile = request.files["File1"];            Stream Streamfile = Fostfile.inputstream;            Hssfworkbook Hssfworkbook = new Hssfworkbook (Streamfile);            Hssfworkbook Hssfworkbook = new Hssfworkbook (Streamfile); Using (Npoi. Ss. Usermodel.isheet sheet = Hssfworkbook.                Getsheetat (0)) {DataTable table = new DataTable (); IRow HeaderRow = sheet.                GetRow (0);//First action header line int cellcount = Headerrow.lastcellnum;//lastcellnum = Physicalnumberofcells int rowCount = sheet.                Lastrownum;//lastrownum = PhysicalNumberOfRows-1//handling header. for (int i = headerrow.firstcellnum; i < Cellcount; i++) {DataColumn column = new Da Tacolumn (Headerrow.getcell (i).                    Stringcellvalue); Table.                Columns.Add (column); } for (int i = (sheet. Firstrownum + 1); I <= RowCount;       i++)         {IRow row = sheet.                    GetRow (i); DataRow datarow = table.                    NewRow (); if (row! = null) {for (int j = row). Firstcellnum; J < Cellcount; J + +) {if (row. Getcell (j) = null) datarow[j] = Getcellvalue (row.                        Getcell (j)); }} table.                Rows.Add (DataRow); } for (int i = 0; i < table. Rows.Count; i++) {//myuploadbll.fordownload (table. ROWS[I][1]. ToString (), table. ROWS[I][2]. ToString (), Convert.toboolean (table.                ROWS[I][3]));        }} return Content (""); }///<summary>//Get the value of the column based on the Excel column type//</summary>//<param name= "cell" >ex CEL columns </param>///<returns></returns> private static STring Getcellvalue (Icell cell) {if (cell = = null) return string.            Empty; Switch (cell. Celltype) {case CellType.BLANK:return string.                Empty; Case CellType.BOOLEAN:return Cell.                Booleancellvalue.tostring (); Case CellType.ERROR:return Cell.                Errorcellvalue.tostring (); Case CellType.NUMERIC:case CellType.Unknown:default:return Cell.                ToString (); Case CellType.STRING:return Cell.                Stringcellvalue; Case CellType.FORMULA:try {hssfformulaevaluator e = new HSS Fformulaevaluator (cell.                        Sheet.workbook);                        E.evaluateincell (cell); return cell.                    ToString ();               } catch {         return cell.                    Numericcellvalue.tostring (); }            }        }    }

. Net MVC Import Export Excel Summary (three export Excel methods, one import Excel method)