tanamonの稀に良く書く日記

KEEP CALM AND DRINK BEER

WicketでExcel2007のファイルを動的に生成する

wicket memo

mavenの指定

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>3.5-FINAL</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.5-FINAL</version>
</dependency>

最新のApache POIを使います。

ExcelPage.html

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html>
<head>
	<title>excel test</title>
</head>
<body>
	<form wicket:id="downloadForm">
		<input type="text" wicket:id="message" />
		<button wicket:id="download">download</button>
	</form>
</body>
</html>

入力フォームに入力した文字をExcelに埋め込むというサンプルです。

ExcelPage.java

package jp.tanamon.wicket.page;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.wicket.IRequestTarget;
import org.apache.wicket.markup.html.WebPage;
import org.apache.wicket.markup.html.form.Button;
import org.apache.wicket.markup.html.form.Form;
import org.apache.wicket.markup.html.form.TextField;
import org.apache.wicket.model.Model;
import org.apache.wicket.request.target.resource.ResourceStreamRequestTarget;
import org.apache.wicket.util.resource.AbstractResourceStreamWriter;
import org.apache.wicket.util.resource.IResourceStream;

public class ExcelPage extends WebPage {

	public ExcelPage() {
		Form<Void> form = new Form<Void>("downloadForm");
		add(form);

		final TextField<String> message = new TextField<String>("message", new Model<String>("てすと"));
		form.add(message);

		Button download = new Button("download") {
			private static final long serialVersionUID = 9183994254859101772L;

			@Override
			public void onSubmit() {
				IResourceStream resource = new AbstractResourceStreamWriter() {
					private static final long serialVersionUID = 7663989930520554901L;

					@Override
					public void write(OutputStream output) {
						String templateName = "template.xlsx";
						InputStream input = ExcelPage.class.getResourceAsStream(templateName);
						try {
							Workbook workbook = WorkbookFactory.create(input);
							workbook.getSheetAt(0).getRow(0).getCell(0).setCellValue(message.getDefaultModelObjectAsString());
							workbook.write(output);
						} catch (InvalidFormatException e) {
							e.printStackTrace();
						} catch (IOException e) {
							e.printStackTrace();
						}
					}

					@Override
					public String getContentType() {
						// see http://www.microsoft.com/japan/technet/prodtechnol/office/ork/library/f88d06fb-c9a4-413c-a1d3-40c97e340c5a.mspx?mfr=true
						return "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
					}
				};

				String downloadName = "download.xlsx";
				IRequestTarget target = new ResourceStreamRequestTarget(resource, downloadName);
				getRequestCycle().setRequestTarget(target);
			}
		};
		form.add(download);
	}
}

なんかContentTypeがえらいことになっているけど、Excel2007仕様だとこうみたいです。

template.xlsx

ExcelPage.javaと同一階層にテキトウなExcelファイルを置いてテンプレートにします。

動作

downloadボタンを押すと、シート1のA1セルに入力文字が入ったExcelファイルがダウンロードされます。