Copy existing data from one workbook to another workbook in Java using File Handling. We have to copy existing data from one workbook to another workbook in Java.

Example:

This includes all the sheets and its content from input workbook as mentioned in the below example.

Overview of Program

As per the above screenshot, we clearly understood that we need two workbooks. One workbook will be the input file from where we have to copy the content. The other workbook will be the output file where we have to export or paste the data from the input file. The entire process will include following steps:

1. Locate path and filename where the existing excel file(testData.xlsx) placed using File Class and FileInputStream class.
2. Locate path and filename where the new excel file(testData_Copy.xlsx) needs to be created using File Class and FileOutputStream class.
3. Create workbook using XSSFWorkbook class for output excel file.
4. Create sheet in the above workbook using XSSFSheet class with the same name as in the input file.
5. Now, create new Row in the output sheet using createRow() method of the sheet object.
6. Now, create new Cell in the above Row using createCell() method of the Row object.
7. Now, input text/value in the newly created cell using setCellValue() Method of the cell object. (Note: These values have been extracted from input excel file.)
8. Once you have created a new Row, you can add multiple cells in the same row using Steps # 5-7.
9. Once you have entered all the values in the FileOutputStream object. Now, you have to write all the data in original excel file. This can be done using write() of the XSSFWorkbook Class.
10. At the end, you have to close the workbook using close() of the XSSFWorkbook Class object.

Java Code

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class CopyContentOneWorkbookToOther {

	public static void main(String[] args) throws IOException 
	{
		// Step #1 : Locate path and file of input excel.
		File inputFile=new File("input_Excel\\testData.xlsx");
		FileInputStream fis=new FileInputStream(inputFile);
		XSSFWorkbook inputWorkbook=new XSSFWorkbook(fis);
		int inputSheetCount=inputWorkbook.getNumberOfSheets();
		System.out.println("Input sheetCount: "+inputSheetCount);
		
		// Step #2 : Locate path and file of output excel.
		File outputFile=new File("output_Excel\\testData_Copy.xlsx");
		FileOutputStream fos=new FileOutputStream(outputFile);
		
		// Step #3 : Creating workbook for output excel file.
		XSSFWorkbook outputWorkbook=new XSSFWorkbook();
		
		// Step #4 : Creating sheets with the same name as appearing in input file.
		for(int i=0;i<inputSheetCount;i++) 
                { 
                  XSSFSheet inputSheet=inputWorkbook.getSheetAt(i); 
                  String inputSheetName=inputWorkbook.getSheetName(i); 
                  XSSFSheet outputSheet=outputWorkbook.createSheet(inputSheetName); 

                 // Create and call method to copy the sheet and content in new workbook. 
                 copySheet(inputSheet,outputSheet); 
                }

               // Step #9 : Write all the sheets in the new Workbook(testData_Copy.xlsx) using FileOutStream Object
               outputWorkbook.write(fos); 
              // Step #10 : At the end of the Program close the FileOutputStream object. 
               fos.close(); 
          }

           public static void copySheet(XSSFSheet inputSheet,XSSFSheet outputSheet) 
           { 
              int rowCount=inputSheet.getLastRowNum(); 
              System.out.println(rowCount+" rows in inputsheet "+inputSheet.getSheetName()); 
               
                int currentRowIndex=0; if(rowCount>0)
		{
			Iterator rowIterator=inputSheet.iterator();
			while(rowIterator.hasNext())
			{
				int currentCellIndex=0;
				Iterator cellIterator=rowIterator.next().cellIterator();
				while(cellIterator.hasNext())
				{
				// Step #5-8 : Creating new Row, Cell and Input value in the newly created sheet. 
					String cellData=cellIterator.next().toString();
					if(currentCellIndex==0)
						outputSheet.createRow(currentRowIndex).createCell(currentCellIndex).setCellValue(cellData);
					else
						outputSheet.getRow(currentRowIndex).createCell(currentCellIndex).setCellValue(cellData);
					
					currentCellIndex++;
				}
				currentRowIndex++;
			}
			System.out.println((currentRowIndex-1)+" rows added to outputsheet "+outputSheet.getSheetName());
			System.out.println();
		}
	}
}

Related Links:
File Handling | Reading data from word document(.doc or .docx) in JAVA.
File Handling | Reading data from Excel files (.xls or .xlsx) using JAVA.
File Handling | Writing data into an Excel(.XLSX or .XLS) File.
File Handling | Implement formatting in Excel using Java.
File Handling | Copy existing data from one workbook to another workbook in Java.
File Handling | Reading data from PDF file using JAVA.
File Handling || Traverse folders and subfolders in Java.
File Handling || Reading and Writing data from a text file.
File Handling || Multiple file creation using template based input data.
Framework || Simple example of Key Driven Framework using excel sheet in Selenium(JAVA).
TestNG:
TestNG – 1 || Introduction and benefits of TestNG Framework.
TestNG – 2 || Installation process and a sample program of TestNG.
TestNG – 3 || Create and execute multiple Test Cases.
TestNG – 4 || Let’s understand @Test Annotation and attributes.
TestNG – 5 || Understand Assertion in TestNG.
TestNG – 6 || Use of @BeforeMethod and @AfterMethod.
TestNG – 7 || Use of @BeforeClass and @AfterClass.
TestNG – 8 || Creation and execution of Test Suites.
TestNG – 9 || Let’s move deep into the Test Suites.
TestNG – 10 || Use @BeforeTest and @AfterTest Annotations.
TestNG – 11 || Groups attribute with @Test Annotation.
TestNG – 12 || Use of @BeforeGroups & @AfterGroups.
TestNG – 13 || Use of @BeforeSuite & @AfterSuite.
TestNG – 14 || DataProvider annotation & attribute.
TestNG – 15 || DataProvider with parameters.
TestNG – 16 || Access data from Excel sheet using DataProvider.
TestNG – 17 ||  Passing multiple Parameters in testng xml.
TestNG – 18 || Multiple Browser and Parallel Execution in TestNG.
TestNG -19 || Concept of Parallel Execution.
TestNG – 20 || Run TestNG Program using main() method.
Java Basics:
Basic Java – 1 || Understand Java before start learning JAVA.
Basic Java – 2 || Variables and Data Types used in JAVA.
Basic Java – 3 || Understanding Class, Objects, Methods in Java.
Basic Java – 4 || More on methods(Return Type and Parameters)
Basic Java – 5 || Methods- Call by Value and Call by Reference in Java.
Basic Java – 6 || Understanding of Constructor and Destructor in JAVA.
Basic Java – 7 || Static Variables and Methods.
Basic Java – 8 || Lets learn about Arrays in Java.
Basic Java – 9 || Performing multiple operations using Java Operators.
Basic Java – 10 || Conditions (If and Switch) in JAVA.
Basic Java – 11 || for and for-each in Java. (Loops Part-1)
Basic Java – 12 || Alternate looping concepts while and do-while. (Loops Part-2)
Basic Java – 13 || Decimal values v/s Octal base(8) values in JAVA.
Basic Java – 14 || Learn about String literals in Java.
Basic Java – 15 || Runtime User Input using Scanner Class (Part-1).
Basic Java – 16 || Runtime User Input using BufferedReader Class (Part-2).
Basic Java – 17 || Runtime User Input using Console Class (Part-3).
Basic Java – 18 || Difference between break and continue keywords.
Basic Java – 19 || Sending Email using Java (Part-1).
Basic Java – 20 || Sending Email with attachment using Java (Part-2).
Basic Java – 21 || Stack memory and Heap memory in Java.
Basic Java – 22 || Let’s learn more about String.
Computer Basics:
Computer Basics -1 || Introduction and Structure of Computer.
Computer Basics -2 || Types of Computers and Usage.
Computer Basics -3 || What are the different types of Software?
Computer Basics -4 || Importance of Operating System(OS).
Computer Basics -5 || Understanding of Number System.
OOPs Concept:
Java || Abstract class with Constructor.
Java | Object Oriented Programming (OOPs) Concept
Java Question And Answer:
QnA | Difference between Integer and int keywords.
QnA | Can main method be overloaded?
QnA | How do I reverse a String/Sentence in Java?
QnA | Perform Multiplication and Division without * or / or % operators.
QnA | How do I get the default value of data type?
QnA | How to split String if it contains period symbol (.) in between?
Java Programs:
Different ways to Reverse a String in Java.
Copy formatting & style of cells from one sheet to another.
Getting IP address and Hostname using InetAddress Class.
User inputs via Command Prompt using arguments of main() method of a class.
Program for List and ArrayList in Java.
Useful methods and implementation under Scanner Class.
Swapping two variable values without using any third variable.
Difference between int x= 10 and y=010 in Java.
Parameterized Constructors v/s Setter and Getter function in JAVA.
Override a Static Method.



			
			Post Views: 
			12,770