Shoeisha Technology Media

CodeZine(コードジン)

記事種別から探す

「Usermodel API」を利用したExcelの計算式・図形操作

最新POIでOffice Open XML形式のExcelファイルを操作 第3回

  • LINEで送る
  • このエントリーをはてなブックマークに追加
2010/03/30 14:00

 JavaからMicrosoftのドキュメントを操作するためのAPI「POI」の最新版では、Office 2007形式のファイルも扱えるようになりました。本稿では前回に引き続き、名前の定義、計算式、図形や画像の利用用など、Usermodel APIの使用方法を確認していきます。

目次

はじめに

 POIはJavaからMicrosoftのドキュメントを操作するためのAPIで、2007年6月からApache Software Foundationのトップレベルプロジェクトに格上げされました。オープンソースとして提供されているので誰でも無償で利用することができます。

 なぜ初期のリリース後、7年以上も経過し、多くの情報が存在するPOIを今回あらためて取り上げることにしたかと言うと、2009年9月リリースの Version 3.5でOffice Open XML形式(以降、OOXML形式)への対応という大きな変更が加わったからです。

 分かりやすくいうと2007形式のExcel(xlsx)やWord(docx)のファイルも扱えるようになったということです。

 本連載では、POIの中からExcelを操作するコンポーネント(HSSF/XSSF)に対象を絞り、下記の予定で紹介していきます。

 また、本連載はOOXML形式のExcelファイルを基本として進めますが、Excel 2003以前のMicrosoft OLE2複合ドキュメント形式(以降OLE2形式)への対応方法についても随時紹介していきます。

対象読者

  • JavaでExcelファイルを操作したい方

名前の定義と計算式

 前回に引き続き今回もUsermodel APIの使用方法を確認していきましょう。POI3.5以降のAPIでは、OOXML形式Excelファイルでの計算式の利用もサポートされています。現時点でのサポート範囲はすべての算術演算と約100種類のワークシート関数です。サポートされるワークシート関数は今後も増加されていく予定ですが、ユーザー定義関数はまだサポートされていません。

 また、計算式を利用する場合、セルに名前(例:A10からL10までの範囲を「売上」とする)を付け、名前で特定のセルを参照できると便利です。POIでも「Nameインターフェース」や「XSSFNameクラス」を利用することで名前を利用したセルの参照ができます。Apache提供のサンプルプログラムを一部変更して作成した「簡易積立計画(Deposit Plan)」(1:目標貯金額、2:年率、3:積立年数を入力すると、目標貯金額に必要となる毎月の積立金額を算出します)から名前の定義と計算式の利用方法を確認してみましょう。なお、すべてのソースはページ上部からダウンロードできます。

Deposit Planの実行結果
Deposit Planの実行結果
ソース1
// 入力項目と数式の名前を定義します
public static void createNames(Workbook wb) {
	// (1) Nameインターフェースの宣言
	Name name;

	// (2)名前を定義して参照するセルを設定
	name = wb.createName();
	name.setNameName("Target_Amount");
	name.setRefersToFormula("'積立計画'!$E$4");

	name = wb.createName();
	name.setNameName("Interest_Rate");
	name.setRefersToFormula("'積立計画'!$E$5");

	name = wb.createName();
	name.setNameName("Deposit_Years");
	name.setRefersToFormula("'積立計画'!$E$6");

	name = wb.createName();
	name.setNameName("Number_of_Deposits");
	name.setRefersToFormula("'積立計画'!$E$10");

	name = wb.createName();
	name.setNameName("Total_Interest_Amount");
	name.setRefersToFormula("'積立計画'!$E$11");

	name = wb.createName();
	name.setNameName("Total_Deposit_Amount");
	name.setRefersToFormula("'積立計画'!$E$12");

	// (3) 名前を定義して参照する計算式を設定
	name = wb.createName();
	name.setNameName("Monthly_Deposit");
	name.setRefersToFormula("-PMT(Interest_Rate/12,Number_of_Deposits,0,Target_Amount)");

	name = wb.createName();
	name.setNameName("Values_Entered");
	name.setRefersToFormula("IF(Target_Amount*Interest_Rate*Deposit_Years>0,1,0)");
}
  • ソース1 -(1)
  •  SSパッケージが提供するNameインターフェースを宣言しています。

  • ソース1 -(2)
  •  ワークブックオブジェクトからXSSFNameまたはHSSFNameのオブジェクトを取得し、「Target_Amount」という名前で「積立計画」ワークシートの「E4」のセルを参照できるように設定しています。

  • ソース1 -(3)
  •  「Monthly_Deposit」という名前でワークシート関数「PMT」を利用した計算式の結果を参照できるよう設定しています。PMTの引数である「Interest_Rate」「Number_of_Deposits」も定義した名前であり、「積立計画」ワークシートの特定のセルを参照しています。「PMT」の前にある「-」は関数の計算結果がマイナスで算出されるため、プラスに直す目的で付けています。

 次に「Monthly_Deposit」の名前で定義した計算式の結果を表示するセル側のソースを確認してみましょう。

ソース2
// 毎月の積立金額
cell = row.createCell(4);
// (1) セルに計算式を設定
cell.setCellFormula("IF(Values_Entered,Monthly_Deposit,\"\")");
cell.setCellStyle(styles.get("formula_yen"));
  • ソース2 -(1)
  •  Cellインターフェースの「setCellFormula(java.lang.String formula)」メソッドを利用してソース1-(3)で定義した計算式「MonthlyDeposit」を設定しています。ここでは同じく名前の定義をした計算式「Values_Entered」の結果が真(=1)を返した場合に、「MonthlyDeposit」の結果がセルに表示されるようにしています。「Values_Entered」は入力項目の1:目標貯金額、2:年率、3:積立年数のすべてが入力されたことを簡易的に確認する目的で利用しています。このようにPOIではワークシート関数や算術演算を利用した計算式が容易に作成できます。なお、POIで計算式を作成する場合、計算式の前に「=」は付けてはいけません。


  • LINEで送る
  • このエントリーをはてなブックマークに追加

著者プロフィール

  • 土田 将人(ツチダ マサト)

    株式会社ビーブレイクシステムズにて業務システムのパッケージソフト(MA-EYES)の製品開発に従事。英語や会計等、システム開発以外のスキルを活かして官公庁やメーカ系のシステム開発で活躍中。少し前に、パッケージソフトMA-EYESの開発を通して「POI」を知り尽くし、勢い余って本稿の執筆に至る。

バックナンバー

連載:最新POIでOffice Open XML形式のExcelファイルを操作
All contents copyright © 2006-2017 Shoeisha Co., Ltd. All rights reserved. ver.1.5