POIを使ってExcelを生成してみる

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.WorkbookUtil;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Main {
    public static void main(String[] args) {
        Workbook wb = new XSSFWorkbook();
        Sheet sheet = createSheet(wb);

        Row row1 = createRow(sheet, 0);
        Row row2 = createRow(sheet, 1);
        Row row3 = createRow(sheet, 2);

        createCell(row1, new Object[] { "ポテトチップス", 198, 2300 });
        createCell(row2, new Object[] { "iPhone", 39800, 20 });
        createCell(row3, new Object[] { "Android", 49800, 18 });

        XSSFFormulaEvaluator.evaluateAllFormulaCells((XSSFWorkbook)wb);
        createWorkbook(wb);
    }

    private static Sheet createSheet(Workbook workbook) {
        // 不適切な文字等がシート名に含まれているとIllegalArgumentExceptionが発生
        Sheet sheet = workbook.createSheet(WorkbookUtil.createSafeSheetName("[サンプル]"));
        return sheet;
    }

    private static Row createRow(Sheet sheet, int row) {
        return sheet.createRow(row);
    }

    private static void createCell(Row row, Object[] values) {
        String itemName = (String)values[0];
        int    price    = (Integer)values[1];
        int    sales    = (Integer)values[2];

        row.createCell(0).setCellValue(itemName);
        row.createCell(1, HSSFCell.CELL_TYPE_NUMERIC).setCellValue(price);
        row.createCell(2, HSSFCell.CELL_TYPE_NUMERIC).setCellValue(sales);

        String cell1Name = CellReference.convertNumToColString(1); // B
        String cell2Name = CellReference.convertNumToColString(2); // C
        int    rowNum    = row.getRowNum() + 1;

        String formula   = String.format("%s%d*%s%d", cell1Name, rowNum, cell2Name, rowNum);

        row.createCell(3, HSSFCell.CELL_TYPE_FORMULA).setCellFormula(formula);
    }

    private static void createWorkbook(Workbook workbook) {
        OutputStream os = null;

        try {
            os = new FileOutputStream("out.xlsx");
            workbook.write(os);
            os.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

要点だけ書くと

• シートを作る時に不適切なシート名が指定されると例外が起きる
• セルで計算式等を使ってセルのデータを利用する場合において「A1*B1」等の表記が必要な場合にはCellReference.convertNumToColStringを使う事で表記名を取得可能
• XSSF(xslx)を使う場合においてはXSSFFormulaEvaluator.evaluateAllFormulaCellsを行わない場合には一部の計算が処理されない事がある