JExcel API – Reading and Writing Excel file in Java

By Datsabk | Updated: December 15, 2016

Viewed: 7,177 (+54 pv/w)

Tags: excel jexcel

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.

   <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.

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 :

3. Read Excel file

Example to read above Excel file.

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:

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

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