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

Source: Internet
Author: User
Tags httpcontext rowcount

. 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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.