Quick introduction to Apache POI

Cells are used to store data in spreadsheet. This chapter shows way for manipulating the data in cells of spreadsheet using Java programming.

Create a Cell

Create row before adding a cell. A row is a collection of cells.

Here is an example how you can create a cell:

package com.sc;

import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

import java.io.FileOutputStream;

public class CreateCell {
    public static void main(String[] args) {
        // we are working with HSSFWorkbook
        Workbook wb = new HSSFWorkbook();
        // uncomment the following to work with 2007 format
        //Workbook wb = new XSSFWorkbook();
        CreationHelper createHelper = wb.getCreationHelper();
        Sheet sheet = wb.createSheet("Sheet1");

        // Create a row and put some cells in it. Rows are 0 based.
        Row row = sheet.createRow((short) 0);
        // Create a cell and put a value in it.
        Cell cell = row.createCell(0);
        cell.setCellValue("This is a demo");

        // Or do it on one line.
        row.createCell(1).setCellValue(1.2);
        RichTextString rText = new HSSFRichTextString("Rich text is supported");
        rText.applyFont((short)5);
        row.createCell(2).setCellValue(rText);
        row.createCell(3).setCellValue(true);
        try {
            // Write the output to a file
            FileOutputStream fileOut = new FileOutputStream("c:/poi/createcell.xls");
            wb.write(fileOut);
            fileOut.close();
        } catch (Exception e) {
            System.out.print(e.getMessage());
        }
    }
}

Save this code in CreateCell.java , compile and excute as shown below

$ javac CreateCell.java

$ java CreateCell

After Compiling and executing the program following output in command prompt.

Types of Cells

There are different type cells like numeric value, formula or strings. Given are the types of cells,type syntax and values.

Below code is an example that shows how to create different types of cells in spreadsheet

import java.io.File;
import java.io.FileOutputStream;
import java.util.Date;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class CellTypes {
	public static void main(String[] args) throws Exception {
		XSSFWorkbook workbook = new XSSFWorkbook();
		XSSFSheet spreadsheet = workbook.createSheet("CellSheet");
		XSSFRow row = spreadsheet.createRow((short) 1);
		row.createCell(0).setCellValue("Cell Types");
		row.createCell(1).setCellValue(1.1);
		row.createCell(2).setCellValue(new Date());
		row.createCell(3).setCellValue(Calendar.getInstance());
		row.createCell(4).setCellValue("a string");
		row.createCell(5).setCellValue(true);
		row.createCell(6).setCellType(Cell.CELL_TYPE_ERROR);
		try {
			// Write the output to a file
			FileOutputStream fileOut = new FileOutputStream(new File("c:/poi/workbook.xlsx"));
			workbook.write(fileOut);
			fileOut.close();
		} catch
	}
}

Make sure that you save the above program in CellTypes.java, Follow the steps below to compile and run this program. 

$javac TypesofCells.java
$java TypesofCells

Now you are able to see that workbook.xlsx file is created under c:\poi folder and will look something like this.

Let's now check how to add styles to your cells. Sometimes styles are required to highlight a cell, Also, cell Styles involve adding borders, merging adjacent cells, cell alignment and color filling.

To apply different styles to cells in a spreadsheet using below code.

Cell style Example

package com.sc;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;

public class CellStyles {
    public static void main(String[] args) {
        XSSFWorkbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet("Sheet1");

        // Create a row and put some cells in it. Rows are 0 based.
        Row row = sheet.createRow(1);

        // Create a cell and put a value in it.
        Cell cell = row.createCell((short)1);
        cell.setCellValue("This is just an example");

        // Let's style our cell border
        CellStyle style = wb.createCellStyle();
        style.setBorderBottom(CellStyle.BORDER_THICK);
        style.setBottomBorderColor(IndexedColors.GREEN.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THICK);
        style.setLeftBorderColor(IndexedColors.RED.getIndex());
        style.setBorderRight(CellStyle.BORDER_THICK);
        style.setRightBorderColor(IndexedColors.BLUE.getIndex());
        style.setBorderTop(CellStyle.BORDER_DASHED);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cell.setCellStyle(style);

        // let's add a cell with a orange background and align center
        // Create a second row
        row = sheet.createRow((short) 2);

        // orange background with fine dots
        style = wb.createCellStyle();
        style.setFillBackgroundColor(IndexedColors.ORANGE.getIndex());
        style.setFillPattern(CellStyle.LESS_DOTS);
        // cenctre alignment
        style.setAlignment(CellStyle.ALIGN_CENTER);
        cell = row.createCell((short) 2);
        cell.setCellValue("Cell with orange background");
        cell.setCellStyle(style);

        // merging cells
        row = sheet.createRow((short) 3);
        cell = row.createCell((short) 1);
        cell.setCellValue("Merging test ");

        sheet.addMergedRegion(new CellRangeAddress(
                3, //first row (0-based)
                3, //last row  (0-based)
                1, //first column (0-based)
                3  //last column  (0-based)
        ));


        try {
            // Write the output to a file
            FileOutputStream fileOut = new FileOutputStream("c:/poi/workbook_cellstyles.xlsx");
            wb.write(fileOut);
            fileOut.close();
        }
        catch(Exception e) {
            System.out.println(e.getMessage());
        }

    }
}

Save above program in CellStyles.java file, Compile and execute the code as shown below

$javac CellStyles.java
$java CellStyles

You will get something like this