1. ASP. net mvc use EPPlus to export data to Excel. 1. asp. netepplus

1. ASP. net mvc use EPPlus to export data to Excel. 1. asp. netepplus

I haven't written a blog for a long time. I want to update it today. Today we want to learn how to export data to an Excel file. Here I am using the free open-source Epplus component.

Source code download: https://github.com/caofangsheng93/ExcelExportInMvc

Introduction

This article describes how to export data to an Excel file. Most backend programs have the report function: export data displayed in the Grid to an Excel file, this article uses the EPPlus component.

EPPlus is a class library that operates Excel tables in the format of OOXML [Open Extended Markup Language. OOXML is developed by Microsoft. Microsoft Office is supported by default.

Open source Website: http://epplus.codeplex.com/

Body

The above is our project.

First, we need to introduce: EPPlus.

I have introduced it here.

When we use ORM in a program, we usually save the data in the collection. The data in the set cannot be exported directly to an Excel file. This is why we need to convert List to able first.

Figure 1: Procedure for exporting an Excel file

 

 

To complete this article, we need four steps:

1. Data: Here I use static data to ensure that this article is as easy as possible.

2. Set: static data is saved in the set.

3. DataTable: Convert the data of the generic set and save it to the DataTable.

4. Export File: DataTable export to Excel

 

First, we create a class:

Public class Student {public int ID {get; set;} public string Name {get; set;} public string Sex {get; set;} public int Age {get; set ;} public string Email {get; set ;}}Student

 

Then create a static data class:

Public class StaticDataOfStudent {public static List <Student> ListStudent {get {return new List <Student> () {new Student () {ID = 1, Name = "Cao ", sex = "male", Email = "caocao@163.com", Age = 24}, new Student () {ID = 2, Name = "Li Yifeng", Sex = "female ", email = "lilingjie@sina.com.cn", Age = 24}, new Student () {ID = 3, Name = "Zhang Sanfeng", Sex = "male", Email = "zhangsanfeng@qq.com ", age = 224}, new Student () {ID = 4, Name = "Sun Quan", Sex = "male", Email = "sunquan@163.com ", age = 1224 },};}}}StaticDataOfStudent

 

Then we export the Excel help class:

/// <Summary> /// Excel export help class /// </summary> public class ExcelExportHelper {public static string ExcelContentType {get {return "application/vnd. openxmlformats-officedocument.spreadsheetml.sheet ";}} /// <summary> /// List to DataTable /// </summary> /// <typeparam name = "T"> </typeparam> /// <param name = "data"> </param> // <returns> </returns> public static DataTable ListToDataTable <T> (List <T> data) {PropertyDescriptorCollection properties = TypeDescriptor. getProperties (typeof (T); DataTable dataTable = new DataTable (); for (int I = 0; I <properties. count; I ++) {PropertyDescriptor property = properties [I]; dataTable. columns. add (property. name, Nullable. getUnderlyingType (property. propertyType )?? Property. propertyType);} object [] values = new object [properties. count]; foreach (T item in data) {for (int I = 0; I <values. length; I ++) {values [I] = properties [I]. getValue (item);} dataTable. rows. add (values);} return dataTable ;} /// <summary> /// export Excel /// </summary> /// <param name = "dataTable"> data source </param> /// <param name = "heading"> workbook Worksheet </param> // <param name = "showSrNo"> // whether to display row editing </Param> /// <param name = "columnsToTake"> column to be exported </param> /// <returns> </returns> public static byte [] ExportExcel (DataTable dataTable, string heading = "", bool showSrNo = false, params string [] columnsToTake) {byte [] result = null; using (ExcelPackage package = new ExcelPackage ()) {ExcelWorksheet workSheet = package. workbook. worksheets. add (string. format ("{0} Data", heading); int startRowFrom = stri Ng. IsNullOrEmpty (heading )? 1: 3; // The start row // whether to display the row number if (showSrNo) {DataColumn dataColumn = dataTable. columns. add ("#", typeof (int); dataColumn. setOrdinal (0); int index = 1; foreach (DataRow item in dataTable. rows) {item [0] = index; index ++ ;}// Add Content Into the Excel File workSheet. cells ["A" + startRowFrom]. loadFromDataTable (dataTable, true); // autofit width of cells with small content int columnIndex = 1; foreach (DataColumn item in dataTable. columns) {ExcelRange columnCells = workSheet. cells [workSheet. dimension. start. row, columnIndex, workSheet. dimension. end. row, columnIndex]; int maxLength = columnCells. max (cell => cell. value. toString (). count (); if (maxLength <150) {workSheet. column (columnIndex ). autoFit () ;}columnindex ++;} // format header-bold, yellow on black using (ExcelRange r = workSheet. cell S [startRowFrom, 1, startRowFrom, dataTable. columns. count]) {r. style. font. color. setColor (System. drawing. color. white); r. style. font. bold = true; r. style. fill. patternType = OfficeOpenXml. style. excelFillStyle. solid; r. style. fill. backgroundColor. setColor (System. drawing. colorTranslator. fromHtml ("# 1fb5ad");} // format cells-add borders using (ExcelRange r = workSheet. cells [startRowFrom + 1, 1, start RowFrom + dataTable. rows. count, dataTable. columns. count]) {r. style. border. top. style = ExcelBorderStyle. thin; r. style. border. bottom. style = ExcelBorderStyle. thin; r. style. border. left. style = ExcelBorderStyle. thin; r. style. border. right. style = ExcelBorderStyle. thin; r. style. border. top. color. setColor (System. drawing. color. black); r. style. border. bottom. color. setColor (System. drawing. color. black); r. sty Le. border. left. color. setColor (System. drawing. color. black); r. style. border. right. color. setColor (System. drawing. color. black);} // removed ignored columns for (int I = dataTable. columns. count-1; I> = 0; I --) {if (I = 0 & showSrNo) {continue;} if (! ColumnsToTake. Contains (dataTable. Columns [I]. ColumnName) {workSheet. DeleteColumn (I + 1) ;}} if (! String. isNullOrEmpty (heading) {workSheet. cells ["A1"]. value = heading; workSheet. cells ["A1"]. style. font. size = 20; workSheet. insertColumn (1, 1); workSheet. insertRow (1, 1); workSheet. column (1 ). width = 5;} result = package. getAsByteArray ();} return result ;} /// <summary> /// export Excel /// </summary> /// <typeparam name = "T"> </typeparam> /// <param name = "data"> </param> // <param name = "heading"> </param> // <param name = "isShowSlNo"> </param> // /<param name = "ColumnsToTake"> </param> // <returns> </returns> public static byte [] ExportExcel <T> (List <T> data, string heading = "", bool isShowSlNo = false, params string [] ColumnsToTake) {return ExportExcel (ListToDataTable <T> (data), heading, isShowSlNo, ColumnsToTake );}}

 

So far, the code of the backend server has basically been completed. Now we can design our front-end code:

We create a ViewModel to display data:

public class StudentViewModel    {        public List<Student> ListStudent        {            get             {                return StaticDataOfStudent.ListStudent;            }        }    }

Then create a controller:

 public class HomeController : Controller    {        // GET: Home        public ActionResult Index()        {            StudentViewModel model = new StudentViewModel();            return View(model);        }        public FileContentResult ExportToExcel()        {            List<Student> lstStudent = StaticDataOfStudent.ListStudent;            string[] columns = { "ID", "Name","Age"};            byte[] filecontent = ExcelExportHelper.ExportExcel(lstStudent,"", false, columns);            return File(filecontent, ExcelExportHelper.ExcelContentType, "MyStudent.xlsx");          }    }

Our view code:

@ Model ExportToExcel. models. studentViewModel @ {ViewBag. title = "Excel File Export" ;}< div class = "panel"> <div class = "panel-heading"> <a href = "@ Url. action ("ExportToExcel ") "class =" btn-primary "> Export </a> </div> <div class =" panel-body "> <table class =" table-striped table- bordered "> <thead> <tr> <th> ID </th> <th> Name </th> <th> Sex </th> <th> Age </th> <th> Email </th> </tr> </thead> <tbody> @ foreach (var item in Model. listStudent) {<tr> <td> @ item. ID </td> <td> @ item. name </td> <td> @ item. sex </td> <td> @ item. age </td> <td> @ item. email </td> </tr >}</tbody> </table> </div>

 

:

After you click "Export", the Excel file is exported to the browser.

 

 

 Summary: This export help class allows you to customize the exported columns.

String [] columns = {"ID", "Name", "Age"}; byte [] filecontent = ExcelExportHelper. exportExcel (lstStudent, "", false, columns); return File (filecontent, ExcelExportHelper. excelContentType, "MyStudent.xlsx ");
Here I just export these three columns.

Reference: http://www.cnblogs.com/liudeyun/p/3535740.html