前言
作为java开发，excel文件的上传和导出应该说是必须要掌握的技能了，但是如果之前没做过的话还是会有一定的难度，这里我记录一下这两个工具的轮子方便以后直接拿过来用
Excel文件上传和读取
这里我是默认使用springboot项目进行演示，因为springboot方便，如果不是springboot项目也只需要稍作修改即可
必要的依赖导入
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
| <dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.7.21</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.7</version>
</dependency>
|
都是常规的依赖 没什么好说的了
前端页面
这里我们就简单写一个input组件和导出的超链接模拟就行
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
| <!DOCTYPE html>
<html >
<head>
<meta charset="UTF-8">
<title>首页</title>
</head>
<body>
<form action="/excel" method="post" enctype="multipart/form-data">
<input type="file" id="file" name="file">
<button type="submit">
上传
</button>
</form>
<a href="/export">导出excel</a>
</body>
<script type="text/javascript">
</script>
</html>
|
需要注意的是我们上传文件中的form标签必须要有enctype="multipart/form-data"属性，这个作用是设置请求的content-type;并且请求方式为post否则后端无法正常接收
Controller代码
1
2
3
4
5
6
7
8
| @Autowired
private ExcelService excelService;
@RequestMapping(value = "/excel",method = RequestMethod.POST)
public Map<String, Object> getexcel(@RequestParam("file") MultipartFile file){
return excelService.readExcelByHutool(file);
}
|
下面被注释掉的代码是另外一种读取excel文件的方式，两种都可以看看，上面的是使用Hutool工具类的方式
service代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
| @Service
public class ExcelService {
public Map<String, Object> importProfession(MultipartFile file) {
Map<String,Object> map = new HashMap<>();
String fileName = file.getOriginalFilename();
System.out.println(fileName);
String pattern = fileName.substring(fileName.lastIndexOf(".")+1);
System.out.println(pattern);
List<List<String>> listContent = new ArrayList<>();
String message="导入成功";
try {
if (file != null) {
if (!OtherExcelUtil.isEXCEL(file)) {
message = "该文件不是Excel文件";
} else {
listContent = OtherExcelUtil.readExcelContents(file,pattern);
if(listContent.isEmpty()){
message="表格内容为空";
}else {
for (int i = 0; i<listContent.size(); i++){
String theme = listContent.get(i).get(0);
String predicate = listContent.get(i).get(1);
String properties = listContent.get(i).get(2);
System.out.println(theme);
System.out.println(predicate);
System.out.println(properties);
}
}
}
}else {
message = "未选择文件";
}
}catch (Exception e){
e.printStackTrace();
}
map.put("code",200);
map.put("msg",message);
map.put("data",fileName);
return map;
}
public Map<String, Object> readExcelByHutool(MultipartFile file) {
InputStream inputStream=null;
try{
inputStream = file.getInputStream();
}catch (Exception e){
e.printStackTrace();
}
ExcelReader excelReader = ExcelUtil.getReader(inputStream);
List<List<Object>> read = excelReader.read(1, excelReader.getRowCount());
ArrayList<Student> studentArrayList = new ArrayList<>();
int count=read.size();
for (int i = 0; i <count ; i++) {
Student student = new Student();
student.setName((String) read.get(i).get(0));
Object o =read.get(i).get(1);
student.setAge(Integer.valueOf(String.valueOf(o)));
student.setEmail((String) read.get(i).get(2));
studentArrayList.add(student);
}
System.out.println(studentArrayList);
Map<String,Object> map = new HashMap<>();
map.put("code",200);
map.put("msg","导入成功");
map.put("data",file.getOriginalFilename());
return map;
}
}
|
需要注意的是，如果你使用的excelService.importProfession(file);这种方式需要另外添加一个工具类（下方的Util工具类）
Util工具类
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
| public class OtherExcelUtil {
public static Workbook readExcel(MultipartFile file, String pattern) {
Workbook workbook = null;
if (file != null) {
try {
InputStream is = file.getInputStream();
if ("xls".equals(pattern)) {
return workbook = new HSSFWorkbook(is);
} else if ("xlsx".equals(pattern)) {
return workbook = new XSSFWorkbook(is);
} else
return null;
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
return workbook;
}
public static Boolean isEXCEL(MultipartFile file) {
if (file != null) {
String fileName = file.getOriginalFilename();
String suffix = fileName.substring(fileName.lastIndexOf(".") + 1);
suffix = suffix.toLowerCase();
if ("xls".equals(suffix) || "xlsx".equals(suffix)) {
return true;
}
}
return false;
}
public static List<List<String>> readExcelContents(MultipartFile file, String pattern) {
List<List<String>> listRow = new ArrayList<>();
Workbook workbook = null;
Sheet sheet = null;
if (isEXCEL(file)) {
workbook = readExcel(file, pattern);
}
if (workbook != null) {
sheet = workbook.getSheetAt(0);
int rowNum = sheet.getPhysicalNumberOfRows();
Row row = null;
String cellData = null;
for (int i = 1; i < rowNum; i++) {
row = sheet.getRow(i);
List<String> listCell = new ArrayList<>();
if (StringUtils.isBlank(row.getCell(0).toString())) {
break;
}
for (int j = 0; j < 3; j++) {
cellData = row.getCell(j).toString();
listCell.add(cellData);
}
listRow.add(listCell);
}
}
return listRow;
}
}
|
这个工具类是使用第二种方式所必须的，如果使用第一种方式我们有我们引入的hutool依赖就足够了
model实体类
我们习惯把每一行的数据封装成一个实体类，这样读取数据之后方便赋值观察上面代码可以看到我使用的例子为student类，以后使用的话要看你需要什么类
1
2
3
4
5
6
7
8
| @Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
private String name;
private Integer age;
private String email;
}
|
演示
效果演示，这里我就使用第一种方式（当然结果都是一样的）
点击上传可以看到控制台输出了结果[Student(name=皮卡丘, age=4, email=719603766@qq.com), Student(name=妙蛙种子, age=3, email=123445@qq.com)]
Excel文件导出
代码不多，我们这里直接就写在controller中即可
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
| @RequestMapping(value = "/export",method = RequestMethod.GET)
@ResponseBody
public void export(HttpServletResponse response){
List<Student> list = new ArrayList<>();
list.add(new Student("张三",14,"719603766@qq.com"));
list.add(new Student("张四",14,"719603766@qq.com"));
list.add(new Student("张五",15,"719603766@qq.com"));
list.add(new Student("张六",16,"719603766@qq.com"));
list.add(new Student("张七",17,"719603766@qq.com"));
ExcelWriter writer = ExcelUtil.getWriter();
writer.addHeaderAlias("name", "姓名");
writer.addHeaderAlias("age", "年龄");
writer.addHeaderAlias("email", "邮件");
writer.merge(2, "学生信息单");
writer.write(list, true);
response.setContentType("application/vnd.ms-excel;charset=utf-8");
SimpleDateFormat format = new SimpleDateFormat("YYYY_mm_dd");
String name ="student_"+format.format(new Date());
response.setHeader("Content-Disposition","attachment;filename="+name+".xls");
ServletOutputStream out= null;
try {
out = response.getOutputStream();
writer.flush(out, true);
} catch (IOException e) {
e.printStackTrace();
}finally {
writer.close();
}
IoUtil.close(out);
}
|
这里我们模拟了数据，实际开发一般会在数据库中获取；注释非常详细了，ok大功告成以后可以直接拿来用了(^▽^)
