使用 hutool包进行excel导入导出

依赖

<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.5.6</version>
</dependency>
<!--Export as Excel-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.1</version>
</dependency>

导入

/**
 * excel导入
 */
@PostMapping("/upload")
@ApiOperation("excel导入")
public Result upload(@RequestParam("file") MultipartFile file) throws IOException {
    // 通过文件获取输入流
    InputStream in = file.getInputStream();
    // 借助hutool读取
    ExcelReader reader = ExcelUtil.getReader(in);
    List<List<Object>> list = reader.read(1); // 从第二行开始读
    // 创建一个List集合
    List<User> users = CollUtil.newArrayList();
    List<List<Object>> data = new ArrayList<>(0);
    // 遍历
    for (List<Object> row : list) {
        User user = new User();
        // 转换成字符串
        user.setUsername(row.get(0).toString());
        user.setPassword(row.get(1).toString());
        user.setNickname(row.get(2).toString());
        user.setEmail(row.get(3).toString());
        user.setPhone(row.get(4).toString());
        user.setAddress(row.get(5).toString());
        // 遍历完一个添加一个
        users.add(user);
        data.add(row);
    }
    // 调用mapper添加到数据库
    boolean flag = userService.addAll(users);

    // 输出到本地文件
    ExcelWriter writer = ExcelUtil.getWriter(new File("C:\\Users\\zg\\Downloads\\export.xlsx"));
    writer.writeHeadRow(Arrays.asList("账号", "密码", "用户名称", "邮箱", "电话"));
    for (List<Object> row : data) {
        // 写入整行
        writer.writeRow(row);
    }
    // 关闭流
    writer.flush();
    writer.close();
    return flag ? Result.ok() : Result.failed();
}

使用 List<List<Object>> list = reader.read(1); 导入时候，excel 如果有多个相邻都为空的会识别成一个空的问题

解决：使用 reader.readAll(); 方法

List<Map<String, Object>> maps = reader.readAll();
map读取为：{姓名=李四, 年龄=null, 性别=null}

使用实体类，表头和字段名一致即可
List<User> list = reader.readAll(User.class);

导出

设置样式

官网自定义样式

ExcelWriter writer = ...;

// 定义单元格背景⾊
StyleSet style = writer.getStyleSet();
// 第⼆个参数表⽰是否也设置头部单元格背景
style.setBackgroundColor(IndexedColors.RED, false);
//设置内容字体
Font font = writer.createFont();
font.setBold(true);
font.setColor(Font.COLOR_RED); 
font.setItalic(true); 
//第⼆个参数表⽰是否忽略头部样式
writer.getStyleSet().setFont(font, true);

以上⾃定义样式是针对单元格集合的，划分为：
头部样式 headCellStyle
普通单元格样式 cellStyle
数字单元格样式 cellStyleForNumber
⽇期单元格样式 cellStyleForDate

有时候我们只需要修改单个单元格样式，其他单元格样式采⽤默认样式，参考如下：

@GetMapping("downloadExampleExcel")
    public Response downloadExampleExcel(HttpServletResponse response) {
        logger.info("downloadExampleExcel response start。。。");
        List<Title> titles = titleService.selectTitles();
        ExcelWriter writer = ExcelUtil.getWriter();
        for (int i = 0, j = 0; i < titles.size(); i++) {
            Title title = titles.get(i);
            writeCell(writer, j ++, title.getFieldDescC(), title.getRequiredFlag());
        }
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + "⽂件名" + ".xls");
        ServletOutputStream out = null;
        try {
            out = response.getOutputStream();
            writer.flush(out, true);
        } catch (Exception e) {
            log.error("downloadExampleExcel response exception", e);
            return ResponseHelper.buildFail(e.getMessage());
        } finally {
            writer.close();
            if (out != null) {
                IoUtil.close(out);
            }
            logger.info("downloadExampleExcel response end。。。");
        }
        return ResponseHelper.buildOk();
    }
    /**
     * 输⼊标题到excel
     * @param writer excel对象
     * @param column 当前列位置
     * @param cellValue 标题内容
     * @param requiredFlag 是否标红
     */
    private void writeCell(ExcelWriter writer, int column, String cellValue, String requiredFlag){
        // 根据x,y轴设置单元格内容
        writer.writeCellValue(column , 0, cellValue);
        Font font = writer.createFont();
        font.setColor(Font.COLOR_RED);
        if (Constants.NUMBER_TWO.equals(requiredFlag)){
            // 根据x,y轴获取当前单元格样式 x 列数，从 0 开始    y 行数，从 0 开始 （包括标题栏）
            CellStyle cellStyle = writer.createCellStyle(column, 0);
            // 内容⽔平居中
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            // 内容垂直居中
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            // 设置边框
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            // 字体颜⾊标红
            cellStyle.setFont(font);
        }
    }

效果：

由于 autoSizeColumnAll() 自动设置列宽方法只对数字、字母生效，对中文不生效

针对中文设置列宽

        ExcelWriter writer = ExcelUtil.getWriter(true);
        //写数据 writer.write(data, true);
        StyleSet style = writer.getStyleSet();
        Font font = writer.createFont();
        font.setColor(IndexedColors.VIOLET.index);
        font.setBold(true);
        font.setFontHeightInPoints((short) 12);
        //重点，设置中文字体
        font.setFontName("宋体");
        style.getHeadCellStyle().setFont(font);
        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)));
            }
        }

默认导出

/**
 * 文件导出 excel
 */
public void exportFile(HttpServletResponse response) throws IOException {
    ExcelWriter writer = ExcelUtil.getWriter();
    String time = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
    // 设置表名
    String excelName="导出文件"+time+".xls";
    // 设置表头
    List<Object> header= CollectionUtil.newArrayList("编号","姓名","年龄","生日","性别");
    // 使用 mybatis-plus 快速查询数据
    Collection<UserEntity> userList = userMapper.selectList(new QueryWrapper<UserEntity>());
    // 输出数据设置
    List<List<Object>> rows=userList.stream().map(a->{
        List<Object> list=new ArrayList<>(0);
        list.add(a.getId());
        list.add(a.getUsername());
        list.add(a.getAge());
        list.add(a.getBirth());
        list.add(a.getGender());
        return list;
    }).collect(Collectors.toList());
    // 数据导出
    writer.writeHeadRow(header).write(rows==null?new ArrayList<List<Object>>():rows);
    response.setContentType("application/vnd.ms-excel;charset=utf-8");
    response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(excelName, "utf-8"));
    ServletOutputStream out = response.getOutputStream();
    // 关闭连接
    writer.flush(out).close();
}

自定义导出

• 工具类

package com.zl.util;

import cn.hutool.poi.excel.ExcelWriter;
import cn.hutool.poi.excel.StyleSet;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.SheetUtil;

/**
 * hutool excel 工具类
 */
public class LocalExcelUtil {
    /**
     * 设置 excel 单元格颜色
     * @param writer ExcelWriter
     * @param x 列
     * @param y 行
     * @param color 颜色
     */
    public static void setColor(ExcelWriter writer,int x,int y,short color){
        Font font = writer.createFont();
        font.setColor(color);
        CellStyle cellStyle = writer.createCellStyle(x, y);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setFont(font);
    }

    /**
     * 设置 excel 自动列宽
     * @param writer ExcelWriter
     * @param fontName 字体
     * @param bold 加粗
     * @param fontColor 字体颜色
     */
    public static void setAuthColWidth(ExcelWriter writer,String fontName,Boolean bold,short fontColor){
        writer.autoSizeColumnAll();
        StyleSet style = writer.getStyleSet();
        Font font = writer.createFont();
        font.setColor(fontColor);
        // 加粗
        font.setBold(bold);
        font.setFontHeightInPoints((short) 12);
        //重点，设置中文字体
        font.setFontName(fontName);
        style.getHeadCellStyle().setFont(font);
        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)));
            }
        }
    }
}

• 导出

    /**
     * 导出数据
     * @param queryUserVO 查询参数
     * @param response 响应
     */
    @Override
    public void exportUserInfo(QueryUserVO queryUserVO, HttpServletResponse response) throws IOException {
        ExcelWriter writer = ExcelUtil.getWriter();
        StyleSet styleSet=writer.getStyleSet();
        // 第二个参数表示是否也设置头部单元格背景
        styleSet.setBackgroundColor(IndexedColors.WHITE,true);
        String time = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd %T"));
        // 设置表名
        String excelName="用户数据"+time+".xls";
        String titleName="用户数据";
        // 查询数据
        QueryWrapper<UserEntity> queryWrapper=new QueryWrapper<>();
        Object[] prams={queryUserVO.getStartTime(),queryUserVO.getEndTime()};
        queryWrapper.apply("DATE_FORMAT(birth,'%Y-%m-%d %T') BETWEEN {0} and {1}",prams);
        queryWrapper.orderByAsc("birth");
        List<UserEntity> data = list(queryWrapper);
        // 查询性别 0 女  1 男
        queryWrapper.eq("gender",queryUserVO.getGender());
        switch (queryUserVO.getGender()){
            case 0:
                titleName= titleName+"（女）";
                break;
            case 1:
                titleName= titleName+"（男）";
                break;
        }
        // 设置表头
        List<Object> header= CollectionUtil.newArrayList("姓名","性别","年龄","生日");
        writer.merge(header.size()-1, titleName);
        writer.writeHeadRow(header);
        // 数据导出
        IntStream.range(0,data.size()).forEach(i->{
            // x 表示列 y 表示行
            writer.writeCellValue(0,i+2,data.get(i).getUsername());
            writer.writeCellValue(1,i+2,data.get(i).getGender());
            writer.writeCellValue(2,i+2,data.get(i).getAge());
            // 当年龄大于55岁时显示红色
            if(data.get(i).getAge()>55){
                LocalExcelUtil.setColor(writer,2,i+2,Font.COLOR_RED);
            }
            writer.writeCellValue(3,i+2,new SimpleDateFormat("yyyy/MM/dd").format(data.get(i).getBirth()));
        });
        // 设置默认列宽
        LocalExcelUtil.setAuthColWidth(writer,"宋体",false,IndexedColors.BLACK.index);
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(excelName, "utf-8"));
        ServletOutputStream out = response.getOutputStream();
        // 关闭连接
        writer.flush(out).close();
    }

根据业务相应的改动就行

---

参考链接：

https://wenku.baidu.com/view/547d8a39b4360b4c2e3f5727a5e9856a561226ff.html