java -springboot 操作 excel文档 easyexcel库 (上传，下载，导出)
2、java操作 excel
https://blog.csdn.net/lzp492782442/article/details/106860383/
The supplied data appears to be in the Office 2007+ XML
https://blog.csdn.net/you23hai45/article/details/70196897
关键问题的原因还是excel2003和excel2007版本的问题
3、解决办法
（1）判断文件后缀名是xls，还是xlsx
（2）如果是xls，使用HSSFWorkbook；如果是xlsx，使用XSSFWorkbook
项目集成
使用idea开发工具简单创建了一个easyexcel-demo项目，加入了web模块以及easyexcel maven依赖，依赖如下：
<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.4</version>
</dependency>
创建模版 放到 excelTemplate目录下
定义模型映射对象 UserExcelModel
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;
@NoArgsConstructor
@AllArgsConstructor
@Data
public class UserExcelModel extends BaseRowModel implements Serializable {
@ExcelProperty(value = "用户名", index = 0)
private String name;
@ExcelProperty(value = "年龄", index = 1)
private Integer age;
@ExcelProperty(value = "手机号", index = 2)
private String mobile;
@ExcelProperty(value = "性别", index = 3)
private String sex;
}
监听类
package com.control;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import lombok.extern.slf4j.Slf4j;
import java.util.ArrayList;
import java.util.List;
@Slf4j
public class ModelExcelListener extends AnalysisEventListener {
private List<Object> datas = new ArrayList<>();
/**
* 通过 AnalysisContext 对象还可以获取当前 sheet，当前行等数据
*/
@Override
public void invoke(Object data, AnalysisContext context) {
//数据存储到list，供批量处理，或后续自己业务逻辑处理。
log.info("读取到数据{}",data);
datas.add(data);
//根据业务自行处理，可以写入数据库等等
}
//所以的数据解析完了调用
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
log.info("所有数据解析完成");
}
}
控制层
其中 HttpServletResponse response 如果这个导出excel的方法封装成方法被调用，那么control的方法也要传HttpServletResponse response过来------踩坑！！！！
/**
* 下载模板
*/
@GetMapping("/downloadTemplate")
public void downloadTemplate(HttpServletResponse response) throws Exception {
ClassPathResource classPathResource = new ClassPathResource("excelTemplate/easyexcel.xlsx");
InputStream inputStream = classPathResource.getInputStream();
Workbook workbook = new XSSFWorkbook(inputStream);
response.setContentType("application/vnd.ms-excel");
response.setHeader("content-Disposition", "attachment;filename=" + URLEncoder.encode("easyexcel2222.xlsx", "utf-8"));
response.setHeader("Access-Control-Expose-Headers", "content-Disposition");
OutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
}
/**
* 导出数据
*/
@GetMapping("/exportData")
public void exportData(HttpServletResponse response) throws Exception {
XSSFWorkbook workbook = new XSSFWorkbook();
// 设置列名
String[] columnNames = {"用户名", "年龄", "手机号", "性别"};
// 设置tab
Sheet sheet1 = workbook.createSheet("第一个tab");
Sheet sheet = workbook.createSheet("第二个tab");
//设置字体样式
Font titleFont = workbook.createFont();
titleFont.setFontName("simsun");
titleFont.setBold(true);
titleFont.setColor(IndexedColors.BLACK.index);
XSSFCellStyle titleStyle = workbook.createCellStyle();
titleStyle.setAlignment(HorizontalAlignment.CENTER);
titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
titleStyle.setFillForegroundColor(IndexedColors.YELLOW.index);
titleStyle.setFont(titleFont);
// 操作第x行开始
Row titleRow = sheet.createRow(0);
for (int i = 0; i < columnNames.length; i++) {
// 写入几行第几列
Cell cell = titleRow.createCell(i);
// 写入的内容
cell.setCellValue(columnNames[i]);
// 写入的格式
cell.setCellStyle(titleStyle);
}
//模拟构造数据
List<UserExcelModel> dataList = new ArrayList<>();
dataList.add(new UserExcelModel("张三", 12, "13867098765", "男"));
dataList.add(new UserExcelModel("张三1", 12, "13867098765", "男"));
dataList.add(new UserExcelModel("张三2", 12, "13867098765", "男"));
dataList.add(new UserExcelModel("张三3", 12, "13867098765", "男"));
//创建数据行并写入值
for (int j = 0; j < dataList.size(); j++) {
UserExcelModel userExcelModel = dataList.get(j);
// 获取tab表里面有多少行数据
int lastRowNum = sheet.getLastRowNum();
// 指定写入的行
Row dataRow = sheet.createRow(lastRowNum + 1);
// 指定第写入第几列
dataRow.createCell(0).setCellValue(userExcelModel.getName());
dataRow.createCell(1).setCellValue(userExcelModel.getAge());
dataRow.createCell(2).setCellValue(userExcelModel.getMobile());
dataRow.createCell(3).setCellValue(userExcelModel.getSex());
}
response.setContentType("application/vnd.ms-excel");
response.setHeader("content-Disposition", "attachment;filename=" + URLEncoder.encode("easyexcel4444.xls", "utf-8"));
response.setHeader("Access-Control-Expose-Headers", "content-Disposition");
OutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
}
// 上传
@PostMapping("/readExcel")
public List<UserExcelModel> readExcel(@RequestParam("file") MultipartFile file){
List<UserExcelModel> list = new ArrayList<>();
try {
list = EasyExcel.read(file.getInputStream(),UserExcelModel.class,new ModelExcelListener()).sheet().doReadSync();
} catch (IOException e) {
e.printStackTrace();
}
return list;
} /**
* 下载模板
*/
@GetMapping("/downloadTemplate")
public void downloadTemplate(HttpServletResponse response) throws Exception {
ClassPathResource classPathResource = new ClassPathResource("excelTemplate/easyexcel.xlsx");
InputStream inputStream = classPathResource.getInputStream();
Workbook workbook = new XSSFWorkbook(inputStream);
response.setContentType("application/vnd.ms-excel");
response.setHeader("content-Disposition", "attachment;filename=" + URLEncoder.encode("easyexcel2222.xlsx", "utf-8"));
response.setHeader("Access-Control-Expose-Headers", "content-Disposition");
OutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
}
/**
* 导出数据
*/
@GetMapping("/exportData")
public void exportData(HttpServletResponse response) throws Exception {
XSSFWorkbook workbook = new XSSFWorkbook();
// 设置列名
String[] columnNames = {"用户名", "年龄", "手机号", "性别"};
// 设置tab
Sheet sheet1 = workbook.createSheet("第一个tab");
Sheet sheet = workbook.createSheet("第二个tab");
//设置字体样式
Font titleFont = workbook.createFont();
titleFont.setFontName("simsun");
titleFont.setBold(true);
titleFont.setColor(IndexedColors.BLACK.index);
XSSFCellStyle titleStyle = workbook.createCellStyle();
titleStyle.setAlignment(HorizontalAlignment.CENTER);
titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
titleStyle.setFillForegroundColor(IndexedColors.YELLOW.index);
titleStyle.setFont(titleFont);
// 操作第x行开始
Row titleRow = sheet.createRow(0);
for (int i = 0; i < columnNames.length; i++) {
// 写入几行第几列
Cell cell = titleRow.createCell(i);
// 写入的内容
cell.setCellValue(columnNames[i]);
// 写入的格式
cell.setCellStyle(titleStyle);
}
//模拟构造数据
List<UserExcelModel> dataList = new ArrayList<>();
dataList.add(new UserExcelModel("张三", 12, "13867098765", "男"));
dataList.add(new UserExcelModel("张三1", 12, "13867098765", "男"));
dataList.add(new UserExcelModel("张三2", 12, "13867098765", "男"));
dataList.add(new UserExcelModel("张三3", 12, "13867098765", "男"));
//创建数据行并写入值
for (int j = 0; j < dataList.size(); j++) {
UserExcelModel userExcelModel = dataList.get(j);
// 获取tab表里面有多少行数据
int lastRowNum = sheet.getLastRowNum();
// 指定写入的行
Row dataRow = sheet.createRow(lastRowNum + 1);
// 指定第写入第几列
dataRow.createCell(0).setCellValue(userExcelModel.getName());
dataRow.createCell(1).setCellValue(userExcelModel.getAge());
dataRow.createCell(2).setCellValue(userExcelModel.getMobile());
dataRow.createCell(3).setCellValue(userExcelModel.getSex());
}
response.setContentType("application/vnd.ms-excel");
response.setHeader("content-Disposition", "attachment;filename=" + URLEncoder.encode("easyexcel4444.xls", "utf-8"));
response.setHeader("Access-Control-Expose-Headers", "content-Disposition");
OutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
}
// 上传
@PostMapping("/readExcel")
public List<UserExcelModel> readExcel(@RequestParam("file") MultipartFile file){
List<UserExcelModel> list = new ArrayList<>();
try {
list = EasyExcel.read(file.getInputStream(),UserExcelModel.class,new ModelExcelListener()).sheet().doReadSync();
} catch (IOException e) {
e.printStackTrace();
}
return list;
}
本地操作记录
ModelExcelListener.java （做了一下修改，让他展示成json）
package com.control;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSONArray;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import java.util.ArrayList;
import java.util.List;
@Slf4j
@Data
public class ModelExcelListener extends AnalysisEventListener {
private List<Object> datas = new ArrayList<>();
/**
* 通过 AnalysisContext 对象还可以获取当前 sheet，当前行等数据
*/
@Override
public void invoke(Object data, AnalysisContext context) {
//数据存储到list，供批量处理，或后续自己业务逻辑处理。
// log.info("读取到数据{}",data);
String data2= JSONArray.toJSONString(data);
datas.add(data2);
log.info("读取到数据1111{}",datas);
//根据业务自行处理，可以写入数据库等等
}
//所以的数据解析完了调用
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
log.info("所有数据解析完成");
}
}
其他的和上面的一致
控制层
读取本地的数据
// 数据读取 @GetMapping("/excel") public void excel(){ String fileName ="/Users/lucax/Desktop/工作/小工具脚本/B端测试平台/代码存档/easyexcel.xlsx"; // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭 ModelExcelListener ModelExcelListener=new ModelExcelListener(); EasyExcel.read(fileName, UserExcelModel.class, ModelExcelListener).sheet(0).doRead(); // 这里就拿到excel的数据了 List aa=ModelExcelListener.getDatas(); log.info(String.valueOf(aa)); }
官网操作 https://www.yuque.com/easyexcel/doc/easyexcel
前端vue下载记录: https://www.cnblogs.com/kaibindirver/p/15797386.html
有空试试，可能在后端加上 responseType: 'arraybuffer' 的话前端估计久不用加，也能导出
浙公网安备 33010602011771号