List of usage examples for org.apache.poi.ss.usermodel Sheet removeRow
void removeRow(Row row);
From source file:org.bbreak.excella.reports.listener.RemoveAdapter.java
License:Open Source License
/** * ?// www . j av a 2s . com * * @param sheet * @param rowIndex */ private void removeControlRow(Sheet sheet, int rowIndex) { // ???? if (rowIndex == sheet.getLastRowNum()) { sheet.removeRow(sheet.getRow(rowIndex)); } else { sheet.removeRow(sheet.getRow(rowIndex)); // #35 POI??????????????????? // ??????0???????? CellRangeAddress rangeAddress = new CellRangeAddress(rowIndex, rowIndex, 0, PoiUtil.getLastColNum(sheet)); PoiUtil.deleteRangeUp(sheet, rangeAddress); // sheet.shiftRows( rowIndex + 1, sheet.getLastRowNum(), -1, true, true); } }
From source file:org.datanucleus.store.excel.ExcelPersistenceHandler.java
License:Open Source License
/** * Deletes a persistent object from the database. * @param op The Object Provider of the object to be deleted. * @throws NucleusDataStoreException when an error occurs in the datastore communication * @throws NucleusOptimisticException thrown if version checking fails on an optimistic transaction for this object */// w w w. jav a 2s. c o m public void deleteObject(ObjectProvider op) { // Check if read-only so update not permitted assertReadOnlyForUpdateOfObject(op); ExecutionContext ec = op.getExecutionContext(); ManagedConnection mconn = storeMgr.getConnection(ec); try { AbstractClassMetaData cmd = op.getClassMetaData(); if (cmd.isVersioned()) { NucleusLogger.PERSISTENCE.warn( "This datastore doesn't support optimistic version checks since the datastore file is for a single-connection"); } Workbook wb = (Workbook) mconn.getConnection(); Table table = ec.getStoreManager().getStoreDataForClass(op.getClassMetaData().getFullClassName()) .getTable(); final Sheet sheet = ExcelUtils.getSheetForClass(op, wb, table); // Invoke any cascade deletion op.loadUnloadedFields(); op.provideFields(cmd.getAllMemberPositions(), new DeleteFieldManager(op)); // Delete this object long startTime = System.currentTimeMillis(); if (NucleusLogger.DATASTORE_PERSIST.isDebugEnabled()) { NucleusLogger.DATASTORE_PERSIST.debug( Localiser.msg("Excel.Delete.Start", op.getObjectAsPrintable(), op.getInternalObjectId())); } int rowId = ExcelUtils.getRowNumberForObjectInWorkbook(op, wb, false, table); if (rowId < 0) { throw new NucleusObjectNotFoundException("object not found", op.getObject()); } if (storeMgr instanceof XLSStoreManager && sheet.getLastRowNum() == rowId) { // Deleting top row which is last row so just remove all cells and leave row // otherwise Apache POI throws an ArrayIndexOutOfBoundsException Row row = sheet.getRow(rowId); Iterator<Cell> it = row.cellIterator(); while (it.hasNext()) { row.removeCell(it.next()); } } else { // Deleting top row so remove it sheet.removeRow(sheet.getRow(rowId)); if (sheet.getLastRowNum() > rowId) { sheet.shiftRows(rowId + 1, sheet.getLastRowNum(), -1); } } if (NucleusLogger.DATASTORE_PERSIST.isDebugEnabled()) { NucleusLogger.DATASTORE_PERSIST .debug(Localiser.msg("Excel.ExecutionTime", (System.currentTimeMillis() - startTime))); } if (ec.getStatistics() != null) { ec.getStatistics().incrementNumWrites(); ec.getStatistics().incrementDeleteCount(); } } finally { mconn.release(); } }
From source file:org.mifos.dmt.excel.cleanup.PurgeEmptyRows.java
License:Open Source License
private void cleanUpSheet(Sheet sheet, int targetRow, int lastRowNum) { logger.info(("sheet " + sheet.getSheetName() + " processed with " + targetRow + " rows.")); for (int i = targetRow; i <= lastRowNum; i++) { Row row = sheet.getRow(i);// w ww.ja v a2 s .c o m if (row == null) continue; sheet.removeRow(row); } }
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 2 s . c o 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.pentaho.di.trans.steps.excelwriter.ExcelWriterStep.java
License:Apache License
public void prepareNextOutputFile() throws KettleException { try {/*from ww w. j ava 2s .c om*/ // sheet name shouldn't exceed 31 character if (data.realSheetname != null && data.realSheetname.length() > 31) { throw new KettleException( BaseMessages.getString(PKG, "ExcelWriterStep.Exception.MaxSheetName", data.realSheetname)); } // clear style cache int numOfFields = meta.getOutputFields() != null && meta.getOutputFields().length > 0 ? meta.getOutputFields().length : 0; if (numOfFields == 0) { numOfFields = data.inputRowMeta != null ? data.inputRowMeta.size() : 0; } data.clearStyleCache(numOfFields); // build new filename String buildFilename = buildFilename(data.splitnr); data.file = KettleVFS.getFileObject(buildFilename, getTransMeta()); if (log.isDebug()) { logDebug(BaseMessages.getString(PKG, "ExcelWriterStep.Log.OpeningFile", buildFilename)); } // determine whether existing file must be deleted if (data.file.exists() && data.createNewFile) { if (!data.file.delete()) { if (log.isBasic()) { logBasic(BaseMessages.getString(PKG, "ExcelWriterStep.Log.CouldNotDeleteStaleFile", buildFilename)); } setErrors(1); throw new KettleException("Could not delete stale file " + buildFilename); } } // adding filename to result if (meta.isAddToResultFiles()) { // Add this to the result file names... ResultFile resultFile = new ResultFile(ResultFile.FILE_TYPE_GENERAL, data.file, getTransMeta().getName(), getStepname()); resultFile .setComment("This file was created with an Excel writer step by Pentaho Data Integration"); addResultFile(resultFile); } boolean appendingToSheet = true; // if now no file exists we must create it as indicated by user if (!data.file.exists()) { // if template file is enabled if (meta.isTemplateEnabled()) { // handle template case (must have same format) // ensure extensions match String templateExt = KettleVFS.getFileObject(data.realTemplateFileName).getName() .getExtension(); if (!meta.getExtension().equalsIgnoreCase(templateExt)) { throw new KettleException("Template Format Mismatch: Template has extension: " + templateExt + ", but output file has extension: " + meta.getExtension() + ". Template and output file must share the same format!"); } if (KettleVFS.getFileObject(data.realTemplateFileName).exists()) { // if the template exists just copy the template in place copyFile(KettleVFS.getFileObject(data.realTemplateFileName, getTransMeta()), data.file); } else { // template is missing, log it and get out if (log.isBasic()) { logBasic(BaseMessages.getString(PKG, "ExcelWriterStep.Log.TemplateMissing", data.realTemplateFileName)); } setErrors(1); throw new KettleException("Template file missing: " + data.realTemplateFileName); } } else { // handle fresh file case, just create a fresh workbook Workbook wb = meta.getExtension().equalsIgnoreCase("xlsx") ? new XSSFWorkbook() : new HSSFWorkbook(); BufferedOutputStreamWithCloseDetection out = new BufferedOutputStreamWithCloseDetection( KettleVFS.getOutputStream(data.file, false)); wb.createSheet(data.realSheetname); wb.write(out); out.close(); wb.close(); } appendingToSheet = false; } // file is guaranteed to be in place now if (meta.getExtension().equalsIgnoreCase("xlsx")) { XSSFWorkbook xssfWorkbook = new XSSFWorkbook(KettleVFS.getInputStream(data.file)); if (meta.isStreamingData() && !meta.isTemplateEnabled()) { data.wb = new SXSSFWorkbook(xssfWorkbook, 100); } else { //Initialize it later after writing header/template because SXSSFWorkbook can't read/rewrite existing data, // only append. data.wb = xssfWorkbook; } } else { data.wb = new HSSFWorkbook(KettleVFS.getInputStream(data.file)); } int existingActiveSheetIndex = data.wb.getActiveSheetIndex(); int replacingSheetAt = -1; if (data.wb.getSheet(data.realSheetname) != null) { // sheet exists, replace or reuse as indicated by user if (data.createNewSheet) { replacingSheetAt = data.wb.getSheetIndex(data.wb.getSheet(data.realSheetname)); data.wb.removeSheetAt(replacingSheetAt); } } // if sheet is now missing, we need to create a new one if (data.wb.getSheet(data.realSheetname) == null) { if (meta.isTemplateSheetEnabled()) { Sheet ts = data.wb.getSheet(data.realTemplateSheetName); // if template sheet is missing, break if (ts == null) { throw new KettleException(BaseMessages.getString(PKG, "ExcelWriterStep.Exception.TemplateNotFound", data.realTemplateSheetName)); } data.sheet = data.wb.cloneSheet(data.wb.getSheetIndex(ts)); data.wb.setSheetName(data.wb.getSheetIndex(data.sheet), data.realSheetname); // unhide sheet in case it was hidden data.wb.setSheetHidden(data.wb.getSheetIndex(data.sheet), false); if (meta.isTemplateSheetHidden()) { data.wb.setSheetHidden(data.wb.getSheetIndex(ts), true); } } else { // no template to use, simply create a new sheet data.sheet = data.wb.createSheet(data.realSheetname); } if (replacingSheetAt > -1) { data.wb.setSheetOrder(data.sheet.getSheetName(), replacingSheetAt); } // preserves active sheet selection in workbook data.wb.setActiveSheet(existingActiveSheetIndex); data.wb.setSelectedTab(existingActiveSheetIndex); appendingToSheet = false; } else { // sheet is there and should be reused data.sheet = data.wb.getSheet(data.realSheetname); } // if use chose to make the current sheet active, do so if (meta.isMakeSheetActive()) { int sheetIndex = data.wb.getSheetIndex(data.sheet); data.wb.setActiveSheet(sheetIndex); data.wb.setSelectedTab(sheetIndex); } // handle write protection if (meta.isSheetProtected()) { protectSheet(data.sheet, data.realPassword); } // starting cell support data.startingRow = 0; data.startingCol = 0; if (!Utils.isEmpty(data.realStartingCell)) { CellReference cellRef = new CellReference(data.realStartingCell); data.startingRow = cellRef.getRow(); data.startingCol = cellRef.getCol(); } data.posX = data.startingCol; data.posY = data.startingRow; // Find last row and append accordingly if (!data.createNewSheet && meta.isAppendLines() && appendingToSheet) { data.posY = 0; if (data.sheet.getPhysicalNumberOfRows() > 0) { data.posY = data.sheet.getLastRowNum(); data.posY++; } } // offset by configured value // Find last row and append accordingly if (!data.createNewSheet && meta.getAppendOffset() != 0 && appendingToSheet) { data.posY += meta.getAppendOffset(); } // may have to write a few empty lines if (!data.createNewSheet && meta.getAppendEmpty() > 0 && appendingToSheet) { for (int i = 0; i < meta.getAppendEmpty(); i++) { openLine(); if (!data.shiftExistingCells || meta.isAppendLines()) { data.posY++; } } } // may have to write a header here if (meta.isHeaderEnabled() && !(!data.createNewSheet && meta.isAppendOmitHeader() && appendingToSheet)) { writeHeader(); } if (meta.isStreamingData() && meta.isTemplateEnabled()) { Sheet templateSheet = ((XSSFWorkbook) data.wb).getSheet(data.realSheetname); int currentRowNum = templateSheet.getLastRowNum(); SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook((XSSFWorkbook) data.wb, 100); Sheet aNewSheet = sxssfWorkbook.getSheet(data.realSheetname); int aNewSheetRowCount = aNewSheet.getLastRowNum(); while (currentRowNum > aNewSheetRowCount) { templateSheet.removeRow(templateSheet.getRow(currentRowNum)); currentRowNum--; } data.wb = sxssfWorkbook; data.sheet = sxssfWorkbook.getSheet(data.realSheetname); } if (log.isDebug()) { logDebug(BaseMessages.getString(PKG, "ExcelWriterStep.Log.FileOpened", buildFilename)); } // this is the number of the new output file data.splitnr++; } catch (Exception e) { logError("Error opening new file", e); setErrors(1); throw new KettleException(e); } }
From source file:org.seasar.fisshplate.core.element.Root.java
License:Apache License
private void removeUnwantedRows(FPContext context) { ///* w ww . j a v a2s .com*/ Sheet outSheet = context.getOutSheet(); int currentRowNum = context.getCurrentRowNum(); int lastRowNum = outSheet.getLastRowNum(); for (int i = currentRowNum; i <= lastRowNum; i++) { outSheet.removeRow(outSheet.getRow(i)); } }
From source file:org.squashtest.tm.service.internal.batchexport.ExcelExporter.java
License:Open Source License
private void appendTestCases(ExportModel model) { List<TestCaseModel> models = model.getTestCases(); Sheet tcSheet = workbook.getSheet(TC_SHEET); Row r;//from www . j a v a 2 s . co m int rIdx = tcSheet.getLastRowNum() + 1; int cIdx = 0; for (TestCaseModel tcm : models) { r = tcSheet.createRow(rIdx); try { r.createCell(cIdx++).setCellValue(tcm.getProjectId()); r.createCell(cIdx++).setCellValue(tcm.getProjectName()); r.createCell(cIdx++).setCellValue(tcm.getPath()); r.createCell(cIdx++).setCellValue(tcm.getOrder()); r.createCell(cIdx++).setCellValue(tcm.getId()); r.createCell(cIdx++).setCellValue(tcm.getReference()); r.createCell(cIdx++).setCellValue(tcm.getName()); if (milestonesEnabled) { r.createCell(cIdx++).setCellValue(tcm.getMilestone()); } r.createCell(cIdx++).setCellValue(tcm.getWeightAuto()); r.createCell(cIdx++).setCellValue(tcm.getWeight().toString()); r.createCell(cIdx++).setCellValue(tcm.getNature().getCode()); r.createCell(cIdx++).setCellValue(tcm.getType().getCode()); r.createCell(cIdx++).setCellValue(tcm.getStatus().toString()); r.createCell(cIdx++).setCellValue(tcm.getDescription()); r.createCell(cIdx++).setCellValue(tcm.getPrerequisite()); r.createCell(cIdx++).setCellValue(tcm.getNbReq()); r.createCell(cIdx++).setCellValue(tcm.getNbCaller()); r.createCell(cIdx++).setCellValue(tcm.getNbAttachments()); r.createCell(cIdx++).setCellValue(format(tcm.getCreatedOn())); r.createCell(cIdx++).setCellValue(tcm.getCreatedBy()); r.createCell(cIdx++).setCellValue(format(tcm.getLastModifiedOn())); r.createCell(cIdx++).setCellValue(tcm.getLastModifiedBy()); appendCustomFields(r, "TC_CUF_", tcm.getCufs()); cIdx = doOptionnalAppendTestCases(r, cIdx, tcm); } catch (IllegalArgumentException wtf) { if (LOGGER.isWarnEnabled()) { LOGGER.warn("cannot export content for test case '" + tcm.getId() + "' : some data exceed the maximum size of an excel cell"); } if (LOGGER.isTraceEnabled()) { LOGGER.trace("", wtf); } tcSheet.removeRow(r); r = tcSheet.createRow(rIdx); r.createCell(0).setCellValue(errorCellTooLargeMessage); } rIdx++; cIdx = 0; } }
From source file:org.squashtest.tm.service.internal.batchexport.ExcelExporter.java
License:Open Source License
private void appendTestSteps(ExportModel model) { List<TestStepModel> models = model.getTestSteps(); Sheet stSheet = workbook.getSheet(ST_SHEET); Row r;//from www . ja v a 2s. co m int rIdx = stSheet.getLastRowNum() + 1; int cIdx = 0; for (TestStepModel tsm : models) { r = stSheet.createRow(rIdx); try { r.createCell(cIdx++).setCellValue(tsm.getTcOwnerPath()); r.createCell(cIdx++).setCellValue(tsm.getTcOwnerId()); r.createCell(cIdx++).setCellValue(tsm.getId()); r.createCell(cIdx++).setCellValue(tsm.getOrder()); r.createCell(cIdx++).setCellValue(tsm.getIsCallStep()); r.createCell(cIdx++).setCellValue(tsm.getDsName()); r.createCell(cIdx++).setCellValue(tsm.getAction()); r.createCell(cIdx++).setCellValue(tsm.getResult()); r.createCell(cIdx++).setCellValue(tsm.getNbReq()); r.createCell(cIdx++).setCellValue(tsm.getNbAttach()); appendCustomFields(r, "TC_STEP_CUF_", tsm.getCufs()); } catch (IllegalArgumentException wtf) { if (LOGGER.isWarnEnabled()) { LOGGER.warn("cannot export content for test step '" + tsm.getId() + "' : some data exceed the maximum size of an excel cell"); } if (LOGGER.isTraceEnabled()) { LOGGER.trace("", wtf); } stSheet.removeRow(r); r = stSheet.createRow(rIdx); r.createCell(0).setCellValue(errorCellTooLargeMessage); } rIdx++; cIdx = 0; } }
From source file:org.squashtest.tm.service.internal.batchexport.ExcelExporter.java
License:Open Source License
private void appendParameters(ExportModel model) { List<ParameterModel> models = model.getParameters(); Sheet pSheet = workbook.getSheet(PRM_SHEET); Row r;//w ww. ja va2 s.c om int rIdx = pSheet.getLastRowNum() + 1; int cIdx = 0; for (ParameterModel pm : models) { r = pSheet.createRow(rIdx); try { r.createCell(cIdx++).setCellValue(pm.getTcOwnerPath()); r.createCell(cIdx++).setCellValue(pm.getTcOwnerId()); r.createCell(cIdx++).setCellValue(pm.getId()); r.createCell(cIdx++).setCellValue(pm.getName()); r.createCell(cIdx++).setCellValue(pm.getDescription()); } catch (IllegalArgumentException wtf) { if (LOGGER.isWarnEnabled()) { LOGGER.warn("cannot export content for parameter '" + pm.getId() + "' : some data exceed the maximum size of an excel cell"); } if (LOGGER.isTraceEnabled()) { LOGGER.trace("", wtf); } pSheet.removeRow(r); r = pSheet.createRow(rIdx); r.createCell(0).setCellValue(errorCellTooLargeMessage); } rIdx++; cIdx = 0; } }
From source file:org.squashtest.tm.service.internal.batchexport.ExcelExporter.java
License:Open Source License
private void appendDatasets(ExportModel model) { List<DatasetModel> models = model.getDatasets(); Sheet dsSheet = workbook.getSheet(DS_SHEET); Row r;// w ww. j av a2 s.c o m int rIdx = dsSheet.getLastRowNum() + 1; int cIdx = 0; for (DatasetModel dm : models) { r = dsSheet.createRow(rIdx); try { r.createCell(cIdx++).setCellValue(dm.getTcOwnerPath()); r.createCell(cIdx++).setCellValue(dm.getOwnerId()); r.createCell(cIdx++).setCellValue(dm.getId()); r.createCell(cIdx++).setCellValue(dm.getName()); r.createCell(cIdx++).setCellValue(dm.getParamOwnerPath()); r.createCell(cIdx++).setCellValue(dm.getParamOwnerId()); r.createCell(cIdx++).setCellValue(dm.getParamName()); r.createCell(cIdx++).setCellValue(dm.getParamValue()); } catch (IllegalArgumentException wtf) { if (LOGGER.isWarnEnabled()) { LOGGER.warn("cannot export content for dataset '" + dm.getId() + "' : some data exceed the maximum size of an excel cell"); } if (LOGGER.isTraceEnabled()) { LOGGER.trace("", wtf); } dsSheet.removeRow(r); r = dsSheet.createRow(rIdx); r.createCell(0).setCellValue(errorCellTooLargeMessage); } rIdx++; cIdx = 0; } }