자바에서 엑셀 파일 poi 로 읽기

얘전방식코드 개선겸

 

package tset;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Calendar;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.IndexedColors;
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.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class main2 {

	public static void main(String[] args) throws Exception {

		// 파일 읽기
		String filename = "test.xlsx";
		try {
			File xlsFile = new File("C:\\Users\\Noah\\Documents\\" + filename);
			FileInputStream fileIn = new FileInputStream(xlsFile);

			// workBook 생성
			XSSFWorkbook wb = new XSSFWorkbook(fileIn);

			// Sheet 읽기
			// for문으로 읽었지만 보통 XSSFSheet sheet = wb.getSheetAt(0); 으로 첫번째만 읽을 수 있음
			for (int sheetNumber = 0; sheetNumber < wb.getNumberOfSheets(); sheetNumber++) {

				XSSFSheet sheet = wb.getSheetAt(sheetNumber);

				for (int rowNumber = 0; rowNumber < sheet.getPhysicalNumberOfRows(); rowNumber++) {

					XSSFRow row = sheet.getRow(rowNumber);

					if (row == null) {
						continue;
					} else {
						for (int colNumber = 0; colNumber < row.getPhysicalNumberOfCells(); colNumber++) {
							XSSFCell cell = row.getCell(colNumber);
							String value = "";

							// 널값 방지
							if (cell == null) {
								continue;
							} else {

								//타입별 정리 
								switch (cell.getCellType()) {
								case STRING: // 텍스트
									value = cell.getStringCellValue();
									break;
								case NUMERIC: // 숫자
									value = cell.getNumericCellValue() + "";
									break;
								case FORMULA: // = 붙은 계산식 처리
									// cell.getCachedFormulaResultType() 결과타입 불러올때 
									FormulaEvaluator formulaEval = wb.getCreationHelper().createFormulaEvaluator();
									//value = cell.getCellFormula() 를 사용하면 =123+100 일때 123+100 을 표시한다. 따라서 계산을 하려면 아래코드를 쓴다. 
									value = formulaEval.evaluate(cell).formatAsString();
									
									break;
								case BLANK: // 빈칸
									value = cell.getBooleanCellValue() + "" ;
									break;

								case ERROR: // 에러난 처리 
									value = cell.getErrorCellValue() +"";
									break;

								}
								System.out.println(sheetNumber + " Sheet, " + rowNumber + " rows 행" + colNumber + " 열 " +value + " type: " + cell.getCellType());
							}

						}
					}

				}
			}

		} catch (

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

}