1.下载模板（无导出数据）
// An highlighted block
public void downloadExcel(HttpServletRequest request, HttpServletResponse response) throws IOException {
try {
// 使用流的方式读入
InputStream inputStream = null;
// 路径：包和模板文件，放在resource文件夹下 /resource/template/excelFile.xlsx
final String path = "/template/excelFile.xlsx";
// 通过类路径创建resource，并获得流
ClassPathResource resource = new ClassPathResource(path);
inputStream = resource.getInputStream();
String fileName = "表格模板"; //下载时展示的名字，中文名会出现乱码，需要处理
String fileName2 = URLEncoder.encode(fileName, "utf-8");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName2 + ".xlsx");
response.setContentType("application/msexcel"); // 定义输出类型
response.setCharacterEncoding("UTF-8"); // 指定对服务器响应进行重新编码的编码
// 设置 Excel在“xxx.xlsx”中发现不可读取的内容。是否恢复此工作薄的内容？如果信任此工作簿的来源，请点击"是"
response.setHeader("Content-Length", String.valueOf(inputStream.available()));
// 用于输出字符流数据或者二进制的字节流数据
OutputStream outputStream = response.getOutputStream();
//循环写入输出流
byte[] b = new byte[2048];
int len;
// 读入缓冲区的总字节数，如果由于已到达流末尾而不再有数据，则返回 -1
while ((len = inputStream.read(b)) != -1) {
outputStream.write(b, 0, len);
}
// 关闭流
outputStream.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
if(null!=inputStream){
inputStream.close();
}
}
}
2.导入数据（读数据）
<!-- 导入表格工具包 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
// An highlighted block
public void importExcel(MultipartFile file) {
try {
InputStream inputStream = null;
inputStream = file.getInputStream();
ExcelReader reader = ExcelUtil.getReader(inputStream, "sheet1"); // hutool包的类
List<List<Object>> readAll = reader.read(); // 获得excel中所有的数据
//将数据插入数据库操作
....
} catch (Exception e) {
e.printStackTrace();
}
}
3.导出数据（EasyExcel）
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.7</version>
</dependency>
模板填充方式
// An highlighted block
public void exportExcel(HttpServletRequest request, HttpServletResponse response) {
try (
InputStream inputStream = new ClassPathResource("/template/excelFile.xlsx").getInputStream();
OutputStream outputStream = response.getOutputStream();
) {
List<xxxVO> voList = xxxService.getVoList();
// 读模板流
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode("导出结果", "UTF-8"));
ExcelWriter write = EasyExcel.write(outputStream).withTemplate(inputStream).build();
WriteSheet writeSheet = EasyExcel.writeSheet().build();
// ## 填充集合，模板下第二行需要填入{./返回的参数字段名}，eg：{./name}；单个对象填充 {name}
write.fill(voList, writeSheet);
// 写集合，模板下第二行不需要填入{./返回的参数字段名}，会自动根据注解标题名识别。
// write.write(voList, writeSheet);
write.finish();
outputStream.finish();
} catch (Exception e) {
e.printStackTrace();
}
}
4.导入数据，并返回导入失败的数据表格（EasyExcel）
将导入失败的记录生成文件，临时存储在磁盘。并将文件名返回给前端，以便后续下载
（1）创建返回对象
public class ReturnVO {
//是否存在导入失败条数
private boolean isFail;
//总条数
private Integer totalSize;
//失败条数
private Integer failSize;
//失败记录文件名
private String failFilename;
}
（2）失败文件导出实体类
@Data
public class ExcelExportResultPO {
@ExcelProperty("名称*")
private String name;
@ExcelProperty("性别*")
private String gender;
@ExcelProperty("手机号*")
private String phoneNum;
@ExcelIgnore
private Integer userId;
@ExcelProperty("导入结果")
private String result;
@ExcelIgnore
private Boolean isError;
}
（3）自定义监听器
public class SchoolDistrictDataListenner extends AnalysisEventListener<ExcelExportResultPO> {
// dao层
private CustomerService customerService;
// 用户id入参
private Integer userId;
// 构造函数
public void setCustomerService (CustomerService customerService) {
this.customerService= customerService;
}
public void setUserId(Integer userId) {
this.userId= userId;
}
// 保存需要插入的数据
List<ExcelExportResultPO> dataList = new ArrayList<ExcelExportResultPO>();
/**
* 这个每一条数据解析都会来调用
*
* @param data
* one row value. Is is same as {@link AnalysisContext#readRowHolder()}
* @param context
*/
@Override
public void invoke(ExcelExportResultPO data, AnalysisContext context) {
//记录错误数量
List<String> errorTips = new ArrayList<>(15);
boolean isError = false;
try {
// 学校名称：不超过30、必填
if (StringUtils.isBlank(data.getName())) {
errorTips.add("名称是必填项");
} else {
// 去空格
String name= data.getSchoolName().trim();
data.setName(name);
}
// 判断这条记录是否符合要求
if (errorTips.size() != 0) {
isError = true;
}
if (!isError) {
// 验证通过，加入成功集合中。可在此加入对象参数
data.setUserId(userId);
dataList.add(data);
}
} catch (Exception e) {
isError = true;
e.printStackTrace();
errorTips.add("解析失败");
} finally {
data.setIsError(isError);
//写入错误结果到excel中
String errorStr = errorTips.stream().collect(Collectors.joining(";"));
log.info("==========> excel信息为:{}", data.toString());
log.info("==========> 错误结果为:{}", errorStr);
data.setResult(errorStr);
}
}
/**
* 所有数据解析完成了 都会来调用
*
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
saveData();
log.info("所有数据解析完成！");
}
/**
* 加上存储数据库
*/
private void saveData() {
log.info("{}条数据，开始存储数据库！", dataList.size());
if (dataList.size() > 0) {
customerService.saveBatch(dataList);
}
log.info("存储数据库成功！");
}
}
（4）导入接口
public RestResponse<ReturnVO > exportExcel(@RequestParam MultipartFile file, HttpServletRequest request, HttpServletResponse response) {
try {
Integer userId = 0;
// 获得模板输入流
InputStream templateInputStream = new ClassPathResource("excel.template/school_district_import_template.xlsx").getInputStream();
InputStream inputStream = null;
inputStream = file.getInputStream();
// 构造自定义监听器，构造时可传入mapper、service以及参数
CustomerDataListenner customerDataListenner = new CustomerDataListenner ();
customerDataListenner .setCustomerMapper(customerMapper);
customerDataListenner .setUserId(userId);
// 延迟解析比率：处理传入文件较大时会报错
ZipSecureFile.setMinInflateRatio(-1.0d);
// 获得解析出的符合条件的数据，进行插入
List<ExcelExportResultPO> data = EasyExcel.read(inputStream, ExcelExportResultPO.class, customerDataListenner ).sheet(0).doReadSync();
if (!CollectionUtils.isEmpty(data)) {
result.setTotalSize(data.size()); //保存总导入数
//筛选出导入失败的记录
List<ExcelExportResultPO> failData = data.stream().filter(ExcelExportResultPO::getIsError).collect(Collectors.toList());
if (!CollectionUtils.isEmpty(failData)) {
//创建新的文件，按模板写入
String failResultFileName = "failFile" + UUID.randomUUID().toString().replace("-", "") + ".xlsx";
ByteArrayOutputStream os = new ByteArrayOutputStream();
ExcelWriter excelWriter = EasyExcel.write(os)
.withTemplate(templateInputStream)
.build();
WriteSheet writeSheet = EasyExcel.writerSheet(0).build();
excelWriter.write(failData, writeSheet);
excelWriter.finish();
InputStream is = new ByteArrayInputStream(os.toByteArray());
// 磁盘临时存储
// String fileUrl = env.getProperty("file.localtion");
String fileUrl = "C:/xxx/xxx";
FileUtil.writeFromStream(is, fileUrl+failResultFileName);
// 存在导入失败的数据，则生成下载文件
result.setHasFail(true);
result.setFailSize(failData.size());
result.setFilename(failResultFileName);
} else {
result.setHasFail(false);
}
}
return RestResponse.success(result);
} catch (Exception e) {
result.setHasFail(true);
result.setTotalSize(0);
result.setFailSize(0);
e.printStackTrace();
return RestResponse.exception("导入失败");
}
}
（5）下载临时存储的失败文件
public void importResultDownload(@Valid @NotBlank String failFileName, HttpServletResponse response) throws IOException {
log.info("导入失败文件下载 >>>>>>>>>>>>> ");
String fileName = "导入失败结果.xlsx";
// String fileUrl = env.getProperty("file.localtion");
String fileUrl = "C:/xxx/xxx";
try (OutputStream out = response.getOutputStream();
InputStream in = FileUtil.getInputStream(FileUtil.file(fileUrl + fileId))) {
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
IOUtils.copy(in, out, 1024);
}catch (Exception e){
e.printStackTrace();
log.info("导入失败文件下载，失败 >>>>>>>>>>>>>{}",e.getMessage());
}
}
参考
MIME (Multipurpose Internet Mail Extensions) 是描述消息内容类型的因特网标准。
MIME 消息能包含文本、图像、音频、视频以及其他应用程序专用的数据。
扫一扫
9629
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
