엑셀 업로드 XlsxListParser

엑셀(.xlsx) 데이터 DB insert 작업

참고자료

업로드 파일

1. 첨부파일 저장

2. 엑셀파일 읽기

Serviceimlp.java

if(TEMP_FILE_LIST3.size() > 0) {
			String fileseq = TEMP_FILE_LIST3.getString(0);
			param.put("fileseq", fileseq);
			List<JSONObject> fileList = commonMapper.selectSysFileTempInfo(param);
			if(fileList.size() > 0) {
				JSONObject file = fileList.get(0);
				String filePath = file.getString("FULLFILENAME");
				XlsxListParser parser = new XlsxListParser(filePath, 0);
				JSONArray list = parser.getDataSet();
				if(list.size() > 0) {
					JSONObject fItem = list.getJSONObject(0);
					JSONObject item = new JSONObject();
					
					item.put("X1_TITLE", fItem.optString("F1"));
					item.put("Y1_TITLE", fItem.optString("F2"));
					item.put("Y2_TITLE", fItem.optString("F3"));
					item.put("Y3_TITLE", fItem.optString("F4"));
					item.put("EXAM_SEQ", param.getString("EXAM_SEQ"));
					item.put("ACC_NO", param.getString("ACC_NO"));
					// 시험데이터 마스터 등록
					examDataMapper.insertExamMstInfo(item);
					
					if(list.size() > 1) {
						for(int i=1; i<list.size(); i++) {
							JSONObject dtlItem = list.getJSONObject(i);
							if(fItem.optString("F1").equals("")) {
								break;
							}
							
							// 엑셀 데이터
							JSONObject dtlInput = new JSONObject();
							dtlInput.put("DATA_SEQ", item.optString("DATA_SEQ"));
							dtlInput.put("X1", dtlItem.optString("F1"));
							dtlInput.put("Y1", dtlItem.optString("F2"));
							dtlInput.put("Y2", dtlItem.optString("F3"));
							dtlInput.put("Y3", dtlItem.optString("F4"));
							
							// 시험데이터 상세 등록
							if(dtlInput.optString("X1").equals("")) {
								break;
							}
							examDataMapper.insertExamDtlInfo(dtlInput);
						}	
					}
					
				}
				
			}
		}

XlsxListParser.java

package com.dbvalley.premier.pdm.common;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
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.Workbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

public class XlsxListParser {
	
	private Workbook workbook;
	private XSSFSheet dataSheet;
	private int INDEX_START_ROW;
	private int INDEX_START_COL=0;
	
	public XlsxListParser(String filePath, int INDEX_START_ROW) {
		this.INDEX_START_ROW = INDEX_START_ROW;
		File xlsFile = new File(filePath);
		if (xlsFile.exists()) {
			FileInputStream fis = null;
			try {
				fis = new FileInputStream(xlsFile);
				workbook = new XSSFWorkbook(fis);
				fis.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} 
			dataSheet = (XSSFSheet) workbook.getSheetAt(0);
		}
	}
	
	public XlsxListParser(String filePath, int INDEX_START_ROW, int INDEX_START_COL, int sheetNo) {
		this.INDEX_START_ROW = INDEX_START_ROW;
		this.INDEX_START_COL = INDEX_START_COL;
		
		File xlsFile = new File(filePath);
		if (xlsFile.exists()) {
			FileInputStream fis = null;
			try {
				fis = new FileInputStream(xlsFile);
				workbook = new XSSFWorkbook(fis);
				fis.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} 
			dataSheet = (XSSFSheet) workbook.getSheetAt(sheetNo);
		}
	}
	
	public JSONArray getDataSet() {
		JSONArray result = new JSONArray();
    	for(int i=INDEX_START_ROW; i<=dataSheet.getLastRowNum(); i++) {
    		Row row = dataSheet.getRow(i);
    		JSONObject json = new JSONObject();
    		for(int j=INDEX_START_COL; j<row.getLastCellNum(); j++) {
    			Cell cell = row.getCell(j);
    			json.put("F"+(j+1), this.getValueString(cell).trim());
    		}
    		result.add(json);
    	}
    	return result;
    }
	
	private String getValueString(Cell cell) {
		String result = "";
		
		try {
			result = cell.getRichStringCellValue().toString();
			
		}catch(IllegalStateException e) {
			System.out.println("cellType : "+cell.getCellType());
			try {
				if(cell.getCellType()==CellType.FORMULA){
					result = String.valueOf(cell.getNumericCellValue());
					
					if (result.substring(result.length() -2, result.length()).equals(".0")) {
						result = result.substring(0, result.length() - 2);
					}
					
				}else if( DateUtil.isCellDateFormatted(cell)) {
					Date date = cell.getDateCellValue();
					result = new SimpleDateFormat("yyyy-MM-dd").format(date);
					
				}else if(cell.getCellType()==CellType.NUMERIC) {
					DataFormatter fmt = new DataFormatter();
					result = fmt.formatCellValue(cell);
					
				}else {
					result = String.valueOf(cell.getNumericCellValue());
					
				}	
				
			}catch(IllegalStateException e1) {
				System.out.println(e1.getLocalizedMessage());
			}
			
			
			
			
		}catch(NullPointerException e1) {
			result = "";
		}
		if(result.equals("-")) result = "";
		return result;
	}
	

}

3. 엑셀데이터 orcle DB table INSERT

DATA_MST

DATA_DTL