List of usage examples for org.apache.poi.ss.usermodel Sheet getMergedRegion
public CellRangeAddress getMergedRegion(int index);
From source file:me.utils.excel.ImportExcelme.java
License:Open Source License
/** * ? //from w w w . j a v a 2 s . c o m * @param sheet * @param row * @param column * @return */ private boolean isMergedRow(Sheet sheet, int row, int column) { int sheetMergeCount = sheet.getNumMergedRegions(); for (int i = 0; i < sheetMergeCount; i++) { CellRangeAddress range = sheet.getMergedRegion(i); int firstColumn = range.getFirstColumn(); int lastColumn = range.getLastColumn(); int firstRow = range.getFirstRow(); int lastRow = range.getLastRow(); if (row == firstRow && row == lastRow) { if (column >= firstColumn && column <= lastColumn) { return true; } } } return false; }
From source file:net.sf.excelutils.tags.EachTag.java
License:Apache License
public int[] parseTag(Object context, Workbook wb, Sheet sheet, Row curRow, Cell curCell) throws ExcelException { String expr = ""; String each = curCell.getStringCellValue(); LOG.debug("EachTag:" + each); StringTokenizer st = new StringTokenizer(each, " "); String widthstr = ""; String onstr = ""; int pos = 0;/* w ww . j a va 2 s .c om*/ while (st.hasMoreTokens()) { String str = st.nextToken(); if (pos == 1) { expr = str; } if (pos == 2 && !"on".equals(str)) { widthstr = str; } if (pos == 3 && !"on".equals(str)) { onstr = str; } if (pos == 4) { onstr = str; } pos++; } int[] widths = new int[0]; if (null != widthstr && !"".equals(widthstr)) { Object o = ExcelParser.parseStr(context, widthstr); if (null != o) { String[] s = o.toString().split(","); widths = new int[s.length]; for (int i = 0; i < widths.length; i++) { widths[i] = Integer.parseInt(s[i]); } } } Object obj = ExcelParser.parseExpr(context, expr); if (null == obj) return new int[] { 0, 0, 0 }; // by onstr get the property if (!"".equals(onstr)) { obj = ExcelParser.parseExpr(context, onstr); if (null == obj) return new int[] { 0, 0, 0 }; } // iterator properties Iterator it = ExcelParser.getIterator(obj); if (null == it) { if (obj instanceof DynaBean) { it = ExcelParser.getIterator(ExcelParser.getBeanProperties(((DynaBean) obj).getDynaClass())); } else { it = ExcelParser.getIterator(ExcelParser.getBeanProperties(obj.getClass())); } } if (null == it) { return new int[] { 0, 0, 0 }; } int index = 0; int arrayIndex = 0; int eachPos = curCell.getColumnIndex(); String modelName = expr.substring(ExcelParser.VALUED_DELIM.length(), expr.length() - ExcelParser.VALUED_DELIM2.length()); // restore the obj obj = ExcelParser.parseExpr(context, expr); while (it.hasNext()) { Object o = it.next(); String property = ""; if (o instanceof Field) { property = ((Field) o).getName(); } else if (o instanceof Map.Entry) { property = ((Map.Entry) o).getKey().toString(); } else if (o instanceof DynaProperty) { property = ((DynaProperty) o).getName(); } else if (null != o) { property = o.toString(); } // test the object is array/list or other if (obj.getClass().isArray() || obj instanceof Collection) { property = modelName + "[" + arrayIndex++ + "]"; } else { property = modelName + "." + property; } Object value = ExcelParser.getValue(context, property); if (null == value) value = ""; if (ExcelUtils.isCanShowType(value)) { // get cell merge count int width = 1; if (index < widths.length) { width = widths[index]; } else if (1 == widths.length) { width = widths[0]; } // get row merged of the curCell int rowMerged = 1; for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress r = sheet.getMergedRegion(i); if (r.getFirstRow() == curRow.getRowNum() && r.getFirstColumn() == curCell.getColumnIndex() && r.getLastColumn() == curCell.getColumnIndex()) { rowMerged = r.getLastRow() - r.getFirstRow() + 1; break; } } Cell cell = WorkbookUtils.getCell(curRow, eachPos); // shift the after cell if (index > 0) { WorkbookUtils.shiftCell(sheet, curRow, cell, 1, rowMerged); } if (width > 1) { Cell nextCell = WorkbookUtils.getCell(curRow, eachPos + 1); WorkbookUtils.shiftCell(sheet, curRow, nextCell, width - 1, rowMerged); } // copy the style of curCell for (int rownum = curRow.getRowNum(); rownum < curRow.getRowNum() + rowMerged; rownum++) { for (int i = 0; i < width; i++) { Row r = WorkbookUtils.getRow(rownum, sheet); Cell c = WorkbookUtils.getCell(r, eachPos + i); Cell cc = WorkbookUtils.getCell(r, curCell.getColumnIndex()); c.setCellStyle(cc.getCellStyle()); } } // merge cells if (width > 1 || rowMerged > 1) { sheet.addMergedRegion( new CellRangeAddress(curRow.getRowNum(), curRow.getRowNum() + rowMerged - 1, cell.getColumnIndex(), cell.getColumnIndex() + width - 1)); } cell.setCellValue("${" + property + "}"); ExcelParser.parseCell(context, sheet, curRow, cell); eachPos += width; index++; } } return new int[] { 0, 0, 0 }; }
From source file:net.sf.excelutils.tags.ForeachTag.java
License:Apache License
public int[] parseTag(Object context, Workbook wb, Sheet sheet, Row curRow, Cell curCell) throws ExcelException { int forstart = curRow.getRowNum(); int forend = -1; int forCount = 0; String foreach = ""; boolean bFind = false; LOG.debug("ForeachTag: start=" + forstart); for (int rownum = forstart; rownum <= sheet.getLastRowNum(); rownum++) { Row row = sheet.getRow(rownum);/*from ww w . j ava 2 s. c o m*/ if (null == row) continue; for (short colnum = row.getFirstCellNum(); colnum <= row.getLastCellNum(); colnum++) { Cell cell = row.getCell(colnum, Row.RETURN_NULL_AND_BLANK); if (null == cell) continue; if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { String cellstr = cell.getStringCellValue(); // get the tag instance for the cellstr ITag tag = ExcelParser.getTagClass(cellstr); if (null != tag) { if (tag.hasEndTag()) { if (0 == forCount) { forstart = rownum; foreach = cellstr; } forCount++; break; } } if (cellstr.startsWith(KEY_END)) { forend = rownum; forCount--; if (forstart >= 0 && forend >= 0 && forend > forstart && forCount == 0) { bFind = true; } break; } } } if (bFind) break; } if (!bFind) return new int[] { 0, 0, 1 }; String properties = ""; String property = ""; // parse the collection an object StringTokenizer st = new StringTokenizer(foreach, " "); int pos = 0; while (st.hasMoreTokens()) { String str = st.nextToken(); if (pos == 1) { property = str; } if (pos == 3) { properties = str; } pos++; } // get collection Object collection = ExcelParser.parseStr(context, properties); if (null == collection) { return new int[] { 0, 0, 1 }; } // get the iterator of collection Iterator iterator = ExcelParser.getIterator(collection); // iterator int shiftNum = forend - forstart - 1; // set the start row number ExcelUtils.addValue(context, property + "StartRowNo", new Integer(forstart + 1)); int old_forend = forend; int propertyId = 0; int shift = 0; if (null != iterator) { while (iterator.hasNext()) { Object obj = iterator.next(); ExcelUtils.addValue(context, property, obj); // Iterator ID ExcelUtils.addValue(context, property + "Id", new Integer(propertyId)); // Index start with 1 ExcelUtils.addValue(context, property + "Index", new Integer(propertyId + 1)); // shift the #foreach #end block sheet.shiftRows(forstart, sheet.getLastRowNum(), shiftNum, true, true); // copy the body fo #foreach #end block WorkbookUtils.copyRow(sheet, forstart + shiftNum + 1, forstart, shiftNum); // parse shift = ExcelParser.parse(context, wb, sheet, forstart, forstart + shiftNum - 1); forstart += shiftNum + shift; forend += shiftNum + shift; propertyId++; } ExcelUtils.addValue(context, property + "Size", new Integer(propertyId)); } // set the end row number ExcelUtils.addValue(context, property + "EndRowNo", new Integer(forstart)); // delete #foreach #end block for (int rownum = forstart; rownum <= forend; rownum++) { sheet.removeRow(WorkbookUtils.getRow(rownum, sheet)); } // remove merged region in forstart & forend for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress r = sheet.getMergedRegion(i); if (r.getFirstRow() >= forstart && r.getLastRow() <= forend) { sheet.removeMergedRegion(i); // we have to back up now since we removed one i = i - 1; } } if (forend + 1 <= sheet.getLastRowNum()) { sheet.shiftRows(forend + 1, sheet.getLastRowNum(), -(forend - forstart + 1), true, true); } return new int[] { ExcelParser.getSkipNum(forstart, forend), ExcelParser.getShiftNum(old_forend, forstart), 1 }; }
From source file:net.sf.excelutils.tags.IfTag.java
License:Apache License
public int[] parseTag(Object context, Workbook wb, Sheet sheet, Row curRow, Cell curCell) throws ExcelException { int ifstart = curRow.getRowNum(); int ifend = -1; int ifCount = 0; String ifstr = ""; boolean bFind = false; for (int rownum = ifstart; rownum <= sheet.getLastRowNum(); rownum++) { Row row = sheet.getRow(rownum);/* ww w. j a va 2 s. c o m*/ if (null == row) continue; for (short colnum = row.getFirstCellNum(); colnum <= row.getLastCellNum(); colnum++) { Cell cell = row.getCell(colnum, Row.RETURN_NULL_AND_BLANK); if (null == cell) continue; if (cell.getCellType() == Cell.CELL_TYPE_STRING) { String cellstr = cell.getStringCellValue(); // get the tag instance for the cellstr ITag tag = ExcelParser.getTagClass(cellstr); if (null != tag) { if (tag.hasEndTag()) { if (0 == ifCount) { ifstart = rownum; ifstr = cellstr; } ifCount++; break; } } if (cellstr.startsWith(KEY_END)) { ifend = rownum; ifCount--; if (ifstart >= 0 && ifend >= 0 && ifend > ifstart && ifCount == 0) { bFind = true; } break; } } } if (bFind) break; } if (!bFind) return new int[] { 0, 0, 1 }; // test if condition boolean bResult = false; // remove #if tag and get condition expression String expr = ifstr.trim().substring(KEY_IF.length()).trim(); // parse the condition expression expr = (String) ExcelParser.parseStr(context, expr, true); // use beanshell to eval expression value try { Interpreter in = createInterpreter(context); LOG.debug("IfTag test expr=" + expr); Object v = in.eval(expr); bResult = ((Boolean) v).booleanValue(); } catch (Exception e) { LOG.error("IfTag test expr error", e); bResult = false; } if (bResult) { // if condition is true // remove #if tag and #end tag only sheet.removeRow(WorkbookUtils.getRow(ifstart, sheet)); sheet.removeRow(WorkbookUtils.getRow(ifend, sheet)); // remove merged region in ifstart & ifend for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress r = sheet.getMergedRegion(i); if (r.getFirstRow() == ifstart && r.getLastRow() == ifstart || r.getFirstRow() == ifend && r.getLastRow() == ifend) { sheet.removeMergedRegion(i); // we have to back up now since we removed one i = i - 1; } } if (ifend + 1 <= sheet.getLastRowNum()) { sheet.shiftRows(ifend + 1, sheet.getLastRowNum(), -1, true, true); } if (ifstart + 1 <= sheet.getLastRowNum()) { sheet.shiftRows(ifstart + 1, sheet.getLastRowNum(), -1, true, true); } return new int[] { 1, -2, 1 }; } else { // if condition is false // remove #if #end block for (int rownum = ifstart; rownum <= ifend; rownum++) { sheet.removeRow(WorkbookUtils.getRow(rownum, sheet)); } // remove merged region in ifstart & ifend for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress r = sheet.getMergedRegion(i); if (r.getFirstRow() >= ifstart && r.getLastRow() <= ifend) { sheet.removeMergedRegion(i); // we have to back up now since we removed one i = i - 1; } } if (ifend + 1 <= sheet.getLastRowNum()) { sheet.shiftRows(ifend + 1, sheet.getLastRowNum(), -(ifend - ifstart + 1), true, true); } return new int[] { ExcelParser.getSkipNum(ifstart, ifend), ExcelParser.getShiftNum(ifend, ifstart), 1 }; } }
From source file:net.sf.excelutils.tags.SheetTag.java
License:Apache License
/** * Parse #sheet detail in list by sheetName *///from w ww . jav a2s . c om public int[] parseTag(Object context, Workbook wb, Sheet sheet, Row curRow, Cell curCell) throws ExcelException { String sheetExpr = curCell.getStringCellValue(); StringTokenizer st = new StringTokenizer(sheetExpr, " "); String properties = ""; String property = ""; String sheetName = ""; // parse the collection an object int pos = 0; while (st.hasMoreTokens()) { String str = st.nextToken(); if (pos == 1) { property = str; } if (pos == 3) { properties = str; } if (pos == 5) { sheetName = str; } pos++; } // get collection Object collection = ExcelParser.parseStr(context, properties); if (null == collection) { return new int[] { 0, 0, 1 }; } // remove #sheet tag sheet.removeRow(curRow); // remove merged region in forstart & forend for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress r = sheet.getMergedRegion(i); if (r.getFirstRow() >= curRow.getRowNum() && r.getLastRow() <= curRow.getRowNum()) { sheet.removeMergedRegion(i); i = i - 1; } } sheet.shiftRows(curRow.getRowNum() + 1, sheet.getLastRowNum(), -1, true, true); // get the iterator of collection Iterator iterator = ExcelParser.getIterator(collection); if (null != iterator) { // first obj, use parse method Object firstObj = null; if (iterator.hasNext()) { firstObj = iterator.next(); } // next obj, clone sheet and use parseSheet method while (iterator.hasNext()) { Object obj = iterator.next(); ExcelUtils.addValue(context, property, obj); try { int sheetIndex = WorkbookUtils.getSheetIndex(wb, sheet); // clone sheet Sheet cloneSheet = wb.cloneSheet(sheetIndex); // set cloneSheet name int cloneSheetIndex = WorkbookUtils.getSheetIndex(wb, cloneSheet); setSheetName(obj, wb, cloneSheetIndex, sheetName); // parse cloneSheet ExcelUtils.parseSheet(context, wb, cloneSheet); } catch (Exception e) { if (LOG.isErrorEnabled()) { LOG.error("parse sheet error", e); } } } if (null != firstObj) { ExcelUtils.addValue(context, property, firstObj); // set sheet name int sheetIndex = WorkbookUtils.getSheetIndex(wb, sheet); setSheetName(firstObj, wb, sheetIndex, sheetName); } } return new int[] { 0, -1, 0 }; }
From source file:net.sf.excelutils.WorkbookUtils.java
License:Apache License
/** * copy row/*from w w w . j a v a2 s .c om*/ * * @param sheet * @param from begin of the row * @param to destination fo the row * @param count count of copy */ public static void copyRow(Sheet sheet, int from, int to, int count) { for (int rownum = from; rownum < from + count; rownum++) { Row fromRow = sheet.getRow(rownum); Row toRow = getRow(to + rownum - from, sheet); if (null == fromRow) return; toRow.setHeight(fromRow.getHeight()); toRow.setHeightInPoints(fromRow.getHeightInPoints()); int lastCellNum = fromRow.getLastCellNum(); lastCellNum = lastCellNum > 255 ? 255 : lastCellNum; for (int i = fromRow.getFirstCellNum(); i <= lastCellNum && i >= 0; i++) { Cell fromCell = getCell(fromRow, i); Cell toCell = getCell(toRow, i); // toCell.setEncoding(fromCell.getEncoding()); toCell.setCellStyle(fromCell.getCellStyle()); toCell.setCellType(fromCell.getCellType()); switch (fromCell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: toCell.setCellValue(fromCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: toCell.setCellFormula(fromCell.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: toCell.setCellValue(fromCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: toCell.setCellValue(fromCell.getStringCellValue()); break; default: } } } // copy merged region List shiftedRegions = new ArrayList(); for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress r = sheet.getMergedRegion(i); if (r.getFirstRow() >= from && r.getLastRow() < from + count) { CellRangeAddress n_r = new CellRangeAddress(r.getFirstRow() + to - from, r.getLastRow() + to - from, r.getFirstColumn(), r.getLastColumn()); shiftedRegions.add(n_r); } } // readd so it doesn't get shifted again Iterator iterator = shiftedRegions.iterator(); while (iterator.hasNext()) { CellRangeAddress region = (CellRangeAddress) iterator.next(); sheet.addMergedRegion(region); } }
From source file:net.sf.excelutils.WorkbookUtils.java
License:Apache License
public static void shiftCell(Sheet sheet, Row row, Cell beginCell, int shift, int rowCount) { if (shift == 0) return;//from w ww.j a va2 s. co m // get the from & to row int fromRow = row.getRowNum(); int toRow = row.getRowNum() + rowCount - 1; for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress r = sheet.getMergedRegion(i); if (r.getFirstRow() == row.getRowNum()) { if (r.getLastRow() > toRow) { toRow = r.getLastRow(); } if (r.getFirstRow() < fromRow) { fromRow = r.getFirstRow(); } } } for (int rownum = fromRow; rownum <= toRow; rownum++) { Row curRow = WorkbookUtils.getRow(rownum, sheet); int lastCellNum = curRow.getLastCellNum(); for (int cellpos = lastCellNum; cellpos >= beginCell.getColumnIndex(); cellpos--) { Cell fromCell = WorkbookUtils.getCell(curRow, cellpos); Cell toCell = WorkbookUtils.getCell(curRow, cellpos + shift); toCell.setCellType(fromCell.getCellType()); toCell.setCellStyle(fromCell.getCellStyle()); switch (fromCell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: toCell.setCellValue(fromCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: toCell.setCellFormula(fromCell.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: toCell.setCellValue(fromCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: toCell.setCellValue(fromCell.getStringCellValue()); break; case Cell.CELL_TYPE_ERROR: toCell.setCellErrorValue(fromCell.getErrorCellValue()); break; } fromCell.setCellValue(""); fromCell.setCellType(Cell.CELL_TYPE_BLANK); // Workbook wb = new Workbook(); // CellStyle style = wb.createCellStyle(); // fromCell.setCellStyle(style); } // process merged region for (int cellpos = lastCellNum; cellpos >= beginCell.getColumnIndex(); cellpos--) { Cell fromCell = WorkbookUtils.getCell(curRow, cellpos); List shiftedRegions = new ArrayList(); for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress r = sheet.getMergedRegion(i); if (r.getFirstRow() == curRow.getRowNum() && r.getFirstColumn() == fromCell.getColumnIndex()) { r.setFirstColumn((short) (r.getFirstColumn() + shift)); r.setLastColumn((short) (r.getLastColumn() + shift)); // have to remove/add it back shiftedRegions.add(r); sheet.removeMergedRegion(i); // we have to back up now since we removed one i = i - 1; } } // readd so it doesn't get shifted again Iterator iterator = shiftedRegions.iterator(); while (iterator.hasNext()) { CellRangeAddress region = (CellRangeAddress) iterator.next(); sheet.addMergedRegion(region); } } } }
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 av a 2s . c om*/ 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 ww.j a va 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
/** * ??????????????/*from w ww .j a va 2 s. 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; } }