POI 라이브러리 사용 Excel Loader 구현

Programming/Java 2017.02.28 22:18 Posted by 파란크리스마스

ExcelUtils

package com.intel4.poi;

import java.io.IOException;
import java.io.InputStream;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtils {
	
	// 엑셀로드
	public static Workbook loalDocument(String filename,InputStream is) throws IOException {
		if (filename.endsWith(".xlsx")) {
			return new XSSFWorkbook(is);
		}
		else   if (filename.endsWith("xls")) {
			return new HSSFWorkbook(is);
		}
		else {
			throw new IllegalArgumentException("Unsupported extension for resource [" + filename + "]");
		}
	}
	
	// 문자열 반환
	public static String getStringValue(Row row, int col) {
		
		if (row == null) return null;
		if (row.getCell(col) == null) return null;
		
		String value = "";
		if (row.getCell(col).getCellType()==Cell.CELL_TYPE_NUMERIC) {
			double doubleValue = row.getCell(col).getNumericCellValue();
			if ((doubleValue == Math.floor(doubleValue)) && !Double.isInfinite(doubleValue)) {
				value = String.valueOf((int)row.getCell(col).getNumericCellValue());
			} else {
				value = String.valueOf(row.getCell(col).getNumericCellValue());
			}
			//System.out.println("doubleValue = " + doubleValue + "/" + value);
		} else if (row.getCell(col).getCellType()==Cell.CELL_TYPE_STRING) {
			try {
				value = row.getCell(col).getStringCellValue();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return value;
	}
	
	// int 값 반환
	public static int getIntegerValue(Row row, int col) {
		
		if (row == null) return 0;
		if (row.getCell(col) == null) return 0;
		
		int value = 0;
		if (row.getCell(col).getCellType()==Cell.CELL_TYPE_NUMERIC) {
			double doubleValue = row.getCell(col).getNumericCellValue();
			if ((doubleValue == Math.floor(doubleValue)) && !Double.isInfinite(doubleValue)) {
				value = (int)row.getCell(col).getNumericCellValue();
			} else {
				value = (int)row.getCell(col).getNumericCellValue();
			}
			//System.out.println("doubleValue = " + doubleValue + "/" + value);
		} else if (row.getCell(col).getCellType()==Cell.CELL_TYPE_STRING) {
			try {
				String stringValue = row.getCell(col).getStringCellValue();
				if (stringValue==null || stringValue.trim().length()==0 || !isNumeric(stringValue)) return 0;
				value = Integer.parseInt(row.getCell(col).getStringCellValue());
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return value;
	}
	
	// 숫자여부 판단
	public static boolean isNumeric(String str) {
	  return str.matches("-?\\d+(\\.\\d+)?");  //match a number with optional '-' and decimal.
	}

}

ExcelLoader

package com.intel4.poi;

import java.io.File;
import java.io.FileInputStream;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class ExcelLoader {
	
	public static void main(String[] args) {
		ExcelLoader loader = new ExcelLoader();
		loader.load("WebContent\\sample.xlsx");
	}
	
	public void load(String filename) {
		int start_row = 1;
		
		FileInputStream inputStream = null;
		try {
			inputStream = new FileInputStream(new File(filename));
			Workbook workbook = ExcelUtils.loalDocument(filename, inputStream);
			
			// Excel 시트
			Sheet sheet = workbook.getSheetAt(0);
			
			// 해당 시트의 마지막 Row까지 반복
			for (int row_index=start_row; row_index<=sheet.getLastRowNum(); row_index++) {
				// Excel Row
				Row row = sheet.getRow(row_index);
				// row가 null인 경우 다음 row로...
				if (row == null) continue;
				
				int basic_seq = ExcelUtils.getIntegerValue(row, 0);
				int seq = ExcelUtils.getIntegerValue(row, 1);
				int problem = ExcelUtils.getIntegerValue(row, 2);
				String answer = ExcelUtils.getStringValue(row, 3);
				int hint  = ExcelUtils.getIntegerValue(row, 4);
				String subject1 = ExcelUtils.getStringValue(row, 5);
				String subject2 = ExcelUtils.getStringValue(row, 6);
				int level = ExcelUtils.getIntegerValue(row, 7);
				
				System.out.println(basic_seq + "/" + seq + "/" + problem +"/"+ answer + "/" + hint + "/" + subject1 + "/" + subject2 + "/" +  level);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (inputStream!=null) {
				try {
					inputStream.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}

	}

}
저작자 표시
신고


 

티스토리 툴바