SpringBoot使用EasyExcel

目录

[TOC]

参考

https://www.bilibili.com/video/BV1mG4y1f7jr/

导入依赖

<!--EasyExcel-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.3.2</version>
</dependency>

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `nickname` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '用户昵称',
  `avatar` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '用户头像链接',
  `username` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户名',
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '密码，加密存储',
  `salt` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '密码盐值',
  `gender` tinyint(1) NOT NULL DEFAULT '0' COMMENT '性别，0表示未知，1表示男性，2表示女性',
  `birthday` date DEFAULT NULL COMMENT '生日',
  `email` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '邮箱',
  `phone` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '手机号',
  `qq` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT 'QQ号码',
  `wechat` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '微信号',
  `weibo` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '微博账号',
  `github` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT 'GitHub账号',
  `linkedin` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT 'LinkedIn账号',
  `country_code` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '国家代码',
  `province` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '省份',
  `city` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '城市',
  `address` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '地址',
  `post_code` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '邮编',
  `last_login_time` datetime DEFAULT NULL COMMENT '最近一次登录时间',
  `last_login_ip` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '最近一次登录IP地址',
  `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '用户状态，0表示禁用，1表示启用',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `remark1` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '自定义字段1',
  `remark2` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '自定义字段2',
  `remark3` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '自定义字段3',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `idx_username` (`username`) USING BTREE COMMENT '唯一索引，保证用户名唯一'
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='用户表'

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

/**
 * 用户信息excel映射类
 */
@Data
public class UserExcelEntity {

    @ExcelProperty(value = "编号")
    private Long id;

    @ExcelProperty(value = "昵称")
    private String nickname;

    @ExcelProperty(value = "用户名")
    private String username;

    @ExcelProperty(value = "密码")
    private String password;
}

文件导入

基本方式导入

基本方式导入，程序中获取到的数据为Map类型

import com.alibaba.excel.EasyExcel;

import java.io.File;
import java.util.List;

/**
 * 1、基本方式导入
 * Date: 2023/8/12
 *
 * @author XiaoFei
 */
public class BaseImport {
    public static void main(String[] args) {
        File file = new File("C:\\Users\\19030\\Desktop\\tmp\\user.xlsx");

        List<Object> list = EasyExcel.read(file).sheet(0).doReadSync();

        list.forEach(System.out::println);
    }
}

模型映射导入

基本语法

• 关联映射

  • @ExcelProperty(index = 0)

  • @ExcelProperty(value = “列名”)

  • @ExcelProperty(value = {“主标题”, “副标题”})

• 格式转换，注解都是属于com.alibaba.excel.annotation下的

  • @DateTimeFormat(value = “yyyy年MM月dd日 HH时mm分ss秒”)
  • @NumberFormat(value = “#.##%”)
  • @ExcelProperty(converter = 自定义格式转换.class)
• 相关方法
  • EasyExcel.read(inputStream).head(映射模型.class).xxx

使用

import com.alibaba.excel.EasyExcel;
import com.xiaofei.excel.entity.excel.UserExcelEntity;
import org.junit.jupiter.api.Test;

import java.io.File;
import java.util.List;

/**
 * 1、文件导入
 */
public class FileImport {

    /**
     * 模型映导入
     */
    @Test
    void modelMapImport() {

        File file = new File("C:\\Users\\19030\\Desktop\\tmp\\user.xlsx");

        List<UserExcelEntity> list = EasyExcel.read(file).head(UserExcelEntity.class).sheet(0).doReadSync();

        list.forEach(System.out::println);
    }
}

导入的监昕器

使用EasyExcel自带监听器

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.listener.PageReadListener;
import com.xiaofei.excel.entity.excel.UserExcelEntity;
import org.junit.jupiter.api.Test;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.util.List;

/**
 * 1、文件导入
 */
public class FileImport {

    /**
     * 导入监听器
     */
    @Test
    void listenerImport() throws FileNotFoundException {

        InputStream inputStream = new FileInputStream("C:\\Users\\19030\\Desktop\\tmp\\user.xlsx");

        EasyExcel.read(inputStream, UserExcelEntity.class, new PageReadListener<>(this::consumerMethod)).sheet(0).doRead();
    }

    /**
     * 消费函数，监听器会自动调用该函数，对数据进行消费
     */
    private void consumerMethod(List<UserExcelEntity> list) {
        list.forEach(System.out::println);
    }
}

使用自定义监听器

创建一个类，实现ReadListener接口

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.xiaofei.excel.entity.excel.UserExcelEntity;

/**
 * 用户信息excel导入监听器
 */
public class UserListener implements ReadListener<UserExcelEntity> {
    
    /**
     * 获取每一行数据和
     */
    @Override
    public void invoke(UserExcelEntity userExcelEntity, AnalysisContext analysisContext) {
        System.out.println(userExcelEntity);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}

import com.alibaba.excel.EasyExcel;
import com.xiaofei.excel.entity.excel.UserExcelEntity;
import com.xiaofei.excel.listener.UserListener;
import org.junit.jupiter.api.Test;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.util.List;

/**
 * 1、文件导入
 */
public class FileImport {
    /**
     * 监听器导入
     */
    @Test
    void listenerImport() throws FileNotFoundException {

        InputStream inputStream = new FileInputStream("C:\\Users\\19030\\Desktop\\tmp\\user.xlsx");

        EasyExcel.read(inputStream, UserExcelEntity.class, new UserListener()).sheet(0).doRead();
    }
}

指定表头导入

如图所示，由于表头所在行数不是第一行，EasyExcel默认是指定第一行为表头，这个时候需要EasyExcel.read().headRowNumber(表头所在行数)指定表头所在行

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.listener.PageReadListener;
import com.xiaofei.excel.entity.excel.UserExcelEntity;
import org.junit.jupiter.api.Test;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.util.List;

/**
 * 1、文件导入
 */
public class FileImport {
    /**
     * 导入监听器
     */
    @Test
    void listenerImport() throws FileNotFoundException {

        InputStream inputStream = new FileInputStream("C:\\Users\\19030\\Desktop\\tmp\\user.xlsx");

        EasyExcel.read(inputStream, UserExcelEntity.class, new PageReadListener<>(this::consumerMethod))
                .headRowNumber(3)
                .sheet(0).doRead();
    }

    /**
     * 消费函数
     */
    private void consumerMethod(List<UserExcelEntity> list) {
        list.forEach(System.out::println);
    }
}

导入异常处理

ReadListener接口中的onException为出现异常时会执行的方法

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.xiaofei.excel.entity.excel.UserExcelEntity;
import lombok.extern.slf4j.Slf4j;

/**
 * 用户信息excel导入监听器
 */
@Slf4j
public class UserListener implements ReadListener<UserExcelEntity> {
    @Override
    public void invoke(UserExcelEntity userExcelEntity, AnalysisContext analysisContext) {
        System.out.println(userExcelEntity);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }

    /**
     * 当出现异常时，该方法会执行
     */
    @Override
    public void onException(Exception exception, AnalysisContext context) throws Exception {
        log.error("Excel导入报错");
        ReadListener.super.onException(exception, context);
    }
}

文件上传导入

/**
 * 文件上传
 *
 * @param file 上传的文件
 * @return true：上传成功。false：上传失败
 */
@ApiOperation(value = "文件上传", httpMethod = "POST", response = ResponseUtils.class, produces = "application/json")
@PostMapping("/upload")
public ResponseUtils<Boolean> uploadFile(@RequestParam("file") MultipartFile file) throws IOException {
    Boolean isSuccess = userService.uploadFile(file);
    return new ResponseUtils<Boolean>().success(isSuccess ? "上传成功" : "上传失败", isSuccess);
}

/**
 * 文件上传
 *
 * @param file 上传的文件
 * @return true：上传成功。false：上传失败
 */
@Override
public Boolean uploadFile(MultipartFile file) throws IOException {

    if (file == null) {
        throw new RuntimeException("上传的文件不能为空");
    }

    //获取file的文件流
    InputStream inputStream = file.getInputStream();

    //EasyExcel读取inputStream里面的数据，批量插入数据库中
    EasyExcel.read(inputStream, UserEntity.class, new PageReadListener<>(this::addBatch)).headRowNumber(3).sheet().doRead();

    //返回处理结果
    return true;
}

/**
 * 批量添加数据
 *
 * @param userEntityList 数据集合
 * @return true：添加成功。false：添加失败
 */
@Override
@CacheEvict(cacheNames = RedisConstant.BASE_CACHE_KEY + "user", allEntries = true)
public Boolean addBatch(List<UserEntity> userEntityList) {
    return this.saveBatch(userEntityList);
}

文件导出

基本方式导出

@Test
void baseImport() {
    String filePath = "C:\\Users\\19030\\Desktop\\tmp\\userExport.xlsx";
    List<UserEntity> userEntities = userService.selectList();

    EasyExcel.write(filePath).sheet("导出数据").head(UserEntity.class).doWrite(userEntities);
}

模型映射导出

import com.alibaba.excel.EasyExcel;
import com.xiaofei.excel.entity.UserEntity;
import com.xiaofei.excel.service.UserService;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

@SpringBootTest
public class EasyExcelApplicationTest {

    @Autowired
    private UserService userService;
    
    /**
     * 基础导入
     */
    @Test
    void baseImport() {

        // 从数据库中查询数据
        List<UserEntity> userEntities = userService.selectList();

        /**
         * 将查询出来的数据导入Excel表格中
         */
        String filePath = "C:\\Users\\19030\\Desktop\\tmp\\userExport.xlsx";
        EasyExcel.write(filePath).sheet("导出数据").head(UserEntity.class).doWrite(userEntities);
    }
}

导出行高列宽

在实体类的字段或类上，添加对应的注解

EasyExcel.write(filePath).sheet(“导出数据”).registerWriteHandler对应的策略，也可以写一个类继承AbstractColumnWidthStyleStrategy

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.style.column.SimpleColumnWidthStyleStrategy;
import com.xiaofei.excel.entity.UserEntity;
import com.xiaofei.excel.service.UserService;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.List;

@SpringBootTest
public class EasyExcelApplicationTest {

    @Autowired
    private UserService userService;
    /**
     * 基础导入
     */
    @Test
    void baseImport() {

        // 从数据库中查询数据
        List<UserEntity> userEntities = userService.selectList();

        /**
         * 将查询出来的数据导入Excel表格中
         */
        String filePath = "C:\\Users\\19030\\Desktop\\tmp\\userExport.xlsx";
        EasyExcel
                .write(filePath)
                .sheet("导出数据").head(UserEntity.class)
                .registerWriteHandler(new SimpleColumnWidthStyleStrategy(30))
                .doWrite(userEntities);
    }
}

也可以写一个类，继承AbstractCellStyleStrategy并且实现CellWriteHandler，编写一个EasyExcel的自定义拦截器

合并单元格导出

导出动态表头

导出链接批注公式

导出图片内容

填充模板

模板填充对象

模板填充列表

模板组合填充

导出文件下载

工具类

import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.util.MapUtils;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.springframework.stereotype.Component;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Excel导出自定义列宽策略设置
 */
@Component
public class ColumnWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {

    private static final int MAX_COLUMN_WIDTH = 255;

    private final Map<Integer, Map<Integer, Integer>> cache = MapUtils.newHashMapWithExpectedSize(8);

    /**
     * 设置列宽度
     */
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);

        // 是否需要设置宽度
        if (needSetWidth) {
            Map<Integer, Integer> maxColumnWidthMap = this.cache.computeIfAbsent(writeSheetHolder.getSheetNo(), (key) -> new HashMap<>(16));
            Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
            // 列宽判断
            if (columnWidth >= 0) {
                if (columnWidth > MAX_COLUMN_WIDTH) {
                    columnWidth = MAX_COLUMN_WIDTH;
                }

                Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
                if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                    maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
                    writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), (columnWidth + 15) * MAX_COLUMN_WIDTH);
                }

            }
        }
    }

    /**
     * 数据长度获取
     */
    private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {
        if (isHead) {
            return cell.getStringCellValue().getBytes().length;
        } else {
            WriteCellData<?> cellData = cellDataList.get(0);
            CellDataTypeEnum type = cellData.getType();
            if (type == null) {
                return -1;
            } else {
                switch (type) {
                    case STRING:
                        return cellData.getStringValue().getBytes().length;
                    case BOOLEAN:
                        return cellData.getBooleanValue().toString().getBytes().length;
                    case NUMBER:
                        return cellData.getNumberValue().toString().getBytes().length;
                    default:
                        return -1;
                }
            }
        }
    }
}

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.enums.BooleanEnum;
import com.alibaba.excel.event.SyncReadListener;
import com.alibaba.excel.read.listener.PageReadListener;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.row.SimpleRowHeightStyleStrategy;
import com.xiaofei.excel.strategy.ColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.*;
import org.springframework.stereotype.Component;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.function.Consumer;

/**
 * EasyExcel工具类
 */
@Component
public class EasyExcelUtils {
    /**
     * 读取指定的文件中的指定的sheet并且指定Consumer对数据进行消费
     *
     * @param inputStream 文件流
     * @param head        读取的数据要转换成哪个类型
     * @param consumer    用于消费数据方法
     * @param sheetName   sheetName名称
     */
    public static <T> void readFile(InputStream inputStream, Class<T> head, Consumer<List<T>> consumer, String sheetName, Integer headRowNumber) {
        EasyExcel.read(inputStream, head, new PageReadListener<>(consumer))
                .autoCloseStream(false) // 不要自动关闭，交给 Servlet 自己处理
                .headRowNumber(headRowNumber == null ? 1 : headRowNumber)
                .sheet(sheetName)
                .doReadSync();
    }

    /**
     * 读取指定的文件中的指定的sheet并且指定Consumer对数据进行消费
     *
     * @param inputStream 文件流
     * @param head        读取的数据要转换成哪个类型
     * @param consumer    用于消费数据方法
     */
    public static <T> void readFile(InputStream inputStream, Class<T> head, Consumer<List<T>> consumer, Integer headRowNumber) {
        EasyExcel.read(inputStream, head, new PageReadListener<T>(consumer))
                .autoCloseStream(false) // 不要自动关闭，交给 Servlet 自己处理
                .headRowNumber(headRowNumber == null ? 1 : headRowNumber)
                .doReadAllSync();
    }

    /**
     * 读取指定的文件中的指定的sheet并且指定Consumer对数据进行消费
     *
     * @param inputStream   文件流
     * @param head          读取的数据要转换成哪个类型
     * @param sheetName     sheetName名称
     * @param headRowNumber 表头在哪一行
     */
    public static <T> List<T> readFile(InputStream inputStream, Class<T> head, String sheetName, Integer headRowNumber) {
        SyncReadListener syncReadListener = new SyncReadListener();
        EasyExcel.read(inputStream, head, syncReadListener)
                .autoCloseStream(false) // 不要自动关闭，交给 Servlet 自己处理
                .headRowNumber(headRowNumber == null ? 1 : headRowNumber)
                .sheet(sheetName)
                .doReadSync();

        // 判断获取到的数据是否为空
        if (syncReadListener.getList() != null) {
            return (List<T>) syncReadListener.getList();
        }

        return new ArrayList<>();
    }

    /**
     * 读取指定的文件中的指定的sheet并且指定Consumer对数据进行消费
     *
     * @param inputStream 文件流
     * @param head        读取的数据要转换成哪个类型
     */
    public static <T> List<T> readFile(InputStream inputStream, Class<T> head, Integer headRowNumber) {
        SyncReadListener syncReadListener = new SyncReadListener();

        EasyExcel.read(inputStream, head, syncReadListener)
                .autoCloseStream(false) // 不要自动关闭，交给 Servlet 自己处理
                .headRowNumber(headRowNumber == null ? 1 : headRowNumber)
                .doReadAllSync();

        // 判断获取到的数据是否为空
        if (syncReadListener.getList() != null) {
            return (List<T>) syncReadListener.getList();
        }
        return new ArrayList<>();
    }

    /**
     * 将数据写入Excel中
     */
    public static <T> void writeFile(HttpServletResponse response, String filename, String sheetName, Class<T> head, List<T> data) throws IOException {

        // 设置 header 和 contentType。写在最后的原因是，避免报错时，响应 contentType 已经被修改了
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码
        filename = URLEncoder.encode(filename, "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + filename + "_" + System.currentTimeMillis() + ExcelTypeEnum.XLSX.getValue());

        // 头样式策略
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle(), contentWriteCellStyle());

        // 内容样式策略
        EasyExcel
                .write(response.getOutputStream(), head)
                .autoCloseStream(false) // 不要自动关闭，交给 Servlet 自己处理
                .registerWriteHandler(new ColumnWidthStyleStrategy()) // 自动适配宽度策略
                .registerWriteHandler(new SimpleRowHeightStyleStrategy((short) 30, (short) 25))//设置表头行高和内容行高
                .registerWriteHandler(horizontalCellStyleStrategy) // 设置自定义样式
                .sheet(sheetName)
                .doWrite(data);

    }

    /**
     * Excel表头样式
     */
    public static WriteCellStyle headWriteCellStyle() {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();

        // 字体
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontName("仿宋");//设置字体名字
        headWriteFont.setFontHeightInPoints((short) 16);//设置字体大小
        headWriteFont.setBold(BooleanEnum.TRUE.getBooleanValue());//字体是否加粗

        // 样式
        headWriteCellStyle.setWriteFont(headWriteFont); //在样式用应用设置的字体样式;

        // 背景
        headWriteCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.index);//背景颜色
        headWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);//背景颜色填充类型

        //边框
        headWriteCellStyle.setBorderBottom(BorderStyle.THIN);//设置底边框;
        headWriteCellStyle.setBottomBorderColor((IndexedColors.BLACK1.index));//设置底边框颜色;
        headWriteCellStyle.setBorderLeft(BorderStyle.THIN);  //设置左边框;
        headWriteCellStyle.setLeftBorderColor(IndexedColors.BLACK1.index);//设置左边框颜色;
        headWriteCellStyle.setBorderRight(BorderStyle.THIN);//设置右边框;
        headWriteCellStyle.setRightBorderColor(IndexedColors.BLACK1.index);//设置右边框颜色;
        headWriteCellStyle.setBorderTop(BorderStyle.THIN);//设置顶边框;
        headWriteCellStyle.setTopBorderColor(IndexedColors.BLACK1.index); //设置顶边框颜色;

        // 其他
        headWriteCellStyle.setWrapped(BooleanEnum.FALSE.getBooleanValue());  //设置自动换行;
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);//设置水平对齐的样式为居中对齐;
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);  //设置垂直对齐的样式为居中对齐;
        headWriteCellStyle.setShrinkToFit(BooleanEnum.FALSE.getBooleanValue());//设置文本收缩至合适


        return headWriteCellStyle;
    }

    /**
     * Excel内容样式
     */
    public static WriteCellStyle contentWriteCellStyle() {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();

        // 字体
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontName("仿宋");//设置字体名字
        headWriteFont.setFontHeightInPoints((short) 14);//设置字体大小
        headWriteFont.setBold(BooleanEnum.FALSE.getBooleanValue());//字体是否加粗
        headWriteFont.setColor(IndexedColors.BLACK.index);

        // 样式
        headWriteCellStyle.setWriteFont(headWriteFont); //在样式用应用设置的字体样式;

        // 背景
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.index);//背景颜色
        headWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);//背景颜色填充类型

        //边框
        headWriteCellStyle.setBorderBottom(BorderStyle.THIN);//设置底边框;
        headWriteCellStyle.setBottomBorderColor((IndexedColors.BLACK1.index));//设置底边框颜色;
        headWriteCellStyle.setBorderLeft(BorderStyle.THIN);  //设置左边框;
        headWriteCellStyle.setLeftBorderColor(IndexedColors.BLACK1.index);//设置左边框颜色;
        headWriteCellStyle.setBorderRight(BorderStyle.THIN);//设置右边框;
        headWriteCellStyle.setRightBorderColor(IndexedColors.BLACK1.index);//设置右边框颜色;
        headWriteCellStyle.setBorderTop(BorderStyle.THIN);//设置顶边框;
        headWriteCellStyle.setTopBorderColor(IndexedColors.BLACK1.index); //设置顶边框颜色;

        // 其他
        headWriteCellStyle.setWrapped(BooleanEnum.FALSE.getBooleanValue());  //设置自动换行;
        headWriteCellStyle.setShrinkToFit(BooleanEnum.FALSE.getBooleanValue());//设置文本收缩至合适
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);//设置水平对齐的样式为居中对齐;
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);  //设置垂直对齐的样式为居中对齐;


        return headWriteCellStyle;
    }
}