java实现多sheet页数据写入或导出

多sheet页数据写入

一：对象实体

package com.mmh.test;

import com.lishiots.cloud.datacenter.kernel.annotation.ExcelExpField;
import io.swagger.annotations.ApiModel;
import lombok.Data;

import java.util.Date;

/**
 * @Description: 描述：测试bean实体对象
 * @Author mmh
 * @Date 2022/10/19 13:58
 */
@ApiModel("测试bean")
@Data
public class TestBean {
    @ExcelName(name = "名称")
    private String name;
    @ExcelName(name = "年龄")
    private int age;
    @ExcelName(name = "分数")
    private double score;
    @ExcelName(name = "是否及格")
    private boolean isPass;
    @ExcelName(name = "时间")
    private Date examDate;
}

@ExcelName 自定义的注解 在博主的 SpringBoot实现简单的Excel导入操作 里有说明

二：功能实现多sheet页数据写入

package com.mmh.test;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.mmh.annotation.ExcelExpField;

import java.lang.reflect.Field;
import java.util.List;

/**
 * @Description: 描述：多sheet页数据写入
 * @Author mmh
 * @Date 2022/10/19 13:58
 */
public class TestExcel {

    public static void main(String[] args) {
        // 设置参数
        List<TestBean> rows  = setTestValue();
        // 通过工具类创建writer并写入多sheet数据
        setSheet(rows,null,"一班成绩单（1）");
    }

     /**
     * 数据写入
     * @param rows 数据体
     * @param listSheetName 可空
     * @param tableName 不可为空
     * @param <T> 对象
     * @return
     */
    public static <T> void setSheet(List<T> rows,String[] listSheetName,String tableName){
        String firstSheetName = tableName;
        if (listSheetName != null && listSheetName.length > 0) {
            firstSheetName = listSheetName[0];
        }
        // 通过工具类创建writer
        ExcelWriter writer = new ExcelWriter("E:/" + tableName + DateTime.now().toString("yyyyMMddHHmmss") + ".xlsx",firstSheetName);
        // 写入到数据流
//        ExcelWriter writer = ExcelUtil.getWriter(true);
        if (!rows.isEmpty()) {
            String sheetName = firstSheetName;
            if (listSheetName !=null && listSheetName.length>0) {
                for (int i = 0;i < listSheetName.length;i++) {
                    if ( i > 0) {
                        sheetName = listSheetName[i];
                        writer.setSheet(listSheetName[i]);
                    }
                    setSheetChild(writer,rows,sheetName);
                }
            } else {
                setSheetChild(writer,rows,sheetName);
            }
        }
        // HttpServletResponse response 写入到数据流
//        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
//        response.setHeader("Content-Disposition","attachment;filename=文件名.xlsx");
//        OutputStream out = new BufferedOutputStream(response.getOutputStream());
//        writer.flush(out, true);
        // 关闭writer，释放内存
        writer.close();
//        IoUtil.close(out);
    }

    /**
     * 设置表头
     * @param writer
     * @param rows 数据集合
     * @param sheetName sheet名称
     * @param <T>
     */
    private static <T> void setSheetChild(ExcelWriter writer,List<T> rows,String sheetName){
        Field[] fields = rows.get(0).getClass().getDeclaredFields();
        for (Field field : fields) {
            if (field.isAnnotationPresent(ExcelExpField.class)) {
                String fieldName = field.getName();
                String title = field.getAnnotation(ExcelName.class).name();
                // 自定义标题别名
                writer.addHeaderAlias(fieldName, title);
            }
        }
        // 默认的，未添加alias的属性也会写出，如果想只写出加了别名的字段，可以调用此方法排除之
        writer.setOnlyAlias(true);
        // 合并单元格后的标题行，使用默认标题样式
        writer.merge(fields.length-1, sheetName);
        // 一次性写出内容，使用默认样式，强制输出标题
        writer.write(rows, true);
    }

    public static List<TestBean> setTestValue (){
        TestBean bean1 = new TestBean();
        bean1.setName("张三");
        bean1.setAge(22);
        bean1.setPass(true);
        bean1.setScore(66.30);
        bean1.setExamDate(DateUtil.date());
        TestBean bean2 = new TestBean();
        bean2.setName("李四");
        bean2.setAge(28);
        bean2.setPass(false);
        bean2.setScore(38.50);
        bean2.setExamDate(DateUtil.date());
        return CollUtil.newArrayList(bean1, bean2);
    }
}