塩湖は、塩類の濃度によって以下のように分類される。
⇧ 何で、鏡みたいに景色を映し出してくれるのか摩訶不思議と思っていたら、
ウユニ塩湖がなぜ鏡の風景を反射するかというと、「水たまりの底が白かったら、いつもよりよく反射するよね」ということらしい。理解できなかった人はお父さんかお母さんに聞いてみてほしい。
ここで何がいいたいかというと、「塩と水があれば再現は可能」ということである。
⇧ 上記サイト様によりますと、「塩」が鍵となっていそう。あとは、水面が凪いだ状態であるって条件が必要そうですかね。
というわけで、今回は、Javaについてです。
レッツトライ～。
Apache POIって？
公式の説明によると、
Mission Statement
The Apache POI Project's mission is to create and maintain Java APIs for manipulating various file formats based upon the Office Open XML standards (OOXML) and Microsoft's OLE 2 Compound Document format (OLE2). In short, you can read and write MS Excel files using Java. In addition, you can read and write MS Word and MS PowerPoint files using Java. Apache POI is your Java Excel solution (for Excel 97-2008). We have a complete API for porting other OOXML and OLE2 formats and welcome others to participate.
⇧ 2つのファイルのフォーマットに対して操作とかできるJavaのAPIってことらしく、 その2つのファイルのフォーマットってのが、
ということらしい。
Wikipediaさんの説明では、
Apache POI（アパッチ・ポイまたはピーオーアイ）はApacheソフトウェア財団のプロジェクトで、WordやExcelといったMicrosoft Office形式のファイルを読み書きできる100% Javaライブラリとして提供されている。
バージョン3.5からISO/IEC 29500 Office Open XML形式のファイルに対応している。 OOXMLへの対応は、Sourcesenseが貢献している。Sourcesenseは、前記の貢献をするためのマイクロソフトから委託を受けたオープンソース企業である。この関係は論争を促し、一部のPOI参加者は、マイクロソフトのOpen Specification Promiseに関するPOI OOXMLの特許保護を問う者もいる。
⇧ ということらしい。
POIという名称は、Microsoft Officeのファイル形式をリバースエンジニアリングした際、その形式が意図的に、しかも中途半端に分かりにくくされていたため、皮肉を込めて "Poor Obfuscation Implementation" (質の悪い難読な実装) と呼んだものの頭字語に端を発している。このようにユーモラスな正式名称を当てはめる方法はかつていろいろなサブプロジェクトに見られたが、ユーモアを不適切と捉えるビジネス界への進出を意識し、公式ウェブページからは削除されている。もうひとつの由来は、ハワイの珍味Poiから来た。ハワイ人がこれを食べ続けると巨人になるとも言われている。
⇧「ユーモアを不適切と捉えるビジネス界」って...
公式の情報によると、
HSSF is the POI Project's pure Java implementation of the Excel '97(-2007) file format. XSSF is the POI Project's pure Java implementation of the Excel 2007 OOXML (.xlsx) file format.
Since 3.8-beta3, POI provides a low-memory footprint SXSSF API built on top of XSSF.
SXSSF is an API-compatible streaming extension of XSSF to be used when very large spreadsheets have to be produced, and heap space is limited. SXSSF achieves its low memory footprint by limiting access to the rows that are within a sliding window, while XSSF gives access to all rows in the document. Older rows that are no longer in the window become inaccessible, as they are written to the disk.
⇧ 使用するAPIによって「メモリ消費」が変わってくるらしい。Excelのバージョンとかも考慮しないといけないみたいなんですが、「Apache POI」で「.xls」とか「.xlsx」のファイルを扱うAPIとしては、
- SXSSF（Streaming extension of XSSF）
- XSSF (XML SpreadSheet Format)
- HSSF (Horrible SpreadSheet Format)
の3タイプあるらしく、「SXSSF（Streaming extension of XSSF）」が一番「メモリ消費」を抑えることができるらしいです。
まぁ、これ知らずに、「XSSF (XML SpreadSheet Format)」を使って実装してしまったのだけど...
ちなみに、「Word」とか「PowerPoint」とかに対応してるAPIも用意されているようです。
私は、「Microsoft Office」がPCに入ってないので、「Libre Office」で代替してますが、拡張子が「.xlsx」であれば、普通に動作しました。
実際に使ってみた
調べた感じだと、
⇧ なんかかなり面倒くさそう...
⇧ ハマりどころが多そう...
⇧ Mapのループ、確かに独特なんですよね...
そして、使ってみた感触は、めちゃくちゃ使い辛れ～...
ちなみに、私のPC環境には、「Libre Office」しかないんだけど、ファイルの「拡張子」 を「.xlsx」にして試したところ、セルの値は取得したりできました。（「書式」とかは試してないんで分からずです...）
というわけで、まずは、Excelファイルを用意ですが、
⇧ 上記サイト様によりますと、「総務省」で日本の人口について統計した資料をダウンロードできるらしい。
というわけで、
⇧ 上記のページにアクセスして、「都道府県別人口と人口増減率（エクセル：17KB）」をダウンロードしてみました。
ダウンロードしたExcelファイルについては、Eclipseで適当な「Gradleプロジェクト」を作成して、「src/main/resources」に配置します。
⇧ なんか、他にもいろいろファイルが配置されてるけど、今回やろうとすることは、「build.gradle」ファイルに「依存関係」を追加したりする以外は、上図の青色になってる2つのファイル（上図で言うと「Library.java」と「n210200200.xlsx」）があればOK。
/*
* This file was generated by the Gradle 'init' task.
*
* This generated file contains a sample Java Library project to get you started.
* For more details take a look at the Java Libraries chapter in the Gradle
* User Manual available at https://docs.gradle.org/6.3/userguide/java_library_plugin.html
*/
plugins {
// Apply the java-library plugin to add support for Java Library
id 'java-library'
}
repositories {
// Use jcenter for resolving dependencies.
// You can declare any Maven/Ivy/file repository here.
jcenter()
}
dependencies {
// This dependency is exported to consumers, that is to say found on their compile classpath.
api 'org.apache.commons:commons-math3:3.6.1'
// This dependency is used internally, and not exposed to consumers on their own compile classpath.
implementation 'com.google.guava:guava:28.2-jre'
// https://mvnrepository.com/artifact/org.apache.poi/poi
implementation group: 'org.apache.poi', name: 'poi', version: '4.1.2'
// https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml
implementation group: 'org.apache.poi', name: 'poi-ooxml', version: '4.1.2'
// https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas
implementation group: 'org.apache.poi', name: 'poi-ooxml-schemas', version: '4.1.2'
// https://mvnrepository.com/artifact/com.opencsv/opencsv
implementation group: 'com.opencsv', name: 'opencsv', version: '5.4'
// https://mvnrepository.com/artifact/xml-apis/xml-apis
implementation group: 'xml-apis', name: 'xml-apis', version: '2.0.2'
// Use JUnit test framework
testImplementation 'junit:junit:4.12'
}
⇧ 今回は、「poi」「poi-ooxml」「poi-ooxml-schemas」の3つがあれば大丈夫かと。
/*
* This Java source file was generated by the Gradle 'init' task.
*/
package Java_one_hundred_apache_poi;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
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;
public class Library {
private static final String INPUT_FILE_PATH = "src/main/resources/n210200200.xlsx";
private static final String OUTPUT_FILE_PATH = "src/main/resources/output/n210200200_output.xlsx";
private static final String[] COLUMN_BY_WRITING_EXCEL = { "都道府県", "総人口" };
private static List<Integer> ALL_BLANK_ROW = new ArrayList<>();
public static void main(String[] args) {
Map<Integer, List<Cell>> excelData = new HashMap<>();
try {
// 読み込み
InputStream input = new FileInputStream(INPUT_FILE_PATH);
Workbook wb = WorkbookFactory.create(input);
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
Sheet sheet = wb.getSheetAt(i);
for (int j = 0; j < sheet.getLastRowNum(); j++) {
Row row1 = sheet.getRow(j);
Iterator<Cell> itr = row1.iterator();
List<Cell> cellValueList = new ArrayList<>();
while (itr.hasNext()) {
cellValueList.add(itr.next());
//System.out.print(itr.next() + "\t");
}
createRowIndexByBlank(cellValueList);
excelData.put(j, cellValueList);
//System.out.println();
}
}
} catch (FileNotFoundException e) {
// TODO 自動生成された catch ブロック
e.printStackTrace();
} catch (EncryptedDocumentException e) {
// TODO 自動生成された catch ブロック
e.printStackTrace();
} catch (IOException e) {
// TODO 自動生成された catch ブロック
e.printStackTrace();
}
// 書き込み
//OutputStream output = null;
try (OutputStream output = new FileOutputStream(OUTPUT_FILE_PATH);
Workbook wbW = new XSSFWorkbook();) {
// wbW = WorkbookFactory.create(true);
// Font wbFont = wbW.createFont();
// wbFont.setCharSet(XSSFFont.ANSI_CHARSET);
Sheet sheetW = wbW.createSheet();
int[] columnIndex = new int[COLUMN_BY_WRITING_EXCEL.length];
// Excelの先頭行（1行目）
Row currentRow = sheetW.createRow(0);
for (int index = 0; index < COLUMN_BY_WRITING_EXCEL.length; index++) {
currentRow.createCell(index).setCellValue(COLUMN_BY_WRITING_EXCEL[index]);
}
// Excelの2行目から
for (Map.Entry<Integer, List<Cell>> entry : excelData.entrySet()) {
List<Cell> cellValueList = entry.getValue();
// 2回目の空白行の後の行の場合
if (entry.getKey() > ALL_BLANK_ROW.get(1)) {
currentRow = sheetW.createRow(entry.getKey() - ALL_BLANK_ROW.get(1));
}
for (int index = 0; index < cellValueList.size(); index++) {
Cell cell = cellValueList.get(index);
// Excelのヘッダー部分のデータから抽出するカラムの列数を決める
if ((CellType.BLANK != cell.getCellType()) && CellType.STRING == cell.getCellType()) {
for (int headerIndex = 0; headerIndex < columnIndex.length; headerIndex++) {
if (COLUMN_BY_WRITING_EXCEL[headerIndex].equals(cell.getStringCellValue())) {
columnIndex[headerIndex] = cell.getColumnIndex();
}
}
}
//
if (cell.getRowIndex() > ALL_BLANK_ROW.get(1) && (CellType.BLANK != cell.getCellType())) {
for (int headerIndex = 0; headerIndex < columnIndex.length; headerIndex++) {
if (columnIndex[headerIndex] == cell.getColumnIndex()) {
setCellValue(cell, currentRow.createCell(headerIndex));
// コンソール出力（確認用）
System.out.print(cell + "\t");
if (headerIndex == columnIndex.length - 1) {
System.out.println();
}
}
}
}
}
}
Row calcRow = sheetW.createRow(sheetW.getLastRowNum()-1);
for (int index = 0; index < COLUMN_BY_WRITING_EXCEL.length; index++) {
if (index == columnIndex[0]) {
calcRow.createCell(index).setCellValue("合計");
}
if (index == COLUMN_BY_WRITING_EXCEL.length - 1) {
calcRow.createCell(index).setCellFormula(
"SUM(B" + (sheetW.getFirstRowNum() + 3) + ":B" + (sheetW.getLastRowNum() - 1 + ")"));
}
}
//sheetW.setForceFormulaRecalculation(true);
wbW.getCreationHelper().createFormulaEvaluator().evaluateAll();
// Cellにセットした値をExcelに書き込み
wbW.write(output);
} catch (FileNotFoundException e) {
// TODO 自動生成された catch ブロック
e.printStackTrace();
} catch (IOException e) {
// TODO 自動生成された catch ブロック
e.printStackTrace();
}
}
// Excelの空白行の確認のメソッド
private static void createRowIndexByBlank(List<Cell> cellList) {
int blankCount = 0;
for (Cell cell : cellList) {
if (CellType.BLANK != cell.getCellType()) {
break;
}
blankCount++;
}
// 空白行の行数をリストに保存する
if (blankCount == cellList.size()) {
ALL_BLANK_ROW.add(cellList.get(0).getRowIndex());
}
}
// Cellに値をセットするようのメソッド
private static void setCellValue(Cell readValue, Cell writeValue) {
switch (readValue.getCellType()) {
case STRING:
writeValue.setCellValue(readValue.getRichStringCellValue().getString());
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(readValue)) {
writeValue.setCellValue(readValue.getDateCellValue());
} else {
writeValue.setCellValue(readValue.getNumericCellValue());
}
break;
case BOOLEAN:
writeValue.setCellValue(readValue.getBooleanCellValue());
break;
case FORMULA:
writeValue.setCellValue(readValue.getCellFormula());
break;
default:
break;
}
}
// Eclipseで「Gradleプロジェクト」作成するとデフォルトで用意されてるメソッド
// 今回は不要
public boolean someLibraryMethod() {
return true;
}
}
⇧ ってな感じで、編集します。ダウンロードするExcelファイルの内容が変わってる場合は、「COLUMN_BY_WRITING_EXCEL」にある名前を変更したりが必要になってきますかね。
では、「Java アプリケーション」として実行してみましょう。
実行した結果、「src/main/resources/output/n210200200_output.xlsx」ってファイルが作成され、Excelから抽出した内容が「コンソール」にも出力されてればOK。
まぁ、何て言うか、Pythonとかならもっと楽にExcelを扱えそうな気がするんだけど、JavaでExcelのデータ取得とか面倒くさすぎて辛い...
そして、実装後に、実務で有益そうな情報が見つかるという...
20分も待てば、232M の .xlsx ファイルができあがる。
（XSSF だと、オンメモリ処理で、メモリを大量に食いつぶすので、SXSSF じゃないとだめ。）
Apache POI でつくった 200,000 × 255 の .xlsx ファイルを Microsoft Excel 2010 で開く - ながさわさんのアレグロモデラート
⇧ 上記サイト様によりますと、「XSSF」は宜しくないようです...
な～に～！？やっちまったな！
というわけで、実業務で「Apache POI」を使う場合は、「SXSSF」を使うようにしとかないと、大容量なサイズのExcelは扱えないらしいので気を付けましょう。
今回はこのへんで。
