Java 使用hutool工具进行导出导入excel表格（代码很简单）

一时失言乱红尘 2024-03-17 00:36 112阅读 0赞

## **1.先引入hutool的相关依赖** ##

<dependency>
                <groupId>cn.hutool</groupId>
                <artifactId>hutool-all</artifactId>
                <version>5.8.4</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>4.1.2</version>
            </dependency>

## 2.导出 ##

#### 创建一个Controller进行测试 ####

@GetMapping(path = "/export")
        public void export(HttpServletResponse response,OrderPageRequest orderPageRequest)throws IOException { {
                // 查询导出订单数据
                List<OrdereExportView> ordereList = getOrdereList(orderPageRequest);
                if (CollectionUtils.isEmpty(ordereList)) {
                    throw new BusinessException("暂无数据！");
                }
                //在内存操作，写到浏览器
                ExcelWriter writer = ExcelUtil.getWriter(true);
    
                //自定义标题别名
                writer.addHeaderAlias("orderCode", "订单编码");
                writer.addHeaderAlias("addressName", "送达方名称");
                writer.addHeaderAlias("amount", "订单金额");
                writer.addHeaderAlias("orderStatusDesc", "订单状态");
                writer.addHeaderAlias("createTime", "创建时间");
                writer.addHeaderAlias("submitTime", "提交时间");
                writer.addHeaderAlias("createTime", "创建时间");
        
                //只保留别名的数据
                writer.setOnlyAlias(true);
                // 默认配置
                writer.write(ordereList, true);
                // 设置单元格样式
                StyleSet style = writer.getStyleSet();
                CellStyle numberStyle = style.getCellStyleForNumber();
                DataFormat dataFormat = writer.getWorkbook().createDataFormat();
                // 保留3位小数
                numberStyle.setDataFormat(dataFormat.getFormat("0.000"));
                for (int i = 0; i < ordereList.size() + 1; i++) {
                    writer.setStyle(numberStyle, 3, i);
                }
    
                // 设置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();
          
        }

## 3.导出效果 ##

![9353ac38c8464d05b3563882f1e1ef9b.png][]

### 4.Excel 表格导入 实现 ###

![259515ec12e3463d96e19f30de9c4be5.png][]

@PostMapping(path="/import")
        public List<OrderItemResponse> importData(@RequestParam("file") MultipartFile file) throws IOException {
            //字节输入流
            InputStream inputStream = file.getInputStream();
            //通过输入流创建ExcelReader 对象
            ExcelReader reader = ExcelUtil.getReader(inputStream);
            // 获取模板第一行第一列判断模板是否正确
            if (!"*客户物料号".equals(reader.getOrCreateRow(0).getCell(0).toString())) {
               throw new BusinessException("请按正确的模板导入!");
            }
            // 导入模板的列名要跟这个一致 不然字段是映射不上
            reader.addHeaderAlias("*客户物料号","customerSkuCode");
            reader.addHeaderAlias("*物料号","skuCode");
            reader.addHeaderAlias("*数量","quantity");
            reader.addHeaderAlias("*单位","unitDesc");
    
            List<OrderItemResponse> list = reader.readAll(OrderItemResponse.class);
            for (int i = 0; i < list.size(); i++) {
              OrderItemResponse item = list.get(i);
              // 设置模板行号 用于错误提示
              item .setImportNo(i+2);
              String msg = "";
              // 校验必填
              if (CollectionUtils.isNull(item.getSkuCode()){
                   msg = "客户物料号不能为空;";
                }
              if (CollectionUtils.isNull(item.getUnitDesc())) {
                   msg = msg + "数量单位不能为空;";
               }
              item.setErrorMsg(msg);
             }
            
            // 保存
            service.BatchAdd(list);
            // 把集合返回 用于前端展示成功行/失败行
            return list;
        }

[9353ac38c8464d05b3563882f1e1ef9b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/630bfef339154985862870b7009b336b.png
[259515ec12e3463d96e19f30de9c4be5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/45712f4ae7c6447d8a80fd1bfe6bc519.png