java导入导出文档

controller层

	@RequestMapping("/exportAccount")
    public String exportFile(BzUnclaimedClearance bzUnclaimedClearance, String[] ids,RedirectAttributes redirectAttributes, HttpServletRequest request, HttpServletResponse response) {
       //需要导出的数据
        List<BzUnclaimedClearance> bzUnclaimedClearances = bzUnclaimedClearanceService.getprintnAccount(ids);
        //日期格式
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
        //创建新的来存导出数据的实体类
        List<BzUnclaimedExportAccount> bzUnclaimedExportAccounts = new ArrayList<>();
        //遍历查出来的数据
        if(bzUnclaimedClearances.size()>0 && bzUnclaimedClearances != null){
           Integer i = 1;
           for (BzUnclaimedClearance u : bzUnclaimedClearances){
               //创建新的对象来存值
               BzUnclaimedExportAccount b = new BzUnclaimedExportAccount();
               //判断不为空的话就赋值给他
               if (u.getSerialNumber()!=null){
                   b.setSerialNumber(u.getSerialNumber());
               }
               if (u.getActualName()!=null){
                   b.setActualName(u.getActualName());
               }
               if (u.getClearanceName()!=null){
                   b.setClearanceName(u.getClearanceName());
               }
               if (u.getTransportContact()!=null){
                   b.setTransportContact(u.getTransportContact());
               }
               if (u.getUnitName()!=null){
                   b.setUnitName(u.getUnitName());
               }
               if(u.getDocumentsNo()!=null){
                   b.setDocumentsNo(u.getDocumentsNo());
               }
               if (u.getAmount()!=null){
                   b.setAmount(u.getAmount());
               }
               if (u.getClassificationDate()!=null){
                   b.setClassificationDate(dateFormat.format(u.getClassificationDate()));
               }
               if (u.getDeathDate()!=null){
                   b.setDeathDate(dateFormat.format(u.getDeathDate()));
               }
               if (u.getActualFuneralDate()!=null){
                   b.setActualFuneralDate(dateFormat.format(u.getActualFuneralDate()));
               }
               if (u.getSkeletonType()!=null){
                   b.setSkeletonType(u.getSkeletonType());
               }
               if (u.getManualNum()!=null){
                   b.setManualNum(u.getManualNum());
               }
               if (u.getPaymentDate()!=null){
                   b.setPaymentDate(dateFormat.format(u.getPaymentDate()));
               }
               //序号
               b.setNumber(i.toString());
               i++;
               //存入集合里面
               bzUnclaimedExportAccounts.add(b);
           }

        }
        String fileName = "统计表" + DateUtils.getDate("yyyyMMdd") + ".xlsx";
        String[] titleName = {"统计表"};
        String[] sheetName = {"统计表"};
        ExportMultiExcel exportMultiExcel = new ExportMultiExcel(titleName, sheetName, BzUnclaimedExportAccount.class);

        //给导出页面赋值
        exportMultiExcel.setDataList(bzUnclaimedExportAccounts);

        try {
            exportMultiExcel.write(response,fileName).dispose();
        }catch (IOException e){
            addMessage(redirectAttributes,"导出统计表！失败信息"+e.getMessage());
            e.printStackTrace();
        }

        return null;
    }

• 创建新的导出实体类

/**
 * @author lwh
 */
public class BzUnclaimedExportAccount extends DataEntity<BzUnclaimedExportAccount> {

    @ExcelField(title ="序号", align = 2, sort = 1  )//这里需要导出的就加上注解，如果有类型字典的加上注解dictType = "sex"填写对应的字典值，导出即可
    private String number;

    @ExcelField(title ="编号", align = 2, sort = 2)
    private String serialNumber;

    @ExcelField(title ="姓名", align = 2, sort = 3)
    private String actualName;

    private String sex;

    private String age;

    //清仓名称
    @ExcelField(title ="名称", align = 2, sort = 4)
    private String clearanceName = "认领";

    //接运联系
    @ExcelField(title ="接运联系", align = 2, sort = 5)
    private String transportContact;

    //单位名称
    private String  unitName;

    @ExcelField(title ="收费单据号", align = 2, sort = 6)
    private String documentsNo;

    @ExcelField(title ="单据金额", align = 2, sort = 7)
    private BigDecimal amount = BigDecimal.ZERO;

    //分类日期
    @JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")
    @ExcelField(title ="分类日期", align = 2, sort = 8)
    private String classificationDate;

easypoi

    @Override
    public void exportEquipmentPatrol(HttpServletResponse response, String[] ids) throws Exception {
        String fileName = "设备巡检信息数据报表";
        String tPath = "static/public/templates/exportDemo/excel/巡检导出.xlsx";
//        List<EquipmentPatrol> data = baseMapper.selectListExport();
        List<EquipmentPatrol> data = lambdaQuery().in(EquipmentPatrol::getId, ids).list();
        List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();
        Map<String, Object> fieldDataSource = new HashMap<String, Object>();
        for (EquipmentPatrol vo : data) {
            Map<String, Object> dataMap = MapUtil.toMap(vo);
            if (dataMap.get("patrolTime") != null) {
                dataMap.put("patrolTime", DateUtil.parseString((LocalDateTime) dataMap.get("patrolTime"), "yyyy-MM-dd HH:mm:ss"));
            }
            dataList.add(dataMap);
        }
        ExportResult er = export(tPath, dataList, fieldDataSource);
        InputStream in = new ByteArrayInputStream(er.getExportStream().toByteArray());
        XSSFWorkbook workbook = new XSSFWorkbook(in);
        response.setContentType(ContentType.CONTENT_TYPE_XLS);
        ServletOutputStream out = response.getOutputStream();
        response.setHeader("Content-disposition", "attachment; filename=\"" + WordUtil.changeNameToDownload(fileName + "-" + DateUtil.parseString(LocalDateTime.now(), "yyyy-MM-dd") + ".xlsx") + "\"");
        workbook.write(out);
        out.flush();
        out.close();
    }

导入execl数据

• 获取execl导入数据

    @Override
    public List<Map<String, String>> subtleImport(MultipartFile file) {
        InputStream inputStream = null;
        try {
            String filename = file.getOriginalFilename();
            if (filename == null) {
                throw new NullArgumentException();
            }
            inputStream = file.getInputStream();
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
            List<Map<String, String>> maps = new ArrayList<>();
            Map<String, String> headMap = new HashMap<>();
            int i = 0;
            for (Sheet xssfSheet : xssfWorkbook) {
                if (xssfSheet == null) {
                    continue;
                }
                //处理当前页，循环读取每一行
                for (int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
                    Map<String, String> dataMap = new HashMap<>();
                    Row xssfRow = xssfSheet.getRow(rowNum);
                    int minColIx = xssfRow.getFirstCellNum();
                    int maxColIx = xssfRow.getLastCellNum();
                    List<String> rowList = new ArrayList<>();
                    //遍历行，获取cell元素
                    for (int colIx = minColIx; colIx < maxColIx; colIx++) {
                        XSSFCell cell = (XSSFCell) xssfRow.getCell(colIx);
                        if (cell == null) {
                            continue;
                        }
                        if (rowNum == 0) {
                            headMap.put(String.valueOf(i), cell.toString());
                            i++;
                        }
                        rowList.add(cell.toString());
                        if (rowNum > 0) {
                            String key = headMap.get(String.valueOf(colIx));
                            String[] split = key.split("\\|");
                            //如果值为整数类型，去除.0
                            if (split.length>1 && cell.getCellType() == CellType.NUMERIC){
                                double numericValue = cell.getNumericCellValue();
                                DecimalFormat decimalFormat = new DecimalFormat("0.####");
                                String formattedValue = decimalFormat.format(numericValue);
                                dataMap.put(split[1], formattedValue);
                            }else {
                                dataMap.put(split[1], String.valueOf(cell));
                            }
                        }
                    }
                    if (rowNum > 0) {
                        maps.add(dataMap);
                    }
                }
            }
            return maps;
        } catch (IOException e) {
            throw new RuntimeException("导入数据失败"+e.getMessage());
        }finally {
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

xlxs导出（多级表头）

controller

  /**
     * 子表导出
     *
     * @param exportRequest 导出请求
     * @author 梁伟浩
     * @since 2023/8/8 16:14
     */
    @PostMapping("/subtleExport")
    @ApiOperation(value = "子表导出")
    public void exp(@RequestBody SubtleExportRequest exportRequest, HttpServletResponse response) {
        OutputStream outputStream = null;
        //表单名称
        String fileName = null;
        try {
            String fileNames = exportRequest.getFileName();
            if (Objects.isNull(fileNames)){
                fileName = "子表"+ ".xlsx";
            }else {
                fileName = fileNames +"子表" + ".xlsx";
            }
            response.reset();
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ";filename*=UTF-8''" + URLEncoder.encode(fileName, "UTF-8"));
            outputStream = response.getOutputStream();

            formService.subtleExport(exportRequest, outputStream);
        } catch (IOException e){
            LOGGER.error(e.getMessage(), e);
            response.setStatus(HttpServletResponse.SC_BAD_REQUEST);
            try {
                if (outputStream != null) {
                    outputStream.write(JSON.toJSONString(R.fail(e.getMessage())).getBytes());
                }
            } catch (IOException ex) {
                throw new RuntimeException(ex);
            }
        }finally {
            try {
                if (outputStream!=null){
                    outputStream.close();
                }
            }catch (IOException e){
                throw new RuntimeException();
            }
        }
    }

service

    /**
     * 子表导出
     *
     * @param exportRequest 导出请求
     * @param outputStream 输出流
     * @author 梁伟浩
     * @since 2023-08-08
     */
    void subtleExport(SubtleExportRequest exportRequest, OutputStream outputStream);

serviceimpl

    @Override
    public void subtleExport(SubtleExportRequest exportRequest, OutputStream outputStream) {
        //获取表头数据
        List<Map<String, Object>> columnList = exportRequest.getColumnList();

        List<String> parentLabels = new ArrayList<>();
        List<List<String>> headList = new ArrayList<>();
        // 递归构造表头
        List<List<String>> lists = constructHeader(columnList, headList, 0, parentLabels);
        //构建数据
        LinkedHashMap<String, String> customField = new LinkedHashMap<>();
        LinkedHashMap<String, String> lastChildProperties = this.getLastChildProperties(columnList, customField);
        //把构建好的多级表头传过去
        Map<String, Object> vars = new HashMap<>();
        vars.put("subtleExport", lists);
        //调用prints的export导出
        printService.export(lastChildProperties, outputStream, exportRequest.getDataList(), null, null,null, vars);
    }

调用公共导出方法

 /**
     * @param customField  表头数据
     * @param outputStream 输出流
     * @param templatePath 模板路径
     * @param extension    模板类型
     * @param dataList     数据
     * @description: 箱列表导出
     * @author lwh
     * @date: 2023/8/7 9:36
     */
    @Override
    public void export(LinkedHashMap<String, String> customField, OutputStream outputStream, List<Map<String, Object>> dataList, String templatePath, String extension, InputStream inputStream,Map<String, Object> vars) {
        try {
            //无模板
            if (Objects.isNull(templatePath)) {
                if (MapUtil.isEmpty(customField)) {
                    throw new RuntimeException("无模板打印情況下，自定义字段[customField]不能为空");
                }
                Set<String> fieldKeys = customField.keySet();
                //构建表头
                List<List<String>> heads = new ArrayList<>();
                //多级表头（子表导出需要多级表头）
                if (vars != null && (vars.containsKey("subtleExport"))) {
                    //获取多级表头（已在调用存在额外参数vars中）
                    heads = (List<List<String>>) vars.get("subtleExport");
                } else {
                    for (String fieldKey : fieldKeys) {
                        String fieldName = customField.get(fieldKey);
                        //是否为子表或者无审批方案台账导出，格式（姓名|xm）
                        if (vars != null && (vars.containsKey("planLedgerExport"))) {
                            StringBuffer sb = new StringBuffer();
                            sb.append(fieldName + ("|") + fieldKey);
                            heads.add(Collections.singletonList(sb.toString()));
                        } else {
                            heads.add(Collections.singletonList(fieldName));
                        }
                        if (Objects.isNull(fieldKey) || Objects.isNull(fieldName)) {
                            throw new RuntimeException("自定义字段信息不能为空");
                        }
                    }
                }
                List<List<Object>> datas = null;
                //时间转类型
                if (Objects.nonNull(dataList)) {
                    dataList.forEach(d -> {
                        d.forEach((key, value) -> {
                            if (d.get(key) instanceof Date) {
                                String time = FormatUtil.toUpper(d.get(key).toString(), null);
                                d.put(key, time);
                            }
                        });
                    });
                    //构建表数据
                    datas = dataList
                            .stream()
                            .map(dataMap -> fieldKeys.stream().map(k -> dataMap.get(k)).collect(Collectors.toList()))
                            .collect(Collectors.toList());
                }
                EasyExcel.write(outputStream)
                        .head(heads)
                        .registerWriteHandler(new SimpleColumnWidthStyleStrategy(30))
                        .registerConverter(new TimestampNumberConverter())
                        .registerConverter(new DateNumberConverter())
                        .autoCloseStream(false)
                        .sheet("数据")
                        .doWrite(datas);
            } else {
                //有模板
                if (templatePath == null) {
                    throw new RuntimeException("打印模板不存在");
                }
                if (!"xlsx".equals(extension)) {
                    throw new RuntimeException("只支持xlsx模板导出");
                }
                EasyExcel.write(outputStream)
                        .withTemplate(inputStream)
                        .registerConverter(new TimestampNumberConverter())
                        .registerConverter(new DateNumberConverter())
                        .autoCloseStream(false)
                        .sheet()
                        .doFill(dataList);
            }
        } catch (RuntimeException e) {
            e.printStackTrace();
            throw new RuntimeException("列表导出失败！" + e.getMessage());
        }
    }

• 多级表头封装数据