java easyPoi 导出excel设置数字类型 easypoi导出excel模板

1，简介

easypoi功能如同名字easy,主打的功能就是容易,让一个没见接触过poi的人员就可以方便的写出Excel导出,Excel模板导出,Excel导入,Word模板导出,通过简单的注解和模板语言(熟悉的表达式语法),完成以前复杂的写法

官网：http://doc.wupaas.com/docs/easypoi

测试代码地址：easypoi导出excel测试地址

2，添加easypoi依赖

<dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>4.3.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>4.3.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>4.3.0</version>
        </dependency>

注：版本尽量保存高点，版本过低可能出现图片导不出，表头无法自动合并等问题。

3，实战测试

准备工具：在游览器导出进行测试

@Component
public class StreamServiceImpl {

    /**
     * 获取导出流
     * @param response
     * @param fileName 导出文件名
     * @return
     */
    public ServletOutputStream getOutputStream(HttpServletResponse response,String fileName) throws IOException {

        fileName = URLEncoder.encode(fileName+".xlsx", "UTF-8");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        //  客户端不缓存
        response.addHeader("Pragma", "no-cache");
        response.addHeader("Cache-Control", "no-cache");

        ServletOutputStream stream = response.getOutputStream();

        return stream;
    }
}

3.1，基于注解导出excel

3.1.1，主要注解

@Excel

属性	类型	默认值	功能
name	String	null	列名,支持name_id
needMerge	boolean	fasle	是否需要纵向合并单元格(用于含有list中,单个的单元格,合并list创建的多个row)
orderNum	String	“0”	列的排序,支持name_id
replace	String[]	{}	值得替换 导出是{a_id,b_id} 导入反过来
savePath	String	“upload”	导入文件保存路径,如果是图片可以填写,默认是upload/className/ IconEntity这个类对应的就是upload/Icon/
type	int	1	导出类型 1 是文本 2 是图片,3 是函数,10 是数字 默认是文本
width	double	10	列宽
height	double	10	列高,后期打算统一使用@ExcelTarget的height,这个会被废弃,注意
isStatistics	boolean	fasle	自动统计数据,在追加一行统计,把所有数据都和输出[这个处理会吞没异常,请注意这一点]
isHyperlink	boolean	false	超链接,如果是需要实现接口返回对象
isImportField	boolean	true	校验字段,看看这个字段是不是导入的Excel中有,如果没有说明是错误的Excel,读取失败,支持name_id
exportFormat	String	“”	导出的时间格式,以这个是否为空来判断是否需要格式化日期
importFormat	String	“”	导入的时间格式,以这个是否为空来判断是否需要格式化日期
format	String	“”	时间格式,相当于同时设置了exportFormat 和 importFormat
databaseFormat	String	“yyyyMMddHHmmss”	导出时间设置,如果字段是Date类型则不需要设置 数据库如果是string 类型,这个需要设置这个数据库格式,用以转换时间格式输出
numFormat	String	“”	数字格式化,参数是Pattern,使用的对象是DecimalFormat
imageType	int	1	导出类型 1 从file读取 2 是从数据库中读取 默认是文件 同样导入也是一样的
suffix	String	“”	文字后缀,如% 90 变成90%
isWrap	boolean	true	是否换行 即支持\n
mergeRely	int[]	{}	合并单元格依赖关系,比如第二列合并是基于第一列 则{0}就可以了
mergeVertical	boolean	fasle	纵向合并内容相同的单元格
fixedIndex	int	-1	对应excel的列,忽略名字
isColumnHidden	boolean	false	导出隐藏列

@ExcelCollection

一对多的集合注解,用以标记集合是否被数据以及集合的整体排序

属性	类型	默认值	功能
id	String	null	定义ID
name	String	null	定义集合列名,支持nanm_id
orderNum	int	0	排序,支持name_id
type	Class<?>	ArrayList.class	导入时创建对象使用

@ExcelEntity

标记是不是导出excel 标记为实体类,一遍是一个内部属性类,标记是否继续穿透,可以自定义内部id

属性	类型	默认值	功能
id	String	null	定义ID

@ExcelIgnore

忽略这个属性,多使用需循环引用中,

@ExcelTarget

限定一个到处实体的注解,以及一些通用设置,作用于最外面的实体

3.1.2，测试

实体类：

@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
@ExcelTarget("courseEntity")
public class CourseEntity {
    /** 主键 */
    private String        id;
    /** 课程名称 */
    @Excel(name = "课程名称", orderNum = "1", width = 25,needMerge = true)
    private String        name;
    /** 老师主键 */
    @ExcelEntity(id = "absent")
    private TeacherEntity mathTeacher;

    @Excel(name = "logo",type = 2,imageType = 1,needMerge = true)
    private String logoUrl;

    @ExcelCollection(name = "学生", orderNum = "4")
    private List<StudentEntity> students;
}


@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
@ExcelTarget("studentEntity")
public class StudentEntity {
    /**
     * id
     */
    private String id;
    /**
     * 学生姓名
     */
    @Excel(name = "学生姓名", height = 20, width = 30, isImportField = "true_st")
    private String name;
    /**
     * 学生性别
     */
    @Excel(name = "学生性别", replace = { "男_1", "女_2" }, suffix = "生", isImportField = "true_st")
    private int sex;

    @Excel(name = "出生日期", databaseFormat = "yyyyMMddHHmmss", format = "yyyy-MM-dd", isImportField = "true_st", width = 20)
    private Date birthday;

    @Excel(name = "进校日期", databaseFormat = "yyyyMMddHHmmss", format = "yyyy-MM-dd")
    private Date registrationDate;
}


@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
@ExcelTarget("teacherEntity")
public class TeacherEntity {
    private String id;
    /** name */
    @Excel(name = "主讲老师_major,代课老师_absent", orderNum = "1", isImportField = "true_major,true_absent",needMerge = true)
    private String name;
}

导出代码：

/**
 * 基于注解导出excel
 */
@RestController
@RequestMapping("/annotation")
public class AnnotationController {
    @Autowired
    private StreamServiceImpl streamService;

    @GetMapping("/exportExcel")
    public void exportExcel(HttpServletResponse response) throws IOException {
        //填充数据
        List<CourseEntity> list = new ArrayList<>();

        for (int i = 0; i < 3; i++) {
            CourseEntity course = new CourseEntity();
            course.setId(""+i);
            course.setName("张三-"+i);
            course.setLogoUrl("http://www.zhcgdsj.suzhou.gov.cn/others//1677053432441.jpeg");
            course.setMathTeacher(new TeacherEntity("1","法外狂徒"));

            List<StudentEntity> studentEntityList = new ArrayList<>();
            for (int j = 0; j < 2; j++) {
                StudentEntity student = new StudentEntity();
                student.setId(""+j);
                student.setName("李四"+j);
                student.setSex(0);
                student.setBirthday(new Date());
                student.setRegistrationDate(new Date());
                studentEntityList.add(student);
            }
            course.setStudents(studentEntityList);
            list.add(course);
        }

        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("基于注解导出excel", "测试"), CourseEntity.class, list);
        ServletOutputStream stream = streamService.getOutputStream(response, "基于注解导出excel");
        workbook.write(stream);
        stream.flush();
        stream.close();
        workbook.close();
    }
}

测试结果：

3.2，基于模板导出excel

excel模板：

导出代码：

@GetMapping("/exportExcel")
    public void exportExcel(HttpServletResponse response) throws IOException {
        
        TemplateExportParams params = new TemplateExportParams(
                "excel/templates.xls");
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("date", "2014-12-25");
        map.put("money", 2000000.00);
        map.put("upperMoney", "贰佰万");
        map.put("company", "执笔潜行科技有限公司");
        map.put("bureau", "财政局");
        map.put("person", "JueYue");
        map.put("phone", "1879740****");
        List<Map<String, String>> listMap = new ArrayList<Map<String, String>>();
        for (int i = 0; i < 4; i++) {
            Map<String, String> lm = new HashMap<String, String>();
            lm.put("id", i + 1 + "");
            lm.put("zijin", i * 10000 + "");
            lm.put("bianma", "A001");
            lm.put("mingcheng", "设计");
            lm.put("xiangmumingcheng", "EasyPoi " + i + "期");
            lm.put("quancheng", "开源项目");
            lm.put("sqje", i * 10000 + "");
            lm.put("hdje", i * 10000 + "");

            listMap.add(lm);
        }
        map.put("maplist", listMap);

        Workbook workbook = ExcelExportUtil.exportExcel(params, map);

        ServletOutputStream stream = streamService.getOutputStream(response, "基于模板导出excel");
        workbook.write(stream);
        stream.flush();
        stream.close();
        workbook.close();
    }

测试结果：

3.3，基于自定义表头导出excel

3.3.1，使用代码实现

导出代码：

@GetMapping("/exportExcel")
    public void exportExcel(HttpServletResponse response) throws IOException {
        List<ExcelExportEntity> colList = new ArrayList<>();

        //基础信息
        ExcelExportEntity baseGroup = new ExcelExportEntity("基础信息", "base");
        List<ExcelExportEntity> baseList = new ArrayList<>();
        baseList.add(new ExcelExportEntity("序号","id"));
        baseList.add(new ExcelExportEntity("所属城区","areaName"));
        baseList.add(new ExcelExportEntity("所属街道","streetName"));
        baseList.add(new ExcelExportEntity("商铺名称","shopName"));
        baseGroup.setList(baseList);
        colList.add(baseGroup);


        //招牌信息
        for (int i = 1; i <= 3; i++) {
            ExcelExportEntity signGroup = new ExcelExportEntity("招牌信息"+i, "sign"+i);
            List<ExcelExportEntity> signList = new ArrayList<>();
            signList.add(new ExcelExportEntity("招牌内容","signContent"));
            signList.add(new ExcelExportEntity("招牌形式","signFormName"));
            signList.add(new ExcelExportEntity("安装时间","installTime"));
            signGroup.setList(signList);
            colList.add(signGroup);
        }


        //封装数据

        List<Map<String,Object>> list = new ArrayList<>();
            for (int i = 0; i < 3; i++) {
                Map<String,Object> dataMap = new HashMap<>();

                List<Map<String,Object>> baseDataList = new ArrayList<>();
                Map<String, Object> map = new HashMap<>();
                map.put("id",i+1);
                map.put("areaName", "上海"+i);
                map.put("streetName", "闵行"+i);
                map.put("shopName", "五福"+i);
                baseDataList.add(map);

                dataMap.put("base",baseDataList);

                //招牌信息
                    for (int j = 0; j < 3; j++) {
                        List<Map<String,Object>> mapList = new ArrayList<>();
                        Map<String,Object> signMap = new HashMap<>();
                        signMap.put("signFormName","自定义"+i);
                        signMap.put("installTime","2023-04-02");
                        signMap.put("signContent","无"+i);
                        mapList.add(signMap);
                        dataMap.put("sign"+(j+1),mapList);
                    }

                list.add(dataMap);

            }


        ExportParams exportParams = new ExportParams(null, "数据信息");
        exportParams.setType(ExcelType.XSSF);

        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, colList, list);

        ServletOutputStream stream = streamService.getOutputStream(response, "自定义表头导出excel");
        workbook.write(stream);
        stream.flush();
        stream.close();
        workbook.close();
    }

测试结果：

3.3.2，使用excel模板实现

excel模板：

 导出代码：

@GetMapping("/exportExcelForTemplates")
    public void exportExcelForTemplates(HttpServletResponse response) throws IOException {
        TemplateExportParams params = new TemplateExportParams(
                "excel/test.xlsx");
        params.setColForEach(true);
        Map<String, Object> map = new HashMap<String, Object>();

        //横向表头信息
        List<Map<String, Object>> listMap = new ArrayList<Map<String, Object>>();
        for (int i = 0; i < 3; i++) {
            Map<String,Object> headMap = new HashMap<>();
            headMap.put("sign","店招牌匾"+i);
            headMap.put("signContent","招牌内容");
            headMap.put("signFormName","招牌形式");
            headMap.put("installTime","安装时间");

            headMap.put("content","t.content"+i);
            headMap.put("formName","t.formName"+i);
            headMap.put("time","t.time"+i);

            listMap.add(headMap);
        }
        map.put("list",listMap);
        map.put("map", map);


        //数据信息
        List<Map<String,Object>> baseList = new ArrayList<>();
        for (int i = 0; i < 3; i++) {
            Map<String,Object> baseMap = new HashMap<>();
            baseMap.put("id", i);
            baseMap.put("area", "上海市"+i);
            baseMap.put("street", "闵行区"+i);
            baseMap.put("storeName", "上海松江大学城");

            baseMap.put("content0","内容0");
            baseMap.put("formName0","样式0");
            baseMap.put("time0","时间0");

            baseMap.put("content1","内容1");
            baseMap.put("formName1","样式1");
            baseMap.put("time1","时间1");

            baseMap.put("content2","内容2");
            baseMap.put("formName2","样式2");
            baseMap.put("time2","时间2");

            baseList.add(baseMap);
        }
        map.put("baseList",baseList);

        Workbook workbook = ExcelExportUtil.exportExcel(params, map);

        ServletOutputStream stream = streamService.getOutputStream(response, "自定义表头导出excel");
        workbook.write(stream);
        stream.flush();
        stream.close();
        workbook.close();

测试结果：