試験管のなかのコード

[Library] JExcelAPI にチャレンジ

JExcelAPI とは

JExcelAPI は、 MS Excel の Workbook を Java で扱うライブラリ。 Workbook の読み込み/書き出しからシートの印刷設定まで様々な機能を持っている。 Jakarta POI も同様の機能を持っているが、POI は PowerPoint/Excel/Word に対応しているのに対し JExcelAPI は Excel だけに絞っているので Excel を使うだけなら JExcelAPI の方が楽。 何も手を入れなくても、日本語がきちんと通るみたいなので嬉しい。

JExcelAPI

http://jexcelapi.sourceforge.net/

JExcelAPI のチュートリアル

http://www.andykhan.com/jexcelapi/tutorial.html

JExcelAPI の実行環境構築

1. JExcelAPI(jxl.jar) をクラスパスに追加(ここではバージョン 2.5.8 を利用)。

サンプルコード

package jp.in_vitro.codelets.jexcelapi;

import java.io.File;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import jxl.BooleanCell;
import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.PageOrientation;
import jxl.format.PaperSize;
import jxl.read.biff.BiffException;
import jxl.write.Boolean;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCell;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class Codelet {

    public Codelet() {
        super();
    }

    public static void main(final String[] args) throws IOException,
            RowsExceededException, WriteException, BiffException {
        File inputFile = new File("c:\\template.xls");
        File outputFile = new File("c:\\output.xls");
        Map<String, String> data = new HashMap<String, String>();
        data.put("bookname", "Object Design");

        Codelet me = new Codelet();
        me.createBook(inputFile);
        me.mergeBook(inputFile, outputFile, data);
    }

    public void createBook(final File target) throws IOException,
            RowsExceededException, WriteException {

        WritableWorkbook book = Workbook.createWorkbook(target);
        WritableSheet sheet = book.createSheet("sheet0", 0);
        sheet.setPageSetup(PageOrientation.PORTRAIT, PaperSize.B5, 1.0, 1.0);

        // line 0
        sheet.addCell(new Label(0, 0, "TODO"));
        sheet.addCell(new Label(1, 0, "期限"));
        sheet.addCell(new Label(2, 0, "優先度"));
        sheet.addCell(new Label(3, 0, "完了"));
        // line 1
        sheet.addCell(new Label(0, 1, "ANSI COMMON LISP を読む"));
        sheet.addCell(new DateTime(1, 1, new Date(System.currentTimeMillis())));
        sheet.addCell(new Number(2, 1, 1));
        sheet.addCell(new Boolean(3, 1, false));
        // line 2
        sheet.addCell(new Label(0, 2, "${bookname} を読む"));
        sheet.addCell(new DateTime(1, 2, new Date(System.currentTimeMillis())));
        sheet.addCell(new Number(2, 2, 2));
        sheet.addCell(new Boolean(3, 2, false));

        book.write();
        book.close();
    }

    public void mergeBook(final File inputFile, final File outputFile,
            final Map<String, String> data) throws BiffException, IOException,
            RowsExceededException, WriteException {

        Workbook inputBook = Workbook.getWorkbook(inputFile);
        WritableWorkbook outputBook = Workbook.createWorkbook(outputFile);

        Sheet[] inputSheets = inputBook.getSheets();
        for (int k = 0; k < inputSheets.length; k++) {
            Sheet inputSheet = inputSheets[k];
            WritableSheet outputSheet = outputBook.createSheet(this.merge(
                    inputSheet.getName(), data), k);
            outputSheet.setPageSetup(PageOrientation.PORTRAIT, PaperSize.B5, 1.0, 1.0);

            for (int i = 0; i < inputSheet.getRows(); i++) {
                for (int j = 0; j < inputSheet.getColumns(); j++) {
                    Cell inputCell = inputSheet.getCell(j, i);
                    CellType inputCellType = inputCell.getType();
                    WritableCell outputCell = null;
                    if (inputCellType == CellType.LABEL) {
                        outputCell = new Label(j, i, this.merge(inputCell
                                .getContents(), data));
                    } else if (inputCellType == CellType.BOOLEAN) {
                        outputCell = new Boolean((BooleanCell) inputCell);
                    } else if (inputCellType == CellType.NUMBER) {
                        outputCell = new Number(j, i, new BigDecimal(inputCell
                                .getContents()).doubleValue());
                    } else if (inputCellType == CellType.DATE) {
                        outputCell = new DateTime(j, i, new Date());
                    }
                    outputSheet.addCell(outputCell);
                }
            }
        }

        outputBook.write();
        outputBook.close();
    }

    protected String merge(final String target, final Map<String, String> data) {

        if (target == null || "".equals(target)) {
            return "";
        }

        int prefixIndex = target.indexOf("${");
        int suffixIndex = target.indexOf("}");
        if (-1 < prefixIndex && prefixIndex < suffixIndex) {
            String key = target.substring(prefixIndex + "${".length(),
                    suffixIndex);
            String value = data.get(key);
            return target.replaceAll("\\$\\{" + key + "\\}", value);
        } else {
            return target;
        }
    }
}

Posted in Library | WriteBacks (0) | | | Bookmark this on Delicious