List of usage examples for org.apache.poi.xssf.usermodel XSSFCellStyle cloneStyleFrom
@Override public void cloneStyleFrom(CellStyle source)
From source file:com.appdynamics.jrbronet.projectplan.ExcelManager.java
private static void copyRow(XSSFWorkbook workbook, XSSFSheet sourceWorksheet, int sourceRowNum, XSSFSheet destinationWorksheet, int destinationRowNum) { // Get the source / new row XSSFRow newRow = destinationWorksheet.getRow(destinationRowNum); XSSFRow sourceRow = sourceWorksheet.getRow(sourceRowNum); // If the row exist in destination, push down all rows by 1 else create a new row if (newRow != null) { destinationWorksheet.shiftRows(destinationRowNum, destinationWorksheet.getLastRowNum(), 1); } else {/* ww w . j ava 2s . c o m*/ newRow = destinationWorksheet.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; continue; } // Copy style from old cell and apply to new cell XSSFCellStyle newCellStyle = workbook.createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); ; newCell.setCellStyle(newCellStyle); // If there is a cell comment, copy if (oldCell.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()); //newCell.setCellValue(oldCell.getRawValue()); //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.getLastRow() - cellRangeAddress.getFirstRow() )), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()); worksheet.addMergedRegion(newCellRangeAddress); } } */ }
From source file:com.hauldata.dbpa.file.book.XlsxTargetSheet.java
License:Apache License
/** * Translate styling to workbook CellStyle. * * @param stylesUsed tracks the styles that have been used in the workbook; it will be updated * @param fontsUsed tracks the fonts that have been used in the workbook; it may be updated * @param colorsUsed tracks the colors that have been used in the workbook; it may be updated *///w w w . j a v a 2 s.c o m public CellStyle getCellStyle(SXSSFWorkbook book, Map<StylesWithFormatting, XSSFCellStyle> stylesUsed, Map<FontStyles, XSSFFont> fontsUsed, Map<Integer, XSSFColor> colorsUsed) { XSSFCellStyle cellStyle = stylesUsed.get(this); if (cellStyle != null) { return cellStyle; } cellStyle = (XSSFCellStyle) book.createCellStyle(); cellStyle.cloneStyleFrom(book.getCellStyleAt(formatIndex)); if (styles.bottomBorder.style != null) { cellStyle.setBorderBottom(resolveBorderStyle(styles.bottomBorder)); } if (styles.leftBorder.style != null) { cellStyle.setBorderLeft(resolveBorderStyle(styles.leftBorder)); } if (styles.rightBorder.style != null) { cellStyle.setBorderRight(resolveBorderStyle(styles.rightBorder)); } if (styles.topBorder.style != null) { cellStyle.setBorderTop(resolveBorderStyle(styles.topBorder)); } if (styles.bottomBorder.color != null) { cellStyle.setBottomBorderColor(getColor(styles.bottomBorder.color, book, colorsUsed)); } if (styles.leftBorder.color != null) { cellStyle.setLeftBorderColor(getColor(styles.leftBorder.color, book, colorsUsed)); } if (styles.rightBorder.color != null) { cellStyle.setRightBorderColor(getColor(styles.rightBorder.color, book, colorsUsed)); } if (styles.topBorder.color != null) { cellStyle.setTopBorderColor(getColor(styles.topBorder.color, book, colorsUsed)); } if (styles.backgroundColor != null) { cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); cellStyle.setFillForegroundColor(getColor(styles.backgroundColor, book, colorsUsed)); } if (styles.textAlign != null) { cellStyle.setAlignment(styles.textAlign); } if (!styles.font.areDefault()) { cellStyle.setFont(getFont(styles.font, book, fontsUsed, colorsUsed)); } stylesUsed.put(this, cellStyle); return cellStyle; }
From source file:com.tikal.tallerWeb.servicio.reporte.cliente.AbstractSeccionXLS.java
License:Apache License
public void paintBorder(XSSFWorkbook wb, Sheet sheet, short borderType, BordeSeccion borde) { for (int i = borde.getUpperRow(); i <= borde.getLowerRow(); i++) { Row row = sheet.getRow(i);//from ww w. j av a 2 s . c o m for (int j = borde.getLeftColumn(); j <= borde.getRightColumn(); j++) { if (i == borde.getUpperRow() || i == borde.getLowerRow() || j == borde.getLeftColumn() || j == borde.getRightColumn()) { Cell cell = row.getCell(j); XSSFCellStyle actual = (XSSFCellStyle) cell.getCellStyle(); XSSFCellStyle nuevo = wb.createCellStyle(); nuevo.cloneStyleFrom(actual); if (i == borde.getUpperRow()) { nuevo.setBorderTop(borderType); nuevo.setTopBorderColor(IndexedColors.BLACK.getIndex()); } if (i == borde.getLowerRow()) { nuevo.setBorderBottom(borderType); nuevo.setBottomBorderColor(IndexedColors.BLACK.getIndex()); } if (j == borde.getLeftColumn()) { nuevo.setBorderLeft(borderType); nuevo.setLeftBorderColor(IndexedColors.BLACK.getIndex()); } if (j == borde.getRightColumn()) { nuevo.setBorderRight(borderType); nuevo.setRightBorderColor(IndexedColors.BLACK.getIndex()); } cell.setCellStyle(nuevo); } } } }
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;//from w w w . ja v a 2 s . c o 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:htmlparser.xls.XLSFile.java
public void createScoreTable(XSSFColor oddrow_color, XSSFColor title_bg_color, XSSFColor title_font_color, int highlight) { String sheetname = WorkbookUtil.createSafeSheetName(this.parser.getCompetitionName()); this.scoresheet = this.excelfile.createSheet(sheetname); CreationHelper createHelper = this.excelfile.getCreationHelper(); CellStyle cellStyle = this.excelfile.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cellStyle.setBorderBottom(CellStyle.BORDER_THIN); cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); cellStyle.setBorderLeft(CellStyle.BORDER_THIN); cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); cellStyle.setBorderRight(CellStyle.BORDER_THIN); cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex()); cellStyle.setBorderTop(CellStyle.BORDER_THIN); cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); int rows = 0; Row headline = this.scoresheet.createRow(rows); Cell cheadline = headline.createCell(0); cheadline.setCellValue(createHelper.createRichTextString(this.parser.getCompetitionName())); XSSFCellStyle customstyle = (XSSFCellStyle) this.excelfile.createCellStyle(); customstyle.cloneStyleFrom(cellStyle); XSSFFont fh = (XSSFFont) this.excelfile.createFont(); fh.setFontHeightInPoints((short) 16); fh.setBoldweight(Font.BOLDWEIGHT_BOLD); fh.setColor(title_bg_color);//from w ww . j a va 2 s . c o m customstyle.setFont(fh); cheadline.setCellStyle(customstyle); int length = this.parser.getTeams().get(0).getData().size(); CellRangeAddress headrow = new CellRangeAddress(rows, rows, 0, length); this.scoresheet.addMergedRegion(headrow); RegionUtil.setBorderBottom(CellStyle.BORDER_THIN, headrow, this.scoresheet, this.excelfile); RegionUtil.setBottomBorderColor(IndexedColors.BLACK.getIndex(), headrow, this.scoresheet, this.excelfile); RegionUtil.setBorderLeft(CellStyle.BORDER_THIN, headrow, this.scoresheet, this.excelfile); RegionUtil.setLeftBorderColor(IndexedColors.BLACK.getIndex(), headrow, this.scoresheet, this.excelfile); RegionUtil.setBorderRight(CellStyle.BORDER_THIN, headrow, this.scoresheet, this.excelfile); RegionUtil.setRightBorderColor(IndexedColors.BLACK.getIndex(), headrow, this.scoresheet, this.excelfile); RegionUtil.setBorderTop(CellStyle.BORDER_THIN, headrow, this.scoresheet, this.excelfile); RegionUtil.setTopBorderColor(IndexedColors.BLACK.getIndex(), headrow, this.scoresheet, this.excelfile); rows++; Row colNms = this.scoresheet.createRow(rows++); customstyle = (XSSFCellStyle) this.excelfile.createCellStyle(); customstyle.cloneStyleFrom(cellStyle); customstyle.setFillForegroundColor(title_bg_color); customstyle.setFillPattern(CellStyle.SOLID_FOREGROUND); XSSFFont f1 = (XSSFFont) this.excelfile.createFont(); f1.setColor(title_font_color); f1.setBoldweight(Font.BOLDWEIGHT_BOLD); customstyle.setFont(f1); int cCN = 0; for (String s : this.shColNms) { Cell c = colNms.createCell(cCN); c.setCellValue(createHelper.createRichTextString(s)); c.setCellStyle(customstyle); cCN++; } double ordNum = 1; customstyle = (XSSFCellStyle) this.excelfile.createCellStyle(); customstyle.cloneStyleFrom(cellStyle); customstyle.setFillForegroundColor(oddrow_color); customstyle.setFillPattern(CellStyle.SOLID_FOREGROUND); for (Team t : this.parser.getTeams()) { Row r = this.scoresheet.createRow(rows++); int cell = 0; Cell order = r.createCell(cell++); order.setCellValue(ordNum++); if (rows % 2 == 0) order.setCellStyle(customstyle); else order.setCellStyle(cellStyle); for (String s : t.getData()) { Cell c = r.createCell(cell); c.setCellValue(createHelper.createRichTextString(s)); if (rows % 2 == 0) c.setCellStyle(customstyle); else c.setCellStyle(cellStyle); cell++; } } for (int i = 0; i <= length; i++) { this.scoresheet.autoSizeColumn(i); } if (highlight >= 0) { highlight += 2; Row r = this.scoresheet.getRow(highlight); customstyle = (XSSFCellStyle) this.excelfile.createCellStyle(); customstyle.cloneStyleFrom(r.getCell(0).getCellStyle()); Font bold = this.excelfile.createFont(); bold.setBoldweight(Font.BOLDWEIGHT_BOLD); customstyle.setFont(bold); for (Cell c : r) { c.setCellStyle(customstyle); } } }
From source file:htmlparser.xls.XLSFile.java
public void createMatchTable(XSSFColor oddrow_color, XSSFColor title_bg_color, XSSFColor title_font_color) { String sheetname = WorkbookUtil.createSafeSheetName(this.parser.getTeamName()); this.matchsheet = this.excelfile.createSheet(sheetname); CreationHelper createHelper = this.excelfile.getCreationHelper(); CellStyle cellStyle = this.excelfile.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cellStyle.setBorderBottom(CellStyle.BORDER_THIN); cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); cellStyle.setBorderLeft(CellStyle.BORDER_THIN); cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); cellStyle.setBorderRight(CellStyle.BORDER_THIN); cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex()); cellStyle.setBorderTop(CellStyle.BORDER_THIN); cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); int rows = 0; Row headline = this.matchsheet.createRow(rows); Cell cheadline = headline.createCell(0); cheadline.setCellValue(createHelper.createRichTextString(this.parser.getTeamName())); XSSFCellStyle customstyle = (XSSFCellStyle) this.excelfile.createCellStyle(); customstyle.cloneStyleFrom(cellStyle); XSSFFont fh = (XSSFFont) this.excelfile.createFont(); fh.setFontHeightInPoints((short) 16); fh.setBoldweight(Font.BOLDWEIGHT_BOLD); fh.setColor(title_bg_color);//w w w .j a va2 s. c om customstyle.setFont(fh); cheadline.setCellStyle(customstyle); int length = this.parser.getMatches().get(0).getData().size(); CellRangeAddress headrow = new CellRangeAddress(rows, rows, 0, length - 1); this.matchsheet.addMergedRegion(headrow); RegionUtil.setBorderBottom(CellStyle.BORDER_THIN, headrow, this.matchsheet, this.excelfile); RegionUtil.setBottomBorderColor(IndexedColors.BLACK.getIndex(), headrow, this.matchsheet, this.excelfile); RegionUtil.setBorderLeft(CellStyle.BORDER_THIN, headrow, this.matchsheet, this.excelfile); RegionUtil.setLeftBorderColor(IndexedColors.BLACK.getIndex(), headrow, this.matchsheet, this.excelfile); RegionUtil.setBorderRight(CellStyle.BORDER_THIN, headrow, this.matchsheet, this.excelfile); RegionUtil.setRightBorderColor(IndexedColors.BLACK.getIndex(), headrow, this.matchsheet, this.excelfile); RegionUtil.setBorderTop(CellStyle.BORDER_THIN, headrow, this.matchsheet, this.excelfile); RegionUtil.setTopBorderColor(IndexedColors.BLACK.getIndex(), headrow, this.matchsheet, this.excelfile); rows++; Row colNms = this.matchsheet.createRow(rows++); customstyle = (XSSFCellStyle) this.excelfile.createCellStyle(); customstyle.cloneStyleFrom(cellStyle); customstyle.setFillForegroundColor(title_bg_color); customstyle.setFillPattern(CellStyle.SOLID_FOREGROUND); XSSFFont f1 = (XSSFFont) this.excelfile.createFont(); f1.setColor(title_font_color); f1.setBoldweight(Font.BOLDWEIGHT_BOLD); customstyle.setFont(f1); int cCN = 0; for (String s : this.mhColNms) { Cell c = colNms.createCell(cCN); c.setCellValue(createHelper.createRichTextString(s)); c.setCellStyle(customstyle); cCN++; } customstyle = (XSSFCellStyle) this.excelfile.createCellStyle(); customstyle.cloneStyleFrom(cellStyle); customstyle.setFillForegroundColor(oddrow_color); customstyle.setFillPattern(CellStyle.SOLID_FOREGROUND); for (Match t : this.parser.getMatches()) { Row r = this.matchsheet.createRow(rows++); int cell = 0; for (String s : t.getData()) { Cell c = r.createCell(cell); c.setCellValue(createHelper.createRichTextString(s)); if (rows % 2 == 0) c.setCellStyle(customstyle); else c.setCellStyle(cellStyle); cell++; } } for (int i = 0; i < length; i++) { this.matchsheet.autoSizeColumn(i); } }
From source file:Import.Utils.XSSFConvert.java
/** * @param oldCell/*from w ww . j a v a2 s . co m*/ * @param newCell * @param styleMap */ public static void copyCell(HSSFCell oldCell, XSSFCell newCell, Map<Integer, HSSFCellStyle> styleMap) { if (styleMap != null) { int stHashCode = oldCell.getCellStyle().hashCode(); HSSFCellStyle sourceCellStyle = styleMap.get(stHashCode); XSSFCellStyle destnCellStyle = newCell.getCellStyle(); if (sourceCellStyle == null) { sourceCellStyle = oldCell.getSheet().getWorkbook().createCellStyle(); } destnCellStyle.cloneStyleFrom(oldCell.getCellStyle()); styleMap.put(stHashCode, sourceCellStyle); newCell.setCellStyle(destnCellStyle); } switch (oldCell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getStringCellValue()); break; case HSSFCell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_BLANK: newCell.setCellType(HSSFCell.CELL_TYPE_BLANK); break; case HSSFCell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case HSSFCell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; default: break; } }
From source file:org.kuali.test.runner.output.PoiHelper.java
License:Educational Community License
private void copyCell(XSSFCell oldCell, XSSFCell newCell, Map<Integer, XSSFCellStyle> styleMap) { if (styleMap != null) { int stHashCode = oldCell.getCellStyle().hashCode(); XSSFCellStyle newCellStyle = styleMap.get(stHashCode); if (newCellStyle == null) { newCellStyle = newCell.getSheet().getWorkbook().createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); newCellStyle.setFont(oldCell.getCellStyle().getFont()); styleMap.put(stHashCode, newCellStyle); }/* www . j av a2s. c o m*/ newCell.setCellStyle(newCellStyle); } switch (oldCell.getCellType()) { case XSSFCell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getStringCellValue()); break; case XSSFCell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case XSSFCell.CELL_TYPE_BLANK: newCell.setCellType(HSSFCell.CELL_TYPE_BLANK); break; case XSSFCell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case XSSFCell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case XSSFCell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; default: break; } }
From source file:sv.com.mined.sieni.controller.GestionNotasController.java
public static void copyRow(XSSFSheet worksheetSource, XSSFSheet worksheetDestination, int sourceRowNum, int destinationRowNum) { // Get the source / new row XSSFRow origen = worksheetSource.getRow(sourceRowNum); XSSFRow destino = worksheetDestination.createRow(destinationRowNum); // Loop through source columns to add to new row for (int i = 0; i < origen.getLastCellNum(); i++) { // Grab a copy of the old/new cell XSSFCell oldCell = origen.getCell(i); XSSFCell newCell = destino.createCell(i); // If the old cell is null jump to next cell if (oldCell == null) { newCell = null;//from w w w . ja va 2s. c o m continue; } //Ajustar tamaos columnas worksheetDestination.setColumnWidth(i, worksheetSource.getColumnWidth(i)); // Copy style from old cell and apply to new cell XSSFCellStyle newCellStyle = newCell.getSheet().getWorkbook().createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); newCell.setCellStyle(newCellStyle); // If there is a cell comment, copy if (oldCell.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; } } }
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 {/* w w w .ja va2 s. com*/ 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); }