EasyExcel结合spire.xls将SQL查询结果转为图片导出

最终通过浏览器访问接口展示效果
localhost:8080/test/get/img

pom依赖

		<!--Spire.Xls excel转换图片生成工具-->
		<dependency>
		    <groupId>e-iceblue</groupId>
		    <artifactId>spire.xls</artifactId>
		    <version>3.9.3</version>
		</dependency>
        <!-- excel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.8</version>
        </dependency>
        <!-- lombok插件 -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

工具类

public class Excel2ImageUtils {
    /**
     * 将查询结果按图片形式导出
     *
     * @param response HttpServletResponse
     * @param dataList easyExcel需要导出的表头字段名称
     * @param classa   类反射对象
     */
    public static void exportImage(HttpServletResponse response, String[] colums, List<?> dataList, Class<?> classa) {
        try {
            //1.创建一个流，等待写入excel文件内容           
            ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
            ServletOutputStream outputStream = null;
            outputStream = response.getOutputStream();
            //添加要导出的字段           
            Set<String> includeColumnFiledNames = new HashSet<>();
            Collections.addAll(includeColumnFiledNames, colums);
            //2.将excel文件写入byteArrayOutputStream中            
            EasyExcel.write(byteArrayOutputStream, classa)
                    .includeColumnFiledNames(includeColumnFiledNames)
                    .sheet()
                    .doWrite(dataList);
            //3.创建inputStream流        
            InputStream inputStream = new ByteArrayInputStream(byteArrayOutputStream.toByteArray());
            //4.创建文档        
            Workbook workbook = new Workbook();
            //5.加载Excel文档            
            workbook.loadFromStream(inputStream);
            //6.获取第一张工作表           
            Worksheet sheet2 = workbook.getWorksheets().get(0);
            //7.设置导出样式           
            CellRange range = sheet2.getRange();
            //内部线           
            range.borderInside(LineStyleType.Thin, Color.BLACK);
            //外部线          
            range.borderAround(LineStyleType.Medium, Color.BLACK);
            //设置文字水平居中          
            range.setHorizontalAlignment(HorizontalAlignType.Center);
            //8.保存到图片          
            BufferedImage bufferedImage = sheet2.toImage(1, 1, sheet2.getLastRow(), sheet2.getLastColumn());
            //9.写出图片到文件         
            ImageIO.write(bufferedImage, "PNG", outputStream);
            //10.关闭流资源         
            outputStream.close();
            inputStream.close();
            byteArrayOutputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

Controller

@RestController
@Api(tags = "导出图片测试")
@RequestMapping("/test")
public class TestController {
    /**
     * 逻辑处理层
     */
    @Autowired
    private ImgTestService imgTestService;

    @GetMapping("get/img")
    @ApiOperation("excel转图片test")
    public void getImg(HttpServletResponse response/*,@RequestBody ParamModel model*/) {
        imgTestService.getImg(response/*,model*/);
    }
}

Service

public void getImg(HttpServletResponse response/*, ParamModel model*/){    //模拟Mybatis查询结果    
        List<ReturnModel> list=new ArrayList<>();
        Collections.addAll(list,
        new ReturnModel("张三","男",20),
        new ReturnModel("李四","女",21),
        new ReturnModel("王五","男",21));
        //开始导出    
        /Excel2ImageUtils.exportImage(
        response,
        new String[]{"name","gender","age"},
        //testMapper.getData(model), 正常的mybatis查询           
        list,
        ReturnModel.class    );
        }

Model

@Data
@AllArgsConstructor
@NoArgsConstructor
@ColumnWidth(12)
@HeadRowHeight(25)
public class ReturnModel {
    @ExcelProperty("姓名")
    private String name;
    @ExcelProperty("性别")
    private String gender;
    @ExcelProperty("年龄")
    private int age;
}