springboot poi多sheet excel导出

springboot poi多sheet excel导出

从hbase 查出数据 一个设备有多个参数 每个参数的数据为一个sheet 仅做记录

/**
     * 输出Excel文档
     * @param workbook
     * @param resources 源数据
     * @param headerNames 表头
     * @param sheetName 表格名
     * @param columnNum 列数量
     * @param sheetNum 页码(sheet页码)
     * @throws IOException
     */
    public static void writeExcel(HSSFWorkbook workbook,
                                  List<String[]> resources,
                                  String[] headerNames,
                                  String sheetName,
                                  Integer columnNum,
                                  Integer sheetNum) throws IOException {
        // 创建表格
        HSSFSheet sheet = workbook.createSheet();
        sheet.setDefaultRowHeightInPoints(13);//默认宽度
        workbook.setSheetName(sheetNum, sheetName);
        // 设置列宽，根据
        for(int i=0; i<=columnNum; i++){
            sheet.setColumnWidth(i, 6000);
        }
        /*
         * 创建合并区域
         * CellRangeAddress(int 首行, int 最后一行, int 首列, int 最后一列);
         */
        CellRangeAddress add = new CellRangeAddress(0, 0, 0, columnNum);
        // 将创建的合并区域设置到表格中.
        sheet.addMergedRegion(add);
        // 创建行
        Row header = sheet.createRow(0);
        // 创建单元格. 合并后的单元格,编号合并.
        //设置样式
        CellStyle titleStyle = workbook.createCellStyle();
        Font titlefont = workbook.createFont();
        titlefont.setFontName("黑体");
        //titlefont.setColor(IndexedColors.VIOLET.index);
        titlefont.setFontHeightInPoints((short)20);
        titlefont.setBold(true);
        titleStyle.setFont(titlefont);
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        Cell c = header.createCell(0);
        c.setCellValue(sheetName);
        c.setCellStyle(titleStyle);
        c = header.createCell(columnNum);
        // 编写表头
        // 定义表头的样式
        CellStyle headerStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setFontName("宋体");
        //font.setColor(IndexedColors.VIOLET.index);
        font.setFontHeightInPoints((short)16);
        headerStyle.setFont(font);
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        // 设置单元格样式
        Row headerRow = sheet.createRow(1);
        for (int i = 0; i < headerNames.length; i++) {
            Cell cell = headerRow.createCell(i);
            // 设置单元格样式
            cell.setCellStyle(headerStyle);
            cell.setCellValue(headerNames[i]);
        }
        // 设置表格数据的样式
        CellStyle bodyStyle = workbook.createCellStyle();
        Font bodyFont = workbook.createFont();
        bodyFont.setFontName("微软雅黑");
        //bodyFont.setColor(IndexedColors.BLUE.index);
        bodyFont.setFontHeightInPoints((short)12);
        bodyStyle.setFont(bodyFont);

        // 编辑表格体数据
        for (int i = 0; i < resources.size(); i++) {
            // 获取行数据
            String[] temp = resources.get(i);
            // 创建行
            Row bodyRow = sheet.createRow(i + 2);
            for (int cellNum = 0; cellNum < temp.length; cellNum++) {
                Cell bodyCell = bodyRow.createCell(cellNum);
                bodyCell.setCellStyle(bodyStyle);
                bodyCell.setCellValue(temp[cellNum]);
            }
        }
        sheet.getRow(0).setHeightInPoints(24);
        sheet.getRow(1).setHeightInPoints(20);

    }

@Service
@Slf4j
public class ExportEquipHistoryDataServiceImpl implements ExportEquipHistoryDataService {

    @Resource(name = "connection2")
    private Connection connection;

    @Value("${hbase.equip2.table.name}")
    private String TEST_EQUIP_TABLE_NAME;

    @Autowired
    private EquipHistoryDataMapper equipHistoryDataMapper;

    private static final int DEVICE_DEFAULT_PAGE_SIZE = 50000;

    @Override
    public void exportHistory( Map<String, Object> map) throws IOException {
        ServletRequestAttributes servletRequestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        HttpServletResponse response = servletRequestAttributes.getResponse();
        String equipCode = (String) map.get("equipCode");
        //根据机械码 获取设备信息
        List<EquipReParamView> paramViews = equipHistoryDataMapper.paramList(equipCode);
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        if (paramViews != null) {
            for (int i = 0; i < paramViews.size(); i++){
                EquipReParamView pv = paramViews.get(i);
                map.put("collectId", pv.getCollectId());
                
                List<String[]> historyData = EquipParamHistoryData(map, pv.getEquipName(), pv.getAlias());
                try {
                    PoiExcelExport.writeExcel(hssfWorkbook, historyData,
                            new String[]{"设备编号",
                                    "设备名称",
                                    "采集时间",
                                    "参数编号",
                                    "参数名称",
                                    "采集值",
                                    "单位"},
                            pv.getAlias(),
                            7,
                            i
                    );
                } catch (IOException e) {
                    e.printStackTrace();
                }

            }};

        //输出
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("历史数据表", "utf-8"));
        OutputStream out = new BufferedOutputStream(response.getOutputStream());
        try{
            hssfWorkbook.write(out);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try{
                // 清理资源
                out.close();//关闭
            }catch(Exception e){
                e.printStackTrace();
            }
        }

    }

    //List<Map<String,List<String[]>>>
    private List<String[]> EquipParamHistoryData(Map<String, Object> map,String equipName,String paramName) {
        String equipCode = (String) map.get("equipCode");
        String collectId = String.valueOf(map.get("collectId"));
        if (equipCode == null || equipCode.trim().equals("") ||collectId == null) {
            return Collections.emptyList();
        }
        Scan scan = reBuildScan(map, equipCode, collectId);
        Table table = null;
        List<String[]> tm= new ArrayList<>();
        ResultScanner rs = null;
        String rowKey = null;
        try {
            table = connection.getTable(TableName.valueOf(TEST_EQUIP_TABLE_NAME));
            rs = table.getScanner(scan);
            Result r;
            while ((r = rs.next()) != null) {
                // 每一行数据
                Map<String, String> columnMap = new HashMap<>();
                // 行键，列族和列限定符一起确定一个单元（Cell）
                rowKey = new String(r.getRow());
                columnMap.put("rowKey", rowKey);
                for (Cell cell : r.listCells()) {
                    columnMap.put(Bytes.toString(CellUtil.cloneQualifier(cell)), Bytes.toString(CellUtil.cloneValue(cell)));
                }
                if (rowKey != null) {
                    String collectTime = columnMap.get("collectTime");
                    String time = DateFormateUtils.milliSecond2Date(collectTime, "yyyy-MM-dd HH:mm:ss");
                    String[] res = new String[]{equipCode, equipName, time, columnMap.get("modelCode"), paramName, columnMap.get("data"), columnMap.get("unitName")};
                    tm.add(res);
                }
            }
        } catch (IOException e) {
            log.error(MessageFormat.format("查询历史数据失败,equipCode:{0}", equipCode), e);
            return Collections.emptyList();
        } finally {
            if (rs != null) {
                rs.close();
            }
        }
        return tm;
    }

    private Scan reBuildScan(Map<String, Object> map, String equipCode, String collectId) {
        Integer enterpriseId = (Integer) map.get("enterpriseId");
        if(enterpriseId == null) {
            enterpriseId = LoginContextHolder.getCurEnterPriseId();
        }
        String startDate = (String) map.get("startDate");
        String endDate = (String) map.get("endDate");
        String startRow = (String) map.get("startRow");
        String endRow = (String) map.get("endRow");
        Scan scan = new Scan();
        if (StringUtils.isNotBlank(endRow)) {
            scan.setStopRow(Bytes.toBytes(endRow));
        } else if (StringUtils.isNotBlank(endDate)) {
            scan.setStopRow(Bytes.toBytes(equipCode + "_" + collectId + "_" + enterpriseId+ "_" + startDate));
        }
        if (StringUtils.isNotBlank(startRow)) {
            scan.setStartRow(Bytes.toBytes(startRow));
        } else if (StringUtils.isNotBlank(startDate)) {
            scan.setStartRow(Bytes.toBytes(equipCode + "_" + collectId + "_" + enterpriseId+ "_" + endDate));
        }
        //降序
        scan.setReversed(true);   //HBase自带的方法，但需要将startRow设置为终止行，endRow设置为起始行

        //filter
        FilterList filterList = new FilterList(FilterList.Operator.MUST_PASS_ALL);
        Filter filter = new RowFilter(CompareFilter.CompareOp.EQUAL, new RegexStringComparator(equipCode + "_" + collectId+ "_" + enterpriseId+  "_.*"));
        filterList.addFilter(filter);

        int pageSize = (map != null && map.get("pageSize") != null) ? (int) map.get("pageSize") :
                DEVICE_DEFAULT_PAGE_SIZE;
        map.put("pageSize", pageSize);
        Filter pageFilter = new PageFilter(pageSize + 1);
        filterList.addFilter(pageFilter);
        scan.setFilter(filterList);
        return scan;
    }
}