如何优雅导出excel

汤一程

已于 2023-12-08 16:49:21 修改

阅读量699

点赞数 13

分类专栏：小技能文章标签： excel java 开发语言 spring boot 后端

于 2023-12-08 16:25:02 首次发布

本文链接：https://blog.csdn.net/qq_41601281/article/details/134859355

版权

小技能专栏收录该内容

3 篇文章 0 订阅

订阅专栏

一.导入依赖

        <!--poi-->
        <dependency>
            <groupId>com.deepoove</groupId>
            <artifactId>poi-tl</artifactId>
            <version>1.12.0</version>
        </dependency>

二.自定义注解

新建一个自定义注解，可以放在属性上，也可以放在方法上。

@Target({ElementType.FIELD, ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelField {

    String name(); //excel的表头

    int seq(); //排序

}

三.使用注解

建一个实体类，用上@ExcelField，注意此时的seq的值，后续导出的excel的表头会按照seq值从左到右排列。

@Data
public class ExcelModel {

    @ExcelField(name = "属性1", seq = 2)
    private String filed1;

    @ExcelField(name = "属性2", seq = 1)
    private LocalDateTime filed2;

    @ExcelField(name = "属性3", seq = 3)
    private Integer filed3;

}

四.导出的工具类

直接把整个工具类放在下面，只需要调主方法即可导出excel

package com.example.demo;

import lombok.Data;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.CollectionUtils;

import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;
import javax.servlet.http.HttpServletResponse;

public class ExcelUtil {

    //日期类型的值格式化成字符串类型
    private static final SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    /**
     * 导出excel(主方法)
     *
     * @param response
     * @param data     :原始数据
     * @param fileName :文件名
     * @param <T>      :实体类型
     * @throws Exception
     */
    public static <T> void exportExcel(HttpServletResponse response, List<T> data, String fileName) throws Exception {
        if (CollectionUtils.isEmpty(data)) {
            return;
        }
        List<Field> fields = getAvailableFields(data.get(0).getClass());
        List<Map<String, Object>> result = getDataGroupByTitle(data, fields);
        List<String> titles = fields.stream().map(field -> getExcelFieldNameByField(field)).collect(Collectors.toList());
        exportExcel(response, result, titles, fileName);
    }


    /**
     * 获取一个类含有ExcelField注解的属性
     *
     * @param type
     * @return
     */
    private static List<Field> getAvailableFields(Class<?> type) {
        Field[] declaredFields = type.getDeclaredFields();
        List<Field> fields = new LinkedList<>();
        for (int i = 0; i < declaredFields.length; i++) {
            Field declaredField = declaredFields[i];
            if (declaredField.getAnnotation(ExcelField.class) != null) {
                fields.add(declaredField);
            }
        }
        return sort(fields);
    }


    /**
     * 根据注解中的seq排序
     *
     * @param fields
     */
    private static List<Field> sort(List<Field> fields) {
        return fields.stream().sorted(Comparator.comparing(f -> f.getAnnotation(ExcelField.class).seq()))
                .collect(Collectors.toList());
    }

    /**
     * 把数据组转成List<Map<"表头","值">>
     *
     * @param data   :原始数据
     * @param fields :实体属性
     * @param <T>    :数据类型
     * @return :<标题,值>
     * @throws Exception
     */
    private static <T> List<Map<String, Object>> getDataGroupByTitle(List<T> data, List<Field> fields) throws Exception {
        List<Map<String, Object>> result = new LinkedList<>();
        for (T t : data) {
            Map<String, Object> map = new HashMap<>();
            for (Field field : fields) {
                field.setAccessible(true);//反射必须要设置,不然反射不到private方法
                map.put(getExcelFieldNameByField(field), field.get(t));
            }
            result.add(map);
        }
        return result;
    }

    /**
     * 获取ExcelField的name
     *
     * @param field :属性
     * @return
     */
    private static String getExcelFieldNameByField(Field field) {
        ExcelField annotation = field.getAnnotation(ExcelField.class);
        return annotation.name();
    }

    /**
     * 导出excel
     *
     * @param response
     * @param data     :数据
     * @param fileName :文件名
     * @throws IOException
     */
    private static void exportExcel(HttpServletResponse response, List<Map<String, Object>> data, List<String> titles, String fileName) throws IOException {
        setHeader(response, fileName);
        XSSFWorkbook workbook = new XSSFWorkbook();//创建一个Excel文件
        XSSFSheet sheet = workbook.createSheet(fileName); //创建一个工作表

        CellStyle stringCellStyle = workbook.createCellStyle();
        stringCellStyle.setDataFormat(workbook.createDataFormat().getFormat("@"));

        XSSFRow row = sheet.createRow(0); //添加表头行
        //添加表头内容
        for (int i = 0; i < data.get(0).keySet().size(); i++) {
            XSSFCell headCell = row.createCell(i);
            headCell.setCellValue(titles.get(i));
        }
        //从第2行开始添加内容
        for (int i = 0; i < data.size(); i++) {
            row = sheet.createRow(i + 1);
            for (int j = 0; j < titles.size(); j++) {
                XSSFCell cell = row.createCell(j);
                setCellValue(cell, data.get(i).get(titles.get(j)), stringCellStyle);
                //自适应列宽
                //sheet.autoSizeColumn(i);
                //sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10);
            }
        }
        OutputStream outputStream = response.getOutputStream();
        // 保存Excel文件
        try {
            workbook.write(outputStream);
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 设置返回的header
     *
     * @param response
     * @param outFileName
     */
    private static void setHeader(HttpServletResponse response, String outFileName) {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename=\"" + outFileName + ".xlsx\"");
    }

    /**
     * 设置单元格的值
     *
     * @param cell
     * @param obj
     * @param stringCellStyle
     */
    private static void setCellValue(XSSFCell cell, Object obj, CellStyle stringCellStyle) {
        cell.setCellStyle(stringCellStyle);
        if (obj instanceof Date) {
            cell.setCellValue(obj == null ? null : simpleDateFormat.format(obj));
        } else {
            cell.setCellValue(obj == null ? null : obj.toString());
        }
    }

}

五.如何使用

我们新建一个接口测试下，代码如下

@RestController
@RequestMapping("/test")
public class TestController {

    @GetMapping("/export-excel")
    public void exportExcel(HttpServletResponse response) throws Exception {
        List<ExcelModel> data = new LinkedList<>();
        ExcelModel excelModel1 = new ExcelModel("测试1", new Date(), 1);
        ExcelModel excelModel2 = new ExcelModel("测试2", new Date(), 2);
        ExcelModel excelModel3 = new ExcelModel("测试3", new Date(), 3);
        data.add(excelModel1);
        data.add(excelModel2);
        data.add(excelModel3);
        
        ExcelUtil.exportExcel(response, data, "out");
    }

}