C # programming Excel import, export (source code download) (bottom)

3. Excel Export

3.1 Export process

3.2 Npoi Operation code

Description: Convert list<t> to Excel

Steps:

① Create a workbook (Workbook);

② Create a worksheet (Sheet) on the workbook;

③ creates the first row on the worksheet, the first behavior column header, and then writes the Cellheard value (as the column name).

④ loops through the List<t> collection, creates one row per loop, and then, based on the Cellheard key (property name), takes the value from the entity object in the list<t> to the cell in turn.

Code:

<summary>///entity class collection exported to excle2003///</summary>///<param name= "Cellheard" > Cell header Key and value:{{" UserName "," name "}, {" Age "," ages "}};</param>///<param name=" enList "> Data source </param>///<param name=" sh Eetname "> Sheet name </param>///<returns> file Download address </returns>public static string EntityListToExcel2003 (dictionary<string, string> Cellheard, IList enList, string sheetname) {try {Stri Ng FileName = sheetname + "-" + DateTime.Now.ToString ("yyyymmddhhmmssfff") + ". xls"; File name string URLPath = "upfiles/excelfiles/" + fileName; File download URL address, supply front desk download string filePath = HttpContext.Current.Server.MapPath ("\ \" + URLPath);        File path//1. Detects if a folder exists and creates a folder if it does not exist string directoryname = Path.getdirectoryname (FilePath); if (!        Directory.Exists (directoryname)) {directory.createdirectory (directoryname); }//2. Resolve cell header, set the Chinese name of the cell Hssfworkbook workbook = new Hssfworkbook(); Workbook Isheet sheet = workbook. Createsheet (SheetName); Worksheet IRow row = sheet.        CreateRow (0);        list<string> keys = CellHeard.Keys.ToList (); for (int i = 0; i < keys. Count; i++) {row. Createcell (i). Setcellvalue (Cellheard[keys[i]); The value of the column named Key}//3.List object value is assigned to excel cell int rowIndex = 1; The assignment starts from the second line (the first row is set to the cell header), and the foreach (Var en in enList) {IRow rowtmp = sheet.            CreateRow (RowIndex); for (int i = 0; i < keys. Count; i++)//Gets the value of the specified property of the object, based on the specified property name {string cellvalue = ""; The value of the Cell object properotyvalue = null; The value of the System.Reflection.PropertyInfo property is properotyinfo = null; Property Information//3.1 If the name of the property header contains '. ', it is the attribute in the subclass, then it is necessary to traverse the subclass, Eg:UserEn.UserName if (Keys[i]. IndexOf (".") >= 0) {//3.1.1 Parsing sub-class attributes (only 1-tier subclasses are parsed here, multi-layer subclasses not processed) string[] p Roperotyarray = Keys[i].                    Split (new string[] {"."}, Stringsplitoptions.removeemptyentries); String subclassname = Properotyarray[0]; // '.' The name of the preceding subclass is string subclassproperotyname = Properotyarray[1]; // '.' The name of the property following the subclass is System.Reflection.PropertyInfo subclassinfo = en. GetType (). GetProperty (Subclassname);                        Gets the type of child class if (subclassinfo! = null) {//3.1.2 Gets the instance of the child class var Subclassen = en. GetType (). GetProperty (Subclassname).                        GetValue (en, null); 3.1.3 Gets the attribute type in the subclass based on the property name Properotyinfo = SubClassInfo.PropertyType.GetProperty (Subclassproperotyna                        ME); if (properotyinfo! = null) {Properotyvalue = Properotyinfo.getvalue (Sub Classen, NULL);                    Gets the value of the Child Class Property}}} else { 3.2 If it is not a property of a subclass, the property corresponding to the object is obtained directly according to the property name Properotyinfo = en. GetType ().                    GetProperty (Keys[i]);                    if (properotyinfo! = null) {Properotyvalue = Properotyinfo.getvalue (en, null);                }}//3.3 property value converted to cell value if (properotyvalue! = null)                    {cellvalue = properotyvalue.tostring (); 3.3.1 Assignment of the time initial value to null if (Cellvalue.trim () = = "0001/1/1 0:00:00" | | Cellvalue.trim () = = "0001/1/1 23:59:5                    9 ") {Cellvalue =" "; }}//3.4 is populated into the Excel cell Rowtmp.createcell (i).            Setcellvalue (Cellvalue);        } rowindex++;        }//4. Makefile FileStream file = new FileStream (FilePath, FileMode.Create); Workbook.        Write (file); File.         Close ();  5. Return to the download path return urlpath;  } catch (Exception ex) {throw ex; }}

3.3 C # Logical operation code

Description: Perform subsequent operations on Excel converted list<t>, such as: detection validity, persistent storage, etc.

Steps:

① gets the List<t> collection.

② calls 3.2, converting list<t> to an Excel file.

The ③ server stores the Excel file and returns the download link.

Code:

public void Exportexcel (HttpContext context) {try {//1. Get data collection list<userentity> Enlist = new Li St<userentity> () {new userentity{name= "Liu Yi", age=22,gender= "Male", Transcriptsen=new Transcriptsentity{chin ESESCORES=80,MATHSCORES=90}}, New Userentity{name= "Chen er", age=23,gender= "Male", Transcriptsen=new transcriptsentity {chinesescores=81,mathscores=91}}, New Userentity{name= "Zhang San", age=24,gender= "Male", Transcriptsen=new Transcripts ENTITY{CHINESESCORES=82,MATHSCORES=92}}, new userentity{name= "John Doe", age=25,gender= "Male", Transcriptsen=new Trans  CRIPTSENTITY{CHINESESCORES=83,MATHSCORES=93}}, new Userentity{name= "Harry", age=26,gender= "Male", transcriptsen=new         TRANSCRIPTSENTITY{CHINESESCORES=84,MATHSCORES=94}},}; 2. Set cell Header//Key: Entity object property name, can get value by reflection//Value:excel column name dictionary<string, string> Cellheader     = new Dictionary<string, string> {{"name", "name"},       {"Age", "ages"}, {"Gendername", "gender"}, {"Transcriptsen.chinesescores", "Language Score"},         {"Transcriptsen.mathscores", "Math Score"},};        3. Perform the Excel conversion operation and return the converted file Download link string urlpath = excelhelper.entitylisttoexcel2003 (Cellheader, Enlist, "Student score"); System.Web.Script.Serialization.JavaScriptSerializer js = new System.Web.Script.Serialization.JavaScriptSerializer        (); Context.        Response.ContentType = "Text/plain"; Context. Response.Write (JS. Serialize (URLPath));    Returns the JSON-formatted content} catch (Exception ex) {throw ex; }}

3.4 Code Analysis

The core code is primarily the mapping between Cellheader and list<t>:

4. Source code Download

4.1 Operation diagram

4.2 Download Address

Baidu Network disk: Http://pan.baidu.com/s/1o69We8M

csdn:http://download.csdn.net/download/polk6/8974195

The above is the C # programming Excel import, export (source code download) (below) content, more relevant content please pay attention to topic.alibabacloud.com (www.php.cn)!

