Project development experience sharing-import Excel and Project Development Experience Sharing

Project development experience sharing-import Excel and Project Development Experience Sharing

In the previous blog, we shared the UI design experience "Beautiful pop-up box effects". Today, let's share our data upload experience: Import Excel!

Introduction as an information management system, we need to input a lot of data, such as the student management system. We need to add a lot of student information, as shown in: at this time, it would be too troublesome to add one by one. Is there a simple way to add the data at once? The answer is yes, and many systems have designed the -- add Excel import function to facilitate the direct import of information to the database through Excel, reducing the workload of users. With this user-friendly feature, we should also learn from similar systems to provide users with a higher user experience! Let's take a look at the specific implementation:
Ideas

1. Introduce commons-fileupload.jar package and add related dependencies

2. Add the ExcelUtility tool class

3. Configure SpringMVC

4. upload an Excel file and save it to the database.

Implementation

1. Introduce the commons-fileupload.jar package and add related dependencies in the pom file of the web:

<pre name="code" class="html"><dependency>      <groupId>commons-fileupload</groupId>      <artifactId>commons-fileupload</artifactId>      <version>1.3.1</version>   </dependency>  

2. Add the ExcelUtility tool class

(1) Excel import code in the ExcelUtility tool class:

/*** Import an excel table * @ param in carries the Excel input stream * @ param sheetName indicates the set of worksheet names to be entered, for example, String [] sheetName = {"sheet1 ", "sheet2", "sheet3 ", "sheet4"} * @ param entityClass List object type * @ param fieldMap ing between the Chinese column headers in Excel and English attributes of the class Map * @ param uniqueFields specifies the combination of business primary keys (composite primary key ), the combination of these columns cannot be repeated * @ return */public <T> List excelToList (InputStream in, String [] sheetName, LinkedHashMap <String, Class <?> EntityClass, LinkedHashMap <String, LinkedHashMap <String, String> fieldMap, LinkedHashMap <String, String []> uniqueFields) {// define the List to be returned <T> resultList = new ArrayList <T> (); try {// create WorkBookWorkbook wb = Workbook Based on the Excel data source. getWorkbook (in); // get the worksheet for (int k = 0; k <sheetName. length; k ++) {resultList. clear (); Sheet sheet = wb. getSheet (sheetName [k]); String singleSheetName = sheetName [k]; // get sh The eet name is Class <?> EnClassName = null; // returns the object for (Entry <String, Class <?> Entry: entityClass. entrySet () {// obtain the Chinese field name String enSheetName = entry. getKey (); // obtain the English field name if (enSheetName. equals (singleSheetName) {enClassName = entry. getValue () ;}// get the valid number of rows in the worksheet int realRows = 0; for (int I = 0; I <sheet. getRows (); I ++) {int nullCols = 0; for (int j = 0; j <sheet. getColumns (); j ++) {Cell currentCell = sheet. getCell (j, I); if (currentCell = null | "". equals (currentCell. getContents (). ToString () {nullCols ++ ;}} if (nullCols = sheet. getColumns () {break;} else {realRows ++;} // if no data exists in the Excel file, the error if (realRows <= 1) is returned) {throw new ExcelException ("" + sheetName [k] + "no data" in the Excel file);} Cell [] firstRow = sheet. getRow (0); String [] excelFieldNames = new String [firstRow. length]; // obtain the name of the column in Excel (int I = 0; I <firstRow. length; I ++) {excelFieldNames [I] = firstRow [I]. getContents (). toString (). Trim () ;}// determines whether all required fields exist in Excel. boolean isExist = true; List <String> excelFieldList = Arrays. asList (excelFieldNames); LinkedHashMap <String, String> enfiledMap = new LinkedHashMap <String, String> (); // give the common field corresponding to the sheet in the object for (Entry <String, linkedHashMap <String, String> entry: fieldMap. entrySet () {// obtain the Chinese field name String enSheetName = entry. getKey (); // obtain the English field name if (enSheetName. equals (singleSheetName) {enfiledM Ap = entry. getValue (); for (String cnName: enfiledMap. keySet () {if (! ExcelFieldList. contains (cnName) {isExist = false; break ;}}// if a column name does not exist, an exception is thrown and the error if (! IsExist) {throw new ExcelException ("necessary fields are missing in Excel or the field name is incorrect");} // put the column name and column number in Map, in this way, you can obtain the column number LinkedHashMap <String, Integer> colMap = new LinkedHashMap <String, Integer> (); for (int I = 0; I <excelFieldNames. length; I ++) {colMap. put (excelFieldNames [I], firstRow [I]. getColumn ();} // specifies the repeated field for the sheet in the object for (Entry <String, String []> fields: uniqueFields. entrySet () {// obtain the Chinese field name String enSheetName = fields. ge TKey (); String [] enuniqueFile = null; // obtain the English field name if (enSheetName. equals (singleSheetName) {enuniqueFile = fields. getValue (); // determines whether duplicate rows exist. // 1. obtain the Cell [] [] uniqueCells = new Cell [enuniqueFile specified by uniqueFields. length] []; for (int I = 0; I <enuniqueFile. length; I ++) {int col = colMap. get (enuniqueFile [I]); uniqueCells [I] = sheet. getColumn (col);} // 2. find the duplicate row for (int I = 1; I <realRows; I ++) {int nullCols = 0; For (int j = 0; j <enuniqueFile. length; j ++) {String currentContent = uniqueCells [j] [I]. getContents (); Cell sameCell = sheet. findCell (currentContent, uniqueCells [j] [I]. getColumn (), uniqueCells [j] [I]. getRow () + 1, uniqueCells [j] [I]. getColumn (), uniqueCells [j] [realRows-1]. getRow (), true); if (sameCell! = Null) {nullCols ++;} if (nullCols = enuniqueFile. length) {throw new ExcelException ("Duplicate rows exist in Excel, please check") ;}}// convert sheet to listfor (int I = 1; I <realRows; I ++) {if (enClassName! = Null) {// create the object to be converted T entity = (T) enClassName. newInstance (); // assign a value to the field in the object for (Entry <String, String> entry: enfiledMap. entrySet () {// obtain the Chinese field name String cnNormalName = entry. getKey (); // obtain the English field name String enNormalName = entry. getValue (); // obtain the column number int col = colMap based on the Chinese field name. get (cnNormalName); // get the content in the current cell String content = sheet. getCell (col, I ). getContents (). toString (). trim (); // assign setFieldValueByName (enNormalName, content, entity) to the object;} resultList. add (entity) ;}}} catch (Exception e) {e. printStackTrace ();} return resultList ;}

(2) Add related dependencies to the web pom file:

<dependency>      <groupId>com.tgb</groupId>      <artifactId>itoo-assess-tool</artifactId>      <version>0.0.1-SNAPSHOT</version>  </dependency> 

3. Configure SpringMVC

<! -- Spring MVC requires the MultipartResolver Processor --> <span style = "white-space: pre"> </span> <bean id = "multipartResolver" class = "org. springframework. web. multipart. commons. commonsMultipartResolver "> <property name =" defaultEncoding "value =" UTF-8 "/> <! -- The total size of the uploaded file cannot exceed 10485760000B. Note that the limit of the maxUploadSize attribute is not for a single file, but the sum of the capacity of all files --> <property name = "maxUploadSize" value = "10485760000"> </property> <property name = "maxInMemorySize" value = "40960"> </ property> </bean>

4. upload an Excel file and save it to the database.

(1) Front-end Jsp code

<A href = "javascript: void (0)" class = "easyui-linkbutton" iconCls = "icon-remove" plain = "true" onclick = "importStudent (); "> Import </a> <div id =" studentImport "class =" easyui-window "title =" batch import students "data-options =" modal: true, closed: true, "style =" width: 700px; height: 350px; padding: 10px; "> <form id =" Manage "method =" post "enctype =" multipart/form-data "action =" "novalidate> <a href =" $ {pageContext. request. contextPath}/student/leadToExcelTemplet "class =" easyui-linkbutton "style =" width: 120px "> click to download the template </a> <br> <input id =" uploadExcel "name =" uploadExcel "class =" easyui-filebox "style =" width: 60% "data-options =" prompt: 'select a file... '"> <a href =" # "class =" easyui-linkbutton "style =" width: 10% "onclick =" uploadExcel () "> Import students </a> </form> </div>

(2) Front-end js Code

Function uploadExcel () {// obtain the full path of the uploaded file var fileName =$ ('# uploadExcel '). filebox ('getvalue') // perform basic verification if (fileName = "") {alert ("Select Upload File! ");} Else {// verify the file format var d1 = /\. [^ \.] + $ /. exec (fileName); if (d1 = ". xls ") {$ ('# manage '). form ('submit ', {url: "$ {pageContext. request. contextPath}/student/import ", onSubmit: function () {return $ (this ). form ('validate');}, success: function (result) {var result = eval ('+ result +'); if (result. errorMsg) {$. messager. show ({title: 'error', msg: result. errorMsg});} else {$. messager. alert ("prompt", "Import successful! "," Info "); $ ('# studentImport '). dialog ('close'); // close the dialog $ ('# dg '). datagrid ('reload'); // reload the Student data }});} else {alert ("select the xls format file! "); $ ('# UploadExcel '). filebox ('setvalue', '') ;}}// import the student function importTeacehr () {$ ('# studentImport '). dialog ('open '). dialog ('settitle', 'batch import ');}

(3) Background Controller code

Private ExcelUtil excelUtil; public ExcelUtil getExcelUtil () {return excelUtil;} public void setExcelUtil (ExcelUtil excelUtil) {this. excelUtil = excelUtil;}/*** export excel template * @ param request * @ param resposne response * @ throws UnsupportedEncodingException encoding exception */@ RequestMapping ("/student/leadToExcelTemplet ") public void leadToExcelQuestionBankTemplet (HttpServletRequest request, HttpServletResponse response) Throws UnsupportedEncodingException {excelUtil = new ExcelUtil (); try {// table header, mapLinkedHashMap <String, String> fieldMap = new LinkedHashMap <String, String> (); fieldMap. put ("code", "student ID"); fieldMap. put ("name", "name"); fieldMap. put ("sex", "gender"); fieldMap. put ("institution", "");...... FieldMap. put ("class", "class"); // sheetNameString sheetName = student information in excel "; // export excelUtil. leadToExcel (fieldMap, sheetName, response); System. out. println ("template exported successfully ~~~~ ");} Catch (ExcelException e) {e. printStackTrace () ;}/ *** @ MethodName: importTeacher * @ Description: import * @ throws Exception */@ RequestMapping ("/student/import ") public void importTeacher (@ RequestParam ("uploadExcel") implements uploadExcel, HttpServletResponse response, HttpServletRequest request) throws Exception {String result = "error"; jacksonJsonUntil = new response (); InputStream In; excelUtil = new ExcelUtil (); boolean flag = false; try {in = uploadExcel. getInputStream (); String sheetName = null; // preparations before importing an Excel file <String, String> normalFieldMap = new LinkedHashMap <String, String> (); // 1. set the correspondence between the field name and the common attribute name of the class in Excel, such as the user name. <pre name = "code" class = "java" style = "color: rgb (51, 51, 51); font-size: 18px; line-height: 26px; "> fieldMap. put ("code", "student ID"); fieldMap. put ("name", "name" ); FieldMap. put ("sex", "gender"); fieldMap. put ("institution", "");...... FieldMap. put ("class", "class"); sheetName = "Student Information"; String [] uniqueFields = {"student ID", "name", "class "}; int error = excelUtil. importExcel (in, Teacher. class, normalFieldMap, referFieldMap, thirdFieldMap, uniqueFields, response); if (error = 0) {result = "success" ;}} catch (Exception e) {e. printStackTrace ();} jacksonJsonUntil. beanToJson (response, result );}

Data upload experience-import an Excel file and share it here. Next blog will continue to share data download experience-export an Excel file.

Summary

From the user's point of view, the user-oriented, closer to the user's usage habits, the higher the software affinity, the more popular the software development!