java - Excel生成

一时失言乱红尘 2023-10-07 16:56 13阅读 0赞

### 大数量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"));
        }
    
    
    }

![在这里插入图片描述][d5d1e2613bb24fa68a6ccb07559a24dc.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjA0ODkw_size_16_color_FFFFFF_t_70]

### 自适应列宽度 ###

链接: [Hutool工具类BigExcelWriter导出Excel调用autoSizeColumnAll()方法无法自适应列宽度的问题][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(推荐) ###

未完待续~

[d5d1e2613bb24fa68a6ccb07559a24dc.png]: https://img-blog.csdnimg.cn/d5d1e2613bb24fa68a6ccb07559a24dc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjA0ODkw_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/248a5b6945294aad9368083e374ca049.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjA0ODkw,size_16,color_FFFFFF,t_70
[Hutool_BigExcelWriter_Excel_autoSizeColumnAll]: https://blog.csdn.net/CW_SZDX/article/details/110236881