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

Wayan

7 Comments

    • Hi Jingah,

      Which version of Apache POI are you using? In the version 3.15 Javadoc the font.setBoldweight() method was marked as deprecated as of 3.15 beta 2 and will be removed in version 3.17.

      Reply
    • Hi Alpesh,

      Can you try something like:

      DataFormat format = workbook.createDataFormat();
      HSSFCellStyle style = workbook.createCellStyle();
      style.setDataFormat(format.getFormat("#,##0.00"));
      
      cell.setCellStyle(style);
      
      Reply
  1. Hello,

    I’m trying to put a formula into a cell and sometimes the formula is calculated and another times, no. The formula is concatenate several cells. When I check in Excel the Type of Cell, Excel says that is text , How can I force the cell to have the type “General”?

    Thank you very much.

    Raúl Redondo

    Reply

Leave a Reply to JingahCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.