springboot easypoi 导入excel解析和导出替换word模板

xiaogg3678

已于 2023-06-14 10:02:32 修改

阅读量1.3k

点赞数 1

文章标签： spring boot 后端 java

于 2023-06-14 09:59:01 首次发布

本文链接：https://blog.csdn.net/xiaogg3678/article/details/131201597

版权

pom 引入：

<!-- 目前的版本对应  poi 4.1.2   和 xmlbeans 3.1.0   , poi 3.17   和 xmlbeans 2.6.0 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.xmlbeans</groupId>
            <artifactId>xmlbeans</artifactId>
            <version>3.1.0</version>
        </dependency>
        <!-- easypoi导出word -->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-spring-boot-starter</artifactId>
            <version>4.4.0</version>
        </dependency>

导入excel和解析表格:

@ApiOperation("系统功能-个人中心-系统管理-人员机构管理-批量新增人员下载excel模板")
    @PostMapping("/downWordExcelTemp")
    public String downWordExcelTemp(HttpServletResponse response) throws IOException {
        //获取文件地址
        //String realPath = "F:/plugin/";
        String fileName="批量创建智库账号信息.xlsx";
        File file = ResourceUtils.getFile("classpath:temp/batchCreateUser.xlsx");
        //把服务器中文件读取到内存中
        FileInputStream fis = new FileInputStream(file);
        //设置下载的类型
        response.setHeader("content-disposition","attachment;fileName="+ URLEncoder.encode(fileName,"UTF-8"));
        //获取输出流
        ServletOutputStream os = response.getOutputStream();
        //复制
        IOUtils.copy(fis,os);
        //关闭资源
        fis.close();
        os.close();
        return null;
    }


    /**
     * 系统功能-个人中心-系统管理-人员机构管理-excel模板批量新增人员信息
     * @param
     * @return
     */
    @ApiOperation("系统功能-个人中心-系统管理-人员机构管理-excel模板批量新增人员信息")
    @PostMapping(value ="/operateIdentyInformationXPAddBatch", consumes = MediaType.MULTIPART_FORM_DATA_VALUE)
    public CppResult operateIdentyInformationXPAddBatch(@RequestPart("file") MultipartFile multipartFile){
        ImportParams params = new ImportParams();
        params.setTitleRows(1);//标题占1行
        params.setHeadRows(1);//表头占1行
//      params.setStartSheetIndex(2);//从第几个sheet开始
//      params.setSheetNum(3);//读取几个sheet
        params.setImportFields(new String[]{"序号","*机构","*姓名","*性别","*身份/职位","权限","*手机号","邮箱","证件号码","账号状态","*密码"});//设置字段是否为合法的模板
        // 参数1：导入的excel文件（或者文件输入流）  参数2：导入对应的实体类型， 参数3：导入参数
        /*File file = null;
        try {
            file = ResourceUtils.getFile("classpath:temp/batchCreateUser.xlsx");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }*/
        List<UserAddBatch> users = null;
        try {
            users = ExcelImportUtil.importExcel(multipartFile.getInputStream(), UserAddBatch.class, params);
        } catch (Exception e) {
            e.printStackTrace();
        }
        for (UserAddBatch userAdd:users) {
            System.out.println("userAdd:"+userAdd);
            if(StringUtils.isNotBlank(userAdd.getName())){
                OperateIdentifyInformationXPReq operateIdentifyInformationXPReq=new OperateIdentifyInformationXPReq();
                operateIdentifyInformationXPReq.setType(1);
                List<PersonnelInstitution> personnelInstitutionList= new ArrayList<>();
                PersonnelInstitution personnelInstitution=new PersonnelInstitution();
                BeanUtils.copyProperties(userAdd,personnelInstitution);
                //填充持股比例
                personnelInstitution.setShareRatio("0%");
                personnelInstitution.setiDType(1);//身份证
                //填充身份
                List<String> identityList=new ArrayList<>();
                identityList.add(userAdd.getIdentityListStr());
                personnelInstitution.setIdentityList(identityList);
                // 填充机构
                List<AffiliationGroup> affiliationGroupList=new ArrayList<>();
                String affiliationGroupStr = userAdd.getAffiliationGroupListStr();
                AffiliationGroup affiliationGroup=new AffiliationGroup();
                affiliationGroup.setId(AffiliationGroupEnum.getNameByCode(affiliationGroupStr));
                affiliationGroupList.add(affiliationGroup);
                personnelInstitution.setAffiliationGroupList(affiliationGroupList);
                //填充权限
                CompanyPermissionGroup permissionGroup=new CompanyPermissionGroup();
                permissionGroup.setId(PermissionGroupEnum.getNameByCode(userAdd.getPermissionGroup()));
                personnelInstitution.setPermissionGroup(permissionGroup);

                personnelInstitutionList.add(personnelInstitution);
                operateIdentifyInformationXPReq.setIdentyInformation(personnelInstitutionList);
                // 单个新增接口
                userService.operateIdentifyInformationXPAdd(operateIdentifyInformationXPReq);
            }else{
                break;
            }
        }
        return success();
    }




package com.realize.user.domain.qos;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.Serializable;
import java.util.List;

@Data
@NoArgsConstructor
@AllArgsConstructor
@ExcelTarget("userAddBatch")
public class UserAddBatch implements Serializable {

    @Excel(name="序号")
    private String id;

    @Excel(name="*机构")
    private String affiliationGroupListStr;

    @Excel(name="*姓名")
    private String name;

    @Excel(name="*性别", replace = {"男_1", "女_2"})
    private Integer gender;

    @Excel(name="*身份/职位")
    private String identityListStr;

    @Excel(name="权限")
    private String permissionGroup;

    @Excel(name="*手机号")
    private String phone;

    @Excel(name="邮箱")
    private String email;

    @Excel(name="证件号码")
    private String iDCard;

    @Excel(name="账号状态", replace = {"解锁_0", "锁定_1"})
    private Integer locked;

    @Excel(name="密码")
    private String password;

}

导出word:

HaspMap里保存word 替换的字符串文本和图片路径：

/**
     *下载word
     * @param response
     * @throws Exception
     */
    @ApiOperation("下载word")
    @PostMapping("/exportToWord")
    public void exportToWord(HttpServletResponse response, @RequestBody DownloadReportSearchVo downloadReportSearchVo) {
        String secCode = SecurityContextHolder.getUserStockCode();
        HengShenCompanyInfoDto companyInfoDto = remoteBasicService.getCompanyInfoByCode(secCode).getData();
        String companyReferred = companyInfoDto.getCompanyReferred();
        String day = DateUtil.format(new Date(),"yyyyMMdd");
        String wordFileName = companyReferred+"("+secCode+")"+"市值诊断报告_"+day+".docx";
        try {
            downloadReportSearchVo.setSecCode(secCode);
            Map<String, Object> wordInitDataMaps = downloadReportService.exportToWord(downloadReportSearchVo);
            // 前端调用下面初始化word数据方法，下载时候从缓存取word map类型替换数据；
            // Map<String, Object> wordInitDataMaps = redisService.getCacheMap(DOWNLOADREPORT_WORDDATA+secCode);
            //读取模板 并 一次性提交maps里要替换的文字和图片内容,然后导出word;
            XWPFDocument  word = null;
            try {
                word = WordExportUtil.exportWord07(phantomjsRoot+"/市值诊断报告_YYYYMMDD.docx", wordInitDataMaps);
            } catch (Exception e) {
                e.printStackTrace();
            }
            response.setHeader("content-disposition", "attachment;filename="+ URLEncoder.encode(wordFileName,"UTF-8"));
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            ServletOutputStream outputStream = response.getOutputStream();
            word.write(outputStream);
            outputStream.close();
            word.close();
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }





    /**
     * 根据原图表数据，封装echart json option 格式,并生成echart图片
     *
     * @param liquidityAnalysisVO
     */
    @Override
    public Map<String, Object> analysisResultToEchartImg(LiquidityAnalysisVO liquidityAnalysisVO, Map<String, Object> maps) {
        List<String> colorList = Arrays.asList("rgb(52,113,219)", "rgb(46,167,224)", "rgb(16,197,121)", "rgb(248,180,0)");

        String suggest = liquidityAnalysisVO.getSuggest();
        String yearAvgSuggest = liquidityAnalysisVO.getYearAvgSuggest();

        // 1 第一张echart图 生成echart option
        //换手率 替换第一张图片和文字
        List<OverviewVO> listYearAvgAnalysis = liquidityAnalysisVO.getYearAvgAnalysis();
        List<String> indexkeyYearAvgAnalysis = listYearAvgAnalysis.stream().map(overviewVO -> overviewVO.getIndexKey()).collect(Collectors.toList());
        List<String> indexValueYearAvgAnalysis = listYearAvgAnalysis.stream().map(overviewVO -> overviewVO.getIndexValue()).collect(Collectors.toList());
        maps.put(DownloadReportEnum.DIAGNOSTIC_RESULT_FLOW_SUGGEST.getName(), suggest);
        maps.put(DownloadReportEnum.DIAGNOSTIC_RESULT_FLOW_1_YEARAVGSUGGEST.getName(), yearAvgSuggest.replaceAll("<span>", "").replaceAll("</span>", ""));
        String option = "{\n" +
                "  title: {\n" +
                "    text: '换手率'\n" +
                "  },\n" +
                "  tooltip: {\n" +
                "    trigger: 'axis',\n" +
                "    axisPointer: {\n" +
                "      type: 'shadow'\n" +
                "    }\n" +
                "  },\n" +
                "  legend: {},\n" +
                "  grid: {\n" +
                "    left: '3%',\n" +
                "    right: '4%',\n" +
                "    bottom: '3%',\n" +
                "    containLabel: true\n" +
                "  },\n" +
                "  xAxis: {\n" +
                "    type: 'value',\n" +
                "    boundaryGap: [0, 0.01]\n" +
                "  },\n" +
                "  yAxis: {\n" +
                "    type: 'category',\n" +
                "    data: ['" + StringUtils.join(indexkeyYearAvgAnalysis, "','") + "'] \n" +
                "  },\n" +
                "  series: [\n" +
                "    {\n" +
                "      barWidth:30, " +
                "      name: '当年度年平均换手率（%）',\n" +
                "      type: 'bar',\n" +
                "      data: [ \n ";
        for (int i = 0; i < indexValueYearAvgAnalysis.size(); i++) {
            String value = indexValueYearAvgAnalysis.get(i);
            String color = colorList.get(i);
            option += " { " +
                    "value: " + value + ", " +
                    "itemStyle: { " +
                    "color: '" + color + "' " +
                    "} " +
                    "}, ";
        }
        option += " ]} \n" +
                "  ]\n" +
                "};\n";
        String echartImgPath1 = eChartImgService.generateEChartImg(option);
        // 2 生成要代替word里图像英文单词字符串
        ImageEntity imageEntity = new ImageEntity();
        imageEntity.setUrl(echartImgPath1);
        imageEntity.setWidth(500);
        imageEntity.setHeight(300);   // 这里的宽高一定要设置，不然图片出不来
        maps.put(DownloadReportEnum.DIAGNOSTIC_RESULT_FLOW_1_IMG1.getName(), imageEntity);//替换图片
}