Asp.net uses the MVC4 framework to export data to an Excel table based on NPOI, mvc4npoi

Asp.net uses the MVC4 framework to export data to an Excel table based on NPOI, mvc4npoi

NPOI is the. NET version of the POI project. POI is an open-source Java project for reading and writing Microsoft OLE2 component documents such as Excel and WORD.

With NPOI, you can read and write WORD/EXCEL documents on a machine without an Office or corresponding environment.NPOIIs built onPOI 3.xVersion above, it can be installedOfficeIn the caseWord/ExcelDocumentation for read/write operations. With NPOI, you can read and write WORD/EXCEL documents on a machine without an Office or corresponding environment.NPOIIs built onPOI 3.xVersion above, it can be installedOfficeIn the caseWord/ExcelDocumentation for read/write operations.

Next we will use NPOI to create an export function under the MVC4 framework.

(1) At the DAL data access layer, define the data tables to be exported. You can organize SQL statements according to the fields to be exported.

 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) At the BLL business logic layer, call GetDate () in the data access layer ();

  public DataTable GetDate()        {            return new SalaryManageDAL.KaoqinsumDAL().GetData();        }

(3) In the controller, let's write the main code of the export function.

Public ActionResult DaoChu () {DataTable dt = new SalaryManageBLL. kaoqinsumBLL (). getDate (); // 1. instantiate the workbook object HSSFWorkbook hssfworkbook = new HSSFWorkbook (); // 2. Create the document summary information DocumentSummaryInformation dsf = PropertySetFactory. createDocumentSummaryInformation (); dsf. company = "Shenyang Institute of Technology"; // Company dsf. category = "Statistics"; // Category // CustomProperties custom attribute SummaryInformation si = PropertySetFactory. createSumma RyInformation (); si. author = ""; // Author // Comments: CreateDateTime creation time Template si. keywords = "kaoqin, yuanban"; // key word si. subject = "kaoqin"; // theme si. title = "Attendance summary"; // Title si. revNumber = "1.0"; // version number // 3. Assign the prepared document abstract to the workbook object hssfworkbook. documentSummaryInformation = dsf; hssfworkbook. summaryInformation = si; // 4. Create Sheet HSSFSheet Sheet1 = (HSSFSheet) hssfworkbook. createSheet ("Sheet1"); HSSFSheet She Et2 = (HSSFSheet) hssfworkbook. createSheet ("Sheet2"); HSSFSheet Sheet3 = (HSSFSheet) hssfworkbook. createSheet ("Sheet3"); // 5. Create the header and 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 ). creat ECell (0); // The first fcell. setCellValue ("Shenyang Institute of Technology" + yeartime + "Attendance summary table"); // text // merge cell Sheet1.AddMergedRegion (new CellRangeAddress (0, 0, 0, 13 )); // 2.0 use Region below 2.0 // Title style HSSFCellStyle fCellStyle = (HSSFCellStyle) hssfworkbook. createCellStyle (); HSSFFont ffont = (HSSFFont) hssfworkbook. createFont (); ffont. fontHeight = 20*20; ffont. fontName = ""; ffont. color = HSSFColor. BLUE. index; fCellStyle. setFont (f Font); fCellStyle. verticalAlignment = NPOI. SS. userModel. verticalAlignment. CENTER; // vertical alignment fCellStyle. alignment = NPOI. SS. userModel. horizontalAlignment. CENTER; // horizontal alignment fcell. cellStyle = fCellStyle; // 7. Set the cell format to create a cell./* simulate setting 7 columns */HSSFDataFormat dataformat = (HSSFDataFormat) hssfworkbook. createDataFormat (); // data format HSSFFont font = (HSSFFont) hssfworkbook. createFont (); // data font. color = HSSFColor. BLACK. index; // Color font. isItalic = false; // italic font. isStrikeout = false; // bold font. fontName = ""; // font // it is essential to change the type specified when loop output data needs to be called sqlDbType is 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 CellRan GeAddressList (0, 65535, 0, 0); // sets a limited type // DVConstraint constraint1 = 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. C ReateCellStyle (); 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.Cell Style = 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.Da TaFormat = 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 (""); HSSFCe Ll 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. createCellSt Yle (); 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 = cellSt Yle10; 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 cellS Tyle12 = (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 a cell and add data HSSFRow r = (HSSFRow) Sheet1.CreateRow (1 ); // The title of the second line 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 row for (int j = 0; j <dt. co Lumns. count; j ++) // write 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. The SUM function HSSFCell cesum = (HSSFCell) Sheet1.CreateRow (Sheet1.LastRowNum + 1 ). createCell (5); // The most The next row + 1 is equal to 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 +" Attendance info. xls "), FileMode. create); hssfworkbook. write (fs); fs. close (); // Response. write ("exported"); 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 "";          }

Because the actual project requires time constraints. Defines a time () with the 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 && 10 > month)              {                  time = year + "-";                  time += "0";                  time = time + Convert.ToString(month - 1);              }              if (month == 1)              {                  year = dt.Year - 1;                  time = Convert.ToString(year) + "-";                  time += "12";              }              return time;          }

(4) define a button on the front-end UI to trigger DaoChu () in the Controller ();

<A href = "#" id = "daochu" class = "easyui-linkbutton" data-options = "iconCls: 'icon-search'"> export data </a>

Define the event triggered by id = "daochu.

　　

$ ("# Daochu"). click (function () {getdaochu = "/Kaoqinsum/DaoChu ";
// Method of submitting the execution controller initDataGrid ("# dg", colums, getdaochu );
// Create a return value date to determine the time when exporting and export the data of the corresponding month. Var date = new Date (); var year = date. getFullYear (); var month = date. getMonth (); var clock; if (0 <month <10) {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 info. xls"; window. open (geturl3);} if ($ ("# OnDutyTime "). datebox ('getvalue') = "") {geturl2 = ".. /ExportFiles/"+ clock +" Attendance info. xls "; window. open (geturl2 );}})

　　

PS: exported Excel table http://pan.baidu.com/s/1ntp2izn password: mxmo

How can I use NPOIdll in ASPnet to export EXCEL files? C # development language is preferred.

You don't need to use this. How fast do you write yourself,
I used this file. It was written in CSV format and may be opened in EXCEL. You can save it as xls.

/// <Summary>
/// Convert the DataTable to a string
/// </Summary>
/// <Param name = "dt"> </param>
/// <Returns> </returns>
Public static string DtToString (DataTable dt)
{
String data = "";
Try
{
Foreach (DataColumn column in dt. Columns)
{
Data + = column. ColumnName + ",";
}
Data + = "\ r \ n ";

// Write data
Foreach (DataRow row in dt. Rows)
{
Foreach (DataColumn column in dt. Columns)
{
String t = row [column]. ToString ();
If (! String. IsNullOrEmpty (t ))
{
T = t. Replace (",","");
T = t. Replace ("\ r ","");
T = t. Replace ("\ n ","");
T = HttpContext. Current. Server. HtmlEncode (t );
Data + = t + ",";
}
Else
Data + = ",";
}
Data + = "\ r \ n ";
}
Data + = "\ r \ n ";
}
Catch {}
Return data;
}

/// <Summary>
/// Convert the string to CSV format for output
/// </Summary>
/// <Param name = "content"> string content & lt ...... remaining full text>

How does Aspnet use npoi to read excel content?

There are examples in NPOI
To reference
Using NPOI;
Using NPOI. HPSF;
Using NPOI. HSSF;
Using NPOI. HSSF. UserModel;
Using NPOI. SS. UserModel;
Using NPOI. POIFS;
Using NPOI. Util;
Then, you can read this information based on the examples in NPOI.
PS. It is best to set all words in EXCEL to "text ".
References: I have used my work