List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getNumMergedRegions
@Override public int getNumMergedRegions()
From source file:com.report.excel.ExcelToHtmlUtils.java
License:Apache License
/** * Creates a map (i.e. two-dimensional array) filled with ranges. Allow fast * retrieving {@link CellRangeAddress} of any cell, if cell is contained in * range./*from w w w .j av a 2 s . co m*/ * * @see #getMergedRange(CellRangeAddress[][], int, int) */ public static CellRangeAddress[][] buildMergedRangesMap(HSSFSheet sheet) { CellRangeAddress[][] mergedRanges = new CellRangeAddress[1][]; for (int m = 0; m < sheet.getNumMergedRegions(); m++) { final CellRangeAddress cellRangeAddress = sheet.getMergedRegion(m); final int requiredHeight = cellRangeAddress.getLastRow() + 1; if (mergedRanges.length < requiredHeight) { CellRangeAddress[][] newArray = new CellRangeAddress[requiredHeight][]; System.arraycopy(mergedRanges, 0, newArray, 0, mergedRanges.length); mergedRanges = newArray; } for (int r = cellRangeAddress.getFirstRow(); r <= cellRangeAddress.getLastRow(); r++) { final int requiredWidth = cellRangeAddress.getLastColumn() + 1; CellRangeAddress[] rowMerged = mergedRanges[r]; if (rowMerged == null) { rowMerged = new CellRangeAddress[requiredWidth]; mergedRanges[r] = rowMerged; } else { final int rowMergedLength = rowMerged.length; if (rowMergedLength < requiredWidth) { final CellRangeAddress[] newRow = new CellRangeAddress[requiredWidth]; System.arraycopy(rowMerged, 0, newRow, 0, rowMergedLength); mergedRanges[r] = newRow; rowMerged = newRow; } } Arrays.fill(rowMerged, cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn() + 1, cellRangeAddress); } } return mergedRanges; }
From source file:Import.Utils.XSSFConvert.java
/** * Rcupre les informations de fusion des cellules dans la sheet source * pour les appliquer la sheet destination... Rcupre toutes les zones * merged dans la sheet source et regarde pour chacune d'elle si elle se * trouve dans la current row que nous traitons. Si oui, retourne l'objet * CellRangeAddress./*from w w w. ja v a 2s . c om*/ * * @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. */ public static CellRangeAddress getMergedRegion(HSSFSheet sheet, int rowNum, short cellNum) { for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress merged = sheet.getMergedRegion(i); if (merged.isInRange(rowNum, cellNum)) { return merged; } } return null; }
From source file:include.excel_import.Outter.java
License:Open Source License
public boolean isMerged() { for (int i = 0; i < wb.getNumberOfSheets(); i++) { HSSFSheet hssfsheet = wb.getSheetAt(i); if (hssfsheet.getNumMergedRegions() > 0) { message += "?<br>"; return true; }//w ww . j a v a 2 s . co m } return false; }
From source file:include.excel_import.XlsValidator.java
License:Open Source License
private boolean validateSheet(HSSFSheet sheet) { if (sheet == null) { message.append("sheet"); return false; } else if (sheet.getNumMergedRegions() > 0) { message.append("?<br>"); return false; }//from w ww.j ava 2 s. co m return true; }
From source file:org.drools.scorecards.parser.xls.XLSScorecardParser.java
License:Apache License
private List<MergedCellRange> getMergedCellRangeList(HSSFSheet worksheet) { List<MergedCellRange> mergedCellRanges = new ArrayList<MergedCellRange>(); int mergedRegionsCount = worksheet.getNumMergedRegions(); for (int ctr = 0; ctr < mergedRegionsCount; ctr++) { CellRangeAddress rangeAddress = worksheet.getMergedRegion(ctr); mergedCellRanges.add(new MergedCellRange(rangeAddress.getFirstRow(), rangeAddress.getFirstColumn(), rangeAddress.getLastRow(), rangeAddress.getLastColumn())); }// w w w. j av a2s . c o m return mergedCellRanges; }
From source file:org.hil.children.service.impl.ChildrenManagerImpl.java
License:Open Source License
private static void copyRow(HSSFWorkbook workbook, HSSFSheet worksheet, int sourceRowNum, int destinationRowNum) { // Get the source / new row HSSFRow newRow = worksheet.getRow(destinationRowNum); HSSFRow sourceRow = worksheet.getRow(sourceRowNum); // If the row exist in destination, push down all rows by 1 else create a new row if (newRow != null) { worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1); } else {//w w w.ja va2 s.com newRow = worksheet.createRow(destinationRowNum); } // Loop through source columns to add to new row for (int i = 0; i < sourceRow.getLastCellNum(); i++) { // Grab a copy of the old/new cell HSSFCell oldCell = sourceRow.getCell(i); HSSFCell newCell = newRow.createCell(i); // If the old cell is null jump to next cell if (oldCell == null) { newCell = null; continue; } // Copy style from old cell and apply to new cell HSSFCellStyle newCellStyle = workbook.createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); newCell.setCellStyle(newCellStyle); // If there is a cell comment, copy if (newCell.getCellComment() != null) { newCell.setCellComment(oldCell.getCellComment()); } // If there is a cell hyperlink, copy if (oldCell.getHyperlink() != null) { newCell.setHyperlink(oldCell.getHyperlink()); } // Set the cell data type newCell.setCellType(oldCell.getCellType()); // Set the cell data value switch (oldCell.getCellType()) { case Cell.CELL_TYPE_BLANK: newCell.setCellValue(oldCell.getStringCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getRichStringCellValue()); break; } } // If there are are any merged regions in the source row, copy to new row for (int i = 0; i < worksheet.getNumMergedRegions(); i++) { CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i); if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) { CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(), (newRow.getRowNum() + (cellRangeAddress.getFirstRow() - cellRangeAddress.getLastRow())), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()); worksheet.addMergedRegion(newCellRangeAddress); } } }
From source file:org.jxstar.report.util.ReportXlsUtil.java
/** * //w w w .jav a 2 s . c o m * @param mainSheet -- ? * @param subSheet -- ? * @param tempRow -- ????? * @return */ public static HSSFSheet appendSheet(HSSFSheet mainSheet, HSSFSheet subSheet, int tempRow) { if (mainSheet == null || subSheet == null) return null; //?? if (!isAllowOut(mainSheet)) return mainSheet; //? int endRowNum = mainSheet.getPhysicalNumberOfRows(); HSSFRow sourow = null, descrow = null; HSSFCell sourcell = null, descell = null, orgcell = null; int i = 0, offsetcnt = 0; //? copySheetImage(mainSheet.getWorkbook(), subSheet.getWorkbook()); //?? CellRangeAddress range = null; int mergedNum = subSheet.getNumMergedRegions(); for (i = 0; i < mergedNum; i++) { range = subSheet.getMergedRegion(i); range.setFirstRow(range.getFirstRow() + endRowNum); range.setLastRow(range.getLastRow() + endRowNum); mainSheet.addMergedRegion(range); } range = null; //int k = 0; //? mainSheet.setAlternativeExpression(subSheet.getAlternateExpression()); mainSheet.setAlternativeFormula(subSheet.getAlternateFormula()); mainSheet.setAutobreaks(subSheet.getAutobreaks()); mainSheet.setDialog(subSheet.getDialog()); mainSheet.setDisplayGuts(subSheet.getDisplayGuts()); mainSheet.setFitToPage(subSheet.getFitToPage()); for (java.util.Iterator<Row> iterow = subSheet.rowIterator(); iterow.hasNext();) { sourow = (HSSFRow) iterow.next(); offsetcnt = sourow.getRowNum() + endRowNum; descrow = mainSheet.createRow(offsetcnt); descrow.setHeight(sourow.getHeight()); descrow.setHeightInPoints(sourow.getHeightInPoints()); java.util.Iterator<Cell> iter = sourow.cellIterator(); while (iter.hasNext()) { sourcell = (HSSFCell) iter.next(); int column = sourcell.getColumnIndex(); descell = descrow.createCell(column); /** * ??????orgcell = mainSheet.getRow(row).getCell(column); * ?? * ??orgcell.getCellStyle()????sheet?? * This Style does not belong to the supplied Workbook. * ?descell.getCellStyle().cloneStyleFrom(sourcell.getCellStyle());???excel * HSSFCellStyle cs = mainSheet.getWorkbook().createCellStyle(); * cs.cloneStyleFrom(sourcell.getCellStyle()); * descell.setCellStyle(cs);//excel? * tempRow???? */ //???????? int row = sourcell.getRowIndex(); if (tempRow > 0 && row > tempRow) { row = tempRow; } orgcell = mainSheet.getRow(row).getCell(column); if (orgcell != null) { //orgcell.getCellType()???0 descell.setCellType(HSSFCell.CELL_TYPE_STRING); //??? descell.setCellStyle(orgcell.getCellStyle()); } else { _log.showWarn("module xls [{0}, {1}] cell is null!", row, column); } if (sourcell.getCellType() == HSSFCell.CELL_TYPE_STRING) descell.setCellValue(sourcell.getStringCellValue()); else if (sourcell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) descell.setCellValue(sourcell.getNumericCellValue()); } sourow = null; sourcell = null; descrow = null; orgcell = null; } return mainSheet; }
From source file:org.jxstar.report.util.ReportXlsUtil.java
private static void insertSheetRegions(HSSFSheet sheet, int startRow, int rows) { //?????//from ww w . j ava 2 s .c o m List<CellRangeAddress> lsRange = FactoryUtil.newList(); //? int copyi = startRow - 1; //???? int mergedNum = sheet.getNumMergedRegions(); CellRangeAddress region = null; for (int i = 0; i < mergedNum; i++) { region = sheet.getMergedRegion(i); if (region.getFirstRow() == copyi && region.getLastRow() == copyi) { lsRange.add(region.copy()); } } if (lsRange.isEmpty()) return; CellRangeAddress range = null; //?? for (int i = 0; i < rows; i++) { for (int j = 0; j < lsRange.size(); j++) { range = lsRange.get(j); range.setFirstRow(range.getFirstRow() + 1); range.setLastRow(range.getLastRow() + 1); sheet.addMergedRegion(range); } } }
From source file:org.jxstar.report.util.ReportXlsUtil.java
/** * ????//from w ww . j a v a 2 s . c o m * @param cell -- ? * @return */ private static CellRangeAddress getMergedRegion(HSSFCell cell) { HSSFSheet sheet = cell.getSheet(); CellRangeAddress range = null; int mergedNum = sheet.getNumMergedRegions(); for (int i = 0; i < mergedNum; i++) { range = sheet.getMergedRegion(i); if (range.getFirstColumn() == cell.getColumnIndex() && range.getFirstRow() == cell.getRowIndex()) { return range; } } return null; }
From source file:org.jxstar.report.util.XlsToHtml.java
/** * ???//w ww. j ava 2 s . c o m * @param sheet -- * @return */ private String mergedRegion(HSSFSheet sheet) { //??? int mergnum = sheet.getNumMergedRegions(); _log.showDebug("xls file merg cell num is: " + mergnum); if (mergnum <= 0) return ""; StringBuilder sbJs = new StringBuilder(); sbJs.append("<script>\n"); sbJs.append("var xlsmerged = function(){\n"); //???? StringBuilder sbMergpos = new StringBuilder(); //???? for (int i = 0; i < mergnum; i++) { CellRangeAddress range = sheet.getMergedRegion(i); int y1 = range.getFirstColumn(); int x1 = range.getFirstRow(); int y2 = range.getLastColumn(); int x2 = range.getLastRow(); String curid = x1 + "," + y1; //? int rowspan = x2 - x1 + 1; int colspan = y2 - y1 + 1; sbJs.append("\tvar curdom = document.getElementById('" + curid + "');\n"); sbJs.append("\tcurdom.style.width = '';\n"); sbJs.append("\tcurdom.rowSpan = " + rowspan + ";\n"); sbJs.append("\tcurdom.colSpan = " + colspan + ";\n"); //??cell?? for (int j = x1; j <= x2; j++) { for (int k = y1; k <= y2; k++) { if (j == x1 && k == y1) continue; sbMergpos.append('\'').append(j).append(',').append(k).append("',"); } } _log.showDebug("xls file merg cell range:" + x1 + "," + y1 + "-" + x2 + "," + y2); } String spos = sbMergpos.substring(0, sbMergpos.length() - 1); sbJs.append("\tvar mergpos = [" + spos + "];\n"); sbJs.append("\tfor (var i = 0, n = mergpos.length; i < n; i++) {\n"); sbJs.append("\t\tdocument.getElementById(mergpos[i]).style.display = 'none';\n"); sbJs.append("\t}\n"); sbJs.append("};\nxlsmerged();\n"); sbJs.append("</script>\n"); return sbJs.toString(); }