工具类：
1、ExcelUtil.java
public class ExcelUtil<T> {
/***
* 读取Excel方法
*/
public static <T extends BaseRowModel> ArrayList<T> readExcel(MultipartFile excel, Class<T> clazz) {
try {
InputStream in = new BufferedInputStream(excel.getInputStream());
GeneralExcelListener<T> listener = new GeneralExcelListener<>();
ExcelReader excelReader = new ExcelReader(in, ExcelTypeEnum.XLS, listener);
excelReader.read(new Sheet(1, 1, clazz));
return listener.getSuccessDatas();
} catch (Exception e) {
return new ArrayList<T>();
}
}
/**
* 导出 Excel ：一个 sheet，带表头
*
* @param response HttpServletResponse
* @param list 数据 list，每个元素为一个 BaseRowModel
* @param fileName 导出的文件名
* @param sheetName 导入文件的 sheet 名
* @param object 映射实体类，Excel 模型
*/
public static void writeExcel(HttpServletResponse response, List<? extends BaseRowModel> list,
String fileName, String sheetName, BaseRowModel object) {
ExcelWriter writer = new ExcelWriter(getOutputStream(fileName, response), ExcelTypeEnum.XLSX);
Sheet sheet = new Sheet(1, 0, object.getClass());
sheet.setSheetName(sheetName);
writer.write(list, sheet);
writer.finish();
}
/**
* 导出文件时为Writer生成OutputStream
*/
private static OutputStream getOutputStream(String fileName, HttpServletResponse response) {
try {
return response.getOutputStream();
} catch (IOException e) {
throw new RuntimeException("导出文件时为Writer生成OutputStream失败！");
}
}
/**
* 返回 ExcelReader
*
* @param excel 需要解析的 Excel 文件
* @param excelListener new ExcelListener()
*/
private static ExcelReader getReader(MultipartFile excel,
ExcelListener excelListener) {
String filename = excel.getOriginalFilename();
if (filename == null || (!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx"))) {
throw new RuntimeException("文件格式错误！");
}
InputStream inputStream;
try {
inputStream = new BufferedInputStream(excel.getInputStream());
return new ExcelReader(inputStream, null, excelListener, false);
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
}
2、GeneralExcelListener.java
/**
* 解析Excel数据，返回数据的类
**/
public class GeneralExcelListener<T> extends AnalysisEventListener<T> {
private final Logger logger = LoggerFactory.getLogger(this.getClass());
//自定义用于暂时存储data。
//可以通过实例获取该值
private ArrayList<T> successDatas = new ArrayList<>();
private ArrayList<T> errorDatas = new ArrayList<>();
public GeneralExcelListener() {
}
@Override
public void invoke(T o, AnalysisContext analysisContext) {
successDatas.add(o);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
}
public ArrayList<T> getErrorDatas() {
return errorDatas;
}
public ArrayList<T> getSuccessDatas() {
return successDatas;
}
}
示例：
Controller层代码
1、导入示例
@PostMapping(value = "/import")
public ModelAndView import(@RequestParam(value = "file", required = true) MultipartFile file) {
ModelAndView mv = new ModelAndView();
//一行代码即可获取解析得到的Excel中的数据
List<Test> datas = ExcelUtil.readExcel(file, Test.class);
//然后对获取的数据，在service层进行业务处理
ResultMsg result = provShareConfigService.insertList(datas);
return mv;
}
2、导出示例
@PostMapping(value = "/export")
public void export(HttpServletRequest request, HttpServletResponse response, @RequestBody JSONObject ob) {
//前端传来的参数
String date = ob.getString("date");
String fileName = "退赔导出";
String sheetName = "sheet1";
//从数据库得到的list集合
List<Test> list = testService.selectAll(date);
//一行代码即可导出
ExcelUtil.writeExcel(response, list, fileName, sheetName, new Test());
}
posted on
