POI导入，导出（分格式写入excel,合并单元格）

最新推荐文章于 2024-01-11 16:58:13 发布

qzz12345

最新推荐文章于 2024-01-11 16:58:13 发布

阅读量1.2k

点赞数

CC 4.0 BY-SA版权

文章标签： excel windows

本文链接：https://blog.csdn.net/qzz12345/article/details/130889691

系列文章目录

之前的文章介绍了阿里easyExcel的导入导出，现在记录一下使用POI进行导入导出的方式

一、列固定

要导入、导出的数据列是固定的，直接上代码：

导入：

 @Override
    @Transactional(rollbackFor = Exception.class)
    public Result<?> importByExcel(HttpServletRequest request, HttpServletResponse response) throws Exception {
   
   
        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
        Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();
        MultipartFile file = fileMap.get("file");//获取上传文件对象
        if (file == null){
   
   
            return Result.error("文件上传失败！");
        }
        XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
        XSSFSheet sheet = null;
        if (workbook != null && (sheet = workbook.getSheetAt(0)) != null){
   
   
            //总行数>2代表有数据，表头占一行
            int a = sheet.getLastRowNum();
            if (sheet.getLastRowNum() >= 1){
   
   
                XSSFRow row0 =sheet.getRow(0); //获取标题行
                //获取行里的总列数
                int columnNum = 0;
                HashSet<String> titles = new HashSet<>();//标题列表
                HashMap<Integer, String> columnsMap = new HashMap<>();
                HashMap<String, String> titlesCustomMap = new HashMap<>();//自定义标题列表对应字段
                if (row0 != null && (columnNum = row0.getPhysicalNumberOfCells()) > 0){
   
    //列大于0
                    titlesCustomMap.put("部门属性","setDepartProperty");
                    titlesCustomMap.put("年","setYears");
                    titlesCustomMap.put("月","setMonths");
                    titlesCustomMap.put("预算实体","setBudgetEntity");
                    titlesCustomMap.put("新架构一级部门","setPrimaryDepart");
                    titlesCustomMap.put("新架构下二级展示部门","setSecondaryDepart");
                    titlesCustomMap.put("按平台分类","setPlatformClass");
                    titlesCustomMap.put("预算科目","setBudgetSubject");
                    titlesCustomMap.put("科目属性","setSubjectProperty");
                    titlesCustomMap.put("一级科目","setPrimarySubject");
                    titlesCustomMap.put("简化科目","setSimplifySubject");
                    titlesCustomMap.put("类型","setFinanceStype");
                    titlesCustomMap.put("项目","setProject");
                    titlesCustomMap.put("BU","setBu");
                    titlesCustomMap.put("版本","setVersion");
                    titlesCustomMap.put("金额","setFinanceMoney");
                    for (int i = 0; i < columnNum; i++) {
   
   
                        //获取标题行每个单元格
                        XSSFCell cell0 = row0.getCell(i);
                        if (cell0 == null) {
   
   
                            continue;
                        }
                        //获取标题
                        String title = cell0.getStringCellValue();
                        String invokeMethod = null;
                        if (StringUtils.isNoneBlank(title,(invokeMethod = titlesCustomMap.get(title))) && !titles.contains(title)) {
   
   //判断表头字段是否存在重复
                            titles.add(title);
                            columnsMap.put(i,invokeMethod);
                        }
                    }
                }
                if(titlesCustomMap.size() == columnsMap.size()){
   
   
                    Class<? extends UPmoProjectFinance> classType = UPmoProjectFinance.class;
//                    List<String> dataKeys = Arrays.asList(new String[]{"setYearMonths"});
                    List<String> bigDecimalKeys = Arrays.asList(new String[]{
   
   "setFinanceMoney"});
//                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-M");
//                    SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM");

                    //获取总行数
                    int lastRowNum = sheet.getLastRowNum();
                    List<UPmoProjectFinance> uPmoProjectFinanceList = new ArrayList<>();  //接收数据
                    for(int m = 1; m <= lastRowNum; m++) {
   
   //从第2行开始读取数据
                        UPmoProjectFinance uPmoProjectFinance = new UPmoProjectFinance();
                        XSSFRow rowData = sheet.getRow(m);
                        for (Map.Entry<Integer, String> column : columnsMap.entrySet()) {
   
   
                            XSSFCell cell = rowData.getCell(column.getKey());
                            String invokeMethod = column.getValue();
                            if (cell != null && StringUtils.isNotBlank(invokeMethod)) {
   
   
                                cell.setCellType(CellType.STRING);
                                String stringCellValue = cell.getStringCellValue();
                                if(StringUtils.isNotBlank(stringCellValue)){
   
   
//                                    if(dataKeys.contains(invokeMethod)){
   
   
//                                        Method declaredMethod = classType.getMethod(invokeMethod, String.class);
//                                        Date date1Date = sdf.parse(stringCellValue);
//                                        declaredMethod.invoke(uPmoProjectFinance,sdf2.format(date1Date));
//                                    }else if (bigDecimalKeys.contains(invokeMethod)){
   
   
//                                        Method declaredMethod = classType.getMethod(invokeMethod, BigDecimal.class);
//                                        declaredMethod.invoke(uPmoProjectFinance,new BigDecimal(stringCellValue));
//                                    }
                                    if (bigDecimalKeys.contains(invokeMethod)){
   
   
                                        Method declaredMethod = classType.getMethod(invokeMethod, BigDecimal.class);
                                        declaredMethod.invoke(uPmoProjectFinance,new BigDecimal(stringCellValue));
                                    }
                                    else {
   
   
                                        Method declaredMethod = classType.getMethod(invokeMethod,String.class);
                                        declaredMethod.invoke(uPmoProjectFinance,stringCellValue);
                                    }
                                }
                            }
                        }
                        uPmoProjectFinanceList.add(uPmoProjectFinance);
                    }
                    for (UPmoProjectFinance uPmoProjectFinance : uPmoProjectFinanceList) {
   
   
                        if(uPmoProjectFinance.getMonths().split("月")[0].length() != 2){
   
   
                            uPmoProjectFinance.setMonths("0"+uPmoProjectFinance.getMonths().split("月")[0]);
                        }else {
   
   
                            uPmoProjectFinance.setMonths(uPmoProjectFinance.getMonths().split("月")[0]);
                        }
//
//                        QueryWrapper<UPmoProjectFinance> queryWrapper = new QueryWrapper<>();
//                        queryWrapper.eq("YEARS",uPmoProjectFinance.getYears());
//                        queryWrapper.eq("MONTHS",uPmoProjectFinance.getMonths());
//                        queryWrapper.eq("VERSION",uPmoProjectFinance.getVersion());
//                        queryWrapper.eq("PROJECT",uPmoProjectFinance.getProject());
//                        long count = this.count(queryWrapper);
//                        if (count>0){
   
   
//                            super.remove(queryWrapper);
//                        }
                    }
                    pmoProjectFinanceMapper.deleteSjYs();
                    super.saveBatch(uPmoProjectFinanceList);


                }else {
   
   
                    return Result.error("表头字段存在异常！");
                }

            }else {
   
   
                return Result.error("文件为空！");
            }
            return Result.ok("导入成功！");
        }else {
   
   
            return Result.error("文件导入失败！");
        }

    }

导出：

    @Override
    @ApiOperation(value="数据导出", notes="数据导出")
    @PostMapping(value = "/projectDataToExcel")
    public Result<?> projectDataToExcel(HttpServletRequest request, HttpServletResponse response) {
   
   
        String project = "G20";
        String project1 = "Sabers";
        String dataDate = null;
        Map<String, List<G20SabersVo>> collect = commonService.getProjectInfo(project,dataDate);
        Map<String, List<G20SabersVo>> collect1 = commonService.getProjectInfo(project1,dataDate);
        List<G20SabersVo> projectInfoHouList = commonService.getProjectInfoHou();
        List<G20SabersVo> resultG20Total = commonService.getProjectInfoTotal(collect,projectInfoHouList);
        List<G20SabersVo> resultSabersTotal = commonService.getProjectInfoTotal(collect1,projectInfoHouList);
        resultG20Total.addAll(resultSabersTotal);
        resultG20Total = resultG20Total.stream().sorted(Comparator.comparing(G20SabersVo::getOrderNo)).collect(Collectors.toList());
        //导出
        String sheetName = "信息表";
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet(sheetName);
        //合并的表头
        String[] head = new String[]{
   
   "物料需求","物料需求","物料需求","物料需求","物料需求","物料需求","物料需求","实际缺料","合格","合格","合格","待检","待检","待检"};
        int titleIndex = 0;
        //表头对应数据查询字段
        Map<String, String> titleMap = new LinkedHashMap<>();
        sheet.setColumnWidth(titleIndex++, 5000); titleMap.put("getKldate", "日期");
        sheet.setColumnWidth(titleIndex++, 7000); titleMap.put("getErpWo", "工单");
        sheet.setColumnWidth(titleIndex++, 5000); titleMap.put("getMainitemNumber", "物料编号");
        sheet.setColumnWidth(titleIndex++, 3000); titleMap.put("getQuantity", "需求");
        sheet.setColumnWidth(titleIndex++, 5000); titleMap.put("getRawItemNumber", "主替物料");
        sheet.setColumnWidth(titleIndex++, 10000); titleMap.put("getHpmc", "物料名称");
        sheet.setColumnWidth(titleIndex++, 3000); titleMap.put("getQuantityIn", "收料");
        sheet.setColumnWidth(titleIndex++, 3000); titleMap.put("getSjql", "实际缺料");
        sheet.setColumnWidth(titleIndex++, 3000); titleMap.put("getHgXbkc", "线边库存");
        sheet.setColumnWidth(titleIndex++, 3000); titleMap.put("getHgDfb", "待翻包");
        sheet.setColumnWidth(titleIndex++, 3000); titleMap.put("getHgGwl", "高五路");
        sheet.setColumnWidth(titleIndex++, 3000); titleMap.put("getDjN2", "N2#1F");
        sheet.setColumnWidth(titleIndex++, 3000); titleMap.put("getDjGwl", "高五路");
        //设置文档密级
        sheet.getHeader().setRight("文档密级：Confidential");
        int rowNumberHe = 0;//合并单元格的表头
        XSSFRow rowTitleHe = sheet.createRow(rowNumberHe++);
        int rowNumber = 1;//表头数据所在行
        XSSFRow rowTitle = sheet.createRow(rowNumber++);

        //实际缺料列设为红
        XSSFCellStyle cellStyleQlTitle = workbook.createCellStyle();
        cellStyleQlTitle.setBorderBottom(BorderStyle.THIN);//下边框
        cellStyleQlTitle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyleQlTitle.setBorderRight(BorderStyle.THIN);//右边框
        cellStyleQlTitle.setBorderTop(BorderStyle.THIN);//上边框

        cellStyleQlTitle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cellStyleQlTitle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        XSSFFont fontQlTitle = workbook.createFont();
        fontQlTitle.setColor(XSSFFont.COLOR_RED);
        cellStyleQlTitle.setFont(fontQlTitle);
        //设置单元格样式
        XSSFCellStyle cellStyleTitle = workbook.createCellStyle();
        cellStyleTitle.setBorderBottom(BorderStyle.THIN);//下边框
        cellStyleTitle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyleTitle.setBorderRight(BorderStyle.THIN