JExcel API – Reading and Writing Excel file in Java

excel-logo-new

In this article, we will discuss about how to read and write an excel file using JExcel API, a simple library and widely used for simple operations which do not involve a high level of formatting and complex formulas based operations.

P.S Tested with JExcel API – 2.6.12

1. Download JExcel

Maven user.

pom.xml

   <dependency>
         <groupId>net.sourceforge.jexcelapi</groupId>
         <artifactId>jxl</artifactId>
         <version>2.6.12</version>
   </dependency>

Or download directly at this location

2. Write Excel file

JExcel API example to show you how to create an Excel file, and write data into it.

ExcelWrite.java

package com.mkyong;

import jxl.Workbook;
import jxl.write.*;
import jxl.write.Number;

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

public class ExcelWrite {

    private static final String EXCEL_FILE_LOCATION = "C:\\temp\\MyFirstExcel.xls";

    public static void main(String[] args) {

        //1. Create an Excel file
        WritableWorkbook myFirstWbook = null;
        try {

            myFirstWbook = Workbook.createWorkbook(new File(EXCEL_FILE_LOCATION));

            // create an Excel sheet
            WritableSheet excelSheet = myFirstWbook.createSheet("Sheet 1", 0);

            // add something into the Excel sheet
            Label label = new Label(0, 0, "Test Count");
            excelSheet.addCell(label);

            Number number = new Number(0, 1, 1);
            excelSheet.addCell(number);

            label = new Label(1, 0, "Result");
            excelSheet.addCell(label);

            label = new Label(1, 1, "Passed");
            excelSheet.addCell(label);

            number = new Number(0, 2, 2);
            excelSheet.addCell(number);

            label = new Label(1, 2, "Passed 2");
            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();
                }
            }


        }

    }

}

Output, an Excel file is created with the following content :

jexcel-write-excel

3. Read Excel file

Example to read above Excel file.

ExcelRead.java

package com.mkyong;

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

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

public class ExcelRead {

    private static final String EXCEL_FILE_LOCATION = "C:\\temp\\MyFirstExcel.xls";

    public static void main(String[] args) {

        Workbook workbook = null;
        try {

            workbook = Workbook.getWorkbook(new File(EXCEL_FILE_LOCATION));

            Sheet sheet = workbook.getSheet(0);
            Cell cell1 = sheet.getCell(0, 0);
            System.out.print(cell1.getContents() + ":");    // Test Count + :
            Cell cell2 = sheet.getCell(0, 1);
            System.out.println(cell2.getContents());        // 1

            Cell cell3 = sheet.getCell(1, 0);
            System.out.print(cell3.getContents() + ":");    // Result + :
            Cell cell4 = sheet.getCell(1, 1);
            System.out.println(cell4.getContents());        // Passed

            System.out.print(cell1.getContents() + ":");    // Test Count + :
            cell2 = sheet.getCell(0, 2);
            System.out.println(cell2.getContents());        // 2

            System.out.print(cell3.getContents() + ":");    // Result + :
            cell4 = sheet.getCell(1, 2);
            System.out.println(cell4.getContents());        // Passed 2

        } catch (IOException e) {
            e.printStackTrace();
        } catch (BiffException e) {
            e.printStackTrace();
        } finally {

            if (workbook != null) {
                workbook.close();
            }

        }

    }

}

The above code is nearly self-understandable. Each cell or sheet is mapped as an object in Java. In the above code, we used the JExcel jar to get the worksheet written. By executing the code, the output is obtained as below:


Test Count:1
Result:Passed
Test Count:2
Result:Passed 2

4. Add formatting to Excel file

An example can be further enhanced by adding some formatting. A short code to add formatting has been shown below:

ExcelFormat .java

package com.mkyong;

import jxl.Workbook;
import jxl.write.*;
import jxl.write.Number;

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

public class ExcelFormat {

    private static final String EXCEL_FILE_LOCATION = "C:\\temp\\MyFormattedExcel.xls";

    public static void main(String[] args) {

        //1. Create an Excel file
        WritableWorkbook mySecondWbook = null;
        try {

            mySecondWbook = Workbook.createWorkbook(new File(EXCEL_FILE_LOCATION));
            WritableSheet myFirstSheet = mySecondWbook.createSheet("Sheet 1", 0);

            WritableCellFormat cFormat = new WritableCellFormat();
            WritableFont font = new WritableFont(WritableFont.ARIAL, 16, WritableFont.BOLD);
            cFormat.setFont(font);
            Label label = new Label(0, 0, "Test Count", cFormat);
            myFirstSheet.addCell(label);
            Number number = new Number(0, 1, 1);
            myFirstSheet.addCell(number);

            label = new Label(1, 0, "Result", cFormat);
            myFirstSheet.addCell(label);
            label = new Label(1, 1, "Passed");
            myFirstSheet.addCell(label);

            number = new Number(0, 2, 2);
            myFirstSheet.addCell(number);

            label = new Label(1, 2, "Passed 2");
            myFirstSheet.addCell(label);

            mySecondWbook.write();

        } catch (WriteException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {

            if (mySecondWbook != null) {
                try {
                    mySecondWbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                } catch (WriteException e) {
                    e.printStackTrace();
                }
            }


        }


    }

}

Output

jexcel-format-excel

The code formats the header to Arial, 16px, Bold font. There are several other supported fonts and sizes available to explore. There are many additional features of JExcel that can be used to create an even more refined and well formatted excel. This article gives you a headstart. Refer to the following links in the references to get going faster.

References

  1. JExcel download link
  2. JExcel function and object reference
  3. Complete JExcel programming guide
  4. Apache POI library(alternate to JExcel API) – download
  5. Apache POI development guide and examples
  6. What is the better API to Reading Excel sheets in java – JXL or Apache POI

About the Author

author image
Datsabk
A technically sound person, oriented towards learning things logically rather than technically. It is my logical approach that has helped me learn and take up any programming language and start with coding. With a responsibility of Java based Web development professionally, I highly believe in imparting knowledge online for any technical subject that I can handle.

Comments

avatar
6 Comment threads
3 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
7 Comment authors
sai kishore pashamshashikumarrichieHarshNadeem Recent comment authors
newest oldest most voted
shashikumar
Guest
shashikumar

Someone please help me, i am not able create workbook
java.io.FileNotFoundException: C:\MyFirstExcel.xls (Access is denied)
at java.io.FileOutputStream.open0(Native Method)
at java.io.FileOutputStream.open(Unknown Source)
at java.io.FileOutputStream.(Unknown Source)
at java.io.FileOutputStream.(Unknown Source)
at jxl.Workbook.createWorkbook(Workbook.java:301)
at jxl.Workbook.createWorkbook(Workbook.java:286)
at ExcelWrite_shashikumar.main(ExcelWrite_shashikumar.java:21)

am using Win10 OS and jxl jar

sai kishore pasham
Guest
sai kishore pasham

jxl is old api, you should try with apache poi api might be your problem solved

if I wrong please inform me

richie
Guest
richie

is jexcel used to open excel file in UI and allows simultaneous operations between excel and UI in java?

Harsh
Guest
Harsh

I get FileNotFoundException when I use same code to create a file. Should there be an excel file with same name already created?

sai kishore pasham
Guest
sai kishore pasham

I think you should try with absolute path

sai kishore pasham
Guest
sai kishore pasham

while writing data into file, even though file is not already available no problem. but while reading data from file pre existence of file is mandatory

Nadeem
Guest
Nadeem

Actually, i want to scan excel file on java .
can u guide me

Nagarajan
Guest
Nagarajan

My Requirement is make the excel file with readonly.

Ajmal Khan Akb
Guest
Ajmal Khan Akb

I want to preview the excel file using java, and then allowing option to save it. I need to show the excel file in jframe or any other way so that I could just preview it and ask the user to save that