List of usage examples for org.apache.poi.ss.usermodel Sheet getNumMergedRegions
int getNumMergedRegions();
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 .j a v a 2s . co 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);/* w w w .j a v a 2 s. c o m*/ // ??????? 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.RemoveAdapter.java
License:Open Source License
/** * ??????????????//w w w . j a v a2s . c o m * * @param sheet * @param removeRowNum * @param removeColNum */ private void removeRegion(Sheet sheet, int removeRowNum, int removeColNum) { // ??????? for (int i = 0; i < sheet.getNumMergedRegions(); i++) { // ??(Region)?? CellRangeAddress region = sheet.getMergedRegion(i); if (removeRowNum != -1) { if (region.getFirstRow() > removeRowNum || removeRowNum > region.getLastRow()) { continue; } } if (removeColNum != -1) { if (region.getFirstColumn() > removeColNum || removeColNum > region.getLastColumn()) { continue; } } // ???INDEX????? sheet.removeMergedRegion(i); return; } }
From source file:org.bbreak.excella.reports.ReportsTestUtil.java
License:Open Source License
/** * /*from w w w . j ava 2s. c o m*/ * * @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.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()); // ?//ww w. j a va 2 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.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 va 2 s . com*/ 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 . jav a 2s . c o m * * @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
/** * ???????????//w w w .ja v a 2 s.c om * * @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.corpus_tools.peppermodules.spreadsheet.Spreadsheet2SaltMapper.java
License:Apache License
/** * get the primary text tiers and their annotations of the given document * //w w w. j av a2s . c o m * @param workbook * @param timeline */ private void getPrimTextTiers(Workbook workbook, STimeline timeline) { // get all primary text tiers String primaryTextTier = getProps().getPrimaryText(); // seperate string of primary text tiers into list by commas List<String> primaryTextTierList = Arrays.asList(primaryTextTier.split("\\s*,\\s*")); TreeSet<String> annosWithoutPrim = new TreeSet<>(); if (workbook != null) { // get corpus sheet Sheet corpusSheet; // default ("Tabelle1"/ first sheet) if (getProps().getCorpusSheet().equals("Tabelle1")) { corpusSheet = workbook.getSheetAt(0); } else { // get corpus sheet by name corpusSheet = workbook.getSheet(getProps().getCorpusSheet()); } // end of the excel file int lastRow = corpusSheet.getLastRowNum(); mapLinenumber2STimeline(lastRow, timeline); if (corpusSheet != null) { // row with all names of the annotation tiers (first row) Row headerRow = corpusSheet.getRow(0); // List for each primary text and its annotations HashMap<Integer, Integer> annoPrimRelations = new HashMap<>(); List<Integer> primTextPos = new ArrayList<Integer>(); if (headerRow != null) { // iterate through all tiers and save tiers (column number) // that hold the primary data int currColumn = 0; List<String> emptyColumnList = new ArrayList<>(); while (currColumn < headerRow.getPhysicalNumberOfCells()) { if (headerRow.getCell(currColumn) == null || headerRow.getCell(currColumn).toString().isEmpty()) { String emptyColumn = CellReference.convertNumToColString(currColumn); emptyColumnList.add(emptyColumn); currColumn++; continue; } else { if (!emptyColumnList.isEmpty()) { for (String emptyColumn : emptyColumnList) { SpreadsheetImporter.logger.warn("Column \"" + emptyColumn + "\" in document \"" + getResourceURI().lastSegment() + "\" has no name."); } emptyColumnList = new ArrayList<>(); } boolean primWasFound = false; String tierName = headerRow.getCell(currColumn).toString(); if (primaryTextTierList.contains(tierName)) { // current tier contains primary text // save all indexes of tier containing primary // text primTextPos.add(currColumn); primWasFound = true; } else { // current tier contains (other) annotations if (tierName.matches(".+\\[.+\\]") || getProps().getAnnoPrimRel() != null || getProps().getShortAnnoPrimRel() != null) { if (tierName.matches(".+\\[.+\\]")) { // the belonging primary text was set by // the annotator String primTier = tierName.split("\\[")[1].replace("]", ""); setAnnotationPrimCouple(primTier, annoPrimRelations, currColumn, headerRow); primWasFound = true; } String primOfAnnoFromConfig = getPrimOfAnnoPrimRel(tierName.split("\\[")[0]); if (primOfAnnoFromConfig != null) { // current tier is an annotation and the // belonging primary text was set by // property setAnnotationPrimCouple(primOfAnnoFromConfig, annoPrimRelations, currColumn, headerRow); primWasFound = true; } } else if (primaryTextTierList.size() == 1 && getProps().getAnnoPrimRel() == null && getProps().getShortAnnoPrimRel() == null) { // There is only one primary text so we can // safely assume this is the one // the annotation is connected to. setAnnotationPrimCouple(primaryTextTierList.get(0), annoPrimRelations, currColumn, headerRow); primWasFound = true; } } if (!primWasFound) { annosWithoutPrim.add(tierName); } currColumn++; } } } final Map<String, SLayer> layerTierCouples = getLayerTierCouples(); Table<Integer, Integer, CellRangeAddress> mergedCells = null; if (corpusSheet.getNumMergedRegions() > 0) { mergedCells = calculateMergedCellIndex(corpusSheet.getMergedRegions()); } int progressTotalNumberOfColumns = 0; if (!primTextPos.isEmpty()) { progressTotalNumberOfColumns = setPrimText(corpusSheet, primTextPos, annoPrimRelations, headerRow, mergedCells, layerTierCouples); } else { SpreadsheetImporter.logger .warn("No primary text for the document \"" + getResourceURI().lastSegment() + "\" found. Please check the spelling of your properties."); } setAnnotations(annoPrimRelations, corpusSheet, mergedCells, layerTierCouples, progressTotalNumberOfColumns); } if (getProps().getMetaAnnotation()) { setDocMetaData(workbook); } // report if any column was not included if (!annosWithoutPrim.isEmpty()) { SpreadsheetImporter.logger.warn( "No primary text column found for columns\n- {}\nin document {}. This means these columns are not included in the conversion!", Joiner.on("\n- ").join(annosWithoutPrim), getResourceURI().toFileString()); } } }
From source file:org.drools.decisiontable.parser.xls.ExcelParser.java
License:Apache License
private CellRangeAddress[] getMergedCells(Sheet sheet) { CellRangeAddress[] ranges = new CellRangeAddress[sheet.getNumMergedRegions()]; for (int i = 0; i < ranges.length; i++) { ranges[i] = sheet.getMergedRegion(i); }/*from w w w. j a va2 s . c o m*/ return ranges; }