Skip to toolbar

File Handling | Copy existing data from one workbook to another workbook in Java.

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.

Copy data from one workbook to another
Copy data from one workbook to another

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:

TestNG:

Java Basics:

Computer Basics:

OOPs Concept:

Java Question And Answer:

Java Programs:


7 thoughts on “File Handling | Copy existing data from one workbook to another workbook in Java.

  1. Very good stuff. really help.
    How to copy sheet from existing work book to existing another work book using XSSFWorkbook?

Leave a Reply

Your email address will not be published. Required fields are marked *

Site Statistics

  • Users online: 0 
  • Visitors today : 6
  • Page views today : 8
  • Total visitors : 49,353
  • Total page view: 67,932

   YouTube ChannelQuora

            Ashok Kumar is working in an IT Company as a QA Consultant. He has started his career as a Test Trainee in manual testing in August 2010. Then he moves towards the automation testing after 4 years. He started learning JAVA and Selenium by self to get the knowledge of automation.

       While learning these tools and working on multiple projects, he found that sometimes people get stuck in live scenarios in their project and they have to do lots of RnD to get out of it. So he decided to start blogging only for such scenarios, where anyone facing any problem in their project, can ask any question or give a solution or you can say an alternate solution to achieve the goal successfully.

Later on, he observed that some people want to learn Java but they have few questions in their mind like how to start Java, whether we should go for the online or offline course. So he started writing tutorials on Java, Jira, Selenium, Excel etc.