Data collection in. NET exported to Excel (supports generics and display field order, customizable display column names)

Abstract: Cough ~ Two years in the park, the work is nearly two years, this is going to write their first article in the garden, mainly because before just small white one, no ink in the belly, now, also drank a little ink ~ Of course ink source is also benefiting from the vast number of procedures ape technology sharing, thanks, and common progress!

Well, don't say much, just cut into the topic that you want to share today.

Topic: Exporting data collections to excel in. Net

Background: Now more and more companies are beginning to focus on big data, the company began to analyze the user's behavior habits or some products to the company's interests, in short, these data can be good to help the company do better, understand their strengths and weaknesses to better serve the user to obtain greater benefits. Now that the company is clear about the direction to do the data, then these data must be shown to people to do data analysis. Of course, this data is only displayed on the page, and does not satisfy every person who wants to see the data, they want to use the data according to their own understanding to do their own analysis. Of course, the most popular of them all want to use Excel to process this data, so we need to export this data to Excel, to meet a large number of user groups.

Content: Because I am an ape-man of the. NET development, here we only analyze how to export data to Excel documents in. NET.

First of all, since we do data, the source of data must be many, so many places need to do data export. For the versatility of the code, we need to define a generic class DataExportHelper.cs code as follows:

   Public classDataexporthelper {/// <summary>        ///Export Excel/// </summary>        /// <typeparam name= "T" >collection type</typeparam>        /// <param name= "Data" >Data Collection</param>        /// <param name= "Exportfilename" >column name to display (class field name)</param>        /// <param name= "ColumnNames" ></param>         Public Static voidExcelexport<t> (list<t> data,stringExportfilename, list<string>columnnames) {StringWriter SW=Getstringwriter (data,columnnames); HttpContext.Current.Response.ContentEncoding= System.Text.Encoding.GetEncoding ("GB2312"); stringFileName =Exportfilename; //determine if the browser is IE then URL-encode the file name            stringUA =HttpContext.Current.Request.UserAgent; if(!string. IsNullOrEmpty (UA)) {if(UA. ToLower (). IndexOf ("MSIE", System.StringComparison.Ordinal) >=0) {FileName=Httputility.urlencode (Exportfilename, Encoding.UTF8); }            }                        stringstr ="attachment;filename="+ FileName +". xls"; HttpContext.Current.Response.AppendHeader ("content-disposition", str); HttpContext.Current.Response.ContentType="Application/ms-excel";            HttpContext.Current.Response.Write (SW);        HttpContext.Current.Response.End (); }        /// <summary>        ///Data String Stitching/// </summary>        /// <typeparam name= "T" >collection type</typeparam>        /// <param name= "Data" >Data Collection</param>        /// <param name= "ColumnNames" >column name to display (class field name)</param>        /// <returns></returns>         Public StaticStringWriter getstringwriter<t> (list<t> data,list<string>columnnames) {StringWriter SW=NewStringWriter (); varType =typeof(T); varProperties =type.            GetProperties (); if(columnnames!=NULL&&columnnames.count >0)            {               //Properties = Properties. Where (t = columnnames.contains (t.name)).                ToArray (); //this loops in order to match the specified field orderPropertyinfo[] Property=NewPropertyinfo[columnnames.count];  for(inti =0; i < Columnnames.count; i++) {Property[i]= Properties. FirstOrDefault (t = T.name = =Columnnames[i]); } Properties=Property ; } SW. Write (SW.            NewLine); if(Data! =NULL)            {                //To Add a column name:                varColName =""; foreach(varPropertyinchproperties) {colname=Property .                    Name; if(property. Customattributes.longcount () >0) {colname=Property . Customattributes.first (). Constructorarguments.first ().                    Value.tostring (); } SW. Write (colname+"\ t"); } SW. Write (SW.                NewLine); foreach(varRowinchdata) {                    foreach(varPropertyinchproperties) SW. Write (property. GetValue (Row,NULL) +"\ t"); Sw. Write (SW.                NewLine); }} SW.            Close (); returnSW; }     }

The necessary comments are in the code, there is not too much to describe, from the above code we can see that this method is not true Excel file, but the use of string line-breaking segmentation to make the Excel program to recognize the format of the table.

The downside is that you can't set a complex style, and opening a file after each download might prompt "Not a valid Excel file, do you want to open it?" "The kind of hint that you just dot is ok." Of course, after opening the file you save as a standard Excel file becomes a real Excel file, so this can be accepted.

The advantage is that this kind of export is only by stitching the string to synthesize the content, so the efficiency of the relative export to the standard Excel file is much more efficient, and there is no formatting function has no big impact, because each business people want to follow their own ideas to edit the style of the table.

Call Mode:

      protected voidCREATEEXECL (int classId) {Studentmanager Marager=NewStudentmanager (); List<StudentModel> list =Marager.            Studentlist (CLASSID); stringFileName ="XX class student data"; varlistfiled =Newlist<string>            {                "Id",                "Name",                "Birthday"            };        Dataexporthelper.excelexport (list, fileName, listfiled); }

The order in which the table columns are displayed after data export is determined by the order of the values in the Listfiled collection, and the values in the collection are the fields in the incoming data collection Studentmodel class. Export data supports generics, note that the field collection to display must be a field in the incoming data type.

Let's take a look at what to do if we want to display the title of each column of the exported table in the corresponding Chinese description. The following is the definition of Studentmodel.

 Public classStudentmodel {[Description ("School Number")]         PublicDateTime Id {Get;Set; } [Description ("name")]         Public DoubleName {Get;Set; } [Description ("Birthday")]         Public DoubleBirthday {Get;Set; } }

As you can see from the above code, the exported Chinese title is obtained from the Description property of the field.

The above is a simple method of data export. If there are errors in this article, or heroes have a better solution, welcome to correct and guidance ~

Note: If reproduced or quoted please specify the source http://www.cnblogs.com/sev7en/p/4021994.html ~

. NET data collection exported to Excel (supports generics and display field order, customizable display column names)