Example usage for org.apache.poi.ss.usermodel Sheet getWorkbook

List of usage examples for org.apache.poi.ss.usermodel Sheet getWorkbook

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. getWorkbook

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Sheet getWorkbook.

Prototype

Workbook getWorkbook();

Source Link

Document

Return the parent workbook

Usage

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

License:Open Source License

private void checkSheet(String expectedSheetName, Sheet actualSheet, boolean outputExcel) {

    // ???/*from w  w  w .j  a v  a2  s. co  m*/
    Workbook expectedWorkbook = getExpectedWorkbook();
    Sheet expectedSheet = expectedWorkbook.getSheet(expectedSheetName);

    expectedSheet.getPrintSetup().getCopies();

    try {
        // ?
        ReportsTestUtil.checkSheet(expectedSheet, actualSheet, false);
    } catch (ReportsCheckException e) {
        fail(e.getCheckMessagesToString());
    } finally {
        String tmpDirPath = ReportsTestUtil.getTestOutputDir();
        try {
            String filepath = null;
            Date now = new Date();
            if (version.equals("2007")) {
                filepath = tmpDirPath + this.getClass().getSimpleName() + now.getTime() + ".xlsx";
            } else {
                filepath = tmpDirPath + this.getClass().getSimpleName() + now.getTime() + ".xls";
            }
            PoiUtil.writeBook(actualSheet.getWorkbook(), filepath);

        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

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 w  w  w .  j  a v a 2s.co  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());

    // ?//w ww.java  2s.c  om
    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.ImageParamParser.java

License:Open Source License

/**
 * ??/*  ww  w  . ja v  a  2s .  co m*/
 * 
 * @param sheet ?
 * @param cell 
 * @param filePath ?
 * @param dx1 ??
 * @param dy1 ???
 * @param scale ???
 * @throws ParseException
 */
public void replaceImageValue(Sheet sheet, Cell cell, String filePath, Integer dx1, Integer dy1, Double scale)
        throws ParseException {

    Workbook workbook = sheet.getWorkbook();

    int format = -1;
    if (filePath.toLowerCase().endsWith(JPEG_SUFFIX) || filePath.toLowerCase().endsWith(JPG_SUFFIX)) {
        format = Workbook.PICTURE_TYPE_JPEG;
    } else if (filePath.toLowerCase().endsWith(PNG_SUFFIX)) {
        format = Workbook.PICTURE_TYPE_PNG;
    }
    if (format == -1) {
        throw new ParseException(cell,
                "????????" + filePath);
    }

    byte[] bytes = null;
    InputStream is = null;
    try {
        is = new FileInputStream(filePath);
        bytes = IOUtils.toByteArray(is);
    } catch (Exception e) {
        throw new ParseException(cell, e);
    } finally {
        try {
            is.close();
        } catch (IOException e) {
            throw new ParseException(cell, e);
        }
    }

    int pictureIdx = workbook.addPicture(bytes, format);

    CreationHelper helper = workbook.getCreationHelper();

    @SuppressWarnings("rawtypes")
    Drawing drawing = drawingCash.get(sheet);
    if (drawing == null) {
        drawing = sheet.createDrawingPatriarch();
        drawingCash.put(sheet, drawing);
    }

    ClientAnchor anchor = helper.createClientAnchor();

    anchor.setRow1(cell.getRowIndex());
    anchor.setCol1(cell.getColumnIndex());
    anchor.setRow2(cell.getRowIndex() + 1);
    anchor.setCol2(cell.getColumnIndex() + 1);
    if (dx1 != null) {
        anchor.setDx1(dx1);
    }
    if (dy1 != null) {
        anchor.setDy1(dy1);
    }

    Picture picture = drawing.createPicture(anchor, pictureIdx);
    picture.resize(scale);

}

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());

    // ?/*  ww w .  jav a  2 s.c  om*/
    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.tag.SingleParamParser.java

License:Open Source License

@Override
public ParsedReportInfo parse(Sheet sheet, Cell tagCell, Object data) throws ParseException {
    List<String> tmpTargets = new ArrayList<String>();
    StringTokenizer tagTokenizer = new StringTokenizer(tagCell.getStringCellValue(), getTag());
    while (tagTokenizer.hasMoreTokens()) {
        tmpTargets.add(tagTokenizer.nextToken());
    }/*from  ww  w .  j  a  v a 2  s  .  com*/

    List<String> finalTargets = new ArrayList<String>();
    for (String tempTarget : tmpTargets) {
        if (tempTarget.startsWith(TagParser.TAG_PARAM_PREFIX)
                && !tempTarget.endsWith(TagParser.TAG_PARAM_SUFFIX)) {
            finalTargets.add(tempTarget.substring(0, tempTarget.indexOf(TagParser.TAG_PARAM_SUFFIX) + 1));
            finalTargets.add(tempTarget.substring(tempTarget.indexOf(TagParser.TAG_PARAM_SUFFIX) + 1));
            continue;
        }
        finalTargets.add(tempTarget);
    }

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

    for (int i = 0; i < finalTargets.size(); i++) {
        String target = finalTargets.get(i);
        if (!target.startsWith(TagParser.TAG_PARAM_PREFIX)) {
            // ???
            paramValues.add(target);
            continue;
        } else {
            // ?????
            target = getTag() + target;
        }

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

        // ?
        checkParam(paramDef, tagCell);

        ReportsParserInfo reportsParserInfo = (ReportsParserInfo) data;

        ParamInfo paramInfo = reportsParserInfo.getParamInfo();

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

            // ???
            paramValues.add(getParamData(paramInfo, replaceParam));

            if (log.isDebugEnabled()) {
                Workbook workbook = sheet.getWorkbook();
                String sheetName = workbook.getSheetName(workbook.getSheetIndex(sheet));
                log.debug("[??=" + sheetName + ",=(" + tagCell.getRowIndex() + ","
                        + tagCell.getColumnIndex() + ")] " + tagCell.getStringCellValue() + "  "
                        + paramValues.get(i));
            }
        }
    }

    // ?
    if (paramValues.size() > 1) {
        StringBuilder builder = new StringBuilder();
        for (Object object : paramValues) {
            if (object == null) {
                continue;
            }
            if (object instanceof Date) {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
                builder.append(sdf.format(object));
                continue;
            }
            builder.append(object);
        }
        PoiUtil.setCellValue(tagCell, builder.toString());
        parsedReportInfo.setParsedObject(builder.toString());
    } else if (paramValues.size() == 1) {
        PoiUtil.setCellValue(tagCell, paramValues.get(0));
        parsedReportInfo.setParsedObject(paramValues.get(0));
    }
    parsedReportInfo.setDefaultRowIndex(tagCell.getRowIndex());
    parsedReportInfo.setDefaultColumnIndex(tagCell.getColumnIndex());
    parsedReportInfo.setRowIndex(tagCell.getRowIndex());
    parsedReportInfo.setColumnIndex(tagCell.getColumnIndex());

    return parsedReportInfo;
}

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

License:Open Source License

private void checkSheet(String expectedSheetName, Sheet actualSheet, boolean outputExcel) {

    // ???/*from  w w w . j a  v a  2 s .co m*/
    Workbook expectedWorkbook = getExpectedWorkbook();
    Sheet expectedSheet = expectedWorkbook.getSheet(expectedSheetName);

    try {
        // ?
        ReportsTestUtil.checkSheet(expectedSheet, actualSheet, false);
    } catch (ReportsCheckException e) {
        fail(e.getCheckMessagesToString());
    } finally {
        if (outputExcel) {
            String tmpDirPath = ReportsTestUtil.getTestOutputDir();
            try {
                String filepath = null;
                Date now = new Date();
                if (version.equals("2007")) {
                    filepath = tmpDirPath + this.getClass().getSimpleName() + now.getTime() + ".xlsx";
                } else {
                    filepath = tmpDirPath + this.getClass().getSimpleName() + now.getTime() + ".xls";
                }
                PoiUtil.writeBook(actualSheet.getWorkbook(), filepath);

            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}

From source file:org.bbreak.excella.trans.tag.sheet2java.SheetToJavaExecuter.java

License:Open Source License

/**
 * ??<BR>/*from   w w  w  . j a  va 2s . co  m*/
 * ?????????<BR>
 * ????SheetData??<BR>
 * 
 * @param sheet 
 * @param sheetParser 
 * @param sheetData ??
 */
@SuppressWarnings("unchecked")
public void postParse(Sheet sheet, SheetParser sheetParser, SheetData sheetData) throws ParseException {

    // ??
    List<Object> results = new ArrayList<Object>();

    // ?SheetToJavaParser???
    List<TagParser<?>> tagParsers = sheetParser.getTagParsers();

    // ?????SheetToJavaSettingParser?
    // ??
    List<String> removeTags = new ArrayList<String>();

    // ????
    List<String> targetTags = new ArrayList<String>();
    for (TagParser<?> tagParser : tagParsers) {
        // SheetToJavaParser?
        if (tagParser instanceof SheetToJavaParser) {
            targetTags.add(tagParser.getTag());
        }
        // SheetToJavaSettingParser?
        if (tagParser instanceof SheetToJavaSettingParser) {
            removeTags.add(tagParser.getTag());
        }
    }

    // ?
    Workbook workbook = sheet.getWorkbook();

    // ???
    for (String tag : targetTags) {

        List<SheetToJavaParseInfo> sheetInfoList = (List<SheetToJavaParseInfo>) sheetData.get(tag);

        if (sheetInfoList == null) {
            continue;
        }

        // ()???
        for (SheetToJavaParseInfo sheetInfo : sheetInfoList) {

            List<SheetToJavaSettingInfo> allColumnInfoList = (List<SheetToJavaSettingInfo>) sheetData
                    .get(sheetInfo.getSettingTagName());

            // ???
            List<SheetToJavaSettingInfo> targetColumnInfoList = new ArrayList<SheetToJavaSettingInfo>();
            for (SheetToJavaSettingInfo columnInfo : allColumnInfoList) {
                if (columnInfo.getSheetName().equals(sheetInfo.getSheetName())) {
                    targetColumnInfoList.add(columnInfo);
                }
            }

            // ???
            Sheet targetSheet = workbook.getSheet(sheetInfo.getSheetName());
            if (targetSheet == null) {
                throw new ParseException(sheetInfo.getSheetNameCell(),
                        "[" + sheetInfo.getSheetName() + "]????");
            }
            results.addAll(parseTargetSheet(targetSheet, sheetInfo, targetColumnInfoList));
        }

        // sheetData???
        sheetData.put(tag, results);
    }

    // ???????
    for (String removeTag : removeTags) {
        sheetData.remove(removeTag);
    }
}

From source file:org.bbreak.excella.trans.tag.sheet2java.SheetToJavaParser.java

License:Open Source License

/**
 * ?//from   w ww  .  jav a  2  s  .c  o m
 * 
 * @param sheet 
 * @param tagCell ???
 * @param data TransProcessor?processBook, processSheet?<BR> 
 *              ????TagParser.parse??????<BR>
 * @return ?
 * @throws ParseException 
 */
@Override
public List<SheetToJavaParseInfo> parse(Sheet sheet, Cell tagCell, Object data) throws ParseException {
    // ?
    int tagRowIdx = tagCell.getRowIndex();
    int tagColIdx = tagCell.getColumnIndex();

    int valueRowFromIdx;
    int valueRowToIdx = sheet.getLastRowNum();

    // ????
    String settingTagName = getTag() + DEFAULT_SETTING_SUFFIX;

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

        // ?
        valueRowFromIdx = TagUtil.adjustValue(tagRowIdx, paramDef, PARAM_DATA_ROW_FROM,
                DEFAULT_DATA_ROW_FROM_ADJUST);
        if (valueRowFromIdx < 0 || valueRowFromIdx > sheet.getLastRowNum()) {
            throw new ParseException(tagCell, "?" + PARAM_DATA_ROW_FROM);
        }

        // ?
        valueRowToIdx = TagUtil.adjustValue(tagRowIdx, paramDef, PARAM_DATA_ROW_TO, valueRowToIdx - tagRowIdx);
        if (valueRowToIdx > sheet.getLastRowNum() || valueRowToIdx < 0) {
            throw new ParseException(tagCell, "?" + PARAM_DATA_ROW_TO);
        }

        // ???
        if (valueRowFromIdx > valueRowToIdx) {
            throw new ParseException(tagCell,
                    "?" + PARAM_DATA_ROW_FROM + "," + PARAM_DATA_ROW_TO);
        }

        // ????
        if (paramDef.containsKey(PARAM_SETTING_TAG_NAME)) {
            settingTagName = paramDef.get(PARAM_SETTING_TAG_NAME);
        }

        // ?
        if (paramDef.containsKey(PARAM_RESULT_KEY)) {
            // ???????
            throw new ParseException(tagCell, PARAM_RESULT_KEY + "????????");
        }

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

    List<SheetToJavaParseInfo> sheetInfoList = new ArrayList<SheetToJavaParseInfo>();

    // ??
    int sheetNameColIdx = tagColIdx++;
    // ???No
    int logicalRowColIdx = tagColIdx++;
    // No
    int dataRowColIdx = tagColIdx;

    // ?????
    Workbook workbook = sheet.getWorkbook();

    // ???
    for (int rowNum = valueRowFromIdx; rowNum <= valueRowToIdx; rowNum++) {
        Row row = sheet.getRow(rowNum);
        if (row != null) {
            // ??
            Cell sheetNameCell = row.getCell(sheetNameColIdx);
            Cell logicalRowNumCell = row.getCell(logicalRowColIdx);
            Cell valueRowNumCell = row.getCell(dataRowColIdx);

            // ?
            if ((sheetNameCell == null) && (logicalRowNumCell == null) && (valueRowNumCell == null)) {
                // ????null??
                continue;

            } else if ((sheetNameCell == null) || (sheetNameCell.getStringCellValue() == null)
                    || ("".equals(sheetNameCell.getStringCellValue()))) {
                // ?????????
                continue;

            } else {
                // ????
                Cell requiredErrorCell = null;
                if (logicalRowNumCell == null) {
                    requiredErrorCell = row.createCell(logicalRowColIdx);
                } else if (valueRowNumCell == null) {
                    requiredErrorCell = row.createCell(dataRowColIdx);
                }

                // ??????
                if (requiredErrorCell != null) {
                    throw new ParseException(requiredErrorCell, "?null??");
                }
            }

            // ???No?
            int logicalRowNum;
            try {
                logicalRowNum = (Integer) PoiUtil.getCellValue(logicalRowNumCell, Integer.class);
            } catch (Exception e) {
                throw new ParseException(logicalRowNumCell, e);
            }

            // No?
            int valueRowNum;
            try {
                valueRowNum = (Integer) PoiUtil.getCellValue(valueRowNumCell, Integer.class);
            } catch (Exception e) {
                throw new ParseException(valueRowNumCell, e);
            }

            // ???
            String sheetName = sheetNameCell.getStringCellValue();
            if (workbook.getSheet(sheetName) == null) {
                throw new ParseException(sheetNameCell, "[" + sheetName + "]????");
            }

            // 
            SheetToJavaParseInfo sheetInfo = new SheetToJavaParseInfo();
            sheetInfo.setSettingTagName(settingTagName);
            sheetInfo.setSettingTagNameCell(tagCell);
            sheetInfo.setSheetName(sheetName);
            sheetInfo.setSheetNameCell(sheetNameCell);
            sheetInfo.setLogicalNameRowNum(logicalRowNum);
            sheetInfo.setLogicalNameRowNumCell(logicalRowNumCell);
            sheetInfo.setValueRowNum(valueRowNum);
            sheetInfo.setValueRowNumCell(valueRowNumCell);

            sheetInfoList.add(sheetInfo);
        }
    }

    return sheetInfoList;
}

From source file:org.bbreak.excella.trans.tag.sheet2java.SheetToJavaSettingParser.java

License:Open Source License

/**
 * ?//from  ww w  .j  a  va  2  s .c  om
 * 
 * @param sheet 
 * @param tagCell ???
 * @param data TransProcessor?processBook, processSheet?<BR> 
 *              ????TagParser.parse??????<BR>
 * @return ?
 * @throws ParseException 
 */
@Override
public List<SheetToJavaSettingInfo> parse(Sheet sheet, Cell tagCell, Object data) throws ParseException {

    // ?
    int tagRowIdx = tagCell.getRowIndex();
    int tagColIdx = tagCell.getColumnIndex();

    // 
    int valueRowFromIdx;
    int valueRowToIdx = sheet.getLastRowNum();

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

        // ?
        valueRowFromIdx = TagUtil.adjustValue(tagRowIdx, paramDef, PARAM_DATA_ROW_FROM,
                DEFAULT_DATA_ROW_FROM_ADJUST);
        if (valueRowFromIdx < 0 || valueRowFromIdx > sheet.getLastRowNum()) {
            throw new ParseException(tagCell, "?" + PARAM_DATA_ROW_FROM);
        }

        // ?
        valueRowToIdx = TagUtil.adjustValue(tagRowIdx, paramDef, PARAM_DATA_ROW_TO, valueRowToIdx - tagRowIdx);
        if (valueRowToIdx > sheet.getLastRowNum() || valueRowToIdx < 0) {
            throw new ParseException(tagCell, "?" + PARAM_DATA_ROW_TO);
        }

        // ???
        if (valueRowFromIdx > valueRowToIdx) {
            throw new ParseException(tagCell,
                    "?" + PARAM_DATA_ROW_FROM + "," + PARAM_DATA_ROW_TO);
        }

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

    List<SheetToJavaSettingInfo> sheetSettingInfoList = new ArrayList<SheetToJavaSettingInfo>();

    // ??
    int sheetNameColIdx = tagColIdx++;
    // 
    int valueColIdx = tagColIdx++;
    // 
    int classColIdx = tagColIdx++;
    // 
    int propertyNameColIdx = tagColIdx++;
    // ?
    int uniqueColIdx = tagColIdx++;

    // ?????
    Workbook workbook = sheet.getWorkbook();

    // ???
    for (int rowNum = valueRowFromIdx; rowNum <= valueRowToIdx; rowNum++) {
        Row row = sheet.getRow(rowNum);
        if (row != null) {
            // ??
            Cell sheetNameCell = row.getCell(sheetNameColIdx);
            Cell valueCell = row.getCell(valueColIdx);
            Cell classCell = row.getCell(classColIdx);
            Cell propertyNameCell = row.getCell(propertyNameColIdx);
            Cell uniqueCell = row.getCell(uniqueColIdx);

            // ?
            if ((sheetNameCell == null) && (valueCell == null) && (classCell == null)
                    && (propertyNameCell == null) && (uniqueCell == null)) {
                // ????null??
                continue;

            } else if ((sheetNameCell == null) || (sheetNameCell.getStringCellValue() == null)
                    || ("".equals(sheetNameCell.getStringCellValue()))) {
                // ?????????
                continue;

            } else {
                // ????
                Cell requiredErrorCell = null;
                if (classCell == null) {
                    // ?null??
                    requiredErrorCell = row.createCell(classColIdx);
                }

                // ??
                if (requiredErrorCell != null) {
                    throw new ParseException(requiredErrorCell, "?null??");
                }
            }

            // ??
            SheetToJavaSettingInfo settingInfo = new SheetToJavaSettingInfo();

            // ????
            String sheetName = sheetNameCell.getStringCellValue();
            if (workbook.getSheet(sheetName) == null) {
                throw new ParseException(sheetNameCell, "[" + sheetName + "]????");
            }

            // ??
            settingInfo.setSheetName(sheetName);
            settingInfo.setSheetNameCell(sheetNameCell);

            // ?
            try {
                settingInfo.setClazz(Class.forName(classCell.getStringCellValue()));
                settingInfo.setClazzCell(classCell);
            } catch (ClassNotFoundException e) {
                throw new ParseException(classCell, e);
            }

            // 
            Object value = PoiUtil.getCellValue(valueCell);
            settingInfo.setValueCell(valueCell);

            // ?????
            boolean isValueTag = false;
            // ????????
            boolean isValueLogicalNameTag = false;
            if (value instanceof String) {
                // ??
                String valueStr = (String) value;
                if ((valueStr).startsWith(SheetToJavaExecuter.TAG_PREFIX)) {
                    // ??
                    isValueTag = true;
                    if ((valueStr).startsWith(SheetToJavaExecuter.TAG_LOGICAL_NAME_PREFIX)) {
                        // ?????
                        isValueLogicalNameTag = true;
                    }
                }
            }

            if (!isValueTag || isValueLogicalNameTag) {
                // ?????????

                // ??
                Cell requiredErrorCell = null;
                if (propertyNameCell == null) {
                    requiredErrorCell = row.createCell(propertyNameColIdx);
                }
                if (requiredErrorCell != null) {
                    throw new ParseException(requiredErrorCell, "?null??");
                }

                // ?
                settingInfo.setPropertyName(propertyNameCell.getStringCellValue());
                settingInfo.setPropertyNameCell(propertyNameCell);

                // ?
                Class<?> propertyClass = null;
                try {
                    Object obj = settingInfo.getClazz().newInstance();
                    propertyClass = PropertyUtils.getPropertyType(obj, settingInfo.getPropertyName());
                } catch (Exception e) {
                    throw new ParseException(propertyNameCell, e);
                }
                if (propertyClass == null) {
                    throw new ParseException(propertyNameCell,
                            "?:" + settingInfo.getPropertyName());
                }

                // ????
                if (uniqueCell != null) {
                    if (uniqueCell.getStringCellValue() != null
                            && uniqueCell.getStringCellValue().equals(UNIQUE_PROPERTY_MARK)) {
                        settingInfo.setUnique(true);
                        settingInfo.setUniqueCell(uniqueCell);
                    }
                }
            } else {
                // ??

                // ?????
                try {
                    TagUtil.getParams((String) value);
                } catch (Exception e) {
                    throw new ParseException(valueCell, e);
                }
            }

            // ??????
            boolean checkTypeFlag = false;
            if (value instanceof String) {
                if (!isValueTag) {
                    // ??
                    checkTypeFlag = true;
                }
            } else {
                // ??
                if (value != null) {
                    // null??
                    checkTypeFlag = true;
                }
            }

            // ?
            if (checkTypeFlag) {
                // ????
                Object obj;
                try {
                    obj = settingInfo.getClazz().newInstance();
                    Class<?> propertyClass = PropertyUtils.getPropertyType(obj, settingInfo.getPropertyName());
                    value = PoiUtil.getCellValue(valueCell, propertyClass);
                } catch (Exception e) {
                    throw new ParseException(valueCell, e);
                }
            }
            settingInfo.setValue(value);

            // ????
            sheetSettingInfoList.add(settingInfo);
        }
    }
    return sheetSettingInfoList;
}