How to write an excel file in java / How to write data to excel in java

Microsoft excel is the most widely used program to maintain data. Hence it becomes vital that the data from a java program be directly written to an excel file in different rows and columns. Java itself does not provide any features to perform excel related operations but there are external libraries written in java which support this. Two of the common libraries are Apache POI and jexcel (or jxl). Both of them provide convenient methods to handle excel files. This post will be using jexcel (or jxl) api for its demonstration.

Suppose we have the following information about a company’s employees :

Employee Id: 123 Department: Customer Support Workplace: Bangalore, India

Employee Id: 456 Department: Quality Assurance Workplace: California, USA

Now we want to export it to an excel file where complete details of an employee are written in a row and each field is written to a column in the row.

 

	private static void writeExcel() throws WriteException, IOException {
		String filePath = "D:\\employee.xls";
		WritableWorkbook workBook = null;
		try {
			//initialize workbook
			workBook = Workbook.createWorkbook(new File(filePath));
			//create sheet with name as Employee and index 0
			WritableSheet sheet = workBook.createSheet("Employee", 0);
			// create font style for header cells
			WritableFont headerCellFont = new WritableFont(WritableFont.ARIAL, 14,
					WritableFont.BOLD, true);
			//create format for header cells
			WritableCellFormat headerCellFormat = new WritableCellFormat(
					headerCellFont);
			// create header cells
			Label headerCell1 = new Label(0, 0, "Employee ID", headerCellFormat);
			Label headerCell2 = new Label(1, 0, "Department", headerCellFormat);
			Label headerCell3 = new Label(2, 0, "Workplace", headerCellFormat);
			// add header cells to sheet
			sheet.addCell(headerCell1);
			sheet.addCell(headerCell2);
			sheet.addCell(headerCell3);
 			// create cell contents for Employee 1
			Label employee1Id = new Label(0, 1, "123");
 			Label employee1Department = new Label(1, 1, "Customer Support");
 			Label employee1Workplace = new Label(2, 1, "Bangalore, India");
			// add cells to sheet
			sheet.addCell(employee1Id);
			sheet.addCell(employee1Department);
			sheet.addCell(employee1Workplace);
 			// create cells content for Employee 2
 			Label employee2Id = new Label(0, 2, "456");
 			Label employee2Department = new Label(1, 2, "Quality Assurance");
 			Label employee2Workplace = new Label(2, 2, "California, USA");
			// add cells to sheet
			sheet.addCell(employee2Id);
			sheet.addCell(employee2Department);
			sheet.addCell(employee2Workplace);
                        //for setting width of columns
                        sheet.setColumnView(0, 15);
                        sheet.setColumnView(1, 20);
                        sheet.setColumnView(2, 15);
			//write workbook
			workBook.write();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (RowsExceededException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		} finally {
			//close workbook
			workBook.close();
		}
	}

Details :

Keeping in mind the hierarchy of components, first we create a workbook. Since we are writing a workbook, an instance of jx.write.WritableWorkbookis required. We get this using static createWorkbook()method of jxl.Workbookclass which takes the path of the file to create as an argument. Using WritableWorkbooka sheet is created. Since this is a write operation, we need a Writable sheet. jxl.WritableWorkbook‘s createSheet()method gives it.

Next we need to create cells one by one and add them to a sheet. Cells have a location in the sheet and their location is defined by row and column numbers. Also, they should have some content. All three things (content, row and column numbers) are provided to the cells at the time of their creation. A cell in jxl api is represented by jxl.write.Labelclass and its constructor accepts column, row of the cell and its contents. So while creating a cell, we can easily supply all the information required by the cell. After creating all the cells, we add them to the sheet.

Finally when all the cells are created and added to the sheet, they are written to the workbook using jxl.WritableWorkbook‘s write()method and the workbook is closed using jxl.WritableWorkbook‘s close() method.

It is also possible to add style to a cell such as changing its background, text color; changing the font type and size of cell contents etc. This is done by passing an instance of jxl.write.WritableCellFormatto jxl.write.Label‘s (which denotes a cell in jxl) constructor. We have used it in above code to change the font and size of the headers and to format them in bold text.

Excel written by the above code will look like :

 

Let’s tweak in :

1. Workbook contents will not be written until the workbook is closed.
2. jxl.write.Labelclass indirectly implements jxl.Cellwhich actually represents a cell in a sheet. jxl.write.Labelis used in place of jxl.Cellbecause addCell()method of a writable sheet accepts an object of type jxl.write.WritableCelland jxl.Labelimplements it.
3. Column and row indices in jxl start with 0.
4. jxl does not support reading and writing excel files with “.xlsx” extension.
5. The above method creates a new excel file if it does not already exist and overwrites if it exists. In order to modify an existing file, a copy of the already existing file has to be made which can then be modified.
   This is done using overloaded version of createWorkbook()method of jxl.Workbookclass which takes the file path of the new file and an existing workbook as argument.
6. File being written should be closed at the time of program execution otherwise an error will be raised as :

   java.io.FileNotFoundException: D:\employee.xls (The process cannot access the file because it is being used by another process)

Liked this post !!! Don’t forget to share…