[Java] Creating an Excel file using Apache POI

Introduction

I will output from what I have learned at the company on a daily basis. This time, create an Excel file (.xlsx) using "Apache POI".

What is Apache POI?

Advance preparation

Prepare the library from the following URL. This time, I will use the latest version "poi-bin-3.17-20170915". (As of December 2017)

• Apache POI
  • URL : https://poi.apache.org/download.html

Mainly used class

--WorkBook class (for workbooks) --Sheet class (handles sheets) --Row class (handles rows) --Cell class (handles cells) --CellStyle class (handles cell style)

There are two formats for these classes, "HSSF" and "XSSF".

What are "HSSF" and "XSSF"?

It refers to the Excel file format.

Creating an Excel file

workbook

python

/**
 *Creating a workbook
*/

// HSSF
HSSFWorkbook workbook1 = new HSSFWorkbook();

// XSSF
XSSFWorkbook workbook2 = new XSSFWorkbook();

// Workbook (Common interface for HSSFWorkbook and XSSFWorkbook classes)
Workbook workbook3 = new HSSFWorkbook();

Workbook workbook4 = new XSSFWorkbook();

Sheet

python

/**
 　*Creating a sheet, specifying a sheet
*/

// HSSF
HSSFSheet sheet1 = workbook1.createSheet();

//Designation by sheet name
sheet1 = workbook1.getSheet("Sheet 1");

//Designated by index(Sheet numbers start at 0)
sheet1 = workbook1.getSheetAt(0);
	    
// XSSF
XSSFSheet sheet2 = workbook2.createSheet();
/*Sheet specification omitted*/
	    
// Sheet (Interface common to HSSFSheet class and XSSFSheet class)
Sheet sheet3 = workbook3.createSheet();
/*Sheet specification omitted*/

Sheet sheet4 = workbook4.createSheet();
/*Sheet specification omitted*/

line

python

/**
　　*Create line, specify line
*/

// HSSF (Line numbers start at 0)
HSSFRow row1 = sheet1.createRow(0);
	    
//Specifying a line(Line numbers start at 0)
row1 = sheet1.getRow(0);
	    
// XSSF (Line numbers start at 0)
XSSFRow row2 = sheet2.createRow(0);
/*Line specification omitted*/
	    
// Row (Interface common to HSSFRow and XSSFRow classes)
Row row3 = sheet3.createRow(0);
/*Line specification omitted*/
	    
Row row4 = sheet4.createRow(0);
/*Line specification omitted*/

cell

python

/**
　　*Create cell, specify cell, set value in cell
*/

// HSSF (Cell numbers start at 0)
HSSFCell cell1 = row1.getCell(0);
	    
//Cell specification(Cell numbers start at 0)
cell1 = row1.getCell(0);
	    
//Set value in cell(Set string)
/*
　　*Data types that can be set in cells
　　*Boolean type: setCellValue(boolean value) 
　　*String type: setCellValue(java.lang.String value)
　　*RichTextString type: setCellValue(RichTextString value)
　　*Calendar type: setCellValue(java.util.Calendar value)
　　*Date type: setCellValue(java.util.Date value)
　　*Double type: setCellValue(double value) 
*/
cell1.setCellValue("Test 1");
	    
// XSSF (Cell numbers start at 0)
XSSFCell cell2 = row2.getCell(0);
/*Cell specification / cell value setting omitted*/
	    
// Cell (Interface common to HSSFCell and XSSFCell classes)
Cell cell3 = row3.getCell(0);
/*Cell specification / cell value setting omitted*/
	    
Cell cell4 = row4.getCell(0);
/*Cell specification / cell value setting omitted*/

Cell style

python

/**
　*Create cell style, specify cell style, set value for cell style
*/

// HSSF
HSSFCellStyle cellstyle1 = workbook1.createCellStyle();
	    
//Set a value for the cell style(font)
Font font = workbook1.createFont();
font.setFontName("MS gothic");
font.setColor(IndexedColors.RED.getIndex());
font.setFontHeightInPoints((short)14);
cellstyle1.setFont(font);
	    
//Cell style specification
cell1.setCellStyle(cellstyle1);
	    
// XSSF
XSSFCellStyle cellstyle2 = workbook2.createCellStyle();
/*Specifying the cell style and setting the value of the cell style are omitted.*/
	    
// CellStyle (Interface common to HSSFCellStyle class and XSSFCellStyle class)
CellStyle cellstyle3 = workbook3.createCellStyle();
/*Specifying the cell style and setting the value of the cell style are omitted.*/
	    
CellStyle cellstyle4 = workbook4.createCellStyle();
/*Specifying the cell style and setting the value of the cell style are omitted.*/

Please refer to the URL below because the cell style is large. URL : https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/CellStyle.html

Excel file output

python

/**
 *Excel file output
*/

//Output file settings
FileOutputStream outExcelFile = null;	    
String outputPath = "File path you want to output";
String fileName = "test.xlsx";

try{
     //Output file
     outExcelFile = new FileOutputStream(outputPath + fileName);
     workbook1.write(outExcelFile);
}catch(Exception e){
     System.out.println(e.toString());
}finally{
     try {
	    outExcelFile.close();
     }catch(Exception e){
	    System.out.println(e.toString());
     }
}

Tried to make it

This time, I tried to output "multiplication table" in Excel. I just want to display it in Excel, so I made it quite forcibly. Please note.

python

package apache_poi.kuku;

import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;



/**
 *Output the multiplication table to an excel file
 * @author s-tsuchida
 *
 */
public class Kuku_Poi {
	
	public static void main(String[] args) {
		
		//workbook
		XSSFWorkbook workBook = null;
		//Sheet
		XSSFSheet sheet = null;
		//Output file
		FileOutputStream outPutFile = null;
		//Output file path
		String outPutFilePath = null;
		//Output file name
		String outPutFileName = null;
		
		//Creating an excel file
		try {
			
			//Creating a workbook
			workBook = new XSSFWorkbook();
			
			//Sheet settings
			sheet = workBook.createSheet();
			workBook.setSheetName(0, "Multiplication table");
			sheet = workBook.getSheet("Multiplication table");
			
			//Create initial line
			XSSFRow row = sheet.createRow(2);
			
			//"Title" cell style settings
			XSSFCellStyle titleCellStyle = workBook.createCellStyle();
			XSSFCell cell = row.createCell(7);
			XSSFFont titleFont = workBook.createFont();
			titleFont.setFontName("MS gothic");
			titleFont.setFontHeightInPoints((short)36);
			titleFont.setUnderline(XSSFFont.U_SINGLE);
			titleCellStyle.setFont(titleFont);
			cell.setCellStyle(titleCellStyle);
			
			//Set "Title" in cell
			cell.setCellValue("Multiplication table");
			
			//Cell style settings for "Table Header"
			XSSFCellStyle headerCellStyle = workBook.createCellStyle();
			XSSFFont headerFont = workBook.createFont();
			headerFont.setFontName("MS gothic");
			headerFont.setFontHeightInPoints((short)25);
			headerCellStyle.setFont(headerFont);
			headerCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			headerCellStyle.setAlignment(HorizontalAlignment.CENTER);
			headerCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.index);
			headerCellStyle.setBorderTop(BorderStyle.MEDIUM);
			headerCellStyle.setBorderBottom(BorderStyle.MEDIUM);
			headerCellStyle.setBorderRight(BorderStyle.MEDIUM);
			headerCellStyle.setBorderLeft(BorderStyle.MEDIUM);
			
			//Set "table header" in cell
			row = sheet.createRow(5);
			//side
			for(int i = 3 , j = 0; i < 13 ; i++, j++) {
				cell = row.createCell(i);
				cell.setCellStyle(headerCellStyle);
				if(i == 3) {
					cell.setCellValue("");
				}else {
					cell.setCellValue(j);
				}
			}
			//Vertical
			for(int i = 6 , j = 1 ; i < 15 ; i++, j++) {
				row = sheet.createRow(i);
				cell = row.createCell(3);
				cell.setCellStyle(headerCellStyle);
				cell.setCellValue(j);
			}
			
			//Cell style setting of "calculation result"
			XSSFCellStyle resultCellStyle = workBook.createCellStyle();
			XSSFFont resultFont = workBook.createFont();
			resultFont.setFontName("MS gothic");
			resultFont.setFontHeightInPoints((short)25);
			resultCellStyle.setFont(resultFont);
			resultCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			resultCellStyle.setAlignment(HorizontalAlignment.CENTER);
			resultCellStyle.setFillForegroundColor(IndexedColors.WHITE.index);
			resultCellStyle.setBorderTop(BorderStyle.MEDIUM);
			resultCellStyle.setBorderBottom(BorderStyle.MEDIUM);
			resultCellStyle.setBorderRight(BorderStyle.MEDIUM);
			resultCellStyle.setBorderLeft(BorderStyle.MEDIUM);
			
			//Set "table header" in cell
			double num1 = 0;
			double num2 = 0;
			double result = 0;
			for(int i = 6 ; i < 15 ; i++) {
				for(int j = 4 ; j < 13 ; j++) {
					//Multiplication table calculation
					num1 = sheet.getRow(5).getCell(j).getNumericCellValue();
					num2 = sheet.getRow(i).getCell(3).getNumericCellValue();
					result = num1 * num2;

					row = sheet.getRow(i);
					cell = row.createCell(j);
					cell.setCellStyle(resultCellStyle);
					cell.setCellValue(result);
				}
			}
			
			//Output excel file
			try {
				
				//Get the current date
				Date date = new Date();
				SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
				
				//Specifying the file path and file name
				outPutFilePath = "File path you want to output";
				outPutFileName = "kuku_" + dateFormat.format(date).toString() +  ".xlsx";
				
				//Output excel file
				outPutFile = new FileOutputStream(outPutFilePath + outPutFileName);
				workBook.write(outPutFile);
				
				System.out.println("「" + outPutFilePath + outPutFileName + "Was output.");
				
			}catch(IOException e) {
				System.out.println(e.toString());
			}
			
		}catch(Exception e) {
			System.out.println(e.toString());
		}
	
	}

}

Summary

This time, I used "Apache POI" for the first time, I felt that the operation was easier and easier to use than I expected. I can't deny the feeling of groping, so I will continue to study.