Example usage for org.apache.poi.ss.util CellRangeAddress CellRangeAddress

List of usage examples for org.apache.poi.ss.util CellRangeAddress CellRangeAddress

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.util.*
  5. CellRangeAddress
  6. CellRangeAddress

Introduction

In this page you can find the example usage for org.apache.poi.ss.util CellRangeAddress CellRangeAddress.

Prototype

public CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)

Source Link

Document

Creates new cell range.

Usage

From source file:org.bbreak.excella.reports.tag.BlockColRepeatParamParser.java

License:Open Source License

@Override
public ParsedReportInfo parse(Sheet sheet, Cell tagCell, Object data) throws ParseException {
    try {//from w w  w  .j av a2s  .c o m
        // ??
        Map<String, String> paramDef = TagUtil.getParams(tagCell.getStringCellValue());

        // ?
        checkParam(paramDef, tagCell);

        ReportsParserInfo info = (ReportsParserInfo) data;
        ParamInfo paramInfo = info.getParamInfo();

        // 
        ParsedReportInfo parsedReportInfo = new ParsedReportInfo();
        List<Object> resultList = new ArrayList<Object>();

        // BC?????
        String bcTagname = paramDef.get(PARAM_VALUE);
        if (log.isDebugEnabled()) {
            log.debug("BC?? : " + bcTagname);
        }

        // ?????
        Object[] paramInfos = getParamData(paramInfo, bcTagname);
        if (paramInfos == null) {
            return parsedReportInfo;
        }
        // ????
        List<SingleParamParser> singleParsers = getSingleReplaceParsers(info);

        // POJOParamInfo???
        List<ParamInfo> paramInfoList = new ArrayList<ParamInfo>();
        for (Object obj : paramInfos) {
            if (obj instanceof ParamInfo) {
                paramInfoList.add((ParamInfo) obj);
                continue;
            }
            ParamInfo childParamInfo = new ParamInfo();
            Map<String, Object> map = PropertyUtils.describe(obj);
            for (Map.Entry<String, Object> entry : map.entrySet()) {
                for (ReportsTagParser<?> parser : singleParsers) {
                    childParamInfo.addParam(parser.getTag(), entry.getKey(), entry.getValue());
                }
            }
            paramInfoList.add(childParamInfo);
        }
        // ??
        if (paramDef.containsKey(PARAM_MIN_REPEAT_NUM)) {
            Integer minRepeatNum = Integer.valueOf(paramDef.get(PARAM_MIN_REPEAT_NUM));
            if (minRepeatNum > paramInfoList.size()) {
                int addEmptyRowNum = minRepeatNum - paramInfoList.size();
                for (int num = 0; num < addEmptyRowNum; num++) {
                    ParamInfo childParamInfo = new ParamInfo();
                    paramInfoList.add(childParamInfo);
                }
            }
        }
        paramInfos = paramInfoList.toArray(new ParamInfo[paramInfoList.size()]);

        // ?
        Integer repeatNum = paramInfos.length;
        if (paramDef.containsKey(PARAM_REPEAT)) {
            if (Integer.valueOf(paramDef.get(PARAM_REPEAT)) < repeatNum) {
                repeatNum = Integer.valueOf(paramDef.get(PARAM_REPEAT));
            }
        }

        // ?
        // TODO ?
        /*
         * Integer turnNum = null; if (paramDef.containsKey( PARAM_TURN)) { turnNum = Integer.valueOf( paramDef.get( PARAM_TURN)); }
         */

        // ??????????
        Map<String, Object> beforeBlockSingleDataMap = new HashMap<String, Object>();
        // ???
        if (paramDef.containsKey(PARAM_DUPLICATE)) {
            String[] tmp = paramDef.get(PARAM_DUPLICATE).split(";");
            // single???
            for (String str : tmp) {
                for (ReportsTagParser<?> parser : singleParsers) {
                    str = parser.getTag() + TAG_PARAM_PREFIX + str + TAG_PARAM_SUFFIX;
                    // ??key???
                    beforeBlockSingleDataMap.put(str, "");
                }
            }
        }

        // removeTag
        boolean removeTag = false;
        if (paramDef.containsKey(PARAM_REMOVE_TAG)) {
            removeTag = Boolean.valueOf(paramDef.get(PARAM_REMOVE_TAG));
        }

        // fromCell
        int[] fromCellPosition = ReportsUtil.getCellIndex(paramDef.get(PARAM_FROM), PARAM_FROM);
        int defaultFromCellRowIndex = tagCell.getRow().getRowNum() + fromCellPosition[0];
        int defaultFromCellColIndex = tagCell.getColumnIndex() + fromCellPosition[1];

        // toCell
        int[] toCellIndex = ReportsUtil.getCellIndex(paramDef.get(PARAM_TO), PARAM_TO);
        int defaultToCellRowIndex = tagCell.getRow().getRowNum() + toCellIndex[0];
        int defaultToCellColIndex = tagCell.getColumnIndex() + toCellIndex[1];

        // ?(??)
        int blockEndRowIndex = defaultToCellRowIndex;
        int blockEndColIndex = defaultToCellColIndex;
        int blockStartRowIndex = defaultFromCellRowIndex;
        int blockStartColIndex = defaultFromCellColIndex;

        // BC??Cell[row][col]
        Object[][] blockCellsValue = ReportsUtil.getBlockCellValue(sheet, defaultFromCellRowIndex,
                defaultToCellRowIndex, defaultFromCellColIndex, defaultToCellColIndex);
        CellStyle[][] blockCellsStyle = ReportsUtil.getBlockCellStyle(sheet, defaultFromCellRowIndex,
                defaultToCellRowIndex, defaultFromCellColIndex, defaultToCellColIndex);
        CellType[][] blockCellTypes = ReportsUtil.getBlockCellType(sheet, defaultFromCellRowIndex,
                defaultToCellRowIndex, defaultFromCellColIndex, defaultToCellColIndex);
        // ?????
        int[] columnWidths = ReportsUtil.getColumnWidth(sheet, defaultFromCellColIndex, defaultToCellColIndex);

        // ?
        int rowlen = defaultToCellRowIndex - defaultFromCellRowIndex + 1;
        int collen = defaultToCellColIndex - defaultFromCellColIndex + 1;

        // ???
        CellRangeAddress[] margedCells = ReportsUtil.getMargedCells(sheet, defaultFromCellRowIndex,
                defaultToCellRowIndex, defaultFromCellColIndex, defaultToCellColIndex);

        // ????
        int turnCount = 0;

        int maxblockEndRowIndex = blockEndRowIndex;

        TagParser<?> parser = null;

        ParsedReportInfo result = null;

        // 
        for (int repeatCount = 0; repeatCount < repeatNum; repeatCount++) {

            collen = defaultToCellColIndex - defaultFromCellColIndex + 1;

            // ??????blockStartRowIndex??blockEndColIndex?
            // TODO ?
            /*
             * if (turnNum != null && turnNum <= turnCount) { blockStartRowIndex = blockEndRowIndex + 1; blockEndColIndex = 0; turnCount = 0; }
             */

            // 
            if (turnCount > 0) {
                blockStartColIndex = blockEndColIndex + 1;
                blockEndColIndex = blockStartColIndex + collen - 1;
            } else {
                // ??????? 2009/11/16
                // blockStartColIndex = tagCell.getColumnIndex();
            }
            turnCount++;

            // ???BC
            if (repeatCount > 0) {
                CellRangeAddress rangeAddress = new CellRangeAddress(blockStartRowIndex, sheet.getLastRowNum(),
                        blockStartColIndex, blockStartColIndex + collen - 1);
                PoiUtil.insertRangeRight(sheet, rangeAddress);
                if (log.isDebugEnabled()) {
                    log.debug("");
                    log.debug(blockStartRowIndex + ":" + sheet.getLastRowNum() + ":" + blockStartColIndex + ":"
                            + (blockStartColIndex + collen - 1));
                }

                // ???
                // ???????????
                // ??????
                int targetColNum = blockEndColIndex - defaultToCellColIndex;
                for (CellRangeAddress address : margedCells) {
                    // ???(BC????????)
                    // ???(BC????????)
                    // ??? + BC?? - BC?
                    // ??? + BC?? - BC?
                    int firstRowNum = address.getFirstRow();
                    int lastRowNum = address.getLastRow();
                    int firstColumnNum = address.getFirstColumn() + targetColNum;
                    int lastColumnNum = address.getLastColumn() + targetColNum;

                    CellRangeAddress copyAddress = new CellRangeAddress(firstRowNum, lastRowNum, firstColumnNum,
                            lastColumnNum);
                    sheet.addMergedRegion(copyAddress);
                }

            }

            // ?
            if (log.isDebugEnabled()) {
                log.debug("?????? =" + repeatCount);
            }

            for (int rowIdx = 0; rowIdx < blockCellsValue.length; rowIdx++) {
                // ?
                // 
                int copyToRowIndex = blockStartRowIndex + rowIdx;
                Row row = sheet.getRow(copyToRowIndex);
                // ??null???????????????
                if (row == null && !ReportsUtil.isEmptyRow(blockCellTypes[rowIdx], blockCellsValue[rowIdx],
                        blockCellsStyle[rowIdx])) {
                    // ????
                    // ?
                    // (??)??????????
                    // ??null???(RowCreate?)???????????
                    row = sheet.createRow(copyToRowIndex);
                }

                if (row != null) {
                    // ?
                    for (int colIdx = 0; colIdx < blockCellsValue[rowIdx].length; colIdx++) {
                        // 
                        int copyToColIndex = blockStartColIndex + colIdx;
                        // ?
                        sheet.setColumnWidth(copyToColIndex, columnWidths[colIdx]);
                        // ?
                        Cell cell = row.getCell(copyToColIndex);
                        // ??
                        CellType cellType = blockCellTypes[rowIdx][colIdx];
                        // ??
                        Object cellValue = blockCellsValue[rowIdx][colIdx];
                        // ??
                        CellStyle cellStyle = blockCellsStyle[rowIdx][colIdx];
                        // ?????????????????
                        if (cell == null && !ReportsUtil.isEmptyCell(cellType, cellValue, cellStyle)) {
                            cell = row.createCell(copyToColIndex);
                        }

                        // 
                        if (cell != null) {
                            // ?
                            cell.setCellType(cellType);
                            // ??
                            PoiUtil.setCellValue(cell, cellValue);
                            // ??
                            if (cellStyle != null) {
                                cell.setCellStyle(cellStyle);
                            }
                            if (log.isDebugEnabled()) {
                                log.debug("row=" + (copyToRowIndex) + " col" + (copyToColIndex) + ">>>>>>"
                                        + blockCellsValue[rowIdx][colIdx]);
                            }
                        }
                    }
                }
            }

            // ?????????
            int plusRowNum = 0;
            // ?????????
            int plusColNum = 0;
            collen = defaultToCellColIndex - defaultFromCellColIndex + 1;

            // 
            for (int targetRow = blockStartRowIndex; targetRow < blockStartRowIndex + rowlen
                    + plusRowNum; targetRow++) {
                if (sheet.getRow(targetRow) == null) {
                    if (log.isDebugEnabled()) {
                        log.debug("row=" + targetRow + " : row is not available. continued...");
                    }
                    continue;
                }

                // ?
                Cell chgTargetCell = null;

                // 
                for (int targetCol = blockStartColIndex; targetCol <= blockStartColIndex + collen + plusColNum
                        - 1; targetCol++) {

                    // ????
                    chgTargetCell = sheet.getRow(targetRow).getCell(targetCol);
                    if (chgTargetCell == null) {
                        if (log.isDebugEnabled()) {
                            log.debug("row=" + targetRow + " col=" + targetCol
                                    + " : cell is not available. continued...");
                        }
                        continue;
                    }

                    parser = info.getMatchTagParser(sheet, chgTargetCell);
                    if (parser == null) {
                        if (log.isDebugEnabled()) {
                            log.debug("row=" + targetRow + " col=" + targetCol
                                    + " parser is not available. continued...");
                        }
                        continue;
                    }

                    String chgTargetCellString = chgTargetCell.getStringCellValue();
                    if (log.isDebugEnabled()) {
                        log.debug("##########  row=" + targetRow + " col=" + targetCol
                                + " =" + chgTargetCellString + " ##########");
                    }

                    // ?
                    result = (ParsedReportInfo) parser.parse(sheet, chgTargetCell,
                            info.createChildParserInfo((ParamInfo) paramInfos[repeatCount]));

                    // ???
                    plusRowNum += result.getRowIndex() - result.getDefaultRowIndex();

                    // ???
                    plusColNum += result.getColumnIndex() - result.getDefaultColumnIndex();

                    // ?single???????????
                    // ???????????
                    if (parser instanceof SingleParamParser
                            && beforeBlockSingleDataMap.containsKey(chgTargetCellString)) {
                        if (beforeBlockSingleDataMap.get(chgTargetCellString)
                                .equals(result.getParsedObject())) {
                            // 
                            PoiUtil.setCellValue(chgTargetCell, "");

                        } else {
                            // ????
                            beforeBlockSingleDataMap.put(chgTargetCellString, result.getParsedObject());
                        }
                    }

                    // ??????????????
                    if (blockStartColIndex != result.getDefaultColumnIndex()
                            && maxblockEndRowIndex <= blockEndRowIndex
                            && result.getRowIndex() > result.getDefaultRowIndex()) {
                        CellRangeAddress preRangeAddress = new CellRangeAddress(blockEndRowIndex + 1,
                                blockEndRowIndex + (result.getRowIndex() - result.getDefaultRowIndex()),
                                blockStartColIndex, targetCol - 1);
                        PoiUtil.insertRangeDown(sheet, preRangeAddress);
                        if (log.isDebugEnabled()) {
                            log.debug("******");
                            log.debug("1 : " + (blockEndRowIndex + 1) + ":"
                                    + (blockEndRowIndex + (result.getRowIndex() - result.getDefaultRowIndex()))
                                    + ":" + blockStartColIndex + ":" + (targetCol - 1));
                        }
                    }

                    // R??????
                    if (parser instanceof RowRepeatParamParser && maxblockEndRowIndex <= blockEndRowIndex
                            && result.getRowIndex() > result.getDefaultRowIndex()) {
                        CellRangeAddress rearRangeAddress = new CellRangeAddress(blockEndRowIndex + 1,
                                blockEndRowIndex + (result.getRowIndex() - result.getDefaultRowIndex()),
                                result.getDefaultColumnIndex() + 1, blockEndColIndex);
                        PoiUtil.insertRangeDown(sheet, rearRangeAddress);
                        if (log.isDebugEnabled()) {
                            log.debug("******");
                            log.debug("2 : " + (blockEndRowIndex + 1) + ":"
                                    + (blockEndRowIndex + (result.getRowIndex() - result.getDefaultRowIndex()))
                                    + ":" + (result.getDefaultColumnIndex() + 1) + ":" + blockEndColIndex);
                        }
                    }

                    blockEndRowIndex = defaultToCellRowIndex + plusRowNum;

                    resultList.add(result.getParsedObject());

                    if (parser instanceof BlockColRepeatParamParser
                            || parser instanceof BlockRowRepeatParamParser) {
                        collen += result.getColumnIndex() - result.getDefaultColumnIndex();
                        plusColNum -= result.getColumnIndex() - result.getDefaultColumnIndex();
                    }

                    // ????????
                    if (blockStartColIndex + collen + plusColNum - 1 > blockEndColIndex) {
                        int beforeLastColIndex = blockEndColIndex;
                        blockEndColIndex = blockStartColIndex + collen + plusColNum - 1;

                        // ???????????
                        CellRangeAddress preRangeAddress = new CellRangeAddress(tagCell.getRowIndex(),
                                targetRow - 1, beforeLastColIndex + 1, blockEndColIndex);
                        PoiUtil.insertRangeRight(sheet, preRangeAddress);
                        if (log.isDebugEnabled()) {
                            log.debug("******");
                            log.debug("1 : " + tagCell.getRowIndex() + ":" + (targetRow - 1) + ":"
                                    + (beforeLastColIndex + 1) + ":" + blockEndColIndex);
                        }
                        // ??????
                        int lastRowNum = sheet.getLastRowNum();
                        if ((blockEndRowIndex + 1) <= lastRowNum
                                && (beforeLastColIndex + 1) <= blockEndColIndex) {
                            CellRangeAddress rangeAddress = new CellRangeAddress(blockEndRowIndex + 1,
                                    lastRowNum, beforeLastColIndex + 1, blockEndColIndex);
                            PoiUtil.insertRangeRight(sheet, rangeAddress);
                            if (log.isDebugEnabled()) {
                                log.debug("******");
                                log.debug("3 : " + (blockEndRowIndex + 1) + ":" + lastRowNum + ":"
                                        + (beforeLastColIndex + 1) + ":" + blockEndColIndex);
                            }
                        }
                    }
                    // ?
                }
                // ??????
                if (blockStartColIndex + collen + plusColNum - 1 < blockEndColIndex) {
                    CellRangeAddress rearRangeAddress = new CellRangeAddress(targetRow, targetRow,
                            blockStartColIndex + collen + plusColNum, blockEndColIndex);
                    PoiUtil.insertRangeRight(sheet, rearRangeAddress);
                    if (log.isDebugEnabled()) {
                        log.debug("******");
                        log.debug("2 : " + targetRow + ":" + targetRow + ":"
                                + (blockStartColIndex + collen + plusColNum) + ":" + blockEndColIndex);
                    }
                }

                plusColNum = 0;

                // ?
            }

            // ???????
            if (maxblockEndRowIndex < blockEndRowIndex) {
                if (log.isDebugEnabled()) {
                    log.debug("******");
                }
                if (repeatCount != 0) {
                    CellRangeAddress preRangeAddress = new CellRangeAddress(maxblockEndRowIndex + 1,
                            blockEndRowIndex, defaultFromCellColIndex, blockStartColIndex - 1);
                    PoiUtil.insertRangeDown(sheet, preRangeAddress);
                    if (log.isDebugEnabled()) {
                        log.debug("1 : " + (maxblockEndRowIndex + 1) + ":" + blockEndRowIndex + ":"
                                + defaultFromCellColIndex + ":" + (blockStartColIndex - 1));
                    }
                }

                // ???????
                CellRangeAddress rearRangeAddress = new CellRangeAddress(maxblockEndRowIndex + 1,
                        blockEndRowIndex, blockEndColIndex + 1, PoiUtil.getLastColNum(sheet));
                PoiUtil.insertRangeDown(sheet, rearRangeAddress);
                if (log.isDebugEnabled()) {
                    log.debug("2 : " + (maxblockEndRowIndex + 1) + ":" + blockEndRowIndex + ":"
                            + (blockEndColIndex + 1) + ":" + PoiUtil.getLastColNum(sheet));
                }

                maxblockEndRowIndex = blockEndRowIndex;
            }
        }

        // 
        if (removeTag) {
            tagCell.setCellType(CellType.BLANK);
        }

        // 
        parsedReportInfo.setDefaultRowIndex(defaultToCellRowIndex);
        parsedReportInfo.setDefaultColumnIndex(defaultToCellColIndex);
        parsedReportInfo.setColumnIndex(blockEndColIndex);
        parsedReportInfo.setParsedObject(resultList);
        parsedReportInfo.setRowIndex(maxblockEndRowIndex);

        if (log.isDebugEnabled()) {
            log.debug("finalBlockRowIndex= " + maxblockEndRowIndex);
            log.debug("finalBlockColIndex=" + blockEndColIndex);
        }
        return parsedReportInfo;

    } catch (Exception e) {
        throw new ParseException(tagCell, e);
    }
}

From source file:org.bbreak.excella.reports.tag.BlockRowRepeatParamParser.java

License:Open Source License

@Override
public ParsedReportInfo parse(Sheet sheet, Cell tagCell, Object data) throws ParseException {
    try {//from w  w w  .  j  av a  2 s .c o  m
        // ??
        Map<String, String> paramDef = TagUtil.getParams(tagCell.getStringCellValue());

        // ?
        checkParam(sheet, paramDef, tagCell);

        ReportsParserInfo reportsParserInfo = (ReportsParserInfo) data;
        ParamInfo paramInfo = reportsParserInfo.getParamInfo();

        // parse?
        ParsedReportInfo parsedReportInfo = new ParsedReportInfo();
        List<Object> resultList = new ArrayList<Object>();

        // ?
        int finalBlockRowIndex = 0;
        int finalBlockColIndex = 0;

        String brTagName = paramDef.get(PARAM_VALUE);
        if (log.isDebugEnabled()) {
            log.debug("BR??: " + brTagName);
        }

        // ?????
        Object[] paramInfos = getParamData(paramInfo, brTagName);
        if (paramInfos == null) {
            return parsedReportInfo;
        }

        // ????
        List<SingleParamParser> singleParsers = getSingleReplaceParsers(reportsParserInfo);

        // POJOParamInfo???
        List<ParamInfo> paramInfoList = new ArrayList<ParamInfo>();
        for (Object obj : paramInfos) {
            if (obj instanceof ParamInfo) {
                paramInfoList.add((ParamInfo) obj);
                continue;
            }
            ParamInfo childParamInfo = new ParamInfo();
            Map<String, Object> map = PropertyUtils.describe(obj);
            for (Map.Entry<String, Object> entry : map.entrySet()) {
                for (ReportsTagParser<?> parser : singleParsers) {
                    childParamInfo.addParam(parser.getTag(), entry.getKey(), entry.getValue());
                }
            }
            paramInfoList.add(childParamInfo);
        }

        // ??
        if (paramDef.containsKey(PARAM_MIN_REPEAT_NUM)) {
            Integer minRepeatNum = Integer.valueOf(paramDef.get(PARAM_MIN_REPEAT_NUM));
            if (minRepeatNum > paramInfoList.size()) {
                int addEmptyRowNum = minRepeatNum - paramInfoList.size();
                for (int num = 0; num < addEmptyRowNum; num++) {
                    ParamInfo childParamInfo = new ParamInfo();
                    paramInfoList.add(childParamInfo);
                }
            }
        }

        paramInfos = paramInfoList.toArray(new ParamInfo[paramInfoList.size()]);

        // repeatNum
        Integer repeatNum = paramInfos.length;
        if (paramDef.containsKey(PARAM_REPEAT_NUM)) {
            if (Integer.valueOf(paramDef.get(PARAM_REPEAT_NUM)) < repeatNum) {
                repeatNum = Integer.valueOf(paramDef.get(PARAM_REPEAT_NUM));
            }
        }

        // duplicateParams  ????????
        Map<String, Object> unduplicableParamMap = new HashMap<String, Object>();
        if (paramDef.containsKey(PARAM_DUPLICATE)) {
            String[] params = paramDef.get(PARAM_DUPLICATE).split(";");
            for (String param : params) {
                for (ReportsTagParser<?> parser : singleParsers) {
                    param = parser.getTag() + TAG_PARAM_PREFIX + param + TAG_PARAM_SUFFIX;
                    unduplicableParamMap.put(param, "");
                }
            }
        }

        // removeTag
        boolean removeTag = false;
        if (paramDef.containsKey(PARAM_REMOVE_TAG)) {
            removeTag = Boolean.valueOf(paramDef.get(PARAM_REMOVE_TAG));
        }

        // 
        int tagCellRowIndex = tagCell.getRowIndex();
        int tagCellColIndex = tagCell.getColumnIndex();

        // fromCell
        String fromCellParamDef = paramDef.get(PARAM_FROM_CELL);
        int[] fromCellPosition = ReportsUtil.getCellIndex(fromCellParamDef, PARAM_FROM_CELL);
        int defaultFromCellRowIndex = tagCellRowIndex + fromCellPosition[0];
        int defaultFromCellColIndex = tagCellColIndex + fromCellPosition[1];

        // toCell
        String toCellParamDef = paramDef.get(PARAM_TO_CELL);
        int[] toCellPosition = ReportsUtil.getCellIndex(toCellParamDef, PARAM_TO_CELL);
        int defaultToCellRowIndex = tagCellRowIndex + toCellPosition[0];
        int defaultToCellColIndex = tagCellColIndex + toCellPosition[1];

        // 
        Object[][] blockCellValues = ReportsUtil.getBlockCellValue(sheet, defaultFromCellRowIndex,
                defaultToCellRowIndex, defaultFromCellColIndex, defaultToCellColIndex);
        CellStyle[][] blockCellStyles = ReportsUtil.getBlockCellStyle(sheet, defaultFromCellRowIndex,
                defaultToCellRowIndex, defaultFromCellColIndex, defaultToCellColIndex);
        CellType[][] blockCellTypes = ReportsUtil.getBlockCellType(sheet, defaultFromCellRowIndex,
                defaultToCellRowIndex, defaultFromCellColIndex, defaultToCellColIndex);
        float[] rowHeight = ReportsUtil.getRowHeight(sheet, defaultFromCellRowIndex, defaultToCellRowIndex);

        // ???
        CellRangeAddress[] margedCells = ReportsUtil.getMargedCells(sheet, defaultFromCellRowIndex,
                defaultToCellRowIndex, defaultFromCellColIndex, defaultToCellColIndex);

        // ?
        final int defaultBlockHeight = defaultToCellRowIndex - defaultFromCellRowIndex + 1;
        final int defaultBlockWidth = defaultToCellColIndex - defaultFromCellColIndex + 1;
        int rowlen = defaultBlockHeight;
        int collen = defaultBlockWidth;

        // ??(fromCell??, 1?????)
        int blockStartRowIndex = defaultFromCellRowIndex;
        int blockStartColIndex = defaultFromCellColIndex;
        int blockEndRowIndex = defaultToCellRowIndex;
        int blockEndColIndex = defaultToCellColIndex;
        int maxblockEndRowIndex = blockEndRowIndex;

        parsedReportInfo.setDefaultRowIndex(defaultToCellRowIndex);
        parsedReportInfo.setDefaultColumnIndex(defaultToCellColIndex);

        for (int repeatCount = 0; repeatCount < repeatNum; repeatCount++) {
            // ????
            if (repeatCount > 0) {
                blockStartRowIndex = blockEndRowIndex + 1;
                blockEndRowIndex = blockStartRowIndex + rowlen - 1;

                CellRangeAddress rangeAddress = new CellRangeAddress(blockStartRowIndex, blockEndRowIndex,
                        blockStartColIndex, PoiUtil.getLastColNum(sheet));
                PoiUtil.insertRangeDown(sheet, rangeAddress);

                if (log.isDebugEnabled()) {
                    log.debug("");
                    log.debug(" : " + blockStartRowIndex + ":" + (blockStartRowIndex + rowlen - 1)
                            + ":" + blockStartColIndex + ":" + PoiUtil.getLastColNum(sheet));
                }

                // ???
                // ???????????
                // ??????
                int targetRowNum = maxblockEndRowIndex - (defaultFromCellRowIndex - 1);
                for (CellRangeAddress address : margedCells) {
                    // ??? + BR?? - BR?
                    // ??? + BR?? - BR?
                    // ???(BR????????)
                    // ???(BR????????)
                    int firstRowNum = address.getFirstRow() + targetRowNum;
                    int lastRowNum = address.getLastRow() + targetRowNum;
                    int firstColumnNum = address.getFirstColumn();
                    int lastColumnNum = address.getLastColumn();

                    CellRangeAddress copyAddress = new CellRangeAddress(firstRowNum, lastRowNum, firstColumnNum,
                            lastColumnNum);
                    sheet.addMergedRegion(copyAddress);
                }

            }

            if (log.isDebugEnabled()) {
                log.debug("repeatCount = " + repeatCount);
                log.debug("blockStartRowIndex = " + blockStartRowIndex);
                log.debug("blockStartColIndex = " + blockStartColIndex);
            }

            // ??
            if (log.isDebugEnabled()) {
                log.debug("?????? =" + repeatCount);
            }

            for (int rowIdx = 0; rowIdx < defaultBlockHeight; rowIdx++) {
                // ?
                Row row = sheet.getRow(blockStartRowIndex + rowIdx);
                // ??null???????????????
                if (row == null && !ReportsUtil.isEmptyRow(blockCellTypes[rowIdx], blockCellValues[rowIdx],
                        blockCellStyles[rowIdx])) {
                    row = sheet.createRow(blockStartRowIndex + rowIdx);
                }

                if (row != null) {
                    // ???
                    row.setHeightInPoints(rowHeight[rowIdx]);

                    // ?
                    for (int colIdx = 0; colIdx < defaultBlockWidth; colIdx++) {
                        // ?
                        Cell cell = row.getCell(blockStartColIndex + colIdx);
                        // ??
                        CellType cellType = blockCellTypes[rowIdx][colIdx];
                        // ??
                        Object cellValue = blockCellValues[rowIdx][colIdx];
                        // ??
                        CellStyle cellStyle = blockCellStyles[rowIdx][colIdx];
                        // ?????????????????
                        if (cell == null && !ReportsUtil.isEmptyCell(cellType, cellValue, cellStyle)) {
                            cell = row.createCell(blockStartColIndex + colIdx);
                        }

                        // 
                        if (cell != null) {
                            // ?
                            cell.setCellType(cellType);
                            // ??
                            PoiUtil.setCellValue(cell, cellValue);
                            // ??
                            if (cellStyle == null) {
                                log.info("Cell Style at [" + rowIdx + "," + colIdx
                                        + "] is not available. Skipping setCellValue()");
                            } else {
                                cell.setCellStyle(cellStyle);
                            }
                            log.debug("row=" + (blockStartRowIndex + rowIdx) + " col"
                                    + (blockStartColIndex + colIdx) + ">>>>>>"
                                    + blockCellValues[rowIdx][colIdx]);
                        }
                    }
                }
            }

            int currentBlockHeight = rowlen;
            int currentBlockWidth = collen;
            // ?????????
            int plusRowNum = 0;
            // ?????????
            int plusColNum = 0;
            collen = defaultBlockWidth;
            // 
            // ???
            for (int targetRow = blockStartRowIndex; targetRow < blockStartRowIndex + rowlen
                    + plusRowNum; targetRow++) {
                if (finalBlockRowIndex < targetRow) {
                    finalBlockRowIndex = targetRow;
                }
                if (sheet.getRow(targetRow) == null) {
                    if (log.isDebugEnabled()) {
                        log.debug("row=" + targetRow + " : row is not available. continued...");
                    }
                    continue;
                }

                for (int targetCol = blockStartColIndex; targetCol <= blockStartColIndex + collen + plusColNum
                        - 1; targetCol++) {
                    if (finalBlockColIndex < targetCol) {
                        finalBlockColIndex = targetCol;
                    }
                    Cell targetCell = sheet.getRow(targetRow).getCell(targetCol);
                    if (targetCell == null) {
                        if (log.isDebugEnabled()) {
                            log.debug("row=" + targetRow + " col=" + targetCol
                                    + " : cell is not available. continued...");
                        }
                        continue;
                    }

                    // ??
                    TagParser<?> parser = reportsParserInfo.getMatchTagParser(sheet, targetCell);
                    if (parser == null) {
                        if (log.isDebugEnabled()) {
                            log.debug("row=" + targetRow + " col=" + targetCol
                                    + " parser is not available. continued...");
                        }
                        continue;
                    }

                    String targetCellTag = targetCell.getStringCellValue();
                    if (log.isDebugEnabled()) {
                        log.debug("##########  row=" + targetRow + " col=" + targetCol
                                + " =" + targetCellTag + " ##########");
                    }

                    // 
                    ParsedReportInfo result = (ParsedReportInfo) parser.parse(sheet, targetCell,
                            reportsParserInfo.createChildParserInfo((ParamInfo) paramInfos[repeatCount]));
                    resultList.add(result.getParsedObject());

                    // ???
                    plusRowNum += result.getRowIndex() - result.getDefaultRowIndex();

                    // ???
                    plusColNum += result.getColumnIndex() - result.getDefaultColumnIndex();

                    int additionalHeight = result.getRowIndex() - result.getDefaultRowIndex();
                    int additionalWidth = result.getColumnIndex() - result.getDefaultColumnIndex();

                    // ??????????
                    currentBlockHeight = currentBlockHeight + additionalHeight;
                    currentBlockWidth = currentBlockWidth + additionalWidth;

                    // ???
                    if (parser instanceof SingleParamParser) {
                        if (unduplicableParamMap.containsKey(targetCellTag)) {
                            if (unduplicableParamMap.get(targetCellTag).equals(result.getParsedObject())) {
                                PoiUtil.setCellValue(targetCell, "");

                            } else {
                                unduplicableParamMap.put(targetCellTag, result.getParsedObject());
                            }
                        }
                    }

                    // ??????????????
                    if (defaultFromCellColIndex != result.getDefaultColumnIndex()
                            && result.getRowIndex() > result.getDefaultRowIndex()
                            && blockStartColIndex < targetCol) {
                        CellRangeAddress preRangeAddress = new CellRangeAddress(blockEndRowIndex + 1,
                                blockEndRowIndex + (result.getRowIndex() - result.getDefaultRowIndex()),
                                blockStartColIndex, targetCol - 1);
                        PoiUtil.insertRangeDown(sheet, preRangeAddress);
                        if (log.isDebugEnabled()) {
                            log.debug("******");
                            log.debug("1 : " + (blockEndRowIndex + 1) + ":"
                                    + (blockEndRowIndex + (result.getRowIndex() - result.getDefaultRowIndex()))
                                    + ":" + blockStartColIndex + ":" + (targetCol - 1));
                        }
                    }

                    // R??????
                    if (parser instanceof RowRepeatParamParser
                            && result.getRowIndex() > result.getDefaultRowIndex()) {
                        CellRangeAddress rearRangeAddress = new CellRangeAddress(blockEndRowIndex + 1,
                                blockEndRowIndex + (result.getRowIndex() - result.getDefaultRowIndex()),
                                result.getDefaultColumnIndex() + 1, PoiUtil.getLastColNum(sheet));
                        PoiUtil.insertRangeDown(sheet, rearRangeAddress);
                        if (log.isDebugEnabled()) {
                            log.debug("******");
                            log.debug("2 : " + (blockEndRowIndex + 1) + ":"
                                    + (blockEndRowIndex + (result.getRowIndex() - result.getDefaultRowIndex()))
                                    + ":" + (result.getDefaultColumnIndex() + 1) + ":"
                                    + PoiUtil.getLastColNum(sheet));
                        }
                    }

                    blockEndRowIndex += result.getRowIndex() - result.getDefaultRowIndex();

                    if (parser instanceof BlockColRepeatParamParser
                            || parser instanceof BlockRowRepeatParamParser) {
                        collen += result.getColumnIndex() - result.getDefaultColumnIndex();
                        plusColNum -= result.getColumnIndex() - result.getDefaultColumnIndex();
                    }

                    // ????????
                    if (blockStartColIndex + collen + plusColNum - 1 > blockEndColIndex) {
                        int beforeLastColIndex = blockEndColIndex;
                        blockEndColIndex = blockStartColIndex + collen + plusColNum - 1;

                        // ???????????
                        CellRangeAddress preRangeAddress = new CellRangeAddress(tagCell.getRowIndex(),
                                targetRow - 1, beforeLastColIndex + 1, blockEndColIndex);
                        PoiUtil.insertRangeRight(sheet, preRangeAddress);

                        if (log.isDebugEnabled()) {
                            log.debug("******");
                            log.debug("1 : " + tagCell.getRowIndex() + ":" + (targetRow - 1) + ":"
                                    + (beforeLastColIndex + 1) + ":" + blockEndColIndex);
                        }
                    }

                    // ?
                }
                // ??????
                if (blockStartColIndex + collen + plusColNum - 1 < blockEndColIndex) {
                    CellRangeAddress rearRangeAddress = new CellRangeAddress(targetRow, targetRow,
                            blockStartColIndex + collen + plusColNum, blockEndColIndex);
                    PoiUtil.insertRangeRight(sheet, rearRangeAddress);

                    if (log.isDebugEnabled()) {
                        log.debug("******");
                        log.debug("2 : " + targetRow + ":" + targetRow + ":"
                                + (blockStartColIndex + collen + plusColNum) + ":" + blockEndColIndex);
                    }
                }
                plusColNum = 0;

                // ?
            }
            // ???????
            int lastColNum = PoiUtil.getLastColNum(sheet);
            if ((maxblockEndRowIndex + 1) <= blockEndRowIndex && (blockEndColIndex + 1) <= lastColNum) {
                CellRangeAddress rearRangeAddress = new CellRangeAddress(maxblockEndRowIndex + 1,
                        blockEndRowIndex, blockEndColIndex + 1, lastColNum);
                PoiUtil.insertRangeDown(sheet, rearRangeAddress);
                if (log.isDebugEnabled()) {
                    log.debug("2 : " + (maxblockEndRowIndex + 1) + ":" + blockEndRowIndex + ":"
                            + (blockEndColIndex + 1) + ":" + lastColNum);
                }
            }
            maxblockEndRowIndex = blockEndRowIndex;
        }

        // 
        if (removeTag) {
            tagCell.setCellType(CellType.BLANK);
        }

        // ??
        parsedReportInfo.setColumnIndex(finalBlockColIndex);
        parsedReportInfo.setRowIndex(finalBlockRowIndex);
        parsedReportInfo.setParsedObject(resultList);
        parsedReportInfo.setDefaultRowIndex(defaultToCellRowIndex);
        parsedReportInfo.setDefaultColumnIndex(defaultToCellColIndex);

        if (log.isDebugEnabled()) {
            log.debug("finalBlockRowIndex= " + finalBlockRowIndex);
            log.debug("finalBlockColIndex=" + finalBlockColIndex);
        }

        return parsedReportInfo;

    } catch (Exception e) {
        throw new ParseException(tagCell, e);
    }

}

From source file:org.bbreak.excella.reports.tag.ColRepeatParamParser.java

License:Open Source License

@Override
public ParsedReportInfo parse(Sheet sheet, Cell tagCell, Object data) throws ParseException {

    Map<String, String> paramDef = TagUtil.getParams(tagCell.getStringCellValue());

    // ?/*from  www  .ja  v  a2 s  . c o  m*/
    checkParam(paramDef, tagCell);

    String tag = tagCell.getStringCellValue();
    ReportsParserInfo info = (ReportsParserInfo) data;
    ParamInfo paramInfo = info.getParamInfo();
    ParsedReportInfo parsedReportInfo = new ParsedReportInfo();

    // ??
    Object[] paramValues = null;
    try {
        // ???
        String replaceParam = paramDef.get(PARAM_VALUE);

        // ?
        Integer repeatNum = null;
        if (paramDef.containsKey(PARAM_REPEAT_NUM)) {
            repeatNum = Integer.valueOf(paramDef.get(PARAM_REPEAT_NUM));
        }
        // ??
        Integer minRepeatNum = null;
        if (paramDef.containsKey(PARAM_MIN_REPEAT_NUM)) {
            minRepeatNum = Integer.valueOf(paramDef.get(PARAM_MIN_REPEAT_NUM));
        }

        // ?
        boolean sheetLink = false;
        if (paramDef.containsKey(PARAM_SHEET_LINK)) {
            sheetLink = Boolean.valueOf(paramDef.get(PARAM_SHEET_LINK));
        }

        // 
        String propertyName = null;
        if (paramDef.containsKey(PARAM_PROPERTY)) {
            propertyName = paramDef.get(PARAM_PROPERTY);
        }

        // ???
        boolean hideDuplicate = false;
        if (paramDef.containsKey(PARAM_DUPLICATE)) {
            hideDuplicate = Boolean.valueOf(paramDef.get(PARAM_DUPLICATE));
        }

        // 
        if (ReportsUtil.VALUE_SHEET_NAMES.equals(replaceParam)) {
            // ??
            paramValues = ReportsUtil.getSheetNames(info.getReportBook()).toArray();
        } else if (ReportsUtil.VALUE_SHEET_VALUES.equals(replaceParam)) {
            // 
            paramValues = ReportsUtil
                    .getSheetValues(info.getReportBook(), propertyName, info.getReportParsers()).toArray();
        } else {
            // ???
            if (paramInfo != null) {
                paramValues = getParamData(paramInfo, replaceParam);
            }
        }

        if (paramValues == null || paramValues.length == 0) {
            // ?
            paramValues = new Object[] { null };
        }

        // ?
        if (hideDuplicate && paramValues.length > 1) {
            List<Object> paramValuesList = new ArrayList<Object>();
            for (int i = 0; i <= paramValues.length - 1; i++) {
                // ?????
                if (!paramValuesList.contains(paramValues[i])) {
                    paramValuesList.add(paramValues[i]);
                } else {
                    paramValuesList.add(null);
                }
            }
            paramValues = paramValuesList.toArray();
        }

        // ?
        int shiftNum = paramValues.length;
        // ?
        int paramLength = paramValues.length;

        // ???????
        if (minRepeatNum != null && shiftNum < minRepeatNum) {
            Object[] tmpValues = new Object[minRepeatNum];
            System.arraycopy(paramValues, 0, tmpValues, 0, paramValues.length);
            paramValues = tmpValues;
            shiftNum = paramValues.length;
            paramLength = paramValues.length;
        }

        // ???
        int defaultFromCellRowIndex = tagCell.getRowIndex();
        // ???
        int defaultFromCellColIndex = tagCell.getColumnIndex();

        // ??
        int unitColSize = 1;

        // ???
        List<CellRangeAddress> maegedAddresses = new ArrayList<CellRangeAddress>();
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            CellRangeAddress targetAddress = sheet.getMergedRegion(i);
            maegedAddresses.add(targetAddress);
        }

        // ???
        if (maegedAddresses.size() > 0) {
            // ?????????????
            for (CellRangeAddress curMergedAdress : maegedAddresses) {
                if (defaultFromCellColIndex == curMergedAdress.getFirstColumn()
                        && defaultFromCellRowIndex == curMergedAdress.getFirstRow()) {
                    // ????????????
                    // ??????
                    unitColSize = curMergedAdress.getLastColumn() - curMergedAdress.getFirstColumn() + 1;

                    // ??????
                    shiftNum = shiftNum * unitColSize;
                }
            }
        }

        // ?
        if (repeatNum != null && repeatNum < shiftNum) {
            // ??????
            // ????????????????
            // ?(repeatNum)??(unitColSize)??
            shiftNum = repeatNum * unitColSize;

            // ??????
            paramLength = repeatNum;
        }

        // ???
        // ?????
        tagCell = new CellClone(tagCell);
        List<Cell> cellList = new ArrayList<Cell>();
        int defaultToOverCellColIndex = tagCell.getColumnIndex() + unitColSize;
        for (int i = defaultFromCellColIndex; i < defaultToOverCellColIndex; i++) {
            Row targetCellRow = sheet.getRow(tagCell.getRowIndex());
            cellList.add(new CellClone(targetCellRow.getCell(i)));
        }

        // ?            
        if (shiftNum > 1) {
            // ?(????????)
            int shiftColSize = tagCell.getColumnIndex() + shiftNum - unitColSize - 1;
            // ???
            CellRangeAddress rangeAddress = new CellRangeAddress(tagCell.getRowIndex(), tagCell.getRowIndex(),
                    tagCell.getColumnIndex(), shiftColSize);
            PoiUtil.insertRangeRight(sheet, rangeAddress);
            // 
            int tagCellWidth = sheet.getColumnWidth(tagCell.getColumnIndex());
            for (int i = tagCell.getColumnIndex() + 1; i <= shiftColSize; i++) {
                int colWidth = sheet.getColumnWidth(i);
                if (colWidth < tagCellWidth) {
                    // ??  ???????
                    // ??????
                    sheet.setColumnWidth(i, tagCellWidth);
                }
            }
        }

        // ???
        Workbook workbook = sheet.getWorkbook();
        String sheetName = workbook.getSheetName(workbook.getSheetIndex(sheet));
        // ??
        List<String> sheetNames = ReportsUtil.getSheetNames(info.getReportBook());
        // ?
        List<Object> resultValues = new ArrayList<Object>();
        // ??(beforeValue)
        Object beforeValue = null;

        // ?
        int valueIndex = -1;
        // ?????
        for (int colIndex = 0; colIndex < shiftNum; colIndex++) {
            // ??
            Row row = sheet.getRow(tagCell.getRowIndex());
            if (row == null) {
                // ????
                // ?
                // (??)??????????
                // ??null???(RowCreate?)???????????
                row = sheet.createRow(tagCell.getRowIndex());
            }
            // ??
            Cell cell = row.getCell(tagCell.getColumnIndex() + colIndex);
            if (cell == null) {
                cell = row.createCell(tagCell.getColumnIndex() + colIndex);
            }
            // ????(null)
            Object value = null;

            // ??????
            // ??0???(?????????)???????
            int cellIndex = colIndex % unitColSize;

            // ?????????
            boolean skipCol = false;
            if (cellIndex != 0) {
                skipCol = true;
            } else {
                valueIndex++;
            }

            // 
            // ?
            PoiUtil.copyCell(cellList.get(cellIndex), cell);

            // ?
            Object currentValue = paramValues[valueIndex];
            // ??=true???????????
            boolean duplicateValue = false;
            if (beforeValue != null && currentValue != null && beforeValue.equals(currentValue)) {
                // ???
                duplicateValue = true;
            }
            if (!skipCol && !(hideDuplicate && duplicateValue)) {
                // ??=true
                // ??????????????
                value = currentValue;
            }
            if (log.isDebugEnabled()) {
                log.debug("[??=" + sheetName + ",=(" + cell.getRowIndex() + ","
                        + cell.getColumnIndex() + ")]  " + tag + "  " + value);
            }
            PoiUtil.setCellValue(cell, value);
            resultValues.add(value);

            // ?
            if (sheetLink) {
                if (!skipCol && valueIndex < sheetNames.size()) {
                    PoiUtil.setHyperlink(cell, HyperlinkType.DOCUMENT,
                            "'" + sheetNames.get(valueIndex) + "'!A1");
                    if (log.isDebugEnabled()) {
                        log.debug("[??=" + sheetName + ",=(" + cell.getRowIndex() + ","
                                + cell.getColumnIndex() + ")]  Hyperlink  " + "'"
                                + sheetNames.get(valueIndex) + "'!A1");
                    }
                }
            }

            // ??
            // ??????????????????
            if (!skipCol && unitColSize > 1 && paramLength > valueIndex + 1) {
                CellRangeAddress rangeAddress = new CellRangeAddress(cell.getRowIndex(), cell.getRowIndex(),
                        cell.getColumnIndex(), cell.getColumnIndex() + unitColSize - 1);
                sheet.addMergedRegion(rangeAddress);

                // ????????????
                beforeValue = value;
            }

            // ???????
            if (unitColSize == 1) {
                beforeValue = value;
            }

        }

        parsedReportInfo.setDefaultRowIndex(tagCell.getRowIndex());
        // ??
        parsedReportInfo.setDefaultColumnIndex(tagCell.getColumnIndex() + unitColSize - 1);
        parsedReportInfo.setRowIndex(tagCell.getRowIndex());
        parsedReportInfo.setColumnIndex(tagCell.getColumnIndex() + shiftNum - 1);
        parsedReportInfo.setParsedObject(resultValues);
        if (log.isDebugEnabled()) {
            log.debug(parsedReportInfo);
        }
        return parsedReportInfo;

    } catch (Exception e) {
        throw new ParseException(tagCell, e);
    }

}

From source file:org.bbreak.excella.reports.tag.ImageParamParser.java

License:Open Source License

@Override
public ParsedReportInfo parse(Sheet sheet, Cell tagCell, Object data) throws ParseException {

    Map<String, String> paramDef = TagUtil.getParams(tagCell.getStringCellValue());

    // ?/*from ww  w. j  a  va 2s.  c  o  m*/
    if (hasComments(sheet)) {
        throw new ParseException(
                "?[" + sheet.getWorkbook().getSheetName(sheet.getWorkbook().getSheetIndex(sheet))
                        + "]");
    }

    // ?
    checkParam(paramDef, tagCell);

    ReportsParserInfo reportsParserInfo = (ReportsParserInfo) data;

    ParamInfo paramInfo = reportsParserInfo.getParamInfo();

    String paramValue = null;

    if (paramInfo != null) {
        // ?
        String replaceParam = paramDef.get(PARAM_VALUE);

        paramValue = getParamData(paramInfo, replaceParam);

        // ??
        Integer dx1 = null;
        if (paramDef.containsKey(PARAM_WIDTH)) {
            dx1 = Integer.valueOf(paramDef.get(PARAM_WIDTH));
        }

        // ???
        Integer dy1 = null;
        if (paramDef.containsKey(PARAM_HEIGHT)) {
            dy1 = Integer.valueOf(paramDef.get(PARAM_HEIGHT));
        }

        // ???
        Double scale = 1.0;
        if (paramDef.containsKey(PARAM_SCALE)) {
            scale = Double.valueOf(paramDef.get(PARAM_SCALE));
        }

        // ??????
        if (ReportsUtil.getMergedAddress(sheet, tagCell.getRowIndex(), tagCell.getColumnIndex()) != null) {
            CellStyle cellStyle = tagCell.getCellStyle();
            tagCell.setCellType(CellType.BLANK);
            tagCell.setCellStyle(cellStyle);
        } else {
            tagCell = new CellClone(tagCell);
            PoiUtil.clearCell(sheet, new CellRangeAddress(tagCell.getRowIndex(), tagCell.getRowIndex(),
                    tagCell.getColumnIndex(), tagCell.getColumnIndex()));
        }

        if (log.isDebugEnabled()) {
            Workbook workbook = sheet.getWorkbook();
            String sheetName = workbook.getSheetName(workbook.getSheetIndex(sheet));

            log.debug("[??=" + sheetName + ",=(" + tagCell.getRowIndex() + ","
                    + tagCell.getColumnIndex() + ")]  " + tagCell.getStringCellValue() + "  " + paramValue);
        }

        if (paramValue != null) {
            replaceImageValue(sheet, tagCell, paramValue, dx1, dy1, scale);
        }

    }

    // ????
    ParsedReportInfo parsedReportInfo = new ParsedReportInfo();
    parsedReportInfo.setParsedObject(paramValue);
    parsedReportInfo.setRowIndex(tagCell.getRowIndex());
    parsedReportInfo.setColumnIndex(tagCell.getColumnIndex());
    parsedReportInfo.setDefaultRowIndex(tagCell.getRowIndex());
    parsedReportInfo.setDefaultColumnIndex(tagCell.getColumnIndex());

    return parsedReportInfo;
}

From source file:org.bbreak.excella.reports.tag.RowRepeatParamParser.java

License:Open Source License

@Override
public ParsedReportInfo parse(Sheet sheet, Cell tagCell, Object data) throws ParseException {

    Map<String, String> paramDef = TagUtil.getParams(tagCell.getStringCellValue());

    // ?// www  . j  av  a2 s  .co m
    checkParam(paramDef, tagCell);

    String tag = tagCell.getStringCellValue();
    ReportsParserInfo reportsParserInfo = (ReportsParserInfo) data;
    // ?
    Object[] paramValues = null;
    try {
        // ?
        boolean rowShift = false;
        if (paramDef.containsKey(PARAM_ROW_SHIFT)) {
            rowShift = Boolean.valueOf(paramDef.get(PARAM_ROW_SHIFT));
        }
        // ??
        boolean hideDuplicate = false;
        if (paramDef.containsKey(PARAM_DUPLICATE)) {
            hideDuplicate = Boolean.valueOf(paramDef.get(PARAM_DUPLICATE));
        }
        // ?
        Integer breakNum = null;
        if (paramDef.containsKey(PARAM_BREAK_NUM)) {
            breakNum = Integer.valueOf(paramDef.get(PARAM_BREAK_NUM));
        }
        // 
        boolean changeBreak = false;
        if (paramDef.containsKey(PARAM_CHANGE_BREAK)) {
            changeBreak = Boolean.valueOf(paramDef.get(PARAM_CHANGE_BREAK));
        }
        // ?
        Integer repeatNum = null;
        if (paramDef.containsKey(PARAM_REPEAT_NUM)) {
            repeatNum = Integer.valueOf(paramDef.get(PARAM_REPEAT_NUM));
        }
        // ??
        Integer minRepeatNum = null;
        if (paramDef.containsKey(PARAM_MIN_REPEAT_NUM)) {
            minRepeatNum = Integer.valueOf(paramDef.get(PARAM_MIN_REPEAT_NUM));
        }
        // ?
        boolean sheetLink = false;
        if (paramDef.containsKey(PARAM_SHEET_LINK)) {
            sheetLink = Boolean.valueOf(paramDef.get(PARAM_SHEET_LINK));
        }
        // 
        String propertyName = null;
        if (paramDef.containsKey(PARAM_PROPERTY)) {
            propertyName = paramDef.get(PARAM_PROPERTY);
        }
        // ???
        String replaceParam = paramDef.get(PARAM_VALUE);
        // 
        if (ReportsUtil.VALUE_SHEET_NAMES.equals(replaceParam)) {
            // ??
            paramValues = ReportsUtil.getSheetNames(reportsParserInfo.getReportBook()).toArray();
        } else if (ReportsUtil.VALUE_SHEET_VALUES.equals(replaceParam)) {
            // 
            paramValues = ReportsUtil.getSheetValues(reportsParserInfo.getReportBook(), propertyName,
                    reportsParserInfo.getReportParsers()).toArray();
        } else {
            // ???
            ParamInfo paramInfo = reportsParserInfo.getParamInfo();
            if (paramInfo != null) {
                paramValues = getParamData(paramInfo, replaceParam);
            }
        }

        if (paramValues == null || paramValues.length == 0) {
            // ?
            paramValues = new Object[] { null };
        }

        // ?
        int shiftNum = paramValues.length;
        // ?
        int paramLength = paramValues.length;

        // ???????
        if (minRepeatNum != null && shiftNum < minRepeatNum) {
            Object[] tmpValues = new Object[minRepeatNum];
            System.arraycopy(paramValues, 0, tmpValues, 0, paramValues.length);
            paramValues = tmpValues;
            shiftNum = paramValues.length;
            paramLength = paramValues.length;
        }

        // ???
        int defaultFromCellRowIndex = tagCell.getRowIndex();
        // ???
        int defaultFromCellColIndex = tagCell.getColumnIndex();

        // ???
        int unitRowSize = 1;

        // ???
        List<CellRangeAddress> maegedAddresses = new ArrayList<CellRangeAddress>();
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            CellRangeAddress targetAddress = sheet.getMergedRegion(i);
            maegedAddresses.add(targetAddress);
        }

        // ???
        if (maegedAddresses.size() > 0) {
            // ?????????????
            for (CellRangeAddress curMergedAdress : maegedAddresses) {
                if (defaultFromCellColIndex == curMergedAdress.getFirstColumn()
                        && defaultFromCellRowIndex == curMergedAdress.getFirstRow()) {
                    // ????????????
                    // ??????
                    unitRowSize = curMergedAdress.getLastRow() - curMergedAdress.getFirstRow() + 1;

                    // ??????
                    shiftNum = shiftNum * unitRowSize;
                }
            }
        }

        // ???
        // ?????
        tagCell = new CellClone(tagCell);
        List<Cell> cellList = new ArrayList<Cell>();
        int defaultToOverCellRowIndex = tagCell.getRowIndex() + unitRowSize;
        for (int i = defaultFromCellRowIndex; i < defaultToOverCellRowIndex; i++) {
            Row targetCellRow = sheet.getRow(i);
            cellList.add(new CellClone(targetCellRow.getCell(tagCell.getColumnIndex())));
        }

        // ?
        if (repeatNum != null && repeatNum < shiftNum) {
            // ??????
            // ????????????????
            // ?????
            shiftNum = repeatNum * unitRowSize;

            // ??????
            paramLength = repeatNum;
        }

        // ?
        if (shiftNum > 1) {
            // ?(????????)
            int shiftRowSize = tagCell.getRowIndex() + shiftNum - unitRowSize - 1;
            if (!rowShift) {
                // ?????
                CellRangeAddress rangeAddress = new CellRangeAddress(tagCell.getRowIndex(), shiftRowSize,
                        tagCell.getColumnIndex(), tagCell.getColumnIndex());
                PoiUtil.insertRangeDown(sheet, rangeAddress);
            } else {
                // ?????
                // #35 POI???????????????????
                // ??????0????????
                CellRangeAddress rangeAddress = new CellRangeAddress(tagCell.getRowIndex() + unitRowSize,
                        tagCell.getRowIndex() + shiftNum - 1, 0, PoiUtil.getLastColNum(sheet));
                PoiUtil.insertRangeDown(sheet, rangeAddress);
                // int shiftStartRow = tagCell.getRowIndex() + 1;
                // int shiftEndRow = sheet.getLastRowNum();
                // if ( shiftEndRow < shiftStartRow) {
                // // ????????????????
                // // ????????????
                // shiftEndRow = shiftStartRow + 1;
                // }
                // sheet.shiftRows( shiftStartRow, shiftEndRow, shiftNum - unitRowSize);
            }
        }

        // ???
        Workbook workbook = sheet.getWorkbook();
        String sheetName = workbook.getSheetName(workbook.getSheetIndex(sheet));
        // ??
        List<String> sheetNames = ReportsUtil.getSheetNames(reportsParserInfo.getReportBook());
        // ?
        List<Object> resultValues = new ArrayList<Object>();
        // ??(beforeValue)
        Object beforeValue = null;

        // ?
        int valueIndex = -1;
        // ?????
        for (int rowIndex = 0; rowIndex < shiftNum; rowIndex++) {
            // ??
            Row row = sheet.getRow(tagCell.getRowIndex() + rowIndex);
            if (row == null) {
                row = sheet.createRow(tagCell.getRowIndex() + rowIndex);
            }
            // ??
            Cell cell = row.getCell(tagCell.getColumnIndex());
            if (cell == null) {
                cell = row.createCell(tagCell.getColumnIndex());
            }
            // ????(null)
            Object value = null;

            // ??????
            // ??0???(?????????)???????
            int cellIndex = rowIndex % unitRowSize;

            // ?????????
            boolean skipRow = false;
            if (cellIndex != 0) {
                skipRow = true;
            } else {
                valueIndex++;
            }

            // (?)
            PoiUtil.copyCell(cellList.get(cellIndex), cell);

            // ?
            Object currentValue = paramValues[valueIndex];
            // ??=true???????????
            boolean duplicateValue = false;
            if (beforeValue != null && currentValue != null && beforeValue.equals(currentValue)) {
                // ???
                duplicateValue = true;
            }
            if (!skipRow && !(hideDuplicate && duplicateValue)) {
                // ??=true
                // ??????????????
                value = currentValue;
            }
            if (log.isDebugEnabled()) {
                log.debug("[??=" + sheetName + ",=(" + cell.getRowIndex() + ","
                        + cell.getColumnIndex() + ")]  " + tag + "  " + value);
            }
            PoiUtil.setCellValue(cell, value);
            resultValues.add(value);

            // ?
            if (sheetLink) {
                if (!skipRow && valueIndex < sheetNames.size()) {
                    PoiUtil.setHyperlink(cell, HyperlinkType.DOCUMENT,
                            "'" + sheetNames.get(valueIndex) + "'!A1");
                    if (log.isDebugEnabled()) {
                        log.debug("[??=" + sheetName + ",=(" + cell.getRowIndex() + ","
                                + cell.getColumnIndex() + ")]  Hyperlink  " + "'"
                                + sheetNames.get(valueIndex) + "'!A1");
                    }
                }
            }

            // 
            if (!skipRow) {
                if (breakNum != null && valueIndex != 0 && valueIndex % breakNum == 0) {
                    // ???
                    sheet.setRowBreak(row.getRowNum() - 1);
                }
                if (changeBreak && valueIndex != 0 && !duplicateValue) {
                    // ???
                    sheet.setRowBreak(row.getRowNum() - 1);
                }
            }

            // ??
            // ??????????
            // ????????????
            if (!skipRow && unitRowSize > 1) {
                // ???
                boolean mergedRegionFlag = false;
                if (rowShift && valueIndex != 0) {
                    // ?????????????
                    mergedRegionFlag = true;
                } else if (!rowShift && paramLength > valueIndex + 1) {
                    // ????????????
                    mergedRegionFlag = true;
                }

                // ??
                if (mergedRegionFlag) {
                    CellRangeAddress rangeAddress = new CellRangeAddress(cell.getRowIndex(),
                            cell.getRowIndex() + unitRowSize - 1, cell.getColumnIndex(), cell.getColumnIndex());
                    sheet.addMergedRegion(rangeAddress);

                    // ????????????
                    beforeValue = currentValue;
                }
            }

            // ???????
            if (unitRowSize == 1) {
                beforeValue = currentValue;
            }

        }

        // ????
        ParsedReportInfo parsedReportInfo = new ParsedReportInfo();
        parsedReportInfo.setParsedObject(resultValues);
        // ??
        parsedReportInfo.setDefaultRowIndex(tagCell.getRowIndex() + unitRowSize - 1);
        parsedReportInfo.setDefaultColumnIndex(tagCell.getColumnIndex());
        parsedReportInfo.setRowIndex(tagCell.getRowIndex() + shiftNum - 1);
        parsedReportInfo.setColumnIndex(tagCell.getColumnIndex());
        if (log.isDebugEnabled()) {
            log.debug(parsedReportInfo);
        }

        return parsedReportInfo;

    } catch (Exception e) {
        throw new ParseException(tagCell, e);
    }
}

From source file:org.bbreak.excella.reports.util.ReportsUtil.java

License:Open Source License

/**
 * ?????????//from   w  w w.j ava  2 s. com
 * 
 * @param sheet 
 * @param rowIndex 
 * @param columnIndex 
 * @return ??
 */
public static CellRangeAddress getMergedAddress(Sheet sheet, int rowIndex, int columnIndex) {

    CellRangeAddress rangeAddress = new CellRangeAddress(rowIndex, rowIndex, columnIndex, columnIndex);

    int fromSheetMargNums = sheet.getNumMergedRegions();
    for (int i = 0; i < fromSheetMargNums; i++) {
        CellRangeAddress mergedAddress = null;
        if (sheet instanceof XSSFSheet) {
            mergedAddress = ((XSSFSheet) sheet).getMergedRegion(i);
        } else if (sheet instanceof HSSFSheet) {
            mergedAddress = ((HSSFSheet) sheet).getMergedRegion(i);
        }

        // fromAddress????
        if (PoiUtil.containCellRangeAddress(mergedAddress, rangeAddress)) {
            return mergedAddress;
        }
    }
    return null;
}

From source file:org.bbreak.excella.reports.util.ReportsUtil.java

License:Open Source License

/**
 * ???????????//from  w w  w  .j ava2  s.  co m
 * 
 * @param sheet ??
 * @param baseFromCellRowIndex ?
 * @param baseToCellRowIndex ?
 * @param baseFromCellColIndex ?
 * @param baseToCellColIndex ?
 * @return ??
 */
public static CellRangeAddress[] getMargedCells(Sheet sheet, int baseFromCellRowIndex, int baseToCellRowIndex,
        int baseFromCellColIndex, int baseToCellColIndex) {
    CellRangeAddress baseAddress = new CellRangeAddress(baseFromCellRowIndex, baseToCellRowIndex,
            baseFromCellColIndex, baseToCellColIndex);
    int num = sheet.getNumMergedRegions();
    List<CellRangeAddress> maegedAddresses = new ArrayList<CellRangeAddress>();
    for (int i = 0; i < num; i++) {
        CellRangeAddress targetAddress = sheet.getMergedRegion(i);
        if (PoiUtil.containCellRangeAddress(baseAddress, targetAddress)) {
            maegedAddresses.add(targetAddress);
        }
    }
    return maegedAddresses.toArray(new CellRangeAddress[0]);
}

From source file:org.bbreak.excella.reports.util.ReportsUtilTest.java

License:Open Source License

/**
 * {@link org.bbreak.excella.reports.util.ReportsUtil#getMergedAddress(Sheet, int, int)} ????
 *//*from  w w w . j a va2 s  . co  m*/
@Test
public void testgetMergedAddress() {

    // ?
    Workbook hssfWb = new HSSFWorkbook();

    // ?
    Sheet hssfSheet = hssfWb.createSheet("testsheet");

    // ?
    Row hssfRow = hssfSheet.createRow(0);

    // ?
    hssfRow.createCell(0);
    hssfRow.createCell(1);
    hssfRow.createCell(2);

    // ??
    CellRangeAddress address1 = new CellRangeAddress(0, 1, 1, 1);
    hssfSheet.addMergedRegion(address1);
    CellRangeAddress address2 = new CellRangeAddress(0, 0, 2, 3);
    hssfSheet.addMergedRegion(address2);

    // 
    assertNull(ReportsUtil.getMergedAddress(hssfSheet, 0, 0));
    assertEquals(address1.toString(), ReportsUtil.getMergedAddress(hssfSheet, 0, 1).toString());
    assertEquals(address2.toString(), ReportsUtil.getMergedAddress(hssfSheet, 0, 2).toString());

    try {
        hssfWb.close();
    } catch (IOException e) {
    }

    // ?
    Workbook xssfWb = new XSSFWorkbook();

    // ?
    Sheet xssfSheet = xssfWb.createSheet("testsheet");

    // ?
    Row xssfRow = xssfSheet.createRow(0);

    // ?
    xssfRow.createCell(0);
    xssfRow.createCell(1);
    xssfRow.createCell(2);

    // ??
    address1 = new CellRangeAddress(0, 1, 1, 1);
    xssfSheet.addMergedRegion(address1);
    address2 = new CellRangeAddress(0, 0, 2, 3);
    xssfSheet.addMergedRegion(address2);

    // 
    assertNull(ReportsUtil.getMergedAddress(xssfSheet, 0, 0));
    assertEquals(address1.toString(), ReportsUtil.getMergedAddress(xssfSheet, 0, 1).toString());
    assertEquals(address2.toString(), ReportsUtil.getMergedAddress(xssfSheet, 0, 2).toString());

    try {
        xssfWb.close();
    } catch (IOException e) {
    }
}

From source file:org.corpus_tools.peppermodules.spreadsheet.tests.Spreadsheet2SaltMapperTest.java

License:Apache License

private void createSecondXlsxSample() throws IOException {

    xlsxWb = new XSSFWorkbook();
    Sheet xlsxSheet = xlsxWb.createSheet("secondXlsxSample");
    Row xlsxRow1 = xlsxSheet.createRow(0);
    Cell xlsxCell1 = xlsxRow1.createCell(0);
    xlsxCell1.setCellValue("tok");
    Cell xlsxCell2 = xlsxRow1.createCell(1);
    xlsxCell2.setCellValue("anno1[tok]");
    Cell xlsxCell3 = xlsxRow1.createCell(2);
    xlsxCell3.setCellValue("tok2");
    Cell xlsxCell4 = xlsxRow1.createCell(3);
    xlsxCell4.setCellValue("anno2[tok2]");

    Row xlsxRow2 = xlsxSheet.createRow(1);
    Cell xlsxCell21 = xlsxRow2.createCell(0);
    xlsxCell21.setCellValue("This");
    Cell xlsxCell22 = xlsxRow2.createCell(1);
    xlsxCell22.setCellValue("pron1");
    Cell xlsxCell23 = xlsxRow2.createCell(2);
    xlsxCell23.setCellValue("This");
    Cell xlsxCell24 = xlsxRow2.createCell(3);
    xlsxCell24.setCellValue("pron2");

    Row xlsxRow3 = xlsxSheet.createRow(2);
    Cell xlsxCell31 = xlsxRow3.createCell(0);
    xlsxCell31.setCellValue("is");
    Cell xlsxCell32 = xlsxRow3.createCell(1);
    xlsxCell32.setCellValue("verb1");
    Cell xlsxCell33 = xlsxRow3.createCell(2);
    xlsxCell33.setCellValue("is");
    Cell xlsxCell34 = xlsxRow3.createCell(3);
    xlsxCell34.setCellValue("verb2");

    Row xlsxRow4 = xlsxSheet.createRow(3);
    Cell xlsxCell41 = xlsxRow4.createCell(0);
    xlsxCell41.setCellValue("an");
    Cell xlsxCell42 = xlsxRow4.createCell(1);
    xlsxCell42.setCellValue("art1");
    Cell xlsxCell43 = xlsxRow4.createCell(2);
    xlsxCell43.setCellValue("an");
    Cell xlsxCell44 = xlsxRow4.createCell(3);
    xlsxCell44.setCellValue("art2");

    Row xlsRow5 = xlsxSheet.createRow(4);
    Cell xlsCell51 = xlsRow5.createCell(0);
    xlsCell51.setCellValue("example");
    Cell xlsCell52 = xlsRow5.createCell(1);
    xlsCell52.setCellValue("noun1");
    Cell xlsCell53 = xlsRow5.createCell(2);
    xlsCell53.setCellValue("ex-");
    Cell xlsCell54 = xlsRow5.createCell(3);
    xlsCell54.setCellValue("noun2");

    Row xlsxRow6 = xlsxSheet.createRow(5);
    xlsxRow6.createCell(0);/* w w  w. j ava  2s  .  c  o  m*/
    xlsxRow6.createCell(1);
    Cell xlsxCell63 = xlsxRow6.createCell(2);
    xlsxCell63.setCellValue("ample");
    xlsxRow6.createCell(3);
    CellRangeAddress mergedPrim = new CellRangeAddress(4, 5, 0, 0);
    xlsxSheet.addMergedRegion(mergedPrim);
    CellRangeAddress mergedAnno1 = new CellRangeAddress(4, 5, 1, 1);
    xlsxSheet.addMergedRegion(mergedAnno1);
    CellRangeAddress mergedAnno2 = new CellRangeAddress(4, 5, 3, 3);
    xlsxSheet.addMergedRegion(mergedAnno2);

    Row xlsxRow7 = xlsxSheet.createRow(6);
    Cell xlsxCell71 = xlsxRow7.createCell(0);
    xlsxCell71.setCellValue(".");
    Cell xlsxCell72 = xlsxRow7.createCell(1);
    xlsxCell72.setCellValue("punct1");
    Cell xlsxCell73 = xlsxRow7.createCell(2);
    xlsxCell73.setCellValue(".");
    Cell xlsxCell74 = xlsxRow7.createCell(3);
    xlsxCell74.setCellValue("punct2");

    xlsxWb.write(outStream);
}

From source file:org.corpus_tools.peppermodules.spreadsheet.tests.Spreadsheet2SaltMapperTest.java

License:Apache License

private void createThirdXlsxSample() throws IOException {

    xlsxWb = new XSSFWorkbook();
    Sheet xlsxSheet = xlsxWb.createSheet("ThirdXlsxSample");
    Row xlsxRow1 = xlsxSheet.createRow(0);
    Cell xlsxCell1 = xlsxRow1.createCell(0);
    xlsxCell1.setCellValue("tok");
    Cell xlsxCell2 = xlsxRow1.createCell(1);
    xlsxCell2.setCellValue("anno1");
    Cell xlsxCell3 = xlsxRow1.createCell(2);
    xlsxCell3.setCellValue("tok2");
    Cell xlsxCell4 = xlsxRow1.createCell(3);
    xlsxCell4.setCellValue("anno2");

    Row xlsxRow2 = xlsxSheet.createRow(1);
    Cell xlsxCell21 = xlsxRow2.createCell(0);
    xlsxCell21.setCellValue("This");
    Cell xlsxCell22 = xlsxRow2.createCell(1);
    xlsxCell22.setCellValue("pron1");
    Cell xlsxCell23 = xlsxRow2.createCell(2);
    xlsxCell23.setCellValue("This");
    Cell xlsxCell24 = xlsxRow2.createCell(3);
    xlsxCell24.setCellValue("pron2");

    Row xlsxRow3 = xlsxSheet.createRow(2);
    Cell xlsxCell31 = xlsxRow3.createCell(0);
    xlsxCell31.setCellValue("is");
    Cell xlsxCell32 = xlsxRow3.createCell(1);
    xlsxCell32.setCellValue("verb1");
    Cell xlsxCell33 = xlsxRow3.createCell(2);
    xlsxCell33.setCellValue("is");
    Cell xlsxCell34 = xlsxRow3.createCell(3);
    xlsxCell34.setCellValue("verb2");

    Row xlsxRow4 = xlsxSheet.createRow(3);
    Cell xlsxCell41 = xlsxRow4.createCell(0);
    xlsxCell41.setCellValue("an");
    Cell xlsxCell42 = xlsxRow4.createCell(1);
    xlsxCell42.setCellValue("art1");
    Cell xlsxCell43 = xlsxRow4.createCell(2);
    xlsxCell43.setCellValue("an");
    Cell xlsxCell44 = xlsxRow4.createCell(3);
    xlsxCell44.setCellValue("art2");

    Row xlsRow5 = xlsxSheet.createRow(4);
    Cell xlsCell51 = xlsRow5.createCell(0);
    xlsCell51.setCellValue("example");
    Cell xlsCell52 = xlsRow5.createCell(1);
    xlsCell52.setCellValue("noun1");
    Cell xlsCell53 = xlsRow5.createCell(2);
    xlsCell53.setCellValue("ex-");
    Cell xlsCell54 = xlsRow5.createCell(3);
    xlsCell54.setCellValue("noun2");

    Row xlsxRow6 = xlsxSheet.createRow(5);
    xlsxRow6.createCell(0);// w ww .  ja va  2s  .  c o  m
    xlsxRow6.createCell(1);
    Cell xlsxCell63 = xlsxRow6.createCell(2);
    xlsxCell63.setCellValue("ample");
    xlsxRow6.createCell(3);
    CellRangeAddress mergedPrim = new CellRangeAddress(4, 5, 0, 0);
    xlsxSheet.addMergedRegion(mergedPrim);
    CellRangeAddress mergedAnno1 = new CellRangeAddress(4, 5, 1, 1);
    xlsxSheet.addMergedRegion(mergedAnno1);
    CellRangeAddress mergedAnno2 = new CellRangeAddress(4, 5, 3, 3);
    xlsxSheet.addMergedRegion(mergedAnno2);

    Row xlsxRow7 = xlsxSheet.createRow(6);
    Cell xlsxCell71 = xlsxRow7.createCell(0);
    xlsxCell71.setCellValue(".");
    Cell xlsxCell72 = xlsxRow7.createCell(1);
    xlsxCell72.setCellValue("punct1");
    Cell xlsxCell73 = xlsxRow7.createCell(2);
    xlsxCell73.setCellValue(".");
    Cell xlsxCell74 = xlsxRow7.createCell(3);
    xlsxCell74.setCellValue("punct2");

    xlsxWb.write(outStream);
}