Java Apache POIでExcelの非表示列を判定するサンプル

環境
ApachePOI 4.1.2
Eclipse 4.24
Java SE-11

構文
1.Excelファイルを読み込みます
InputStream型の変数名1 = new FileInputStream(ファイルのパス);
Workbook型の変数名2 = WorkbookFactory.create(InputStream型の変数名1);

2.1番目のシートを読み込みます
Sheet sheet = Workbook型の変数名2 .getSheetAt(0);

3.非表示列の判定
for(Cell cell : row){
// 列が非表示か判定
if(sheet.isColumnHidden(cell.getColumnIndex())){処理コード}

使用例

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
package com.arkgame.study;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import org.apache.poi.EncryptedDocumentException;
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;
public class ExcelRead2 {
public static final String filePath = "C:\\tpes_fip\\2023\\\\memberinfo_B.xlsx";
public static void main(String[] args) throws EncryptedDocumentException, IOException {
InputStream inputStream = null;
Workbook workbook = null;
FileOutputStream fileOutputStream = null;
try {
// Excelファイルを読み込む
inputStream = new FileInputStream(filePath);
workbook = WorkbookFactory.create(inputStream);
// 1番目のシートを読み込む
Sheet sheet = workbook.getSheetAt(0);
int colmIndex = 0;
int res = 0;
for (Row row : sheet) {
colmIndex = 0;
for (Cell cell : row) {
// 列が非表示か判定
if (sheet.isColumnHidden(cell.getColumnIndex())) {
res = ++colmIndex;
}
colmIndex++;
}
}
System.out.println(res + "列");
// Excelファイル出力
fileOutputStream = new FileOutputStream(filePath);
workbook.write(fileOutputStream);
} catch (Exception ex) {
ex.printStackTrace();
} finally {
try {
workbook.close();
fileOutputStream.close();
} catch (Exception ex2) {
ex2.printStackTrace();
}
}
}
}
package com.arkgame.study; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import org.apache.poi.EncryptedDocumentException; 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; public class ExcelRead2 { public static final String filePath = "C:\\tpes_fip\\2023\\\\memberinfo_B.xlsx"; public static void main(String[] args) throws EncryptedDocumentException, IOException { InputStream inputStream = null; Workbook workbook = null; FileOutputStream fileOutputStream = null; try { // Excelファイルを読み込む inputStream = new FileInputStream(filePath); workbook = WorkbookFactory.create(inputStream); // 1番目のシートを読み込む Sheet sheet = workbook.getSheetAt(0); int colmIndex = 0; int res = 0; for (Row row : sheet) { colmIndex = 0; for (Cell cell : row) { // 列が非表示か判定 if (sheet.isColumnHidden(cell.getColumnIndex())) { res = ++colmIndex; } colmIndex++; } } System.out.println(res + "列"); // Excelファイル出力 fileOutputStream = new FileOutputStream(filePath); workbook.write(fileOutputStream); } catch (Exception ex) { ex.printStackTrace(); } finally { try { workbook.close(); fileOutputStream.close(); } catch (Exception ex2) { ex2.printStackTrace(); } } } }
package com.arkgame.study;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;

import org.apache.poi.EncryptedDocumentException;
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;

public class ExcelRead2 {

      public static final String filePath = "C:\\tpes_fip\\2023\\\\memberinfo_B.xlsx";

      public static void main(String[] args) throws EncryptedDocumentException, IOException {

            InputStream inputStream = null;
            Workbook workbook = null;
            FileOutputStream fileOutputStream = null;

            try {
                  // Excelファイルを読み込む
                  inputStream = new FileInputStream(filePath);
                  workbook = WorkbookFactory.create(inputStream);

                  // 1番目のシートを読み込む
                  Sheet sheet = workbook.getSheetAt(0);

                  int colmIndex = 0;

                  int res = 0;
                  for (Row row : sheet) {
                        colmIndex = 0;

                        for (Cell cell : row) {
                              // 列が非表示か判定
                              if (sheet.isColumnHidden(cell.getColumnIndex())) {
                                    res = ++colmIndex;

                              }
                              colmIndex++;
                        }

                  }
                  System.out.println(res + "列");

                  // Excelファイル出力
                  fileOutputStream = new FileOutputStream(filePath);
                  workbook.write(fileOutputStream);

            } catch (Exception ex) {
                  ex.printStackTrace();
            } finally {

                  try {
                        workbook.close();
                        fileOutputStream.close();
                  } catch (Exception ex2) {
                        ex2.printStackTrace();
                  }
            }

      }

}

実行結果
3列

Java

Posted by arkgame