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

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

1. Introduction

1.1 third-party class libraries: Npoi

Description: Npoi is a. NET version of the POI project that can be used for read and write operations in Excel and Word.

Pros: No office environment is installed.

Download Address: 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:

Extract data from Excel2003 and record to list set///<param Name= "Cellheard" > Unit header key and value:{{"UserName", "name"}, {"Age", "ages"}};< /param>///<param name= "FilePath" > Save file Absolute path </param>///<param name= "errormsg" > Error message </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 = NE W StringBuilder (); Error message when you convert Excel to an entity object, there is a malformed error message list<t> enlist = new 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 Head {//1. Determine if the current line is blank, if the line is not read the next line, ending ExcelRead 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 won Take the childExample of class 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 attribute System.Reflection.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 + "line 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, str            ing> Cellheader = new Dictionary<string, string> {{"name", "name"}, {"Age", "Ages"}, {"Gendername", "gender"}, {"Transcriptsen.chinesescores", "Language Score"}, {"Transcriptsen.mathscores         "," 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; }}

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

