List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet getNumMergedRegions
@Override public int getNumMergedRegions()
From source file:com.centurylink.mdw.drools.Excel2007Parser.java
License:Apache License
@SuppressWarnings("deprecation") private void processSheet(XSSFSheet sheet, List<? extends DataListener> listeners) { int mergedRegionCount = sheet.getNumMergedRegions(); CellRangeAddress[] mergedRanges = new CellRangeAddress[mergedRegionCount]; for (int i = 0; i < mergedRegionCount; i++) { mergedRanges[i] = sheet.getMergedRegion(i); }//ww w .j a v a2 s . com for (int i = 0; i <= sheet.getLastRowNum(); i++) { XSSFRow row = sheet.getRow(i); if (row != null) { newRow(listeners, i, row.getLastCellNum()); for (int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++) { XSSFCell cell = row.getCell(cellNum); if (cell != null) { CellRangeAddress merged = getRangeIfMerged(cell, mergedRanges); if (merged != null) { XSSFRow topRow = sheet.getRow(merged.getFirstRow()); XSSFCell topLeft = topRow.getCell(merged.getFirstColumn()); String cellValue = topLeft.getCellType() == CellType.NUMERIC.getCode() ? String.valueOf(topLeft.getNumericCellValue()) : topLeft.getStringCellValue(); newCell(listeners, i, cellNum, cellValue, topLeft.getColumnIndex()); } else { String cellValue = null; if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) cellValue = String.valueOf(cell.getBooleanCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) cellValue = String.valueOf(cell.getNumericCellValue()); else cellValue = cell.getStringCellValue(); newCell(listeners, i, cellNum, cellValue, DataListener.NON_MERGED); } } } } } finishSheet(listeners); }
From source file:com.centurylink.mdw.workflow.drools.Excel2007Parser.java
License:Apache License
private void processSheet(XSSFSheet sheet, List<? extends DataListener> listeners) { int mergedRegionCount = sheet.getNumMergedRegions(); CellRangeAddress[] mergedRanges = new CellRangeAddress[mergedRegionCount]; for (int i = 0; i < mergedRegionCount; i++) { mergedRanges[i] = sheet.getMergedRegion(i); }// www.j a va 2 s . com for (int i = 0; i <= sheet.getLastRowNum(); i++) { XSSFRow row = sheet.getRow(i); if (row != null) { newRow(listeners, i, row.getLastCellNum()); for (int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++) { XSSFCell cell = row.getCell(cellNum); if (cell != null) { CellRangeAddress merged = getRangeIfMerged(cell, mergedRanges); if (merged != null) { XSSFRow topRow = sheet.getRow(merged.getFirstRow()); XSSFCell topLeft = topRow.getCell(merged.getFirstColumn()); newCell(listeners, i, cellNum, topLeft.getStringCellValue(), topLeft.getColumnIndex()); } else { String cellValue = null; if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) cellValue = String.valueOf(cell.getBooleanCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) cellValue = String.valueOf(cell.getNumericCellValue()); else cellValue = cell.getStringCellValue(); newCell(listeners, i, cellNum, cellValue, DataListener.NON_MERGED); } } } } } finishSheet(listeners); }
From source file:com.vodafone.poms.ii.helpers.ActivityLoader.java
public void readFile(FileUploadEvent event) { if (event.getFile() != null) { Activity activity = null;//from w ww . j av a 2 s .c om List<Activity> activities = new ArrayList<>(); try { String errors = ""; XSSFWorkbook myWorkBook = new XSSFWorkbook(event.getFile().getInputstream()); int numberOfSheets = myWorkBook.getNumberOfSheets(); XSSFSheet sheet = null; System.out.println(numberOfSheets); for (int i = 0; i < numberOfSheets; i++) { if (myWorkBook.getSheetAt(i).getSheetName().toLowerCase().contains("rack")) { sheet = myWorkBook.getSheetAt(i); break; } } if (sheet != null) { int numOfMergedRegions = sheet.getNumMergedRegions(); for (int i = 0; i < numOfMergedRegions; i++) { sheet.removeMergedRegion(0); } Iterator<Row> rowIterator = sheet.iterator(); if (rowIterator.hasNext()) { rowIterator.next(); } while (rowIterator.hasNext()) { Row row = rowIterator.next(); if (getCellValue(row.getCell(0)).length() > 0) { activity = new Activity(); Sites site = null; SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); if ((site = sitesController .getSites(getCellValue(row.getCell(0)).toUpperCase())) != null) { activity.setSite(site); // Site activity.setAsp( subContractorsController.getSubcontractors(getCellValue(row.getCell(1)))); // ASP if (activity.getAsp() == null) { errors += "Row:" + (row.getRowNum() + 1) + " contains unrecognized ASP: Current Value: " + getCellValue(row.getCell(1)) + "\n"; System.out.println("Row:" + (row.getRowNum() + 1) + " contains unrecognized ASP: Current Value: " + getCellValue(row.getCell(1))); continue; } activity.setArea(areaController.getArea(getCellValue(row.getCell(2)))); // Area if (activity.getArea() == null) { errors += "Row:" + (row.getRowNum() + 1) + " contains unrecognized Area: Current Value: " + getCellValue(row.getCell(2)) + "\n"; System.out.println("Row:" + (row.getRowNum() + 1) + " contains unrecognized Area: Current Value: " + getCellValue(row.getCell(2))); continue; } if (vendorOwnerController.getByName(getCellValue(row.getCell(3))) != null) { activity.setVendorOwner( vendorOwnerController.getByName(getCellValue(row.getCell(3)))); // Owner } else { errors += "Row:" + (row.getRowNum() + 1) + " contains unrecognized Vendor Owner: Current Value: " + getCellValue(row.getCell(3)) + "\n"; System.out.println("Row:" + (row.getRowNum() + 1) + " contains unrecognized Vendor Owner: Current Value: " + getCellValue(row.getCell(3))); continue; } activity.setClaimStatus( claimStatusController.getClaimStatus(getCellValue(row.getCell(4)))); // Claim activity.setApprovalStatus( approvalStatusController.getApprovalStatus(getCellValue(row.getCell(5)))); // Approval activity.setActivityType( domainNamesController.getDomainNames(getCellValue(row.getCell(6)))); // type if (activity.getActivityType() == null) { System.out.println("Row:" + (row.getRowNum() + 1) + " Activity Type is not recognized, Current Value: " + getCellValue(row.getCell(6))); errors += "Row:" + (row.getRowNum() + 1) + " Activity Type is not recognized, Current Value: " + getCellValue(row.getCell(6)) + "\n"; continue; } activity.setPhase(phasesController.getPhases(getCellValue(row.getCell(7)))); // phase if (row.getCell(8).getCellType() == XSSFCell.CELL_TYPE_NUMERIC) { activity.setActivityDate(getDateCellValue(row.getCell(8))); // date } else { System.out.println("Row:" + (row.getRowNum() + 1) + " Activity date is not recognized, Current Value: " + getCellValue(row.getCell(8))); errors += "Row:" + (row.getRowNum() + 1) + " Activity date is not recognized, Current Value: " + getCellValue(row.getCell(8)) + "\n"; continue; } activity.setActivityCode( activityCodeController.getActivityCode(getCellValue(row.getCell(9)))); // Activity Code activity.setAcDescription(getCellValue(row.getCell(10))); // Claim activity.setActivityDetails(getCellValue(row.getCell(11))); // Claim if (activity.getActivityDetails() == null) { System.out.println("Row:" + (row.getRowNum() + 1) + " Empty Activity Details/Merged Cells"); errors += "Row:" + (row.getRowNum() + 1) + " Empty Activity Details/Merged Cells" + "\n"; continue; } if (activity.getActivityDetails().length() == 0) { activity.setActivityDetails("Regular Activity"); } try { activity.setQty(Double.parseDouble( (getCellValue(row.getCell(12)) != null ? getCellValue(row.getCell(12)) : "0"))); // Qty } catch (NumberFormatException e) { activity.setQty(0); } activity.setAcVendorPrice(Float.parseFloat( (getCellValue(row.getCell(13)) != null ? getCellValue(row.getCell(13)) : "0"))); // vendor price activity.setTotalPriceVendor(Float.parseFloat( (getCellValue(row.getCell(14)) != null ? getCellValue(row.getCell(14)) : "0"))); // vendor price activity.setTotalPriceVendorTaxes(Float.parseFloat( (getCellValue(row.getCell(15)) != null ? getCellValue(row.getCell(15)) : "0"))); // vendor price activity.setAcSubcontractorPrice(Float.parseFloat( (getCellValue(row.getCell(16)) != null ? getCellValue(row.getCell(16)) : "0"))); // vendor price activity.setTotalPriceAsp(Float.parseFloat( (getCellValue(row.getCell(17)) != null ? getCellValue(row.getCell(17)) : "0"))); // vendor price activity.setTotalUm(Float.parseFloat( (getCellValue(row.getCell(18)) != null ? getCellValue(row.getCell(18)) : "0"))); // vendor price activity.setTotalUmPercent(Float.parseFloat( (getCellValue(row.getCell(19)) != null ? getCellValue(row.getCell(19)) : "0"))); // vendor price activity.setActivityComment(getCellValue(row.getCell(20))); // vendor price activity.setCorrelateTo(getCellValue(row.getCell(21))); // vendor price activity.setSysDate(new Date()); activity.setCreator(usersController.getLoggedInUser()); activity.setTaxes(13.0); activities.add(activity); } else { System.out.println("Row:" + (row.getRowNum() + 1) + " Activity Site is not recognized, Current Value: " + getCellValue(row.getCell(0))); errors += "Row:" + (row.getRowNum() + 1) + " Activity Site is not recognized, Current Value: " + getCellValue(row.getCell(0)) + "\n"; } } } } if (!testFile) { activityController.createMultiple(activities); } if (errors.length() == 0) { setErrors("No Errors Found"); } else { setErrors(errors); } } catch (FileNotFoundException ex) { Logger.getLogger(ActivityLoader.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(ActivityLoader.class.getName()).log(Level.SEVERE, null, ex); } catch (Exception e) { e.printStackTrace(); } finally { } } }
From source file:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceGenerator.java
License:Open Source License
private XSSFRow copyRow(XSSFWorkbook workbook, XSSFSheet worksheet, int sourceRowNum, int destinationRowNum) { XSSFRow sourceRow = worksheet.getRow(sourceRowNum); worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1, true, false); XSSFRow 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 XSSFCell oldCell = sourceRow.getCell(i); XSSFCell newCell = newRow.createCell(i); // If the old cell is null jump to next cell if (oldCell == null) { newCell = null;/* w w w . j a va 2 s.co m*/ continue; } // Copy style from old cell and apply to new cell XSSFCellStyle newCellStyle = workbook.createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); newCell.setCellStyle(newCellStyle); // Set the cell data type newCell.setCellType(oldCell.getCellType()); } // 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.getLastRow() - cellRangeAddress.getFirstRow())), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()); worksheet.addMergedRegion(newCellRangeAddress); } } newRow.setHeight(sourceRow.getHeight()); return newRow; }
From source file:org.apache.ofbiz.pricat.AbstractPricatParser.java
License:Apache License
public void writeCommentsToFile(XSSFWorkbook workbook, XSSFSheet sheet) { report.println();/*from w w w.j a va2s . c o m*/ report.print(UtilProperties.getMessage(resource, "WriteCommentsBackToExcel", locale), InterfaceReport.FORMAT_NOTE); FileOutputStream fos = null; XSSFCreationHelper factory = workbook.getCreationHelper(); XSSFFont boldFont = workbook.createFont(); boldFont.setFontName("Arial"); boldFont.setBold(true); boldFont.setCharSet(134); boldFont.setFontHeightInPoints((short) 9); XSSFFont plainFont = workbook.createFont(); plainFont.setFontName("Arial"); plainFont.setCharSet(134); plainFont.setFontHeightInPoints((short) 9); XSSFSheet errorSheet = null; if (errorMessages.keySet().size() > 0) { String errorSheetName = UtilDateTime.nowDateString("yyyy-MM-dd HHmm") + " Errors"; errorSheetName = WorkbookUtil.createSafeSheetName(errorSheetName); errorSheet = workbook.createSheet(errorSheetName); workbook.setSheetOrder(errorSheetName, 0); workbook.setActiveSheet(workbook.getSheetIndex(errorSheetName)); XSSFDrawing drawingPatriarch = errorSheet.getDrawingPatriarch(); if (drawingPatriarch == null) { drawingPatriarch = errorSheet.createDrawingPatriarch(); } for (int i = 0; i <= getHeaderRowNo(); i++) { XSSFRow newRow = errorSheet.createRow(i); XSSFRow row = sheet.getRow(i); newRow.setHeight(row.getHeight()); copyRow(row, newRow, factory, drawingPatriarch); } // copy merged regions for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress mergedRegion = sheet.getMergedRegion(i); if (mergedRegion.getFirstRow() < getHeaderRowNo()) { errorSheet.addMergedRegion(mergedRegion); } } // copy images List<XSSFPictureData> pics = workbook.getAllPictures(); List<XSSFShape> shapes = sheet.getDrawingPatriarch().getShapes(); for (int i = 0; i < shapes.size(); i++) { XSSFShape shape = shapes.get(i); XSSFAnchor anchor = shape.getAnchor(); if (shape instanceof XSSFPicture && anchor instanceof XSSFClientAnchor) { XSSFPicture pic = (XSSFPicture) shape; XSSFClientAnchor clientAnchor = (XSSFClientAnchor) anchor; if (clientAnchor.getRow1() < getHeaderRowNo()) { for (int j = 0; j < pics.size(); j++) { XSSFPictureData picture = pics.get(j); if (picture.getPackagePart().getPartName() .equals(pic.getPictureData().getPackagePart().getPartName())) { drawingPatriarch.createPicture(clientAnchor, j); } } } } } } try { // set comments in the original sheet XSSFDrawing patriarch = sheet.getDrawingPatriarch(); for (CellReference cell : errorMessages.keySet()) { if (cell != null && errorMessages.get(cell) != null) { XSSFComment comment = sheet.getCellComment(new CellAddress(cell.getRow(), cell.getCol())); boolean isNewComment = false; if (comment == null) { XSSFClientAnchor anchor = factory.createClientAnchor(); anchor.setDx1(100); anchor.setDx2(100); anchor.setDy1(100); anchor.setDy2(100); anchor.setCol1(cell.getCol()); anchor.setCol2(cell.getCol() + 4); anchor.setRow1(cell.getRow()); anchor.setRow2(cell.getRow() + 4); anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE); comment = patriarch.createCellComment(anchor); isNewComment = true; } XSSFRichTextString rts = factory.createRichTextString("OFBiz PriCat:\n"); rts.applyFont(boldFont); rts.append(errorMessages.get(cell), plainFont); comment.setString(rts); comment.setAuthor("Apache OFBiz PriCat"); if (isNewComment) { sheet.getRow(cell.getRow()).getCell(cell.getCol()).setCellComment(comment); OFBizPricatUtil.formatCommentShape(sheet, cell); } } } // set comments in the new error sheet XSSFDrawing errorPatriarch = errorSheet.getDrawingPatriarch(); int newRowNum = getHeaderRowNo() + 1; Map<Integer, Integer> rowMapping = new HashMap<Integer, Integer>(); for (CellReference cell : errorMessages.keySet()) { if (cell != null && errorMessages.get(cell) != null) { XSSFRow row = sheet.getRow(cell.getRow()); Integer rowNum = Integer.valueOf(row.getRowNum()); int errorRow = newRowNum; if (rowMapping.containsKey(rowNum)) { errorRow = rowMapping.get(rowNum).intValue(); } else { XSSFRow newRow = errorSheet.getRow(errorRow); if (newRow == null) { newRow = errorSheet.createRow(errorRow); } rowMapping.put(rowNum, Integer.valueOf(errorRow)); newRow.setHeight(row.getHeight()); copyRow(row, newRow, factory, errorPatriarch); newRowNum++; } } } // write to file if (sequenceNum > 0L) { File commentedExcel = FileUtil.getFile(tempFilesFolder + userLoginId + "/" + sequenceNum + ".xlsx"); fos = new FileOutputStream(commentedExcel); workbook.write(fos); } else { fos = new FileOutputStream(pricatFile); workbook.write(fos); } fos.flush(); fos.close(); workbook.close(); } catch (FileNotFoundException e) { report.println(e); Debug.logError(e, module); } catch (IOException e) { report.println(e); Debug.logError(e, module); } finally { if (fos != null) { try { fos.close(); } catch (IOException e) { Debug.logError(e, module); } } if (workbook != null) { try { workbook.close(); } catch (IOException e) { Debug.logError(e, module); } } } report.println(UtilProperties.getMessage(resource, "ok", locale), InterfaceReport.FORMAT_OK); report.println(); }
From source file:org.kuali.test.runner.output.PoiHelper.java
License:Educational Community License
private void copySheets(XSSFSheet newSheet, XSSFSheet sheet) { int maxColumnNum = 0; Map<Integer, XSSFCellStyle> styleMap = new HashMap<Integer, XSSFCellStyle>(); int mergedReqionsCount = sheet.getNumMergedRegions(); for (int i = 0; i < mergedReqionsCount; ++i) { newSheet.addMergedRegion(sheet.getMergedRegion(i)); }/*from w ww . j a va 2 s . c om*/ for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) { XSSFRow srcRow = sheet.getRow(i); XSSFRow destRow = newSheet.createRow(i); if (srcRow != null) { copyRow(srcRow, destRow, styleMap); if (srcRow.getLastCellNum() > maxColumnNum) { maxColumnNum = srcRow.getLastCellNum(); } } } for (int i = 0; i <= maxColumnNum; i++) { newSheet.setColumnWidth(i, sheet.getColumnWidth(i)); } }
From source file:sv.com.mined.sieni.controller.GestionNotasController.java
public StreamedContent getFilePlantilla() { filePlantilla = null;//from w ww .ja v a 2s . c om String ruthPath = null; try { if (this.getEvaluacionSubir() != null && this.getEvaluacionSubir().getIdEvaluacion() != null) { // Se crea el libro XSSFWorkbook libro = new XSSFWorkbook(); // Se crea una hoja dentro del libro XSSFSheet sheetD = libro.createSheet(); //Obtener lista de alumnos del curso List<SieniAlumno> alumnosEval = sieniAlumnoFacadeRemote .findAlumnosInscritos(this.getEvaluacionSubir().getIdCurso().getIdCurso()); //Leer datos y colocarlos en la hoja int f = 0; //Guardar datos en celda for (SieniAlumno alumno : alumnosEval) { // Se crea una fila dentro de la hoja XSSFRow fila = sheetD.createRow(f); f++; // Se crea las celdas dentro de la fila XSSFCell celdaCarnet = fila.createCell((short) 0); XSSFCell celdaAlumno = fila.createCell((short) 1); XSSFCell celdaNota = fila.createCell((short) 2); //Colocar valor en celda celdaCarnet.setCellValue(alumno.getAlCarnet()); celdaAlumno.setCellValue(alumno.getNombreCompleto()); celdaNota.setCellValue((double) 0.00); } //Encabezados desde plantilla InputStream stream = ((ServletContext) FacesContext.getCurrentInstance().getExternalContext() .getContext()).getResourceAsStream("/resources/templates/PlantillaAlumnosEval.xlsx"); StreamedContent plantillaXLS = new DefaultStreamedContent(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Plantilla.xlsx"); XSSFWorkbook plantilla = new XSSFWorkbook(plantillaXLS.getStream()); XSSFSheet sheetP = plantilla.getSheetAt(0); //Filas que ocupa el encabezado de plantilla int encabezado = 3; //Quitar encabezado y desplazar Datos sheetD.shiftRows(0, sheetD.getLastRowNum(), encabezado); //Copiar contenido de plantilla a la hoja del reporte int inicio = 0; for (int row = 0; row < encabezado; row++) { copyRow(sheetP, sheetD, row, inicio); inicio++; } //Combinar las columnas al igual que la plantilla for (int m = 0; m < sheetP.getNumMergedRegions(); m++) { CellRangeAddress cellRangeAddress = sheetP.getMergedRegion(m).copy(); sheetD.addMergedRegion(cellRangeAddress); } //Evaluacion XSSFCell celdaEval = sheetD.getRow(0).getCell(1); celdaEval.setCellValue(this.getEvaluacionSubir().getEvNombre()); // Se salva el libro. FileOutputStream elFichero = new FileOutputStream("ListaAlumnos.xlsx"); libro.write(elFichero); elFichero.close(); //Leer libro para descarga FileInputStream file = new FileInputStream(new File("ListaAlumnos.xlsx")); filePlantilla = new DefaultStreamedContent(file, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "AlumnosEval.xlsx"); } else { new ValidationPojo().printMsj("Seleccione una evaluacion", FacesMessage.SEVERITY_ERROR); } } catch (Exception exc) { new ValidationPojo().printMsj( "Ocurrio un error al descargar plantilla ... consulte con el administrador" + ruthPath, FacesMessage.SEVERITY_ERROR); } return filePlantilla; }
From source file:tan.jam.jsf.Shifting.java
public static void InsertRow(XSSFWorkbook workbook, XSSFSheet worksheet, int sourceRowNum, int destinationRowNum) { worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1); XSSFRow newRow = worksheet.getRow(destinationRowNum); XSSFRow sourceRow = worksheet.getRow(sourceRowNum); if (newRow != null) { worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1); } else {//from www . ja v a 2 s . c o m newRow = worksheet.createRow(destinationRowNum); } for (int i = 0; i < sourceRow.getLastCellNum(); i++) { // Grab a copy of the old/new cell XSSFCell oldCell = sourceRow.getCell(i); XSSFCell newCell = newRow.createCell(i); if (oldCell == null) { newCell = null; continue; } XSSFCellStyle newCellStyle = workbook.createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); ; newCell.setCellStyle(newCellStyle); if (oldCell.getCellComment() != null) { newCell.setCellComment(oldCell.getCellComment()); } if (oldCell.getHyperlink() != null) { newCell.setHyperlink(oldCell.getHyperlink()); } newCell.setCellType(oldCell.getCellType()); switch (oldCell.getCellType()) { case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK: newCell.setCellValue(oldCell.getStringCellValue()); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA: newCell.setCellFormula("+" + "F" + destinationRowNum + "*G" + destinationRowNum); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC: //newCell.setCellValue(oldCell.getNumericCellValue()); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING: newCell.setCellValue(""); break; } } 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.getLastRow() - cellRangeAddress.getFirstRow())), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()); worksheet.addMergedRegion(newCellRangeAddress); } } int inc = destinationRowNum + 1; worksheet.getRow(destinationRowNum).getCell(7).setCellFormula("+F" + inc + "*G" + inc); }