Using C # to implement Excel import and export instances tutorial

1. Introduce

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.

2. Excel Import

2.1 Operation Flow

2.2 Npoi Operation code

Description: Convert Excel file to list

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:

+ View Code

2.3 C # Logical operation code

Description: Follow up on Excel converted list: Detection validity, persistent storage, etc.

Steps:

① calls the 2.2 code to convert the Excel file to list.

② the list for validation: whether the required items are empty, whether there are duplicate records, and so on.

③ the list 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      {           #region  1. Get Excel file and convert to a list collection            // 1.1 store Excel files to local server          httppostedfile filepost = context. request.files["filed"]; //  get uploaded files         string  Filepath = excelhelper.saveexcelfile (filepost); //  save file and get file path            //  Cell Header         //  Key: Entity object attribute name, can get value by reflection         // value: attribute corresponding Chinese annotation          dictionary<string, string> cellheader = new dictionary<string, string> {             {  "name",  "name"  },              {  age ", "  },              {  "Gendername",  "Gender"  },              {  "Transcriptsen.chinesescores",  "language Achievement"  },              {  "Transcriptsen.mathscores",  "Mathematical Results"  },  
       };           // 1.2 parsing file, stored in a list collection          List<UserEntity> enlist = ExcelHelper.ExcelToEntityList<
Userentity> (CELLHEADER, FILEPATH, OUT ERRORMSG);     &NBSp;      #endregion            #region   2. Validation of the list collection            #region  2.1 inspection required must fill            for  (int i = 0; i < enlist. count; i++)         {       
     UserEntity en = enlist[i];             string errorMsgStr =  "section
" +  (i + 1)  + " Row Data detection exception: ";             bool ishavenoinputvalue =  false; //  contains no entries             if   (String. IsNullOrEmpty (en. Name))            &nbsP {                ERRORMSGSTR
 +=  "Name column cannot be empty;";                 
ishavenoinputvalue = true;             }              if  (Ishavenoinputvalue)  //  if the required fields are not filled in,              {                 en.
isexcelvaildateok = false;                 
Errormsg.appendline (ERRORMSGSTR);             }         &NBSP}            #endregion &NBSp          #region  2.2 detect 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 validation              {                
Continue             }                for  (int j = i + 1; j <  enlist. count; j++)      &NBsp;      {           
     UserEntity enB = enlist[j];                 //  Determine if required columns are all repeated                 if   (Ena.name == enb.name)                  {              
      enA.IsExcelVaildateOK = false;                   
  enB.IsExcelVaildateOK = false;                      errormsg.appendline ("First"  +  (i + 1)  +  "line with the first"  +  (j + 1)  +  "line of the required column repeated");                 }   
          }         }            #endregion            // todo: Other testing            #endregion            // 3.todo: Persistent storage operations on the list collection. such as: Store to database                   
 4. Return action Results         bool isSuccess = false;         if  (errormsg.length == 0)          {     &NBsp;      issuccess = true; //  error message if 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 contents of JSON format     }     catch  (Exception  ex)     {  throw ex;     }

3. Excel Export

3.1 Export process

3.2 Npoi Operation code

Description: Convert list 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).

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

Code:

 <summary>///  entity class collection is exported to Excle2003/// </summary>/// <param name= " Cellheard "> Unit header Key and value:{ { " UserName ", " name " }, { " Age ", " ages "   };</param>/// <param name= "enList" > Data source </param>/// <param name= " SheetName "> 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, provide front desk download   &nbsP;     string filepath = httpcontext.current.server.mappath ("\"   + urlpath); //  file path           // 1. Detects if a folder exists. Create a folder if it doesn't exist         string directoryName = 
Path.getdirectoryname (FilePath);         if  (! Directory.Exists (directoryname))         {    
        directory.createdirectory (directoryname);        &NBSP}           //  2. Resolve cell headers, set the Chinese name of the cell header         hssfworkbook workbook =  new hssfworkbook (); //  Workbook         ISheet  Sheet = workbook. Createsheet (sheetname); //  worksheet &nbSp;       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]]); //  A value with column name key         }   The value of the         // 3.list object is assigned to the cell in Excel          int rowIndex = 1; //  assignment starting from the second line (the first row is set to the cell header)          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           Value of        object properotyValue = null; //  property                   Information for system.reflection.propertyinfo properotyinfo = null; //  properties                    // 3.1  if the name of the property header contains '. ', is the attribute in the subclass, it is necessary to traverse the subclass, Eg:UserEn.UserName                 if  (Keys[i]. IndexOf (".")  >= 0)                  {                     // 3.1.1  parsing Subclass attributes (only 1-layer subclasses are parsed, and multi-layer subclasses are not processed)                      string[] properotyarray =  keys[i]. Split (new string[] {  ".")
 }, stringsplitoptions.removeemptyentries);                      string subClassName = properotyArray[0]; //  '. ' The previous name for the subclass                      string subclassproperotyname = properotyarray[1]; //  '. ' The property name followed by the subclass                      system.reflection.propertyinfo subclassinfo = en. GetType (). GetProperty (Subclassname); //  Gets the type of the subclass                      if  (subclassinfo != null)                       {                         // 3.1.2  getting instances of subclasses                          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);  //  Gets the value of the Child class property                          }                      }                  }                  else                  {                     // 3.2  If you are not a property of a subclass, get the corresponding property of the object 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 cell values                  if  (properotyvalue != null)                 &nBsp {                  
  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  Fill in the cells in Excel                  Rowtmp.createcell (i).
Setcellvalue (Cellvalue);             }       
      rowIndex++;        &NBSP}           //  4. Generate Files         FileStream file = new 
FileStream (filepath, filemode.create);         workbook.
Write (file);         file.
Close ();           // 5. Back to download Path         return urlpath;    &NBSP}     catch  (Exception ex)     { 
       throw ex;    &NBSP}}

3.3 C # Logical operation code

Description: Follow up on Excel converted list: Detection validity, persistent storage, etc.

Steps:

① Gets the list collection.

② calls 3.2 to convert the list 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}&NBSP},              new userentity{name= "John", Age=24,gender= "Male", TranscriptsEn=new  TRANSCRIPTSENTITY{CHINESESCORES=82,MATHSCORES=92}&NBSP},              new&nBsp 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, can get value through reflection         //  Name of the Value:excel column         Dictionary<string, string>  cellheader = new dictionary<string, string> {             {  "name",  "name"  },              {  age ", "  },             {  "Gendername",  "Gender"  },              {  "Transcriptsen.chinesescores",  "language Achievement"  } ,             {  "Transcriptsen.mathscores",
  "Math Score"  },         };           // 3. Perform Excel conversion operations and return the converted file Download link          string urlpath = excelhelper.entitylisttoexcel2003 (Cellheader,
 enlist,  "student Achievement");         system.web.script.serialization.javascriptserializer js
 = new system.web.script.serialization.javascriptserializer ();         context.
response.contenttype =  "Text/plain";         context. Response.wrIte (JS. Serialize (URLPath)); //  returns JSON-formatted content     }     catch  (
EXCEPTION EX)     {        throw ex;    &NBSP}}

3.4 Code Analysis

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

4. SOURCE download

4.1 Run diagram

Another reference to import and export operations on Excel in C # is also very useful


First, take a look at the most common import operation bar!

private void import () {          //Open Excel selection box       OpenFileDialog frm = new 
OpenFileDialog ();        frm.
filter =  "Excel file (*.xls,xlsx) |*.xls;*.xlsx";        if  (frm. ShowDialog ()  == dialogresult.ok)        {                            string excelname = frm.
FileName;           workbook excel = new workbook (
Excelname);           list<string[]> importystring=
Getimportexcelroute (Excel); &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP}} 

 

Loop through gets the value of each column in Excel    Public list<string[]> getimportexcelroute (workbook excel )         {             int icount = excel.
Worksheets.count;             list<string[]> routlist
 = new List<string[]> ();             for  (int i = 0;  i < icount; i++)              {                 Worksheet sheet = excel.
Worksheets[i];                 Cells  Cells = sheet.
Cells;         &nbSp;       int rowcount = cells.
MaxRow;                 int  Columncount = cells.
Maxcolumn;                 int 
routnamecolumn = 0;                 int 
routattachcolumn = 0;                 int 
routdesccolumn = 0;                 int 
routmesgcolumn = 0;                //get the column where the title is located                  if  (ROWCOUNT  >  0 && columncount > 0)                  {                     //find the corresponding column information       
              int r0 = 2;                      for  (int c = 0; c <= columncount; c++)   
                  {                          STRING STRVAL = CELLS[R0, C].
Stringvalue.trim ();                          if  (strval ==  "Remarks")                          {                       
      routDescColumn = c;                   
          break;                          }                      }                     r0 = 3;                      for  (int c = 0; c <= columncount; c++)   
                  {                          //Get text box content                          string strval =  CELLS[R0, C].
Stringvalue.trim ();                          if  (strval ==  "approval details")                          {                     
        routNameColumn = c;                          }             
            if  (strval ==  "Fine Items")                          {                             routmesgcolumn
 = c;                         }                          if  (strval ==  "preconditions and work requirements")                           {                 
           routAttachColumn = c;                          }                      }                       //find the value below the corresponding header column                      if  (routnamecolumn > 0  && routattachcolumn > 0 && routdesccolumn > 0)                       {//to find the corresponding value from the corresponding column                          for  (int r = 4; r  <= rowcount; r++)                          {                              string[] str = new string[6];                   
          string strRoutName =  "";                   
          string strRoutMesg =  "";                   
          string strRoutAttach =  "";                   
          string strRoutDesc =  "";                              string strroutrole =  "";                   
          string strRoutPro =  "";                              for  (int c = 0; c  <= columncount; c++)                              {                                  int mergcolumncount = 0
;                            
     int mergrowcount = 0;                                  bool ismerged =  CELLS[R, C]. ismerged;//whether to merge cells                                    if   (ismerged)                                  {                                      RANGE RANGE = CELLS[R, C].
Getmergedrange ();                                      if   (Range != null)                                       {                                          mergcolumncount = range.
ColumnCount;                                           mergrowcount = range.
ROWCOUNT;                   
                  }                                  }                                   //Get text box content                                   string strVal =  "";
                                 strval = cells[r ,  c].
Stringvalue.trim ();                                  if  (c ==  Routnamecolumn)                                  {                                      
strroutname = strval;                                      if  (mergrowcount > 1 && string. IsNullOrEmpty (strroutname))                                       {                                
         strroutname = getroutname (routList, 0);                   
                  }                                  }                                   if  (C == routmesgcolumn)                                   {                              
       strRoutMesg = strVal;                                      if  (mergrowcount > 1 &&  String. IsNullOrEmpty (STRROUTMESG))                                       {                                
         strroutmesg = getroutname (routList, 1);                   
                  }                                   }                                   if  (C == routattachcolumn)                                   {                           
          strRoutAttach = strVal;                                  }                                   if  (C == routdesccolumn)                                   {                              
       strRoutDesc = strVal;                                  }                              }                    }  } 

can see that the import is relatively simple, that is, to iterate through the values of each column in each row, you can see a lot of Cells this attribute, which requires a third party plug-in: using Aspose.cells; need to download a A aspose.cells DLL.

Two, export, which is to combine data into Excel format:

Private void export () {    SaveFileDialog frm = new 
SaveFileDialog ();     frm.
filter =  "Excel file (*.xls,xlsx) |*.xls;*.xlsx";     frm.
filename = flowname +  ". xlsx";     if  (frm. ShowDialog ()  == dialogresult.ok)     {         string strpath = frm.
FileName;
        Workbook workbook = null;
       string strpath = _exportFlowRoutExcelPath;        if  (File.exists (strpath))         {          workbook = new workbook (
strpath);         }         else
        {          
 workbook = new workbook ();        }        worksheet sheet  = workbook. worksheets[0]; //worksheet        cells cells = sheet. cells;//cell        string str= "";//get the data to export                            try           {                          
      routexporttoexcel (WORKBOOK,CELLS,STR);                                           messagebox.show ("Export success!")
");             }               catch                                         {                 messagebox.show ("Export failed!")
");             }          }     }  }

 

Public void routexporttoexcel (WORKBOOK WORKBOOK, CELLS CELLS,STRING STR) { 
   Get rows and columns     int routCount =0;//;
    int rowcount = 4 + routCount;
    int columnCount = 25;     for  (int i = 0; i < rowcount; i++)      {        Style style = 
Settingcellstyle (Workbook, cells);         if  (i == 0)          {            style.
font.color = color.red;             style.
font.size = 16;             cells. Merge (0,&NBsp;0, 1, columncount)//Merge cell              CELLS[I, 0]. Putvalue ("Integrated Pipeline decision Authorization System matters")/fill in the content             cells[0 ,  0]. SetStyle (style),//To the cell association style             cells. Setrowheight (0, 38);//Set row height             cells. Setcolumnwidth (1, 20);//Set column width                   if  (i > 0)         { 
           string routeName =  "";
            string routeNote =  "";             string routecondition =  "";
            string guid =  "";             if  (i > 3)              {                 cells. Setrowheight (i, 42)//Set row height                  JsonObject routJsonObj = routeJsonArray[i - 4] as 
Jsonobject;                 routename  = routjsonobj["Routname" "] == null ? " " : routjsonobj[" RoutName "].
ToString ();                 routenote  = routjsonobj["Note"] == null&nbSP;?   ""  : routjsonobj["note"].
ToString ();                 routecondition  = routjsonobj["Condition" "] == null ? " " : routjsonobj[" condition "].
ToString ();                 guid =  routjsonobj["GUID"] == null ?  ""  : routjsonobj["GUID".
ToString ();             }              for  (int j = 0; j < columncount;  j++)             {                 CELLS[I, J]. SetStyle (style)//To cell association style           &Nbsp;     //Fill Row                  if  (i > 3)                  {                     if  (j == 0)//serial number                      {                          CELLS[I, J]. Putvalue (i - 3)/fill in content                      }                      else if  (j == 4 | |  j == 5 | |  J == 24)//Approval detail   Details   notes                      {                         
Fillexcelroutproperty (i,j,style,cells,routename,routenote,routecondition);                      }                      else if  (j == 2 | |  j == 3 | |  j == 6 | |  j == 7 | |  j == 8 | |  j == 10 | |  j == 11 | |  j == 12 | |  J == 13)//category, classification, level or plate, region, pipeline, precondition responsible department and responsible person, audit proof and acceptance responsibility Departmentand the responsible person, the specific audit proofreading acceptance request, the Promoter                      {                         fillexcelroutextproperty (i, j, 
Guid, style, cells,routextpropertyjsonarray);                      }                      else if  (j >= 14 && j <= 23)// Route role variables (from approver 1 to CFA)                      {                         fillexcElroutroleval (I,j,guid,style,cells,routrolevaljsonarray);                      }                      else if  (j == 9)//Front conditions and job requirements                      {                       
  fillexcelroutpreconditon (I,j,guid,style,cells,routpreconditonjsonarray);                   
  }                 }                &nbSp;else                 {                     Settingcellstyleandline (CELLS, I, J)//Set the header row and column of Excel                  }              }         }     }

 <summary>///  Set cell styles and lines/// </summary>/// <param name= "Cells" > </param>/// <param name= "i" ></param>/// <param name= "J" ></param > Public void settingcellstyleandline (cells cells, int i, int j) {     if  (i == 1)     {         if  (j == 0)         {             cells. Merge (1, j, 3, 1)//merged cells              CELLS[I, J]. Putvalue ("serial number");//fill in the content             cells. Setcolumnwidth (j, 5);//Set column width                   if  (j == 1)         {             cells. Merge (1, j, 3, 1)//merged cells              cells. Setcolumnwidth (j, 5);//Set column width         }    }

 <summary>///  Set the style of the cell/// </summary>/// <param name= "Workbook" >
</param>/// <param name= "Cells" ></param>/// <returns></returns> Public style settingcellstyle (workbook workbook, cells cells) {     style style = workbook. Styles[workbook. Styles.add ()];//new style     style. horizontalalignment = textalignmenttype.center;//text centered     style. font.name =  "Song body";//Text font     style. font.size = 10;//Text Size     style. islocked = false;//Unit Gejian     style. font.isbold = false;//Bold     style. Foregroundcolor = color.fromargb (255, 255, 255);/Set background color     style. pattern = backgroundtype.solid; //sets the background style     style. Istextwrapped = true;//the cell content to wrap     style. Borders[bordertype.leftborder]. linestyle = cellbordertype.thin; //applies boundary   left boundary     style. Borders[bordertype.rightborder]. linestyle = cellbordertype.thin; //applies boundary   right border     style. Borders[bordertype.topborder]. linestyle = cellbordertype.thin; //applies border   upper boundary     style. Borders[bordertype.bottomborder].
linestyle = cellbordertype.thin; //applied border   lower boundary     return style; }