java中excel⽂件的导⼊，限制上传的⽂件类型，⽂件的⼤⼩，
显⽰上传⽂件的进度条
1. 前台代码
1<!DOCTYPE html>
2<html lang="en" xmlns:th="">
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/* 判断是否有导⼊⽂件 */
26if (!$("#excel").val()) {
27 window.alert("请导⼊excel⽂件");
28return;
29 }
30
31/* 判断输⼊的⽂件的类型 */
32var splitArray = $("#excel").val().toLowerCase().split(".");
33var type = splitArray[splitArray.length - 1];
34if (type != "xls" && type != "xlsx") {
35 window.alert("导⼊的⽂件类型有误");
36return;
37 }
38
39// 获取excel对象
40var excel = $("#excel")[0].files[0];
41
42/* 判断输⼊的excel⽂件的⼤⼩ */
43var excelSize = excel.size;
44if (excelSize > 1024 * 1024 * 10) {
45 window.alert("当前上传的excel⽂件的⼤⼩为" +
46 und(excelSize / 1024 / 1024 * 100) / 100 +
47 "M，超过10M");
48return;
49 }
50
51var 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();
63if (myXhr.upload) {
inputtypefile不上传文件
64 myXhr.upload.addEventListener('progress', function (e) {
65var loaded = e.loaded;//已经上传⼤⼩情况
66var tot = e.total;//附件总⼤⼩
67var per = Math.floor(100 * loaded / tot); //已经上传的百分⽐
68 $("#sonDiv").html(per + "%");
69 $("#sonDiv").css("width", per + "%");
70 $("#schedule").und(loaded / 1024 / 1024 * 100) / 100 + "M");
71 console.log('附件总⼤⼩ = ' + loaded);
72 console.log('已经上传⼤⼩ = ' + tot);
73 }, false);
74 }
75return myXhr;
76 },
77 success: function (json) {
78if (sult) {
79 alert("导⼊成功")
80 } else {
81 Message);
82 }
83 }
84 });
85 })
86 })
87</script>
88</head>
89<body>
90请选择你要导⼊的excel⽂件(.xls;.xlsx)<br/>
91<input type="file" id="excel"/>
92<div id="fatherDiv">
93<div id="sonDiv"></div><span id="schedule">已上传 0M</span> 94<button id="importExcel">导⼊</button>
95</div><br/>
96
97
98
99</body>
100</html>
2. 后台spring代码
2.1 controller层
1package ller;
2
3import l.importExcel.service.ImportExcelService;
4import org.apache.poi.hssf.usermodel.HSSFWorkbook;
5import org.apache.poi.ss.usermodel.Workbook;
6import org.apache.poi.xssf.usermodel.XSSFWorkbook;
7import org.springframework.beans.factory.annotation.Autowired;
8import org.springframework.stereotype.Controller;
9import org.springframework.web.bind.annotation.RequestMapping;
10import org.springframework.web.bind.annotation.RequestParam;
11import org.springframework.web.bind.annotation.ResponseBody;
12import org.springframework.web.multipart.MultipartFile;
13
14import java.io.IOException;
15import java.io.InputStream;
16import flect.InvocationTargetException;
17import java.util.HashMap;
18import java.util.Map;
19
20/**
21 * ClassName:ImportExcel
22 * Package:ller
23 * Description:
24 *
25 * @Date:2019/2/25 21:46
26 * @Author: 郑军
27*/
28 @Controller
29public class ImportExcelController {
30
31 @Autowired
32private ImportExcelService importExcelService;
33
34 @RequestMapping("/")
35public String toIndex() {
36return "index";
37 }
38
39 @RequestMapping("/importExcel")
40 @ResponseBody
41public Map<String, Object> importExcel(@RequestParam("file") MultipartFile file) { 42
43 Map<String, Object> map = new HashMap<>();
44
45/**
46 * 判断⽂件是否为空
47*/
48if (file.isEmpty()) {
49 map.put("result", false);
50 map.put("errorMessage", "导⼊数据为空");
51return map;
52 }
53
54/**
55 * 判断⽂件类型是否正确
56*/
57 String originalFilename = OriginalFilename();
58 String fileType = originalFilename.substring(originalFilename.lastIndexOf("."));
59if (!".xls".equals(fileType) && !".xlsx".equals(fileType)) {
60 map.put("result", false);
61 map.put("errorMessage", "导⼊的⽂件类型有误");
62return map;
63 }
64
65/**
66 * 判断⽂件的⼤⼩
67*/
68long size = Size();
69if (size > 1024 * 1024 * 10) {
70 map.put("result", false);
71 map.put("errorMessage", "当前⽂件⼤⼩为"+il(size * 100 / 1024/ 1024 / 10 / 100) +
72 "M，超过10M");
73return map;
74 }
75
76try {
77 InputStream inputStream = InputStream();
78/**
79 * 通过上传的不同的⽂件后缀，创建不同的workbook
80*/
81 Workbook workbook = null;
82if (".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);
92if (resultMap.size() > 0) {
93 map.put("result", ("result"));
94 map.put("errorMessage", ("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
106return map;
107
108 }
109
110 }
2.2 service业务层
1package l.importExcel.service.impl;
2
3import l.importExcel.domain.Salary;
4import l.importExcel.mapper.SalaryMapper;
5import l.importExcel.service.ImportExcelService;
6import org.apachemons.beanutils.BeanUtils;
7import org.ansaction.Transaction;
8import org.apache.poi.ss.usermodel.Cell;
9import org.apache.poi.ss.usermodel.Row;
10import org.apache.poi.ss.usermodel.Sheet;
11import org.apache.poi.ss.usermodel.Workbook;
12import org.springframework.beans.factory.annotation.Autowired;
13import org.springframework.stereotype.Service;
14import ansaction.annotation.Transactional;
15
16import java.io.IOException;
17import flect.InvocationTargetException;
18import java.util.ArrayList;
19import java.util.HashMap;
20import java.util.List;
21import java.util.Map;
22
23/**
24 * ClassName:ImportExcelServiceImpl
25 * Package:l.importExcel.service.impl
26 * Description:
27 *
28 * @Date:2019/2/26 21:57
29 * @Author: 郑军
30*/
31 @Service
32public class ImportExcelServiceImpl implements ImportExcelService {
33
34 @Autowired
35private SalaryMapper salaryMapper;
36
37 @Override
38 @Transactional(rollbackFor = RuntimeException.class)
39public Map<String, Object> importExcel(Workbook workbook) throws InvocationTargetException, IllegalAccessException { 40
41 Map<String, Object> resultMap = new HashMap<>();
42 List<List<Salary>> salaryDateList = new ArrayList<>();
43int numberOfSheets = NumberOfSheets();
44
45
46for (int i = 0; i < numberOfSheets; i++) {
47 Sheet sheet = SheetAt(i);
48// 将sheet页中的数据，转换成list集合
49 List<Salary> salaryList = parseExcelToList(sheet);
50// 将每⼀个sheet页的数据，然后放在⼀个list集合中。
51 salaryDateList.add(salaryList);
52
53 }
54
55// 按照业务逻辑，将不同sheet页的数据，插⼊不同的数据库表中。这个地⽅只⽤了⼀张表
56for (List<Salary> salaryList : salaryDateList) {
57 salaryMapper.insertExcelData(salaryList);
58 }
59
60// 导⼊成功之后，向resultMap中添加返回成功信息
61 resultMap.put("result", true);
62 resultMap.put("errorMessage", "导⼊成功");
63
64return resultMap;
65 }
66
67/**
68 * 将sheet中的数据转换成⼀个list集合。
69 *
70 * @param sheet
71 * @return
72*/
73private List<Salary> parseExcelToList(Sheet sheet) throws InvocationTargetException, IllegalAccessException {
74
75// excel导⼊的时候，对应的数据库中的字段数组。
76 String[] property = {"account", "money"};
77 List<Salary> salaryList = new ArrayList<>();
78
79int lastRowNum = LastRowNum();
80// 如果sheet页的数据为空
81if (lastRowNum < 1) {
82return null;
83 }
84
85for (int i = 1; i < lastRowNum + 1; i++) {
86
87 Map<String, Object> map = new HashMap<>();
88
89 Row row = Row(i);
90if (row == null) {
91continue;
92 }
93
94for (int j = 0; j < property.length; j++) {
95 Cell cell = Cell(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
108return salaryList;
109 }
110
111/**
112 * 获取单元格对应的值
113 *
114 * @param cell 单元格对象
115 * @return单元格对应的值
116*/
117private Object getCellValue(Cell cell) {
118
119int cellType = CellType();
120 Object cellValue = null;
121
122switch (cellType) {
123
124// 数值获取对应的数据
125case 0:
126 cellValue = NumericCellValue();
127break;
128
129// 字符串获取去除⾸位空格
130case 1:
131 cellValue = StringCellValue().trim();
132break;
133
134case 2:
135 cell.setCellType(1);
136if (StringCellValue() == null
137 || StringCellValue().length() == 0)
138break;
139 cellValue = StringCellValue().replaceAll("#N/A", "").trim(); 140 cellValue += "&CELL_TYPE_FORMULA";
141break;
142
143case 3:
144 cellValue = "";
145break;
146
147case 4:
148 cellValue = BooleanCellValue());
149break;
150
151case 5:
152 cellValue = "";
153break;
154
155default:
156 cellValue = "";
157 }
158
159return cellValue;
160
161 }
162
163 }
View Code
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论