If you are a Java application developer working with Excel from within your Java applications, you would be interested in providing the functionality of manipulating Excel data in your application. Excel lets you format cell styling, set the type of a cell to different data types, fill a cell with different colors, and so on. Apache POI serves as your bridge between Java and Excel, offering tools to manipulate Excel XLSX files programmatically. In this blog series, we’ll unveil the art of Excel cell manipulation using Apache POI in Java. Let’s dive in and discover the synergy of code and spreadsheets!

Table of Contents

Throughout the blog, we’ll go through code samples and explanation of the same for the following:

  1. Creating Cells in Excel File
  2. Create Date Cells in Excel
  3. Working with Different Types of Cells
  4. Setting Cell Appearance

Working with Cells in Excel File in Java

Creating Cells in Excel File

//Create Cells
XSSFWorkbook wb = new XSSFWorkbook();
XSSFCreationHelper createHelper = wb.getCreationHelper();
XSSFSheet sheet = wb.createSheet("new sheet");
// Create a row and put some cells in it. Rows are 0 based.
Row row = sheet.createRow(0);
// Create a cell and put a value in it.
Cell cell = row.createCell(0);
cell.setCellValue(1);
row.createCell(1).setCellValue(1.2);
row.createCell(2).setCellValue(createHelper.createRichTextString("This is a string"));
row.createCell(3).setCellValue(true);
// Write the output to a file
try (FileOutputStream fileOut = new FileOutputStream("workbook.xlsx")) {
    wb.write(fileOut);
}

The above Java code snippet demonstrates the usage of the Apache POI API to work with cells in an Excel workbook. The code creates a new Excel workbook (`XSSFWorkbook`), initializes a sheet within the workbook, and then populates cells with various types of data. First, the code creates a new sheet named “new sheet“. It then generates a row at index 0 on the sheet and proceeds to fill the cells within that row. The first cell (index 0) is assigned an integer value of 1 using the `setCellValue` method. In contrast, the second cell (index 1) is set to a decimal value of 1.2 directly within the `createCell` method. The third cell (index 2) contains a string value “This is a string” created using the `createRichTextString` method from the `XSSFCreationHelper` instance. Lastly, the fourth cell (index 3) is populated with the boolean value “true”. After the cell values are set, the code writes the resulting workbook to an Excel file named “workbook.xlsx” using a `FileOutputStream`.

Creating Date Type Cell

XSSFWorkbook wb = new XSSFWorkbook();
//Workbook wb = new XSSFWorkbook();
CreationHelper createHelper = wb.getCreationHelper();
org.apache.poi.ss.usermodel.Sheet sheet = wb.createSheet("new sheet");
// Create a row and put some cells in it. Rows are 0 based.
Row row = sheet.createRow(0);
// Create a cell and put a date value in it.  The first cell is not styled
// as a date.
Cell cell = row.createCell(0);
cell.setCellValue(new Date());
// we style the second cell as a date (and time).  It is important to
// create a new cell style from the workbook otherwise you can end up
// modifying the built in style and effecting not only this cell but other cells.
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(
    createHelper.createDataFormat().getFormat("m/d/yy h:mm"));
cell = row.createCell(1);
cell.setCellValue(new Date());
cell.setCellStyle(cellStyle);
//you can also set date as java.util.Calendar
cell = row.createCell(2);
cell.setCellValue(Calendar.getInstance());
cell.setCellStyle(cellStyle);
// Write the output to a file
try (FileOutputStream fileOut = new FileOutputStream("workbook.xlsx")) {
    wb.write(fileOut);
}

This Java code snippet showcases the usage of the Apache POI API for working with date-type cells in an Excel workbook. The code begins by creating a new Excel workbook (`XSSFWorkbook`).

The first cell (index 0) is intended to hold a date value. A `Date` object representing the current date and time is set as the cell’s value using the `setCellValue` method. This cell is not explicitly styled as a date.

For the second cell (index 1), a new cell style (`CellStyle`) is created using the `createCellStyle` method of the workbook. This style is then configured to have a specific date and time format using the `setDataFormat` method. The `createDataFormat().getFormat(“m/d/yy h:mm”)` process creates a custom date format with a format pattern of “m/d/yy h:mm”. The second cell is assigned the current date and time value, and the newly created cell style is applied to it using the `setCellStyle` method.

The third cell (index 2) demonstrates an alternative method to populate date-type cells using `java.util.Calendar`. Similar to the second cell, the custom cell style is applied to this cell as well.

Working with Different Types of Cells

XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet("new sheet");
Row row = sheet.createRow(2);
row.createCell(0).setCellValue(1.1);
row.createCell(1).setCellValue(new Date());
row.createCell(2).setCellValue(Calendar.getInstance());
row.createCell(3).setCellValue("a string");
row.createCell(4).setCellValue(true);
row.createCell(5).setCellType(CellType.ERROR);		// Write the output to a file
try (FileOutputStream fileOut = new FileOutputStream("workbook.xlsx")) {
    wb.write(fileOut);
}

In this code sample, different cell styles are applied to cells in an Excel file using the Apache POI API as explained in the following steps.

  1. A row is created at index 2 on the sheet using `createRow(2)`. Cells are then populated within this row with the following data.
  2. The first cell (index 0) is assigned a decimal value of 1.1 using the `setCellValue` method.
  3. In the second cell (index 1), the current date and time are inserted using a `Date` object via the `setCellValue` method.
  4. The third cell (index 2) contains the date and time from a `Calendar` instance, set using the `setCellValue` method.
  5. The fourth cell (index 3) holds the text “a string”.
  6. The fifth cell (index 4) is designated for a boolean value “true”.
  7. Moving forward, a specific cell type is assigned to the sixth cell (index 5) by invoking the `setCellType` method. This cell is configured as an error cell using the `CellType.ERROR` enumeration, which indicates that it will display an error value.

Finally, the modified workbook is written to a file named “workbook.xlsx” using a `FileOutputStream`. In summary, this code example demonstrates how to establish an Excel workbook, apply different cell styles to accommodate various data types, and ultimately save the modified workbook to a file. This practical illustration showcases the capabilities of the Apache POI library for working with Excel files in Java.

Setting Cell Appearance

XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet("new sheet");
// Create a row and put some cells in it. Rows are 0 based.
Row row = sheet.createRow(1);
// Aqua background
CellStyle style = wb.createCellStyle();
style.setFillBackgroundColor(IndexedColors.AQUA.getIndex());
style.setFillPattern(FillPatternType.BIG_SPOTS);
Cell cell = row.createCell(1);
cell.setCellValue("X");
cell.setCellStyle(style);
// Orange "foreground", foreground being the fill foreground not the font color.
style = wb.createCellStyle();
style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cell = row.createCell(2);
cell.setCellValue("X");
cell.setCellStyle(style);
// Write the output to a file
try (FileOutputStream fileOut = new FileOutputStream("workbook.xlsx")) {
    wb.write(fileOut);
}
wb.close();

This code snippet demonstrates how to manipulate the appearance of cells in an Excel file using the Apache POI library in Java. This is achieved using the following steps.

  1. A row is created at index 1 on the sheet using `createRow(1)`. The following changes are made to the cells within this row.
  2. For the first cell (index 1), a custom cell style is created using `wb.createCellStyle()`. This style is configured with an aqua background color using `setFillBackgroundColor` and `IndexedColors.AQUA.getIndex()`. The pattern for the background is set to “big spots” using `setFillPattern(FillPatternType.BIG_SPOTS)`. A cell is created in this row at index 1 and filled with the value “X”. The previously created style is applied to this cell using `setCellStyle`.
  3. For the second cell (index 2), a new cell style is created. This style is designed with an orange foreground color using `setFillForegroundColor` and `IndexedColors.ORANGE.getIndex()`. The fill pattern is set to “solid foreground” using `setFillPattern(FillPatternType.SOLID_FOREGROUND)`. Another cell is created at index 2 in the same row and populated with the value “X”. The newly generated style is then assigned to this cell using `setCellStyle`.

After configuring the cell appearances, the modified workbook is written to a file named “workbook.xlsx” using a `FileOutputStream`.

In summary, this code example showcases how to use the Apache POI library to manipulate cell appearances in an Excel file. The demonstrated techniques involve creating custom cell styles to control background and foreground colors, as well as fill patterns, providing flexibility in visually customizing Excel spreadsheets using Java.

Conclusion

In all the above Java examples for the Apache POI library, we have demonstrated how to work with Excel file contents from within your Java application. The code samples showed how to create cells in an Excel file, set the appearance of these, set cell style to different data types such as string, numeric, decimal, etc., and how set the cell type to date type. We’ll be adding more examples for working with Excel files using Apache POI for Java, so stay tuned.

See Also