Cloning a sheet in a Worksheet

In this post under Apache Excel, I will show how to clone an existing sheet in a Worksheet.

Below is the complete code for your reference.

Main class

1  package package3;
3  import;
4  import;
5  import;
6  import;
8  import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
9  import;
10 import org.apache.poi.xssf.usermodel.XSSFSheet;
11 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
13 public class Example3 {
14     public static void main(String[] args) throws IOException, InvalidFormatException {
15         File file = new File("Example2.xlsx");
16         try(XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(file));
17             FileOutputStream fileOutputStream = new FileOutputStream(file)) {
18             workbook.cloneSheet(0, "Sheet 2");
19             workbook.write(fileOutputStream);
20         }
21     }
22 }

In the above code, at line 15, I create a File object for an existing excel file “Example2.xlsx”.

At line 16, in try with resource block, I create a “workbook” object of type “XSSFWorkbook”.

In the same try with resource block, I create an instance of FileOutputStream to the same file object.

Please note sheets in the worksheet are indexed from 0.

At line 18, I instruct the workbook to clone sheet with index 0 and name the new sheet as “Sheet 2” by taking help of “cloneSheet” method in the Workbook object.

At line 19, I save the modified workbook to the same file.

In this way we can clone a sheet and its contents in a excel workbook.

Leave a Reply