Excel 拡張子 (xls) (xlsx) 両方に対応した操作 Apache POI（１）

久々に、Apache POI に触れます。
　Excel 2003 の形式、拡張子 .xls
　Excel 2007 からの形式、拡張子 .xlsx
両方に対応するには、
org.apache.poi.ss.usermodel.* にある
WorkbookFactory　で、Workbook を取得して

Workbook book = WorkbookFactory.create(inputstream);

　org.apache.poi.ss.usermodel.Workbook
　org.apache.poi.ss.usermodel.WorkbookFactory
　org.apache.poi.ss.usermodel.Cell
　org.apache.poi.ss.usermodel.Row
　org.apache.poi.ss.usermodel.Sheet
これらを使うんですね。

Stream 処理するように、作業用クラスを用意しておけば充分です。
と、以下に書いたのだけれど、、、

import java.io.InputStream;
import java.io.OutputStream;
import java.util.function.Consumer;
import java.util.function.Predicate;
import java.util.stream.IntStream;
import java.util.stream.Stream;
import org.apache.poi.ss.usermodel.Cell;
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;
/**
 * ExcelWorker
 */
public class ExcelWorker{
    private InputStream inst;
    public ExcelWorker(InputStream inst) {
        this.inst = inst;
    }
    public void readOneSheet(Consumer<Row> action) {
        try(Workbook book = WorkbookFactory.create(inst)){
            Sheet sheet = book.getSheetAt(0);
            IntStream.rangeClosed(0, sheet.getLastRowNum()).mapToObj(n->sheet.getRow(n)).forEach(row->action.accept(row));
        }catch(Exception ex){
            throw new RuntimeException(ex);
        }
    }
    public void readOneSheet(Predicate<Row> pre, Consumer<Row> action) {
        try(Workbook book = WorkbookFactory.create(inst)){
            Sheet sheet = book.getSheetAt(0);
            IntStream.rangeClosed(0, sheet.getLastRowNum()).mapToObj(n->sheet.getRow(n)).filter(pre).forEach(row->action.accept(row));
        }catch(Exception ex){
            throw new RuntimeException(ex);
        }
    }
    public static Stream<Cell> getCellStream(Row row){
        return IntStream.range(0, row.getLastCellNum()).mapToObj(n->row.getCell(n));
    }
    public void writeOneSheet(OutputStream outst, Consumer<Row> action) {
        try(Workbook book = WorkbookFactory.create(inst)){
            Sheet sheet = book.getSheetAt(0);
            IntStream.rangeClosed(0, sheet.getLastRowNum()).mapToObj(n->sheet.getRow(n)).forEach(row->action.accept(row));
            book.write(outst);
            outst.flush();
        }catch(Exception ex){
            throw new RuntimeException(ex);
        }
    }
    public void writeOneSheet(OutputStream outst, Predicate<Row> pre, Consumer<Row> action) {
        try(Workbook book = WorkbookFactory.create(inst)){
            Sheet sheet = book.getSheetAt(0);
            IntStream.rangeClosed(0, sheet.getLastRowNum()).mapToObj(n->sheet.getRow(n)).filter(pre).forEach(row->action.accept(row));
            book.write(outst);
            outst.flush();
        }catch(Exception ex){
            throw new RuntimeException(ex);
        }
    }
}

使用サンプルは、、、

File file = new File(Thread.currentThread().getContextClassLoader().getResource("./"+filename).getPath());
try(InputStream inst = new FileInputStream(file)){
    ExcelWorker worker = new ExcelWorker(inst);
    worker.readOneSheet(row->{
        System.out.println("--- getRowNum() = " + row.getRowNum());
        ExcelWorker.getCellStream(row).forEach(cell->{
            System.out.print(" ["+cell.getColumnIndex()+"]=");
            if (cell.getCellType().equals(CellType.STRING)) {
                // 文字列
                System.out.print(cell.getStringCellValue());
            }else if(cell.getCellType().equals(CellType.NUMERIC)) {
                if (DateUtil.isCellDateFormatted(cell)){
                    // 日付 or 時刻
                    //LocalDate date = cell.getDateCellValue().toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
                    //System.out.print(date);
                    LocalDateTime datetime = cell.getDateCellValue().toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime();
                    System.out.print(datetime);
                }else{
                    // 数値
                    double d = cell.getNumericCellValue();
                    System.out.print(d);
                }
            }else if(cell.getCellType().equals(CellType.BOOLEAN)) {
                // TODO Boolean
            }else if(cell.getCellType().equals(CellType.BLANK)) {
                // TODO blank
            }
        });
        System.out.print("\n");
    });
}catch(IOException e){
    e.printStackTrace();
}

oboe2uran.hatenablog.com

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

POI のバージョンも、上がってました。。。

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

poi-ooxml は、対象が xlsx であれば実行時に必要です。