NOW Hiring

Quick links

Share

This article demonstrate how to create and how to read Excel files with the Java Excel API.

1. Installation

Download the Java Excel library from the webpage

To use this library in your Java program add the lib jxl.jar to your classpath in your project. See Changing classpath in Eclipse.

2. Create an Excel Spreadsheet

Create a new Java project called de.vogella.java.excel. Create the de.vogella.java.excel.writer package and the following class.

package writer;

import java.io.File;
import java.io.IOException;
import java.util.Locale;

import jxl.CellView;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.UnderlineStyle;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;


public class WriteExcel {

        private WritableCellFormat timesBoldUnderline;
        private WritableCellFormat times;
        private String inputFile;

public void setOutputFile(String inputFile) {
        this.inputFile = inputFile;
        }

        public void write() throws IOException, WriteException {
                File file = new File(inputFile);
                WorkbookSettings wbSettings = new WorkbookSettings();

                wbSettings.setLocale(new Locale("en", "EN"));

                WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings);
                workbook.createSheet("Report", 0);
                WritableSheet excelSheet = workbook.getSheet(0);
                createLabel(excelSheet);
                createContent(excelSheet);

                workbook.write();
                workbook.close();
        }

        private void createLabel(WritableSheet sheet)
                        throws WriteException {
                // Lets create a times font
                WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10);
                // Define the cell format
                times = new WritableCellFormat(times10pt);
                // Lets automatically wrap the cells
                times.setWrap(true);

                // create create a bold font with unterlines
                WritableFont times10ptBoldUnderline = new WritableFont(
                                WritableFont.TIMES, 10, WritableFont.BOLD, false,
                                UnderlineStyle.SINGLE);
                timesBoldUnderline = new WritableCellFormat(times10ptBoldUnderline);
                // Lets automatically wrap the cells
                timesBoldUnderline.setWrap(true);

                CellView cv = new CellView();
                cv.setFormat(times);
                cv.setFormat(timesBoldUnderline);
                cv.setAutosize(true);

                // Write a few headers
                addCaption(sheet, 0, 0, "Header 1");
                addCaption(sheet, 1, 0, "This is another header");


        }

        private void createContent(WritableSheet sheet) throws WriteException,
                        RowsExceededException {
                // Write a few number
                for (int i = 1; i < 10; i++) {
                        // First column
                        addNumber(sheet, 0, i, i + 10);
                        // Second column
                        addNumber(sheet, 1, i, i * i);
                }
                // Lets calculate the sum of it
                StringBuffer buf = new StringBuffer();
                buf.append("SUM(A2:A10)");
                Formula f = new Formula(0, 10, buf.toString());
                sheet.addCell(f);
                buf = new StringBuffer();
                buf.append("SUM(B2:B10)");
                f = new Formula(1, 10, buf.toString());
                sheet.addCell(f);

                // now a bit of text
                for (int i = 12; i < 20; i++) {
                        // First column
                        addLabel(sheet, 0, i, "Boring text " + i);
                        // Second column
                        addLabel(sheet, 1, i, "Another text");
                }
        }

        private void addCaption(WritableSheet sheet, int column, int row, String s)
                        throws RowsExceededException, WriteException {
                Label label;
                label = new Label(column, row, s, timesBoldUnderline);
                sheet.addCell(label);
        }

        private void addNumber(WritableSheet sheet, int column, int row,
                        Integer integer) throws WriteException, RowsExceededException {
                Number number;
                number = new Number(column, row, integer, times);
                sheet.addCell(number);
        }

        private void addLabel(WritableSheet sheet, int column, int row, String s)
                        throws WriteException, RowsExceededException {
                Label label;
                label = new Label(column, row, s, times);
                sheet.addCell(label);
        }

        public static void main(String[] args) throws WriteException, IOException {
                WriteExcel test = new WriteExcel();
                test.setOutputFile("c:/temp/lars.xls");
                test.write();
                System.out
                                .println("Please check the result file under c:/temp/lars.xls ");
        }
}

I assume that the code is pretty much self-explaining. I tried to add lots of comments to make it easier to understand.For more complex examples have a look at the excellent documentation of the Java Excel API which is also part of the distribution.

3. Read an existing Excel Spreadsheet

Reuse the project "de.vogella.java.excel". Create a package "de.vogella.java.excelreader" and the following class "ReadExcel".

package reader;

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

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

public class ReadExcel {

        private String inputFile;

        public void setInputFile(String inputFile) {
                this.inputFile = inputFile;
        }

        public void read() throws IOException  {
                File inputWorkbook = new File(inputFile);
                Workbook w;
                try {
                        w = Workbook.getWorkbook(inputWorkbook);
                        // Get the first sheet
                        Sheet sheet = w.getSheet(0);
                        // Loop over first 10 column and lines

                        for (int j = 0; j < sheet.getColumns(); j++) {
                                for (int i = 0; i < sheet.getRows(); i++) {
                                        Cell cell = sheet.getCell(j, i);
                                        CellType type = cell.getType();
                                        if (type == CellType.LABEL) {
                                                System.out.println("I got a label "
                                                                + cell.getContents());
                                        }

                                        if (type == CellType.NUMBER) {
                                                System.out.println("I got a number "
                                                                + cell.getContents());
                                        }

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

        public static void main(String[] args) throws IOException {
                ReadExcel test = new ReadExcel();
                test.setInputFile("c:/temp/lars.xls");
                test.read();
        }

}

Create an excel spreadsheet and save it somewhere, e.g. c:/temp/lars.xls.

I assume that the code is pretty much self-explaining. I tried to add lots of comments to make it easier to understand.For more complex examples have a look at the excellent documentation of the Java Excel API which is also part of the distribution.

4. About this website

5. Links and Literature

5.1. Java Excel Resources

5.2. vogella GmbH training and consulting support

TRAINING SERVICE & SUPPORT

The vogella company provides comprehensive training and education services from experts in the areas of Eclipse RCP, Android, Git, Java, Gradle and Spring. We offer both public and inhouse training. Whichever course you decide to take, you are guaranteed to experience what many before you refer to as “The best IT class I have ever attended”.

The vogella company offers expert consulting services, development support and coaching. Our customers range from Fortune 100 corporations to individual developers.

Copyright © 2012-2016 vogella GmbH. Free use of the software examples is granted under the terms of the EPL License. This tutorial is published under the Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Germany license.

See Licence.