C # Excel import and export,

C # Excel import and export,

This article describes how to import and export C # Excel files.

Directory

1. Introduction: Describes the third-party class library NPOI and the Excel structure.

2. Excel import: describes how to use NPOI to import an Excel file, including flowchart, NOPI, and C # code.

3. Excel export: describes how to use NPOI to export data in Excel, including flowchart, NOPI, C # code, and code analysis.

4. Download source code: display the runtime diagram and download the source code

 

1. Introduction 1.1 third-party Class Library: NPOI

Note:NPOI is the. NET version of the POI project and can be used for reading and writing Excel and Word.

Advantages:You do not need to install the Office environment.

:Http://npoi.codeplex.com/releases

 

1.2 Excel Structure

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

Worksheet (Sheet ):A Workbook can contain multiple worksheets.

Row ):A worksheet can contain multiple rows.

 

2. Excel import 2.1 operation procedure

 

2.2 NPOI operation code

Note:Convert an Excel file to a List <T>

Steps:

① Read an Excel file and initialize a Workbook (Workbook );

② Obtain a worksheet (Sheet) from the workbook. By default, it is the first worksheet in the workbook;

③ Traverse all rows in the worksheet. The row is traversed from the second row by default. The first row (number 0) is the head of the cell;

④ Traverse each cell in the row and assign the attribute to the object according to certain rules.

Code:

/// <Summary> /// obtain data from Excel2003 and record it to the List set /// </summary> /// <param name = "cellHeard"> key and Value: {"UserName", "name" },{ "Age", "Age "}}; </param> /// <param name = "filePath"> Save the absolute path of the file </param> /// <param name = "errorMsg"> error message </param> /// <returns> converted List object set </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 converting an Excel file to an object, A formatted error message List <T> enlist = new List <T> (); // List of converted sets <string> keys = cellHeard. keys. toList (); // object attribute name to be assigned value try {using (FileStream fs = File. openRead (filePath) {HSSFWorkbook workbook = new HSSFWorkbook (fs); HSSFSheet sheet = (HSSFSheet) workbook. getSheetAt (0); // get the first Sheet page of this file for (int I = 1; I <= sheet. lastRowNum; I ++) // from 1 Start, 0th behavior unit header {// 1. checks whether the current row has empty rows. if the empty row does not read the next row, the Excel read operation if (sheet. getRow (I) = null) {break;} T en = new T (); string errStr = ""; // whether an error message exists during conversion of the current row, format: 1st rows data conversion exception: XXX column; for (int j = 0; j <keys. count; j ++) {// 2. if the attribute header name contains '. ', it indicates the attribute in the subclass, so we need to traverse the subclass, eg: UserEn. trueName if (keys [j]. indexOf (". ")> = 0) {// 2.1 parse the subclass attribute string [] properotyArray = keys [j]. split (new string [] {". "}, StringSplitOptions. removeE MptyEntries); string subClassName = properotyArray [0]; // '. 'string subClassProperotyName = properotyArray [1]; // '. 'System. reflection. propertyInfo subClassInfo = en. getType (). getProperty (subClassName); // obtain the subclass type if (subClassInfo! = Null) {// 2.1.1 obtain the sub-class instance var subClassEn = en. getType (). getProperty (subClassName ). getValue (en, null); // 2.1.2 obtain the attribute information System in the subclass Based on the attribute name. reflection. propertyInfo properotyInfo = subClassInfo. propertyType. getProperty (subClassProperotyName); if (properotyInfo! = Null) {try {// convert the value of the Excel cell to the value of the object property. If the type is incorrect, record the error information properotyInfo. setValue (subClassEn, GetExcelCellToProperty (properotyInfo. propertyType, sheet. getRow (I ). getCell (j), null);} catch (Exception e) {if (errStr. length = 0) {errStr = "+" + I + "row data conversion exception:";} errStr + = cellHeard [keys [j] + "column; ";}}} else {// 3. assign System to the specified attribute. reflection. propertyInfo properotyInfo = en. getType (). getProperty (keys [j ]); If (properotyInfo! = Null) {try {// convert the value of the Excel cell to the value of the object property. If the type is incorrect, record the error information properotyInfo. setValue (en, GetExcelCellToProperty (properotyInfo. propertyType, sheet. getRow (I ). getCell (j), null);} catch (Exception e) {if (errStr. length = 0) {errStr = "+" + I + "row data conversion exception:";} errStr + = cellHeard [keys [j] + "column; ";}}}// if an error message exists, add it to the error message if (errStr. length> 0) {errorMsg. appendLine (errStr);} enlist. add (en) ;}} return enlist;} catch (Exception ex) {throw ex ;}}

 

2.3 C # logic operation code

Note:Perform subsequent operations on the List converted from Excel, such as checking validity and persistent storage.

Steps:

① Call code 2.2 and convert the Excel file to List <T>.

② Check the validity of the List <T>: whether the required items are empty, whether repeated records exist, and so on.

③ Perform persistent storage for List <T>. For example, store data in a database.

④ Return the operation result.

Code:

Public void ImportExcel (HttpContext context) {StringBuilder errorMsg = new StringBuilder (); // error message try {# region 1. obtain the Excel file and convert it to a List set // 1.1 store the Excel file to the local server HttpPostedFile filePost = context. request. files ["filed"]; // obtain the uploaded file string filePath = ExcelHelper. saveExcelFile (filePost); // save the file and obtain the file path // Cell Header // key: Object object property name, which can be obtained through reflection // value: dictionary <string, string> cellheader = new Dicti Onary <string, string >{{ "Name", "Name" },{ "Age", "Age" },{ "GenderName", "gender "}, {"TranscriptsEn. chineseScores "," Chinese score "},{" TranscriptsEn. mathScores "," Mathematical score "},}; // parses the file and stores it in a List set. List <UserEntity> enlist = ExcelHelper. excelToEntityList <UserEntity> (cellheader, filePath, out errorMsg); # endregion # region 2. verify the validity of the List set # region 2.1 check whether required items are required for (int I = 0; I <enlist. count; I ++) {User Entity en = enlist [I]; string errorMsgStr = "nth" + (I + 1) + "Row Data Detection exception:"; bool isHaveNoInputValue = false; // whether the input item if (string. isNullOrEmpty (en. name) {errorMsgStr + = "Name column cannot be blank;"; isHaveNoInputValue = true;} if (isHaveNoInputValue) // {en. isExcelVaildateOK = false; errorMsg. appendLine (errorMsgStr) ;}# endregion # region 2.2 check for repeated objects in Excel for (int I = 0; I <enlist. count; I ++) {UserE Ntity enA = enlist [I]; if (enA. isExcelVaildateOK = false) // The above verification fails. do not perform this step to verify {continue;} for (int j = I + 1; j <enlist. count; j ++) {UserEntity enB = enlist [j]; // determines whether all the required columns are repeated if (enA. name = enB. name) {enA. isExcelVaildateOK = false; enB. isExcelVaildateOK = false; errorMsg. appendLine ("Number" + (I + 1) + "the required columns of the row and number" + (j + 1) + "already exist ");}}} # endregion // TODO: other detection # endregion // 3. TODO: Persistent storage of List sets Operation. For example, store it in a database // 4. return operation result bool isSuccess = false; if (errorMsg. length = 0) {isSuccess = true; // if the error message Chengdu is null, it 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); // return Json content} catch (Exception ex) {throw ex ;}}

　　

3. Excel export 3.1 export process

 

3.2 NPOI operation code

Note:Convert List to Excel

Steps:

① Create a Workbook );

② Create a worksheet in the workbook );

③ Create the first row on the worksheet, the first row column header, and write the cellHeard value (as the column name) in sequence ).

④ Cyclically traverse the List <T> set. Each loop creates a row, and then creates a row based on the cellHeard key (attribute name) the object values in List <T> are stored in cells in sequence.

Code:

/// <Summary> // The object class set is exported to Excle2003 // </summary> /// <param name = "cellHeard"> Key and Value of the unit header: {"UserName", "name" },{ "Age", "Age "}}; </param> /// <param name = "enList"> data source </param> /// <param name = "sheetName"> worksheet name </param> /// <returns> </returns> public static string EntityListToExcel2003 (Dictionary <string, string> cellHeard, IList enList, string sheetName) {try {string fileName = sheetName + "-" + Da TeTime. now. toString ("yyyyMMddHHmmssfff") + ". xls "; // file name string urlPath =" UpFiles/ExcelFiles/"+ fileName; // URL of the file download, which is provided to the front-end to download string filePath = HttpContext. current. server. mapPath ("\" + urlPath); // file path // 1. check whether a folder exists. If not, create a folder string directoryName = Path. getDirectoryName (filePath); if (! Directory. exists (directoryName) {Directory. createDirectory (directoryName);} // 2. parse the cell header and 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 the Key column name} // 3. list object Int rowIndex = 1; // The value is assigned from the second row (the first row has been set as the unit header) foreach (var en in enList) {IRow rowTmp = sheet. createRow (rowIndex); for (int I = 0; I <keys. count; I ++) // obtain the {string cellValue = ""; // The value of the Cell object properotyValue = null Based on the specified attribute name; // attribute value: System. reflection. propertyInfo properotyInfo = null; // attribute information // 3.1 If the attribute header name contains '. ', it indicates the attribute in the subclass, so we need to traverse the subclass, eg: UserEn. userName if (keys [I]. indexO F (". ")> = 0) {// 3.1.1 resolve the subclass attribute (only Layer 1 subclass is resolved here, and the multi-tier subclass is not processed) string [] properotyArray = keys [I]. split (new string [] {". "}, StringSplitOptions. removeEmptyEntries); string subClassName = properotyArray [0]; // '. 'string subClassProperotyName = properotyArray [1]; // '. 'System. reflection. propertyInfo subClassInfo = en. getType (). getProperty (subClassName); // obtain the subclass type if (subClassInfo! = Null) {// 3.1.2 obtain the sub-class instance var subClassEn = en. getType (). getProperty (subClassName ). getValue (en, null); // 3.1.3 obtain the property type properotyInfo = subClassInfo In the subclass based on the property name. propertyType. getProperty (subClassProperotyName); if (properotyInfo! = Null) {properotyValue = properotyInfo. getValue (subClassEn, null); // obtain the value of the subclass attribute }}} else {// 3.2 if it is not a subclass attribute, obtain the property properotyInfo = en of the object based on the property name. getType (). getProperty (keys [I]); if (properotyInfo! = Null) {properotyValue = properotyInfo. GetValue (en, null) ;}// the 3.3 property value is converted and assigned to the cell value if (properotyValue! = Null) {cellValue = properotyValue. toString (); // 3.3.1 assigns an empty if (cellValue. trim () = "0001/1/1 0:00:00" | cellValue. trim () = "0001/1/1 23:59:59") {cellValue = "" ;}}// fill in rowTmp in the Excel cell. createCell (I ). setCellValue (cellValue);} rowIndex ++;} // 4. generate file FileStream file = new FileStream (filePath, FileMode. create); workbook. write (file); file. close (); // 5. return download path return urlPath;} catch (Exception ex) {throw ex ;}}

 

3.3 C # logic operation code

Note:Perform subsequent operations on the List converted from Excel, such as checking validity and persistent storage.

Steps:

① Obtain the List <T> set.

② Call 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. obtain the data set 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 2", Age = 23, Gender = "Male ", transcriptsEn = new TranscriptsEntity {ChineseScores = 81, MathScores = 91 }}, new UserEntity {Name = "James", Age = 24, Gender = "Male ", transcriptsEn = new TranscriptsEntity {ChineseScores = 82, MathScores = 92 }}, new UserEntity {Name = "", Age = 25, Gender = "Male ", transcriptsEn = new TranscriptsEntity {ChineseScores = 83, MathScores = 93 }}, new UserEntity {Name = "", Age = 26, Gender = "Male ", transcriptsEn = new TranscriptsEntity {ChineseScores = 84, MathScores = 94 },}; // 2. set the cell header // key: object property name. You can obtain the value through reflection // value: name of the Excel column Dictionary <string, string> cellheader = new Dictionary <string, string >{{ "Name", "Name" },{ "Age", "Age" },{ "GenderName", "gender" },{ "TranscriptsEn. chineseScores "," Chinese score "},{" TranscriptsEn. mathScores "," Mathematical score "},}; // 3. perform Excel conversion 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); // return Json content} catch (Exception ex) {throw ex ;}}

 

3.4 code analysis

The core code mainly involves the celling between cellheader and List <T>:

 

4. Download the source code 4.1.

 

4.2

Baidu Network Disk: http://pan.baidu.com/s/1o69We8M