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

1.引入pom.xml依赖

简单版依赖

1.  
   <dependency>
2.  
   <groupId>cn.hutool</groupId>
3.  
   <artifactId>hutool-all</artifactId>
4.  
   <version>${hutool.version}</version>
5.  
   </dependency>
6.  
   <dependency>
7.  
   <groupId>org.apache.poi</groupId>
8.  
   <artifactId>poi-ooxml</artifactId>
9.  
   <version>5.2.2</version>
10.  
    </dependency>

复杂表的依赖

1.  
   <dependency>
2.  
   <groupId>org.apache.poi</groupId>
3.  
   <artifactId>poi-ooxml-schemas</artifactId>
4.  
   <version>4.1.2</version>
5.  
   </dependency>
6.  
   <!-- hutool-->
7.  
   <dependency>
8.  
   <groupId>cn.hutool</groupId>
9.  
   <artifactId>hutool-all</artifactId>
10.  
    <version>5.7.19</version>
11.  
    </dependency>
12.  
    <!-- poi-->
13.  
    <dependency>
14.  
    <groupId>org.apache.poi</groupId>
15.  
    <artifactId>poi-ooxml</artifactId>
16.  
    <version>5.0.0</version>
17.  
    </dependency>
18.  
    <dependency>
19.  
    <groupId>org.apache.poi</groupId>
20.  
    <artifactId>poi</artifactId>
21.  
    <version>4.0.1</version>
22.  
    </dependency>
23.  
    <dependency>
24.  
    <groupId>commons-net</groupId>
25.  
    <artifactId>commons-net</artifactId>
26.  
    <version>3.6</version>
27.  
    </dependency>

导出实现简单版

1.  
   //表格导出接口
2.  
   @GetMapping("/export")
3.  
   public void export(HttpServletResponse response) throws IOException {
4.  
   //查询所有用户
5.  
   List<UserEntity> list= userService.list();
6.  
    
7.  
   for (int i = 0; i < list.size(); i ) {
8.  
   list.get(i).setDeptName(deptService.selectById(list.get(i).getDeptId()).getName());
9.  
   }
10.  
     
11.  
     
12.  
    //在内存操作，写到浏览器
13.  
    ExcelWriter writer= ExcelUtil.getWriter(true);
14.  
    writer.renameSheet(0, "用户记录");
15.  
    //自定义标题别名
16.  
    writer.addHeaderAlias("name","用户名");
17.  
    writer.addHeaderAlias("email","邮箱");
18.  
    writer.addHeaderAlias("mobile","手机号");
19.  
    writer.addHeaderAlias("deptName","部门");
20.  
    // 只导出有别名的字段
21.  
    writer.setOnlyAlias(true);
22.  
    //默认配置
23.  
    writer.write(list,true);
24.  
    //设置content—type
25.  
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset:utf-8");
26.  
     
27.  
    //设置标题
28.  
    String fileName= URLEncoder.encode("用户信息","UTF-8");
29.  
    //Content-disposition是MIME协议的扩展，MIME协议指示MIME用户代理如何显示附加的文件。
30.  
    response.setHeader("Content-Disposition","attachment;filename=" fileName ".xlsx");
31.  
    ServletOutputStream outputStream= response.getOutputStream();
32.  
     
33.  
    //将Writer刷新到OutPut
34.  
    writer.flush(outputStream,true);
35.  
    outputStream.close();
36.  
    writer.close();
37.  
    }

复杂版

1.  
   @RequestMapping("/export")
2.  
   public void export1(HttpServletResponse response){
3.  
   List<UserEntity> list = new ArrayList<>();
4.  
    
5.  
    
6.  
   //传入数据
7.  
   List<UserEntity> userEntityList = userService.list();
8.  
    
9.  
    
10.  
    for (UserEntity user : userEntityList) {
11.  
    UserEntity obj = new UserEntity();
12.  
    obj.setId(user.getId());
13.  
    obj.setName(user.getName());
14.  
    obj.setEmail(user.getEmail());
15.  
    obj.setMobile(user.getMobile());
16.  
    obj.setStatus (user.getStatus());
17.  
    obj.setDeptId(user.getDeptId());
18.  
    obj.setCreateBy(user.getCreateBy());
19.  
    obj.setCreateTime(user.getCreateTime());
20.  
    obj.setLastUpdateBy(user.getLastUpdateBy());
21.  
    obj.setLastUpdateTime(user.getLastUpdateTime());
22.  
    obj.setDelFlag(user.getDelFlag());
23.  
    list.add(obj);
24.  
    }
25.  
     
26.  
    // 1.创建ExcelWriter
27.  
    // 通过工具类创建writer，默认创建xls格式
28.  
    ExcelWriter writer = ExcelUtil.getWriter();
29.  
    writer.renameSheet(0, "用户记录");
30.  
    //创建工作表
31.  
    //创建xlsx格式的
32.  
    //ExcelWriter writer = ExcelUtil.getWriter(true);
33.  
     
34.  
    // 2.设置一级标题
35.  
    // 合并单元格后的标题行，使用默认标题样式，从0开始
36.  
    // 设置表头高度
37.  
    writer.setRowHeight(0, 25);
38.  
     
39.  
    // 3.设置二级标题
40.  
    writer.addHeaderAlias("id", "编号");
41.  
    writer.addHeaderAlias("name", "用户名");
42.  
    writer.addHeaderAlias("email", "邮箱");
43.  
    writer.addHeaderAlias("mobile", "手机号");
44.  
    writer.addHeaderAlias("status", "状态 0：禁用 1：正常");
45.  
    writer.addHeaderAlias("deptId", "机构ID");
46.  
    writer.addHeaderAlias("createBy", "创建人");
47.  
    writer.addHeaderAlias("createTime", "创建时间");
48.  
    writer.addHeaderAlias("lastUpdateBy", "更新人");
49.  
    writer.addHeaderAlias("lastUpdateTime", "更新时间");
50.  
    writer.addHeaderAlias("delFlag", "是否删除 -1：已删除 0：正常");
51.  
     
52.  
     
53.  
    // 4.设置表头字体
54.  
    // 获取表头样式，获取样式后可自定义样式
55.  
    CellStyle headCellStyle = writer.getHeadCellStyle();
56.  
    // 获取单元格样式
57.  
    // CellStyle cellStyle = excelWriter.getCellStyle();
58.  
    // 设置内容字体
59.  
    Font font = writer.createFont();
60.  
    // 设置字体
61.  
    font.setFontName("宋体");
62.  
    // 设置字体大小
63.  
    font.setFontHeightInPoints((short) 14);
64.  
    // 字体加粗
65.  
    font.setBold(true);
66.  
    // 字体颜色
67.  
    font.setColor(Font.SS_NONE);
68.  
    headCellStyle.setFont(font);
69.  
     
70.  
    // 5.设置单元格宽度
71.  
    int[] arr = {30, 30, 25};
72.  
    for (int i = 0; i < arr.length; i ) {
73.  
    writer.setColumnWidth(i, arr[i]);
74.  
    }
75.  
    writer.merge(list.size()-2, "员工信息表");
76.  
     
77.  
    // 只导出有别名的字段
78.  
    writer.setOnlyAlias(true);
79.  
     
80.  
    // 一次性写出内容，使用默认样式，强制输出标题
81.  
    writer.write(list, true);
82.  
     
83.  
    // 从第几行写入
84.  
    // excelWriter.setCurrentRow(1);
85.  
    // excelWriter.writeRow(data());
86.  
    // 设置某个单元格的样式
87.  
    // CellStyle orCreateCellStyle = excelWriter.getOrCreateCellStyle(0, 1);
88.  
    // 设置某行的样式
89.  
    // excelWriter.setRowStyle();
90.  
    ServletOutputStream out = null;
91.  
    SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
92.  
    try {
93.  
    String fileName = URLEncoder.encode(dateFormat.format(new Date()) "用户信息表" , StandardCharsets.UTF_8.name());
94.  
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
95.  
    response.setCharacterEncoding("utf-8");
96.  
    response.setHeader("Content-disposition", "attachment;filename*=utf-8''" fileName ".xlsx");
97.  
    out = response.getOutputStream();
98.  
    // 将Excel Workbook刷出到输出流
99.  
    writer.flush(out, true);
100.  
      
101.  
     } catch (IOException e) {
102.  
     e.printStackTrace();
103.  
     throw new RuntimeException("文件写入失败！");
104.  
     } finally {
105.  
     // 记住关流
106.  
     IoUtil.close(writer);
107.  
      
108.  
     IoUtil.close(out);
109.  
     }
110.  
     }

简单版导入实现

1.  
   /**
2.  
   * 导入excel
3.  
   * @param file
4.  
   */
5.  
   @PostMapping("/import")
6.  
   public String importExcel(@RequestParam("file") MultipartFile file) throws Exception {
7.  
   if (file.isEmpty()) {
8.  
   System.out.println("文件为空！");
9.  
   return "文件为空";
10.  
    }
11.  
     
12.  
    // 1.获取上传文件输入流
13.  
    InputStream inputStream = null;
14.  
    try {
15.  
    //文件处理成io流
16.  
    inputStream = file.getInputStream();
17.  
    } catch (Exception e) {
18.  
    e.printStackTrace();
19.  
    }
20.  
     
21.  
    // //io流给ExcelReader
22.  
    ExcelReader excelReader=ExcelUtil.getReader(inputStream);
23.  
     
24.  
    // 从第二行开始获取数据 excelReader.read的结果是一个2纬的list，外层是行，内层是行对应的所有列
25.  
    List<List<Object>> list = excelReader.read(2, excelReader.getRowCount());
26.  
    List<UserEntity> listUser = CollUtil.newArrayList();
27.  
    for (List<Object> row: list) {
28.  
    UserEntity user=new UserEntity();
29.  
    user.setName(row.get(0).toString());
30.  
    user.setEmail(row.get(1).toString());
31.  
    user.setMobile(row.get(2).toString());
32.  
    user.setDeptId(deptService.selectByName(row.get(3).toString()).getId());
33.  
    listUser.add(user);
34.  
    // ****类似一个一个地对应****
35.  
    }
36.  
    //批量注册进数据库
37.  
    userService.saveBatch(listUser);
38.  
    return "导入成功";
39.  
    }

复杂版导入实现

1.  
   // 处理文件上传
2.  
   @PostMapping("/excelImport")
3.  
   public String uploadImg(@RequestParam("file") MultipartFile file, HttpServletRequest request) throws Exception {
4.  
    
5.  
   System.out.println(file);
6.  
    
7.  
   if (file.isEmpty()) {
8.  
   System.out.println("文件为空！");
9.  
   return "文件为空";
10.  
    }
11.  
     
12.  
    // 1.获取上传文件输入流
13.  
    InputStream inputStream = null;
14.  
    try {
15.  
    inputStream = file.getInputStream();
16.  
    } catch (Exception e) {
17.  
    // return ResponseData.fail(ResponseCodeEnum.ERROR_PARAM_INVALID);
18.  
    e.printStackTrace();
19.  
    }
20.  
     
21.  
    // 调用用 hutool 方法读取数据 默认调用第一个sheet
22.  
    ExcelReader excelReader = ExcelUtil.getReader(inputStream);
23.  
    // 从第二行开始获取数据 excelReader.read的结果是一个2纬的list，外层是行，内层是行对应的所有列
24.  
    // 读取方式1
25.  
    List<List<Object>> read = excelReader.read(2, excelReader.getRowCount());
26.  
    List<UserEntity> excels = new ArrayList<>();
27.  
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
28.  
    // 循环获取的数据
29.  
    for (int i = 0; i < read.size(); i ) {
30.  
    List list = read.get(i);
31.  
    UserEntity excel = new UserEntity();
32.  
    //按照列获取
33.  
    excel.setName(list.get(1).toString());
34.  
    excel.setEmail(list.get(2).toString());
35.  
    excel.setMobile(list.get(3).toString());
36.  
    excel.setStatus(Integer.parseInt(list.get(4).toString()));
37.  
    excel.setDeptId(Long.parseLong(list.get(5).toString()));
38.  
    excel.setLastUpdateBy(list.get(6).toString());
39.  
    String s = list.get(7).toString();
40.  
    Date date = sdf.parse(s);
41.  
    excel.setCreateTime(date);
42.  
    excel.setLastUpdateBy(list.get(8).toString());
43.  
    excel.setLastUpdateTime(sdf.parse(list.get(9).toString()));
44.  
    excel.setDelFlag(Integer.parseInt(list.get(10).toString()));
45.  
    //强制类型转换
46.  
    //excel.setWeight(Double.parseDouble(list.get(3).toString()));
47.  
    //excel.setStatus(Integer.parseInt(list.get(5).toString()));
48.  
    excels.add(excel);
49.  
    }
50.  
    excels.forEach(
51.  
    index -> {
52.  
    System.out.println(index);
53.  
    });
54.  
     
55.  
    for (UserEntity excel : excels) {
56.  
    System.out.println(excel);
57.  
    userService.save(excel);
58.  
    }
59.  
    System.out.println("导入成功");
60.  
    return "导入成功";
61.  
    }

Hutool工具类

1.  
   import cn.hutool.core.io.IORuntimeException;
2.  
   import cn.hutool.poi.excel.ExcelWriter;
3.  
   import org.apache.poi.ss.usermodel.Cell;
4.  
   import org.apache.poi.ss.usermodel.Sheet;
5.  
   import org.apache.poi.ss.util.CellRangeAddress;
6.  
    
7.  
   import javax.servlet.ServletOutputStream;
8.  
   import javax.servlet.http.HttpServletResponse;
9.  
   import java.io.IOException;
10.  
    import java.io.OutputStream;
11.  
    import java.util.ArrayList;
12.  
    import java.util.List;
13.  
     
14.  
    /**
15.  
    * 功能: poi导出excel工具类
16.  
    *
17.  
    */
18.  
    public class PoiExcelUtil {
19.  
     
20.  
    /**
21.  
    * 合并单元格处理,获取合并行
22.  
    *
23.  
    * @param sheet
24.  
    * @return List<CellRangeAddress>
25.  
    */
26.  
    public static List<CellRangeAddress> getCombineCell(Sheet sheet) {
27.  
    List<CellRangeAddress> list = new ArrayList<>();
28.  
    // 获得一个 sheet 中合并单元格的数量
29.  
    int sheetmergerCount = sheet.getNumMergedRegions();
30.  
    // 遍历所有的合并单元格
31.  
    for (int i = 0; i < sheetmergerCount; i ) {
32.  
    // 获得合并单元格保存进list中
33.  
    CellRangeAddress ca = sheet.getMergedRegion(i);
34.  
    list.add(ca);
35.  
    }
36.  
    return list;
37.  
    }
38.  
     
39.  
    public static int getRowNum(List<CellRangeAddress> listCombineCell, Cell cell, Sheet sheet) {
40.  
    int xr = 0;
41.  
    int firstC = 0;
42.  
    int lastC = 0;
43.  
    int firstR = 0;
44.  
    int lastR = 0;
45.  
    for (CellRangeAddress ca : listCombineCell) {
46.  
    // 获得合并单元格的起始行, 结束行, 起始列, 结束列
47.  
    firstC = ca.getFirstColumn();
48.  
    lastC = ca.getLastColumn();
49.  
    firstR = ca.getFirstRow();
50.  
    lastR = ca.getLastRow();
51.  
    if (cell.getRowIndex() >= firstR && cell.getRowIndex() <= lastR) {
52.  
    if (cell.getColumnIndex() >= firstC && cell.getColumnIndex() <= lastC) {
53.  
    xr = lastR;
54.  
    }
55.  
    }
56.  
    }
57.  
    return xr;
58.  
    }
59.  
     
60.  
    /**
61.  
    * 判断指定的单元格是否是合并单元格
62.  
    *
63.  
    * @param sheet
64.  
    * @param row 行下标
65.  
    * @param column 列下标
66.  
    * @return
67.  
    */
68.  
    public static boolean isMergedRegion(Sheet sheet, int row, int column) {
69.  
    int sheetMergeCount = sheet.getNumMergedRegions();
70.  
    for (int i = 0; i < sheetMergeCount; i ) {
71.  
    CellRangeAddress range = sheet.getMergedRegion(i);
72.  
    int firstColumn = range.getFirstColumn();
73.  
    int lastColumn = range.getLastColumn();
74.  
    int firstRow = range.getFirstRow();
75.  
    int lastRow = range.getLastRow();
76.  
    if (row >= firstRow && row <= lastRow) {
77.  
    if (column >= firstColumn && column <= lastColumn) {
78.  
    return true;
79.  
    }
80.  
    }
81.  
    }
82.  
    return false;
83.  
    }
84.  
     
85.  
    /** 如果需要合并的话，就合并 */
86.  
    public static void mergeIfNeed(
87.  
    ExcelWriter writer,
88.  
    int firstRow,
89.  
    int lastRow,
90.  
    int firstColumn,
91.  
    int lastColumn,
92.  
    Object content) {
93.  
    if (lastRow - firstRow > 0 || lastColumn - firstColumn > 0) {
94.  
    writer.merge(firstRow, lastRow, firstColumn, lastColumn, content, false);
95.  
    } else {
96.  
    writer.writeCellValue(firstColumn, firstRow, content);
97.  
    }
98.  
    }
99.  
     
100.  
     public static void writeExcel(HttpServletResponse response, ExcelWriter writer) {
101.  
     // response为HttpServletResponse对象
102.  
     response.setContentType("application/vnd.ms-excel;charset=utf-8");
103.  
     // test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
104.  
     response.setHeader("Content-Disposition", "attachment;filename=1.xls");
105.  
      
106.  
     ServletOutputStream servletOutputStream = null;
107.  
     try {
108.  
     servletOutputStream = response.getOutputStream();
109.  
     writer.flush(servletOutputStream);
110.  
     servletOutputStream.flush();
111.  
     } catch (IORuntimeException | IOException e) {
112.  
     e.printStackTrace();
113.  
     } finally {
114.  
     writer.close();
115.  
     try {
116.  
     if (servletOutputStream != null) {
117.  
     servletOutputStream.close();
118.  
     }
119.  
     } catch (IOException e) {
120.  
     e.printStackTrace();
121.  
     }
122.  
     }
123.  
     }
124.  
      
125.  
     public static void writeExcel(String filename, ExcelWriter writer, HttpServletResponse response) {
126.  
     OutputStream ouputStream = null;
127.  
     try {
128.  
     filename = new String(filename.getBytes("UTF-8"), "ISO-8859-1");
129.  
     response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
130.  
     response.setHeader("Content-disposition", "attachment;filename=" filename);
131.  
     ouputStream = response.getOutputStream();
132.  
     Runtime.getRuntime().gc();
133.  
     writer.flush(ouputStream);
134.  
     ouputStream.flush();
135.  
     } catch (Exception e) {
136.  
     e.printStackTrace();
137.  
     } finally {
138.  
     if (null != ouputStream) {
139.  
     try {
140.  
     ouputStream.close();
141.  
     } catch (IOException e) {
142.  
     e.printStackTrace();
143.  
     }
144.  
     }
145.  
     }
146.  
     }
147.  
     }

控制器

1.  
   @GetMapping("downExl")
2.  
   public void downExl(HttpServletResponse response){
3.  
   List<TcUser> tcUsers = new ArrayList<>();
4.  
   TcUser tcUser1 = new TcUser(1, "这是1", "111a");
5.  
   TcUser tcUser2 = new TcUser(2, "这是2", "222b");
6.  
   TcUser tcUser3 = new TcUser(3, "这是3", "333c");
7.  
   tcUsers.add(tcUser1);
8.  
   tcUsers.add(tcUser2);
9.  
   tcUsers.add(tcUser3);
10.  
    // 商品导出or模板
11.  
    List<String> headerList;
12.  
    // 使用 hutool创建exl
13.  
    ExcelWriter writer = ExcelUtil.getBigWriter();
14.  
    String[] header = {"序号", "ID", "用户名称", "密码"};
15.  
    headerList = Arrays.asList(header);
16.  
    Sheet sheet = writer.getSheet();
17.  
    writer.merge(headerList.size() - 1, "测试exl");
18.  
    writer.writeRow(headerList);
19.  
    for (int i = 0; i < headerList.size(); i ) {
20.  
    if (i == 0 || i == 1 || i == 2 || i == 3 || i == 5 || i == 6 || i == 7 || i == 8 || i == 10 || i == 11) {
21.  
    sheet.setColumnWidth(i, 10 * 256);
22.  
    } else {
23.  
    sheet.setColumnWidth(i, 20 * 256);
24.  
    }
25.  
    }
26.  
    int row = 1;
27.  
    for (TcUser tcUser : tcUsers) {
28.  
    int firstRow = row 1;
29.  
    int lastRow = row 1;
30.  
    int col = -1;
31.  
    PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, col, col, row);
32.  
    PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, col, col, tcUser.getId());
33.  
    PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, col, col, tcUser.getUsername());
34.  
    PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, col, col, tcUser.getPassword());
35.  
    row ;
36.  
    }
37.  
    PoiExcelUtil.writeExcel("测试exl.xls", writer, response);
38.  
    }
39.  
    }

这篇好文章是转载于：学新通技术网