package com.yq.utils; import cn.hutool.core.collection.CollUtil; import cn.hutool.core.io.IoUtil; import cn.hutool.json.JSONArray; import cn.hutool.poi.excel.ExcelReader; import cn.hutool.poi.excel.ExcelUtil; import cn.hutool.poi.excel.ExcelWriter; import cn.hutool.poi.excel.sax.Excel07SaxReader; import cn.hutool.poi.excel.sax.handler.RowHandler; import com.yq.exception.BizException; import com.yq.exception.ErrorMessagesEnum; import lombok.extern.slf4j.Slf4j; import org.apache.commons.lang3.StringUtils; import org.springframework.web.multipart.MultipartFile; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.stream.Collectors; /** * @author: wensm * @date: 2021/5/7 0007 * @description: TODO **/ @Slf4j public class ExcelUtils { private static List<List<Object>> lineList = new ArrayList<>(); /** * excel 导出工具类 * * @param response * @param fileName 文件名 * @param projects 对象集合 * @param columnNames 导出的excel中的列名 * @param keys 对应的是对象中的字段名字 * @throws IOException */ // public static void export(HttpServletResponse response, String fileName, ArrayList<Map<String, Object>> projects, String[] columnNames, String[] keys) throws IOException { public static void export(HttpServletResponse response, String fileName, List<?> projects, String[] keys, String[] columnNames) throws IOException { // Map<String, Object> row1 = new LinkedHashMap<>(); // row1.put("姓名", "张三"); // row1.put("年龄", 23); // row1.put("成绩", 88.32); // row1.put("是否合格", true); // row1.put("考试日期", DateUtil.date()); // // Map<String, Object> row2 = new LinkedHashMap<>(); // row2.put("姓名", "李四"); // row2.put("年龄", 33); // row2.put("成绩", 59.50); // row2.put("是否合格", false); // row2.put("考试日期", DateUtil.date()); // // ArrayList<Map<String, Object>> rows = CollUtil.newArrayList(row1, row2); ExcelWriter bigWriter = ExcelUtil.getBigWriter(); // ExcelWriter bigWriter = ExcelUtil.getWriter(true); for (int i = 0; i < keys.length; i++) { bigWriter.addHeaderAlias(keys[i], columnNames[i]); bigWriter.setColumnWidth(i, 20); } // 一次性写出内容，使用默认样式，强制输出标题 // bigWriter.write(rows, true); bigWriter.write(projects, true); //response为HttpServletResponse对象 response.setContentType("application/vnd.ms-excel;charset=utf-8"); // response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8"); //test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码 // String name = StringUtils.toUtf8String("申请学院"); response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xlsx").getBytes(), "iso-8859-1")); ServletOutputStream out = response.getOutputStream(); bigWriter.flush(out, true); // 关闭writer，释放内存 bigWriter.close(); //此处记得关闭输出Servlet流 IoUtil.close(out); } /** * excel导入工具类 * * @param file 文件 * @return 返回数据集合 * @throws BizException * @throws IOException */ public static List<Map<String, Object>> leading(MultipartFile file) throws BizException, IOException { String fileName = file.getOriginalFilename(); // 上传文件为空 if (StringUtils.isEmpty(fileName)) { throw new BizException(ErrorMessagesEnum.OPERATION_NON_EXCEL_ERROR); } //上传文件大小为1000条数据 if (file.getSize() > 1024 * 1024 * 10) { log.error("upload | 上传失败: 文件大小超过10M，文件大小为：{}", file.getSize()); throw new BizException(ErrorMessagesEnum.OPERATION_LARGE_EXCEL_ERROR); } // 上传文件名格式不正确 if (fileName.lastIndexOf(".") != -1 && !".xlsx".equals(fileName.substring(fileName.lastIndexOf(".")))) { throw new BizException(ErrorMessagesEnum.OPERATION_EXCEL_FORMAT_ERROR); } // log.info("========columNames========{}",columnNames); // //读取数据 // ExcelUtil.read07BySax(file.getInputStream(), 0, createRowHandler()); // //// Excel07SaxReader reader = new Excel07SaxReader(createRowHandler()); //// reader.read(file.getInputStream(),1); // // // log.info("========lineList========{}",lineList.toString()); // //去除excel中的第一行数据 // lineList.remove(0); // // //将数据封装到list<Map>中 // List<Map<String, Object>> dataList = new ArrayList<>(); // for (int i = 0; i < lineList.size(); i++) { // if (null != lineList.get(i)) { // Map<String, Object> hashMap = new HashMap<>(); // for (int j = 0; j < columNames.length; j++) { // Object property = lineList.get(i).get(j); // hashMap.put(columNames[j], property); // } // dataList.add(hashMap); // } else { // break; // } // } // ExcelReader reader = ExcelUtil.getReader(file.getInputStream()); //// reader.addHeaderAlias("学号", "sno"); //// reader.addHeaderAlias("姓名", "name"); //// reader.addHeaderAlias("年龄", "age"); //// reader.addHeaderAlias("性别", "gender"); //// reader.addHeaderAlias("籍贯", "nativePlace"); //// reader.addHeaderAlias("入学时间", "enrollmentTime"); // // for (int i = 0; i < columNames.length; i++) { // reader.addHeaderAlias(columNames[i], keys[i]); // } // List dataList = new ArrayList(); // if(reader.getRowCount()>0) dataList = reader.read(1); // dataList = reader.readAll(); ExcelReader reader = ExcelUtil.getReader(file.getInputStream(),0); List<Map<String,Object>> dataList = reader.readAll(); return dataList; } /** * excel导入工具类 * * @param file 文件 * @return 返回数据集合 * @throws BizException * @throws IOException */ public static List<Map<String, Object>> leading(MultipartFile file,Integer headerRowIndex, Integer startRowIndex) throws BizException, IOException { String fileName = file.getOriginalFilename(); // 上传文件为空 if (StringUtils.isEmpty(fileName)) { throw new BizException(ErrorMessagesEnum.OPERATION_NON_EXCEL_ERROR); } //上传文件大小为1000条数据 if (file.getSize() > 1024 * 1024 * 10) { log.error("upload | 上传失败: 文件大小超过10M，文件大小为：{}", file.getSize()); throw new BizException(ErrorMessagesEnum.OPERATION_LARGE_EXCEL_ERROR); } // 上传文件名格式不正确 if (fileName.lastIndexOf(".") != -1 && !".xlsx".equals(fileName.substring(fileName.lastIndexOf(".")))) { throw new BizException(ErrorMessagesEnum.OPERATION_EXCEL_FORMAT_ERROR); } // log.info("========columNames========{}",columnNames); // //读取数据 // ExcelUtil.read07BySax(file.getInputStream(), 0, createRowHandler()); // //// Excel07SaxReader reader = new Excel07SaxReader(createRowHandler()); //// reader.read(file.getInputStream(),1); // // // log.info("========lineList========{}",lineList.toString()); // //去除excel中的第一行数据 // lineList.remove(0); // // //将数据封装到list<Map>中 // List<Map<String, Object>> dataList = new ArrayList<>(); // for (int i = 0; i < lineList.size(); i++) { // if (null != lineList.get(i)) { // Map<String, Object> hashMap = new HashMap<>(); // for (int j = 0; j < columNames.length; j++) { // Object property = lineList.get(i).get(j); // hashMap.put(columNames[j], property); // } // dataList.add(hashMap); // } else { // break; // } // } // ExcelReader reader = ExcelUtil.getReader(file.getInputStream()); //// reader.addHeaderAlias("学号", "sno"); //// reader.addHeaderAlias("姓名", "name"); //// reader.addHeaderAlias("年龄", "age"); //// reader.addHeaderAlias("性别", "gender"); //// reader.addHeaderAlias("籍贯", "nativePlace"); //// reader.addHeaderAlias("入学时间", "enrollmentTime"); // // for (int i = 0; i < columNames.length; i++) { // reader.addHeaderAlias(columNames[i], keys[i]); // } // List dataList = new ArrayList(); // if(reader.getRowCount()>0) dataList = reader.read(1); // dataList = reader.readAll(); ExcelReader reader = ExcelUtil.getReader(file.getInputStream(),0); // List<Map<String,Object>> dataList = reader.readAll(); List<Map<String,Object>> dataList = reader.read(headerRowIndex,startRowIndex,reader.getRowCount()); return dataList; } /** * excel导入工具类 * * @param file 文件 * @return 返回数据集合 * @throws BizException * @throws IOException */ public static List<Map<String, Object>> leadingSheets(MultipartFile file,Integer headerRowIndex, Integer startRowIndex) throws BizException, IOException { String fileName = file.getOriginalFilename(); // 上传文件为空 if (StringUtils.isEmpty(fileName)) { throw new BizException(ErrorMessagesEnum.OPERATION_NON_EXCEL_ERROR); } //上传文件大小为1000条数据 if (file.getSize() > 1024 * 1024 * 10) { log.error("upload | 上传失败: 文件大小超过10M，文件大小为：{}", file.getSize()); throw new BizException(ErrorMessagesEnum.OPERATION_LARGE_EXCEL_ERROR); } // 上传文件名格式不正确 if (fileName.lastIndexOf(".") != -1 && !".xlsx".equals(fileName.substring(fileName.lastIndexOf(".")))) { throw new BizException(ErrorMessagesEnum.OPERATION_EXCEL_FORMAT_ERROR); } // log.info("========columNames========{}",columnNames); // //读取数据 // ExcelUtil.read07BySax(file.getInputStream(), 0, createRowHandler()); // //// Excel07SaxReader reader = new Excel07SaxReader(createRowHandler()); //// reader.read(file.getInputStream(),1); // // // log.info("========lineList========{}",lineList.toString()); // //去除excel中的第一行数据 // lineList.remove(0); // // //将数据封装到list<Map>中 // List<Map<String, Object>> dataList = new ArrayList<>(); // for (int i = 0; i < lineList.size(); i++) { // if (null != lineList.get(i)) { // Map<String, Object> hashMap = new HashMap<>(); // for (int j = 0; j < columNames.length; j++) { // Object property = lineList.get(i).get(j); // hashMap.put(columNames[j], property); // } // dataList.add(hashMap); // } else { // break; // } // } // ExcelReader reader = ExcelUtil.getReader(file.getInputStream()); //// reader.addHeaderAlias("学号", "sno"); //// reader.addHeaderAlias("姓名", "name"); //// reader.addHeaderAlias("年龄", "age"); //// reader.addHeaderAlias("性别", "gender"); //// reader.addHeaderAlias("籍贯", "nativePlace"); //// reader.addHeaderAlias("入学时间", "enrollmentTime"); // // for (int i = 0; i < columNames.length; i++) { // reader.addHeaderAlias(columNames[i], keys[i]); // } // List dataList = new ArrayList(); // if(reader.getRowCount()>0) dataList = reader.read(1); // dataList = reader.readAll(); ExcelReader reader1 = ExcelUtil.getReader(file.getInputStream()); // ExcelReader reader = ExcelUtil.getReader(file.getInputStream(),0); int sheetCount = reader1.getSheetCount(); log.info("================sheetCount========={}",sheetCount); List<Map<String,Object>> dataList = new ArrayList<>(); for(int i=0;i<sheetCount;i++){ ExcelReader reader = ExcelUtil.getReader(file.getInputStream(),i); dataList.addAll(reader.read(headerRowIndex,startRowIndex,reader.getRowCount())); } // List<Map<String,Object>> dataList = reader.readAll(); // dataList = reader.read(headerRowIndex,startRowIndex,reader.getRowCount()); return dataList; } /** * 通过实现handle方法编写我们要对每行数据的操作方式 */ private static RowHandler createRowHandler() { //清空一下集合中的数据 lineList.removeAll(lineList); // log.info("====remove====lineList========{}",lineList.toString()); return new RowHandler() { @Override public void handle(int i, long l, List<Object> list) { // log.info("====add====list========{}",list.toString()); //将读取到的每一行数据放入到list集合中 JSONArray jsonObject = new JSONArray(list); lineList.add(jsonObject.toList(Object.class)); // log.info("====add====lineList========{}",lineList.toString()); } // @Override // public void handle(int sheetIndex, int rowIndex, List rowlist) { // //将读取到的每一行数据放入到list集合中 // JSONArray jsonObject = new JSONArray(rowlist); // lineList.add(jsonObject.toList(Object.class)); // } }; } }