How to write data to an existing Excel file in Java

This article shows you how to write data to an existing Excel file using Apache POI.

Apache POI is a pure Java library developed for reading, writing, and modifying Microsoft documents(eg. Excel, Word, PowerPoint). The Excel spreadsheet is a very popular file format created by Microsoft.

Let’s assume a sample Excel file(students.xlsx) which we are going to update. As you can see in the below image this Excel file contains the student’s data(name, address, email id, and age).

Table of Contents

How to open an existing Excel file in Java?

First of all, to update or write data to an existing Excel file, we have to open the existing Excel file. Let’s see here, how do we open an existing Excel file?

We can use FileInputStream and WorkbookFactory class for opening the Excel file. The FileInputStream is used for reading byte-oriented data (streams of raw bytes) whereas the WorkbookFactory class is used for creating Workbook (HSSFWorkbook or XSSFWorkbook based on input file format XLS/XLSX provided).

Let’s see the piece of code below for this:

// Creating file object of existing excel file
File xlsxFile = new File("E:/Excel/students.xlsx");

//Creating input stream
 FileInputStream inputStream = new FileInputStream(xlsxFile);
             
 //Creating workbook from input stream
 Workbook workbook = WorkbookFactory.create(inputStream);

//Your next code goes here...

How to update an Excel file in Java?

Apache Software Foundation provides a popular library called Apache POI which is capable enough to update or modify an existing Excel file in Java.

Steps to update an Excel file using Apache POI

Load an existing Excel file to InputStream. eg. FileInputStream inStream= new FileInputStream(new File(filePath));
Get the Workbook from the InputStream. eg. Workbook workbook = WorkbookFactory.create(inStream);
Update new data to an existing Sheet or create a new Sheet.
Close the InputStream.
Write the Workbook to an OutputStream. It will overwrite the existing file with updated data.
Close the Workbook and OutputStream.

Let’s take the above sample Excel(students.xlsx) to update new data. Here we are going to add two more students to this file.

The below code example will work for both Excel file formats XLS and XLSX because we used WorkbookFactory. It creates HSSFWorkbook or XSSFWorkbook object based on the input file supplied.

package com.javacodepoint.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.ss.usermodel.Cell;
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;

/**
 * 
 * @author javacodepoint.com
 *
 */

public class UpdateExistingExcelFile {

	public static void main(String[] args) {

		// Creating file object of existing excel file
		File xlsxFile = new File("E:/Excel/students.xlsx");
		
		//New students records to update in excel file
		Object[][] newStudents = {
                {"Rakesh sharma", "New Delhi", "rakesh.sharma@gmail.com", 22},
                {"Thomas Hardy", "London", "thomas.hardy@gmail.com", 25}
        };

		try {
			//Creating input stream
			FileInputStream inputStream = new FileInputStream(xlsxFile);
			
			//Creating workbook from input stream
			Workbook workbook = WorkbookFactory.create(inputStream);

			//Reading first sheet of excel file
			Sheet sheet = workbook.getSheetAt(0);

			//Getting the count of existing records
			int rowCount = sheet.getLastRowNum();

			//Iterating new students to update
			for (Object[] student : newStudents) {
				
				//Creating new row from the next row count
				Row row = sheet.createRow(++rowCount);

				int columnCount = 0;

				//Iterating student informations
				for (Object info : student) {
					
					//Creating new cell and setting the value
					Cell cell = row.createCell(columnCount++);
					if (info instanceof String) {
						cell.setCellValue((String) info);
					} else if (info instanceof Integer) {
						cell.setCellValue((Integer) info);
					}
				}
			}
			//Close input stream
			inputStream.close();

			//Crating output stream and writing the updated workbook
			FileOutputStream os = new FileOutputStream(xlsxFile);
			workbook.write(os);
			
			//Close the workbook and output stream
			workbook.close();
			os.close();
			
			System.out.println("Excel file has been updated successfully.");
			
		} catch (EncryptedDocumentException | IOException e) {
			System.err.println("Exception while updating an existing excel file.");
			e.printStackTrace();
		}
	}
}

After updating the existing file the updated file will look as follow:

How to update an Excel file using Apache POI

How to update a specific Row and Cell in Excel?

To update a specific Row and Cell of an existing Excel file, the steps are almost the same as in the above example.

The only you have to do here is, get the particular Row and Cell using getRow() and getCell() then overwrite the cell value with new data.

Let’s assume we need to update the age of the first student.

See the below code snippet to understand this:

//Getting first sheet of workbook
Sheet sheet = workbook.getSheetAt(0);

//Getting age cell of first row from the sheet
Cell cell= sheet.getRow(1).getCell(3);

//Updating the cell value with new data
cell.setCellValue(30);

How to create a new Sheet or rename Sheet in Excel?

The following code snippet is used to create a new Sheet in an Excel file.

Sheet newSheet = workbook.createSheet("New Student List");

The following code snippet is used to rename the Sheet in an Excel file.

workbook.setSheetName(0, "Old Student List");

Let’s see the complete example below:

package com.javacodepoint.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.ss.usermodel.Cell;
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;
/**
 * 
 * @author javacodepoint.com
 *
 */

public class CreateOrRenameSheet {

	public static void main(String[] args) {

		// Creating file object of existing excel file
		File xlsxFile = new File("E:/Excel/students.xlsx");

		// New students records to update in excel file
		Object[][] newStudents = { 
				{ "Alok Jha", "Patna", "alok.j@gmail.com", 21 },
				{ "Robert", "Hyderabad", "robert@gmail.com", 20 } 
		};
		try {
			// Creating input stream
			FileInputStream inputStream = new FileInputStream(xlsxFile);

			// Creating workbook from input stream
			Workbook workbook = WorkbookFactory.create(inputStream);

			// Reading first sheet of excel file
			Sheet sheet = workbook.createSheet("New Student List");
			
			int rowCount=0;

			// Creating header of the new excel sheet
			Row header = sheet.createRow(rowCount++);
			header.createCell(0).setCellValue("Student Name");
			header.createCell(1).setCellValue("Address");
			header.createCell(2).setCellValue("Email ID");
			header.createCell(3).setCellValue("Age");

			// Iterating new students to update
			for (Object[] student : newStudents) {

				// Creating new row from the next row count
				Row row = sheet.createRow(rowCount++);

				int columnCount = 0;

				// Iterating student informations
				for (Object info : student) {

					// Creating new cell and setting the value
					Cell cell = row.createCell(columnCount++);
					if (info instanceof String) {
						cell.setCellValue((String) info);
					} else if (info instanceof Integer) {
						cell.setCellValue((Integer) info);
					}
				}
			}
			// Close input stream
			inputStream.close();
			
			//Renaming the first sheet of workbook
			workbook.setSheetName(0, "Old Student List");

			// Crating output stream and writing the updated workbook
			FileOutputStream os = new FileOutputStream(xlsxFile);
			workbook.write(os);

			// Close the workbook and output stream
			workbook.close();
			os.close();

			System.out.println("Excel file has been updated successfully.");

		} catch (EncryptedDocumentException | IOException e) {
			System.err.println("Exception while updating an existing excel file.");
			e.printStackTrace();
		}
	}
}

How to add a new column in the existing Excel?

Let’s see how can we add a new column to an existing Excel file using Apache poi in Java. Here we will see two scenarios, first adding a new blank column and another one adding a new column with data.

Add a new blank column in existing Excel using Java

By using shiftColumns() method of Sheet class, we can add a new blank column to an existing Excel file. The purpose of this method is to shift columns between startColumn and endColumn with a specified number of columns.

eg.- sheet.shiftColumns(startColumn, endColumn, newColCount);

Let’s see the below code to understand it better:

/*
	 * This method work for inserting new blank column just before the provided column index
	 */
	public static void insertNewColumnBefore(Workbook workbook, int colIndex) {
		
		//Getting the first sheet from workbook
		Sheet sheet = workbook.getSheetAt(0);
		
		int startColumn = colIndex;
		int endColumn = sheet.getRow(0).getLastCellNum();
		
		// to insert only one column
		int newColCount = 1;
		
		/*
		 * Shifts columns between startColumn and endColumn, newColCount number of columns.
		 * Code ensures that columns don't wrap around
		 */
		sheet.shiftColumns(startColumn, endColumn, newColCount);
	}

Note: find the complete source code in the below download link.

Add a new column with data in existing Excel using Java

If we are required to add a new column with data to an existing Excel file, then we have to add the data after shifting the columns. Let’s see the below code to understand this:

/*
	 * This method work for inserting new blank column just before the provided
	 * column index with data
	 */
	public static void insertNewColumnBeforeWithData(Workbook workbook, int colIndex) {

		// Getting the first sheet from workbook
		Sheet sheet = workbook.getSheetAt(0);

		int startColumn = colIndex;
		int endColumn = sheet.getRow(0).getLastCellNum();

		// to insert only one column
		int newColCount = 1;

		/*
		 * Shifts columns between startColumn and endColumn, newColCount number of
		 * columns. Code ensures that columns don't wrap around
		 */
		sheet.shiftColumns(startColumn, endColumn, newColCount);

		// Add the data
		for (int i = 0; i <= sheet.getLastRowNum(); i++) {
			Row row = sheet.getRow(i);
			if (i == 0) {
				row.createCell(colIndex).setCellValue("ID");
			} else {
				row.createCell(colIndex).setCellValue(generateId());
			}
		}

	}

	/*
	 * This method generate random id
	 */
	private static int generateId() {
		return (int) (Math.random() * 100000);
	}

Note: find the complete source code in the below download link.

How to disable Cell editing in Excel?

Disable cell editing is nothing but making the cell read-only. Sometimes we are required to disallow users to update the cell value, in that case, we can make that cell a read-only cell. Let’s see the steps for making it:

Steps to make Cell read-only in Excel sheet

Create a sheet. eg. workbook.createSheet("students");
Protect the sheet with a password. eg. sheet.protectSheet("password");
Create CellStyle using createCellStyle() method and set locked with a boolean value true or false.
Create Row and Cell in an Excel sheet.
Set the cell value and cell style.
Write the workbook to an output stream.

Important points to remember:

If you protect the sheet with a password, automatically all the cells become read-only.
To make only specific cell read-only, you have to set appropriate CellStyle.
You may create two different CellStyle with lock value true and false. eg. cellStyle.setLocked(true);

Let’s see the below sample example to understand this better. Here we are going to make an Email ID column read-only.

package com.javacodepoint.excel;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.EncryptedDocumentException;
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.xssf.usermodel.XSSFWorkbook;

/**
 * 
 * @author javacodepoint.com
 *
 */

public class DisableCellEditingExcel {
	public static void main(String[] args) {

		File xlsxFile = new File("E:/Excel/students.xlsx");

		// Student records to write in excel file
		Object[][] students = { { "Rahul Singh", "Hyderabad", "rahul.singh@gmail.com", 25 },
				{ "Pawan Patil", "Mumbai", "pawan.patil@gmail.com", 27 },
				{ "karl Jablonski", "Seattle", "jablonski.karl@gmail.com", 25 },
				{ "Jhon Smith", "New Yark", "smith.jh@gmail.com", 22 } };

		try {			
			// Creating workbook
			Workbook workbook = new XSSFWorkbook();

			// Create sheet
			Sheet sheet = workbook.createSheet("Students");
			sheet.protectSheet("password");
			
			//Creating two styles for disable or enable cell
			CellStyle disableCellStyle= workbook.createCellStyle();
			disableCellStyle.setLocked(true);
			
			CellStyle enableCellStyle= workbook.createCellStyle();
			enableCellStyle.setLocked(false);

			int rowCount = 0;
			//Creating header of the new excel sheet
			Row header = sheet.createRow(rowCount++);
			header.createCell(0).setCellValue("Student Name");
			header.createCell(1).setCellValue("Address");
			header.createCell(2).setCellValue("Email ID");
			header.createCell(3).setCellValue("Age");
			
			for (Object[] student : students) {

				// Creating new row from the next row count
				Row row = sheet.createRow(rowCount++);
				int columnCount = 0;

				// Iterating student informations
				for (Object info : student) {

					// Creating new cell and setting the value
					Cell cell = row.createCell(columnCount);
					if (info instanceof String) {
						cell.setCellValue((String) info);
					} else if (info instanceof Integer) {
						cell.setCellValue((Integer) info);
					}
					
					//Making email id column read-only
					if(columnCount == 2) {
						cell.setCellStyle(disableCellStyle);
					}else {
						cell.setCellStyle(enableCellStyle);
					}				
					columnCount++;
				}
			}
			// Crating output stream and writing the updated workbook
			FileOutputStream os = new FileOutputStream(xlsxFile);
			workbook.write(os);

			// Close the workbook and output stream
			workbook.close();
			os.close();

			System.out.println("File created successfully with specific disabled cell.");

		} catch (EncryptedDocumentException | IOException e) {
			System.err.println("Exception while creating read-only cell in excel file.");
			e.printStackTrace();
		}
	}
}

Conclusion

In this article, you have seen how to write data to an existing Excel file using Apache POI. You have also seen how to update a particular row and cell value in an Excel spreadsheet.

You can download the maven application of the above example programs from the end of this article.

You might like this:

Share with friends