easypoi导出Excel

1.jar包

  		<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>

2.实体类

package com.gt.model.menu;

import cn.afterturn.easypoi.excel.annotation.Excel;

/**
 * @功能说明：实体,参数看api文档,get,set方法太多,自己创建
 * 构造方法必须有
 * @作者： zhangdan
 * @创建日期：2019-12-02
 * @版本号：V1.0
 */
public class ExportClue {
    @Excel(name = "序号")
    private Integer serialNumber;

    @Excel(name = "编号", orderNum = "1", width = 30)
    private String number;

    @Excel(name = "举报人", orderNum = "2", width = 20)
    private String name;

    @Excel(name = "举报人手机号", orderNum = "3", width = 15)
    private String phone;

    @Excel(name = "举报内容", orderNum = "4", width = 30)
    private String content;

    @Excel(name = "线索类型", orderNum = "5", width = 15)
    private String typeName;

    @Excel(name = "状态", orderNum = "6", replace = {"已采纳代发红包_3"}, width = 15)
    private Integer state;

    @Excel(name = "奖励金额(元)", orderNum = "7", width = 15)
    private String money;

    @Excel(name = "录入时间", orderNum = "8", width = 20)
    private String createtime;

    @Excel(name = "采纳时间", orderNum = "9", width = 20)
    private String adoptTime;

    @Excel(name = "领取方式", orderNum = "10", replace = {" 线上领取_1", "线下领取_2"}, width = 10)
    private String drawType;

    public ExportClue(Integer serialNumber, String number, String name, String phone, String content, String typeName, Integer state, String money, String createtime, String adoptTime, String drawType) {
        this.serialNumber = serialNumber;
        this.number = number;
        this.name = name;
        this.phone = phone;
        this.content = content;
        this.typeName = typeName;
        this.state = state;
        this.money = money;
        this.createtime = createtime;
        this.adoptTime = adoptTime;
        this.drawType = drawType;
    }
}

3.工具类

package com.gt.utils;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.word.WordExportUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;


/**
 * @author zhangdan
 * @date 2019/8/12 15:43
 * @content:
 */
public class ExcelUtiles {
    private static Log log = LogFactory.getLog(ExcelUtiles.class);

    /**
     * @param params map数据集合
     * 暂时没用
     * @return
     */
    public Boolean exportWord(String templatePath, String temDir, Map<String, Object> params, HttpServletRequest request, HttpServletResponse response) throws IOException {
        String fileName = temDir + PbUtils.getCurrentTime() + "分局举报奖励审批表.docx";
        try {
            XWPFDocument doc = WordExportUtil.exportWord07(temDir + templatePath, params);
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + new String((fileName).getBytes(), "iso-8859-1"));
            OutputStream out = response.getOutputStream();
            doc.write(out);
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,
                                   String fileName, boolean isCreateHeader, HttpServletResponse response) {
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName,
                                   HttpServletResponse response) {
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
    }

    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        defaultExport(list, fileName, response);
    }

    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName,
                                      HttpServletResponse response, ExportParams exportParams) {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        if (workbook != null) ;
        downLoadExcel(fileName, response, workbook);
    }

    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(PbUtils.getCurrentTime() + fileName, "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            //throw new NormalException(e.getMessage());
        }
    }


    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        if (workbook != null) ;
        downLoadExcel(fileName, response, workbook);
    }

    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
        if (StringUtils.isBlank(filePath)) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
        } catch (NoSuchElementException e) {
            System.out.println("模板不能为空");
            //throw new NormalException("模板不能为空");
        } catch (Exception e) {
            e.printStackTrace();
            //throw new NormalException(e.getMessage());
        }
        return list;
    }

    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
        if (file == null) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
        } catch (NoSuchElementException e) {
            System.out.println("excel文件不能为空");
            // throw new NormalException("excel文件不能为空");
        } catch (Exception e) {
            //throw new NormalException(e.getMessage());
            System.out.println(e.getMessage());
        }
        return list;
    }
}

4.controller

/**
	 * excel导出
	 *
	 * @param reportClue
	 * @param response
	 * @return
	 */
	@RequestMapping("/export")
	@ResponseBody
	public List<Object> export(ReportClue reportClue, HttpServletResponse response) {
		try {
			List<Map> list = reportClueService.isDownload(reportClue);
			List<ExportClue> personList = new ArrayList<>();
			for (int i = 0; i < list.size(); i++) {
				Map<String, Object> maps = list.get(i);
				Integer number = i + 1;
				ExportClue exportClue = new ExportClue(number, maps.get("number").toString(),
						maps.get("tName").toString(), maps.get("phone").toString(), maps.get("content").toString(),
						maps.get("typeName").toString(), (Integer) maps.get("state"), maps.get("money").toString(),
						maps.get("createtime").toString(), maps.get("adoptTime").toString(),
						maps.get("drawType").toString());
				personList.add(exportClue);
			}
			ExcelUtiles.exportExcel(personList, "市中公安线索管理", "市中公安线索管理", ExportClue.class, "市中公安线索管理.xls", response);
		} catch (Exception e) {
			logger.error(e.getMessage());
		}
		return null;
	}

5.导出示例