java中excel文件的导入，限制上传的文件类型，文件的大小，显示上传文件的进度条

1. 前台代码

  1 <!DOCTYPE html>
  2 <html lang="en" xmlns:th="http://www.thymeleaf.org">
  3 <head>
  4     <meta charset="UTF-8">
  5     <title>导入excel测试</title>
  6     <style>
  7         #fatherDiv{
  8             width:100px;
  9             height:20px;
 10             border:1px solid green;
 11         }
 12         #sonDiv{
 13             width:0px;
 14             height:20px;
 15             background:green;
 16         }
 17     </style>
 18 
 19     <script th:src="@{|/js/jquery-2.1.4.min.js/|}"></script>
 20     <script>
 21         $(function () {
 22 
 23             $("#importExcel").bind("click", function () {
 24 
 25                 /* 判断是否有导入文件 */
 26                 if (!$("#excel").val()) {
 27                     window.alert("请导入excel文件");
 28                     return;
 29                 }
 30 
 31                 /* 判断输入的文件的类型 */
 32                 var splitArray = $("#excel").val().toLowerCase().split(".");
 33                 var type = splitArray[splitArray.length - 1];
 34                 if (type != "xls" && type != "xlsx") {
 35                     window.alert("导入的文件类型有误");
 36                     return;
 37                 }
 38 
 39                 // 获取excel对象
 40                 var excel = $("#excel")[0].files[0];
 41 
 42                 /* 判断输入的excel文件的大小 */
 43                 var excelSize = excel.size;
 44                 if (excelSize > 1024 * 1024 * 10) {
 45                     window.alert("当前上传的excel文件的大小为" +
 46                         Math.round(excelSize / 1024 / 1024 * 100) / 100 +
 47                         "M,超过10M");
 48                     return;
 49                 }
 50 
 51                 var formData = new FormData();
 52                 formData.append("file", excel);
 53 
 54                 // ajax异步文件上传
 55                 $.ajax({
 56                     type: "post",
 57                     url: "/importExcel",
 58                     data: formData,
 59                     contentType: false, // 不再采用普通的form表单元素提交方式。(multipart/form-data)
 60                     processData: false, // 提交文件，不是提交普通的字符串。
 61                     xhr: function () {  // 显示上传进度条
 62                         myXhr = $.ajaxSettings.xhr();
 63                         if (myXhr.upload) {
 64                             myXhr.upload.addEventListener('progress', function (e) {
 65                                 var loaded = e.loaded;//已经上传大小情况
 66                                 var tot = e.total;//附件总大小
 67                                 var per = Math.floor(100 * loaded / tot); //已经上传的百分比
 68                                 $("#sonDiv").html(per + "%");
 69                                 $("#sonDiv").css("width", per + "%");
 70                                 $("#schedule").html(Math.round(loaded / 1024 / 1024 * 100) / 100 + "M");
 71                                 console.log('附件总大小 = ' + loaded);
 72                                 console.log('已经上传大小 = ' + tot);
 73                             }, false);
 74                         }
 75                         return myXhr;
 76                     },
 77                     success: function (json) {
 78                         if (json.result) {
 79                             alert("导入成功")
 80                         } else {
 81                             alert(json.errorMessage);
 82                         }
 83                     }
 84                 });
 85             })
 86         })
 87     </script>
 88 </head>
 89 <body>
 90     请选择你要导入的excel文件(.xls;.xlsx)<br/>
 91     <input type="file" id="excel"/> &nbsp;&nbsp;
 92     <div id="fatherDiv">
 93         <div id="sonDiv"></div> <span id="schedule">已上传 0M</span> &nbsp;&nbsp;
 94         <button id="importExcel">导入</button>
 95     </div><br/>
 96 
 97 
 98 
 99 </body>
100 </html>

2. 后台spring代码

　　2.1 controller层

  1 package com.bjpowernode.excel.importExcel.controller;
  2 
  3 import com.bjpowernode.excel.importExcel.service.ImportExcelService;
  4 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  5 import org.apache.poi.ss.usermodel.Workbook;
  6 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  7 import org.springframework.beans.factory.annotation.Autowired;
  8 import org.springframework.stereotype.Controller;
  9 import org.springframework.web.bind.annotation.RequestMapping;
 10 import org.springframework.web.bind.annotation.RequestParam;
 11 import org.springframework.web.bind.annotation.ResponseBody;
 12 import org.springframework.web.multipart.MultipartFile;
 13 
 14 import java.io.IOException;
 15 import java.io.InputStream;
 16 import java.lang.reflect.InvocationTargetException;
 17 import java.util.HashMap;
 18 import java.util.Map;
 19 
 20 /**
 21  * ClassName:ImportExcel
 22  * Package:com.bjpowernode.excel.importExcel.controller
 23  * Description:
 24  *
 25  * @Date:2019/2/25 21:46
 26  * @Author: 郑军
 27  */
 28 @Controller
 29 public class ImportExcelController {
 30 
 31     @Autowired
 32     private ImportExcelService importExcelService;
 33 
 34     @RequestMapping("/")
 35     public String toIndex() {
 36         return "index";
 37     }
 38 
 39     @RequestMapping("/importExcel")
 40     @ResponseBody
 41     public Map<String, Object> importExcel(@RequestParam("file") MultipartFile file) {
 42 
 43         Map<String, Object> map = new HashMap<>();
 44 
 45         /**
 46          * 判断文件是否为空
 47          */
 48         if (file.isEmpty()) {
 49             map.put("result", false);
 50             map.put("errorMessage", "导入数据为空");
 51             return map;
 52         }
 53 
 54         /**
 55          * 判断文件类型是否正确
 56          */
 57         String originalFilename = file.getOriginalFilename();
 58         String fileType = originalFilename.substring(originalFilename.lastIndexOf("."));
 59         if (!".xls".equals(fileType) && !".xlsx".equals(fileType)) {
 60             map.put("result", false);
 61             map.put("errorMessage", "导入的文件类型有误");
 62             return map;
 63         }
 64 
 65         /**
 66          * 判断文件的大小
 67          */
 68         long size = file.getSize();
 69         if (size > 1024 * 1024 * 10) {
 70             map.put("result", false);
 71             map.put("errorMessage", "当前文件大小为"+Math.ceil(size * 100 / 1024/ 1024 / 10 / 100) +
 72             "M,超过10M");
 73             return map;
 74         }
 75 
 76         try {
 77             InputStream inputStream = file.getInputStream();
 78             /**
 79              *  通过上传的不同的文件后缀，创建不同的workbook
 80              */
 81             Workbook workbook = null;
 82             if (".xls".equals(fileType)) {
 83                 workbook = new HSSFWorkbook(inputStream);
 84             }else {
 85                 workbook = new XSSFWorkbook(inputStream);
 86             }
 87 
 88             /**
 89              * excel文件解析并且导入数据库。
 90              */
 91             Map<String, Object> resultMap = importExcelService.importExcel(workbook);
 92             if (resultMap.size() > 0) {
 93                 map.put("result", resultMap.get("result"));
 94                 map.put("errorMessage", resultMap.get("errorMessage"));
 95             } else {
 96                 map.put("result", false);
 97                 map.put("errorMessage", "导入失败");
 98             }
 99 
100         } catch (Exception e) {
101             e.printStackTrace();
102             map.put("result", false);
103             map.put("errorMessage", "导入失败");
104         }
105 
106         return  map;
107 
108     }
109 
110 }

2.2 service业务层

  1 package com.bjpowernode.excel.importExcel.service.impl;
  2 
  3 import com.bjpowernode.excel.importExcel.domain.Salary;
  4 import com.bjpowernode.excel.importExcel.mapper.SalaryMapper;
  5 import com.bjpowernode.excel.importExcel.service.ImportExcelService;
  6 import org.apache.commons.beanutils.BeanUtils;
  7 import org.apache.ibatis.transaction.Transaction;
  8 import org.apache.poi.ss.usermodel.Cell;
  9 import org.apache.poi.ss.usermodel.Row;
 10 import org.apache.poi.ss.usermodel.Sheet;
 11 import org.apache.poi.ss.usermodel.Workbook;
 12 import org.springframework.beans.factory.annotation.Autowired;
 13 import org.springframework.stereotype.Service;
 14 import org.springframework.transaction.annotation.Transactional;
 15 
 16 import java.io.IOException;
 17 import java.lang.reflect.InvocationTargetException;
 18 import java.util.ArrayList;
 19 import java.util.HashMap;
 20 import java.util.List;
 21 import java.util.Map;
 22 
 23 /**
 24  * ClassName:ImportExcelServiceImpl
 25  * Package:com.bjpowernode.excel.importExcel.service.impl
 26  * Description:
 27  *
 28  * @Date:2019/2/26 21:57
 29  * @Author: 郑军
 30  */
 31 @Service
 32 public class ImportExcelServiceImpl implements ImportExcelService {
 33 
 34     @Autowired
 35     private SalaryMapper salaryMapper;
 36 
 37     @Override
 38     @Transactional(rollbackFor = RuntimeException.class)
 39     public Map<String, Object> importExcel(Workbook workbook) throws InvocationTargetException, IllegalAccessException {
 40 
 41         Map<String, Object> resultMap = new HashMap<>();
 42         List<List<Salary>> salaryDateList = new ArrayList<>();
 43         int numberOfSheets = workbook.getNumberOfSheets();
 44 
 45 
 46         for (int i = 0; i < numberOfSheets; i++) {
 47             Sheet sheet = workbook.getSheetAt(i);
 48             // 将sheet页中的数据，转换成list集合
 49             List<Salary> salaryList = parseExcelToList(sheet);
 50             // 将每一个sheet页的数据，然后放在一个list集合中。
 51             salaryDateList.add(salaryList);
 52 
 53         }
 54 
 55         // 按照业务逻辑，将不同sheet页的数据，插入不同的数据库表中。这个地方只用了一张表
 56         for (List<Salary> salaryList : salaryDateList) {
 57             salaryMapper.insertExcelData(salaryList);
 58         }
 59 
 60         // 导入成功之后，向resultMap中添加返回成功信息
 61         resultMap.put("result", true);
 62         resultMap.put("errorMessage", "导入成功");
 63 
 64         return resultMap;
 65     }
 66 
 67     /**
 68      * 将sheet中的数据转换成一个list集合。
 69      *
 70      * @param sheet
 71      * @return
 72      */
 73     private List<Salary> parseExcelToList(Sheet sheet) throws InvocationTargetException, IllegalAccessException {
 74 
 75         // excel导入的时候，对应的数据库中的字段数组。
 76         String[] property = {"account", "money"};
 77         List<Salary> salaryList = new ArrayList<>();
 78 
 79         int lastRowNum = sheet.getLastRowNum();
 80         // 如果sheet页的数据为空
 81         if (lastRowNum < 1) {
 82             return null;
 83         }
 84 
 85         for (int i = 1; i < lastRowNum + 1; i++) {
 86 
 87             Map<String, Object> map = new HashMap<>();
 88 
 89             Row row = sheet.getRow(i);
 90             if (row == null) {
 91                 continue;
 92             }
 93 
 94             for (int j = 0; j < property.length; j++) {
 95                 Cell cell = row.getCell(j);
 96                 // 获取单元格对应的数据值。单元格中的数据对应着可能为日期，字符串，数字
 97                 Object value = getCellValue(cell);
 98                 map.put(property[j], value);
 99             }
100 
101             Salary salary = new Salary();
102             BeanUtils.populate(salary, map);
103             salary.setsId(i + "");
104             salaryList.add(salary);
105 
106         }
107 
108         return salaryList;
109     }
110 
111     /**
112      * 获取单元格对应的值
113      *
114      * @param cell 单元格对象
115      * @return 单元格对应的值
116      */
117     private Object getCellValue(Cell cell) {
118 
119         int cellType = cell.getCellType();
120         Object cellValue = null;
121 
122         switch (cellType) {
123 
124             // 数值获取对应的数据
125             case 0:
126                 cellValue = cell.getNumericCellValue();
127                 break;
128 
129             // 字符串获取去除首位空格
130             case 1:
131                 cellValue = cell.getStringCellValue().trim();
132                 break;
133 
134             case 2:
135                 cell.setCellType(1);
136                 if (cell.getStringCellValue() == null
137                         || cell.getStringCellValue().length() == 0)
138                     break;
139                 cellValue = cell.getStringCellValue().replaceAll("#N/A", "").trim();
140                 cellValue += "&CELL_TYPE_FORMULA";
141                 break;
142 
143             case 3:
144                 cellValue = "";
145                 break;
146 
147             case 4:
148                 cellValue = Boolean.toString(cell.getBooleanCellValue());
149                 break;
150 
151             case 5:
152                 cellValue = "";
153                 break;
154 
155             default:
156                 cellValue = "";
157         }
158 
159         return cellValue;
160 
161     }
162 
163 }
View Code
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/rensheng/p/10434877.html