POI之excel操作

 一、apache POI常规导出

1.下载开发包解压：

2.引入POI的依赖：

                  <dependency>
                            <groupId>org.apache.poi</groupId>
                            <artifactId>poi</artifactId>
                            <version>3.11</version>
                   </dependency> 

3.POI使用：

public String exportXls() throws IOException{
        List<Subarea> list = subareaService.findAll();
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("分区数据");
        HSSFRow row0 = sheet.createRow(0);
        row0.createCell(0).setCellValue("分区编号");
        row0.createCell(1).setCellValue("开始编号");
        row0.createCell(2).setCellValue("结束编号");
        row0.createCell(3).setCellValue("位置信息");
        row0.createCell(4).setCellValue("省市区");
        for (Subarea subarea : list) {
            HSSFRow row = sheet.createRow(sheet.getLastRowNum()+1);
            row.createCell(0).setCellValue(subarea.getId());
            row.createCell(1).setCellValue(subarea.getStartnum());
            row.createCell(2).setCellValue(subarea.getEndnum());
            row.createCell(3).setCellValue(subarea.getPosition());
            row.createCell(4).setCellValue(subarea.getRegion().getName());
        }
        String filename = "分区数据.xls";
        String mimeType = ServletActionContext.getServletContext().getMimeType(filename);
        ServletActionContext.getResponse().setContentType(mimeType);
        String agent = ServletActionContext.getRequest().getHeader("User-Agent");
        filename = FileUtils.encodeDownloadFilename(filename, agent);
        ServletActionContext.getResponse().setHeader("content-disposition","attchment;filename="+filename);
        ServletOutputStream out = ServletActionContext.getResponse().getOutputStream();
        workbook.write(out);
        return NONE;
    }
 

@Test
         public void test1() throws FileNotFoundException, IOException{
                   String filePath = "C:\\Users\\zhaoqx\\Desktop\\资料\\区域导入测试数据.xls";
                   //包装一个Excel文件对象
                   HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(new File(filePath)));
                   //读取文件中第一个Sheet标签页
                   HSSFSheet hssfSheet = workbook.getSheetAt(0);
                   //遍历标签页中所有的行
                   for (Row row : hssfSheet) {
                            System.out.println();
                            for (Cell cell : row) {
                                     String value = cell.getStringCellValue();
                                     System.out.print(value + " ");
                            }
                   }
         }

 

二、JueYue的ExcelExportUtil导出动态复杂表头

1.导包

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;

2.具体使用

        //根据分组添加表头
        List<ExcelExportEntity> entityList = new ArrayList<>();
        ExcelExportEntity exportEntity = new ExcelExportEntity("地区", "enterprise", 20);
        exportEntity.setNeedMerge(true);
        exportEntity.setMergeVertical(true);
        entityList.add(exportEntity);
        ExcelExportEntity export = new ExcelExportEntity("机构", "account", 20);
        entityList.add(export);
        CommonResult<List<EnterpriseEnergyMeasureDTO>> measureResult = feignMeterStatisticsService.measures(query.getSubjectId());
        CommonResult<List<EnterpriseAccountConnDTO>> result = feignEnterpriseArchiveListService.findLowestNodeAccountConn(query.getSubjectId(), projectIds);
        if (result.isFail() || CollectionUtil.isEmpty(result.getData())) {
            throw new BusinessException("没有找到企业预制分项能耗组织信息！");
        }
        List<Map<String, Object>> data = new ArrayList<>();
        result.getData().forEach(conn -> {
            Map<String, Object> enterprise = new HashMap<>(temp);
            enterprise.put("enterprise", conn.getEnterpriseName());
            enterprise.put("account", FILL_STR);
            Map<Long, List<InteEnergyUnialDTO>> measureMap = Optional.ofNullable(getInteEnergyUnialMonthly(Sets.newHashSet(conn.getAccountIds()), query.getMonth()))
                    .orElseGet(Lists::newArrayList)
                    .stream()
                    .filter(Objects::nonNull)
                    .collect(Collectors.groupingBy(InteEnergyUnialDTO::getAccountId));

            Map<Long,UnialSumDTO> enterpriseData = new HashMap<>();
            List<Map<String, Object>> accountMaps = conn.getAccounts()
                    .stream()
                    .map(account -> {
                        Map<String, Object> accountMap = new HashMap<>(temp);
                        accountMap.put("enterprise", conn.getEnterpriseName());
                        accountMap.put("account", account.getAccountName());
                        //遍历分组赋值
                         Optional.ofNullable(measureMap.get(account.getAccountId()))
                                .orElseGet(Lists::newArrayList)
                                .forEach(unialDTO -> {
                                    accountMap.put("p_" + unialDTO.getMeasureId(), unialDTO.getPosSumVal());
                                    accountMap.put("a_" + unialDTO.getMeasureId(), unialDTO.getManageArea());
                                    accountMap.put("u_" + unialDTO.getMeasureId(), unialDTO.getUnilateralEnergy());

                                    UnialSumDTO sumDTO = Optional.ofNullable(enterpriseData.get(unialDTO.getMeasureId()))
                                            .orElseGet(UnialSumDTO::new);
                                    sumDTO.posSumVal(unialDTO.getPosSumVal());
                                    sumDTO.manageArea(unialDTO.getManageArea());
                                    enterpriseData.put(unialDTO.getMeasureId(), sumDTO);
                                });

                        return accountMap;
                    }).collect(Collectors.toList());

            //获取组织的电量信息
            enterpriseData.forEach((id, dto) -> {
                enterprise.put("p_" + id, exportNullToStr(dto.getPosSumVal()));
                enterprise.put("a_" + id, exportNullToStr(dto.getManageArea()));
                //计算单方能耗 单位kWh/㎡
                enterprise.put("u_" + id, BigDecimalUtils.divideBigDecimal(dto.getPosSumVal(),dto.getManageArea()));
            });
            data.add(enterprise);
            //排序效果 不能交换企业组织add顺序
            data.addAll(accountMaps);
        });

        return ExcelExportUtil.exportExcel(new ExportParams(null, "分项能耗报表", ExcelType.HSSF), entityList, data);