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" &NBSP;}, { age ", " &NBSP;}, { "Gendername", "Gender" &NBSP;}, { "Transcriptsen.chinesescores", "language Achievement" &NBSP;}, { "Transcriptsen.mathscores", "Mathematical Results" },
}; // 1.2 parsing file, stored in a list collection List<UserEntity> enlist = ExcelHelper.ExcelToEntityList<
Userentity> (CELLHEADER,&NBSP;FILEPATH,&NBSP;OUT&NBSP;ERRORMSG); &NBSP;&NBSP;&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 {&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&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;&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;&NBSP;&NBSP;&NBSP;&NBSP;&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;&NBSP;&NBSP;&NBSP;&NBSP;&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;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&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;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP} // 4. Generate Files FileStream file = new
FileStream (filepath, filemode.create); workbook.
Write (file); file.
Close (); // 5. Back to download Path return urlpath; &NBSP;&NBSP;&NBSP;&NBSP} catch (Exception ex) {
throw ex; &NBSP;&NBSP;&NBSP;&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&NBSP;TRANSCRIPTSENTITY{CHINESESCORES=81,MATHSCORES=91}&NBSP}, new userentity{name= "John", Age=24,gender= "Male", TranscriptsEn=new &NBSP;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" &NBSP;}, { age ", " &NBSP;}, { "Gendername", "Gender" &NBSP;}, { "Transcriptsen.chinesescores", "language Achievement" &NBSP;} , { "Transcriptsen.mathscores",
"Math Score" &NBSP;}, }; // 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&NBSP;EX) { throw ex; &NBSP;&NBSP;&NBSP;&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++)
{ &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;STRING&NBSP;STRVAL&NBSP;=&NBSP;CELLS[R0,&NBSP;C].
Stringvalue.trim (); if (strval == "Remarks") {
routDescColumn = c;
break; } } r0 = 3; for (int c = 0; c <= columncount; c++)
{ //Get text box content string strval = &NBSP;CELLS[R0,&NBSP;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 = &NBSP;CELLS[R,&NBSP;C]. ismerged;//whether to merge cells if (ismerged) { &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;RANGE&NBSP;RANGE&NBSP;=&NBSP;CELLS[R,&NBSP;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&NBSP;WORKBOOK,&NBSP;CELLS&NBSP;CELLS,STRING&NBSP;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 &NBSP;CELLS[I,&NBSP;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++) { &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;CELLS[I,&NBSP;J]. SetStyle (style)//To cell association style &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&Nbsp; //Fill Row if (i > 3) { if (j == 0)//serial number { &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;CELLS[I,&NBSP;J]. Putvalue (i - 3)/fill in content } else if (j == 4 | | j == 5 | | &NBSP;J&NBSP;==&NBSP;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 | | &NBSP;J&NBSP;==&NBSP;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,&NBSP;I,&NBSP;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 &NBSP;CELLS[I,&NBSP;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; }