java poi excel导出导入

最新推荐文章于 2022-05-17 16:26:14 发布

农夫山泉不加糖

最新推荐文章于 2022-05-17 16:26:14 发布

阅读量163

点赞数

分类专栏： java 文章标签： excel poi java

本文链接：https://blog.csdn.net/weixin_43109130/article/details/116206762

版权

java 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

这段代码展示了如何使用Java的POI库来导出和导入Excel数据字典。导出功能创建了一个包含模板数据的工作簿，包括名称、编号、类型和排序等必填字段。导入功能从上传的Excel文件中读取数据，解析为数据字典实体并保存。整个过程涉及到文件流处理、单元格操作以及数据验证。

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

java poi excel导出导入

1.导出excel模板

  /**
     * 导出模板
     *
     * @param request
     * @return
     */
    @PostMapping("/getDictionaryTemplate")
    @ResponseBody
    @ApiOperation("导出模板")
    @ControllerLog(description = "导出模板", logLevel = 6)
    public ResponseResult getDictionaryTemplate(HttpServletResponse response, @RequestBody Map<String, Object> request) {
        return dictionaryService.getDictionaryTemplate(response, request);
    }

 @Override
    public ResponseResult getDictionaryTemplate(HttpServletResponse response, Map<String, Object> request) {
        // 获取所有数据
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("operateLog");
        // 创建表头
        createTempWorkBookHead(sheet, workbook);
        createExcelData(response, workbook, sheet);
        return ResponseResult.general(200, "导出模板成功", "true");
    }


    private void createTempWorkBookHead(Sheet sheet, Workbook workbook) {
        int head = 0;
        Row row = sheet.createRow(0);
        Cell cell = row.createCell(head++);
        cell.setCellValue("名称（必填）");
        cell = row.createCell(head++);
        cell.setCellValue("编号（必填，本次插入数据不得重复，假如此批数据已存在数据库，根据编码做更新操作，没有则插入）");
        cell = row.createCell(head++);
        cell.setCellValue("类型（必填）");
        cell = row.createCell(head++);
        cell.setCellValue("排序（必填）");
    }

private void createExcelData(HttpServletResponse response, Workbook workbook, Sheet sheet) {
        int cellIndex = 0;
        Row row = sheet.createRow(1);
        Cell cell = row.createCell(cellIndex++);
        cell.setCellValue("插件");
        cell.setCellStyle(FileUtil.genContextStyle((XSSFWorkbook) workbook));

        cell = row.createCell(cellIndex++);
        cell.setCellValue("8527");
        cell.setCellStyle(FileUtil.genContextStyle((XSSFWorkbook) workbook));

        cell = row.createCell(cellIndex++);
        cell.setCellValue("key");
        cell.setCellStyle(FileUtil.genContextStyle((XSSFWorkbook) workbook));

        cell = row.createCell(cellIndex++);
        cell.setCellValue("1");
        cell.setCellStyle(FileUtil.genContextStyle((XSSFWorkbook) workbook));
        FileUtil.createTempFile(response, workbook, "dictionaryTemplate.xlsx");
    }

	/**
     * 
     *
     * @param response
     * @param workbook 文件对象
     * @param fileName 文件名称
     * @return
     */
    public static boolean createTempFile(HttpServletResponse response, Workbook workbook, String fileName) {
        try {
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.setContentType("application/octet-stream");
            workbook.write(response.getOutputStream());
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

2.导入

    /**
     * 导入
     *
     * @param
     * @return
     */
    @PostMapping("/importDictionary")
    @ResponseBody
    @ApiOperation("导入")
    @ControllerLog(description = "导入", logLevel = 6)
    public ResponseResult importDictionary(@RequestParam("file")MultipartFile file) {
        return dictionaryService.importDictionary(file);
    }

  @Override
    public ResponseResult importDictionary(MultipartFile multipartFile) {
        try {
            return parseFileList(multipartFile);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return ResponseResult.general(200, "导入数据字典失败", false);
    }

private ResponseResult parseFileList(MultipartFile file) throws IOException {
        //读取导入的excel文件
        XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
        XSSFSheet sheet = workbook.getSheetAt(0);
        int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
        //用户信息
        DictionaryEntity dictionaryEntity;
        List<DictionaryEntity> dictionaryEntities = new ArrayList<>();
        for (int i = 1; i < physicalNumberOfRows; i++) {
            int rowIndex = 0;
            String dictId = UUID.randomUUID().toString();
            XSSFRow row = sheet.getRow(i);
            dictionaryEntity = new DictionaryEntity();
            //读取导入文件，解析数据
            //用户名称(必填)、所属部门（必填，部门以“\”区分上下级，单位以[某单位]标注、联系方式、备注、职务、行政区码、机构编码、密级（绝密，机密，秘密，内部，公开）
            row.getCell(rowIndex).setCellType(CellType.STRING);
            String dictName = row.getCell(rowIndex++).getStringCellValue();
            row.getCell(rowIndex).setCellType(CellType.STRING);
            String dictCode = row.getCell(rowIndex++).getStringCellValue();
            row.getCell(rowIndex).setCellType(CellType.STRING);
            String dictType = row.getCell(rowIndex++).getStringCellValue();
            row.getCell(rowIndex).setCellType(CellType.STRING);
            String dictOrders = row.getCell(rowIndex++).getStringCellValue();
            dictionaryEntity.setDictId(dictId);
            dictionaryEntity.setDictName(dictName);
            dictionaryEntity.setDictCode(dictCode);
            dictionaryEntity.setDictType(dictType);
            dictionaryEntity.setDictOrders(dictOrders);
            dictionaryEntity.setDictDisplay("1");
            dictionaryEntity.setCreateTime(String.valueOf(System.currentTimeMillis() / 1000));
            dictionaryEntity.setModifyTime(String.valueOf(System.currentTimeMillis() / 1000));
            dictionaryEntities.add(dictionaryEntity);
        }
        // dictionaryEntities 数据进行解析操作即可
        if (CollectionUtils.isEmpty(dictionaryEntities)) {
            return ResponseResult.general(200, "无数据，导入数据字典失败", false);
        }
    }