ASP. NET uses the MVC4 framework to export data to an Excel table based on Npoi

Npoi is the. NET version of the POI project. Poi is an open source Java program that reads and writes Microsoft OLE2 component documents such as Excel, Word, and so on.

With Npoi, you can read and write Word/excel documents on a machine that does not have Office installed or the appropriate environment. Npoi is built on the POI 3.x version, which can read and write word/excel documents without Office installation. With Npoi, you can read and write Word/excel documents on a machine that does not have Office installed or the appropriate environment. Npoi is built on the POI 3.x version, which can read and write word/excel documents without Office installation.

Below we use Npoi to make an export function under the MVC4 framework.

(1) in the DAL data access layer, define the data tables that need to be exported, and you can organize the SQL statements according to the fields you need to export.

Public DataTable GetData ()        {            datatable dt = new DataTable ();            using (SqlConnection conn = new SqlConnection (configurationmanager.connectionstrings["ConnStr"]. ToString ()))            {                String sql = "SELECT [loginid],[wageid],[name],[userlimit],[ondutytime],[carshifa],[ Ondutyday],[nightonduty],[allnightonduty],[carallowance],[workovertime],[weekendnightwork],[weekendovernight] From Kaoqinsum where Ondutytime=datename (Yy,getdate ()) + '-' + datename (M,dateadd (M,-1,getdate ())) ";                Conn. Open ();                SqlCommand cmd = new SqlCommand (SQL, conn);                SqlDataAdapter SDA = new SqlDataAdapter (cmd);                Sda. Fill (DT);                Conn. Close ();                return dt;            }        }

(2) in the BLL business logic layer, call the GETDATE () in the data access layer;

   Public DataTable GetDate ()        {            returnnew  salarymanagedal.kaoqinsumdal (). GetData ();        }

(3) In the controller, we write the main code of the export function.

  Public ActionResult Daochu () {DataTable dt = new SALARYMANAGEBLL.KAOQINSUMBLL ().              GetDate ();              1. Instantiate workbook Workbook object Hssfworkbook Hssfworkbook = new Hssfworkbook ();              2. Create Document summary information documentsummaryinformation DSF = Propertysetfactory.createdocumentsummaryinformation (); Dsf.company = "Shenyang Institute of Technology";//company DSF. Category = "Statistics";//class//customproperties custom attribute summaryinformation si = propertysetfactory .              Createsummaryinformation (); Si. Author = "Hospital Office";//author//comments comment createdatetime create time template si. Keywords = "Kaoqin,yuanban";//the keyword SI. Subject = "Kaoqin";//Theme Si. title = "Attendance Summary";//Heading Si. Revnumber = "1.0";//version number//3, Workbook object Hssfworkbook that the written document Digest is assigned.              Documentsummaryinformation = DSF; Hssfworkbook.              SummaryInformation = si; 4. Create Sheet HssfsheET Sheet1 = (hssfsheet) hssfworkbook.              Createsheet ("Sheet1"); Hssfsheet Sheet2 = (hssfsheet) hssfworkbook.              Createsheet ("Sheet2"); Hssfsheet Sheet3 = (hssfsheet) hssfworkbook.              Createsheet ("Sheet3"); 5. Create the header footer Sheet1.createrow (0). Createcell (1).              Setcellvalue (123);              Sheet1.Header.Center = "Statistical data";              Sheet1.Header.Left = "Logo.png";              Sheet1.Header.Right = "Zhguaddress";              Sheet1.Footer.Center = "page";              6. Title String yeartime = Time (); Hssfcell Fcell = (Hssfcell) sheet1.createrow (0). Createcell (0);//First line Fcell. Setcellvalue ("Shenyang Institute of Technology" + Yeartime + "attendance summary table");//text//Merge cell sheet1.addmergedregion (new Cellrangead Dress (0, 0, 0, 13));//2.0 use 2.0 below for region//heading style Hssfcellstyle Fcellstyle = (hssfcellstyle) hssfwo Rkbook.              Createcellstyle (); Hssffont Ffont = (hssffont) hssfworkbook.              CreateFont (); FFont.              Fontheight = 20 * 20; Ffont.              FontName = "Song Body"; Ffont.              Color = HSSFColor.BLUE.index;              Fcellstyle.setfont (Ffont); Fcellstyle.verticalalignment = Npoi. Ss. usermodel.verticalalignment.center;//vertical Alignment fcellstyle.alignment = Npoi. Ss. usermodel.horizontalalignment.center;//Horizontal Alignment Fcell.              CellStyle = Fcellstyle; 7. Set cell format to create cell/* Analog set 7 column */Hssfdataformat DataFormat = (hssfdataformat) hssfworkbook. Createdataformat ();//data format Hssffont font = (Hssffont) hssfworkbook. CreateFont ();//Data font fonts. Color = HSSFColor.BLACK.index; Color font. Isitalic = false;//Italic font. Isstrikeout = false;//bold font.  FontName = "Arial";//fonts//must be changed in the loop output data when the specified type needs to call SqlDbType more complex//id int type Hssfcell Cell1 = (Hssfcell) sheet1.createrow (1). Createcell (0); Create cell Hssfcellstyle CellStyle1 = (hssfcellstyle) HSSFWOrkbook.              Createcellstyle ();//cell style Cellstyle1.dataformat = Hssfdataformat.getbuiltinformat (""); Cellrangeaddresslist ranglist1 = new Cellrangeaddresslist (0, 65535, 0, 0);//Set Limited type//Dvconstraint Constra Int1 = Dvconstraint.createnumericconstraint (DVConstraint.ValidationType.INTEGER,              DVConstraint.OperatorType.BETWEEN, "0", "100");//constraint Cellstyle1.setfont (font); Cell1.              CellStyle = CellStyle1; Cell1.              Setcellvalue (""); Name Hssfcell cell2 = (Hssfcell) sheet1.createrow (1).              Createcell (1); Hssfcellstyle cellStyle2 = (hssfcellstyle) hssfworkbook.              Createcellstyle ();              Cellstyle2.dataformat = Hssfdataformat.getbuiltinformat ("");              Cellstyle2.setfont (font); Cell2.              CellStyle = CellStyle2; Cell2.              Setcellvalue (""); Phone Hssfcell Cell3 = (Hssfcell) sheet1.createrow (1).              Createcell (2); Hssfcellstyle CellstyLe3 = (hssfcellstyle) hssfworkbook.              Createcellstyle ();              Cellstyle3.dataformat = Hssfdataformat.getbuiltinformat ("");              Cellstyle3.setfont (font); Cell3.              CellStyle = CellStyle3; Cell3.              Setcellvalue (""); Address Hssfcell Cell4 = (Hssfcell) sheet1.createrow (1).              Createcell (3); Hssfcellstyle cellStyle4 = (hssfcellstyle) hssfworkbook.              Createcellstyle ();              Cellstyle4.dataformat = Hssfdataformat.getbuiltinformat ("");              Cellstyle4.setfont (font); Cell4.              CellStyle = cellStyle4; Cell4.              Setcellvalue (""); Status Hssfcell cell5 = (Hssfcell) sheet1.createrow (1).              Createcell (4); Hssfcellstyle cellStyle5 = (hssfcellstyle) hssfworkbook.              Createcellstyle ();              Cellstyle5.dataformat = Hssfdataformat.getbuiltinformat ("");              Cellstyle5.setfont (font); Cell5.              CellStyle = CellStyle5; Cell5. Setcellvalue ("");              Balance Hssfcell cell6 = (Hssfcell) sheet1.createrow (1).              Createcell (5); Hssfcellstyle cellStyle6 = (hssfcellstyle) hssfworkbook.              Createcellstyle (); Cell6.              Setcellvalue ("");              Cellstyle6.dataformat = Hssfdataformat.getbuiltinformat ("");              Cellstyle6.setfont (font); Cell6.              CellStyle = CellStyle6; CreateDate Hssfcell cell7 = (Hssfcell) sheet1.createrow (1).              Createcell (6); Hssfcellstyle CellStyle7 = (hssfcellstyle) hssfworkbook.              Createcellstyle ();              Cellstyle7.dataformat = Hssfdataformat.getbuiltinformat ("");              Cellstyle7.setfont (font); Cell7.              CellStyle = CellStyle7; Cell7.              Setcellvalue (""); Hssfcell Cell8 = (Hssfcell) sheet1.createrow (1).              Createcell (7); Hssfcellstyle CellStyle8 = (hssfcellstyle) hssfworkbook.              Createcellstyle ();     Cellstyle8.dataformat = Hssfdataformat.getbuiltinformat ("");         Cellstyle8.setfont (font); Cell8.              CellStyle = CellStyle8; Cell8.              Setcellvalue (""); Hssfcell cell9 = (Hssfcell) sheet1.createrow (1).              Createcell (8); Hssfcellstyle cellStyle9 = (hssfcellstyle) hssfworkbook.              Createcellstyle ();              Cellstyle9.dataformat = Hssfdataformat.getbuiltinformat ("");              Cellstyle9.setfont (font); Cell9.              CellStyle = CellStyle9; Cell9.              Setcellvalue (""); Hssfcell Cell10 = (Hssfcell) sheet1.createrow (1).              Createcell (9); Hssfcellstyle CellStyle10 = (hssfcellstyle) hssfworkbook.              Createcellstyle ();              Cellstyle10.dataformat = Hssfdataformat.getbuiltinformat ("");              Cellstyle10.setfont (font); Cell10.              CellStyle = CellStyle10; Cell10.              Setcellvalue (""); Hssfcell cell11 = (Hssfcell) sheet1.createrow (1).              Createcell (10); Hssfcellstyle CellStyle11 = (hssfcellstyle) hssfworkbook.    Createcellstyle ();          Cellstyle11.dataformat = Hssfdataformat.getbuiltinformat ("");              Cellstyle11.setfont (font); Cell11.              CellStyle = CellStyle11; Cell11.              Setcellvalue (""); Hssfcell cell12 = (Hssfcell) sheet1.createrow (1).              Createcell (11); Hssfcellstyle cellStyle12 = (hssfcellstyle) hssfworkbook.              Createcellstyle ();              Cellstyle12.dataformat = Hssfdataformat.getbuiltinformat ("");              Cellstyle12.setfont (font); Cell12.              CellStyle = CellStyle12; Cell12.              Setcellvalue (""); Hssfcell cell13 = (Hssfcell) sheet1.createrow (1).              Createcell (12); Hssfcellstyle cellStyle13 = (hssfcellstyle) hssfworkbook.              Createcellstyle ();              Cellstyle13.dataformat = Hssfdataformat.getbuiltinformat ("");              Cellstyle13.setfont (font); Cell13.              CellStyle = CellStyle13; Cell13.              Setcellvalue (""); 8. Create cell to add data hssfrow r = (hssfrow) Sheet1.createrow (1);//The second row header for (int i = 0; i < dt. Columns.count; i++) {R.createcell (i). Setcellvalue (dt. Columns[i].              ToString ()); } if (dt. Rows.Count > 0) {for (int i = 0; i < dt. Rows.Count;  i++) {Hssfrow row = (hssfrow) sheet1.createrow (i + 2);//write line for (int j = 0; j < dt.) Columns.count; J + +)//write to column {if (dt. COLUMNS[J]. ColumnName = = "balance") {row. Createcell (j).                              Cellstyle.dataformat = Hssfdataformat.getbuiltinformat ("0.00"); Row. Createcell (j). Setcellvalue (convert.todouble (dt. ROWS[I][J].                          ToString ())); } else {row. Createcell (j). Setcellvalue (dt. ROWS[I][J].                          ToString ());                }      }}}//9, sum sum function Hssfcell cesum = (Hssfcell) sheet1.creater ow (Sheet1.lastrownum + 1). Createcell (5);//The Last line +1 lines equals the Total row Hssfcell Cebegin = (Hssfcell) sheet1.getrow (2). Getcell (5);//start Hssfcell Ceend = (Hssfcell) sheet1.getrow (sheet1.lastrownum-1). Getcell (5);//End cesum.              Setcellformula ("sum (" + geta_z (5) + 3 + ":" + geta_z (5) + Sheet1.lastrownum + ")");              FileStream fs = new FileStream (Server.MapPath ("~/exportfiles/" + yeartime + "absence information. xls"), FileMode.Create); Hssfworkbook.              Write (FS); Fs.              Close ();              Response.Write ("Export Complete");                return View ();          return; }

　　

public string geta_z (double p)          {              string[] str = {"0:a", "1:b", "2:c", "3:d", "4:e", "5:f", "6:g", "7:h", "8:i", " 9:j "," 10:k "," 11:l "," 12:m "," 13:n "," 14:o "," 15:p "," 16:q "," 17:r "," 18:s "," 19:t "," 20:u "," 21:v "," 22:w "," 23:x "," 24: Y "," 25:z "};              for (int i = 0; i < str. Length; i++)              {                  if (p.tostring () = = Str[i]. Split (': ') [0]. ToString ())                  {                      return str[i]. Split (': ') [1]. ToString ();                  }              }              Return "";          }

Due to the time constraints in the actual project. Defines a time () with a return value type of string;

  Public String time ()          {              String time = "";              DateTime dt = DateTime.Now;              int year = dt. Year;              int month = dt. Month;              if (1 < month && > month)              {Time                  = year + "-";                  Time + = "0";                  Time = time + convert.tostring (month-1);              }              if (month = = 1)              {year                  = dt. Year-1;                  Time = convert.tostring (year) + "-";                  Time + = "a";              }              return time;          }

(4) Define a button in the foreground UI interface to implement the Daochu () in the trigger controller.

<a href= "#" id= "Daochu" class= "Easyui-linkbutton" data-options= "iconcls: ' Icon-search '" > Export Data </a>

Defines the events that are triggered by the id= "Daochu".

　　

$ ("#daochu"). Click (function () {Getdaochu = "/kaoqinsum/daochu";
method to submit the execution controller Initdatagrid ("#dg", Colums, Getdaochu);
Create a return value date that is used to determine the time of the export and to export the data for the corresponding month.                var date = new Date ();                var year = Date.getfullyear ();                var month = Date.getmonth ();                var clock;                    if (0 < month <) {Clock = year + "-";                    Clock + = "0";                Clock + = month;                    } if (month = = 0) {year = Date.getfullyear ()-1;                    Clock = year + "-";                Clock + = "12"; if ($ ("#OnDutyTime"). Datebox (' GetValue ')! = "") {geturl3 = ":                    /exportfiles/"+ $ (" #OnDutyTime "). Datebox (' getValue ') +" attendance information. xls ";;                window.open (GETURL3); if ($ ("#OnDutyTime"). Datebox (' getValue ') = = "") {Geturl2 = ":                    /exportfiles/"+ clock +" attendance information. xls ";                window.open (GETURL2); }            })

　　

PS: Exported Excel table http://pan.baidu.com/s/1ntp2izn   Password: Mxmo