hutool导出导出excel中文自适应列宽+反射+自定义注解获取表头

导出excel

参考:https://blog.csdn.net/sunnyzyq/article/details/121994504

jra包

<!-- hutool工具包 -->
<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
	<version>5.4.1</version>
</dependency>

<!-- Excel包 -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>

1.ExcelByHuTools 工具代码

package com.user.service.dguser.uilt.excel;

import cn.hutool.core.io.IoUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import cn.hutool.poi.excel.StyleSet;
import com.user.service.dguser.config.ExportField;
import com.user.service.dguser.uilt.RefUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.SheetUtil;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * @author dgzhang
 * @date 2022/11/23 11:50
 */
@Slf4j
public class ExcelByHuTools {


    public static <T> ExcelWriter exportExcelByHuTools(Class<T> pojo,
                                                       List<T> data, String sheetName) throws InstantiationException, IllegalAccessException {
        Map<String, String> headers = getFieldAnnotation(pojo);
        return exportExcelByHuTools(headers, data, sheetName);
    }

    /**
     * 导出文件
     *
     * @param sheetName
     * @param headers   表头
     * @param data      数据
     * @param <T>
     */
    public static <T> ExcelWriter exportExcelByHuTools(Map<String, String> headers,
                                                       List<T> data, String sheetName) {

        long startTime = System.currentTimeMillis();

        //通过工具类创建writer
        try {
            //数据量特别大时，使用BigExcelWriter对象，可以避免内存溢出
            ExcelWriter writer = ExcelUtil.getBigWriter();
            //设置sheet的名称
            writer.renameSheet(sheetName);
            //设置表头的名称， 这个添加顺序和导出顺序相同
            headers.forEach(writer::addHeaderAlias);

            //写入数据
            writer.write(data, true);

            //表头设置字体
            CellStyle headCellStyle = writer.getHeadCellStyle();
            Font font = createFont(writer, true, false, "宋体", 18);
            headCellStyle.setFont(font);

            //普通单元格，全局样式设置
            Font font1 = createFont(writer, false, false, "宋体", 14);
            setBaseGlobalStyle(writer, font1);

            //调整自适应宽度
            int columnCount = writer.getColumnCount();
            for (int i = 0; i < columnCount; ++i) {
                double width = SheetUtil.getColumnWidth(writer.getSheet(), i, false);
                if (width != -1.0D) {
                    width *= 256.0D;
                    //此处可以适当调整，调整列空白处宽度
                    width += 220D;
                    writer.setColumnWidth(i, Math.toIntExact(Math.round(width / 256D)));
                }
            }
            log.info("写入记录耗时：[{}]ms", System.currentTimeMillis() - startTime);
            return writer;
        } catch (Exception e) {
            //如果导出异常，则生成一个空的文件
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 设置基础字体样式
     *
     * @param writer
     * @param bold     是否粗体
     * @param italic
     * @param fontName 字体名称
     * @param fontSize 字体大小
     * @return
     */
    public static Font createFont(ExcelWriter writer, boolean bold, boolean italic, String fontName, int fontSize) {
        Font font = writer.getWorkbook().createFont();
        //设置字体名称 宋体 / 微软雅黑 /等
        font.setFontName(fontName);
        //设置是否斜体
        font.setItalic(italic);
        //设置字体大小 以磅为单位
        font.setFontHeightInPoints((short) fontSize);
        //设置是否加粗
        font.setBold(bold);
        return font;
    }

    /**
     * 方法描述: 全局基础样式设置
     * 默认 全局水平居中+垂直居中
     * 默认 自动换行
     * 默认单元格边框颜色为黑色，细线条
     * 默认背景颜色为白色
     */
    public static StyleSet setBaseGlobalStyle(ExcelWriter writer, Font font) {
        //全局样式设置
        StyleSet styleSet = writer.getStyleSet();
        //设置全局文本居中
        styleSet.setAlign(HorizontalAlignment.CENTER, VerticalAlignment.CENTER);
        //设置全局字体样式
        styleSet.setFont(font, true);
        //设置背景颜色 第二个参数表示是否将样式应用到头部
        styleSet.setBackgroundColor(IndexedColors.WHITE, false);
        //设置自动换行 当文本长于单元格宽度是否换行
        styleSet.setWrapText();
        // 设置全局边框样式
        styleSet.setBorder(BorderStyle.THIN, IndexedColors.BLACK);
        return styleSet;
    }

    //导出文档
    public static void export(HttpServletRequest request, HttpServletResponse response, ExcelWriter writer, String filename) throws IOException {
        setResponseHeader(request, response, filename);
        ServletOutputStream out = response.getOutputStream();
        writer.flush(out, true);
        writer.close();
        IoUtil.close(out);
    }

    //发送响应流方法
    public static void setResponseHeader(HttpServletRequest request, HttpServletResponse response, String fileName) throws UnsupportedEncodingException {
        response.setContentType("application/octet-stream;charset=UTF-8");
        String finalFileName = fileName;
        String userAgent = request.getHeader("user-agent");
        if (userAgent != null && userAgent.indexOf("Firefox") >= 0 || userAgent.indexOf("Chrome") >= 0 || userAgent.indexOf("Safari") >= 0) {
            finalFileName = new String((fileName).getBytes(), "ISO8859-1");
        } else {
            finalFileName = URLEncoder.encode(fileName, "UTF8"); //其他浏览器
        }
        response.setHeader("Content-Disposition", "attachment;filename=".concat(finalFileName));
        response.addHeader("Pargam", "no-cache");
        response.addHeader("Cache-Control", "no-cache");
    }

    /**
     * description 获取实体的所有字段，和对应注解值
     *
     * @param pojo 实体
     * @author dgzhang
     * @version 1.0
     * @date 2021/3/24 15:47
     */
    public static Map<String, String> getFieldAnnotation(Class<?> pojo) throws IllegalAccessException, InstantiationException {
        Object object = pojo.newInstance();
        Field[] fields = object.getClass().getDeclaredFields();
        Map<String, String> resultMap = new LinkedHashMap<>();
        for (Field field : fields) {
            // 是否引用ApiModelProperty注解
            boolean bool = field.isAnnotationPresent(ExportField.class);
            if (bool) {
                String value = field.getAnnotation(ExportField.class).value();
                resultMap.put(field.getName(), value);
            }
        }
        return resultMap;
    }
}

2.自定义注解

package com.user.service.dguser.config;

import java.lang.annotation.*;

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
@Documented
public @interface ExportField {

    String value() default "";

    int type() default 0;

}

3.实体类

package com.user.service.dguser.model;

import com.user.service.dguser.config.ExportField;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.Date;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class User2 {
    @ExportField(value = "学号")
    private String deptId;
    @ExportField(value = "姓名")
    private String name;
    @ExportField(value = "手机号")
    private String phone;
    @ExportField(value = "图像")
    private String avatar;
    @ExportField(value = "创建时间")
    private Date createdTime;
}

4.测试

package com.user.service.dguser.controller;

import cn.hutool.poi.excel.ExcelWriter;
import com.user.service.dguser.model.User2;
import com.user.service.dguser.uilt.excel.ExcelByHuTools;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;

@RestController
@RequestMapping("test")
public class ExcelOutController {

    @Value("${Excel.path:dd}")
    private String path;

    @GetMapping("export")
    public void export1(HttpServletRequest request, HttpServletResponse response) throws IOException {

        List<User2> list = new ArrayList<>();
        list.add(new User2("001", "小三", "13411112222", "三根毛", new Date()));
        list.add(new User2("002", "小四", "13411112222", "三根毛", new Date()));
        list.add(new User2("003", "", "13411112222", "三根毛", new Date()));
        list.add(new User2("004", "小六", "", "三根毛", new Date()));
        list.add(new User2("", "", "", "小七三根毛大家啊嘎啊噶跟家里刚啊刚来", new Date()));
        list.add(new User2("006", "小八", "13411112222", "三根毛", new Date()));


        try {
            ExcelWriter writer = ExcelByHuTools.exportExcelByHuTools(User2.class, list, "sheet1");

            ExcelByHuTools.export(request, response, writer, "dgzhang.xlsx");
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }

    }
}

测试结果