Read and Write Excel using Java

You can Read and Write excel using java. We will use Workbook – JExcelApi to read and write an excel sheet.

Download Workbook – JExcelApi from http://sourceforge.net/projects/jexcelapi/files/

We are using eclipse for this example.

  • Read cells/data from the Excel file
  • Create a new file and write data to the newly created file
  • Write data to the existing excel file

    Package Explorer Excel Manipulation
  • So, let’s pick the first example i.e. Read cells/data from the Excel file. To start with this program you need an excel sheet to read. So, for this program to run we have added a folder “excelsheet” in the project’s root and kept an excel file with name “testsheet.xls”. We will read this file using the below program.
import java.io.File;
import java.io.IOException;

import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class ReadExcel {

	/**
	 * @param args
	 * @throws IOException
	 * @throws BiffException
	 */
	public static void main(String[] args) throws BiffException, IOException {
		// TODO Auto-generated method stub
		String path = System.getProperty("user.dir");
		path = path + "\\excelsheet";

		Workbook readbook = Workbook.getWorkbook(new File(path
				+ "\\Excel.xls"));

		Sheet read = readbook.getSheet(0);
		int row_count = read.getRows();

		System.out.println("count: " + row_count);

		for (int i = 0; i < row_count; i++) {

			String s_no = read.getCell(0, i).getContents();

			String task = read.getCell(1, i).getContents();

			System.out.println("First Column: '" + s_no + "'\nSecond Column: '" + task+"'");
		}

		readbook.close();
	}

}

 

Example:

(Create a new file and write data to the newly created file) In this example, we will write the code to create an excel sheet then, write data in few cells

import java.io.File;
import java.io.IOException;
import java.util.Date;

import jxl.Workbook;
import jxl.write.Colour;
import jxl.write.DateFormats;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

public class WriteNewExcel {

	/**
	 * @param args
	 * @throws IOException
	 * @throws WriteException
	 */
	public static void main(String[] args) throws IOException, WriteException {
		// TODO Auto-generated method stub

		String path = System.getProperty("user.dir");
		path = path + "\\excelsheet";

		WritableWorkbook create_workbook = Workbook.createWorkbook(new File(
				path + "\\Excel.xls"));

		WritableSheet create_sheet = create_workbook.createSheet("one", 1);
		WritableFont wfobj = new WritableFont(WritableFont.ARIAL, 12,
				WritableFont.BOLD);
		WritableCellFormat cfobj = new WritableCellFormat(wfobj);

		cfobj.setBackground(Colour.GREEN);

		cfobj.setWrap(true);
		Label lblDate = new Label(0, 0, "S.No.", cfobj);
		create_sheet.addCell(lblDate);

		lblDate = new Label(1, 0, "Task", cfobj);
		create_sheet.addCell(lblDate);

		create_workbook.write();
		create_workbook.close();
	}

}

Example:

(Write data to the existing excel file) Suppose you have an existing excel sheet in which you have to write the new content or replace the existing one then you can also do that. Below example illustrate, how to write data to the existing excel file.

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

import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

public class WriteExistingFile {

	/**
	 * @param args
	 * @throws IOException
	 * @throws BiffException
	 * @throws WriteException
	 */
	public static void main(String[] args) throws BiffException, IOException,
			WriteException {
		// TODO Auto-generated method stub

		String path = System.getProperty("user.dir");
		path = path + "\\excelsheet";

		Workbook readbook = Workbook
				.getWorkbook(new File(path + "\\Excel.xls"));

		WritableWorkbook copy = Workbook.createWorkbook(new File(path
				+ "\\Excel.xls"), readbook);

		WritableSheet write_sheet = copy.getSheet(0);
		int row_count = write_sheet.getRows();

		System.out.println(row_count);

		for (int i = 0; i < row_count; i++) {

			Label lblDate = new Label(1, i, "Task Updated");
			write_sheet.addCell(lblDate);

			lblDate = new Label(2, i, "New Column");
			write_sheet.addCell(lblDate);
		}
		
		copy.write();
		copy.close();
		readbook.close();
	}
}