List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getMergedRegion
@Override public CellRangeAddress getMergedRegion(int index)
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())); }/* ww w . j av a 2 s . com*/ 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 {//ww w . ja va 2 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
/** * //from w w w . ja v a 2s . 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) { //?????//w ww . j av a 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 ww w.j a v a 2s. 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 a v a 2s . 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(); }
From source file:ro.nextreports.engine.exporter.util.XlsUtil.java
License:Apache License
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; }/*from w w w .ja va 2 s . c o m*/ } return null; }