<>SSM Project's excel File upload and add to database ( Novice , Please give more advice on the shortcomings )

Curriculum design based on school , To use excel File upload and database import , I found a lot of them online demo, Finally, I found an article to use poi Of , After modification , It can be used normally . Write a blog and write it down , For future use .
Give me a general idea :

* The front desk will excel The file is passed in to the background
* use excel The tool class will excel Read data into files list aggregate
* take list The data in the collection passes through for-each take out , Insert into database .
* Back to front desk , Inserted successfully .
The first is to use jar package ( What I use in this project is Gradle)
compile 'org.apache.poi:poi:3.17' compile 'org.apache.poi:poi-ooxml:3.17'
compile 'org.apache.poi:poi-ooxml-schemas:3.17' compile
'org.apache.xmlbeans:xmlbeans:3.1.0' compile group: 'commons-fileupload', name:
'commons-fileupload', version: '1.3.1' compile group: 'commons-io', name:
'commons-io', version: '2.2'
Spring-MVC configuration file ( I almost forgot ..)
<bean id="multipartResolver"
class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
<property name="defaultEncoding" value="utf-8"></property> <property
name="maxUploadSize" value="10485760000"></property> <property
name="maxInMemorySize" value="40960"></property> </bean>
Post code
1.jsp code ( What I use here is easyui Of filebox, It can also be used directly file Input box , Use normal form submission )
<div style="margin:50px;width:150px"> <form id="form_excel" name="form_excel"
method="post" enctype="multipart/form-data"> <tr> <td> Please select Excel file :</td> <td>
<input class="easyui-filebox" id="file_excel" style="width:200%"
buttonText=" Select file " accept="application/vnd.ms-excel" name="file_excel" /> </td>
</tr> <tr> <td colspan="2" align="right"> <a id="submit" href="#"
class="easyui-linkbutton" data-options="iconCls:'icon-ok'"> add to </a> </td> </tr>
</form> </div> <script type="text/javascript"> $(function(){ // Login form submission
$("#submit").click(function(){ $('#form_excel').form('submit', {
url:'${pageContext.request.contextPath}/manage/DoExcel', onSubmit: function(){
}, success:function(data){ if(data=="1"){ alert(" Added successfully !");
location.href="${pageContext.request.contextPath}/manage/main"; }else
if(data=="2"){ alert(" Add failed !");
location.href="${pageContext.request.contextPath}/manage/main"; } } }); }) })
</script>
2.Excel Tools ( take Excel Convert data in file to list aggregate )
package com.niqi.Utils; import java.io.IOException; import
java.io.InputStream; import java.math.BigDecimal; import java.util.ArrayList;
import java.util.List; import org.apache.poi.hssf.usermodel.HSSFCell; import
org.apache.poi.hssf.usermodel.HSSFWorkbook; import
org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet; import
org.apache.poi.ss.usermodel.Workbook; import
org.apache.poi.xssf.usermodel.XSSFWorkbook; import
org.springframework.web.multipart.MultipartFile; import com.niqi.pojo.User; /**
* read excel Data in file * @author niqi * */ public class ReadExcel { // Total amount private int
totalRows = 0; // Total number of articles private int totalCells = 0; // Error message receiver private String
errorMsg; // Construction method public ReadExcel(){} // Get total rows public int getTotalRows() {
return totalRows;} // Get total number of columns public int getTotalCells() { return totalCells;}
// Get error information public String getErrorInfo() { return errorMsg; } /** *
read EXCEL file , Get information collection * @param fielName * @return */ public List<User>
getExcelInfo(MultipartFile mFile) { String fileName =
mFile.getOriginalFilename();// Get file name System.out.println(" file name "+fileName);
List<User> stuList = null; try { if (!validateExcel(fileName)) {// Verify that the file name is qualified
return null; } boolean isExcel2003 = true;// According to the file name, the file is 2003 Version or 2007 edition if
(isExcel2007(fileName)) { isExcel2003 = false; } stuList =
createExcel(mFile.getInputStream(), isExcel2003); } catch (Exception e) {
e.printStackTrace(); } return stuList; } /** * according to excel The content inside reads customer information * @param is
Input stream * @param isExcel2003 excel yes 2003 still 2007 edition * @return * @throws IOException */
public List<User> createExcel(InputStream is, boolean isExcel2003) { List<User>
stuList = null; try{ Workbook wb = null; if (isExcel2003) {//
When excel yes 2003 Time , establish excel2003 wb = new HSSFWorkbook(is); } else {//
When excel yes 2007 Time , establish excel2007 wb = new XSSFWorkbook(is); } stuList =
readExcelValue(wb);// read Excel Customer information inside } catch (IOException e) {
e.printStackTrace(); } return stuList; } /** * read Excel Customer information inside * @param wb *
@return */ private List<User> readExcelValue(Workbook wb) { // Get the first one shell Sheet
sheet = wb.getSheetAt(0); System.out.println("gaolei dayin============"
+sheet); // obtain Excel Number of rows for this.totalRows = sheet.getPhysicalNumberOfRows();
System.out.println(" Number of rows ======="+this.totalRows); // obtain Excel Number of columns for ( The premise is that there are rows ) if
(totalRows > 1 && sheet.getRow(0) != null) { this.totalCells =
sheet.getRow(0).getPhysicalNumberOfCells();
System.out.println(" Total number of columns =========="+this.totalCells); } List<User> userList =
new ArrayList<User>(); // loop Excel Number of rows for (int r = 1; r < totalRows; r++) { Row
row = sheet.getRow(r); if (row == null){ continue; } User user = new User(); //
loop Excel Column of ( This is based on the table fields , Self replacement ) for (int c = 0; c < this.totalCells; c++) { Cell cell
= row.getCell(c); if (null != cell) { // if (c == 0) { // if(cell.getCellType()
== HSSFCell.CELL_TYPE_NUMERIC){ // try { // user.setId(Integer.valueOf((int)
cell.getNumericCellValue())); // } catch (NumberFormatException e) { // // TODO
Auto-generated catch block // e.printStackTrace(); // System.out.println("if
false"); // } // }else{ // try { //
user.setId(Integer.valueOf(cell.getStringCellValue())); // } catch (Exception
e) { // // TODO Auto-generated catch block // e.printStackTrace(); //
System.out.println(" type mismatch "); // } // } // } else if (c == 1) {
if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
// When the import number is greater than 8 Bitemporal , Can use scientific counting , Use it here BigDecimal BigDecimal bd = new
BigDecimal(cell.getNumericCellValue()); String accountid = bd.toPlainString();
user.setAccountid(accountid);// account number }else{
user.setAccountid(cell.getStringCellValue()); } } else if (c == 2){
if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){ String password =
String.valueOf(cell.getNumericCellValue());
// If it's pure numbers , For example, what you wrote was 25,cell.getNumericCellValue() Get is 25.0, Remove by intercepting the string .0 get 25 String pwd
=password.substring(0, password.length()-2>0?password.length()-2:1);
user.setPassword(pwd); }else{ user.setPassword(cell.getStringCellValue()); } }
else if (c == 3){ if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){ int sid
= Integer.valueOf((int) cell.getNumericCellValue()); user.setSid(sid); }else{
user.setSid(Integer.valueOf((int) cell.getNumericCellValue())); } } } } //
Add to list userList.add(user); } return userList; } /** * verification EXCEL file * * @param
filePath * @return */ public boolean validateExcel(String filePath) { if
(filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
errorMsg = " The file name is not excel format "; return false; } return true; } //
@ describe : Is it 2003 Of excel, return true yes 2003 public static boolean isExcel2003(String
filePath) { return filePath.matches("^.+\\.(?i)(xls)$"); }
//@ describe : Is it 2007 Of excel, return true yes 2007 public static boolean isExcel2007(String
filePath) { return filePath.matches("^.+\\.(?i)(xlsx)$"); } }
3.ServiceImpl( Here we mainly perform the database insertion operation )
package com.niqi.service.impl; import java.util.List; import
org.springframework.beans.factory.annotation.Autowired; import
org.springframework.stereotype.Service; import
org.springframework.web.multipart.MultipartFile; import
com.niqi.Utils.ReadExcel; import com.niqi.dao.UserDao; import
com.niqi.pojo.User; import com.niqi.service.HelpThreeDoExcelService; /** *
excel Database insertion business implementation layer * @author niqi * */ @Service("helpThreeDoExcelService") public
class HelpThreeDoExcelServiceImpl implements HelpThreeDoExcelService {
@Autowired private UserDao userdao; @Override public String
readExcelFile(MultipartFile file) { // Create process EXCEL Class of ReadExcel readExcel=new
ReadExcel(); // analysis excel, Get the uploaded event list List<User> userList = null; int insertResult =
0; String insertMsg = ""; try { userList = readExcel.getExcelInfo(file);
// So far, the excel Convert data in to list It's inside , Next, you can operate list, Can be saved to the database , Or something else , // It's about your business , There is no concrete demonstration here
// Database insert for(User s :userList) { if(userdao.select(s)==null) { insertResult +=
userdao.insertSelective(s); } System.out.println(s.toString()); }
if(insertResult ==0) { insertMsg = "All insert false"; }else if(insertResult ==
userList.size()){ insertMsg = "All insert success"; }else { insertMsg = "Part
insert success"; } } catch (Exception e) { e.printStackTrace();
System.err.println(" accept excel Data in table failed !!!"); } for(User s : userList) {
System.out.println(" Printing excel Data in "+s.toString()); } return insertMsg; } }
4.Controller layer ( Interact with the front desk )
// How to add an account excel Related operations Ability to insert data into database @RequestMapping(value="DoExcel",method=
{RequestMethod.POST}) @ResponseBody public String
DoExcel(@RequestParam(value="file_excel") MultipartFile file,HttpServletRequest
request) { String readResult =null; try { readResult =
helpThreeDoExcelService.readExcelFile(file); System.out.println("canshu
:"+readResult); } catch (Exception e) { e.printStackTrace();
System.out.println("upload failture"); } if (readResult=="All insert false") {
return "2"; } System.out.println(" Insert results ="+readResult); return "1"; }
5.UserMapper.xml( I also posted it , It's a normal insert )
<!-- Insert account number --> <insert id="insertSelective" parameterType="com.niqi.pojo.User">
insert into user(accountid,password,sid)
values(#{accountid},#{password},#{sid}) </insert>
Finally, let's paste the running chart
Jsp page

Add failure graph ( ha-ha )

Finally, thank you for your reading , I wish you all the best ( bald ) Getting stronger !!!

Technology
©2019-2020 Toolsou All rights reserved,
Huawei 2021 session Hardware Engineer Logical post (FPGA) Super detailed surface !!!Vue-element-admin upgrade ui edition virtual machine VMware Download and install the most detailed tutorial !C++ Move constructor and copy constructor sound of dripping water java Backstage interview pygame Realize full screen mode and adjustable window size mysql Database setting character set configuration modification my.ini file (windows)30 What's the experience of being a junior programmer at the age of 20 C++ Multithreading programming ( Summary of common functions and parameters )python_ cherry tree