Bug 57885

Summary: Add a function to copy a sheet in org.apache.poi.ss.util.SheetUtil class
Product: POI Reporter: Prejith Pulikkottil <pulikkot>
Component: XSSFAssignee: POI Developers List <dev>
Status: NEEDINFO ---    
Severity: enhancement Keywords: PatchAvailable
Priority: P2    
Version: 3.12-dev   
Target Milestone: ---   
Hardware: PC   
OS: All   
Attachments: Merged Cell border style issue
copy a sheet from source to destination

Description Prejith Pulikkottil 2015-05-04 12:19:24 UTC
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
Comment 1 Nick Burch 2015-05-04 13:08:55 UTC
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!
Comment 2 Prejith Pulikkottil 2015-05-05 07:52:15 UTC
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
Comment 3 Prejith Pulikkottil 2015-05-05 08:05:14 UTC
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"
Comment 4 Nick Burch 2015-05-05 08:57:11 UTC
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
Comment 5 Prejith Pulikkottil 2015-05-26 10:06:20 UTC
Created attachment 32756 [details]
Merged Cell border style issue
Comment 6 Prejith Pulikkottil 2015-05-26 10:07:51 UTC
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();
  }
}
Comment 7 Prejith Pulikkottil 2015-06-16 12:33:00 UTC
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
Comment 8 Javen O'Neal 2016-06-19 20:28:24 UTC
(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/