java导出动态excel

weixin_45357254

已于 2023-08-01 11:24:39 修改

阅读量1k

点赞数

文章标签： java excel 开发语言

于 2023-08-01 11:23:44 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_45357254/article/details/132038395

版权



import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.IOUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelExportUtil {

    private static final Logger log = LoggerFactory.getLogger(ExcelExportUtil.class);

    public static void main(String[] args) throws Exception {
        List<ImportantEntData> testData = new ArrayList<>();
        ImportantEntData importantEntData = new ImportantEntData();
        importantEntData.setEconomicType("外资企业");
        importantEntData.setIndustryId(798L);
        importantEntData.setIndustryName("计算机、通信和其他电子设备制造业");
        importantEntData.setRegion("火炬高新区");
        importantEntData.setUnnSocCrCd("91350200761741188P");
        importantEntData.setEntCode("761741188");
        importantEntData.setIndustryCode("39");
        importantEntData.setEntName("戴尔（厦门）有限公司");
        importantEntData.setEntNum("1");
        Children children = new Children();
        children.setCurrentPeriod("1");
        children.setSamePeriod("2");
        children.setIncreasePercentage("3");
        children.setIncreaseAmount("4");
        importantEntData.setDeficitNum(children);
        Children children1 = new Children();
        children1.setCurrentPeriod("5");
        children1.setSamePeriod("6");
        children1.setIncreasePercentage("7");
        children1.setIncreaseAmount("8");
        importantEntData.setAccountsReceivable(children1);
        Children children2 = new Children();
        children2.setCurrentPeriod("9");
        children2.setSamePeriod("10");
        children2.setIncreasePercentage("11");
        children2.setIncreaseAmount("12");
        importantEntData.setInventory(children2);
        testData.add(importantEntData);

        ImportantEntData importantEntData1 = new ImportantEntData();
        importantEntData1.setEconomicType("外资企业2");
        importantEntData1.setIndustryId(7982L);
        importantEntData1.setIndustryName("计算机、通信和其他电子设备制造业2");
        importantEntData1.setRegion("火炬高新区2");
        importantEntData1.setUnnSocCrCd("91350200761741188P2");
        importantEntData1.setEntCode("7617411882");
        importantEntData1.setIndustryCode("392");
        importantEntData1.setEntName("戴尔（厦门）有限公司2");
        importantEntData1.setEntNum("12");
        Children children11 = new Children();
        children11.setCurrentPeriod("12");
        children11.setSamePeriod("22");
        children11.setIncreasePercentage("32");
        children11.setIncreaseAmount("42");
        importantEntData1.setDeficitNum(children11);
        Children children22 = new Children();
        children22.setCurrentPeriod("52");
        children22.setSamePeriod("62");
        children22.setIncreasePercentage("72");
        children22.setIncreaseAmount("82");
        importantEntData1.setAccountsReceivable(children22);
        Children children33 = new Children();
        children33.setCurrentPeriod("92");
        children33.setSamePeriod("102");
        children33.setIncreasePercentage("112");
        children33.setIncreaseAmount("122");
        importantEntData1.setInventory(children33);
        testData.add(importantEntData1);


        List<String> nameList = new ArrayList<>();
        ImportantEntData temp = new ImportantEntData();
        Field[] declaredFields1 = temp.getClass().getDeclaredFields();
        for (Field field : declaredFields1) {
            if (field.isAnnotationPresent(Excel.class)) {
                String name = field.getDeclaredAnnotation(Excel.class).name();
                nameList.add(name);
            }
        }
        String aa = "{\"deficitNum\":{\"currentPeriod\":\"本期\",\"samePeriod\":\"同期\"},\"accountsReceivable\":{\"currentPeriod\":\"本期\",\"samePeriod\":\"同期\",\"increasePercentage\":\"比增%\",\"increaseAmount\":\"增长额\"},\"inventory\":{\"currentPeriod\":\"本期\",\"samePeriod\":\"同期\",\"increasePercentage\":\"比增%\",\"increaseAmount\":\"增长额\"}}";
        ImportantEntData params = JSONObject.parseObject(aa, ImportantEntData.class);

        String fileName = "测试1";
        String sheetName = "sheet-2";
        Workbook workBook = new HSSFWorkbook();
        export(params, testData, workBook, sheetName, Children.class);
        workBook.write(new FileOutputStream(fileName + ".xlsx"));
    }

    public static <T, U> void exportData(HttpServletResponse response,Object params, List<T> dataList, Workbook workbook, String sheetName, String fileName, Class<U> type) throws Exception {
        export(params, dataList, workbook, sheetName, Children.class);
        outExcelStream(response,workbook);
    }

    public static List<String> createHead(Object params, Workbook workbook, String sheetName) {
        //准备固定列表头数据
        List<String> nameList = new ArrayList<>();
        Field[] declaredFields1 = params.getClass().getDeclaredFields();
        for (Field field : declaredFields1) {
            if (field.isAnnotationPresent(Excel.class)) {
                String name = field.getDeclaredAnnotation(Excel.class).name();
                nameList.add(name);
            }
        }
        Sheet sheet = workbook.createSheet(sheetName);
        //表头占据2行
        Row headerRow = sheet.createRow(0);
        sheet.createRow(1);
        CellStyle headerCellStyle = workbook.createCellStyle();
        headerCellStyle.setAlignment(HorizontalAlignment.CENTER);
        headerCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //构造固定列表头
        for (int i = 0; i < nameList.size(); i++) {
            sheet.addMergedRegion(new CellRangeAddress(0, 1, i, i));
            Cell cell1 = headerRow.createCell(i);
            cell1.setCellValue(nameList.get(i));
            cell1.setCellStyle(headerCellStyle);
        }
        return nameList;
    }

    public static Field[] createChildHead(Object params, Workbook workbook, String sheetName, List<String> nameList) throws Exception {
        //准备合并列表头数据
        List<Map<String, Object>> paramList = new ArrayList<>();
        Field[] declaredFields = params.getClass().getDeclaredFields();
        for (int i = 0; i < declaredFields.length; i++) {
            Field field = declaredFields[i];
            field.setAccessible(true);
            Object o = field.get(params);
            boolean t = o instanceof Children;
            if (o != null && t) {
                Map<String, Object> m1 = new HashMap<>();
                m1.put("title", field.getDeclaredAnnotation(Tab.class).name());
                Children children = JSONObject.parseObject(JSON.toJSONString(o), Children.class);
                Field[] declaredFields2 = children.getClass().getDeclaredFields();
                List b = new ArrayList();
                for (Field fields : declaredFields2) {
                    fields.setAccessible(true);
                    Object o1 = fields.get(children);
                    if (o1 != null) {
                        String name = fields.getDeclaredAnnotation(Tab.class).name();
                        b.add(name);
                    }
                }
                m1.put("child", b);
                paramList.add(m1);
            }
        }
        Sheet sheet = workbook.getSheet(sheetName);
        CellStyle headerCellStyle = workbook.createCellStyle();
        headerCellStyle.setAlignment(HorizontalAlignment.CENTER);
        headerCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //构造合并列表头
        int length = 0;
        for (int i = 0; i < paramList.size(); i++) {
            Map<String, Object> map = paramList.get(i);
            for (Map.Entry<String, Object> entry : map.entrySet()) {
                Object value = entry.getValue();
                if (value instanceof String) {
                    Cell cell1 = sheet.getRow(0).createCell(nameList.size() + length);
                    cell1.setCellValue(entry.getValue().toString());
                    cell1.setCellStyle(headerCellStyle);
                } else {
                    JSONArray list = JSONArray.parseArray(JSONObject.toJSONString(value));
                    sheet.addMergedRegion(new CellRangeAddress(0, 0, nameList.size() + length, nameList.size() - 1 + list.size() + length));
                    for (int j = 0; j < list.size(); j++) {
                        Cell cell2 = sheet.getRow(1).createCell(nameList.size() + length + j);
                        cell2.setCellValue(list.get(j).toString());
                        cell2.setCellStyle(headerCellStyle);
                    }
                    length += list.size();
                }
            }
        }
        return declaredFields;
    }

    public static <T, U> void export(Object params, List<T> dataList, Workbook workbook, String sheetName, Class<U> type) throws Exception {
        List<String> nameList = createHead(params, workbook, sheetName);
        Field[] declaredFields = createChildHead(params, workbook, sheetName, nameList);
        Sheet sheet = workbook.getSheet(sheetName);
        //构造数据
        for (int i = 0; i < dataList.size(); i++) {
            Row dataRow = sheet.createRow(i + 2);
            T obj = dataList.get(i);
            int tab = 0;
            for (int j = 0; j < declaredFields.length; j++) {
                Field field = declaredFields[j];
                field.setAccessible(true);
                Object o = field.get(obj);
                boolean t = o instanceof Children;
                if (o != null) {
                    if (!t) {
                        Cell cell1 = dataRow.createCell(j);
                        cell1.setCellValue(o == null ? "" : o.toString());
                    } else {
                        Object o2 = field.get(params);//应收和库存
                        if (o2 != null) {
                            U children = com.alibaba.fastjson.JSONObject.parseObject(com.alibaba.fastjson.JSON.toJSONString(o), type);
//                            T children = JSONObject.parseObject(JSON.toJSONString(o), T.class);//应收和库存
                            Field[] declaredFields2 = children.getClass().getDeclaredFields();
                            int addTime = 0;
                            for (int k = 0; k < declaredFields2.length; k++) {//长度4
                                declaredFields2[k].setAccessible(true);
                                Object o1 = declaredFields2[k].get(children);
                                Object o3 = declaredFields2[k].get(o2);
                                if (o3 != null) {
                                    if (o1 != null) {
                                        Cell cell1 = dataRow.createCell(nameList.size() + tab + addTime);
                                        cell1.setCellValue(o1.toString());
                                    }
                                    addTime++;
                                }
                            }
                            tab += addTime;
                        }
                    }
                }
            }
        }
    }

    public <T> boolean isInstanceOfClass(Object obj, Class<T> clazz) {
        return clazz.isInstance(obj);
    }

    public <T> boolean isInstanceOfArray(Object obj, Class<T[]> arrayClass) {
        return arrayClass.isInstance(obj);
    }

    public static void outExcelStream(HttpServletResponse response, Workbook workBook) {
        // 导出响应流，设置响应头信息
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        try {
            // 将工作簿写入响应输出流中
            workBook.write(response.getOutputStream());
        } catch (Exception e) {
            log.error("导出Excel异常{}", e.getMessage());
        } finally {
            IOUtils.closeQuietly(workBook);
        }
    }
}



import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
import java.math.BigDecimal;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;

/**
 * 自定义导出Excel数据注解
 *
 */
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface Excel
{
    /**
     * 导出时在excel中排序
     */
    public int sort() default Integer.MAX_VALUE;

    /**
     * 导出到Excel中的名字.
     */
    public String name() default "";

    /**
     * 日期格式, 如: yyyy-MM-dd
     */
    public String dateFormat() default "";

    /**
     * 读取内容转表达式 (如: 0=男,1=女,2=未知)
     */
    public String readConverterExp() default "";

    /**
     * 分隔符，读取字符串组内容
     */
    public String separator() default ",";

    /**
     * BigDecimal 精度 默认:-1(默认不开启BigDecimal格式化)
     */
    public int scale() default -1;

    /**
     * BigDecimal 舍入规则 默认:BigDecimal.ROUND_HALF_EVEN
     */
    public int roundingMode() default BigDecimal.ROUND_HALF_EVEN;

    /**
     * 导出时在excel中每个列的高度 单位为字符
     */
    public double height() default 14;

    /**
     * 导出时在excel中每个列的宽 单位为字符
     */
    public double width() default 16;

    /**
     * 文字后缀,如% 90 变成90%
     */
    public String suffix() default "";

    /**
     * 当值为空时,字段的默认值
     */
    public String defaultValue() default "";

    /**
     * 提示信息
     */
    public String prompt() default "";

    /**
     * 设置只能选择不能输入的列内容.
     */
    public String[] combo() default {};

    /**
     * 是否需要纵向合并单元格,应对需求:含有list集合单元格)
     */
    public boolean needMerge() default false;

    /**
     * 是否导出数据,应对需求:有时我们需要导出一份模板,这是标题需要但内容需要用户手工填写.
     */
    public boolean isExport() default true;

    /**
     * 另一个类中的属性名称,支持多级获取,以小数点隔开
     */
    public String targetAttr() default "";

    /**
     * 是否自动统计数据,在最后追加一行统计数据总和
     */
    public boolean isStatistics() default false;

    /**
     * 导出类型（0数字 1字符串）
     */
    public ColumnType cellType() default ColumnType.STRING;

    /**
     * 导出列头背景色
     */
    public IndexedColors headerBackgroundColor() default IndexedColors.GREY_50_PERCENT;

    /**
     * 导出列头字体颜色
     */
    public IndexedColors headerColor() default IndexedColors.WHITE;

    /**
     * 导出单元格背景色
     */
    public IndexedColors backgroundColor() default IndexedColors.WHITE;

    /**
     * 导出单元格字体颜色
     */
    public IndexedColors color() default IndexedColors.BLACK;

    /**
     * 导出字段对齐方式
     */
    public HorizontalAlignment align() default HorizontalAlignment.CENTER;

    /**
     * 自定义数据处理器
     */
    public Class<?> handler() default ExcelHandlerAdapter.class;

    /**
     * 自定义数据处理器参数
     */
    public String[] args() default {};

    /**
     * 字段类型（0：导出导入；1：仅导出；2：仅导入）
     */
    Type type() default Type.ALL;

    public enum Type
    {
        ALL(0), EXPORT(1), IMPORT(2);
        private final int value;

        Type(int value)
        {
            this.value = value;
        }

        public int value()
        {
            return this.value;
        }
    }

    public enum ColumnType
    {
        NUMERIC(0), STRING(1), IMAGE(2);
        private final int value;

        ColumnType(int value)
        {
            this.value = value;
        }

        public int value()
        {
            return this.value;
        }
    }
}



import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * Excel注解集
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface Tab
{
    public String name() default "";
}