当前位置: 首页 > 工具软件 > Excel2Entity > 使用案例 >

EasyExcel使用ResponseEntity导出excel

罗俭
2023-12-01

之前写过一篇使用EasyExcel导出excel到项目服务器中,然后在下载页面点击下载之后根据路径进行下载的。在这篇文章中我将用ResponseEntity直接返回excel到前端,记录一下该方法,同时也给大家一个参考。
1、controller层代码(根据前端的需求来)

	@GetMapping("/export_excel")
    public ResponseEntity exportExcel(@RequestBody List<PrdRequest> prdRequest) {
        return prdService.exportExcel(prdRequest);
    }

2、service层实现代码

public ResponseEntity exportExcel(List<PrdRequest> prdRequest) {
        try {
        	//根据条件进行查询
        	List<PrdVo> prdVoList = prdMapper.selectPrd(prdRequest);
            //数据写入到字节流
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            String sheetName = "产品信息查询导出表";
            boolean flag = ExcelUtils.writeExcel(bos, PrdVo.class, prdVoList , sheetName);
            //下载文件
            String fileName = sheetName + ".xlsx";
            //excel导出
            if (flag) {
                return ExcelUtils.downloadExcel(fileName, bos);
            }
        } catch (Exception e) {
            log.error("产品信息导出异常", e);
        }
        return null;
    }

3、导出结果实体类

@ApiModel(value = "PrdVo", description = "产品信息excel导出实体")
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class PrdVo implements Serializable {

    @ExcelProperty(value = "产品名称", index = 0)
    private String prdName;

    @ExcelProperty(value = "产品编码", index = 1)
    private String prdCode;

    @ExcelProperty(value = "厂商名称", index = 2)
    private String manufacturerName;
    。。。其它的字段
}

4、ExcelUtils工具类

@Slf4j
public class ExcelUtils {

    /**
     * 表头进行自动扩展的导出
     *
     * @param os        文件输出流
     * @param clazz     Excel实体映射类
     * @param data      导出数据
     * @param sheetName sheet名称
     * @return
     */
    public static Boolean writeExcel(OutputStream os, Class clazz, List<?> data, String sheetName) {
        try (BufferedOutputStream bos = new BufferedOutputStream(os)) {
            EasyExcel.write(bos, clazz)
                    .sheet(sheetName)
                    //使用该方法对表头的宽度进行扩展
                    .registerWriteHandler(new ColumnWidthStyleStrategy())
                    .doWrite(data);
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }
    /**
     * ResponseEntity下载文件
     *
     * @param fileName
     * @param byteOutPutStream
     */
    public static ResponseEntity<byte[]> downloadExcel(String fileName, ByteArrayOutputStream byteOutPutStream) {
        //下载文件
        try {
            HttpHeaders headers = new HttpHeaders();
            headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
            // 文件名称
            headers.setContentDispositionFormData("attachment",
                    new String(fileName.getBytes("GBK"), "ISO8859-1"));
            ResponseEntity<byte[]> responseEntity = new ResponseEntity<byte[]>(byteOutPutStream.toByteArray(), headers, HttpStatus.OK);
            return responseEntity;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
}

5、ColumnWidthStyleStrategy类,不能使用自带的,因为使用自带的只有第一次导出的时候会格式化

public class ColumnWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {
    private static final int MAX_COLUMN_WIDTH = 255;

    private final Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<Integer, Map<Integer, Integer>>(8);

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head,
                                  Integer relativeRowIndex, Boolean isHead) {
        boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
        if (!needSetWidth) {
            return;
        }
        Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());
        if (maxColumnWidthMap == null) {
            maxColumnWidthMap = new HashMap<Integer, Integer>(16);
            CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
        }
        Integer columnWidth = dataLength(cellDataList, cell, isHead);
        if (columnWidth < 0) {
            return;
        }
        if (columnWidth > MAX_COLUMN_WIDTH) {
            columnWidth = MAX_COLUMN_WIDTH;
        }
        Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
        if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
            maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
            writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
        }
    }

    private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
        if (isHead) {
            return cell.getStringCellValue().getBytes(CommonConstant.UTF_8).length;
        }
        CellData cellData = cellDataList.get(0);
        CellDataTypeEnum type = cellData.getType();
        if (type == null) {
            return -1;
        }
        switch (type) {
            case STRING:
                return cellData.getStringValue().getBytes(CommonConstant.UTF_8).length;
            case BOOLEAN:
                return cellData.getBooleanValue().toString().getBytes(CommonConstant.UTF_8).length;
            case NUMBER:
                return cellData.getNumberValue().toString().getBytes(CommonConstant.UTF_8).length;
            default:
                return -1;
        }
    }
}

mapper文件中进行查询的语句没有放在里面,感觉都很简单,没有必要,需要注意的是第四步的时候使用HttpStatus.OK,因为我之前使用了HttpStatus.CREATED,导出的报表会偶发性的出一点问题。
希望这篇文章能够帮助到需要的人,欢迎大家转载收藏。

 类似资料: