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

September 17, 2017 7 Comments

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:







			
			Post Views: 
			8,228
			
 
10786total visits,16visits today

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

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

    Reply

Leave a Reply

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