JavaでExcelを操作しよう！！

###Excel

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

![6668de5bf05cb524e045cb3aabae269b.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/652427/7b6a8eea-2551-4753-ab82-fe427cab9bfd.png)


###Java

```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で実装）
```java: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

```