https://bz.apache.org/bugzilla/show_bug.cgi?id=57885
--- Comment #6 from Prejith Pulikkottil <[email protected]> --- As suggested, I wrote a code snippet for copy sheet functionality from one work book to another. See the code snippet and attached workbook After the copy functionality, my output file was corrupted and showed the error "Repaired Records: Format from /xl/styles.xml part (Styles)" When I look at the reason on different poi forums, I can see the that it is the issue with border style. And see the solution, unsetBorderId and unsetFillId After applying the unset functions data was successfully copied without border style In nutshell, I can see some issues with border styling the XSSF type files package eis.utils; import java.io.ByteArrayOutputStream; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.InputStream; import java.util.Date; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; import org.apache.poi.xssf.usermodel.XSSFCellStyle; public class XlsxExcelUtil { private static void removeRows(Sheet destSheet) { if (null != destSheet) { for (int i = destSheet.getFirstRowNum(); i <= destSheet.getLastRowNum(); i++) { Row row = destSheet.getRow(i); if (null != row) { destSheet.removeRow(row); } } } } private static void addRows(Sheet destSheet, int totalRowCount) { if (null != destSheet) { for (int i = 0; i <= totalRowCount; i++) { destSheet.createRow(i); } } } private static void copyMergedRegion(Sheet srcSheet, Sheet destSheet) { for (int i = 0; i < srcSheet.getNumMergedRegions(); i++) { destSheet.addMergedRegion(srcSheet.getMergedRegion(i)); } } private static void copyRow(Row srcRow, Row destRow) { destRow.setHeight(srcRow.getHeight()); for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) { Cell srcCell = srcRow.getCell(j); if (srcCell != null) { Cell destCell = destRow.createCell(j); copyCell(srcCell, destCell); } } } private static void copyCell(Cell srcCell, Cell destCell) { CellStyle cellStyleClazz = destCell.getSheet().getWorkbook() .createCellStyle(); if (cellStyleClazz instanceof XSSFCellStyle) { XSSFCellStyle newCellStyle = (XSSFCellStyle) cellStyleClazz; newCellStyle.cloneStyleFrom(srcCell.getCellStyle()); newCellStyle.setDataFormat(srcCell.getCellStyle().getDataFormat()); // Issue with border style, so added this code // newCellStyle.getCoreXf().unsetBorderId(); // newCellStyle.getCoreXf().unsetFillId(); // ------------- destCell.setCellStyle(newCellStyle); } if (srcCell.getCellType() == Cell.CELL_TYPE_BLANK) { destCell.setCellType(Cell.CELL_TYPE_BLANK); } else if (srcCell.getCellType() == Cell.CELL_TYPE_STRING) { destCell.setCellValue(srcCell.getStringCellValue()); } else if (srcCell.getCellType() == Cell.CELL_TYPE_NUMERIC) { destCell.setCellValue(srcCell.getNumericCellValue()); } else if (srcCell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { destCell.setCellValue(srcCell.getBooleanCellValue()); } else if (srcCell.getCellType() == Cell.CELL_TYPE_FORMULA) { destCell.setCellFormula(srcCell.getCellFormula()); } else if (srcCell.getCellType() == Cell.CELL_TYPE_ERROR) { destCell.setCellErrorValue(srcCell.getErrorCellValue()); } } /** * * Copy a sheet from one workbook to another workbook. The method supports * only two similar type of workbooks Xlsx type * * @param srcSheet * @param destSheet */ public static void copySheet(Sheet srcSheet, Sheet destSheet) { removeRows(destSheet); addRows(destSheet, srcSheet.getLastRowNum()); copyMergedRegion(srcSheet, destSheet); for (int i = srcSheet.getFirstRowNum(); i <= srcSheet.getLastRowNum(); i++) { Row srcRow = srcSheet.getRow(i); if (null == srcRow) { destSheet.createRow(i); } else { Row destRow = destSheet.createRow(i); copyRow(srcRow, destRow); } } } public void testXlsxSheetCopy() { try { File templateFile = new File("C:/TestXlsx/Template_V2.xlsx"); InputStream inputStream = new FileInputStream(templateFile); Workbook merWorkBook = WorkbookFactory.create(inputStream); inputStream.close(); Sheet destPdrSheet = merWorkBook.getSheet("PDR"); File pdrFile = new File("C:/TestXlsx/P23163.xlsx"); InputStream pdrInputStream = new FileInputStream(pdrFile); Workbook pdrWorkBook = WorkbookFactory.create(pdrInputStream); pdrInputStream.close(); Sheet srcPdrSheet = pdrWorkBook.getSheetAt(0); XlsxExcelUtil.copySheet(srcPdrSheet, destPdrSheet); ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream(); merWorkBook.setForceFormulaRecalculation(true); merWorkBook.write(byteArrayOutputStream); FileOutputStream resultFile = new FileOutputStream(new File( "C:/TestXlsx/outputXlsxFile.xlsx")); byteArrayOutputStream.writeTo(resultFile); } catch (Exception e) { e.printStackTrace(); } } public static void main(String[] args) { XlsxExcelUtil obj = new XlsxExcelUtil(); obj.testXlsxSheetCopy(); } } -- You are receiving this mail because: You are the assignee for the bug. --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
