Example usage for org.apache.poi.ss.usermodel Row getRowNum

Introduction

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

Prototype

int getRowNum();

Source Link

Document

Get row number this row represents

Usage

From source file:org.bbreak.excella.reports.listener.BreakAdapter.java

License:Open Source License

private boolean isInMergedRegion(Sheet sheet, Row row, Cell cell) {
    // ???????//from   w  w  w  .ja  va2s.c  o m
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
        CellRangeAddress region = sheet.getMergedRegion(i);
        if (region.isInRange(row.getRowNum(), cell.getColumnIndex())) {
            return true;
        }
    }
    return false;
}

---

From source file:org.bbreak.excella.reports.listener.BreakAdapter.java

License:Open Source License

protected void setRowBreakMergedRegion(Sheet sheet, Row row, Cell cell) {
    PoiUtil.setCellValue(cell, null);/*from  w  ww  . j  av a2  s . c  om*/
    // ???????
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
        CellRangeAddress region = sheet.getMergedRegion(i);
        if (region.isInRange(row.getRowNum(), cell.getColumnIndex())) {
            // ??????????
            sheet.setRowBreak(row.getRowNum() + 1);
            return;
        }
    }
    // ????????????
    sheet.setRowBreak(row.getRowNum());
}

---

From source file:org.bbreak.excella.reports.listener.BreakAdapter.java

License:Open Source License

protected void setRowBreak(Sheet sheet, Row row, Cell cell) {
    sheet.setRowBreak(row.getRowNum());
    row.removeCell(cell);
}

---

From source file:org.bbreak.excella.reports.ReportsTestUtil.java

License:Open Source License

/**
 * //from w w  w. java 2 s  .  c om
 * 
 * @param expected 
 * @param actual 
 * @param isActCopyOfExp ??????true
 * @throws ReportsCheckException 
 */
public static void checkSheet(Sheet expected, Sheet actual, boolean isActCopyOfExp)
        throws ReportsCheckException {

    List<CheckMessage> errors = new ArrayList<CheckMessage>();

    Workbook expectedWorkbook = expected.getWorkbook();
    Workbook actualWorkbook = actual.getWorkbook();

    if (log.isDebugEnabled()) {
        log.debug("[" + actualWorkbook.getSheetName(actualWorkbook.getSheetIndex(actual))
                + "] check start!");
    }

    // ----------------------
    // ????
    // ----------------------
    // ??
    String eSheetName = expectedWorkbook.getSheetName(expectedWorkbook.getSheetIndex(expected));
    String aSheetName = actualWorkbook.getSheetName(actualWorkbook.getSheetIndex(actual));

    if (!isActCopyOfExp) {
        if (!eSheetName.equals(aSheetName)) {
            errors.add(new CheckMessage("??", eSheetName, aSheetName));
        }
    }

    // ?
    String ePrintSetupString = getPrintSetupString(expected.getPrintSetup());
    String aPrintSetupString = getPrintSetupString(actual.getPrintSetup());

    if (!ePrintSetupString.equals(aPrintSetupString)) {
        errors.add(new CheckMessage("?", ePrintSetupString, aPrintSetupString));
    }

    // ?
    String eHeaderString = getHeaderString(expected.getHeader());
    String aHeaderString = getHeaderString(actual.getHeader());
    if (!eHeaderString.equals(aHeaderString)) {
        errors.add(new CheckMessage("", eHeaderString, aHeaderString));
    }
    String eFooterString = getFooterString(expected.getFooter());
    String aFooterString = getFooterString(actual.getFooter());
    if (!eFooterString.equals(aFooterString)) {
        errors.add(new CheckMessage("", eFooterString, aFooterString));
    }

    // 
    String eBreaksString = getBreaksString(expected);
    String aBreaksString = getBreaksString(actual);
    log.debug(eBreaksString + "/" + aBreaksString);
    if (!eBreaksString.equals(aBreaksString)) {
        errors.add(new CheckMessage("", eBreaksString, aBreaksString));
    }

    // ?
    String expectedPrintArea = expectedWorkbook.getPrintArea(expectedWorkbook.getSheetIndex(expected));
    String actualPrintArea = actualWorkbook.getPrintArea(actualWorkbook.getSheetIndex(actual));
    if (expectedPrintArea != null || actualPrintArea != null) {
        // ????????Null?????????????
        // if ( expectedPrintArea == null || actualPrintArea == null || !equalPrintArea( expectedPrintArea, actualPrintArea, isActCopyOfExp)) {
        // errors.add( new CheckMessage( "?", expectedPrintArea, actualPrintArea));
        // }
        if (!isActCopyOfExp) {
            if (expectedPrintArea == null || actualPrintArea == null
                    || !expectedPrintArea.equals(actualPrintArea)) {
                errors.add(new CheckMessage("?", expectedPrintArea, actualPrintArea));
            }
        }
    }

    // (?)
    String ePaneInformationString = getPaneInformationString(expected.getPaneInformation());
    String aPaneInformationString = getPaneInformationString(actual.getPaneInformation());

    if (!ePaneInformationString.equals(aPaneInformationString)) {
        errors.add(new CheckMessage("(?)", expectedPrintArea, actualPrintArea));
    }

    // ??????

    // ?????

    // ?????

    // 

    // 
    if (expected.isDisplayGridlines() ^ actual.isDisplayGridlines()) {
        errors.add(new CheckMessage("",
                String.valueOf(expected.isDisplayGridlines()), String.valueOf(actual.isDisplayGridlines())));
    }

    // ?
    if (expected.isDisplayRowColHeadings() ^ actual.isDisplayRowColHeadings()) {
        errors.add(new CheckMessage("?", String.valueOf(expected.isDisplayRowColHeadings()),
                String.valueOf(actual.isDisplayRowColHeadings())));
    }

    // ?
    if (expected.isDisplayFormulas() ^ actual.isDisplayFormulas()) {
        errors.add(new CheckMessage("?", String.valueOf(expected.isDisplayFormulas()),
                String.valueOf(actual.isDisplayFormulas())));
    }
    // ??
    if (expected.getNumMergedRegions() != actual.getNumMergedRegions()) {
        errors.add(new CheckMessage("??", String.valueOf(expected.getNumMergedRegions()),
                String.valueOf(actual.getNumMergedRegions())));
    }

    for (int i = 0; i < actual.getNumMergedRegions(); i++) {

        CellRangeAddress actualAddress = null;
        if (expected instanceof HSSFSheet) {
            actualAddress = ((HSSFSheet) actual).getMergedRegion(i);
        } else if (expected instanceof XSSFSheet) {
            actualAddress = ((XSSFSheet) actual).getMergedRegion(i);
        }

        StringBuffer expectedAdressBuffer = new StringBuffer();
        boolean equalAddress = false;
        for (int j = 0; j < expected.getNumMergedRegions(); j++) {
            CellRangeAddress expectedAddress = null;
            if (expected instanceof HSSFSheet) {
                expectedAddress = ((HSSFSheet) expected).getMergedRegion(j);
            } else if (expected instanceof XSSFSheet) {
                expectedAddress = ((XSSFSheet) expected).getMergedRegion(j);
            }
            if (expectedAddress.toString().equals(actualAddress.toString())) {
                equalAddress = true;
                break;
            }
            CellReference crA = new CellReference(expectedAddress.getFirstRow(),
                    expectedAddress.getFirstColumn());
            CellReference crB = new CellReference(expectedAddress.getLastRow(),
                    expectedAddress.getLastColumn());
            expectedAdressBuffer.append(" [" + crA.formatAsString() + ":" + crB.formatAsString() + "]");
        }

        if (!equalAddress) {
            errors.add(new CheckMessage("??", expectedAdressBuffer.toString(),
                    actualAddress.toString()));
        }

    }

    int maxColumnNum = -1;
    if (expected instanceof HSSFSheet) {
        maxColumnNum = HSSF_MAX_COLUMN_NUMBER;
    } else if (expected instanceof XSSFSheet) {
        maxColumnNum = XSSF_MAX_COLUMN_NUMBER;
    }
    for (int i = 0; i < maxColumnNum; i++) {
        try {
            // 
            checkCellStyle(expected.getWorkbook(), expected.getColumnStyle(i), actual.getWorkbook(),
                    actual.getColumnStyle(i));
        } catch (ReportsCheckException e) {
            CheckMessage checkMessage = e.getCheckMessages().iterator().next();
            checkMessage.setMessage("[" + i + "]" + checkMessage.getMessage());
            errors.add(checkMessage);
        }

        // 
        if (expected.getColumnWidth(i) != actual.getColumnWidth(i)) {
            errors.add(new CheckMessage("[" + i + "]", String.valueOf(expected.getColumnWidth(i)),
                    String.valueOf(actual.getColumnWidth(i))));
        }
    }

    // ???
    if (expected.getLastRowNum() != actual.getLastRowNum()) {
        // ??????
        if (expected.getLastRowNum() < actual.getLastRowNum()) {
            int lastRowIndex = -1;
            if (expected instanceof HSSFSheet) {
                lastRowIndex = 0;
            }
            Iterator<Row> rowIterator = actual.rowIterator();
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                // ?????
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    if (cell.getCellTypeEnum() != CellType.BLANK) {
                        lastRowIndex = row.getRowNum();
                        break;
                    }
                }
            }
            if (expected.getLastRowNum() != lastRowIndex) {
                errors.add(new CheckMessage("", String.valueOf(expected.getLastRowNum()),
                        String.valueOf(lastRowIndex)));
            }
        } else {
            errors.add(new CheckMessage("", String.valueOf(expected.getLastRowNum()),
                    String.valueOf(actual.getLastRowNum())));
        }

    }

    if (errors.isEmpty()) {
        for (int i = 0; i <= expected.getLastRowNum(); i++) {
            try {
                checkRow(expected.getRow(i), actual.getRow(i));
            } catch (ReportsCheckException e) {
                errors.addAll(e.getCheckMessages());
            }
        }
    }

    if (!errors.isEmpty()) {
        if (log.isErrorEnabled()) {
            for (CheckMessage message : errors) {
                log.error("?[" + message.getMessage() + "]");
                log.error(":" + message.getExpected());
                log.error(":" + message.getActual());
            }
        }
        throw new ReportsCheckException(errors);
    }

    if (log.isDebugEnabled()) {
        log.debug("[" + actualWorkbook.getSheetName(actualWorkbook.getSheetIndex(actual))
                + "] check end.");
    }

}

---

From source file:org.bbreak.excella.reports.ReportsTestUtil.java

License:Open Source License

/**
 * /*from  w  ww  .j a  va2 s  .  c om*/
 * 
 * @param expected 
 * @param actual 
 * @throws ReportsCheckException 
 */
public static void checkRow(Row expected, Row actual) throws ReportsCheckException {

    List<CheckMessage> errors = new ArrayList<CheckMessage>();

    // ----------------------
    // ????
    // ----------------------

    if (expected == null && actual == null) {
        return;
    }

    if (expected == null) {
        if (actual.iterator().hasNext()) {
            errors.add(new CheckMessage("[" + actual.getRowNum() + "]", null, actual.toString()));
            throw new ReportsCheckException(errors);
        } else {
            return;
        }
    }
    if (actual == null) {
        if (expected.iterator().hasNext()) {
            errors.add(new CheckMessage("[" + expected.getRowNum() + "]", expected.toString(), null));
            throw new ReportsCheckException(errors);
        } else {
            return;
        }
    }

    // ??(shiftRow??????????????????)
    // float adjustHight = 0f;
    // if ( hasHeightAdjustBorderCell( actual.getSheet().getRow( actual.getRowNum() - 1), actual, actual.getSheet().getRow( actual.getRowNum() + 1))) {
    // log.error( "true");
    // adjustHight = 0.75f;
    // }
    //
    // if ( expected.getHeightInPoints() != actual.getHeightInPoints() + adjustHight) {
    // if ( log.isErrorEnabled()) {
    // log.error( "expectedROW[" + expected.getRowNum() + "]:" + expected.getHeightInPoints());
    // log.error( "actualROW[" + actual.getRowNum() + "]:" + (actual.getHeightInPoints() + adjustHight));
    // }
    // throw new Exception( "??");
    // }

    // 
    if (expected.getLastCellNum() != actual.getLastCellNum()) {
        errors.add(new CheckMessage("[" + expected.getRowNum() + "]",
                String.valueOf(expected.getLastCellNum()), String.valueOf(actual.getLastCellNum())));
        throw new ReportsCheckException(errors);
    }

    // ???
    for (int i = 0; i < expected.getLastCellNum(); i++) {
        try {
            checkCell(expected.getCell(i), actual.getCell(i));
        } catch (ReportsCheckException e) {
            errors.addAll(e.getCheckMessages());
        }
    }
    if (!errors.isEmpty()) {
        throw new ReportsCheckException(errors);
    }
}

---

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 .  j  a v  a 2 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.cerberus.service.export.ExportServiceFactory.java

License:Open Source License

private void createReportByTagExport(Workbook workbook) {
    //handles the export of the execution by tag data
    HashMap<String, SummaryStatisticsDTO> summaryMap = new HashMap<String, SummaryStatisticsDTO>();

    HashMap<String, HashMap<String, List<TestCaseExecution>>> mapList = new HashMap<String, HashMap<String, List<TestCaseExecution>>>();
    List<String> mapCountries = new ArrayList<String>();
    List<CellStyle> stylesList = new LinkedList<CellStyle>();

    if (exportOptions.contains("chart") || exportOptions.contains("list")) {
        //then we need to create the default colors for each cell
        HSSFWorkbook hwb = new HSSFWorkbook();
        HSSFPalette palette = hwb.getCustomPalette();

        CellStyle okStyle = workbook.createCellStyle();

        // get the color which most closely matches the color you want to use
        // code to get the style for the cell goes here
        okStyle.setFillForegroundColor(palette.findSimilarColor(92, 184, 0).getIndex());
        okStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        //okStyle.setFont();

        stylesList.add(okStyle);/*from   w w  w .  jav a2s .c o m*/

    }
    for (TestCaseExecution execution : (List<TestCaseExecution>) list) {
        //check if the country and application shows

        if (exportOptions.contains("chart") || exportOptions.contains("summary")) {
            String keySummaryTable = execution.getApplication() + " " + execution.getCountry() + " "
                    + execution.getEnvironment();
            SummaryStatisticsDTO stats;

            String status = execution.getControlStatus();

            if (summaryMap.containsKey(keySummaryTable)) {
                stats = summaryMap.get(keySummaryTable);
            } else {
                stats = new SummaryStatisticsDTO();
                stats.setApplication(execution.getApplication());
                stats.setCountry(execution.getCountry());
                stats.setEnvironment(execution.getEnvironment());
            }
            stats.updateStatisticByStatus(status);
            summaryMap.put(keySummaryTable, stats); //updates the map
        }
        if (exportOptions.contains("list")) {
            if (exportOptions.contains("filter")) {
                //filter active
            } else {
                //all data is saved

            }
            HashMap<String, List<TestCaseExecution>> listExecution;
            List<TestCaseExecution> testCaseList;
            String testKey = execution.getTest();
            String testCaseKey = execution.getTestCase();

            if (mapList.containsKey(testKey)) {
                listExecution = mapList.get(testKey);
            } else {
                listExecution = new HashMap<String, List<TestCaseExecution>>();
            }
            if (listExecution.containsKey(testCaseKey)) {
                testCaseList = listExecution.get(testCaseKey);
            } else {
                testCaseList = new ArrayList<TestCaseExecution>();
            }
            testCaseList.add(execution);
            listExecution.put(testCaseKey, testCaseList);
            mapList.put(testKey, listExecution);

            if (mapCountries.indexOf(execution.getCountry()) == -1) {
                mapCountries.add(execution.getCountry());
            }

        }

    }
    int rowCount = -1;

    //Create a blank sheet
    Sheet sheet = workbook.createSheet("Report by Tag");
    sheet.getPrintSetup().setLandscape(true);

    PrintSetup ps = sheet.getPrintSetup();

    sheet.setAutobreaks(true);

    //ps.setFitHeight((short) 1);
    ps.setFitWidth((short) 1);
    sheet.setFitToPage(true);
    sheet.setColumnWidth(0, 9000);

    if (exportOptions.contains("chart")) {
        SummaryStatisticsDTO sumsTotal = calculateTotalValues(summaryMap);

        Row row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("Report By Status");

        row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("Status");
        row.createCell(1).setCellValue("Total");
        row.createCell(2).setCellValue("Percentage");

        row = sheet.createRow(++rowCount);
        CellStyle okStyle = stylesList.get(0);
        Cell cellOk = row.createCell(0);
        cellOk.setCellValue("OK");
        cellOk.setCellStyle(okStyle);

        row.createCell(1).setCellValue(sumsTotal.getOk());
        row.createCell(2).setCellValue(sumsTotal.getPercOk());

        row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("KO");
        row.createCell(1).setCellValue(sumsTotal.getKo());
        row.createCell(2).setCellValue(sumsTotal.getPercKo());

        row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("FA");
        row.createCell(1).setCellValue(sumsTotal.getFa());
        row.createCell(2).setCellValue(sumsTotal.getPercFa());

        row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("NA");
        row.createCell(1).setCellValue(sumsTotal.getNa());
        row.createCell(2).setCellValue(sumsTotal.getPercNa());

        row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("NE");
        row.createCell(1).setCellValue(sumsTotal.getNe());
        row.createCell(2).setCellValue(sumsTotal.getPercNe());

        row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("PE");
        row.createCell(1).setCellValue(sumsTotal.getPe());
        row.createCell(2).setCellValue(sumsTotal.getPercPe());

        row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("CA");
        row.createCell(1).setCellValue(sumsTotal.getCa());
        row.createCell(2).setCellValue(sumsTotal.getPercCa());

        row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("Total");
        row.createCell(1).setCellValue(sumsTotal.getTotal());

        sheet.createRow(++rowCount).createCell(0).setCellValue("");
        sheet.createRow(++rowCount).createCell(0).setCellValue("");
        sheet.createRow(++rowCount).createCell(0).setCellValue("");
        sheet.createRow(++rowCount).createCell(0).setCellValue("");

    }
    if (exportOptions.contains("summary")) {
        //draw the table with data

        Row row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("Summary Table");

        //start creating data
        row = sheet.createRow(++rowCount);

        row.createCell(0).setCellValue("Application");
        row.createCell(1).setCellValue("Country");
        row.createCell(2).setCellValue("Environment");
        row.createCell(3).setCellValue("OK");
        row.createCell(4).setCellValue("KO");
        row.createCell(5).setCellValue("FA");
        row.createCell(6).setCellValue("NA");
        row.createCell(7).setCellValue("NE");
        row.createCell(8).setCellValue("PE");
        row.createCell(9).setCellValue("CA");
        row.createCell(10).setCellValue("NOT OK");
        row.createCell(11).setCellValue("Total");

        /*temporary styles*/
        CellStyle styleBlue = workbook.createCellStyle();
        CellStyle styleGreen = workbook.createCellStyle();
        HSSFWorkbook hwb = new HSSFWorkbook();
        HSSFPalette palette = hwb.getCustomPalette();
        // get the color which most closely matches the color you want to use
        HSSFColor myColor = palette.findSimilarColor(66, 139, 202);

        // get the palette index of that color 
        short palIndex = myColor.getIndex();
        // code to get the style for the cell goes here
        styleBlue.setFillForegroundColor(palIndex);
        styleBlue.setFillPattern(CellStyle.SPARSE_DOTS);

        HSSFColor myColorGreen = palette.findSimilarColor(92, 184, 0);
        styleGreen.setFillForegroundColor(myColorGreen.getIndex());
        styleGreen.setFillPattern(CellStyle.SPARSE_DOTS);

        int startRow = (rowCount + 2);
        TreeMap<String, SummaryStatisticsDTO> sortedSummaryMap = new TreeMap<String, SummaryStatisticsDTO>(
                summaryMap);
        for (String key : sortedSummaryMap.keySet()) {
            row = sheet.createRow(++rowCount);
            SummaryStatisticsDTO sumStats = summaryMap.get(key);
            //application
            row.createCell(0).setCellValue((String) sumStats.getApplication());
            //country
            row.createCell(1).setCellValue((String) sumStats.getCountry());
            //environment
            row.createCell(2).setCellValue((String) sumStats.getEnvironment());

            //OK
            row.createCell(3).setCellValue(sumStats.getOk());
            //KO
            row.createCell(4).setCellValue(sumStats.getKo());
            //FA
            row.createCell(5).setCellValue(sumStats.getFa());
            //NA
            row.createCell(6).setCellValue(sumStats.getNa());
            //NE
            row.createCell(7).setCellValue(sumStats.getNe());
            //PE
            row.createCell(8).setCellValue(sumStats.getPe());
            //CA
            row.createCell(9).setCellValue(sumStats.getCa());
            int rowNumber = row.getRowNum() + 1;
            //NOT OK
            //row.createCell(11).setCellValue(sumStats.getNotOkTotal());
            row.createCell(10).setCellFormula("SUM(E" + rowNumber + ":J" + rowNumber + ")");
            //Total
            row.createCell(11).setCellFormula("SUM(D" + rowNumber + ",K" + rowNumber + ")");
            //row.createCell(12).setCellValue(sumStats.getTotal());

            if (sumStats.getOk() == sumStats.getTotal()) {
                for (int i = 0; i < 12; i++) {
                    row.getCell(i).setCellStyle(styleGreen);
                }
            }
        }
        //TODO:FN percentages missing
        //Total row
        row = sheet.createRow(++rowCount);

        row.createCell(0).setCellValue("Total");
        row.createCell(1).setCellValue("");
        row.createCell(2).setCellValue("");
        //OK
        row.createCell(3).setCellFormula("SUM(D" + startRow + ":D" + rowCount + ")");
        //KO
        row.createCell(4).setCellFormula("SUM(E" + startRow + ":E" + rowCount + ")");
        //FA
        row.createCell(5).setCellFormula("SUM(F" + startRow + ":F" + rowCount + ")");
        //NA
        row.createCell(6).setCellFormula("SUM(G" + startRow + ":G" + rowCount + ")");
        //NE
        row.createCell(7).setCellFormula("SUM(H" + startRow + ":H" + rowCount + ")");
        //PE
        row.createCell(8).setCellFormula("SUM(I" + startRow + ":I" + rowCount + ")");
        //CA
        row.createCell(9).setCellFormula("SUM(J" + startRow + ":J" + rowCount + ")");

        int rowNumberTotal = row.getRowNum() + 1;
        //NOT OK
        row.createCell(10).setCellFormula("SUM(E" + rowNumberTotal + ":J" + rowNumberTotal + ")");
        //Total
        row.createCell(11).setCellFormula("SUM(D" + rowNumberTotal + ",K" + rowNumberTotal + ")");
        for (int i = 0; i < 12; i++) {
            row.getCell(i).setCellStyle(styleBlue);
        }

        //add some empty rows
        sheet.createRow(++rowCount).createCell(0).setCellValue("");
        sheet.createRow(++rowCount).createCell(0).setCellValue("");
        sheet.createRow(++rowCount).createCell(0).setCellValue("");
        sheet.createRow(++rowCount).createCell(0).setCellValue("");

    }

    if (exportOptions.contains("list")) {
        //exports the data from test cases' executions
        Row r = sheet.createRow(++rowCount);
        r.createCell(0).setCellValue("Test");
        r.createCell(1).setCellValue("Test Case");
        r.createCell(2).setCellValue("Description");
        r.createCell(3).setCellValue("Application");
        r.createCell(4).setCellValue("Environment");
        r.createCell(5).setCellValue("Browser");
        //creates the country list

        Collections.sort(mapCountries);//sorts the list of countries
        int startIndexForCountries = 6;
        for (String country : mapCountries) {
            r.createCell(startIndexForCountries).setCellValue(country);
            startIndexForCountries++;
        }

        TreeMap<String, HashMap<String, List<TestCaseExecution>>> sortedKeys = new TreeMap<String, HashMap<String, List<TestCaseExecution>>>(
                mapList);
        rowCount++;
        for (String keyMapList : sortedKeys.keySet()) {
            rowCount = createRow(keyMapList, mapList.get(keyMapList), sheet, rowCount, mapCountries);
        }
    }
}

---

From source file:org.cgiar.ccafs.marlo.action.center.capdev.test.java

License:Open Source License

public void readFile() throws FileNotFoundException {
    final File file = new File("C:\\Users\\logonzalez\\Downloads\\participants.xlsx");
    FileInputStream fileInput;/*from  w w w.ja  v a2  s . c om*/
    try {
        fileInput = new FileInputStream(file);
        final XSSFWorkbook wb = new XSSFWorkbook(fileInput);
        final Sheet sheet = wb.getSheetAt(0);
        final List<Row> notEmptyRows = this.searchForEmptyRows(sheet);
        // System.out.println(sheet.getLastRowNum());

        final Row firstRow = sheet.getRow(9);
        final int totalRows = sheet.getLastRowNum() - firstRow.getRowNum();
        // System.out.println("firstRow " + firstRow.getRowNum());
        // System.out.println("totalRows " + totalRows);
        final int totalColumns = firstRow.getLastCellNum();
        System.out.println("notEmptyRows.size " + notEmptyRows.size());
        for (int fila = 0; fila < notEmptyRows.size(); fila++) {
            final Row row = notEmptyRows.get(fila);
            for (int col = 0; col < row.getLastCellNum(); col++) {
                final Cell cell = row.getCell(col);
                System.out.println(this.getCellData(cell));
            }
            System.out.println("-----------");

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

}

---

From source file:org.cgiar.ccafs.marlo.action.center.capdev.test.java

License:Open Source License

public List<Row> searchForEmptyRows(Sheet sheet) {
    // Decide which rows to process
    final List<Row> notEmptyRows = new ArrayList<>();
    final Row firstRow = sheet.getRow(10);
    final int rowStart = firstRow.getRowNum();
    final int rowEnd = sheet.getLastRowNum();

    for (int rowNum = rowStart; rowNum <= rowEnd; rowNum++) {
        final Row r = sheet.getRow(rowNum);
        if (r != null) {
            // System.out.println("empty row " + r.getRowNum());
            // sheet.removeRow(r);
            // This whole row is empty
            // Handle it as needed
            notEmptyRows.add(r);/*  ww w .  j a v  a  2s  .com*/
            continue;
        }

    }
    return notEmptyRows;
}

---

From source file:org.cgiar.ccafs.marlo.utils.ReadExcelFile.java

License:Open Source License

public List<Map<String, Object>> getDataExcelFile(Workbook wb) {
    final List<Map<String, Object>> fullData = new ArrayList<>();
    try {//from   w ww . ja  v a 2s. c o m
        Sheet sheet = wb.getSheetAt(0);
        this.searchForEmptyRows(sheet);
        Row firstRow = sheet.getRow(9); // fila donde esta el encabezado del template
        totalRows = (sheet.getLastRowNum() - firstRow.getRowNum()) + 1;
        totalColumns = firstRow.getLastCellNum();
        List<Row> rows = this.searchForEmptyRows(sheet);
        for (int fila = 0; fila < rows.size(); fila++) {
            Row row = rows.get(fila);
            Map<String, Object> data = new HashMap<>();
            for (int col = 0; col < firstRow.getLastCellNum(); col++) {
                Cell cell = row.getCell(col);
                Cell headerCell = firstRow.getCell(col);
                data.put(headerCell.getStringCellValue(), this.getCellData(cell));
            }
            fullData.add(data);

        }

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

    return fullData;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »