In this tutorial, we will see how to read excel file using jxl and write excel using jxl. We are going to use JExcel API for reading and writing file in java.
Code: Reading Excel File
File contents:
package com.slb.core.java;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import java.io.File;
import java.io.IOException;
public class ReadExcelFile {
public static void main(String[] args) {
String fileLocation = "C:\\java\\book.xls";
readExcelFile(fileLocation);
}
private static void readExcelFile(String fileLocation) {
Workbook workbook = null;
try {
workbook = Workbook.getWorkbook(new File(fileLocation));
Sheet sheet = workbook.getSheet(0);
Cell cell1 = sheet.getCell(0, 0);
System.out.print(cell1.getContents() + ":");
Cell cell2 = sheet.getCell(0, 1);
System.out.println(cell2.getContents());
Cell cell3 = sheet.getCell(1, 0);
System.out.print(cell3.getContents() + ":");
Cell cell4 = sheet.getCell(1, 1);
System.out.println(cell4.getContents());
Cell cell5 = sheet.getCell(2, 1);
System.out.println(cell5.getContents());
System.out.print(cell1.getContents() + ":");
cell2 = sheet.getCell(0, 2);
System.out.println(cell2.getContents());
System.out.print(cell3.getContents() + ":");
cell4 = sheet.getCell(1, 2);
System.out.println(cell4.getContents());
Cell cell6 = sheet.getCell(2, 1);
System.out.println(cell6.getContents());
System.out.print(cell3.getContents() + ":");
cell4 = sheet.getCell(1, 2);
System.out.println(cell4.getContents());
} catch (IOException e) {
e.printStackTrace();
} catch (BiffException e) {
e.printStackTrace();
} finally {
if (workbook != null) {
workbook.close();
}
}
}
}
output:
hello:simplified
world:learning
blog
hello:1
world:2
blog
world:2
Code: Writing Excel File
package com.slb.core.java;
import jxl.Workbook;
import jxl.write.*;
import jxl.write.Number;
import java.io.File;
import java.io.IOException;
public class WriteExcel {
public static void main(String[] args) {
String fileLocation = "C:\\java\\book2.xls";
writeExcelFile(fileLocation);
}
private static void writeExcelFile(String fileLocation) {
WritableWorkbook myFirstWbook = null;
try {
myFirstWbook = Workbook.createWorkbook(new File(fileLocation));
// create an Excel sheet
WritableSheet excelSheet = myFirstWbook.createSheet("Sheet1", 0);
// add something into the Excel sheet
Label label = new Label(0, 0, "Simplified");
excelSheet.addCell(label);
Number number = new Number(0, 1, 1);
excelSheet.addCell(number);
label = new Label(1, 0, "Learning");
excelSheet.addCell(label);
label = new Label(1, 1, "Blog");
excelSheet.addCell(label);
number = new Number(0, 2, 2);
excelSheet.addCell(number);
label = new Label(1, 2, "Thanks!");
excelSheet.addCell(label);
myFirstWbook.write();
} catch (IOException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
} finally {
if (myFirstWbook != null) {
try {
myFirstWbook.close();
} catch (IOException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
}
}
}
}
File contents we created by writing
Explanation: Reading Excel File using jxl api
We are using jxl api to read the file. There are two ways to add jxl in our project:
Maven pom.xml
If we are using pom.xml then we can add below dependency in pom.xml file as:
pom.xml file: under dependencies tag add below dependency
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.12</version>
</dependency>Using jar file jxl
If we are using jar, then download the jar file from here and add it to your project config.
In the above example, we are reading and writing Excel files using jxl api.
Code walkthrough
Reading xls files
We are creating the object of workbook first to get the workbook like
workbook = Workbook.getWorkbook(new File(fileLocation));From file location of Excel file we are getting the object of workbook.
Sheet sheet = workbook.getSheet(0);From workbook object, we are getting sheet, we are reading from sheet(0) that means from the first sheet of workbook.
Cell cell1 = sheet.getCell(0, 0);Now from sheet, we are getting the cell object to read a particular cell.
System.out.print(cell1.getContents() + ":");Cell object has method getContents() to read the contents of the cell, we are using that to read the data, and we are printing that on the console using System.out.print
This is how we can read from Excel files.
Writing xls files
For writing or generating the workbook, we need to first create the workbook like:
myFirstWbook = Workbook.createWorkbook(new File(fileLocation));On given location, it will generate the Excel file and returns the workbook object out of it.
Now, the next step is to create the workbook sheet like this:
WritableSheet excelSheet = myFirstWbook.createSheet("Sheet1", 0);We have created the sheet and named it “Sheet1”.
On sheet object we have addCell() method through which we can add/write contents on the sheet.
excelSheet.addCell(number);This is how we write into Excel files.
Get source code at SLB-Github.
Checkout other Java Tutorials
FAQ Java jxl
- What Is Jxl API?
Jxl API is an open-source library available under LGPL licence for reading and writing data into Excel spreadsheets in Java programs. With an intuitive user interface that makes communication with Excel easier programmatically. Jxl’s purpose can also be described as free library use by developers. - Why we use Jxl API?
Jxl API provides an easy way to read and write data from Excel files, supporting both 1997-2003 as well as 2007 versions. It is widely employed to automate data entry tasks, create reports and conduct data analyses in various fields - How do I Install Jxl API within Java?
If you want to use the Jxl API within a Java project, add the Jxl Jar file as part of your classpath. You can download it either directly from Jxl’s website or Maven Central repository.
- How Can I Read Data from an Excel File With Jxl API?
In order to access Excel data using Jxl API, first create and open an Excel file before accessing its Sheet object in order to iterate through its rows and columns to read the data. - How can I write data to an Excel file using Jxl API?
To write data to an Excel file using the Jxl API, first create a Workbook object and add a Sheet object; you can then add new cells with data directly in them. - How can I format an Excel file using Jxl API?
Jxl API offers various ways for formatting Excel files. You can set font size, font color, background color and border style settings among many others. - Can Jxl API Create Formulas in an Excel File?
Jxl API offers a Formula class which allows users to easily create formulas in Excel files using basic mathematical operations, conditional expressions and more. - What methods are available in Jxl API for handling errors?
Jxl API offers various mechanisms for handling errors gracefully within your code. Exceptions can be captured and handled appropriately to prevent further delays and possible performance degradation. - How can Jxl API assist me in working with large Excel files?
Jxl API offers various means for working with large Excel files. Use its Streaming API to read and write large Excel files without running out of memory space. - What are the limitations of Jxl API?
Jxl API does have some restrictions; for instance, it does not support Excel 2010 file formats.
Conclusion
In this tutorial we saw that using jxl api how to read and write Excel files in java. For better use and for better implementation, we should check for Apache-Poi library for excel related work.