easypoi导出另外一种方式

easypoi导出另外一种方式

1.导入pom依赖
<!--easypoi  start-->
		<dependency>
			<groupId>cn.afterturn</groupId>
			<artifactId>easypoi-base</artifactId>
			<version>3.2.0</version>
		</dependency>

		<dependency>
			<groupId>cn.afterturn</groupId>
			<artifactId>easypoi-web</artifactId>
			<version>3.2.0</version>
		</dependency>

		<dependency>
			<groupId>cn.afterturn</groupId>
			<artifactId>easypoi-annotation</artifactId>
			<version>3.2.0</version>
		</dependency>
		<!--easypoi end-->
2. 实体类User
	
public class User implements Serializable {
	private static final long serialVersionUID = 1L;
	
	/**
	 * 用户ID
	 */
	@TableId
	private Long id;

	/**
	 * 账号
	 */
	@NotBlank(message="账号不能为空", groups = {AddGroup.class, UpdateGroup.class})
	private String loginName;

	/**
	 * 用户名
	 */
	@NotBlank(message="用户名不能为空", groups = {AddGroup.class, UpdateGroup.class})
	private String userName;

	private String icon;

	/**
	 * 密码
	 */
	@NotBlank(message="密码不能为空", groups = AddGroup.class)
	private String password;

	/**
	 * 盐
	 */
	private String salt;

	/**
	 * 邮箱
	 */
	//@NotBlank(message="邮箱不能为空", groups = {AddGroup.class, UpdateGroup.class})
	@Email(message="邮箱格式不正确", groups = {AddGroup.class, UpdateGroup.class})
	private String email;

	/**
	 * 手机号
	 */
	private String tel;

	/**
	 * 状态  0：禁用   1：正常
	 */
	private Integer status;

	/**
	 * 登录ip
	 */
	private String loginIp;

	/**
	 * 登录失败次数
	 */
	private Integer loginErrorCount;


	/**
	 * 角色ID列表
	 */
	@TableField(exist=false)
	private List<Long> roleIdList;

	/**
	 * 创建者ID
	 */
	private Long createUserId;

	/**
	 * 最后一次登录时间
	 */
	private Date lastLoginTime;

	/**
	 * 创建时间
	 */
	private Date createTime;

	@TableField(exist = false)
	private String roleName;

	@TableField(exist = false)
	private String clazzName;

}

	
3. 控制器方法

@ApiOperation(value="导出学生", notes="导出学生")
    @PostMapping("/exportStudent")
    public Object exportStudent(@RequestBody @ApiJsonObject(name = "map2",paramType = "body",
            value = {
                    @ApiJsonProperty(key = "clazzId", example = "29", description = "班级id"),
                    @ApiJsonProperty(key = "loginName", example = "gsl", description = "登录名称"),
                    @ApiJsonProperty(key = "downloadName", example = "teacher", description = "下载文档名称",required = true),
                    @ApiJsonProperty(key = "title", example = "teacher", description = "title",required = true),
                    @ApiJsonProperty(key = "sheetName", example = "teacher", description = "sheetName",required = true)
            })  Map map, HttpServletResponse response) {
        Integer clazzId = (Integer) map.get("clazzId"); // 班级id
        String loginName = (String) map.get("loginName"); //登录名称
        String downloadName =(String) map.get("downloadName"); //文件名称
        String title = (String) map.get("title"); //标题
        String sheetName = (String) map.get("sheetName"); // sheet名称
        if (StringUtils.isBlank(title) || StringUtils.isBlank(sheetName) || StringUtils.isBlank(downloadName)) {
            return R.error("标题、sheet名称、文件名称不能为空");
        }
        //当前教师id
        Long userId = this.getUserId();
        List<User> userList = userService.queryUserForAllStudentByConditions(userId,loginName,clazzId);
        // List<ExportStudent> userList = userService.exportStudentByConditions(userId,loginName,clazzId);
        ExportParams exportParams = new ExportParams();
        exportParams.setSheetName(sheetName);
        response.setContentType("application/x-download;charset=UTF-8");
        FileOutputStream fos = null;
        InputStream input = null;
        OutputStream output = null;
        List<Map<String, String>> mapList = new ArrayList<Map<String, String>>();
        List<ExcelExportEntity> entityList = new ArrayList<ExcelExportEntity>();
        for (User user:userList) {
            Map<String, String> map1 = new LinkedHashTreeMap<>();
            map1.put("账号", user.getLoginName());
            map1.put("用户名", user.getUserName());
            map1.put("班级",user.getClazzName());
            if (user.getLastLoginTime() != null) {
                map1.put("最后一次登录时间", MyDateUtils.formatDate(user.getLastLoginTime(), MyDateUtils.COM_Y_M_D_H_M_S_PATTERN));
            }else {
                map1.put("最后一次登录时间", null);
            }
            mapList.add(map1);
        }
        for (String keys : mapList.get(0).keySet()) {
            ExcelExportEntity exportEntity = new ExcelExportEntity(keys, keys, 25);
            //设置是否换行
            exportEntity.setWrap(true);
            exportEntity.setHeight(15);
            entityList.add(exportEntity);
        }
        File file = null;
        Workbook workbook = ExcelExportUtil.exportBigExcel(exportParams, entityList, mapList);
        try {
            fos = new FileOutputStream(Constant.SPT + downloadName + ".xls");
            workbook.write(fos);
            file = new File(Constant.SPT + downloadName + ".xls");

            //不是附件 直接下载xlsx
            RequestUtils.setDownloadHeader(response, new String(downloadName.getBytes(), "ISO8859-1") + ".xls");
            input = new FileInputStream(file);
            output = response.getOutputStream();
            byte[] buff = new byte[1024];
            int len = 0;
            while ((len = input.read(buff)) > -1) {
                output.write(buff, 0, len);
            }

        } catch (IOException e) {
            return R.error("导出失败");
        } finally {
            try {
                if (fos != null) {
                    fos.close();
                }
            } catch (IOException e) {
                System.out.println(e.getMessage());
            }
            try {
                if (output != null) {
                    output.close();
                }
            } catch (IOException e) {
                System.out.println(e.getMessage());
            }
            try {
                if (input != null) {
                    input.close();
                }
            } catch (IOException e) {
                System.out.println(e.getMessage());
            }
            ExcelExportUtil.closeExportBigExcel();
            if (file != null) {
                file.delete();
            }
        }
        return null;
    }

easypoi导出图片的时候会报错：


这是因为导出图片的时候，有的值是空值

解决办法：

import cn.afterturn.easypoi.cache.ImageCache;
import cn.afterturn.easypoi.cache.manager.POICacheManager;
import com.google.common.cache.CacheBuilder;
import com.google.common.cache.CacheLoader;
import com.google.common.cache.LoadingCache;
import org.apache.poi.util.IOUtils;
import org.springframework.boot.context.event.ApplicationReadyEvent;
import org.springframework.context.ApplicationListener;
import org.springframework.stereotype.Component;

import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.util.concurrent.TimeUnit;

@Component
public class ExcelListener implements ApplicationListener<ApplicationReadyEvent> {

    @Override
    public void onApplicationEvent(ApplicationReadyEvent applicationReadyEvent) {
        LoadingCache<String, byte[]> loadingCache = CacheBuilder.newBuilder().expireAfterWrite(1, TimeUnit.DAYS)
                .maximumSize(2000).build(new CacheLoader<String, byte[]>() {
                    @Override
                    public byte[] load(String imagePath) throws Exception {
                        InputStream is = POICacheManager.getFile(imagePath);
                        BufferedImage bufferImg = ImageIO.read(is);
                        ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
                        try {
                            ImageIO.write(bufferImg,
                                    imagePath.substring(imagePath.lastIndexOf(".")+1),
                                    byteArrayOut);
                            return byteArrayOut.toByteArray();
                        } finally {
                            IOUtils.closeQuietly(is);
                            IOUtils.closeQuietly(byteArrayOut);
                        }
                    }
                });
        ImageCache.setLoadingCache(loadingCache);
    }
}