huruyosi’s blog

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

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)+")"という意味を持ちます。

Dockerコンテナで稼動しているnginxを使ってLet's Encrypt の証明書を発行する - 実践

前回思いついたことを実際に試して見ました。

huruyosi.hatenablog.com

目次

実際にやってみる

webroot用のコンテナを作成

/usr/share/nginx/html/ディレクトリを管理するためのコンテナを作成します。 Dockerfileを

FROM centos:centos7

MAINTAINER huruyosi<huruyosi@example.co.jp>

RUN mkdir -p /usr/share/nginx/html
ADD nginx/usr/share/nginx/html/ /usr/share/nginx/html/
VOLUME ["/usr/share/nginx/html"]

にして、

$ sudo docker build -t nginx_data .
$ sudo docker run -it --name nginx_data nginx_data ls

を行ってnginx_dataという名前のコンテナを作成します。

nginxのコンテナの起動オプションを変更

$ /usr/bin/docker run -v /etc/letsencrypt/:/etc/letsencrypt/ --volumes-from nginx_data  -p 443:443 -p 80:80 --name nginx nginx:latest

--volumes-fromオプションを使ってwebroot用のデータコンテナをマウントします。また、ホストの/etc/letsencryptディレクトリもマウントしておきます。

更新用Let's Encryptのコンテナ作成

Dockerfileを

FROM centos:centos7

MAINTAINER huruyosi<huruyosi@example.co.jp>

# set timezone
RUN echo 'ZONE="Asia/Tokyo"' > /etc/sysconfig/clock
RUN rm -f /etc/localtime
RUN ln -fs /usr/share/zoneinfo/Asia/Tokyo /etc/localtime

RUN yum install -y git && \
    cd /root && \
    git clone https://github.com/letsencrypt/letsencrypt && \
    cd /root/letsencrypt && \
    ./letsencrypt-auto --help


ENTRYPOINT ["/root/letsencrypt/letsencrypt-auto"]
CMD ["certonly","--webroot","-w","/usr/share/nginx/html","-d","www.example.co.jp","--renew-by-default"]

letsencrypt-autoコマンドに--renew-by-defaultオプションを指定します。 これを docker build -t letsencrypt .でビルドします。

証明書の更新を行う

webroot用のデータコンテナとホストの/etc/letsencryptをマウントして実行します。letsencrypt-autoコマンドの引数はDockerfileの CMD にある通りです。

$ sduo docker run -i -t --rm --name letsencrypt --volumes-from nginx_data -v /etc/letsencrypt/:/etc/letsencrypt/ infra/letsencrypt
$ sudo systemctl restart nginx

結果は

Updating letsencrypt and virtual environment dependencies......
Requesting root privileges to run with virtualenv: /root/.local/share/letsencrypt/bin/letsencrypt certonly --webroot -w /usr/share/nginx/html -d www.example.co.jp --renew-by-default

IMPORTANT NOTES:
 - Congratulations! Your certificate and chain have been saved at
   /etc/letsencrypt/live/mng.akky.org/fullchain.pem. Your cert will
   expire on 2016-04-08. To obtain a new version of the certificate in
   the future, simply run Let's Encrypt again.
 - If you like Let's Encrypt, please consider supporting our work by:

   Donating to ISRG / Let's Encrypt:   https://letsencrypt.org/donate
   Donating to EFF:                    https://eff.org/donate-le

となり、証明書の更新が成功しました。/etc/letsencrypt/archive/ディレクトリを見ると新しい証明書が作成されていました。

cronで更新する

/etc/cron.monthly/letsencrypt_update.sh を作成して、月初に実行します。 letsencrypt_update.shの中身は上で実行した証明書を更新するコマンドと同じです。

#!/bin/sh

docker run -i -t --rm --name letsencrypt --volumes-from infra_nginx_data -v /etc/letsencrypt/:/etc/letsencrypt/ infra/letsencrypt
systemctl restart nginx

やった後に気づいたこと

--tls-sni-01-portオプションと--http-01-portオプションでポートを変更すればnginxに手を出さなくても実現できるんじゃないかな。

参考サイト

Let’s Encrypt サーバー証明書の取得と自動更新設定メモ | あぱーブログ

Dockerコンテナで稼動しているnginxを使ってLet's Encrypt の証明書を発行する - メモ

初めての Let's Encrypt

検索すればヒットするので、ここでは割愛。Dockerのホストでは --standaloneで証明書の発行を確認でき、nginxが稼動しているDockerコンテナ内では/letsencrypt-auto certonly --webroot -w /usr/share/nginx/html -d ホスト名を手打ちして証明書を発行できました。

これから先の更新を自動化したいので考えた事をメモしておきます。

作成するコンテナ

下の3つのコンテナを作成します。 1. nginxのコンテナ 1. git clone https://github.com/letsencrypt/letsencryptしたLet's Encryptのコンテナ 1. webroot配下を収めるデータコンテナ

発行された証明書がおかれる/etc/letsencrypt/ディレクトリはDockerホストの/etc/letsencrypt/をそのまま使います。nginxとLet's Encryptのそれぞれが同じデータコンテナとホストの/etc/letsencrypt/ディレクトリをマウントすれば目的を達成できると見込んでいます。

やってみた。

huruyosi.hatenablog.com

d3.jsで作成したグラフの項目名を折り返す

Diverging Stacked Bar Chart

を参考にして横方向の積み上げグラフを作った時に系列のラベル名が長いと左余白を超える場合があり、見た目が格好悪いものになってしまいます。

svg 折り返し」で検索してみると foreignObjectを使う方法が出てくるけれども、何かが違う感じがするので、検索ワードを変更すると、 stack overflow に答えがありました。

stackoverflow.com

stack overflowの返答に書かれていたリンク先 Wrapping Long Labels を見ると、1文字づつ幅を確認していき、単語単位であふれる前に改行を行っていました。

日本語で単語の切れ目で折り返しを行おうとすると、形態素解析を行う必要が出てくるので今回は省略して、横方向にラベルを折り返しできるようにしました。ちょっとダサい部分もあるけど、目的を達成られたので、後日調整しようと思います。

   /**
    * 文字を折り返して指定された幅に収める
    *
    * http://bl.ocks.org/mbostock/7555321
    */
    function wrap(text, width) {
        text.each(function() {
            var text = d3.select(this);
            var words = [];
            org_label = text.text();
            for( i=org_label.length-1 ; 0 <= i ; i--){
                words.push(org_label.substr(i, 1));
            }
            var word,
                line = [],
                lineNumber = 0,
                lineHeight = 1.1, // ems
                y = text.attr("y"),
                dy = parseFloat(text.attr("dy")),
                tspan = text.text(null).append("tspan").attr("x", 0).attr("y", y).attr("dy", dy + "em");
            while (word = words.pop()) {
                line.push(word);
                tspan.text(line.join(""));
                if (tspan.node().getComputedTextLength() > width) {
                    line.pop();
                    tspan.text(line.join(""));
                    line = [word];
                    tspan = text.append("tspan").attr("x", 0).attr("y", y).attr("dy", ++lineNumber * lineHeight + dy + "em").text(word);
                }
            }
            lineNumber++
            var trans_y = ( lineNumber < 2 ? 0 : lineNumber-1.5 )* (text.node().getBBox().height/lineNumber) * -1;
            text.attr("transform","translate(-6, " + trans_y + ")")
        });
    }

折り返しの計算方法の参考したサイト

qiita.com

jacksonでjava.util.Dateをデシリアライズすると9時間ずれる

"sched_date": "2015-09-12"をデシリアライズしてjava.util.Date sched_dateマッピングすると2015年9月12日 0時0分0.0秒を期待しているのですが、java.util.Date#.getTime()を行ってみると、9時間進んでいます。

日本で9時間のずれということはタイムゾーンの可能性が非常に高いです。 案の定、FAQに書かれています。

http://wiki.fasterxml.com/JacksonFAQDateHandling

How come this time is off by 9 hours? (5 hours, 3 hours etc)

You may be thinking in terms of your local time zone. Remember that Jackson defaults to > using GMT (Greenwich time, one hour behind central European timezone; multiple hours ahead of US time zones).

ObjectMapeerにタイムゾーンを指定すると希望する値を取れます。

ObjectMapper mapper = new ObjectMapper();
mapper.setTimeZone(TimeZone.getTimeZone(ZoneId.of("Asia/Tokyo")));

stackoverflow.com

sbadminのサイドバーを縮小させる

メインコンテンツの表示領域を確保するために、左側に配置されているサイドバーの幅を狭めたいと思い探していると既に作成されている方がいらっしゃいました。

[SB Admin 2] BootstrapのAdmin Theme - 世界の一部

目的を達していたので、sbadmin2の現時点での最新リリース v1.0.8 に追従させました。

https://github.com/huruyosiathatena/sb-admin2-sidebar-toggle-collapse

ついでに、気になった箇所を直しました。

live demo

f:id:huruyosi:20151231182157p:plain

spring boot で context-pathを設定する

素のservletで開発を行っている頃は当たり前の様に設定していたcontext-pathを spring boot MVC で設定します。

設定方法

application.yml

設定は application.yml に「server.contextPath」を設定します。

server:
  contextPath: /hogeApp

コントローラー

@RequestMappingアノテーションのvalues値は context-pathを除いた値を設定します。たとえば context-path を 含んだURLが 「/hogeApp/report/project」にするのであれば、@RequestMapping(value = "/report/project", method = RequestMethod.GET)にします。

thymeleafのviewテンプレート

たとえばcss を読み込むlinkタグを<link href="/hogeApp/bootstrap/css/bootstrap.min.css" rel="stylesheet" />とするのであれば、<link th:href="@{/bootstrap/css/bootstrap.min.css}" rel="stylesheet" />とします。@{...}を使うことでcontext-pathの値が反映されます。

MockMvcを使ったテストコード

org.springframework.test.web.servlet.MockMvcを使って処理結果がリダイレクトを期待するテストコードは下の様に書きます。

@RunWith(SpringJUnit4ClassRunner.class)
@SpringApplicationConfiguration(classes = App.class)
@WebAppConfiguration
@Transactional
public class ReportControllerTest {

    @Autowired
    WebApplicationContext wac;

    @Autowired
    TestingCcontext context;

    MockMvc mockMvc;

    /** application.yml に定義されている contextPath */
    String serverContextPath;


    @Before
    public void setup() {
        mockMvc = MockMvcBuilders.webAppContextSetup(wac).build();
        serverContextPath = wac.getEnvironment().getProperty("server.contextPath");
    }

    @Test
    public void 期間指定されない() throws Exception{
        context.setClock(Clock.fixed(Instant.parse("2001-01-06T09:54:30.00Z"), ZoneId.systemDefault()));
        mockMvc.perform(get("/workTimes"))
            .andExpect(status().isFound())
            .andExpect(header().string("Location", serverContextPath + "/workTimes/2001-01"))
            ;
    }
}

mockMvc.performのURIにはcontext-pathの値を含めていませんが、レスポンスヘッダーのLocationにはcontext-pathが付与されています。そのため、素直に比較すると失敗します。

application.ymlに定義したcontext-pathを取得する方法として、wac.getServletContext().getConetxtPath()を試すと空文字になっていたので駄目でした。 ならばと、application.ymlの定義を直接取得するために、wac.getEnvironment().getProperty("server.contextPath")を使います。 これを@Beforeで行い、テストクラスのプロパティに設定します。 プロパティに設定した値をアサーションで利用します。