Reading and Writing Excel file in Java Using JExcel API

By | April 9, 2018

Reading and Writing Excel file in Java Using JExcel API – In this java tutorial, i will give you some example to read and write an excel file using JExcel API, a simple excel library for java and widely used for simple operations which do not involve a high level of formatting and complex formulas based operations.

Reading and Writing Excel file in Java Using JExcel API

P.S Tested with JExcel API – 2.6.12

1. Import JExcel library using Maven Depedency

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

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\\JavaFromToExcel.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();
                }
            }
        }
    }
}

 

3. Read Excel file

Example to read above Excel file.

ExcelRead.java
package com.cimblo;

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\\JavaFromToExcel.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.cimblo;

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\\JavaFromToExcel.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();
                }
            }
        }
    }
}

 

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. Apache POI library(alternate to JExcel API) – download
  4. Apache POI development guide and examples

One thought on “Reading and Writing Excel file in Java Using JExcel API

  1. Pingback: Reading and Writing Excel file in Java Using Apache POI ~ Cimblo.com

Leave a Reply

Your email address will not be published. Required fields are marked *