C # Excel import, Export

This article mainly introduces the C # Excel import, export.

Directory

1. Description: Describe the third-party class library Npoi and Excel structure

2. Excel import: Describes how C # calls Npoi for Excel import, including: Flowchart, Nopi, and C # code

3. Excel export: Describes how C # calls Npoi for Excel export, including: Flowchart, Nopi, C # code, and Code analysis

4. SOURCE Download: Display operation diagram and source code download

1. Introduction 1.1 third-party class library: Npoi

Description: Npoi is a. NET version of the POI project that you can use for Excel, Word read and write operations.

Pros: No office environment is installed.

:http://npoi.codeplex.com/releases

1.2 Excel Structure Introduction

Workbooks (Workbook): Each Excel file can be understood as a workbook.

Worksheets (Sheet): a Workbook (Workbook) can contain multiple worksheets.

row: one worksheet (Sheet) can contain multiple rows.

2. Excel Import 2.1 Operation flow

2.2 Npoi Operation code

Description: convert Excel files to list<t>

Steps:

① read the Excel file and initialize a workbook (Workbook) with this;

② get a worksheet (Sheet) from the workbook; The first sheet of the workbook is the default;

③ traverses all rows of the worksheet (row), by default, from the second row, the first row (ordinal 0) is the cell header;

④ iterates through each cell (cell) of a row, assigning values to an object's properties according to certain rules.

Code:

<summary>///the data from the Excel2003 and records it into the list collection///</summary>///<param name= "cellheard" > Unit header key and value: {{"UserName", "name"}, {"Age", "ages"}};</param>///<param name= "FilePath" > Save file Absolute path </param>///<par Am Name= "errormsg" > Error messages </param>///<returns> converted List Object collection </returns>private static list<t > excel2003toentitylist<t> (dictionary<string, string> Cellheard, String filePath, out StringBuilder ERRORMSG) where T:new () {errormsg = new StringBuilder ();//error message when Excel converts to an entity object, there will be a format error message list<t> enlist = NE W list<t> (); Converted collection List<string> keys = CellHeard.Keys.ToList (); To assign a value to the entity object property name of the try {using (FileStream fs = File.openread (FilePath)) {Hssfworkbook Workbo            OK = new Hssfworkbook (FS); Hssfsheet sheet = (hssfsheet) workbook. Getsheetat (0); Gets the first sheet page of this file for (int i = 1; I <= sheet. Lastrownum;               i++)//Starting from 1, the No. 0 Act unit Header { 1. Determine if the current line is empty, if the empty line is not read the next line of operation, the end of the Excel read operation if (sheet.                GetRow (i) = = null) {break;                } t en = new t (); String errstr = ""; When the current row is converted, there is an error message in the format: line 1th data conversion exception: XXX column; for (int j = 0; J < keys. Count; J + +) {//2. If the name of the attribute header contains '. ', it is the attribute in the subclass, then the subclass must be traversed, eg:UserEn.TrueName if (k EYS[J]. IndexOf (".") >= 0) {//2.1 parsing sub-class attribute string[] Properotyar Ray = Keys[j].                        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) {                           2.1.1 Gets the instance of the subclass var Subclassen = en. GetType (). GetProperty (Subclassname).                            GetValue (en, null); 2.1.2 Gets the attribute information in the subclass based on the property name System.Reflection.PropertyInfo Properotyinfo = Subclassinfo.propertyt Ype.                            GetProperty (Subclassproperotyname);                                if (properotyinfo! = null) {try The value of the {//Excel cell is converted to the value of the object property, and if the type is not correct, the error message is logged properotyinfo. SetValue (Subclassen, Getexcelcelltoproperty (Properotyinfo.propertytype, sheet. GetRow (i).                                Getcell (j)), NULL); } catch (Exception e) {i F (errstr.length = = 0) {errstr = "+ i +" row data   Conversion exception: ";                                 } Errstr + = Cellheard[keys[j]] + "column;";                    }                                                            }                        } } else {//3. Assign a value to the specified property System.reflec tion. PropertyInfo properotyinfo = en. GetType ().                        GetProperty (Keys[j]);                                if (properotyinfo! = null) {try { The value of the Excel cell is converted to the value of the object property, and the error message is logged if the type is not Properotyinfo.setvalue (en, getexce Lcelltoproperty (Properotyinfo.propertytype, sheet. GetRow (i).                            Getcell (j)), NULL); } catch (Exception e) {if (errstr.le                           Ngth = = 0) {         ERRSTR = "First" + i + "Row Data conversion exception:";                            } Errstr + = Cellheard[keys[j]] + "column;"; }}}//If you have an error message, add it to the error message if (err                Str.length > 0) {errormsg.appendline (ERRSTR); } enlist.            Add (en);    }} return enlist;    } catch (Exception ex) {throw ex; }}

2.3 C # Logical operation code

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

Steps:

① calls the 2.2 code to convert the Excel file to list<t>.

② checks the list<t> for validity: is the required entry empty, is there a duplicate record, and so on.

③ Persistent storage operations on list<t>. such as: Store to database.

④ returns the result of the operation.

Code:

public void Importexcel (HttpContext context) {StringBuilder errormsg = new StringBuilder ();//error message try { #region 1. Get the Excel file and convert it to a list collection//1.1 store the Excel file to the local server httppostedfile FilePost = context. Request.files["filed"]; Gets the uploaded file string filePath = Excelhelper.saveexcelfile (filepost); Save file and get file path//cell Header//Key: Entity object property name, can get value by reflection//value: attribute corresponding to Chinese annotation dictionary<string, stri             ng> Cellheader = new Dictionary<string, string> {{"name", "name"}, {"Age", "Ages"}, {"Gendername", "gender"}, {"Transcriptsen.chinesescores", "Language Score"}, {"Transcriptsen.mathsc        Ores "," Math Score "},};  1.2 Parse file, stored in a List collection list<userentity> enlist = excelhelper.exceltoentitylist<userentity> (Cellheader,        FilePath, out errormsg); #endregion #region 2. Check the list collection for validation #region 2.1 The required fields are required for (int i = 0; i < enlist. Count;    i++)    {userentity en = enlist[i];            String errormsgstr = "First" + (i + 1) + "Row Data detection exception:"; BOOL Ishavenoinputvalue = false; Whether the entry contains an input if (string). IsNullOrEmpty (en.                Name) {errormsgstr + = "Name column cannot be empty;";            Ishavenoinputvalue = true; } if (Ishavenoinputvalue)//If required value is not filled in {en.                Isexcelvaildateok = false;            Errormsg.appendline (ERRORMSGSTR); }} #endregion #region 2.2 detects if there are duplicate objects in Excel for (int i = 0; i < enlist. Count;            i++) {userentity EnA = enlist[i];            if (Ena.isexcelvaildateok = = false)//above verify does not pass, do not perform this step verification {continue; } for (int j = i + 1; j < enlist. Count;                J + +) {userentity EnB = enlist[j]; Determine if all required columns repeat if (ena.name = = enb.name) {Ena.isexcelvaildateok= false;                    Enb.isexcelvaildateok = false;                Errormsg.appendline ("the" + (i + 1) + "line and" + (j + 1) + "The required column of the line is repeated"); }}} #endregion//TODO: Additional detection #endregion//3.TODO: Persist storage operations on the list collection.        such as: storage to the database//4. Returns the result of the operation bool issuccess = FALSE; if (Errormsg.length = = 0) {issuccess = true;//If the error message Chengdu is empty, indicating no error message} var rs = new {succes        s = issuccess, msg = errormsg.tostring (), data = enlist}; System.Web.Script.Serialization.JavaScriptSerializer js = new System.Web.Script.Serialization.JavaScriptSerializer        (); Context.        Response.ContentType = "Text/plain"; Context. Response.Write (JS. Serialize (RS));    Returns the JSON-formatted content} catch (Exception ex) {throw ex; }}

　　

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 </returns>public static string EntityListToExcel2003 ( Dictionary<string, string> Cellheard, IList enList, string sheetname) {try {string fileName = Sheetnam E + "-" + 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 the 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 subclassproperty, which gets the properties of the object 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{chi NESESCORES=80,MATHSCORES=90}}, New Userentity{name= "Chen er", age=23,gender= "Male", Transcriptsen=new transcriptsentit Y{CHINESESCORES=81,MATHSCORES=91}}, New Userentity{name= "Zhang San", age=24,gender= "Male", Transcriptsen=new Transcript SENTITY{CHINESESCORES=82,MATHSCORES=92}}, new userentity{name= "John Doe", age=25,gender= "Male", Transcriptsen=new Tran SCRIPTSENTITY{CHINESESCORES=83,MATHSCORES=93}}, new Userentity{name= "Harry", age=26,gender= "Male", Transcriptsen=ne        W 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 run diagram

4.2

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

C # Excel import, Export