LoginSignup
2
0

More than 3 years have passed since last update.

@spacecow

JavaでExcelを操作しよう！！

Posted at

Excel

まずは、Excelファイルを用意しよう！

6668de5bf05cb524e045cb3aabae269b.png

Java

javaExcel

import java.io.File;
import java.io.IOException;
import java.text.DateFormat;
import java.util.Date;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
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;

/**
 * Excelのcellの値を取得する
 *
 */
public class ExcelC {

    public static final String SAMPLE_XLSX_FILE_PATH = "D:\\filesample\\sampleBook.xlsx";

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

        // Creating a Workbook from an Excel file (.xls or .xlsx)
        Workbook workbook = WorkbookFactory.create(new File(SAMPLE_XLSX_FILE_PATH));

        Sheet sheet = workbook.getSheetAt(0);
        Row head = sheet.getRow(1);
        ExcelC aaa = new ExcelC();
        DataFormatter dataFormatter = new DataFormatter();
        //cellrow
        int bb = 3;
        //cellY座標
        for(int y = 2 ; y < 12 ; y++) {
            Row row = sheet.getRow(y);
            System.out.print("Row" + bb + "\r\n");
            bb ++;
            //cellX座標
            for (int i = 0 ; i < 11 ; i++){
                Cell headc = head.getCell(i);
                Cell cell = row.getCell(i);
                String cellValue = dataFormatter.formatCellValue(headc);
                System.out.print(cellValue + ": ");
                aaa.printCellValue(cell);
                System.out.print("\r\n");

            }
            System.out.print("\r\n");
        }
        // Closing the workbook
        workbook.close();
    }

    private static void printCellValue(Cell cell) {
        DateFormat fd = DateFormat.getDateInstance(DateFormat.FULL);
        switch (cell.getCellTypeEnum()) {
            case BOOLEAN:
                System.out.print(cell.getBooleanCellValue());
                break;
            case STRING:
                System.out.print(cell.getRichStringCellValue().getString());
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    Date kkk = cell.getDateCellValue();
                    System.out.print(fd.format(kkk));
                } else {
                    System.out.print(cell.getNumericCellValue());
                }
                break;
            case FORMULA:
                System.out.print(cell.getCellFormula());
                break;
            case BLANK:
                System.out.print("");
                break;
            default:
                System.out.print("");
        }
        System.out.print("\t");
    }
}

実行結果

Row3
ida: a1 
ida: a2 
id: a3  
id: a4  
id: a5  
id: a6  
id: a7  
id: a8  
id: a9  
id: a10 
ida: a11    

Row4
ida: b1 
ida: b2 
id: b3  
id: b4  
id: b5  
id: b6  
id: b7  
id: b8  
id: b9  
id: b10 
ida: b11    

Row5
ida: c1 
ida: c2 
id: c3  
id: c4  
id: c5  
id: c6  
id: c7  
id: c8  
id: c9  
id: c10 
ida: c11    

Row6
ida: d1 
ida: d2 
id: d3  
id: d4  
id: d5  
id: d6  
id: d7  
id: d8  
id: d9  
id: d10 
ida: d11    

Row7
ida: e1 
ida: e2 
id: e3  
id: e4  
id: e5  
id: e6  
id: e7  
id: e8  
id: e9  
id: e10 
ida: e11    

Row8
ida: f1 
ida: f2 
id: f3  
id: f4  
id: f5  
id: f6  
id: f7  
id: f8  
id: f9  
id: f10 
ida: f11    

Row9
ida: g1 
ida: g2 
id: g3  
id: g4  
id: g5  
id: g6  
id: g7  
id: g8  
id: g9  
id: g10 
ida: g11    

Row10
ida: h1 
ida: h2 
id: h3  
id: h4  
id: h5  
id: h6  
id: h7  
id: h8  
id: h9  
id: h10 
ida: h11    

Row11
ida: i1 
ida: i2 
id: i3  
id: i4  
id: i5  
id: i6  
id: i7  
id: i8  
id: i9  
id: i10 
ida: i11    

Row12
ida: j1 
ida: j2 
id: j3  
id: j4  
id: j5  
id: j6  
id: j7  
id: j8  
id: j9  
id: j10 
ida: j11

Java（Iteratorで実装）

javaExcelIterator
import java.io.File;
import java.io.IOException;
import java.text.DateFormat;
import java.util.Date;
import java.util.Iterator;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
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;

/**
 * Excelのcellの値を取得する(Iterator)
 *
 */
public class GetOutExcel {

    public static final String SAMPLE_XLSX_FILE_PATH = "D:\\filesample\\sampleBook.xlsx";

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

        // Creating a Workbook from an Excel file (.xls or .xlsx)
        Workbook workbook = WorkbookFactory.create(new File(SAMPLE_XLSX_FILE_PATH));

        // Retrieving the number of sheets in the Workbook
        System.out.println("Workbook has " + workbook.getNumberOfSheets() + " Sheets : ");

        // 1. You can obtain a sheetIterator and iterate over it
        Iterator<Sheet> sheetIterator = workbook.sheetIterator();
        System.out.println("Retrieving Sheets using Iterator");
        while (sheetIterator.hasNext()) {
            Sheet sheet = sheetIterator.next();
            System.out.println("=> " + sheet.getSheetName());
        }

        // Getting the Sheet at index zero
        Sheet sheet = workbook.getSheetAt(0);

        // Create a DataFormatter to format and get each cell's value as String
        DataFormatter dataFormatter = new DataFormatter();

        //besides String
        GetOutExcel aaa = new GetOutExcel();

        // 1. You can obtain a rowIterator and columnIterator and iterate over them
        System.out.println("\n\nIterating over Rows and Columns using Iterator\n");
        Iterator<Row> rowIterator = sheet.rowIterator();
        int num = 0;
        int num2 = 0;
        String head;
        while (rowIterator.hasNext()) {
            num ++;
            Row row = rowIterator.next();

            // Now let's iterate over the columns of the current row
            Iterator<Cell> cellIterator = row.cellIterator();
            System.out.print("column"+ num +": ");

            while (cellIterator.hasNext()) {

                if(num2 >= 10) {
                    num2 -= 10;
                }
                num2 ++;
                Cell cell = cellIterator.next();
//                String cellValue = dataFormatter.formatCellValue(cell);

//                System.out.print(cellValue + "\t");
//                System.out.print("column" + num2 + ": " + cellValue + "\r\n");


                //besides String
              aaa.printCellValue(cell);
            }
            System.out.println();
        }
        // Closing the workbook
        workbook.close();

    }

    //besides String
    private static void printCellValue(Cell cell) {
        DateFormat fd = DateFormat.getDateInstance(DateFormat.FULL);
        switch (cell.getCellTypeEnum()) {
            case BOOLEAN:
                System.out.print(cell.getBooleanCellValue());
                break;
            case STRING:
                System.out.print(cell.getRichStringCellValue().getString());
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    Date kkk = cell.getDateCellValue();
                    System.out.print(fd.format(kkk));
                } else {
                    System.out.print(cell.getNumericCellValue());
                }
                break;
            case FORMULA:
                System.out.print(cell.getCellFormula());
                break;
            case BLANK:
                System.out.print("");
                break;
            default:
                System.out.print("");
        }

        System.out.print("\t");
    }
}

実行結果（Iterator）

Workbook has 1 Sheets : 
Retrieving Sheets using Iterator
=> Sheet1


Iterating over Rows and Columns using Iterator

column1: xx xx  xx  xx  xx  xx  xx  xx  xx  xx  xx  xx  xx  
column2: ida    ida id  id  id  id  id  id  id  id  ida ida ida 
column3: a1 a2  a3  a4  a5  a6  a7  a8  a9  a10 a11 a12 a13 
column4: b1 b2  b3  b4  b5  b6  b7  b8  b9  b10 b11 b12 b13 
column5: c1 c2  c3  c4  c5  c6  c7  c8  c9  c10 c11 c12 c13 
column6: d1 d2  d3  d4  d5  d6  d7  d8  d9  d10 d11 d12 d13 
column7: e1 e2  e3  e4  e5  e6  e7  e8  e9  e10 e11 e12 e13 
column8: f1 f2  f3  f4  f5  f6  f7  f8  f9  f10 f11 f12 f13 
column9: g1 g2  g3  g4  g5  g6  g7  g8  g9  g10 g11 g12 g13 
column10: h1    h2  h3  h4  h5  h6  h7  h8  h9  h10 h11 h12 h13 
column11: i1    i2  i3  i4  i5  i6  i7  i8  i9  i10 i11 i12 i13 
column12: j1    j2  j3  j4  j5  j6  j7  j8  j9  j10 j11 j12 j13 
column13: k1    k2  k3  k4  k5  k6  k7  k8  k9  k10 k11 k12 k13 
column14: l1    l2  l3  l4  l5  l6  l7  l8  l9  l10 l11 l12 l13 
column15: m1    m2  m3  m4  m5  m6  m7  m8  m9  m10 m11 m12 m13 
column16: n1    n2  n3  n4  n5  n6  n7  n8  n9  n10 n11 n12 n13
2
0
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
2
0