hutool工具使用：EXECL的导入导出

最新推荐文章于 2024-05-16 19:28:54 发布

Wei_Jian_

最新推荐文章于 2024-05-16 19:28:54 发布

阅读量305

点赞数

文章标签： java servlet

本文链接：https://blog.csdn.net/Wei_Jian_/article/details/125958959

版权

这篇博客介绍了如何在Java中利用Hutool和Apache POI库来实现Excel数据的导出和导入功能。首先，通过添加相关依赖，然后展示了如何导出数据，包括设置单元格宽度、高度，转换字典值并写入到Excel文件。接着，演示了数据的导入过程，读取Excel文件，进行字典值转换，并将数据保存到数据库。整个过程中涉及到字典映射、数据处理和文件流操作。

摘要由CSDN通过智能技术生成

1.引入依赖

   <dependency>
                <groupId>cn.hutool</groupId>
                <artifactId>hutool-all</artifactId>
                <version>5.1.0</version>
            </dependency>
            <!-- Excel导出 -->
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>3.17</version>
            </dependency>

2.导出功能实现

先参考Hutool参考文档

   @RequestMapping(value = "excle")
    @ResponseBody
    public void excle(HttpServletRequest request, LawInspectRecord lawInspectRecord, RedirectAttributes redirectAttributes, HttpServletResponse response) {
        Date date = new Date();
        lawInspectRecord.setInspectType("1");
        SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmm");
        try {
            Page<LawInspectRecord> page = lawInspectRecordService.findPages(new Page<LawInspectRecord>(request, response), lawInspectRecord);
            //查出所有数据
            List<LawInspectRecord> Sale = page.getList();
            ExcelWriter writer = ExcelUtil.getWriter(true);
            writer.setColumnWidth(-1, 30);
            writer.setRowHeight(-1, 20);
            //     String fileName = "订单信息数据"+format.format(date);
            //      FileOutputStream output = new FileOutputStream("D:\\data"+fileName+".xls");
            //存储循环后的数据
            List<LawInspectRecord> lis = new ArrayList<>();
            Sale.forEach(s -> {
                /*进行字典值转换*/
                String res = lawInspectRecordService.findByresult(s.getResult());
                String color = lawInspectRecordService.findByColor(s.getHpys());
                String carType = lawInspectRecordService.findByCartype(s.getVehicleType());
                String fuelType = lawInspectRecordService.findByFueltype(s.getRlzl());
                s.setVehicleType(carType);
                s.setRlzl(fuelType);
                s.setResult(res);
                s.setHpys(color);
                lis.add(s);

            });
               //指定对应元素对应列
            writer.addHeaderAlias("motorOwner", "机动车所有人");
           //未指定的元素是否展示
            writer.setOnlyAlias(true);
            writer.write(lis);
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=road_check" + System.currentTimeMillis() + ".xlsx");
            ServletOutputStream out = response.getOutputStream();
            writer.flush(out, true);
            // 一次性写出内容

            //     writer.flush(out);
            // 关闭writer，释放内存
            writer.close();
            IoUtil.close(out);
            logger.info("__________数据导出成功!");

            /* return 
        } catch (Exception e) {
            e.printStackTrace();
            logger.info("数据导出失败!");
        }
        addMessage(redirectAttributes, "数据导出成功!");
 

    }

3.导入功能

 @RequestMapping("importXls")
    @ResponseBody
    public ApiResponse importXls(@RequestParam(value = "impfile", required = false) MultipartFile file, HttpServletRequest request, OverVehicle overVehicler, RedirectAttributes redirectAttributes) {
        try {
            InputStream bookStream = file.getInputStream();

          //  String sbcsId = ObdUtils.getSbcsId(UserUtils.getUser().getId());

            ExcelReader xls = ExcelUtil.getReader(bookStream);
            if(xls!=null&&xls.getRowCount()>0) {

                      //指定导入列对应元素，用map存储
                HashMap<String, String> headerAlias = new HashMap<>();
                headerAlias.put("车牌号", "vehicleNumber");
      
                headerAlias.put("车主电话", "vehicleHosterPhone");
          //      String update_flag=UserUtils.getUser().getLoginName()+"-"+ DateUtil.now();
                xls.setHeaderAlias(headerAlias);
                  //拿到元素后用list存储
                List<OverVehicle> list = xls.read(0, 1, OverVehicle.class);
               list.forEach(s->{
                     //字典值转换
                   s.setDelFlag("0");
                   if(StringUtils.isNotBlank(s.getFuelType())){
                    String  fuelType= s.getFuelType();
                    String fueltt=   overVehicleService.findBytype(fuelType);
                    s.setFuelType(fueltt);
                   }
                   if(StringUtils.isNotBlank(s.getVehicleType())){
                       String  hicle= s.getVehicleType();
                    String vehicleT=   overVehicleService.findByvehicleType(hicle);
                    s.setVehicleType(vehicleT);
                   }
                   if(StringUtils.isNotBlank(s.getEmissionStandard())){
                       String  hicl= s.getEmissionStandard();
                    String mission=   overVehicleService.findByStandard(hicl);
                    s.setEmissionStandard(mission);
                   }
                    //存入数据库
                   logger.info("2222222----"+s);
                   overVehicleService.save(s);
               });
                    return ApiResponse.ok("ok", list.size());

            }
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return ApiResponse.error("error");

    }