投稿
  1. 极客之音首页
  2. 技术随笔

java – Excel生成

飞熊 • • 技术随笔 • 阅读 247

人生之路坎坎坷坷，跌跌撞撞在所难免。但是，不论跌了多少次，你都必须坚强勇敢地站起来。任何时候，无论你面临着生命的何等困惑抑或经受着多少挫折，无论道路多艰难，希望变得如何渺茫，请你不要绝望，再试一次，坚持到底，成功终将属于勇不言败的你。

导读：本篇文章讲解 java – Excel生成，希望对大家有帮助，欢迎收藏，转发！站点地址：www.bmabk.com，来源：原文

大数量Excel生成

这里使用hutool 提供的Excel生成方法

        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.3.7</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>

import cn.hutool.core.io.IoUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * @author zhou
 */
public class MyExcelUtil {

    /**
     * 通过反射去遍历对象属性 构建List<String>
     * @param e
     * @return List<String>
     */
    public static List<String> reflectBuild(Object e){
        Class cls = e.getClass();
        Field[] fields = cls.getDeclaredFields();
        List<String> row = new ArrayList<>();
        for (int i = 0; i < fields.length; i++) {
            Field f = fields[i];
            f.setAccessible(true);
            try {
                row.add((String) f.get(e));
            } catch (IllegalAccessException illegalAccessException) {
                illegalAccessException.printStackTrace();
            }
        }
        return row;
    }

    /**
     * 导出Excel
     *
     * @param title    String title = "表头";
     * @param headRow  String headRow = "属性1,属性2,属性3;
     * @param rows     内容集合 内容属性需跟headRow数量相同和对应
     * @param response 响应流
     * @param filename 文件名称带后缀
     */
    public static void ExcelExportIO(String title, String headRow, List<List<String>> rows,
                              HttpServletResponse response, String filename) {
        //导出清单
        String[] split = headRow.split(",");
        ExcelWriter writer = ExcelUtil.getBigWriter();
        //设置标题
        if (title != null) {
            writer.merge(split.length - 1, title);
        }
        //设置表头
        writer.writeHeadRow(Arrays.asList(split));
        //设置表单
        writer.write(rows, false);
        try (ServletOutputStream out = response.getOutputStream()) {
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8"));
            writer.flush(out, true);
            writer.close();
            IoUtil.close(out);
        } catch (IOException e) {
            throw new RuntimeException("导出Excel失败");
        }
    }
}

import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.List;

/**
 * @author zhou
 */
@RestController
public class ExcelController {

    public List<CcZgQdResult> getData() {
        List<CcZgQdResult> list = new ArrayList<>();
        for (int i = 0; i < 2000; i++) {
            CcZgQdResult ccZgQdResult = new CcZgQdResult();
            ccZgQdResult.setName4("市级行政区" + i);
            ccZgQdResult.setName6("县级行政区" + i);
            ccZgQdResult.setName9("乡镇行政区" + i);
            ccZgQdResult.setJcbh("监测编号" + i);
            list.add(ccZgQdResult);
        }
        System.out.println("======生成模拟数据结束=========");
        return list;
    }

    // http://localhost:8080/excel
    @GetMapping("/excel")
    public void barCharts(HttpServletResponse response) {

        String title = "查处整改清单";
        String headRow = "市级行政区,县级行政区,乡镇行政区,监测编号,监测面积,下发批次,下发时间,市级已复核,当前查处整改情况";
        String filename = "查处整改清单.xls";
        List<CcZgQdResult> list = getData();


        // 放在要检测的代码段前，取开始前的时间戳
        Long startTime = System.currentTimeMillis();
        System.out.println("开始计时========");

        List<List<String>> rows = new ArrayList<>();
        list.forEach(t -> rows.add(MyExcelUtil.reflectBuild(t)));
        MyExcelUtil.ExcelExportIO(title, headRow, rows, response, filename);

        // 计算并打印耗时
        Long tempTime = (System.currentTimeMillis() - startTime);
        System.out.println("生成excel的数据数量为："+ rows.size() + "条，花费时间：" +
                (((tempTime / 86400000) > 0) ? ((tempTime / 86400000) + "d") : "") +
                ((((tempTime / 86400000) > 0) || ((tempTime % 86400000 / 3600000) > 0)) ? ((tempTime % 86400000 / 3600000) + "h") : ("")) +
                ((((tempTime / 3600000) > 0) || ((tempTime % 3600000 / 60000) > 0)) ? ((tempTime % 3600000 / 60000) + "m") : ("")) +
                ((((tempTime / 60000) > 0) || ((tempTime % 60000 / 1000) > 0)) ? ((tempTime % 60000 / 1000) + "s") : ("")) +
                ((tempTime % 1000) + "ms"));
    }


}

在这里插入图片描述
在这里插入图片描述

自适应列宽度

链接: Hutool工具类BigExcelWriter导出Excel调用autoSizeColumnAll()方法无法自适应列宽度的问题.

import cn.hutool.core.exceptions.DependencyException;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.poi.excel.BigExcelWriter;
import org.apache.poi.xssf.streaming.SXSSFSheet;

public class MyExcelWriter extends BigExcelWriter {

    public static MyExcelWriter getBigWriter() {
        try {
            return new MyExcelWriter();
        } catch (NoClassDefFoundError var1) {
            throw new DependencyException(ObjectUtil.defaultIfNull(var1.getCause(), var1), "You need to add dependency of 'poi-ooxml' to your project, and version >= 4.1.2");
        }
    }

    @Override
    public BigExcelWriter autoSizeColumnAll() {
        final SXSSFSheet sheet = (SXSSFSheet) this.sheet;
        sheet.trackAllColumnsForAutoSizing();
        super.autoSizeColumnAll();
        for (int i = 0; i < sheet.getRow(sheet.getLastRowNum()).getPhysicalNumberOfCells(); i++) {
            int colWidth = sheet.getColumnWidth(i) * 2;
            if (colWidth < 255 * 256) {
                sheet.setColumnWidth(i, Math.max(colWidth, 3000));
            } else {
                sheet.setColumnWidth(i, 6000);
            }
        }
        sheet.untrackAllColumnsForAutoSizing();
        return this;
    }

}


import cn.hutool.poi.excel.ExcelWriter;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * @author zhou
 */
public class MyExcelUtil {

    /**
     * 通过反射去遍历对象属性 构建List<String>
     * @param e
     * @return List<String>
     */
    public static List<String> reflectBuild(Object e){
        Class cls = e.getClass();
        Field[] fields = cls.getDeclaredFields();
        List<String> row = new ArrayList<>();
        for (int i = 0; i < fields.length; i++) {
            Field f = fields[i];
            f.setAccessible(true);
            try {
                row.add((String) f.get(e));
            } catch (IllegalAccessException illegalAccessException) {
                illegalAccessException.printStackTrace();
            }
        }
        return row;
    }

    /**
     * 导出Excel
     *
     * @param title    String title = "表头";
     * @param headRow  String headRow = "属性1,属性2,属性3;
     * @param rows     内容集合 内容属性需跟headRow数量相同和对应
     * @param response 响应流
     * @param filename 文件名称带后缀
     */
    public static void ExcelExportIO(String title, String headRow, List<List<String>> rows,
                                     HttpServletResponse response, String filename) {
        //导出清单
        String[] split = headRow.split(",");
        ExcelWriter writer = MyExcelWriter.getBigWriter();
        //设置标题
        if (title != null) {
            writer.merge(split.length - 1, title);
        }
        //设置表头
        writer.writeHeadRow(Arrays.asList(split));
        //设置表单
        writer.write(rows, false);
        // 设置所有列为自动宽度，不考虑合并单元格
        writer.autoSizeColumnAll();
        try (ServletOutputStream out = response.getOutputStream()) {
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8"));
            //response.setHeader("Content-Length", fileLength); 适合文件下载进度的计算
            writer.flush(out, true);
        } catch (IOException e) {
            throw new RuntimeException("导出Excel失败");
        }finally {
            writer.close();
        }
    }
}

改造一下代码

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

/**
 * hutool导出excle字段注解
 */
@Target({ElementType.TYPE,ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
public @interface DcExcelField {

    /**
     * 导出的标题名称
     * @return
     */
    String dcTitle() default "";

    /**
     * 标题的位置
     * @return
     */
    int index() default 0;

}

import lombok.Data;

@Data
public class Fields {

    /**
     * 属性值
     */
    private  String fieldKey;

    /**
     * 标题名
     */
    private  String fieldName;

    /**
     * 位置角标
     */
    private int index;
    

}

import java.io.Serializable;

import com.zm.util.DcExcelField;
import lombok.Data;
import lombok.EqualsAndHashCode;

@Data
public class UserTestEntity implements Serializable {

    private static final long serialVersionUID = 1L;

    @DcExcelField(dcTitle = "系统ID", index = 1)
    private String id;

    @DcExcelField(dcTitle = "名称1", index = 2)
    private String name1;

    @DcExcelField(dcTitle = "名称2", index = 3)
    private String name2;

    @DcExcelField(dcTitle = "名称3", index = 4)
    private String name3;

    @DcExcelField(dcTitle = "名称4", index = 5)
    private String name4;

    @DcExcelField(dcTitle = "名称5", index = 6)
    private String name5;

    @DcExcelField(dcTitle = "名称6", index = 7)
    private String name6;

    // 8 9 换了位置
    @DcExcelField(dcTitle = "名称7", index = 9)
    private String name7;

    @DcExcelField(dcTitle = "名称8", index = 8)
    private String name8;

    @DcExcelField(dcTitle = "名称9", index = 10)
    private String name9;


}

import cn.hutool.core.io.IoUtil;
import cn.hutool.poi.excel.ExcelWriter;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.List;
import java.util.stream.Collectors;

public class MyExcelUtil {

    public static List<Fields> reflectDcExcelField(Class cls) {
        List<Fields> list = new ArrayList<>();
        Field[] fields = cls.getDeclaredFields();
        for (int i = 0; i < fields.length; i++) {
            Field f = fields[i];
            f.setAccessible(true);
            DcExcelField annotation = f.getAnnotation(DcExcelField.class);
            if (annotation != null) {
                Fields obj = new Fields();
                obj.setFieldKey(f.getName());
                obj.setFieldName(annotation.dcTitle());
                obj.setIndex(annotation.index());
                list.add(obj);
            }
        }
        // 排个序
        return list.stream().sorted(Comparator.comparing(Fields::getIndex))
                .collect(Collectors.toList());
    }

    /**
     * 导出Excel-javabean
     *
     * @param title    String title = "表头";
     * @param cls
     * @param rows     内容集合 内容属性需跟headRow数量相同和对应
     * @param response 响应流
     * @param filename 文件名称带后缀
     */
    public static void ExcelExportIO(String title, Class cls, List<?> rows,
                                     HttpServletResponse response, String filename) {
        List<Fields> fields = reflectDcExcelField(cls);
        String[] split = fields.stream().map(Fields::getFieldKey).toArray(String[]::new);
        ExcelWriter writer = MyExcelWriter.getBigWriter();
        //设置标题
        if (title != null) {
            writer.merge(split.length - 1, title);
        }
        //自定义表头标题别名
        for (Fields field : fields) {
            writer.addHeaderAlias(field.getFieldKey(), field.getFieldName());
        }
        //设置表单
        writer.write(rows, true);
        writer.autoSizeColumnAll();
        try (ServletOutputStream out = response.getOutputStream()) {
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8"));
            writer.flush(out, true);
            writer.close();
            IoUtil.close(out);
        } catch (IOException e) {
            throw new RuntimeException("导出Excel失败");
        }
    }


}

// http://localhost:8080/excel
    @GetMapping("/excel")
    public void barCharts(HttpServletResponse response) {
        String title = "某某某清单";
        String filename = title + ".xls";
        List<UserTestEntity> rows = getData();
        // 放在要检测的代码段前，取开始前的时间戳
        long startTime = System.currentTimeMillis();
        log.info("开始计时========");
        MyExcelUtil.ExcelExportIO(title, UserTestEntity.class, rows, response, filename);
        // 计算并打印耗时
        long tempTime = (System.currentTimeMillis() - startTime);
        String endTime = (((tempTime / 86400000) > 0) ? ((tempTime / 86400000) + "d") : "") +
                ((((tempTime / 86400000) > 0) || ((tempTime % 86400000 / 3600000) > 0)) ? ((tempTime % 86400000 / 3600000) + "h") : ("")) +
                ((((tempTime / 3600000) > 0) || ((tempTime % 3600000 / 60000) > 0)) ? ((tempTime % 3600000 / 60000) + "m") : ("")) +
                ((((tempTime / 60000) > 0) || ((tempTime % 60000 / 1000) > 0)) ? ((tempTime % 60000 / 1000) + "s") : ("")) +
                ((tempTime % 1000) + "ms");
        log.info("生成excel的数据数量为：{} 条，花费时间：{}", rows.size(), endTime);
    }

使用easyExcel实现导出excel(推荐)

未完待续~

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/133920.html

赞 (0)
飞熊的头像飞熊bm
0 0

相关推荐

发表回复

登录后才能评论
极客之音——专业性很强的中文编程技术网站，欢迎收藏到浏览器，订阅我们！