因为公司禁止使用第三方工具，所以使用的原生poi来实现的。（如果公司没要求，建议使用阿里的esayExcel）
一、创建导出类的扫描注解，判断是否是导出的信息
@Retention(RetentionPolicy.RUNTIME) public @interface ExcelColumn { /** * 列索引 * @return */ public int columnIndex() default 0; /** * 列名 * @return */ public String columnName() default ""; }
二、创建导出对应的java类，并加上面的自定义注解；
@Data public class BillMeituanPaymentOrderDto { @ExcelColumn(columnIndex = 0,columnName = "产品类型") private String productType; @ExcelColumn(columnIndex = 1,columnName = "车型") private String carType; @ExcelColumn(columnIndex = 2,columnName = "订单号") private String orderNo; @ExcelColumn(columnIndex = 3,columnName = "城市") private String city; @ExcelColumn(columnIndex = 4,columnName = "订单支付状态") private String orderPayState; @ExcelColumn(columnIndex = 5,columnName = "订单创建时间") private String orderTime; @ExcelColumn(columnIndex = 6,columnName = "订单支付完成时间") private String payTime; @ExcelColumn(columnIndex = 7,columnName = "服务商订单id") private String operatorOrderId; @ExcelColumn(columnIndex = 8,columnName = "订单是否归属当月账期") private String isCurrentMonth; @ExcelColumn(columnIndex = 9,columnName = "是否垫付") private String isAdvance; }
三、生成公用的excel导出util类：ExportExcelUtils
import lombok.extern.slf4j.Slf4j; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.streaming.SXSSFRow; import org.apache.poi.xssf.streaming.SXSSFSheet; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.springframework.util.CollectionUtils; import javax.servlet.http.HttpServletResponse; import java.io.*; import java.lang.reflect.Field; import java.text.SimpleDateFormat; import java.util.Calendar; import java.util.Date; import java.util.GregorianCalendar; import java.util.List; @Slf4j public class ExportExcelUtils { /** * 将数据填充到excel里 * @param workbook 要生成excel * @param list 填充数据集合 * @param isCreateTitle 是否创建标题 * @param isCreateSheet 是否创建sheet * @param sheetName sheet名称 * @return * @throws IOException */ public static SXSSFWorkbook updateWorkbook(SXSSFWorkbook workbook, List<?> list, boolean isCreateTitle, boolean isCreateSheet, String sheetName) throws IOException { SXSSFSheet sheet; // 设置工作表的名称 if (isCreateSheet) { sheet = workbook.createSheet(sheetName); } else { sheet = workbook.getSheet(sheetName); } // 获取实体所有属性 Field[] fields = list.get(0).getClass().getDeclaredFields(); ExcelColumn excelColumn; // 列索引 int index = 0; // 行 SXSSFRow row; //创建标题 if (isCreateTitle) { Font font = workbook.createFont(); //设置标题高度、字体 font.setFontHeightInPoints((short) 16); font.setFontName("宋体"); font.setBold(true); // 创建单元格标题样式 CellStyle styleTitle = workbook.createCellStyle(); styleTitle.setAlignment(HorizontalAlignment.CENTER); // 设置标题的下左右边框 styleTitle.setBorderBottom(BorderStyle.THIN); styleTitle.setBorderLeft(BorderStyle.THIN); styleTitle.setBorderRight(BorderStyle.THIN); styleTitle.setFont(font); //设置标题背景颜色 styleTitle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); styleTitle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 创建第1行 row = sheet.createRow(0); // 列名称 String name; // 创建表头 for (Field f : fields) { // 是否是注解 if (f.isAnnotationPresent(ExcelColumn.class)) { // 获取注解 excelColumn = f.getAnnotation(ExcelColumn.class); // 获取列索引 index = excelColumn.columnIndex(); // 列名称 name = excelColumn.columnName(); //设置表单元格宽度值 sheet.setColumnWidth(index, (name.getBytes().length + 2) * 256); //设置标题单元格高度 row.setHeightInPoints(35); // 创建单元格 creCell(row, index, name, styleTitle); } } } // 创建单元格样式 CellStyle style = workbook.createCellStyle(); // 居中显示 style.setAlignment(HorizontalAlignment.CENTER); // 设置上下左右边框 style.setBorderBottom(BorderStyle.THIN); style.setBorderLeft(BorderStyle.THIN); style.setBorderRight(BorderStyle.THIN); style.setBorderTop(BorderStyle.THIN); // 行索引 因为表头已经设置，索引行索引从1开始 int rowIndex = sheet.getLastRowNum()+1; for (Object obj : list) { // 创建新行，索引加1,为创建下一行做准备 row = sheet.createRow(rowIndex++); for (Field f : fields) { // 设置属性可访问 f.setAccessible(true); // 判断是否是注解 if (f.isAnnotationPresent(ExcelColumn.class)) { // 获取注解 excelColumn = f.getAnnotation(ExcelColumn.class); // 获取列索引 index = excelColumn.columnIndex(); //设置表单元格宽度值 try { creCell(row, index, String.valueOf(f.get(obj)), style); } catch (IllegalAccessException e) { e.printStackTrace(); } } } } return workbook; } /** * 将excel生成到指定的路径 * @param workbook * @param filepath 路径 * @param filename 文件名称 * @return */ public static boolean exportExe(SXSSFWorkbook workbook, String filepath, String filename) { boolean success = false; FileOutputStream output = null; try { //判断是否存在目录. 不存在则创建 isChartPathExist(filepath); //输出Excel文件 output = new FileOutputStream(filepath + "/" + filename); //写入磁盘 workbook.write(output); success = true; } catch (Exception e) { log.info("导出错误", e); } finally { if (workbook != null) { //消除生成excel表格生成的临时文件 workbook.dispose(); } Streams.close(workbook); Streams.close(output); } return success; } /** * 导出设置返回头 * * @param response * @param excelName */ public static void setHeader(HttpServletResponse response, String excelName) { response.setContentType("application/vnd.ms-excel;charset=utf-8"); response.setHeader("Content-type", "application/xls;charset=UTF-8"); try { String fileName = new String(excelName.getBytes("UTF-8"), "ISO8859-1"); response.addHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls"); } catch (UnsupportedEncodingException e) { log.error("设置返回头出错啦,", e); } } /** * 创建单元格 * * @param row * @param c * @param cellValue * @param style */ public static void creCell(Row row, int c, String cellValue, CellStyle style) { Cell cell = row.createCell(c); cell.setCellValue(cellValue); cell.setCellStyle(style); } /** * 生成excel到指定路径 * * @param wb * @param path * @throws Exception */ public static void generateExcelToPath(HSSFWorkbook wb, String path) throws Exception { FileOutputStream fos = null; try { fos = new FileOutputStream(path); wb.write(fos); } finally { if (fos != null) { fos.flush(); fos.close(); } if (wb != null) { wb.close(); } } } /** * 判断文件夹是否存在，如果不存在则新建 * * @param dirPath 文件夹路径 */ public static void isChartPathExist(String dirPath) { File file = new File(dirPath); if (!file.exists()) { file.mkdirs(); } } public static boolean delAllFile(String path) { boolean flag = false; File file = new File(path); if (!file.exists()) { return flag; } if (!file.isDirectory()) { return flag; } String[] tempList = file.list(); File temp = null; for (int i = 0; i < tempList.length; i++) { if (path.endsWith(File.separator)) { temp = new File(path + tempList[i]); } else { temp = new File(path + File.separator + tempList[i]); } if (temp.isFile()) { temp.delete(); } if (temp.isDirectory()) { delAllFile(path + "/" + tempList[i]);// 先删除文件夹里面的文件 flag = true; } } return flag; } }
四、使用
@Slf4j @Service public class BizExportMeituanExcelService { //默认10分钟 protected int workSeconds = 60 ; @Autowired private WorkLock workLock; @Autowired private WorkLogService workLogService;
//这里是application里的配置生成文件路径信息，并设置了默认值 @Value("${test.bill.file:/app/data/bill}") private String axebillFile; /** * 批量导出Excel * * @param response * @param req 为生成excel的过滤条件，这个自己根据需求定义 * @throws IOException */ public JsonResult exportExcel(HttpServletResponse response, ExportBillMeituanReq req) throws IOException { workLock.lock(workSeconds); JsonResult result = new JsonResult(); if (StringUtils.isBlank(req.getActNo()) || StringUtils.isBlank(req.getChannel())) { result.setSuccess(false); result.setMessage("账期和产品类型不能为空！"); return result; } String msg = ""; Date startTime = new Date(); try { boolean success = false; String actNo = req.getActNo(); String channel = req.getChannel(); List<String> cityList = settleDayService.getCityList(actNo,channel); //如果账期内有交易的城市，才生成excel if (!CollectionUtils.isEmpty(cityList)) { String filepath = getAxebillFile() + "/meituan/" + actNo + "/" + channel; ExportExcelUtils.delAllFile(filepath); for (String s : cityList) { success = exportSingleExcel(response, actNo, s, channel, filepath); } } else { result.setSuccess(true); result.setMessage("请先点击【生成结算单】，或者没有数据！"); return result; } if (success) { result.setSuccess(true); result.setMessage("生成成功！"); } else { result.setSuccess(false); result.setMessage("生成失败！"); } } catch (Exception e) {throw e; } finally { workLock.unLock(); } return result; } /** * 生成单个excel表格 */ private boolean exportSingleExcel(HttpServletResponse response, String actNo, String city, String channel, String filepath) throws IOException { //单张表格的各个sheet // 创建Excel工作簿对象 SXSSFWorkbook workbook = new SXSSFWorkbook(100); //查询3个sheet的数据 List<BillMeituanPaymentOrderDto> paymentOrderTotalList = new ArrayList<>(); int total = paymentOrderService.getTotal(actNo, city,channel); int pageSize = 10000; for (int i = 0; i < (total + pageSize - 1) / pageSize; i++) {
//这里是分页查要导出的询数据 List<BillMeituanPaymentOrderDto> paymentOrderList = paymentOrderService.getPaymentOrderList(actNo, city, channel, i*pageSize, pageSize); if (i == 0) {
//将要导出的数据生成excel ExportExcelUtils.updateWorkbook(workbook, paymentOrderList, true, true, "订单明细"); } else { ExportExcelUtils.updateWorkbook(workbook, paymentOrderList, false, false, "订单明细"); } } List<BillMeituanSettleDayDto> settleDayList = settleDayService.getSettleDayList(actNo, city,channel); if (!CollectionUtils.isEmpty(settleDayList)) { ExportExcelUtils.updateWorkbook(workbook, settleDayList, true, true, "账单汇总"); } //设置生成excel的名称 String filename = actNo + "-" + city + "-" + channel + "账单.xlsx"; return ExportExcelUtils.exportExe(workbook, filepath, filename); } }