Hi: Can you please add a copySheet method on org.apache.poi.ss.util.SheetUtil helper class? It is great help on users to do the copy sheet process
To copy a sheet within one workbook, you just need to call workbook.cloneSheet(sheetIndex) For any other kind of sheet copying, if you'd care to work up a patch to add the missing functionality, we'd be delighted to review it and commit it if appropriate!
How do I copy the cloned to sheet to another workbook? for example copy a sheet from workbook1 and need to paste the copied one to another workbook2
http://www.coderanch.com/t/420958/open-source/Copying-sheet-excel-file-excel Also I have tired to copy sheet based on below open source forum using poi-3.11 jars copy XLS sheets is working fine (XLS to XLS) I was able to open the copied sheet. But XLSX sheets (XLSX to XLSX) giving me error while I opening the copied sheet "Removed Records: Merge cells from /xl/worksheets/sheet5.xml part"
I don't think we can use the code from coderanch as I don't believe it's under a compatible license If you'd be able to write up (from scratch!) code to do the same things, we'd love it as a contribution to the project! See http://poi.apache.org/guidelines.html for a bit more on contributing
Created attachment 32756 [details] Merged Cell border style issue
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(); } }
Created attachment 32826 [details] copy a sheet from source to destination I have written the code sheet copy process, added test case in the same class. Current limitation is Font style was not able to copy, but I'm least prioritized this issue. Attached the code and different type of source sheet and destination sheet thanks Prejith George
(In reply to Prejith Pulikkottil from comment #7) > Created attachment 32826 [details] > copy a sheet from source to destination Is this code licensed under Apache software license 2.0? Could you reformulate this as a patch against POI trunk? https://poi.apache.org/guidelines.html#SubmittingPatches SheetUtil.java: https://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/util/SheetUtil.java?view=log TestSheetUtil.java: https://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/util/TestSheetUtil.java?view=log Excel files: https://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/