Excel导入导出(easypoi)
目前使用easypoi进行文件导入导出
1.引入jar,版本可以自己选择使用量多的,两个jar版本最好一致,每个版本有细微差异,easypoi有部分bug,在实际使用过程中遇到可以跟下源码,根据业务进行调整
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>${easypoi.version}</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>${easypoi.version}</version>
</dependency>
2.Excel文件导入(支持多sheet)
/** * 文件根据sheet导入 * @param file excel文件 * @param index sheet下标 * @param titleRows 标题行数 * @param headerRows 表头行数 * @param tClass 表实体类 * @return */ public static <T> List<T> importSheet(File file, int index, Integer titleRows, Integer headerRows, Class<T> tClass) { ImportParams importParams = new ImportParams(); importParams.setStartSheetIndex(index); importParams.setTitleRows(titleRows); importParams.setHeadRows(headerRows); importParams.setNeedVerify(false); List<T> ts = null; try { ts = ExcelImportUtil.importExcel(file,tClass,importParams); }catch (Exception e){ e.printStackTrace(); } return ts; }
附加:实体案例,调用案例,仅供参考
2.1实体类
@ExcelTarget("xx") public class xxDto extends Model<xxxDto> { //orderNum 表示表格显示顺序,从0开始 @Excel(name = "cardNum",orderNum = "0") @ApiModelProperty(value = "身份证号码") private String cardNum; @Excel(name = "name",orderNum = "1") @ApiModelProperty(value = "姓名") private String name; }
2.2调用类
public ResponseWrapper importFile(MultipartFile multipartFilefile){ //springboot 传输文件为multipartFile,需要转换文件类型为File File file = FileUtils.transferToFile(multipartFilefile); //传输参数与工具类对应 List<xxDto> xxDtos = WorkBookUtils.importSheet(file, 0, 0, 1, xxDto.class); //解析完的数据做相应业务处理 }
3.Excel文件导出(支持多sheet)
public static Map<String, Object> createOneSheet(String sheetName, Class<?> clazz, List<?> data){ ExportParams exportParams = new ExportParams(null,sheetName, ExcelType.XSSF); return createOneSheet(exportParams,clazz,data); } public static Map<String, Object> createOneSheet(ExportParams exportParams,Class<?> clazz,List<?> data){ Map<String, Object> map = new HashMap<>(); map.put("title",exportParams); map.put("entity", clazz); map.put("data",data); return map; } public static Workbook mutiSheet(List<Map<String, Object>> mapListList){ Workbook workbook; workbook = ExcelExportUtil.exportExcel(mapListList,ExcelType.XSSF); return workbook; }
附加:调用案例,仅供参考
3.1调用类
private File fillData(File savefile) throws Exception { List<Map<String, Object>> lists = new ArrayList<>(); List<xx> xxDatas = jcxxService.getBaseMapper().selectList(xxWrapper); Map<String, Object> xxTemp = WorkBookUtils.createOneSheet("xx", xx.class, xxDatas); lists.add(xxTemp); Workbook workbook = WorkBookUtils.mutiSheet(lists); FileOutputStream fos = new FileOutputStream(savefile); workbook.write(fos); fos.close(); return savefile; }
4.文件下载
/** * 下载文件 * * @param request * @param response * @param file * @param fileName * @throws IOException */ private void datatoResponse(HttpServletRequest request, HttpServletResponse response, File file, String fileName) throws IOException { OutputStream out = null; FileInputStream in = null; try { // 1.读取要下载的内容 in = new FileInputStream(file); response.setHeader("Content-Disposition", "attachment;filename=".concat(String.valueOf(URLEncoder.encode(fileName, "UTF-8")))); String mineType = request.getServletContext().getMimeType(fileName); response.setContentType(mineType); response.setHeader("Content-disposition", "attachment; filename=" + fileName); out = response.getOutputStream(); int len = 0; byte[] buffer = new byte[1024]; while ((len = in.read(buffer)) > 0) { out.write(buffer, 0, len); } } catch (IOException e) { e.printStackTrace(); } finally { if (out != null) { out.close(); } if (in != null) { in.close(); } } }
本文来自博客园,作者:小辉辉。。,转载请注明原文链接:https://www.cnblogs.com/zjylsh/p/15504783.html