DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Generative AI has transformed nearly every industry. How can you leverage GenAI to improve your productivity and efficiency?

SBOMs are essential to circumventing software supply chain attacks, and they provide visibility into various software components.

Related

  • How to Convert XLS to XLSX in Java
  • How to Edit a PowerPoint PPTX Document in Java
  • How to Merge Excel XLSX Files in Java
  • Memory Leak Due To Mutable Keys in Java Collections

Trending

  • Advancing DSLs in the Age of GenAI
  • Debug Like a Pro in 2025: 10 New Eclipse Java Debugger Features to Enhance Your Productivity (With Spring Boot Examples)
  • Exploring Data Redaction Enhancements in Oracle Database 23ai
  • When Caches Collide: Solving Race Conditions in Fare Updates
  1. DZone
  2. Coding
  3. Java
  4. Easy Excel Report Creation in Java With Apache POI

Easy Excel Report Creation in Java With Apache POI

If you're not working with huge datasets, this source code will get Excel spreadsheets up and running in minutes without the need for CSV.

By 
Radek Hecl user avatar
Radek Hecl
·
May. 16, 17 · Tutorial
Likes (17)
Comment
Save
Tweet
Share
49.0K Views

Join the DZone community and get the full member experience.

Join For Free

This tutorial is here for people who want to create Excel reports in Java. You will find an example together with all you need to include into your codebase within. You should be ready to go in 15 minutes.

Let's go straight to the point and see the example of the generated report.

Image title

And here is the code that created it.

byte[] report = new XlsxBuilder().
        startSheet("Dream cars").                            // start with sheet
        startRow().                                          // then go row by row
        setRowTitleHeight().                                 // set row style, add borders and so on 
        addTitleTextColumn("Dream cars").                    // add columns
        startRow().                                          // another row
        setRowTitleHeight().                                 // row styling
        setRowThinBottomBorder().
        addBoldTextLeftAlignedColumn("Dreamed By:").
        addTextLeftAlignedColumn("John Seaman").
        startRow().                                          // empty row
        startRow().                                          // header row
        setRowTitleHeight().
        setRowThickTopBorder().
        setRowThickBottomBorder().
        addBoldTextCenterAlignedColumn("Type").
        addBoldTextCenterAlignedColumn("Color").
        addBoldTextCenterAlignedColumn("Reason").
        startRow().                                          // rows with records (usually within a loop)
        addTextLeftAlignedColumn("Ferrari").
        addTextLeftAlignedColumn("Green").
        addTextLeftAlignedColumn("It looks nice").
        startRow().
        addTextLeftAlignedColumn("Lamborgini").
        addTextLeftAlignedColumn("Yellow").
        addTextLeftAlignedColumn("It's fast enough").
        startRow().
        addTextLeftAlignedColumn("Bugatti").
        addTextLeftAlignedColumn("Blue").
        addTextLeftAlignedColumn("Price is awesome").
        startRow().
        setRowThinTopBorder().
        startRow().
        startRow().                                          // footer row
        addTextLeftAlignedColumn("This is just a footer and I use it instead of 'Lorem ipsum dolor...'").
        setColumnSize(0, "XXXXXXXXXXXXX".length()).          // setting up column sizes at the end of the sheet
        setAutoSizeColumn(1).
        setAutoSizeColumn(2).
        build();
// now deal with byte array (e.g. write to the file or database)


If you like what have you seen and you want to have it ready in your project, then keep reading.

Xlsx Builder

Here is the source code of the XlsxBuilder unit. To make this work, you can just refactor it into your codebase and add the dependencies (which are below).

package com.enterprisemath.articles.poi;

import com.enterprisemath.utils.ValidationUtils;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.Arrays;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.builder.ToStringBuilder;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * Excel report builder.
 *
 * @author radek.hecl
 */
public class XlsxBuilder {

    /**
     * Underlined workbook.
     */
    private Workbook workbook;

    /**
     * Current sheet.
     */
    private Sheet sheet = null;

    /**
     * Current row.
     */
    private Row row = null;

    /**
     * Next row index.
     */
    private int nextRowIdx = 0;

    /**
     * Style attributes for row.
     */
    private Set<StyleAttribute> rowStyleAttributes;

    /**
     * Next column index.
     */
    private int nextColumnIdx = 0;

    private Map<Set<StyleAttribute>, CellStyle> styleBank = new HashMap<Set<StyleAttribute>, CellStyle>();

    /**
     * Creates new instance.
     */
    public XlsxBuilder() {
        workbook = new XSSFWorkbook();
    }

    /**
     * Starts sheet.
     *
     * @param name sheet name
     * @return this instance
     */
    public XlsxBuilder startSheet(String name) {
        sheet = workbook.createSheet(name);
        nextRowIdx = 0;
        nextColumnIdx = 0;
        rowStyleAttributes = new HashSet<StyleAttribute>();
        return this;
    }

    /**
     * Sets auto sizing columns.
     *
     * @param idx column index, starting from 0
     * @return this instance
     */
    public XlsxBuilder setAutoSizeColumn(int idx) {
        sheet.autoSizeColumn(idx);
        return this;
    }

    /**
     * Sets column size.
     *
     * @param idx column index, starting from 0
     * @param m number of 'M' standard characters to use for size calculation
     * @return this instance
     */
    public XlsxBuilder setColumnSize(int idx, int m) {
        sheet.setColumnWidth(idx, (m + 1) * 256);
        return this;
    }

    /**
     * Starts new row.
     *
     * @return this instance
     */
    public XlsxBuilder startRow() {
        row = sheet.createRow(nextRowIdx);
        nextRowIdx = nextRowIdx + 1;
        nextColumnIdx = 0;
        rowStyleAttributes = new HashSet<StyleAttribute>();
        return this;
    }

    /**
     * Sets row top border as thin.
     *
     * @return this instance
     */
    public XlsxBuilder setRowThinTopBorder() {
        ValidationUtils.guardEquals(0, nextColumnIdx, "must be called before inserting columns");
        row.setRowStyle(getCellStyle(StyleAttribute.THIN_TOP_BORDER));
        rowStyleAttributes.add(StyleAttribute.THIN_TOP_BORDER);
        return this;
    }

    /**
     * Sets row top border as thick.
     *
     * @return this instance
     */
    public XlsxBuilder setRowThickTopBorder() {
        ValidationUtils.guardEquals(0, nextColumnIdx, "must be called before inserting columns");
        row.setRowStyle(getCellStyle(StyleAttribute.THICK_TOP_BORDER));
        rowStyleAttributes.add(StyleAttribute.THICK_TOP_BORDER);
        return this;
    }

    /**
     * Sets row bottom border as thin.
     *
     * @return this instance
     */
    public XlsxBuilder setRowThinBottomBorder() {
        ValidationUtils.guardEquals(0, nextColumnIdx, "must be called before inserting columns");
        row.setRowStyle(getCellStyle(StyleAttribute.THIN_BOTTOM_BORDER));
        rowStyleAttributes.add(StyleAttribute.THIN_BOTTOM_BORDER);
        return this;
    }

    /**
     * Sets row bottom border as thick.
     *
     * @return this instance
     */
    public XlsxBuilder setRowThickBottomBorder() {
        ValidationUtils.guardEquals(0, nextColumnIdx, "must be called before inserting columns");
        row.setRowStyle(getCellStyle(StyleAttribute.THICK_BOTTOM_BORDER));
        rowStyleAttributes.add(StyleAttribute.THICK_BOTTOM_BORDER);
        return this;
    }

    /**
     * Sets row height to capture the title.
     *
     * @return this instance
     */
    public XlsxBuilder setRowTitleHeight() {
        ValidationUtils.guardEquals(0, nextColumnIdx, "must be called before inserting columns");
        row.setHeightInPoints(30);
        return this;
    }

    /**
     * Adds title column.
     *
     * @param text text
     * @return this instance
     */
    public XlsxBuilder addTitleTextColumn(String text) {
        Cell cell = row.createCell(nextColumnIdx);
        CellStyle style = getCellStyle(StyleAttribute.TITLE_SIZE, StyleAttribute.BOLD);
        cell.setCellStyle(style);
        cell.setCellValue(StringUtils.stripToEmpty(text));
        nextColumnIdx = nextColumnIdx + 1;
        return this;
    }

    /**
     * Adds simple left aligned text.
     *
     * @param text text
     * @return this instance
     */
    public XlsxBuilder addTextLeftAlignedColumn(String text) {
        Cell cell = row.createCell(nextColumnIdx);
        CellStyle style = getCellStyle(StyleAttribute.ALIGN_LEFT);
        cell.setCellStyle(style);
        cell.setCellValue(StringUtils.stripToEmpty(text));
        nextColumnIdx = nextColumnIdx + 1;
        return this;
    }

    /**
     * Adds simple center aligned text.
     *
     * @param text text
     * @return this instance
     */
    public XlsxBuilder addTextCenterAlignedColumn(String text) {
        Cell cell = row.createCell(nextColumnIdx);
        CellStyle style = getCellStyle(StyleAttribute.ALIGN_CENTER);
        cell.setCellStyle(style);
        cell.setCellValue(StringUtils.stripToEmpty(text));
        nextColumnIdx = nextColumnIdx + 1;
        return this;
    }

    /**
     * Adds simple center aligned text.
     *
     * @param val value
     * @return this instance
     */
    public XlsxBuilder addDoubleCenterAlignedColumn(double val) {
        Cell cell = row.createCell(nextColumnIdx);
        CellStyle style = getCellStyle(StyleAttribute.ALIGN_CENTER);
        cell.setCellStyle(style);
        cell.setCellValue(val);
        nextColumnIdx = nextColumnIdx + 1;
        return this;
    }

    /**
     * Adds bold left aligned text.
     *
     * @param text text
     * @return this instance
     */
    public XlsxBuilder addBoldTextLeftAlignedColumn(String text) {
        Cell cell = row.createCell(nextColumnIdx);
        CellStyle style = getCellStyle(StyleAttribute.ALIGN_LEFT, StyleAttribute.BOLD);
        cell.setCellStyle(style);
        cell.setCellValue(StringUtils.stripToEmpty(text));
        nextColumnIdx = nextColumnIdx + 1;
        return this;
    }

    /**
     * Adds bold center aligned text.
     *
     * @param text text
     * @return this instance
     */
    public XlsxBuilder addBoldTextCenterAlignedColumn(String text) {
        Cell cell = row.createCell(nextColumnIdx);
        CellStyle style = getCellStyle(StyleAttribute.ALIGN_CENTER, StyleAttribute.BOLD);
        cell.setCellStyle(style);
        cell.setCellValue(StringUtils.stripToEmpty(text));
        nextColumnIdx = nextColumnIdx + 1;
        return this;
    }

    /**
     * Builds the result object.
     * Object cannot be reused after calling this method.
     *
     * @return created object
     */
    public byte[] build() {
        ByteArrayOutputStream bos = null;
        try {
            bos = new ByteArrayOutputStream();
            workbook.write(bos);
            bos.close();
        } catch (IOException e) {
            throw new RuntimeException(e);
        } finally {
            IOUtils.closeQuietly(bos);
        }
        return bos.toByteArray();
    }

    /**
     * Returns cell style.
     *
     * @param attrs attributes
     * @return cell style
     */
    private CellStyle getCellStyle(StyleAttribute... attrs) {
        Set<StyleAttribute> allattrs = new HashSet<StyleAttribute>();
        allattrs.addAll(rowStyleAttributes);
        allattrs.addAll(Arrays.asList(attrs));
        if (styleBank.containsKey(allattrs)) {
            return styleBank.get(allattrs);
        }
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        style.setFont(font);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        for (StyleAttribute attr : allattrs) {
            if (attr.equals(StyleAttribute.TITLE_SIZE)) {
                font.setFontHeightInPoints((short) 18);
            }
            else if (attr.equals(StyleAttribute.BOLD)) {
                font.setBoldweight(Font.BOLDWEIGHT_BOLD);
            }
            else if (attr.equals(StyleAttribute.THIN_TOP_BORDER)) {
                style.setBorderTop(CellStyle.BORDER_THIN);
            }
            else if (attr.equals(StyleAttribute.THIN_BOTTOM_BORDER)) {
                style.setBorderBottom(CellStyle.BORDER_THIN);
            }
            else if (attr.equals(StyleAttribute.THICK_TOP_BORDER)) {
                style.setBorderTop(CellStyle.BORDER_THICK);
            }
            else if (attr.equals(StyleAttribute.THICK_BOTTOM_BORDER)) {
                style.setBorderBottom(CellStyle.BORDER_THICK);
            }
            else if (attr.equals(StyleAttribute.ALIGN_LEFT)) {
                style.setAlignment(CellStyle.ALIGN_LEFT);
            }
            else if (attr.equals(StyleAttribute.ALIGN_CENTER)) {
                style.setAlignment(CellStyle.ALIGN_CENTER);
            }
            else {
                throw new RuntimeException("unknown cell style attribute: " + attr);
            }
        }
        styleBank.put(allattrs, style);
        return style;
    }

    @Override
    public String toString() {
        return ToStringBuilder.reflectionToString(this);
    }

    /**
     * Possible style attributes.
     */
    private static enum StyleAttribute {

        /**
         * Thin top border.
         */
        THIN_TOP_BORDER,
        /**
         * Thin bottom border.
         */
        THIN_BOTTOM_BORDER,
        /**
         * Thick top border.
         */
        THICK_TOP_BORDER,
        /**
         * Thick bottom border.
         */
        THICK_BOTTOM_BORDER,
        /**
         * Title font size.
         */
        TITLE_SIZE,
        /**
         * Bold font.
         */
        BOLD,
        /**
         * Left alignment.
         */
        ALIGN_LEFT,
        /**
         * Center alignment.
         */
        ALIGN_CENTER

    };

}


And here are the Maven dependencies for compilation and running.

<dependency>
    <groupId>com.enterprisemath</groupId>
    <artifactId>em-utils</artifactId>
    <version>2.4.0</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.9</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.9</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>3.9</version>
</dependency>

Final Notes

Excel reporting works usually pretty well until the point you reach tens of thousands of rows. For larger data sets, it is better to use something like CSV. If you want to get unit tests for this unit, then please let me know. If you like this and would like ready-to-use code for another topic, then please let me know what you would like.

Enjoy!

Apache POI Java (programming language)

Opinions expressed by DZone contributors are their own.

Related

  • How to Convert XLS to XLSX in Java
  • How to Edit a PowerPoint PPTX Document in Java
  • How to Merge Excel XLSX Files in Java
  • Memory Leak Due To Mutable Keys in Java Collections

Partner Resources

×

Comments

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • [email protected]

Let's be friends: