Jasper ReportでExcelを作成した時にExcelの数式にする

やりたいこと

下の様に出力されるExcelを作ります.

	A	B	～	U	V	W	X
1	日付	曜日	～	EC	実店舗	その他	合計
2	1月1日	金	～	12	4		=sum(U2:W2)
3	1月2日	土	～	68	78		=sum(U3:W3)

システム化されているECサイト(U列)と実店舗(V列)での販売数量に加えて、紹介などのシステムを通さない販売数量(W列)を手入力し、これらの合計(X列)をExcelの数式によって求めるようにします。

jrxmlの記載

detailセクションに合計用のテキストフィールドを配置し、propertyExpressionのnameプロパティに net.sf.jasperreports.export.xls.formulaを指定し、値に目的の数式を記載します。

         <textField pattern="#,##0" isBlankWhenNull="true">
                <reportElement mode="Opaque" x="703" y="0" width="36" height="20" backcolor="#3399FF" uuid="XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX">
                    <propertyExpression name="net.sf.jasperreports.export.xls.formula"><![CDATA["SUM(U"+($V{COLUMN_COUNT}.intValue()+1 )+":W"+($V{COLUMN_COUNT}.intValue()+1 )+")"]]></propertyExpression>
                <box>
                    <topPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <leftPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <bottomPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                    <rightPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                </box>
                <textElement textAlignment="Right" verticalAlignment="Middle" markup="none">
                    <font size="6"/>
                    <paragraph rightIndent="1"/>
                </textElement>
                <textFieldExpression><![CDATA[Integer.valueOf(0)]]></textFieldExpression>
            </textField>

iReport DesignerのAdd/modify propertyダイアログでは下の様にします。 Excelのsum関数の引数を動的に作成するためにUse an expressionにチェックを付けました。

f:id:huruyosi:20160116113743p:plain

必要とする数式の作り方

propertyExpressionの評価結果が必要とする数式になるようにします。

静的な数式

行番号を指定しない数式を使うのであれば、Use an expressionにチェックを付ける必要はありません。たとえばoffset関数を使ってSUM(OFFSET(U2,ROW()-1,,,3))とすることで目的を達成することができますが、Excelで数式を確認した時に分かり難いと思います。

jrxmlではpropertyExpression の代わりに propertyを使いvalue属性に数式を記載します。

動的な数式

propertyExpression の式をJasperReportが評価した結果が目的の数式sum(U2:W2)になるようにします。数式を作ることを考えた時に、作成されたExcelでの行番号をJaper Reportでどのようにして表す方法を考える必要があります。

sourceforge.netのサンプル JasperReports 6.2.0 - XLS Formula Sampleを見ると後ろの方に説明がありました。

Here was used a instead of simple , and the formula expression is more complicated, and needs a CDATA section to be written. In a CDATA section could be used any dynamic expression we need, containing also variable names, parameter names, field names, and any other valid java expression. The value of A5 will be calculated by Excel when opening the generated .xls document, and for other output formats the 0 value will be exported.

必要そうな所だけ翻訳サイトで翻訳してみると

変数名、パラメータ名、フィールド名、および任意の他の有効なJavaの式を含む、私たちに必要なすべての動的表現を使用することができます。

だったので、Jaspoer Reportの変数を使ってJava的に文字列を作ればよさそうです。よく見ると、Add/modify propertyダイアログにexpression Editorを起動するボタンがあります。

f:id:huruyosi:20160116124150p:plain

Excelの行番号として使えそうなものといえば、現在のレコード番号を得られるCOLUMN_COUNTです。レコード数とExcelの番号を一致させるために、DataSourceの1レコード目がExcelの2行目になるように、$V{COLUMN_COUNT}.intValue()+1にしています。

今回作成したpropertyExpression の式"SUM(U"+($V{COLUMN_COUNT}.intValue()+1 )+":W"+($V{COLUMN_COUNT}.intValue()+1 )+")"は"SUM(U"+(現在のレコード数+1 )+":W"+(現在のレコード数+1)+")"という意味を持ちます。

huruyosi’s blog

プログラミングとかインフラとかのメモです。