常用工具类Hutool(一)的导入导出功能的实现

1.引入pom.xml依赖

简单版依赖

   <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>${hutool.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.2.2</version>
        </dependency>

复杂表的依赖

<dependency> 
    <groupId>org.apache.poi</groupId>
     <artifactId>poi-ooxml-schemas</artifactId> 
    <version>4.1.2</version>
 </dependency>
<!-- hutool-->
            <dependency>
                <groupId>cn.hutool</groupId>
                <artifactId>hutool-all</artifactId>
                <version>5.7.19</version>
            </dependency>
<!-- poi-->
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>5.0.0</version>
            </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.0.1</version>
        </dependency>
        <dependency>
        <groupId>commons-net</groupId>
        <artifactId>commons-net</artifactId>
        <version>3.6</version>
        </dependency>

导出实现简单版

//表格导出接口
    @GetMapping("/export")
    public void export(HttpServletResponse response) throws IOException {
        //查询所有用户
        List<UserEntity> list= userService.list();

        for (int i = 0; i < list.size(); i++) {
            list.get(i).setDeptName(deptService.selectById(list.get(i).getDeptId()).getName());
        }


        //在内存操作，写到浏览器
        ExcelWriter writer= ExcelUtil.getWriter(true);
        writer.renameSheet(0, "用户记录");
        //自定义标题别名
        writer.addHeaderAlias("name","用户名");
        writer.addHeaderAlias("email","邮箱");
        writer.addHeaderAlias("mobile","手机号");
        writer.addHeaderAlias("deptName","部门");
        // 只导出有别名的字段
        writer.setOnlyAlias(true);
        //默认配置
        writer.write(list,true);
        //设置content—type
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset:utf-8");

        //设置标题
        String fileName= URLEncoder.encode("用户信息","UTF-8");
        //Content-disposition是MIME协议的扩展，MIME协议指示MIME用户代理如何显示附加的文件。
        response.setHeader("Content-Disposition","attachment;filename="+fileName+".xlsx");
        ServletOutputStream outputStream= response.getOutputStream();

        //将Writer刷新到OutPut
        writer.flush(outputStream,true);
        outputStream.close();
        writer.close();
    }

复杂版

   @RequestMapping("/export")
    public void export1(HttpServletResponse response){
        List<UserEntity> list = new ArrayList<>();


        //传入数据
        List<UserEntity> userEntityList = userService.list();


        for (UserEntity user : userEntityList) {
            UserEntity obj = new UserEntity();
            obj.setId(user.getId());
            obj.setName(user.getName());
            obj.setEmail(user.getEmail());
            obj.setMobile(user.getMobile());
            obj.setStatus (user.getStatus());
            obj.setDeptId(user.getDeptId());
            obj.setCreateBy(user.getCreateBy());
            obj.setCreateTime(user.getCreateTime());
            obj.setLastUpdateBy(user.getLastUpdateBy());
            obj.setLastUpdateTime(user.getLastUpdateTime());
            obj.setDelFlag(user.getDelFlag());
            list.add(obj);
        }

        // 1.创建ExcelWriter
        // 通过工具类创建writer，默认创建xls格式
        ExcelWriter writer = ExcelUtil.getWriter();
        writer.renameSheet(0, "用户记录");
        //创建工作表
        //创建xlsx格式的
        //ExcelWriter writer = ExcelUtil.getWriter(true);

        // 2.设置一级标题
        // 合并单元格后的标题行，使用默认标题样式，从0开始
        // 设置表头高度
        writer.setRowHeight(0, 25);

        // 3.设置二级标题
        writer.addHeaderAlias("id", "编号");
        writer.addHeaderAlias("name", "用户名");
        writer.addHeaderAlias("email", "邮箱");
        writer.addHeaderAlias("mobile", "手机号");
        writer.addHeaderAlias("status", "状态  0：禁用   1：正常");
        writer.addHeaderAlias("deptId", "机构ID");
        writer.addHeaderAlias("createBy", "创建人");
        writer.addHeaderAlias("createTime", "创建时间");
        writer.addHeaderAlias("lastUpdateBy", "更新人");
        writer.addHeaderAlias("lastUpdateTime", "更新时间");
        writer.addHeaderAlias("delFlag", "是否删除  -1：已删除  0：正常");


        // 4.设置表头字体
        // 获取表头样式，获取样式后可自定义样式
        CellStyle headCellStyle = writer.getHeadCellStyle();
        // 获取单元格样式
//        CellStyle cellStyle = excelWriter.getCellStyle();
        // 设置内容字体
        Font font = writer.createFont();
        // 设置字体
        font.setFontName("宋体");
        // 设置字体大小
        font.setFontHeightInPoints((short) 14);
        // 字体加粗
        font.setBold(true);
        // 字体颜色
        font.setColor(Font.SS_NONE);
        headCellStyle.setFont(font);

        // 5.设置单元格宽度
        int[] arr = {30, 30, 25};
        for (int i = 0; i < arr.length; i++) {
            writer.setColumnWidth(i, arr[i]);
        }
        writer.merge(list.size()-2, "员工信息表");

        // 只导出有别名的字段
        writer.setOnlyAlias(true);

        // 一次性写出内容，使用默认样式，强制输出标题
        writer.write(list, true);

        // 从第几行写入
//        excelWriter.setCurrentRow(1);
//        excelWriter.writeRow(data());
        // 设置某个单元格的样式
//        CellStyle orCreateCellStyle = excelWriter.getOrCreateCellStyle(0, 1);
        // 设置某行的样式
//        excelWriter.setRowStyle();
        ServletOutputStream out = null;
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
        try {
            String fileName = URLEncoder.encode(dateFormat.format(new Date())+"用户信息表" , StandardCharsets.UTF_8.name());
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            out = response.getOutputStream();
            // 将Excel Workbook刷出到输出流
            writer.flush(out, true);

        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException("文件写入失败！");
        } finally {
            // 记住关流
            IoUtil.close(writer);

            IoUtil.close(out);
        }
    }

简单版导入实现

    /**
     * 导入excel
     * @param file
     */
    @PostMapping("/import")
    public String importExcel(@RequestParam("file") MultipartFile file) throws Exception {
        if (file.isEmpty()) {
            System.out.println("文件为空！");
            return "文件为空";
        }

        // 1.获取上传文件输入流
        InputStream inputStream = null;
        try {
            //文件处理成io流
            inputStream = file.getInputStream();
        } catch (Exception e) {
            e.printStackTrace();
        }

//        //io流给ExcelReader
        ExcelReader excelReader=ExcelUtil.getReader(inputStream);

    // 从第二行开始获取数据   excelReader.read的结果是一个2纬的list，外层是行，内层是行对应的所有列
        List<List<Object>> list = excelReader.read(2, excelReader.getRowCount());
        List<UserEntity> listUser = CollUtil.newArrayList();
        for (List<Object> row: list) {
            UserEntity user=new UserEntity();
            user.setName(row.get(0).toString());
            user.setEmail(row.get(1).toString());
            user.setMobile(row.get(2).toString());
            user.setDeptId(deptService.selectByName(row.get(3).toString()).getId());
            listUser.add(user);
//            ****类似一一对应****
        }
        //批量注册进数据库
        userService.saveBatch(listUser);
        return "导入成功";
    }

复杂版导入实现

// 处理文件上传
    @PostMapping("/excelImport")
    public String uploadImg(@RequestParam("file") MultipartFile file, HttpServletRequest request) throws Exception {

        System.out.println(file);

        if (file.isEmpty()) {
            System.out.println("文件为空！");
            return "文件为空";
        }

        // 1.获取上传文件输入流
        InputStream inputStream = null;
        try {
            inputStream = file.getInputStream();
        } catch (Exception e) {
            // return ResponseData.fail(ResponseCodeEnum.ERROR_PARAM_INVALID);
            e.printStackTrace();
        }

        // 调用用 hutool 方法读取数据 默认调用第一个sheet
        ExcelReader excelReader = ExcelUtil.getReader(inputStream);
        // 从第二行开始获取数据   excelReader.read的结果是一个2纬的list，外层是行，内层是行对应的所有列
        // 读取方式1
    List<List<Object>> read = excelReader.read(2, excelReader.getRowCount());
    List<UserEntity> excels = new ArrayList<>();
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        // 循环获取的数据
    for (int i = 0; i < read.size(); i++) {
      List list = read.get(i);
        UserEntity excel = new UserEntity();
      //按照列获取
      excel.setName(list.get(1).toString());
      excel.setEmail(list.get(2).toString());
      excel.setMobile(list.get(3).toString());
      excel.setStatus(Integer.parseInt(list.get(4).toString()));
      excel.setDeptId(Long.parseLong(list.get(5).toString()));
      excel.setLastUpdateBy(list.get(6).toString());
        String s = list.get(7).toString();
        Date date = sdf.parse(s);
        excel.setCreateTime(date);
        excel.setLastUpdateBy(list.get(8).toString());
       excel.setLastUpdateTime(sdf.parse(list.get(9).toString()));
      excel.setDelFlag(Integer.parseInt(list.get(10).toString()));
      //强制类型转换
      //excel.setWeight(Double.parseDouble(list.get(3).toString()));
      //excel.setStatus(Integer.parseInt(list.get(5).toString()));
      excels.add(excel);
    }
    excels.forEach(
        index -> {
          System.out.println(index);
        });

        for (UserEntity excel : excels) {
            System.out.println(excel);
           userService.save(excel);
        }
        System.out.println("导入成功");
        return "导入成功";
    }

Hutool工具类

import cn.hutool.core.io.IORuntimeException;
import cn.hutool.poi.excel.ExcelWriter;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * 功能: poi导出excel工具类
 *
 */
public class PoiExcelUtil {

  /**
   * 合并单元格处理,获取合并行
   *
   * @param sheet
   * @return List<CellRangeAddress>
   */
  public static List<CellRangeAddress> getCombineCell(Sheet sheet) {
    List<CellRangeAddress> list = new ArrayList<>();
    // 获得一个 sheet 中合并单元格的数量
    int sheetmergerCount = sheet.getNumMergedRegions();
    // 遍历所有的合并单元格
    for (int i = 0; i < sheetmergerCount; i++) {
      // 获得合并单元格保存进list中
      CellRangeAddress ca = sheet.getMergedRegion(i);
      list.add(ca);
    }
    return list;
  }

  public static int getRowNum(List<CellRangeAddress> listCombineCell, Cell cell, Sheet sheet) {
    int xr = 0;
    int firstC = 0;
    int lastC = 0;
    int firstR = 0;
    int lastR = 0;
    for (CellRangeAddress ca : listCombineCell) {
      // 获得合并单元格的起始行, 结束行, 起始列, 结束列
      firstC = ca.getFirstColumn();
      lastC = ca.getLastColumn();
      firstR = ca.getFirstRow();
      lastR = ca.getLastRow();
      if (cell.getRowIndex() >= firstR && cell.getRowIndex() <= lastR) {
        if (cell.getColumnIndex() >= firstC && cell.getColumnIndex() <= lastC) {
          xr = lastR;
        }
      }
    }
    return xr;
  }

  /**
   * 判断指定的单元格是否是合并单元格
   *
   * @param sheet
   * @param row 行下标
   * @param column 列下标
   * @return
   */
  public static boolean isMergedRegion(Sheet sheet, int row, int column) {
    int sheetMergeCount = sheet.getNumMergedRegions();
    for (int i = 0; i < sheetMergeCount; i++) {
      CellRangeAddress range = sheet.getMergedRegion(i);
      int firstColumn = range.getFirstColumn();
      int lastColumn = range.getLastColumn();
      int firstRow = range.getFirstRow();
      int lastRow = range.getLastRow();
      if (row >= firstRow && row <= lastRow) {
        if (column >= firstColumn && column <= lastColumn) {
          return true;
        }
      }
    }
    return false;
  }

  /** 如果需要合并的话，就合并 */
  public static void mergeIfNeed(
      ExcelWriter writer,
      int firstRow,
      int lastRow,
      int firstColumn,
      int lastColumn,
      Object content) {
    if (lastRow - firstRow > 0 || lastColumn - firstColumn > 0) {
      writer.merge(firstRow, lastRow, firstColumn, lastColumn, content, false);
    } else {
      writer.writeCellValue(firstColumn, firstRow, content);
    }
  }

  public static void writeExcel(HttpServletResponse response, ExcelWriter writer) {
    // response为HttpServletResponse对象
    response.setContentType("application/vnd.ms-excel;charset=utf-8");
    // test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
    response.setHeader("Content-Disposition", "attachment;filename=1.xls");

    ServletOutputStream servletOutputStream = null;
    try {
      servletOutputStream = response.getOutputStream();
      writer.flush(servletOutputStream);
      servletOutputStream.flush();
    } catch (IORuntimeException | IOException e) {
      e.printStackTrace();
    } finally {
      writer.close();
      try {
        if (servletOutputStream != null) {
          servletOutputStream.close();
        }
      } catch (IOException e) {
        e.printStackTrace();
      }
    }
  }

  public static void writeExcel(String filename, ExcelWriter writer, HttpServletResponse response) {
    OutputStream ouputStream = null;
    try {
      filename = new String(filename.getBytes("UTF-8"), "ISO-8859-1");
      response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
      response.setHeader("Content-disposition", "attachment;filename=" + filename);
      ouputStream = response.getOutputStream();
      Runtime.getRuntime().gc();
      writer.flush(ouputStream);
      ouputStream.flush();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      if (null != ouputStream) {
        try {
          ouputStream.close();
        } catch (IOException e) {
          e.printStackTrace();
        }
      }
    }
  }
}

控制器

@GetMapping("downExl")
    public void downExl(HttpServletResponse response){
        List<TcUser> tcUsers = new ArrayList<>();
        TcUser tcUser1 = new TcUser(1, "这是1", "111a");
        TcUser tcUser2 = new TcUser(2, "这是2", "222b");
        TcUser tcUser3 = new TcUser(3, "这是3", "333c");
        tcUsers.add(tcUser1);
        tcUsers.add(tcUser2);
        tcUsers.add(tcUser3);
        // 商品导出or模板
        List<String> headerList;
        // 使用 hutool创建exl
        ExcelWriter writer = ExcelUtil.getBigWriter();
        String[] header = {"序号", "ID", "用户名称", "密码"};
        headerList = Arrays.asList(header);
        Sheet sheet = writer.getSheet();
        writer.merge(headerList.size() - 1, "测试exl");
        writer.writeRow(headerList);
        for (int i = 0; i < headerList.size(); i++) {
            if (i == 0 || i == 1 || i == 2 || i == 3 || i == 5 || i == 6 || i == 7 || i == 8 || i == 10 || i == 11) {
                sheet.setColumnWidth(i, 10 * 256);
            } else {
                sheet.setColumnWidth(i, 20 * 256);
            }
        }
        int row = 1;
        for (TcUser tcUser : tcUsers) {
            int firstRow = row + 1;
            int lastRow = row + 1;
            int col = -1;
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, row);
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, tcUser.getId());
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, tcUser.getUsername());
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, tcUser.getPassword());
            row ++;
        }
        PoiExcelUtil.writeExcel("测试exl.xls", writer, response);
    }
}