List of usage examples for org.apache.poi.ss.usermodel Sheet shiftRows
void shiftRows(int startRow, int endRow, int n, boolean copyRowHeight, boolean resetOriginalRowHeight);
From source file:cn.afterturn.easypoi.excel.export.template.ExcelExportOfTemplateUtil.java
License:Apache License
/** * Sheet ?,?? ,??/*from w ww . j a v a2 s .co m*/ * * @param sheet * @param pojoClass * @param dataSet * @param workbook */ private void addDataToSheet(Class<?> pojoClass, Collection<?> dataSet, Sheet sheet, Workbook workbook) throws Exception { // ?? Map<String, Integer> titlemap = getTitleMap(sheet); Drawing patriarch = PoiExcelGraphDataUtil.getDrawingPatriarch(sheet); // Field[] fileds = PoiPublicUtil.getClassFields(pojoClass); ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class); String targetId = null; if (etarget != null) { targetId = etarget.value(); } // ?? List<ExcelExportEntity> excelParams = new ArrayList<ExcelExportEntity>(); getAllExcelField(null, targetId, fileds, excelParams, pojoClass, null, null); // ?? sortAndFilterExportField(excelParams, titlemap); short rowHeight = getRowHeight(excelParams); int index = teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(), titleHeight = index; int shiftRows = getShiftRows(dataSet, excelParams); //?,? sheet.shiftRows(teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(), sheet.getLastRowNum(), shiftRows, true, true); mergedRegionHelper.shiftRows(sheet, teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(), shiftRows); templateSumHandler.shiftRows(teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(), shiftRows); PoiExcelTempUtil.reset(sheet, teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(), sheet.getLastRowNum()); if (excelParams.size() == 0) { return; } Iterator<?> its = dataSet.iterator(); while (its.hasNext()) { Object t = its.next(); index += createCells(patriarch, index, t, excelParams, sheet, workbook, rowHeight, 0)[0]; } // ?? mergeCells(sheet, excelParams, titleHeight); }
From source file:cn.bzvs.excel.export.template.ExcelExportOfTemplateUtil.java
License:Apache License
/** * Sheet ?,?? ,??/*from w w w . j av a2 s.c o m*/ * * @param sheet * @param pojoClass * @param dataSet * @param workbook */ private void addDataToSheet(Class<?> pojoClass, Collection<?> dataSet, Sheet sheet, Workbook workbook) throws Exception { if (workbook instanceof XSSFWorkbook) { super.type = ExcelType.XSSF; } // ?? Map<String, Integer> titlemap = getTitleMap(sheet); Drawing patriarch = sheet.createDrawingPatriarch(); // Field[] fileds = PoiPublicUtil.getClassFields(pojoClass); ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class); String targetId = null; if (etarget != null) { targetId = etarget.value(); } // ?? List<ExcelExportEntity> excelParams = new ArrayList<ExcelExportEntity>(); getAllExcelField(null, targetId, fileds, excelParams, pojoClass, null); // ?? sortAndFilterExportField(excelParams, titlemap); short rowHeight = getRowHeight(excelParams); int index = teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(), titleHeight = index; //?,? sheet.shiftRows(teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(), sheet.getLastRowNum(), getShiftRows(dataSet, excelParams), true, true); if (excelParams.size() == 0) { return; } Iterator<?> its = dataSet.iterator(); while (its.hasNext()) { Object t = its.next(); index += createCells(patriarch, index, t, excelParams, sheet, workbook, rowHeight); } // ?? mergeCells(sheet, excelParams, titleHeight); }
From source file:com.qihang.winter.poi.excel.export.template.ExcelExportOfTemplateUtil.java
License:Apache License
/** * Sheet ?,?? ,??/* www . j a va 2 s . c om*/ * * @param teplateParams * @param pojoClass * @param dataSet * @param workbook */ private void addDataToSheet(Class<?> pojoClass, Collection<?> dataSet, Sheet sheet, Workbook workbook) throws Exception { if (workbook instanceof XSSFWorkbook) { super.type = com.qihang.winter.poi.excel.entity.enmus.ExcelType.XSSF; } // ?? Map<String, Integer> titlemap = getTitleMap(sheet); Drawing patriarch = sheet.createDrawingPatriarch(); // Field[] fileds = com.qihang.winter.poi.util.PoiPublicUtil.getClassFields(pojoClass); com.qihang.winter.poi.excel.annotation.ExcelTarget etarget = pojoClass .getAnnotation(com.qihang.winter.poi.excel.annotation.ExcelTarget.class); String targetId = null; if (etarget != null) { targetId = etarget.value(); } // ?? List<com.qihang.winter.poi.excel.entity.params.ExcelExportEntity> excelParams = new ArrayList<com.qihang.winter.poi.excel.entity.params.ExcelExportEntity>(); getAllExcelField(null, targetId, fileds, excelParams, pojoClass, null); // ?? sortAndFilterExportField(excelParams, titlemap); short rowHeight = getRowHeight(excelParams); int index = teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(), titleHeight = index; //?,? sheet.shiftRows(teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(), sheet.getLastRowNum(), getShiftRows(dataSet, excelParams), true, true); if (excelParams.size() == 0) { return; } Iterator<?> its = dataSet.iterator(); while (its.hasNext()) { Object t = its.next(); index += createCells(patriarch, index, t, excelParams, sheet, workbook, rowHeight); } // ?? mergeCells(sheet, excelParams, titleHeight); }
From source file:com.vaadin.addon.spreadsheet.Spreadsheet.java
License:Open Source License
/** * Shifts rows between startRow and endRow n number of rows. If you use a * negative number for n, the rows will be shifted upwards. This method * ensures that rows can't wrap around./*from w w w . ja va 2 s . c o m*/ * <p> * If you are adding / deleting rows, you might want to change the number of * visible rows rendered {@link #getRows()} with {@link #setMaxRows(int)}. * <p> * See {@link Sheet#shiftRows(int, int, int, boolean, boolean)}. * * @param startRow * The first row to shift, 0-based * @param endRow * The last row to shift, 0-based * @param n * Number of rows to shift, positive numbers shift down, negative * numbers shift up. * @param copyRowHeight * True to copy the row height during the shift * @param resetOriginalRowHeight * True to set the original row's height to the default */ public void shiftRows(int startRow, int endRow, int n, boolean copyRowHeight, boolean resetOriginalRowHeight) { Sheet sheet = getActiveSheet(); int lastNonBlankRow = getLastNonBlankRow(sheet); sheet.shiftRows(startRow, endRow, n, copyRowHeight, resetOriginalRowHeight); // need to re-send the cell values to client // remove all cached cell data that is now empty getFormulaEvaluator().clearAllCachedResultValues(); int start = n < 0 ? Math.max(lastNonBlankRow, startRow) : startRow; int end = n < 0 ? endRow : startRow + n - 1; valueManager.updateDeletedRowsInClientCache(start + 1, end + 1); int firstAffectedRow = n < 0 ? startRow + n : startRow; int lastAffectedRow = n < 0 ? endRow : endRow + n; if (copyRowHeight || resetOriginalRowHeight) { // might need to increase the size of the row heights array int oldLength = getState(false).rowH.length; int neededLength = endRow + n + 1; if (n > 0 && oldLength < neededLength) { getState().rowH = Arrays.copyOf(getState().rowH, neededLength); } for (int i = firstAffectedRow; i <= lastAffectedRow; i++) { Row row = sheet.getRow(i); if (row != null) { if (row.getZeroHeight()) { getState().rowH[i] = 0f; } else { getState().rowH[i] = row.getHeightInPoints(); } } else { getState().rowH[i] = sheet.getDefaultRowHeightInPoints(); } } } if (hasSheetOverlays()) { reloadImageSizesFromPOI = true; } // need to shift the cell styles, clear and update // need to go -1 and +1 because of shifted borders.. final ArrayList<Cell> cellsToUpdate = new ArrayList<Cell>(); for (int r = (firstAffectedRow - 1); r <= (lastAffectedRow + 1); r++) { if (r < 0) { r = 0; } Row row = sheet.getRow(r); final Integer rowIndex = new Integer(r + 1); if (row == null) { valueManager.updateDeletedRowsInClientCache(rowIndex, rowIndex); if (getState(false).hiddenRowIndexes.contains(rowIndex)) { getState().hiddenRowIndexes.remove(rowIndex); } for (int c = 0; c < getState().cols; c++) { styler.clearCellStyle(r, c); } } else { if (row.getZeroHeight()) { getState().hiddenRowIndexes.add(rowIndex); } else if (getState(false).hiddenRowIndexes.contains(rowIndex)) { getState().hiddenRowIndexes.remove(rowIndex); } for (int c = 0; c < getState().cols; c++) { Cell cell = row.getCell(c); if (cell == null) { styler.clearCellStyle(r, c); if (r <= lastNonBlankRow + n) { // There might be a pre-shift value for this cell in // client-side and should be overwritten cell = row.createCell(c); cellsToUpdate.add(cell); } } else { cellsToUpdate.add(cell); } } } } rowsMoved(firstAffectedRow, lastAffectedRow, n); for (Cell cell : cellsToUpdate) { styler.cellStyleUpdated(cell, false); markCellAsUpdated(cell, false); } styler.loadCustomBorderStylesToState(); updateMarkedCells(); // deleted and formula cells and style selectors updateRowAndColumnRangeCellData(firstRow, firstColumn, lastRow, lastColumn); // shifted area values updateMergedRegions(); CellReference selectedCellReference = selectionManager.getSelectedCellReference(); if (selectedCellReference != null) { if (selectedCellReference.getRow() >= firstAffectedRow && selectedCellReference.getRow() <= lastAffectedRow) { selectionManager.onSheetAddressChanged(selectedCellReference.formatAsString(), false); } } }
From source file:generate.CopyRow.java
/** * Copies a row from a row index on the given workbook and sheet to another row index. If the destination row is * already occupied, shift all rows down to make room. * * @param workbook//from www . j a v a2 s . com * @param worksheet * @param from * @param to */ public static void copyRow(Workbook workbook, Sheet worksheet, Integer from, Integer to) { Row sourceRow = worksheet.getRow(from); Row newRow = worksheet.getRow(to); if (alreadyExists(newRow)) worksheet.shiftRows(to, worksheet.getLastRowNum(), 1, true, true); else { newRow = worksheet.createRow(to); } for (int i = 0; i < sourceRow.getLastCellNum(); i++) { Cell oldCell = sourceRow.getCell(i); Cell newCell = newRow.createCell(i); if (oldCell != null) { copyCellStyle(workbook, oldCell, newCell); copyCellComment(oldCell, newCell); copyCellHyperlink(oldCell, newCell); copyCellDataTypeAndValue(oldCell, newCell); } } copyAnyMergedRegions(worksheet, sourceRow, newRow); }
From source file:invoiceapplication.CopyRowOriginal.java
public static void copyRow(Sheet worksheet, int sourceRowNum, int destRowNum) { // Get the source / new row Row newRow = worksheet.getRow(destRowNum); Row sourceRow = worksheet.getRow(sourceRowNum); // If the row exists in destination, push down all rows by 1 else create a new row if (newRow != null) { worksheet.shiftRows(newRow.getRowNum(), worksheet.getLastRowNum(), 1, true, true); } else {/*from w w w .j av a 2 s. c om*/ newRow = worksheet.createRow(destRowNum); } copyAnyMergedRegions(worksheet, sourceRow, newRow); // Loops through source column to add to new row for (int i = 0; i < sourceRow.getLastCellNum(); i++) { //Grab a copy of the old/new cell Cell oldCell = sourceRow.getCell(i); Cell newCell = newRow.createCell(i); // if the old cell is null jump to next cell if (oldCell == null) { newCell = null; continue; } // Use old cell style newCell.setCellStyle(oldCell.getCellStyle()); // 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: break; case Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: newCell.setCellValue(oldCell.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getStringCellValue()); break; } } }
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 www . j a v a 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);/*from w ww .j a va 2 s . com*/ 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.PageTag.java
License:Apache License
public int[] parseTag(Object context, Workbook wb, Sheet sheet, Row curRow, Cell curCell) throws ExcelException { int rowNum = curRow.getRowNum(); LOG.debug("#page at rownum = " + rowNum); sheet.setRowBreak(rowNum - 1);/*from w w w . j a v a 2s .c om*/ sheet.removeRow(curRow); if (rowNum + 1 <= sheet.getLastRowNum()) { sheet.shiftRows(rowNum + 1, sheet.getLastRowNum(), -1, true, true); } return new int[] { 0, -1, 0 }; }
From source file:net.sf.excelutils.tags.SheetTag.java
License:Apache License
/** * Parse #sheet detail in list by sheetName *///from www. j a v a 2 s . co m 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 }; }