List of usage examples for org.apache.poi.ss.usermodel Sheet getMergedRegion
public CellRangeAddress getMergedRegion(int index);
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 ww.j av a 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 . ja v a 2s .c o m checkParam(paramDef, tagCell); String tag = tagCell.getStringCellValue(); ReportsParserInfo reportsParserInfo = (ReportsParserInfo) data; // ? Object[] paramValues = null; try { // ? boolean rowShift = false; if (paramDef.containsKey(PARAM_ROW_SHIFT)) { rowShift = Boolean.valueOf(paramDef.get(PARAM_ROW_SHIFT)); } // ?? boolean hideDuplicate = false; if (paramDef.containsKey(PARAM_DUPLICATE)) { hideDuplicate = Boolean.valueOf(paramDef.get(PARAM_DUPLICATE)); } // ? Integer breakNum = null; if (paramDef.containsKey(PARAM_BREAK_NUM)) { breakNum = Integer.valueOf(paramDef.get(PARAM_BREAK_NUM)); } // boolean changeBreak = false; if (paramDef.containsKey(PARAM_CHANGE_BREAK)) { changeBreak = Boolean.valueOf(paramDef.get(PARAM_CHANGE_BREAK)); } // ? Integer repeatNum = null; if (paramDef.containsKey(PARAM_REPEAT_NUM)) { repeatNum = Integer.valueOf(paramDef.get(PARAM_REPEAT_NUM)); } // ?? Integer minRepeatNum = null; if (paramDef.containsKey(PARAM_MIN_REPEAT_NUM)) { minRepeatNum = Integer.valueOf(paramDef.get(PARAM_MIN_REPEAT_NUM)); } // ? boolean sheetLink = false; if (paramDef.containsKey(PARAM_SHEET_LINK)) { sheetLink = Boolean.valueOf(paramDef.get(PARAM_SHEET_LINK)); } // String propertyName = null; if (paramDef.containsKey(PARAM_PROPERTY)) { propertyName = paramDef.get(PARAM_PROPERTY); } // ??? String replaceParam = paramDef.get(PARAM_VALUE); // if (ReportsUtil.VALUE_SHEET_NAMES.equals(replaceParam)) { // ?? paramValues = ReportsUtil.getSheetNames(reportsParserInfo.getReportBook()).toArray(); } else if (ReportsUtil.VALUE_SHEET_VALUES.equals(replaceParam)) { // paramValues = ReportsUtil.getSheetValues(reportsParserInfo.getReportBook(), propertyName, reportsParserInfo.getReportParsers()).toArray(); } else { // ??? ParamInfo paramInfo = reportsParserInfo.getParamInfo(); if (paramInfo != null) { paramValues = getParamData(paramInfo, replaceParam); } } if (paramValues == null || paramValues.length == 0) { // ? paramValues = new Object[] { null }; } // ? int shiftNum = paramValues.length; // ? int paramLength = paramValues.length; // ??????? if (minRepeatNum != null && shiftNum < minRepeatNum) { Object[] tmpValues = new Object[minRepeatNum]; System.arraycopy(paramValues, 0, tmpValues, 0, paramValues.length); paramValues = tmpValues; shiftNum = paramValues.length; paramLength = paramValues.length; } // ??? int defaultFromCellRowIndex = tagCell.getRowIndex(); // ??? int defaultFromCellColIndex = tagCell.getColumnIndex(); // ??? int unitRowSize = 1; // ??? List<CellRangeAddress> maegedAddresses = new ArrayList<CellRangeAddress>(); for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress targetAddress = sheet.getMergedRegion(i); maegedAddresses.add(targetAddress); } // ??? if (maegedAddresses.size() > 0) { // ????????????? for (CellRangeAddress curMergedAdress : maegedAddresses) { if (defaultFromCellColIndex == curMergedAdress.getFirstColumn() && defaultFromCellRowIndex == curMergedAdress.getFirstRow()) { // ???????????? // ?????? unitRowSize = curMergedAdress.getLastRow() - curMergedAdress.getFirstRow() + 1; // ?????? shiftNum = shiftNum * unitRowSize; } } } // ??? // ????? tagCell = new CellClone(tagCell); List<Cell> cellList = new ArrayList<Cell>(); int defaultToOverCellRowIndex = tagCell.getRowIndex() + unitRowSize; for (int i = defaultFromCellRowIndex; i < defaultToOverCellRowIndex; i++) { Row targetCellRow = sheet.getRow(i); cellList.add(new CellClone(targetCellRow.getCell(tagCell.getColumnIndex()))); } // ? if (repeatNum != null && repeatNum < shiftNum) { // ?????? // ???????????????? // ????? shiftNum = repeatNum * unitRowSize; // ?????? paramLength = repeatNum; } // ? if (shiftNum > 1) { // ?(????????) int shiftRowSize = tagCell.getRowIndex() + shiftNum - unitRowSize - 1; if (!rowShift) { // ????? CellRangeAddress rangeAddress = new CellRangeAddress(tagCell.getRowIndex(), shiftRowSize, tagCell.getColumnIndex(), tagCell.getColumnIndex()); PoiUtil.insertRangeDown(sheet, rangeAddress); } else { // ????? // #35 POI??????????????????? // ??????0???????? CellRangeAddress rangeAddress = new CellRangeAddress(tagCell.getRowIndex() + unitRowSize, tagCell.getRowIndex() + shiftNum - 1, 0, PoiUtil.getLastColNum(sheet)); PoiUtil.insertRangeDown(sheet, rangeAddress); // int shiftStartRow = tagCell.getRowIndex() + 1; // int shiftEndRow = sheet.getLastRowNum(); // if ( shiftEndRow < shiftStartRow) { // // ???????????????? // // ???????????? // shiftEndRow = shiftStartRow + 1; // } // sheet.shiftRows( shiftStartRow, shiftEndRow, shiftNum - unitRowSize); } } // ??? Workbook workbook = sheet.getWorkbook(); String sheetName = workbook.getSheetName(workbook.getSheetIndex(sheet)); // ?? List<String> sheetNames = ReportsUtil.getSheetNames(reportsParserInfo.getReportBook()); // ? List<Object> resultValues = new ArrayList<Object>(); // ??(beforeValue) Object beforeValue = null; // ? int valueIndex = -1; // ????? for (int rowIndex = 0; rowIndex < shiftNum; rowIndex++) { // ?? Row row = sheet.getRow(tagCell.getRowIndex() + rowIndex); if (row == null) { row = sheet.createRow(tagCell.getRowIndex() + rowIndex); } // ?? Cell cell = row.getCell(tagCell.getColumnIndex()); if (cell == null) { cell = row.createCell(tagCell.getColumnIndex()); } // ????(null) Object value = null; // ?????? // ??0???(?????????)??????? int cellIndex = rowIndex % unitRowSize; // ????????? boolean skipRow = false; if (cellIndex != 0) { skipRow = true; } else { valueIndex++; } // (?) PoiUtil.copyCell(cellList.get(cellIndex), cell); // ? Object currentValue = paramValues[valueIndex]; // ??=true??????????? boolean duplicateValue = false; if (beforeValue != null && currentValue != null && beforeValue.equals(currentValue)) { // ??? duplicateValue = true; } if (!skipRow && !(hideDuplicate && duplicateValue)) { // ??=true // ?????????????? value = currentValue; } if (log.isDebugEnabled()) { log.debug("[??=" + sheetName + ",=(" + cell.getRowIndex() + "," + cell.getColumnIndex() + ")] " + tag + " " + value); } PoiUtil.setCellValue(cell, value); resultValues.add(value); // ? if (sheetLink) { if (!skipRow && valueIndex < sheetNames.size()) { PoiUtil.setHyperlink(cell, HyperlinkType.DOCUMENT, "'" + sheetNames.get(valueIndex) + "'!A1"); if (log.isDebugEnabled()) { log.debug("[??=" + sheetName + ",=(" + cell.getRowIndex() + "," + cell.getColumnIndex() + ")] Hyperlink " + "'" + sheetNames.get(valueIndex) + "'!A1"); } } } // if (!skipRow) { if (breakNum != null && valueIndex != 0 && valueIndex % breakNum == 0) { // ??? sheet.setRowBreak(row.getRowNum() - 1); } if (changeBreak && valueIndex != 0 && !duplicateValue) { // ??? sheet.setRowBreak(row.getRowNum() - 1); } } // ?? // ?????????? // ???????????? if (!skipRow && unitRowSize > 1) { // ??? boolean mergedRegionFlag = false; if (rowShift && valueIndex != 0) { // ????????????? mergedRegionFlag = true; } else if (!rowShift && paramLength > valueIndex + 1) { // ???????????? mergedRegionFlag = true; } // ?? if (mergedRegionFlag) { CellRangeAddress rangeAddress = new CellRangeAddress(cell.getRowIndex(), cell.getRowIndex() + unitRowSize - 1, cell.getColumnIndex(), cell.getColumnIndex()); sheet.addMergedRegion(rangeAddress); // ???????????? beforeValue = currentValue; } } // ??????? if (unitRowSize == 1) { beforeValue = currentValue; } } // ???? ParsedReportInfo parsedReportInfo = new ParsedReportInfo(); parsedReportInfo.setParsedObject(resultValues); // ?? parsedReportInfo.setDefaultRowIndex(tagCell.getRowIndex() + unitRowSize - 1); parsedReportInfo.setDefaultColumnIndex(tagCell.getColumnIndex()); parsedReportInfo.setRowIndex(tagCell.getRowIndex() + shiftNum - 1); parsedReportInfo.setColumnIndex(tagCell.getColumnIndex()); if (log.isDebugEnabled()) { log.debug(parsedReportInfo); } return parsedReportInfo; } catch (Exception e) { throw new ParseException(tagCell, e); } }
From source file:org.bbreak.excella.reports.util.ReportsUtil.java
License:Open Source License
/** * ???????????//w w w . ja v a2 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.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 www .j a v a 2s .c o m*/ return ranges; }
From source file:org.efaps.esjp.common.file.FileUtil_Base.java
License:Apache License
/** * @param _sheet the sheet containing the data. * @param _rowNum the num of the row to copy. * @param _cellNum the num of the cell to copy. * @return the CellRangeAddress created. *//*from w w w .ja va 2 s . c o m*/ protected CellRangeAddress getMergedRegion(final Sheet _sheet, final int _rowNum, final short _cellNum) { CellRangeAddress ret = null; for (int i = 0; i < _sheet.getNumMergedRegions(); i++) { final CellRangeAddress merged = _sheet.getMergedRegion(i); if (merged.isInRange(_rowNum, _cellNum)) { ret = merged; break; } } return ret; }
From source file:org.netxilia.impexp.impl.ExcelImportService.java
License:Open Source License
@Override public List<SheetFullName> importSheets(INetxiliaSystem workbookProcessor, WorkbookId workbookName, InputStream is, IProcessingConsole console) throws ImportException { List<SheetFullName> sheetNames = new ArrayList<SheetFullName>(); try {/* w w w .ja v a 2 s . c o m*/ log.info("Starting import:" + workbookName); Workbook poiWorkbook = new HSSFWorkbook(is); IWorkbook nxWorkbook = workbookProcessor.getWorkbook(workbookName); log.info("Read POI"); NetxiliaStyleResolver styleResolver = new NetxiliaStyleResolver( styleService.getStyleDefinitions(workbookName)); HSSFPalette palette = ((HSSFWorkbook) poiWorkbook).getCustomPalette(); for (int s = 0; s < poiWorkbook.getNumberOfSheets(); ++s) { Sheet poiSheet = poiWorkbook.getSheetAt(s); SheetFullName sheetName = new SheetFullName(workbookName, getNextFreeSheetName(nxWorkbook, poiSheet.getSheetName())); ISheet nxSheet = null; BlockCellCommandBuilder cellCommandBuilder = new BlockCellCommandBuilder(); try { List<CellReference> refreshCells = new ArrayList<CellReference>(); for (Row poiRow : poiSheet) { if (poiRow.getRowNum() % 100 == 0) { log.info("importing row #" + poiRow.getRowNum()); } for (Cell poiCell : poiRow) { if (nxSheet == null) { // lazy creation while (true) { try { nxSheet = nxWorkbook.addNewSheet(sheetName.getSheetName(), SheetType.normal); nxSheet.setRefreshEnabled(false); break; } catch (AlreadyExistsException e) { // may happen is simultaneous imports take place sheetName = new SheetFullName(workbookName, getNextFreeSheetName(nxWorkbook, poiSheet.getSheetName())); } } } CellReference ref = new CellReference(sheetName.getSheetName(), poiRow.getRowNum(), poiCell.getColumnIndex()); try { ICellCommand cmd = copyCell(poiCell, ref, palette, styleResolver); if (cmd != null) { cellCommandBuilder.command(cmd); } if (poiCell.getCellType() == Cell.CELL_TYPE_FORMULA) { refreshCells.add(ref); } } catch (Exception e) { if (console != null) { console.println("Could import cell " + ref + ":" + poiCell + ":" + e); } log.error("Could import cell " + ref + ":" + poiCell + ":" + e, e); } } if (poiRow.getRowNum() % 100 == 0 && !cellCommandBuilder.isEmpty()) { nxSheet.sendCommandNoUndo(cellCommandBuilder.build()); cellCommandBuilder = new BlockCellCommandBuilder(); } } if (nxSheet == null) { // empty sheet continue; } if (!cellCommandBuilder.isEmpty()) { nxSheet.sendCommandNoUndo(cellCommandBuilder.build()); } // add the columns after as is not very clear how to get the number of cols in poi for (int c = 0; c < nxSheet.getDimensions().getNonBlocking().getColumnCount(); ++c) { int width = 50; try { width = PoiUtils.widthUnits2Pixel(poiSheet.getColumnWidth(c)); nxSheet.sendCommand(ColumnCommands.width(Range.range(c), width)); } catch (NullPointerException ex) { // ignore it // NPE in at org.apache.poi.hssf.model.Sheet.getColumnWidth(Sheet.java:998) // defaultColumnWidth can be null !? } CellStyle poiStyle = poiSheet.getColumnStyle(c); if (poiStyle == null) { continue; } Styles styles = PoiUtils.poiStyle2Netxilia(poiStyle, poiSheet.getWorkbook().getFontAt(poiStyle.getFontIndex()), palette, styleResolver); if (styles != null) { nxSheet.sendCommand(ColumnCommands.styles(Range.range(c), styles)); } } // merge List<AreaReference> spans = new ArrayList<AreaReference>(poiSheet.getNumMergedRegions()); for (int i = 0; i < poiSheet.getNumMergedRegions(); ++i) { CellRangeAddress poiSpan = poiSheet.getMergedRegion(i); spans.add(new AreaReference(sheetName.getSheetName(), poiSpan.getFirstRow(), poiSpan.getFirstColumn(), poiSpan.getLastRow(), poiSpan.getLastColumn())); } nxSheet.sendCommand(SheetCommands.spans(spans)); // refresh all the cells now nxSheet.setRefreshEnabled(true); nxSheet.sendCommandNoUndo(moreCellCommands.refresh(refreshCells, false)); } finally { if (nxSheet != null) { sheetNames.add(sheetName); } } } } catch (IOException e) { throw new ImportException(null, "Cannot open workbook:" + e, e); } catch (StorageException e) { throw new ImportException(null, "Error storing sheet:" + e, e); } catch (NotFoundException e) { throw new ImportException(null, "Cannot find workbook:" + e, e); } catch (NetxiliaResourceException e) { throw new ImportException(null, e.getMessage(), e); } catch (NetxiliaBusinessException e) { throw new ImportException(null, e.getMessage(), e); } return sheetNames; }
From source file:org.newcashel.meta.model.NCClass.java
License:Apache License
private static CellRangeAddress getCellRangeAddress(Sheet sheet, int row, int col) { for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress cellRange = sheet.getMergedRegion(i); if (cellRange.isInRange(row, col)) return cellRange; }//from w ww .j a v a 2s .c om return null; }
From source file:org.openpythia.utilities.SSUtilities.java
License:Apache License
public static Row copyRow(Sheet sheet, Row sourceRow, int destination) { Row newRow = sheet.createRow(destination); // get the last row from the headings int lastCol = sheet.getRow(0).getLastCellNum(); for (int currentCol = 0; currentCol <= lastCol; currentCol++) { Cell newCell = newRow.createCell(currentCol); // if there is a cell in the template, copy its content and style Cell currentCell = sourceRow.getCell(currentCol); if (currentCell != null) { newCell.setCellStyle(currentCell.getCellStyle()); newCell.setCellComment(currentCell.getCellComment()); switch (currentCell.getCellType()) { case Cell.CELL_TYPE_STRING: newCell.setCellValue(currentCell.getStringCellValue()); break; case Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(currentCell.getNumericCellValue()); break; case Cell.CELL_TYPE_FORMULA: String dummy = currentCell.getCellFormula(); dummy = dummy.replace("Row", String.valueOf(destination + 1)); newCell.setCellFormula(dummy); newCell.setCellFormula(//from w ww . j a v a 2 s . com currentCell.getCellFormula().replace("Row", String.valueOf(destination + 1))); break; case Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(currentCell.getBooleanCellValue()); break; default: } } } // if the row contains merged regions, copy them to the new row int numberMergedRegions = sheet.getNumMergedRegions(); for (int i = 0; i < numberMergedRegions; i++) { CellRangeAddress mergedRegion = sheet.getMergedRegion(i); if (mergedRegion.getFirstRow() == sourceRow.getRowNum() && mergedRegion.getLastRow() == sourceRow.getRowNum()) { // this region is within the row - so copy it sheet.addMergedRegion(new CellRangeAddress(destination, destination, mergedRegion.getFirstColumn(), mergedRegion.getLastColumn())); } } return newRow; }
From source file:org.openpythia.utilities.SSUtilities.java
License:Apache License
public static void deleteRow(Sheet sheet, Row rowToDelete) { // if the row contains merged regions, delete them List<Integer> mergedRegionsToDelete = new ArrayList<>(); int numberMergedRegions = sheet.getNumMergedRegions(); for (int i = 0; i < numberMergedRegions; i++) { CellRangeAddress mergedRegion = sheet.getMergedRegion(i); if (mergedRegion.getFirstRow() == rowToDelete.getRowNum() && mergedRegion.getLastRow() == rowToDelete.getRowNum()) { // this region is within the row - so mark it for deletion mergedRegionsToDelete.add(i); }//from ww w. j a v a 2s.co m } // now that we know all regions to delete just do it for (Integer indexToDelete : mergedRegionsToDelete) { sheet.removeMergedRegion(indexToDelete); } int rowIndex = rowToDelete.getRowNum(); // this only removes the content of the row sheet.removeRow(rowToDelete); int lastRowNum = sheet.getLastRowNum(); // shift the rest of the sheet one index down if (rowIndex >= 0 && rowIndex < lastRowNum) { sheet.shiftRows(rowIndex + 1, lastRowNum, -1); } }
From source file:org.primefaces.extensions.component.exporter.ExcelExporter.java
License:Apache License
protected void tableColumnGroup(Sheet sheet, SubTable table, String facetType) { ColumnGroup cg = table.getColumnGroup(facetType); List<UIComponent> headerComponentList = null; if (cg != null) { headerComponentList = cg.getChildren(); }/*from w w w. jav a 2s. com*/ if (headerComponentList != null) { for (UIComponent component : headerComponentList) { if (component instanceof org.primefaces.component.row.Row) { org.primefaces.component.row.Row row = (org.primefaces.component.row.Row) component; int sheetRowIndex = sheet.getPhysicalNumberOfRows() > 0 ? sheet.getLastRowNum() + 1 : 0; Row xlRow = sheet.createRow(sheetRowIndex); int i = 0; for (UIComponent rowComponent : row.getChildren()) { UIColumn column = (UIColumn) rowComponent; String value = null; if (facetType.equalsIgnoreCase("header")) { value = column.getHeaderText(); } else { value = column.getFooterText(); } int rowSpan = column.getRowspan(); int colSpan = column.getColspan(); Cell cell = xlRow.getCell(i); if (rowSpan > 1 || colSpan > 1) { if (rowSpan > 1) { cell = xlRow.createCell((short) i); Boolean rowSpanFlag = false; for (int j = 0; j < sheet.getNumMergedRegions(); j++) { CellRangeAddress merged = sheet.getMergedRegion(j); if (merged.isInRange(sheetRowIndex, i)) { rowSpanFlag = true; } } if (!rowSpanFlag) { cell.setCellStyle(cellStyle); cell.setCellValue(value); sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, //first row (0-based) sheetRowIndex + rowSpan - 1, //last row (0-based) i, //first column (0-based) i //last column (0-based) )); } } if (colSpan > 1) { cell = xlRow.createCell((short) i); for (int j = 0; j < sheet.getNumMergedRegions(); j++) { CellRangeAddress merged = sheet.getMergedRegion(j); if (merged.isInRange(sheetRowIndex, i)) { cell = xlRow.createCell((short) ++i); } } cell.setCellStyle(cellStyle); cell.setCellValue(value); sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, //first row (0-based) sheetRowIndex, //last row (0-based) i, //first column (0-based) i + colSpan - 1 //last column (0-based) )); i = i + colSpan - 1; } } else { cell = xlRow.createCell((short) i); for (int j = 0; j < sheet.getNumMergedRegions(); j++) { CellRangeAddress merged = sheet.getMergedRegion(j); if (merged.isInRange(sheetRowIndex, i)) { cell = xlRow.createCell((short) ++i); } } cell.setCellValue(value); cell.setCellStyle(facetStyle); } i++; } } } } }