はじめに
Apache POIを使ってcsvからExcelに出力してみました。ついでに棒グラフも出力させました。
こんな感じです
| template.xlsx | output.xlsx |
|---|---|
 |
 |
初学者のため、至らぬ点も多々あるかと思いますが、ご意見等頂けると幸いです。
目次
1.Apache POIについて
2.使用環境
3.前準備
4.実装
5.まとめ
1.Apache POIについて
そもそもApache POIって何？から始まりました。
調べてみたらMicrosoft Office形式のファイル操作ができるみたいです。(Excel以外にも、Word、PowerPoint等)
wiki
https://ja.wikipedia.org/wiki/Apache_POI
API Document
https://poi.apache.org/apidocs/dev/overview-summary.html
色々できて便利みたいですが、その分奥が深そうです...
今回はタイトルの通り、Excel操作をしていきます！
2.使用環境
Windows 7
Java 8
Apache POI 4.1.1
Microsoft Excel 2010
3.前準備
・Apache POIをダウンロードします。
Apache POI
https://poi.apache.org/index.html
リンクからDownload→poi-bin-～.zip→赤枠内の取得できるリンクからダウンロード
今回は上から2番目をダウンロードしました。
・解凍、ビルドパス
追加したいプロジェクトで右クリック→新規→フォルダー
で、libフォルダを作成して、ダウンロードしたzipをlibフォルダに解凍する。
今回は解凍したフォルダのすべてのjarファイルにビルドパスを通しました！
・インプットデータ（.csv）
今回インプットデータは下のサイトから準備しました！
なんちゃって個人情報
http://kazina.com/dummy/
カレーの食べ方とか面白そうですが、今回は遠慮しました。
・テンプレート作成（.xlsx）
１からフォントやスタイルをPOIで設定するのは面倒大変なので、最初にテンプレートを作っておきます！
4.実装
前準備も終わりいよいよ実装ですね。
Apache POIでExcelを操作する際、XSSFまたはHSSFを使用します。
ざっくり以下の違いがあり、今回はXSSFを使用します。
| HSSF | ～Excel2003(.xls) |
|---|---|
| XSSF | Excel2007(.xlsx)～ |
とりあえずテンプレートファイルを読み込んで出力
FileInputStream fis = null;
FileOutputStream fos = null;
Workbook wb = null;
try{
// テンプレートファイルを取得
fis = new FileInputStream("テンプレートのファイルパス");
wb = (XSSFWorkbook)WorkbookFactory.create(fis);
// デフォルトのフォント設定
wb.createFont().setFontName("フォント名");
// (1)start
// テンプレートからシートを複製し、シート名を設定する
// 複製はワークブック内の最後のシートに追加される
Sheet sheet = wb.cloneSheet(wb.getSheetIndex("テンプレートシート名"));
wb.setSheetName(wb.getSheetIndex(sheet), "アウトプットシート名");
// 行、セルを取得(列単位での取得不可)
// 行やセルがデフォルト状態だとnullになるためcreateする
int rowIndex = 0;
Row row = sheet.getRow(rowIndex);
if(row == null){
row = sheet.createRow(rowIndex);
}
int columnIndex = 0;
Cell cell = row.getCell(columnIndex);
if(cell == null){
cell = row.createCell(columnIndex);
}
// A1に設定
cell.setCellValue("Hello World!!");
// (1)end
// アウトプットファイルに出力
fos = new FileOutputStream("アウトプットファイルパス");
wb.write(fos);
}catch(Exception e) {
e.printStackTrace();
}finally {
if(fis != null) {
try {
fis.close();
}catch(IOException e) {
}
}
if(fos != null) {
try {
fos.close();
}catch(IOException e) {
}
}
if(wb != null) {
try {
wb.close();
}catch(IOException e) {
}
}
}
これでテンプレートファイルを変更せずに出力できますが、セル単位で値を設定していくので、(1)の範囲を何度も繰り返す必要があります。
そこで、移動処理や入力処理をまとめたManagerクラスを作成しました。
Managerクラス作成
ExcelManager.java
package poi.common.util;
import static poi.common.constant.ExcelConstants.*;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.PrintSetup;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xddf.usermodel.chart.XDDFChart;
import org.apache.poi.xssf.usermodel.XSSFChart;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTAxDataSource;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTBarChart;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTBarSer;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTBoolean;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTCatAx;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTChart;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTLegend;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumDataSource;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumRef;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTPlotArea;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTScaling;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTSerTx;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTStrRef;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTValAx;
import org.openxmlformats.schemas.drawingml.x2006.chart.STAxPos;
import org.openxmlformats.schemas.drawingml.x2006.chart.STBarDir;
import org.openxmlformats.schemas.drawingml.x2006.chart.STLegendPos;
import org.openxmlformats.schemas.drawingml.x2006.chart.STOrientation;
import org.openxmlformats.schemas.drawingml.x2006.chart.STTickLblPos;
public class ExcelManager {
private Sheet sheet = null;
private Row row = null;
private Cell cell = null;
private int offset;
private int rowIndex;
private int columnIndex;
private Map<Integer,CellStyle> styleMap;
// 設定処理
/**
* シートを設定し、参照するセル位置を「A1」に設定する
* @param sheet
*/
public void setSheet(Sheet sheet) {
this.sheet = sheet;
setPosition();
this.styleMap = new HashMap<>();
this.offset = 1;
this.rowIndex = 0;
this.columnIndex = 0;
}
/**
* テンプレートの印刷設定をコピーする
* @param printSetup
*/
public void setPrintSetup(PrintSetup printSetup) {
PrintSetup newSetup = this.sheet.getPrintSetup();
// コピー枚数
newSetup.setCopies(printSetup.getCopies());
// 下書きモード
//newSetup.setDraft(printSetup.getDraft());
// シートに収まる高さのページ数
newSetup.setFitHeight(printSetup.getFitHeight());
// シートが収まる幅のページ数
newSetup.setFitWidth(printSetup.getFitWidth());
// フッター余白
//newSetup.setFooterMargin(printSetup.getFooterMargin());
// ヘッダー余白
//newSetup.setHeaderMargin(printSetup.getHeaderMargin());
// 水平解像度
//newSetup.setHResolution(printSetup.getHResolution());
// 横向きモード
newSetup.setLandscape(printSetup.getLandscape());
// 左から右への印刷順序
//newSetup.setLeftToRight(printSetup.getLeftToRight());
// 白黒
newSetup.setNoColor(printSetup.getNoColor());
// 向き
newSetup.setNoOrientation(printSetup.getNoOrientation());
// 印刷メモ
//newSetup.setNotes(printSetup.getNotes());
// ページの開始
//newSetup.setPageStart(printSetup.getPageStart());
// 用紙サイズ
newSetup.setPaperSize(printSetup.getPaperSize());
// スケール
newSetup.setScale(printSetup.getScale());
// 使用ページ番号
//newSetup.setUsePage(printSetup.getUsePage());
// 有効な設定
//newSetup.setValidSettings(printSetup.getValidSettings());
// 垂直解像度
//newSetup.setVResolution(printSetup.getVResolution());
}
/**
* 印刷範囲取得
* @return
*/
public String getPrintArea() {
int firstRow = this.sheet.getFirstRowNum();
int lastRow = this.rowIndex;
int firstColumn = this.sheet.getRow(firstRow).getFirstCellNum();
int lastColumn = this.sheet.getRow(lastRow).getLastCellNum()-1;
String printArea = "$" + getColumnAlphabet(firstColumn)
+ "$" + String.valueOf(firstRow+1)
+ ":$" + getColumnAlphabet(lastColumn)
+ "$" + String.valueOf(lastRow);
return printArea;
}
// 設定処理はここまで
// 移動処理
/**
* 参照する行位置を設定する
*/
private void setRow() {
if((this.row = sheet.getRow(this.rowIndex)) == null) {
this.row = sheet.createRow(this.rowIndex);
}
}
/**
* 参照するセル位置を設定する
*/
private void setCell() {
if((this.cell = row.getCell(this.columnIndex)) == null) {
this.cell = row.createCell(this.columnIndex);
}
}
/**
* オフセット（移動量）を設定する
* ※デフォルトは「1」
* @param offset
*/
public void setOffset(int offset) {
this.offset = offset;
}
/**
* 参照する行位置、セル位置を設定する
*/
private void setPosition() {
setRow();
setCell();
}
/**
* 行と列を指定して参照するセル位置を設定する
* @param rowIndex
* 行位置(0ベース)
* @param columnIndex
* 列位置(0ベース)
*/
public void setPosition(int rowIndex, int columnIndex) {
this.rowIndex = rowIndex;
this.columnIndex = columnIndex;
setPosition();
}
/**
* 設定してあるオフセットに従って参照するセル位置を移動する
* 現在列位置+オフセット
*/
public void nextCell() {
moveCell(this.offset);
}
/**
* 指定したオフセットに従って参照するセル位置を移動する
* 現在列位置+オフセット
* @param columnOffset
*/
public void moveCell(int columnOffset) {
move(0, columnOffset);
}
/**
* 設定してあるオフセットに従って参照する行位置を移動する
* 現在行位置+オフセット
* ※列位置は0（A）列になる
*/
public void nextRow() {
nextRow(0);
}
/**
* 設定してあるオフセットに従って参照する行位置を移動し、指定した列位置に移動する
* 現在行位置+オフセット
* @param columnIndex
*/
public void nextRow(int columnIndex) {
this.columnIndex = columnIndex;
moveRow(this.offset);
}
/**
* 指定したオフセットに従って参照する行位置を移動する
* 現在行位置+オフセット
* ※列位置は変わらない
* @param rowOffset
*/
public void moveRow(int rowOffset) {
move(rowOffset, 0);
}
/**
* 指定した行オフセット、列オフセットに従って参照する行位置、列位置を移動する
* 現在行位置+行オフセット
* 現在列位置+列オフセット
* @param rowOffset
* @param columnOffset
*/
public void move(int rowOffset, int columnOffset) {
this.rowIndex += rowOffset;
this.columnIndex += columnOffset;
setPosition();
}
/**
* 現在行位置から以降の行を1行下へシフトする（行の挿入）
* ※上の行のスタイル、高さを引き継ぐ
*/
public void shiftRows() {
int lastRowNum = this.sheet.getLastRowNum();
int lastCellNum = this.sheet.getRow(this.rowIndex-1).getLastCellNum();
this.sheet.shiftRows(this.rowIndex, lastRowNum+1, 1);
Row newRow = this.sheet.getRow(this.rowIndex);
if(newRow == null) {
newRow = this.sheet.createRow(this.rowIndex);
}
Row oldRow = this.sheet.getRow(this.rowIndex-1);
for(int i = 0; i < lastCellNum-1; i++) {
Cell newCell = newRow.createCell(i);
Cell oldCell = oldRow.getCell(i);
// oldCellがnullでなければスタイル設定
// wrokbookに作成出来るcellstyleには上限があるのでmapに詰める
if(oldCell != null) {
if(!styleMap.containsKey(i)) {
CellStyle newStyle = this.sheet.getWorkbook().createCellStyle();
newStyle.cloneStyleFrom(oldCell.getCellStyle());
newStyle.setBorderTop(BorderStyle.DOTTED);
styleMap.put(i, newStyle);
}
newCell.setCellStyle(styleMap.get(i));
}
}
newRow.setHeightInPoints(oldRow.getHeightInPoints());
setPosition();
}
// 移動処理はここまで
// 入力処理
/**
* char型の値をセルに設定し、指定したオフセットに従って参照する列位置を移動する
* 値がなければBLANKを設定する
* @param value
* @param columnOffset
*/
public void setCellValue(char value, int columnOffset) {
setCellValue(String.valueOf(value), columnOffset);
}
/**
* char型の値をセルに設定し、設定してあるオフセットに従って参照する列位置を移動する
* 値がなければBLANKを設定する
* @param value
*/
public void setCellValue(char value) {
setCellValue(value, this.offset);
}
/**
* String型の値をセルに設定し、指定したオフセットに従って参照する列位置を移動する
* 値がなければBLANKを設定する
* @param value
* @param columnOffset
*/
public void setCellValue(String value, int columnOffset) {
if(value.trim().length() == 0) {
this.cell.setBlank();
}else {
this.cell.setCellValue(value);
}
moveCell(columnOffset);
}
/**
* String型の値をセルに設定し、設定してあるオフセットに従って参照する列位置を移動する
* 値がなければBLANKを設定する
* @param value
*/
public void setCellValue(String value) {
setCellValue(value, this.offset);
}
/**
* RichTextString型の値をセルに設定し、指定したオフセットに従って参照する列位置を移動する
* 値がなければBLANKを設定する
* @param value
* @param columnOffset
*/
public void setCellValue(RichTextString value, int columnOffset) {
if(value.getString().trim().length() == 0) {
this.cell.setBlank();
}else {
this.cell.setCellValue(value);
}
moveCell(columnOffset);
}
/**
* RichTextString型の値をセルに設定し、設定してあるオフセットに従って参照する列位置を移動する
* 値がなければBLANKを設定する
* @param value
*/
public void setCellValue(RichTextString value) {
setCellValue(value, this.offset);
}
/**
* byte型の値をセルに設定し、指定したオフセットに従って参照する列位置を移動する
* @param value
* @param columnOffset
*/
public void setCellValue(byte value, int columnOffset) {
this.cell.setCellValue(value);
moveCell(columnOffset);
}
/**
* byte型の値をセルに設定し、設定してあるオフセットに従って参照する列位置を移動する
* @param value
*/
public void setCellValue(byte value) {
setCellValue(value, this.offset);
}
/**
* short型の値をセルに設定し、指定したオフセットに従って参照する列位置を移動する
* @param value
* @param columnOffset
*/
public void setCellValue(short value, int columnOffset) {
this.cell.setCellValue(value);
moveCell(columnOffset);
}
/**
* short型の値をセルに設定し、設定してあるオフセットに従って参照する列位置を移動する
* @param value
*/
public void setCellValue(short value) {
setCellValue(value, this.offset);
}
/**
* int型の値をセルに設定し、指定したオフセットに従って参照する列位置を移動する
* @param value
* @param columnOffset
*/
public void setCellValue(int value, int columnOffset) {
this.cell.setCellValue(value);
moveCell(columnOffset);
}
/**
* int型の値をセルに設定し、設定してあるオフセットに従って参照する列位置を移動する
* @param value
*/
public void setCellValue(int value) {
setCellValue(value, this.offset);
}
/**
* long型の値をセルに設定し、指定したオフセットに従って参照する列位置を移動する
* @param value
* @param columnOffset
*/
public void setCellValue(long value, int columnOffset) {
this.cell.setCellValue(value);
moveCell(columnOffset);
}
/**
* long型の値をセルに設定し、設定してあるオフセットに従って参照する列位置を移動する
* @param value
*/
public void setCellValue(long value) {
setCellValue(value, this.offset);
}
/**
* double型の値をセルに設定し、指定したオフセットに従って参照する列位置を移動する
* @param value
* @param columnOffset
*/
public void setCellValue(double value, int columnOffset) {
this.cell.setCellValue(value);
moveCell(columnOffset);
}
/**
* double型の値をセルに設定し、設定してあるオフセットに従って参照する列位置を移動する
* @param value
*/
public void setCellValue(double value) {
setCellValue(value, this.offset);
}
/**
* float型の値をセルに設定し、指定したオフセットに従って参照する列位置を移動する
* @param value
* @param columnOffset
*/
public void setCellValue(float value, int columnOffset) {
this.cell.setCellValue(value);
moveCell(columnOffset);
}
/**
* float型の値をセルに設定し、設定してあるオフセットに従って参照する列位置を移動する
* @param value
*/
public void setCellValue(float value) {
setCellValue(value, this.offset);
}
/**
* boolean型の値をセルに設定し、指定したオフセットに従って参照する列位置を移動する
* @param value
* @param columnOffset
*/
public void setCellValue(boolean value, int columnOffset) {
this.cell.setCellValue(value);
moveCell(columnOffset);
}
/**
* boolean型の値をセルに設定し、設定してあるオフセットに従って参照する列位置を移動する
* @param value
*/
public void setCellValue(boolean value) {
setCellValue(value, this.offset);
}
/**
* Calendar型の値をセルに設定し、指定したオフセットに従って参照する列位置を移動する
* @param value
* @param columnOffset
*/
public void setCellValue(Calendar value, int columnOffset) {
this.cell.setCellValue(value);
moveCell(columnOffset);
}
/**
* Calendar型の値をセルに設定し、設定してあるオフセットに従って参照する列位置を移動する
* @param value
*/
public void setCellValue(Calendar value) {
setCellValue(value, this.offset);
}
/**
* Date型の値をセルに設定し、指定したオフセットに従って参照する列位置を移動する
* @param value
* @param columnOffset
*/
public void setCellValue(Date value, int columnOffset) {
this.cell.setCellValue(value);
moveCell(columnOffset);
}
/**
* Date型の値をセルに設定し、設定してあるオフセットに従って参照する列位置を移動する
* @param value
*/
public void setCellValue(Date value) {
setCellValue(value, this.offset);
}
/**
* LocalDate型の値をセルに設定し、指定したオフセットに従って参照する列位置を移動する
* @param value
* @param columnOffset
*/
public void setCellValue(LocalDate value, int columnOffset) {
this.cell.setCellValue(value);
moveCell(columnOffset);
}
/**
* LocalDate型の値をセルに設定し、設定してあるオフセットに従って参照する列位置を移動する
* @param value
*/
public void setCellValue(LocalDate value) {
setCellValue(value, this.offset);
}
/**
* LocalDateTime型の値をセルに設定し、指定したオフセットに従って参照する列位置を移動する
* @param value
* @param columnOffset
*/
public void setCellValue(LocalDateTime value, int columnOffset) {
this.cell.setCellValue(value);
moveCell(columnOffset);
}
/**
* LocalDateTime型の値をセルに設定し、設定してあるオフセットに従って参照する列位置を移動する
* @param value
*/
public void setCellValue(LocalDateTime value) {
setCellValue(value, this.offset);
}
/**
* String型の計算式("="は要らない)をセルに設定し、指定したオフセットに従って参照する列位置を移動する
* @param value
* @param columnOffset
*/
public void setCellFormula(String value, int columnOffset) {
this.cell.setCellFormula(value);
moveCell(columnOffset);
}
/**
* String型の計算式("="は要らない)をセルに設定し、設定してあるオフセットに従って参照する列位置を移動する
* @param value
*/
public void setCellFormula(String value) {
setCellFormula(value, this.offset);
}
/**
* String型のコメントをセルに設定する(表示領域は固定値)
* ※列位置は移動しない
* @param commentStr
*/
public void setCellComment(String commentStr) {
CreationHelper helper = sheet.getWorkbook().getCreationHelper();
XSSFDrawing drawing = (XSSFDrawing)sheet.createDrawingPatriarch();
// row1の位置からずらす値(ピクセル単位)
int dx1 = 0;
// col1の位置からずらす値(ピクセル単位)
int dy1 = 0;
// row2の位置からずらす値(ピクセル単位)
int dx2 = 0;
// col2の位置からずらす値(ピクセル単位)
int dy2 = 0;
// コメント表示領域の左上の列位置(セル単位)
int col1 = this.columnIndex + 1;
// コメント表示領域の左上の行位置(セル単位)
int row1 = this.rowIndex;
// コメント表示領域の右下の列位置(セル単位)
int col2 = this.columnIndex + 4;
// コメント表示領域の右下の行位置(セル単位)
int row2 = this.rowIndex + 3;
ClientAnchor anchor = drawing.createAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2);
Comment comment = drawing.createCellComment(anchor);
//comment.setAuthor("master");
comment.setString(helper.createRichTextString(commentStr));
this.cell.setCellComment(comment);
}
/**
* 現在のセル位置から棒グラフを挿入(大きさは固定値)
*/
public void setBarChart() {
XSSFDrawing drawing = (XSSFDrawing)sheet.createDrawingPatriarch();
int dx1 = 0;
int dy1 = 0;
int dx2 = 0;
int dy2 = 0;
int col1 = this.columnIndex;
int row1 = this.rowIndex;
move(16, 10);
int col2 = this.columnIndex;
int row2 = this.rowIndex;
ClientAnchor anchor = drawing.createAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2);
XDDFChart chart = (XDDFChart)drawing.createChart(anchor);
CTChart ctChart = ((XSSFChart)chart).getCTChart();
CTPlotArea ctPlotArea = ctChart.getPlotArea();
CTBarChart ctBarChart = ctPlotArea.addNewBarChart();
CTBoolean ctBoolean = ctBarChart.addNewVaryColors();
ctBoolean.setVal(true);
ctBarChart.addNewBarDir().setVal(STBarDir.COL);
for (int r = 4; r < 8; r++) {
CTBarSer ctBarSer = ctBarChart.addNewSer();
CTSerTx ctSerTx = ctBarSer.addNewTx();
CTStrRef ctStrRef = ctSerTx.addNewStrRef();
// 凡例
ctStrRef.setF("集計!$B$" + r);
ctBarSer.addNewIdx().setVal(r-4);
CTAxDataSource cttAxDataSource = ctBarSer.addNewCat();
ctStrRef = cttAxDataSource.addNewStrRef();
// 項目
ctStrRef.setF("集計!$C$3:$I$3");
CTNumDataSource ctNumDataSource = ctBarSer.addNewVal();
CTNumRef ctNumRef = ctNumDataSource.addNewNumRef();
// データエリア
ctNumRef.setF("集計!$C$" + r + ":$I$" + r);
ctBarSer.addNewSpPr().addNewLn().addNewSolidFill()
.addNewSrgbClr().setVal(new byte[] {0,0,0});
}
int catId = 100;
int valId = 200;
ctBarChart.addNewAxId().setVal(catId);
ctBarChart.addNewAxId().setVal(valId);
CTCatAx ctCatAx = ctPlotArea.addNewCatAx();
ctCatAx.addNewAxId().setVal(catId);
CTScaling ctScaling = ctCatAx.addNewScaling();
ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);
ctCatAx.addNewDelete().setVal(false);
ctCatAx.addNewAxPos().setVal(STAxPos.B);
ctCatAx.addNewCrossAx().setVal(valId);
ctCatAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);
CTValAx ctValAx = ctPlotArea.addNewValAx();
ctValAx.addNewAxId().setVal(valId);
ctScaling = ctValAx.addNewScaling();
ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);
ctValAx.addNewDelete().setVal(false);
ctValAx.addNewAxPos().setVal(STAxPos.L);
ctValAx.addNewCrossAx().setVal(catId);
ctValAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);
CTLegend ctLegend = ctChart.addNewLegend();
ctLegend.addNewLegendPos().setVal(STLegendPos.B);
ctLegend.addNewOverlay().setVal(false);
}
// 入力処理はここまで
// 変換処理
/**
* 指定した列番号をA1表記に変換する
* @param ascii
* @return
*/
public String getColumnAlphabet(int ascii) {
String alphabet = "";
if(ascii < 26) {
alphabet = String.valueOf((char)(ASCII+ascii));
}else {
int div = ascii / 26;
int mod = ascii % 26;
alphabet = getColumnAlphabet(div-1) + getColumnAlphabet(mod);
}
return alphabet;
}
/**
* 現在の列番号をA1表記に変更する
* @return
*/
public String getColumnAlphabet() {
return getColumnAlphabet(this.columnIndex);
}
// 変換処理はここまで
}
かいつまんで説明すると、
・nextCell()で次のセルに移動、nextRow()で次の行に移動しセルは0列（A列）に移動する。
・setCellValue()でセルに値を設定し、次のセルに移動する。
・setBarChart()はテンプレートで値を入れるセルを準備していたので範囲設定は固定値にしている。
あれこれ詰め込んで神クラスになりつつあるので分割しなきゃとは思いつつ、一旦これで(笑)
Mainクラスの完成版
PoiSample.java
package poi.main;
import static poi.common.constant.ExcelConstants.*;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import poi.common.dao.CSVInputDao;
import poi.common.dto.CSVDto;
import poi.common.util.ExcelManager;
public class PoiSample {
public static void main(String[] args) {
System.out.println("開始");
FileInputStream fis = null;
FileOutputStream fos = null;
Workbook wb = null;
ExcelManager manager = new ExcelManager();
int rowIndex = 0;
CSVInputDao csvInDao = new CSVInputDao();
CSVDto csvDto = new CSVDto();
Map<Integer, List<CSVDto>> agesMap = new TreeMap<>();
SimpleDateFormat sdFormat = new SimpleDateFormat("yyyy/MM/dd");
csvInDao.setFile(FILE_PATH_INPUT);
try {
fis = new FileInputStream(FILE_PATH_TEMPLATE);
wb = (XSSFWorkbook)WorkbookFactory.create(fis);
wb.createFont().setFontName(FONT);
// シート名を指定して取得
Sheet sheet = wb.cloneSheet(wb.getSheetIndex(SHEET_NAME_TEMPLATE));
// シート名の設定
wb.setSheetName(wb.getSheetIndex(sheet), SHEET_NAME_ALL_DATA);
System.out.println("createSheet:" + sheet.getSheetName());
manager.setSheet(sheet);
csvInDao.open();
// 全データ出力
while((csvDto = csvInDao.read()) != null) {
if(rowIndex != 0){
if(rowIndex >= 2) {
manager.shiftRows();
}
int age = Integer.parseInt(csvDto.getAge());
String comment = "電話:" + csvDto.getTelephone() + LINE_SEPARATOR
+ "携帯:" + csvDto.getMobile() + LINE_SEPARATOR
+ "メール:" + csvDto.getMail();
manager.setCellComment(comment);
manager.setCellValue(csvDto.getName());
manager.setCellValue(csvDto.getFurigana());
manager.setCellValue(csvDto.getSex());
manager.setCellValue(age);
manager.setCellValue(sdFormat.parse(csvDto.getBirthDay()));
manager.setCellValue(csvDto.getMarriage());
manager.setCellValue(csvDto.getBloodType());
manager.setCellValue(csvDto.getBirthPlace());
manager.setCellValue(csvDto.getCareer());
// 年代取得 ex)23→20
age = (int)(age / 10) * 10;
if(agesMap.containsKey(age)) {
agesMap.get(age).add(csvDto);
}else {
List<CSVDto> dtoList = new ArrayList<>();
dtoList.add(csvDto);
agesMap.put(age, dtoList);
}
}
rowIndex++;
manager.nextRow();
}
// 印刷設定、印刷範囲
manager.setPrintSetup(wb.getSheetAt(wb.getSheetIndex(SHEET_NAME_TEMPLATE)).getPrintSetup());
wb.setPrintArea(wb.getSheetIndex(sheet), manager.getPrintArea());
// 年代毎にシート分けして出力
List<String> sheetNames = new ArrayList<>();
for(Map.Entry<Integer, List<CSVDto>> ageMap : agesMap.entrySet()) {
String sheetName = String.valueOf(ageMap.getKey()) + "代";
if((sheet = wb.getSheet(sheetName)) == null) {
sheet = wb.cloneSheet(wb.getSheetIndex(SHEET_NAME_TEMPLATE));
wb.setSheetName(wb.getSheetIndex(sheet), sheetName);
}
sheetNames.add(sheet.getSheetName());
System.out.println("createSheet:" + sheet.getSheetName());
manager.setSheet(sheet);
rowIndex = 1;
manager.nextRow();
for(CSVDto nextDto: ageMap.getValue()) {
if(rowIndex >= 2) {
manager.shiftRows();
}
String comment = "電話:" + nextDto.getTelephone() + LINE_SEPARATOR
+ "携帯:" + nextDto.getMobile() + LINE_SEPARATOR
+ "メール:" + nextDto.getMail();
manager.setCellComment(comment);
manager.setCellValue(nextDto.getName());
manager.setCellValue(nextDto.getFurigana());
manager.setCellValue(nextDto.getSex());
manager.setCellValue(Integer.parseInt(nextDto.getAge()));
manager.setCellValue(sdFormat.parse(nextDto.getBirthDay()));
manager.setCellValue(nextDto.getMarriage());
manager.setCellValue(nextDto.getBloodType());
manager.setCellValue(nextDto.getBirthPlace());
manager.setCellValue(nextDto.getCareer());
rowIndex++;
manager.nextRow();
}
// 印刷設定、印刷範囲
manager.setPrintSetup(wb.getSheetAt(wb.getSheetIndex(SHEET_NAME_TEMPLATE)).getPrintSetup());
wb.setPrintArea(wb.getSheetIndex(sheet), manager.getPrintArea());
}
// 集計出力
sheet = wb.cloneSheet(wb.getSheetIndex(SHEET_NAME_GRAPH_TEMPLATE));
wb.setSheetName(wb.getSheetIndex(sheet), SHEET_NAME_AGGREGATED);
System.out.println("createSheet:" + sheet.getSheetName());
manager.setSheet(sheet);
String formula = "";
for(rowIndex = 3; 7 >= rowIndex; rowIndex++) {
manager.setPosition(rowIndex, 2);
for(String sheetName: sheetNames) {
if(rowIndex == 7) {
formula = "SUM($" + manager.getColumnAlphabet() + "$4:$"
+ manager.getColumnAlphabet() +"$7)";
}else {
formula = "COUNTIF(\'" + sheetName + "\'!$I:$I,$B$" + String.valueOf(rowIndex+1) + ")";
}
manager.setCellFormula(formula);
}
}
// 数式は設定しただけでは計算されないので、再計算
wb.getCreationHelper().createFormulaEvaluator().evaluateAll();
rowIndex++;
manager.setPosition(rowIndex, 1);
// 棒グラフ設定
manager.setBarChart();
// 印刷設定、印刷範囲
manager.setPrintSetup(wb.getSheetAt(wb.getSheetIndex(SHEET_NAME_GRAPH_TEMPLATE)).getPrintSetup());
wb.setPrintArea(wb.getSheetIndex(sheet), manager.getPrintArea());
// 不要なテンプレートを削除
wb.removeSheetAt(wb.getSheetIndex(SHEET_NAME_TEMPLATE));
wb.removeSheetAt(wb.getSheetIndex(SHEET_NAME_GRAPH_TEMPLATE));
// ファイル出力
fos = new FileOutputStream(FILE_PATH_OUTPUT);
wb.write(fos);
System.out.println("終了");
}catch(Exception e) {
e.printStackTrace();
}finally {
if(fis != null) {
try {
fis.close();
}catch(IOException e) {
}
}
if(fos != null) {
try {
fos.close();
}catch(IOException e) {
}
}
if(wb != null) {
try {
wb.close();
}catch(IOException e) {
}
}
}
}
}
5.まとめ
分からないことも多く、試行錯誤しました。
ですが、なんとかcsvファイルを読み込み、全データを出力し、年代毎に出力し、集計を出力できました！
個人的にManagerクラスを作成したおかげで実装が楽になりましたが、色々詰め込みすぎたので処理内容ごとにクラスを分けて継承を使っていくべきだったと思います。
継承は苦手なので勉強しないといけないですね。
長々と最後まで見ていただいてありがとうございます！
最後にもう一度githubのリンク公開しておきます!