C # Development --- use features to export custom data to Excel,

C # Development --- use features to export custom data to Excel,

 There are many ways to export an Excel file from C # on the Internet. But it is not automated enough to use it. So it took some time to use the features to make a more common export method. You only need to automatically export the desired data based on the object class.

 

1. Install Aspose. Cells on NuGet or use the built-in class library of Microsoft.

 2. instance type of data to be exported:

Using System. componentModel; using System. reflection; using System. runtime. serialization; public class OrderReport {[DisplayName ("Order Number")] public string orderNo {get; set;} [IgnoreDataMember] public DateTime orderTime {get; set ;} [DisplayName ("Order Time")] public String orderTime_fomart {get {return orderTime. toShortDateString () ;}} [DisplayName ("Commodity Code")] public string itemCode {get; set;} [DisplayName ("commodity name")] public string itemName {get; set ;}}View Code Add [DisplayName ("Order Number")] to the definition object to export it to Excel to generate a column name. You do not need to write one-to-one column names in the export. The [IgnoreDataMember] attribute is used for export. It is ignored and does not need to be exported. For more information about the features, see MSDN.

3. Export method:

/// <Summary> /// export class /// </summary> public class ExportHandle {/// <summary> // suspend order report export /// </ summary> public static void execExportOrderReport () {var orderReportList = new List <OrderReport> () {new OrderReport () {orderNo = "XD00001", orderTime = DateTime. now, itemCode = "G001", itemName = ""}, new OrderReport () {orderNo = "XD00002", orderTime = DateTime. now, itemCode = "G002", itemName = ""}, new Ord ErReport () {orderNo = "XD00003", orderTime = DateTime. now, itemCode = "G003", itemName = "cut cake"}, new OrderReport () {orderNo = "XD00004", orderTime = DateTime. now, itemCode = "G004", itemName = "prawns"}, new OrderReport () {orderNo = "XD00005", orderTime = DateTime. now, itemCode = "G005", itemName = "king crab" }}; string path = "OrderReport.xlsx"; Console. writeLine ("start to execute export"); OutDataToExcel (orderReportList, "Order Report", path); Cons Ole. writeLine ("export completed: location" + path );} /// <summary> /// export method /// </summary> /// <typeparam name = "T"> </typeparam> /// <param name = "list"> exported data list </param> /// <param name = "title"> data category title </param> /// <param name = "path"> export the excel storage path </param> public static void OutDataToExcel <T> (List <T> list, string title, string path) {Workbook workbook = new Workbook (); // Worksheet sheet = workbook. worksheets [0]; // Worksheet Sheet. isGridlinesVisible = false; // remove the initial cell line Cells = sheet. cells; // cell // set Style styleTitle = workbook for the title. createStyle (); // Add styleTitle. horizontalAlignment = TextAlignmentType. center; // text Center styleTitle. font. name = ""; // text font styleTitle. font. size = 18; // text Size styleTitle. font. isBold = true; // bold // Style 1 The date Style style1 = workbook under the title. createStyle (); // New Style style1.HorizontalAlignment = TextAli GnmentType. center; // text Center style1.Font. name = ""; // text font style1.Font. size = 12; // text Size // Style 2 column name Style style2 = workbook. createStyle (); // New Style style2.HorizontalAlignment = TextAlignmentType. center; // text Center style2.Font. name = ""; // text font style2.Font. size = 12; // text Size style2.Font. isBold = true; // bold style2.Borders [BorderType. leftBorder]. lineStyle = CellBorderType. thin; style2.Borders [BorderType. rightBor Der]. lineStyle = CellBorderType. thin; style2.Borders [BorderType. topBorder]. lineStyle = CellBorderType. thin; style2.Borders [BorderType. bottomBorder]. lineStyle = CellBorderType. thin; // Style 3 data Style style3 = workbook. createStyle (); // New Style style3.HorizontalAlignment = TextAlignmentType. center; // text Center style3.Font. name = ""; // text font style3.Font. size = 10; // text Size style3.Borders [BorderType. leftBorder]. L IneStyle = CellBorderType. thin; style3.Borders [BorderType. rightBorder]. lineStyle = CellBorderType. thin; style3.Borders [BorderType. topBorder]. lineStyle = CellBorderType. thin; style3.Borders [BorderType. bottomBorder]. lineStyle = CellBorderType. thin; if (list. count = 0) return; var t = list. first (). getType (); // get the attributes of the List class // ignore the var properties field of [IgnoreDataMemberAttribute] through reflection filtering = t. getProperties (). wher E (x => x. getCustomAttribute <IgnoreDataMemberAttribute> () = null); int Colnum = properties. count (); // Number of table columns int Rownum = list. count; // Number of table rows // generate Row 1 header row cells. merge (0, 0, 1, Colnum); // Merge cells [0, 0]. putValue (title); // enter the content of cells [0, 0]. setStyle (styleTitle); cells. setRowHeight (0, 38); // Row Height // generate Row 2 Date cells. merge (1, 0, 1, Colnum); // Merge cells [1, 0]. putValue (DateTime. now. tow.datestring (); // enter the content of cells [1, 0]. setStyle (style1); cells. setRowHeight (1, 20); // Row Height // column name and data row int I = 0; foreach (var item in properties) {var itemType = t. getProperty (item. name); var colName = itemType. getCustomAttribute <DisplayNameAttribute> (). displayName; // obtain the DisplayName attribute value of the field through reflection. cells [2, I]. putValue (colName); cells [2, I]. setStyle (style2); cells. setColumnWidth (I, colName. length * 3); // set the column width to int k = 0; foreach (var rowdata in List) {// Add data object value = rowdata. GetType (). GetProperty (item. Name). GetValue (rowdata, null); string ss = value = null? "": Value. toString (); cells [3 + k, I]. putValue (ss); cells [3 + k, I]. setStyle (style3); cells. setRowHeight (3 + k, 18); // set the Row Height k ++;} I ++;} workbook. save (path); // generate an Excel file }}View Code The export method OutDataToExcel <T> (List <T> list, Enum en, string path) uses generic parameters to automatically export any object list. Var properties = t. GetProperties (). Where (x => AttributeAccessor. GetAttribute <IgnoreDataMemberAttribute> (x) = null); Use the lamda expression to filter out the passed object attributes. It is not an attribute field of IgnoreDataMemberAttribute

Foreach (var item in properties) {} traverses object attributes, which is equivalent to reading data cyclically by DataTable

Object value = rowdata. GetType (). GetProperty (item. Name). GetValue (rowdata, null); get the attribute value through the attribute Name.

Use the preceding two steps to automatically

 

 

}

4. Export result:

In summary, general export is achieved through features. You only need to set the fields and feature values of related classes to customize the export.