package com.zt.modules.sample.controller;
|
|
import com.alibaba.excel.EasyExcel;
|
import com.alibaba.excel.ExcelReader;
|
import com.alibaba.excel.context.AnalysisContext;
|
import com.alibaba.excel.event.AnalysisEventListener;
|
import com.alibaba.excel.read.metadata.ReadSheet;
|
import com.zt.common.servlet.Result;
|
import com.zt.common.utils.ExcelUtils;
|
import com.zt.modules.sample.excel.MultipleHeaderExcel;
|
import com.zt.modules.sample.excel.SampleExcel;
|
import io.swagger.annotations.Api;
|
import io.swagger.annotations.ApiOperation;
|
import org.springframework.web.bind.annotation.GetMapping;
|
import org.springframework.web.bind.annotation.PostMapping;
|
import org.springframework.web.bind.annotation.RequestMapping;
|
import org.springframework.web.bind.annotation.RestController;
|
import org.springframework.web.multipart.MultipartFile;
|
|
import javax.servlet.http.HttpServletResponse;
|
import java.io.IOException;
|
import java.util.ArrayList;
|
import java.util.List;
|
|
/**
|
* 示例
|
*
|
* @author hehz
|
*/
|
@RestController
|
@RequestMapping("/sample")
|
@Api(value = "示例", tags = "示例")
|
public class SampleController {
|
|
/**
|
* 导入参考https://www.yuque.com/easyexcel/doc/read
|
*
|
* @param file
|
* @return
|
* @throws IOException
|
*/
|
@PostMapping("excel/import")
|
@ApiOperation("Excel导入")
|
public Result excelImport(MultipartFile file) throws IOException {
|
EasyExcel.read(file.getInputStream(), SampleExcel.class, new AnalysisEventListener<SampleExcel>() {
|
private List<SampleExcel> list = new ArrayList<>();
|
|
@Override
|
public void invoke(SampleExcel excel, AnalysisContext analysisContext) {
|
// 解析每一条
|
list.add(excel);
|
}
|
|
@Override
|
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
|
// 保存数据库
|
System.out.println(list.size());
|
}
|
}).sheet().doRead();
|
|
return Result.ok("这是测试成功返回结果");
|
}
|
|
@PostMapping("multiple/header/excel/import")
|
@ApiOperation("多表头Excel导入")
|
public Result excelImport2(MultipartFile file) throws IOException {
|
EasyExcel
|
.read(file.getInputStream(), MultipleHeaderExcel.class,
|
new AnalysisEventListener<MultipleHeaderExcel>() {
|
private List<MultipleHeaderExcel> list = new ArrayList<>();
|
|
@Override
|
public void invoke(MultipleHeaderExcel excel, AnalysisContext analysisContext) {
|
// 解析每一条
|
list.add(excel);
|
}
|
|
@Override
|
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
|
// 保存数据库
|
System.out.println(list.size());
|
}
|
}).sheet().headRowNumber(2).doRead();
|
|
return Result.ok();
|
}
|
|
@PostMapping("multiple/sheet/excel/import")
|
@ApiOperation("多sheet Excel导入")
|
public Result excelImport3(MultipartFile file) throws IOException {
|
ExcelReader excelReader = null;
|
try {
|
excelReader = EasyExcel.read(file.getInputStream()).build();
|
|
ReadSheet readSheet1 = EasyExcel.readSheet(0).head(SampleExcel.class)
|
.registerReadListener(new AnalysisEventListener<SampleExcel>() {
|
private List<SampleExcel> list = new ArrayList<>();
|
|
@Override
|
public void invoke(SampleExcel excel, AnalysisContext analysisContext) {
|
// 解析每一条
|
list.add(excel);
|
}
|
|
@Override
|
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
|
// 保存数据库
|
System.out.println(list.size());
|
}
|
}).build();
|
|
ReadSheet readSheet2 = EasyExcel.readSheet(1).headRowNumber(2).head(MultipleHeaderExcel.class)
|
.registerReadListener(new AnalysisEventListener<MultipleHeaderExcel>() {
|
private List<MultipleHeaderExcel> list = new ArrayList<>();
|
|
@Override
|
public void invoke(MultipleHeaderExcel excel, AnalysisContext analysisContext) {
|
// 解析每一条
|
list.add(excel);
|
}
|
|
@Override
|
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
|
// 保存数据库
|
System.out.println(list.size());
|
}
|
}).build();
|
// 这里注意 一定要把sheet1 sheet2 一起传进去，不然有个问题就是03版的excel 会读取多次，浪费性能
|
excelReader.read(readSheet1, readSheet2);
|
} finally {
|
if (excelReader != null) {
|
excelReader.finish();
|
}
|
}
|
|
EasyExcel
|
.read(file.getInputStream(), MultipleHeaderExcel.class,
|
new AnalysisEventListener<MultipleHeaderExcel>() {
|
private List<MultipleHeaderExcel> list = new ArrayList<>();
|
|
@Override
|
public void invoke(MultipleHeaderExcel excel, AnalysisContext analysisContext) {
|
// 解析每一条
|
list.add(excel);
|
}
|
|
@Override
|
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
|
// 保存数据库
|
System.out.println(list.size());
|
}
|
}).sheet().doRead();
|
|
return Result.ok();
|
}
|
|
/**
|
* 具体参考https://www.yuque.com/easyexcel/doc/write
|
*
|
* @param response
|
* @throws Exception
|
*/
|
@GetMapping("excel/export")
|
@ApiOperation("Excel导出")
|
public void export(HttpServletResponse response) throws Exception {
|
List<SampleExcel> list = new ArrayList<>();
|
list.add(new SampleExcel("1234567890", "这是测试一", "测试一", "这是Excel测试一", "ce shi 1"));
|
list.add(new SampleExcel("1234567891", "这是测试二", "测试二", "这是Excel测试二", "ce shi 2"));
|
list.add(new SampleExcel("1234567892", "这是测试三", "测试三", "这是Excel测试三", "ce shi 3"));
|
list.add(new SampleExcel("1234567893", "这是测试四", "测试四", "这是Excel测试四", "ce shi 4"));
|
|
ExcelUtils.export(response, null, list, SampleExcel.class);
|
}
|
|
@GetMapping("multiple/header/excel/export")
|
@ApiOperation("多表头Excel导出")
|
public void export2(HttpServletResponse response) throws Exception {
|
List<MultipleHeaderExcel> list = new ArrayList<>();
|
list.add(new MultipleHeaderExcel("1234567890", "这是测试一", "测试一", "这是Excel测试一", "ce shi 1"));
|
list.add(new MultipleHeaderExcel("1234567891", "这是测试二", "测试二", "这是Excel测试二", "ce shi 2"));
|
list.add(new MultipleHeaderExcel("1234567892", "这是测试三", "测试三", "这是Excel测试三", "ce shi 3"));
|
list.add(new MultipleHeaderExcel("1234567893", "这是测试四", "测试四", "这是Excel测试四", "ce shi 4"));
|
|
ExcelUtils.export(response, null, list, MultipleHeaderExcel.class);
|
}
|
|
@GetMapping("multiple/sheet/excel/export")
|
@ApiOperation("多sheet Excel导出")
|
public void export3(HttpServletResponse response) throws Exception {
|
List<SampleExcel> list = new ArrayList<>();
|
list.add(new SampleExcel("1234567890", "这是测试一", "测试一", "这是Excel测试一", "ce shi 1"));
|
list.add(new SampleExcel("1234567891", "这是测试二", "测试二", "这是Excel测试二", "ce shi 2"));
|
list.add(new SampleExcel("1234567892", "这是测试三", "测试三", "这是Excel测试三", "ce shi 3"));
|
list.add(new SampleExcel("1234567893", "这是测试四", "测试四", "这是Excel测试四", "ce shi 4"));
|
|
List<MultipleHeaderExcel> list2 = new ArrayList<>();
|
list2.add(new MultipleHeaderExcel("1234567890", "这是测试一", "测试一", "这是Excel测试一", "ce shi 1"));
|
list2.add(new MultipleHeaderExcel("1234567891", "这是测试二", "测试二", "这是Excel测试二", "ce shi 2"));
|
list2.add(new MultipleHeaderExcel("1234567892", "这是测试三", "测试三", "这是Excel测试三", "ce shi 3"));
|
list2.add(new MultipleHeaderExcel("1234567893", "这是测试四", "测试四", "这是Excel测试四", "ce shi 4"));
|
|
ExcelUtils.export(response, null, new String[] { "普通", "多表头" }, new List[] { list, list2 });
|
}
|
}
|
