项目整体描述
页面图简介：
首页是一个index.html页面，长这样
功能概括说明
- 方法1：读取指定的Excel
- 方法2：读取上传的Excel里面的内容
- 方法3：java单文件导入Excel,保存到target的目录下
- 方法4：java多文件导入Excel,保存到target的目录下
- 方法5：java导出Excel
具体功能举例代码介绍
首页index.html
<!DOCTYPE html SYSTEM "http://www.thymeleaf.org/dtd/xhtml1-strict-thymeleaf-4.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"
xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>文件上传</title>
</head>
<body>
<p>单文件上传并保存</p>
<form method="post" action="/excel/uploadExcel" enctype="multipart/form-data">
<p><input type="file" name="file00"></p>
<p><span th:if="${msg}"></span></p>
<input type="submit" value="提交">
</form>
<hr/>
<p>多文件上传并保存</p>
<form method="post" enctype="multipart/form-data" action="/excel/uploadBatchExcel">
<p>文件1：<input type="file" name="file"/></p>
<p>文件2：<input type="file" name="file"/></p>
<p><input type="submit" value="上传"/></p>
</form>
<hr/>
<p>excel单文件上传并输出读取内容</p>
<form method="post" action="/excel/readUploadExcel" enctype="multipart/form-data">
<p><input type="file" name="fileExcel"></p>
<p><span th:if="${msg}"></span></p>
<input type="submit" value="提交">
</form>
</body>
</html>
操作成功跳转hello.html
<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body>
<p>单文件上传</p>
<p th:text="${msg}"></p>
<hr>
<p>多文件上传</p>
<ul>
<li th:each="msg1:${msgList}" th:text="${msg1}"></li>
</ul>
</body>
</html>
首页跳转index.html代码
import com.alibaba.excel.EasyExcel;
import com.example.demo.bean.Teacher;
import com.example.demo.bean.TeacherListener;
import com.example.demo.setvice.ImportService;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.util.ResourceUtils;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.multipart.MultipartRequest;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.UUID;
/**
* 操作Excel
* @Author 211145187
* @Date 2022/2/22 19:43
**/
@RequestMapping("/excel")
@Controller
public class OperateExcelController {
@Autowired
private ImportService importService;
//首页
@GetMapping(value = "")
public String index() {
//使用@RestController不能直接返回return "index"，否则不会跳转页面，只会再页面显示index文本而已
return "index";
}
方法1：读取指定的Excel
说明：需要用到阿里的第三方库easyexcel进行读取
excel长这样
pom
<!--读取excel文件-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
controller
//方法1：读取指定的Excel
@RequestMapping(value = "/readExcel")
@ResponseBody
public List<Teacher> readExcel() {
String fileName = "C:\\Users\\211145187\\Desktop\\fsdownload\\details (1).xls";
TeacherListener teacherListener = new TeacherListener();
EasyExcel.read(fileName, Teacher.class, teacherListener).sheet().doRead();
return teacherListener.getTeacherList();
}
Teacher
@Data
public class Teacher {
//姓名
private String name;
//班级
private String classes;
//所属学院
private String college;
//别名
private String alias;
}
TeacherListener
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import lombok.Getter;
import java.util.ArrayList;
import java.util.List;
/**
* 教师读取类
* @Author 211145187
* @Date 2022/2/23 16:50
**/
public class TeacherListener extends AnalysisEventListener<Teacher> {
@Getter
private List<Teacher> teacherList = new ArrayList<>();
public TeacherListener() {
super();
teacherList.clear();
}
/**
* 每一条数据解析都会调用
*/
@Override
public void invoke(Teacher teacher, AnalysisContext context) {
teacherList.add(teacher);
}
/**
* 所有数据解析完成都会调用
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
teacherList.forEach(System.out::println);
}
}
最终展示结果
方法2：读取上传的Excel里面的内容
pom
<!--文件上传-->
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.3</version>
</dependency>
controller
//方法2：读取上传的Excel里面的内容 @RequestMapping(value = "/readUploadExcel") @ResponseBody public String readUploadExcel(HttpServletRequest request) throws Exception { MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request; MultipartFile file = multipartRequest.getFile("fileExcel"); if (file.isEmpty()) { return "文件不能为空"; } InputStream inputStream = file.getInputStream(); List<List<Object>> list = importService.getBankListByExcel(inputStream, file.getOriginalFilename()); inputStream.close(); list.forEach(System.out::println); return "上传成功并输出内容"; }service
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.stereotype.Service;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
/**
* @Author 211145187
* @Date 2022/2/24 09:33
**/
@Service
public class ImportService {
/**
* 处理上传的文件
*
* @param in
* @param fileName
* @return
* @throws Exception
*/
public List getBankListByExcel(InputStream in, String fileName) throws Exception {
List list = new ArrayList<>();
//创建Excel工作薄
Workbook work = this.getWorkbook(in, fileName);
if (null == work) {
throw new Exception("创建Excel工作薄为空！");
}
Sheet sheet = null;
Row row = null;
Cell cell = null;
for (int i = 0; i < work.getNumberOfSheets(); i++) {
sheet = work.getSheetAt(i);
if (sheet == null) {
continue;
}
for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
row = sheet.getRow(j);
if (row == null || row.getFirstCellNum() == j) {
continue;
}
List<Object> li = new ArrayList<>();
for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
cell = row.getCell(y);
li.add(cell);
}
list.add(li);
}
}
work.close();
return list;
}
/**
* 判断文件格式
*
* @param inStr
* @param fileName
* @return
* @throws Exception
*/
public Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
Workbook workbook = null;
String fileType = fileName.substring(fileName.lastIndexOf("."));
if (".xls".equals(fileType)) {
workbook = new HSSFWorkbook(inStr);
} else if (".xlsx".equals(fileType)) {
workbook = new XSSFWorkbook(inStr);
} else {
throw new Exception("请上传excel文件！");
}
return workbook;
}
}
最终展示结果
方法3：java单文件导入Excel,保存到target的目录下
controller
//方法3：java单文件导入Excel,保存到target的目录下
@PostMapping(value = "/uploadExcel")
public String uploadExcel(@RequestParam("file00") MultipartFile file, Model model) throws IOException {
try {
if(file.isEmpty()){
model.addAttribute("msg","上传失败，请选择文件！");
return "index";
}
String filename = file.getOriginalFilename();
//filePath获取的是编译后的路径，而不是项目看到的路径，filePath=/E:/WorkSpace/demo/target/classes/
String filePath = ResourceUtils.getURL("classpath:").getPath()+"static/oneFile/";
//避免文件重复覆盖
String uuid= UUID.randomUUID().toString().replaceAll("-", "");
//时间戳分类文件
String time = new SimpleDateFormat("YYYY-MM").format(new Date());
String realPath = filePath + time + "/" + uuid + "-" + filename;
System.out.println("realPath:" + realPath);
//最后保存的路径在这里：target/classes/static/oneFile/2022-02/548881060e3d417a91d87b0a10959077-sop.sql
File dest = new File(realPath);
//检测是否存在目录，无，则创建
if(!dest.getParentFile().exists()){
dest.getParentFile().mkdirs();//新建文件夹 多级目录
}
file.transferTo(dest);//文件写入
} catch (IOException e) {
e.printStackTrace();
}
model.addAttribute("msg","文件上传成功！");
return "hello";
}
最终展示结果
方法4：java多文件导入Excel,保存到target的目录下
controller
//方法4：java多文件导入Excel,保存到target的目录下
@PostMapping(value = "/uploadBatchExcel")
public String uploadBatchExcel(HttpServletRequest request, Model model) throws IOException {
MultipartRequest request1 = (MultipartRequest)request;
//猜测 file为 input 类型为 file
List<MultipartFile> fileList = request1.getFiles("file");
List<String> msgList = new ArrayList<>();
try {
String filePath = ResourceUtils.getURL("classpath:").getPath()+"static/multiFile/";
for (int i = 1; i <= fileList.size(); i++){
MultipartFile file = fileList.get(i - 1);
if (file.isEmpty()){
msgList.add("上传第"+i+"个文件失败");
model.addAttribute("msgList",msgList);
continue;
}
String filename = file.getOriginalFilename();
//避免文件重复覆盖
String uuid= UUID.randomUUID().toString().replaceAll("-", "");
//时间戳分类文件
String time = new SimpleDateFormat("YYYY-MM").format(new Date());
String realPath = filePath + time + "/" + uuid + "-" + filename;
System.out.println("realPath:" + realPath);
File dest = new File(realPath);
//System.out.println("realPath"+realPath);
//检测是否存在目录，无，则创建
if(!dest.getParentFile().exists()){
dest.getParentFile().mkdirs();//新建文件夹 多级目录
}
msgList.add("第"+i+"个文件，上传成功!");
file.transferTo(dest);
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
model.addAttribute("msgList",msgList);
return "hello";
}
最终展示结果
方法5：java导出Excel
pom
<!--操作Excel-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
controller
//方法5：java导出Excel
@RequestMapping("/exportExcel")
public void createExcel(HttpServletResponse response) throws IOException {
//创建HSSFWorkbook对象(excel的文档对象)
HSSFWorkbook wb = new HSSFWorkbook();
buildScoreSheet(wb);
buildTeacherSheet(wb);
//.....省略部分代码
//输出Excel文件
OutputStream output=response.getOutputStream();
response.reset();
response.setHeader("Content-disposition", "attachment; filename=details.xls");
response.setContentType("application/octet-stream;charset=iso8859-1");
wb.write(output);
output.close();
}
/**
* 设置样式
* @Author 211145187
* @Date 2022/2/22 20:15
* @Param wb wb
* @Return CellStyle
**/
private CellStyle getHeadStyle(Workbook wb) {
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
return cellStyle;
}
/**
* 构建参数
* @Author 211145187
* @Date 2022/2/22 20:20
* @Param wb wb
**/
private void buildScoreSheet(HSSFWorkbook wb) {
//建立新的sheet对象（excel的表单）
HSSFSheet sheet=wb.createSheet("成绩表");
//在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
HSSFRow row1=sheet.createRow(0);
//创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
HSSFCell cell=row1.createCell(0);
//设置单元格内容
cell.setCellValue("学员考试成绩一览表");
cell.setCellStyle(getHeadStyle(wb));
//合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
sheet.addMergedRegion(new CellRangeAddress(0,0,0,3));
//在sheet里创建第二行
HSSFRow row2=sheet.createRow(1);
//创建单元格并设置单元格内容
row2.createCell(0).setCellValue("姓名");
row2.createCell(1).setCellValue("班级");
row2.createCell(2).setCellValue("笔试成绩");
row2.createCell(3).setCellValue("机试成绩");
//在sheet里创建第三行
HSSFRow row3=sheet.createRow(2);
row3.createCell(0).setCellValue("李明");
row3.createCell(1).setCellValue("As178");
row3.createCell(2).setCellValue(87);
row3.createCell(3).setCellValue(78);
}
/**
* 构建参数
* @Author 211145187
* @Date 2022/2/22 20:20
* @Param wb wb
**/
private void buildTeacherSheet(HSSFWorkbook wb) {
String[] titleData = new String[4];
titleData[0] = "姓名";
titleData[1] = "班级";
titleData[2] = "所属学院";
titleData[3] = "别名";
List<Teacher> teacherList = new ArrayList<>();
Teacher teacher1 = new Teacher();
teacher1.setName("周杰伦");
teacher1.setClasses("三年二班");
teacher1.setCollege("魔法学院");
teacher1.setAlias("Jay Chou");
teacherList.add(teacher1);
Teacher teacher2 = new Teacher();
teacher2.setName("陈奕迅");
teacher2.setClasses("三年二班");
teacher2.setCollege("魔法学院");
teacher2.setAlias("Eason");
teacherList.add(teacher2);
//建立新的sheet对象（excel的表单）
HSSFSheet sheet=wb.createSheet("教师表");
//在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
HSSFRow row1=sheet.createRow(0);
//创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
HSSFCell cell=row1.createCell(0);
//设置单元格内容
cell.setCellValue("教师一览表");
cell.setCellStyle(getHeadStyle(wb));
//合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
sheet.addMergedRegion(new CellRangeAddress(0,0,0,3));
//在sheet里创建第二行
HSSFRow row2=sheet.createRow(1);
//创建单元格并设置单元格内容
for (int i = 0; i < titleData.length; i++) {
HSSFCell cell2 = row2.createCell(i);
cell2.setCellValue(titleData[i]);
cell2.setCellStyle(getHeadStyle(wb));
}
//在sheet里创建第三行
for (int j = 0; j < teacherList.size(); j++) {
Teacher teacher = teacherList.get(j);
HSSFRow row3 = sheet.createRow(j + 2);
for (int k = 0; k < titleData.length; k++) {
HSSFCell cell3 = row3.createCell(k);
if (k == 0) {
cell3.setCellValue(teacher.getName());
} else if (k == 1) {
cell3.setCellValue(teacher.getClasses());
} else if (k == 2) {
cell3.setCellValue(teacher.getCollege());
} else {
cell3.setCellValue(teacher.getAlias());
}
}
}
}
最终展示效果包含2张表
可能出错场景说明
场景1：springboot配合thymeleaf，调用接口不跳转页面只显示文本
springboot配合thymeleaf，调用接口不跳转页面只显示文本
场景2：org.springframework.web.multipart.MultipartException: Current request is not a multipart request
org.springframework.web.multipart.MultipartException: Current request is not a multipart request
场景3：Error resolving template [excel/readExcel], template might not exist or might not be accessible by
Error resolving template [excel/readExcel], template might not exist or might not be accessible by
场景4：springboot实现上传，前端必须指定enctype="multipart/form-data"，后端请求方式必须是post，且方法中添加注解或类型转换
前端
<hr/>
<p>多文件上传并保存</p>
<form method="post" enctype="multipart/form-data" action="/excel/uploadBatchExcel">
<p>文件1：<input type="file" name="file"/></p>
<p>文件2：<input type="file" name="file"/></p>
<p><input type="submit" value="上传"/></p>
</form>
后端
要么使用注解标注@RequestParam("file00") MultipartFile file
public String uploadExcel(@RequestParam("file00") MultipartFile file, Model model)
或者
要么request类型转换MultipartRequest request1 = (MultipartRequest)request
public String uploadBatchExcel(HttpServletRequest request, Model model) throws IOException {
MultipartRequest request1 = (MultipartRequest)request;
