LoginSignup
3
5

More than 5 years have passed since last update.

@anchorIndigo_215632(crossfish21)

apache POI 自分用メモ crossfish21

Posted at

基本操作

import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.apache.poi.ss.formula.EvaluationWorkbook;
import org.apache.poi.ss.formula.FormulaParser;
import org.apache.poi.ss.formula.FormulaRenderer;
import org.apache.poi.ss.formula.FormulaRenderingWorkbook;
import org.apache.poi.ss.formula.FormulaType;
import org.apache.poi.ss.formula.ptg.AreaPtg;
import org.apache.poi.ss.formula.ptg.Ptg;
import org.apache.poi.ss.formula.ptg.RefPtgBase;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetProtection;

/**
* ユーザーごとの顧客関連設定値をDBに登録するサーブレット
*/
public class Class01 {

  /*
     * Cell を返す。
     *
     * @param args
     *            行
     */
  public static void main(String[] args) {

//        aaa();   // 新規ブック作成
//        bbb();   // 既存ブックの読み込み
//        blankCheck();   // 空白チェック
        rowAndColumn();   // 行と列の操作
//        book();   // ブック操作
//        cell();  // セル操作



    }

  public static void aaa() {

        try(XSSFWorkbook book1 = new XSSFWorkbook()) {

            XSSFSheet sheet1 = book1.createSheet("シート01");
            // シートを作成する。初期状態ではシートが存在しない

            XSSFRow row01 = sheet1.createRow(0);
            // 行のインスタンス生成。1行目が0になる

            XSSFCell cell01 = row01.createCell(0);
            //セルのインスタンス生成。1列目が０になる

            cell01.setCellValue("AAA");  // セルの値を設定


            XSSFCell cell02 = row01.createCell(1);
            cell02.setCellValue("BBB");

            XSSFCell cell03 = row01.createCell(2);
            cell03.setCellFormula("A1&B1");  // セルの数式を設定。「＝」はいらないらしい


            try(FileOutputStream out = new FileOutputStream("C:/work/aaa.xlsx")) {
                book1.write(out);
                //新規ブックとして出力する。既存のファイルパスを指定した場合は上書きされる
            } catch (Exception e) {
                e.printStackTrace();
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
   }



    public static void bbb(){  // 既存ブックの読み込み

        try(FileInputStream input = new FileInputStream("C:/work/bbb.xlsx")){

            try(XSSFWorkbook book1 = (XSSFWorkbook) WorkbookFactory.create(input)){

                // ※シートに保護かかかっていても、読み込みは問題ないらしい

                XSSFSheet sheet1 = book1.getSheet("Sheet1");  // シート"Sheet1"を取得
                Row row02 = sheet1.getRow(1);  // 2行目を取得
                Cell cell01 = row02.getCell(0);  // 2行目のA1セル取得
                Cell cell02 = row02.getCell(1);  // 2行目のB1セル取得
                Cell cell03 = row02.getCell(2);

                String str1 = cell01.getStringCellValue();
                // セルの文字列を取得。セルの値が数値の場合は読み込みエラーになるので注意
                System.out.println(str1);

                Double double01 = cell02.getNumericCellValue();
                //数値を取得する
                System.out.println(double01);

                try {
                    String str3 = cell03.getCellFormula();
                    //数式を取得する。数式以外が入っているとエラーになる
                    System.out.println(str3);
                } catch (IllegalStateException e) {
                    // 数式以外が入っているセルから数式を取得した場合は、IllegalStateExceptionが発生
                    e.printStackTrace();
                }


                // セルのパラメータを取得
                System.out.println(cell01.getColumnIndex());  // 列番号を取得。０から始まる数値なので注意
                System.out.println(cell01.getRowIndex());  // 行番号を取得。０から始まる数値なので注意
                System.out.println(cell01.getAddress());  // セルのアドレスを取得。これはエクセルのアドレスがそのまま取得される

            } catch (Exception e) {
                e.printStackTrace();
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }



    public static void blankCheck(){  // セルが空白であるかどうかをチェックする

        try(FileInputStream input = new FileInputStream("C:/work/bbb.xlsx")){

            try(XSSFWorkbook book1 = (XSSFWorkbook) WorkbookFactory.create(input)){

                XSSFSheet sheet1 = book1.getSheet("Sheet1");

                // 空白のセルの場合はどうなるか
                Row row03 = sheet1.getRow(2);  // 3行目を取得
                Cell cell01 = row03.getCell(0);
                Cell cell02 = row03.getCell(1);
                Cell cell03 = row03.getCell(2);

                if (cell01 == null || cell01.getCellType() == Cell.CELL_TYPE_BLANK) {
                // これで空白かどうかを判断できる。nullチェックは必要なのか?
                    System.out.println("セル" + cell01.getAddress() + "は空白");
                }else{
                    String str1 = cell01.getStringCellValue();
                    System.out.println(str1);
                }


                if (cell02 == null || cell02.getCellType() == Cell.CELL_TYPE_BLANK) {
                    System.out.println("セル" + cell02.getAddress() + "は空白");
                }else{
                    Double double01 = cell02.getNumericCellValue();
                    System.out.println(double01);
                }

                if (cell03 == null || cell03.getCellType() == Cell.CELL_TYPE_BLANK) {
                    System.out.println("セル" + cell03.getAddress() + "は空白");
                }else{
                    String str4= cell03.getCellFormula();
                    System.out.println(str4);
                }

            } catch (Exception e) {
                e.printStackTrace();
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

    }


    public static void rowAndColumn(){

        try(FileInputStream input = new FileInputStream("C:/work/行と列.xlsx")){

            try(XSSFWorkbook book1 = (XSSFWorkbook) WorkbookFactory.create(input)){

                XSSFSheet sheet1 = book1.getSheet("Sheet1");  // シート"Sheet1"を取得
//                Row row05 = sheet1.getRow(4);  // 5行目を取得
//                row05.setZeroHeight(true);
//                // 非表示にする。すでに非表示の行でも、エラーにはならない
//
//                Row row08 = sheet1.getRow(7);
//                row08.setZeroHeight(false);
//                // 非表示の列を再表示する。非表示ではない行でも、エラーにはならない
//
//                Row row11 = sheet1.getRow(10);
//                sheet1.removeRow(row11);
//                // 11行目全体の値を削除(らしい)。行自体が削除されるのではない
//                //行自体は削除されないが、セルの書式や罫線などは全てクリアされているらしい
//
//                Row row12 = sheet1.getRow(11);
//                row12.setHeightInPoints(50.25F);
//                // 行の高さを設定。floatでないと駄目。0.25単位で設定する。それ以上細かい設定をしても、丸められる
//
//                sheet1.shiftRows(9,14,-5);
//                // 10～15行目を上に5行移動させる。結合セルがあっても可能
//                // セルの書式や罫線等もそのまま反映される
//
//                book1.setForceFormulaRecalculation(true);
//                // 行の移動や、行の内容削除などで、数式の計算結果がおかしくなる場合は上のように再計算を実行するといい




                // ※  Row row05 = sheet1.getRow(4);  のように、行単位では操作が可能だが、列単位ではできないらしい

                sheet1.setColumnHidden(4, true);  // 5列目を非表示にする
                sheet1.setColumnHidden(7, false);  // 3列目を再表示する




                try(FileOutputStream out = new FileOutputStream("C:/work/行と列.xlsx")) {
                    book1.write(out);
                } catch (Exception e) {
                    e.printStackTrace();
                }

            } catch (Exception e) {
                e.printStackTrace();
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }




    public static void book(){  // ブック関連

        try(FileInputStream input = new FileInputStream("C:/work/ccc.xlsx")){

            try(XSSFWorkbook book1 = (XSSFWorkbook) WorkbookFactory.create(input)){

                XSSFSheet sheet1 = book1.getSheet("Sheet1");  // シート"Sheet1"を取得
                Row row01 = sheet1.getRow(0);  // 1行目を取得

                book1.setForceFormulaRecalculation(true);  // 再計算を実行する


                try(FileOutputStream out = new FileOutputStream("C:/work/ccc.xlsx")) {
                    book1.write(out);
                } catch (Exception e) {
                    e.printStackTrace();
                }

            } catch (Exception e) {
                e.printStackTrace();
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }




    public static void cell(){  // セル関連

        try(FileInputStream input = new FileInputStream("C:/work/セル.xlsx")){

            try(XSSFWorkbook book1 = (XSSFWorkbook) WorkbookFactory.create(input)){

                XSSFSheet sheet1 = book1.getSheet("Sheet1");  // シート"Sheet1"を取得


                Row row11 = sheet1.getRow(10);  // 11行目を取得
                Cell cell01 = row11.getCell(0);
                Cell cell02 = row11.createCell(1);  // 値を設定する場合は、createCellにする
//                cell02.setCellFormula(cell01.getCellFormula());
                // 数式のコピー。しかし、取得できるのは文字列なので、相対参照の数式をコピーできるわけではない
                copyCell2Cell(cell01,cell02,book1);


                try(FileOutputStream out = new FileOutputStream("C:/work/セル.xlsx")) {
                    book1.write(out);
                } catch (Exception e) {
                    e.printStackTrace();
                }

            } catch (Exception e) {
                e.printStackTrace();
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

数式を相対参照でコピーする

import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.apache.poi.ss.formula.EvaluationWorkbook;
import org.apache.poi.ss.formula.FormulaParser;
import org.apache.poi.ss.formula.FormulaRenderer;
import org.apache.poi.ss.formula.FormulaRenderingWorkbook;
import org.apache.poi.ss.formula.FormulaType;
import org.apache.poi.ss.formula.ptg.AreaPtg;
import org.apache.poi.ss.formula.ptg.Ptg;
import org.apache.poi.ss.formula.ptg.RefPtgBase;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class CellFormulaCopy {

    public static void main(String[] args) {

        try(FileInputStream input = new FileInputStream("C:/work/セル.xlsx")){

            try(XSSFWorkbook book1 = (XSSFWorkbook) WorkbookFactory.create(input)){

                XSSFSheet sheet1 = book1.getSheet("Sheet1");  // シート"Sheet1"を取得

                Row row11 = sheet1.getRow(10);  // 11行目を取得
                Cell cell01 = row11.getCell(0);
                Cell cell02 = row11.createCell(1);  // 値を設定する場合は、createCellにする

                cell02.setCellFormula(cell01.getCellFormula());
                // 数式のコピー。しかし、取得できるのは文字列なので、相対参照の数式をコピーできるわけではない

                copyFormula(cell01,cell02,book1);
                // 数式を相対参照でコピーする。copyFormulaの数式を、cell02にコピー

                try(FileOutputStream out = new FileOutputStream("C:/work/セル.xlsx")) {
                    book1.write(out);
                } catch (Exception e) {
                    e.printStackTrace();
                }

            } catch (Exception e) {
                e.printStackTrace();
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    public static void copyFormula(Cell srcCell, Cell destCell, XSSFWorkbook book) {
    // 相対参照で数式をコピーする
    // srcCell コピー元   destCell コピー先

        String formula = srcCell.getCellFormula();
        EvaluationWorkbook ew;
        FormulaRenderingWorkbook rw;
        Ptg[] ptgs;

        ew = XSSFEvaluationWorkbook.create((XSSFWorkbook) book);
        ptgs = FormulaParser.parse(formula, (XSSFEvaluationWorkbook) ew, FormulaType.CELL, 0);
        //詳しいことはわからないが、最後のパラメータはシート番号を０始まりで指定
        rw = (XSSFEvaluationWorkbook) ew;

        for (Ptg ptg : ptgs) {
        // 座標の計算
            int shiftRows = destCell.getRowIndex() - srcCell.getRowIndex();
            int shiftCols = destCell.getColumnIndex() - srcCell.getColumnIndex();

            if (ptg instanceof RefPtgBase) {
                RefPtgBase ref = (RefPtgBase) ptg;

                if (ref.isColRelative()) {
                    ref.setColumn(ref.getColumn() + shiftCols);
                }

                if (ref.isRowRelative()) {
                    ref.setRow(ref.getRow() + shiftRows);
                }

            } else if (ptg instanceof AreaPtg) {
                AreaPtg ref = (AreaPtg) ptg;

                if (ref.isFirstColRelative()) {
                    ref.setFirstColumn(ref.getFirstColumn() + shiftCols);
                }

                if (ref.isLastColRelative()) {
                    ref.setLastColumn(ref.getLastColumn() + shiftCols);
                }

                if (ref.isFirstRowRelative()) {
                    ref.setFirstRow(ref.getFirstRow() + shiftRows);
                }

                if (ref.isLastRowRelative()) {
                    ref.setLastRow(ref.getLastRow() + shiftRows);
                }
            }
        }

        destCell.setCellFormula(FormulaRenderer.toFormulaString(rw, ptgs));
    }
}
3
5
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
3
5