C # Excel import, Export _c# tutorial

This article mainly introduces C # Excel import, export, for everyone's reference, the specific contents are as follows

I. INTRODUCTION
1.1 Third party class library: Npoi

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

Advantages: Do not install Office environment.

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

Introduction to 1.2 Excel structure

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

Sheet (Sheet): a Workbook (workbook) can contain more than one worksheet.

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

Two. Excel Import
2.1 Operation Flow

2.2 Npoi Operation code

Description: Convert Excel file to list<t>

Steps:

① reads the Excel file and initializes a workbook (workbook);

② Gets a worksheet (Sheet) from the workbook, and defaults to the first worksheet in the workbook;

③ traverses all rows of the worksheet (row); The default starts at the second line, and the first row (ordinal 0) is the cell header;

④ traverses each cell of the row, assigning values to the properties of the object according to certain rules.

Code:

<summary>///data from Excel2003 and recorded in 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>///<p Aram 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 = new StringBuilder ();//error message, when Excel converts to an entity object, there is a format error message list<t> = new List<t> (); The converted collection list<string> keys = CellHeard.Keys.ToList (); The Entity object property name to be assigned a try {using (FileStream fs = File.openread (FilePath)) {Hssfworkbook workbook = new HSSF
      Workbook (FS); Hssfsheet sheet = (hssfsheet) workbook. Getsheetat (0); Gets the first sheet page for this file for (int i = 1; I <= sheet. Lastrownum; i++)//Starting from 1, No. 0 Act cell Header {//1. To determine whether the current row is blank, and if blank lines are not read Next lineAction, ending the Excel read operation if (sheet).
        GetRow (i) = = null) {break;
        } t en = new t (); String errstr = ""; When the current row is converted, is there 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 '. ', which means the attribute in the subclass, then it is necessary to traverse the subclass, Eg:UserEn.TrueName if (Keys[j]. IndexOf (".") >= 0) {//2.1 parsing subclass Property string[] Properotyarray = keys[j].
            Split (new string[] {"."}, Stringsplitoptions.removeemptyentries); String subclassname = Properotyarray[0]; // '.' The previous name for the subclass string subclassproperotyname = Properotyarray[1]; // '.' The attribute name for the subclass is System.Reflection.PropertyInfo subclassinfo = en. GetType (). GetProperty (Subclassname);  Gets the type of the subclass if (subclassinfo!= null) {//2.1.1 Gets the instance var Subclassen of the child class = en. GetType (). GetProperty (Subclassname).
              GetValue (en, null); 2.1.2 Gets the attribute information in a subclass based on the property name System.reflectIon.
              PropertyInfo properotyinfo = SubClassInfo.PropertyType.GetProperty (subclassproperotyname); if (properotyinfo!= null) {The value of the try {//Excel cell is converted to the value of the object property. If the type is not correct, log the error message Properotyinfo.setvalue (Subclassen, Getexcelcelltoproperty (Properotyinfo.propertytype, sheet . GetRow (i).
                Getcell (j)), NULL);
                    catch (Exception e) {if (errstr.length = 0) {
                  ERRSTR = "First" + i + "Row Data conversion exception:";
                } Errstr + = Cellheard[keys[j]] + "column;";
            else {//3. Assign a value to a specified property System.Reflection.PropertyInfo properotyinfo = en. GetType ().
            GetProperty (Keys[j]); if (properotyinfo!= null) {The value of the try {//Excel cell is converted to the value of the object property, if the type is not, log error messages Properotyinfo.setvalue (EN, Getexcelcelltoproperty (properotyinfo.propertytype, sheet). GetRow (i).
              Getcell (j)), NULL);
                  catch (Exception e) {if (errstr.length = 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 (Errstr.length > 0) {er
        Rormsg.appendline (ERRSTR); } enlist.
      Add (en);
  } return enlist;
  catch (Exception ex) {throw ex;
 

 }
}

2.3 C # Logical operation code

Description: After the conversion of Excel list<t> to follow up operations, such as: Detection of validity, persistent storage and so on

Steps:

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

② list<t> Validation: Whether the required items are empty, whether there are duplicate records, and so on.

③ the list<t> for persistent storage operations. such as: Store to database.

④ returns the result of the operation.

Code:

public void Importexcel (HttpContext context) {StringBuilder errormsg = new StringBuilder ();//error message try {#r Egion 1. Get Excel file and convert to a list set//1.1 store Excel file to 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, which can be retrieved by reflection//value: attribute corresponding Chinese annotation dictionary<string, string> cel  Lheader = new Dictionary<string, string> {{"name", "name"}, {"Age", "ages"}, {"Gendername", "gender"
 
    }, {"Transcriptsen.chinesescores", "Language Score"}, {"Transcriptsen.mathscores", "Math Grade"},}; 1.2 Parse file, stored in a List collection list<userentity> enlist = excelhelper.exceltoentitylist<userentity> (Cellheader,
 
    FilePath, out errormsg); #endregion #region 2. Validation of the list collection #region 2.1 The required fields must be filled for (int i = 0; i < enlist. Count;
      i++) {userentity en = enlist[i]; String Errormsgstr= "First" + (i + 1) + "Row Data detection exception:"; BOOL Ishavenoinputvalue = false; Contains an item if (string) that is not entered. IsNullOrEmpty (en.
        Name) {errormsgstr + = "Name column cannot be empty;";
      Ishavenoinputvalue = true; } if (Ishavenoinputvalue)//If the required entry has a value not filled in {en.
        Isexcelvaildateok = false;
      Errormsg.appendline (ERRORMSGSTR); #endregion #region 2.2 Detects whether there are duplicate objects for (int i = 0; i < enlist in Excel). Count;
      i++) {userentity EnA = enlist[i];
      if (Ena.isexcelvaildateok = false)//above validation does not pass, do not perform this step validation {continue; for (int j = i + 1; j < enlist. Count;
        J + +) {userentity EnB = enlist[j];
          Determine if all mandatory columns are duplicates if (Ena.name = = enb.name) {Ena.isexcelvaildateok = false;
          Enb.isexcelvaildateok = false;
        Errormsg.appendline ("First" + (i + 1) + "row and First" + (j + 1) + "Row's required column repeated"); }} #endregion//TODO: Other detection #endregion//3.TODO: Persistent storage operations on the list collection.
    such as: Store to Database//4. Return operation result bool issuccess = false;  if (Errormsg.length = = 0) {issuccess = true;//If error message Chengdu is empty, indicates no error message} var rs = new {success = 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 content in JSON format} 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 (row) on the worksheet, the first behavior column header, and then writes the Cellheard value (as the column name).

The ④ loops through the List<t> collection, creates a row (row) at a time, and then stores the values from the entity objects in the list<t> to the cell according to the Cellheard key (the property name).

Code:

<summary>///entity class collection exported to Excle2003///</summary>///<param name= "Cellheard" > Cell header Key and value:{{"User Name "," name "}, {" Age "," ages "}};</param>///<param name=" enList "> Data source </param>///<param name=" Sheet " Name > Worksheet name </param>///<returns> file download address </returns> public static string EntityListToExcel2003 ( Dictionary<string, string> Cellheard, IList enList, string sheetname) {try {string fileName = SheetName + "-" + DateTime.Now.ToString ("yyyymmddhhmmssfff") + ". xls"; File name string URLPath = "upfiles/excelfiles/" + fileName; File download URL address, supply foreground download string filePath = HttpContext.Current.Server.MapPath ("\" + URLPath);
    File path//1. Detects if a folder exists, and if not, create a folder string directoryname = Path.getdirectoryname (FilePath); if (!
    Directory.Exists (directoryname)) {directory.createdirectory (directoryname); //2. Resolve cell headers, set the Chinese name of the cell header 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]]); Value of column named key}//3.List object value assigned to excel cell int rowIndex = 1; Assignment starting at the second line (the first row has been set to the cell header) foreach (Var en in enList) {irow rowtmp = sheet.
      CreateRow (RowIndex); for (int i = 0; i < keys. Count; i++/////////According to the specified property name, gets the value of the object's specified property {string cellvalue = ""; The value of the Cell object properotyvalue = null; The value of the property System.Reflection.PropertyInfo properotyinfo = null; Property Info//3.1 If the name of the property header contains '. ', which means the attribute in the subclass, then it is necessary to traverse the subclass, Eg:UserEn.UserName if (Keys[i]. IndexOf (".") >= 0) {//3.1.1 resolves subclass attributes (only 1-layer subclasses are parsed, not multiple-layer subclasses) string[] Properotyarray = Keys[i].s
          Plit (new string[] {"."}, Stringsplitoptions.removeemptyentries); String subclassname = Properotyarray[0]; // '.' The previous name for the subclassCalled string subclassproperotyname = Properotyarray[1]; // '.' The attribute name for the subclass is System.Reflection.PropertyInfo subclassinfo = en. GetType (). GetProperty (Subclassname); Gets the type of the subclass if (subclassinfo!= null) {//3.1.2 Gets the instance of the subclass 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 (subclassproperotyname); if (properotyinfo!= null) {Properotyvalue = Properotyinfo.getvalue (Subclassen, NULL);
          /Get the value of the Subclass property}} else {//3.2 if it is not a subclass property, get the object's corresponding property directly based on the property name Properotyinfo = en. GetType ().
          GetProperty (Keys[i]);
          if (properotyinfo!= null) {properotyvalue = Properotyinfo.getvalue (en, null);
      }///3.3 property values are converted to the cell value if (properotyvalue!= null) {    Cellvalue = Properotyvalue.tostring ();
          3.3.1 Assignment to time initial value is null if (Cellvalue.trim () = "0001/1/1 0:00:00" | | Cellvalue.trim () = "0001/1/1 23:59:59")
          {cellvalue = ""; }//3.4 fills in the Excel cell Rowtmp.createcell (i).
      Setcellvalue (Cellvalue);
    } rowindex++;
    //4. Makefile FileStream file = new FileStream (FilePath, FileMode.Create); Workbook.
    Write (file); File.
 
    Close ();
  5. Back to download path return urlpath;
  catch (Exception ex) {throw ex;
 

 }
}

3.3 C # Logical operation code

Description: After the conversion of Excel list<t> to follow up operations, such as: Detection of validity, persistent storage and so on

Steps:

① gets the List<t> collection.

② calls 3.2 to convert 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 list< Userentity> () {new userentity{name= "Liu Yi", age=22,gender= "Male", Transcriptsen=new transcriptsentity{chinesescores =80,MATHSCORES=90}, New Userentity{name= "Chen er", age=23,gender= "Male", Transcriptsen=new transcriptsentity{ CHINESESCORES=81,MATHSCORES=91}, new Userentity{name= "John", Age=24,gender= "Male", transcriptsen=new TRANSCRIPTSENTITY{CHINESESCORES=82,MATHSCORES=92}, new Userentity{name= "Dick", age=25,gender= "Male", TranscriptsEn= New transcriptsentity{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, which can be obtained by reflection//Value:excel column name dictionary<string, string> cellheader = new Dic
 Tionary<string, string> {{"name", "name"}, {"Age", "ages"}, {"Gendername", "Sex"},     {"Transcriptsen.chinesescores", "Chinese Score"}, {"Transcriptsen.mathscores", "Math Grade"},};
    3. Perform Excel conversion operations 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 content in JSON format} catch (Exception ex) {throw ex;
 
 }
}

3.4 Code Analysis

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

Four. SOURCE download
4.1 Run diagram

SOURCE Download: Http://xiazai.jb51.net/201605/yuanma/C

The above is the whole content of this article, hope to be able to help everybody's study.