How do I format cell style in Excel document?

This example demonstrate how to use HSSFCellStyle and HSSFFont to format the cell style in Excel document. Using this class we can define cell border, foreground and background color. We can also define the font we used to display the cell value.

package org.kodejava.poi;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;

import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelCellFormat {
    public static void main(String[] args) {
        // Create an instance of workbook and sheet
        try (HSSFWorkbook workbook = new HSSFWorkbook()) {
            HSSFSheet sheet = workbook.createSheet();

            // Create an instance of HSSFCellStyle which will be used to format the
            // cell. Here we define the cell top and bottom border, and we also
            // define the background color.
            HSSFCellStyle style = workbook.createCellStyle();
            style.setBorderTop(BorderStyle.DOUBLE);
            style.setBorderBottom(BorderStyle.THIN);
            style.setFillForegroundColor(
                    HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

            // We also define the font that we are going to use for displaying the
            // data of the cell. We set the font to ARIAL with 20pt in size and
            // make it BOLD and give blue as the color.
            HSSFFont font = workbook.createFont();
            font.setFontName(HSSFFont.FONT_ARIAL);
            font.setFontHeightInPoints((short) 20);
            font.setBold(true);
            font.setColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
            style.setFont(font);

            // We create a simple cell, set its value and apply the cell style.
            HSSFRow row = sheet.createRow(1);
            HSSFCell cell = row.createCell(1);
            cell.setCellValue(new HSSFRichTextString("Hi there... It's me again!"));
            cell.setCellStyle(style);
            sheet.autoSizeColumn((short) 1);

            // Finally, we write out the workbook into an Excel file.
            try (FileOutputStream fos = new FileOutputStream("ExcelDemo.xls")) {
                workbook.write(fos);
            } catch (IOException e) {
                e.printStackTrace();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

Maven Dependencies

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.2.3</version>
</dependency>

Maven Central

How do I create an Excel document using Apache POI?

This example demonstrate how to create an Excel document using Apache POI library. In this example we create a simple document containing two sheets which have a value on their first cell.

package org.kodejava.poi;

import org.apache.poi.hssf.usermodel.*;

import java.io.FileOutputStream;
import java.io.IOException;

public class CreateExcelDemo {
    public static void main(String[] args) {
        // Creating an instance of HSSFWorkbook.
        try (HSSFWorkbook workbook = new HSSFWorkbook()) {

            // Create two sheets in the Excel document and name it First Sheet and
            // Second Sheet.
            HSSFSheet firstSheet = workbook.createSheet("FIRST SHEET");
            HSSFSheet secondSheet = workbook.createSheet("SECOND SHEET");

            // Manipulate the first sheet by creating an HSSFRow which represent a
            // single row in Excel sheet, the first row started from 0 index. After
            // the row is created we create a HSSFCell in this first cell of the row
            // and set the cell value with an instance of HSSFRichTextString
            // containing the words FIRST SHEET.
            HSSFRow rowA = firstSheet.createRow(0);
            HSSFCell cellA = rowA.createCell(0);
            cellA.setCellValue(new HSSFRichTextString("FIRST SHEET"));

            HSSFRow rowB = secondSheet.createRow(0);
            HSSFCell cellB = rowB.createCell(0);
            cellB.setCellValue(new HSSFRichTextString("SECOND SHEET"));

            // To write out the workbook into a file we need to create an output
            // stream where the workbook content will be written to.
            try (FileOutputStream fos = new FileOutputStream("CreateExcelDemo.xls")) {
                workbook.write(fos);
            } catch (IOException e) {
                e.printStackTrace();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

Maven Dependencies

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.2.3</version>
</dependency>

Maven Central