엑셀 파싱 기본.

필요의존, 핵심은 POI

<!-- to Excel Parsing -->
		<dependency>
		    <groupId>org.apache.poi</groupId>
		    <artifactId>poi-ooxml</artifactId>
		    <version>4.0.0</version>
		</dependency>  
	
		<!-- json, multipartFile -->
		<dependency>
		    <groupId>com.fasterxml.jackson.core</groupId>
		    <artifactId>jackson-databind</artifactId>
		    <version>2.9.4</version>
		</dependency>

		<dependency>
		    <groupId>commons-fileupload</groupId>
		    <artifactId>commons-fileupload</artifactId>
		    <version>1.3.2</version>
		</dependency>

 

---

저장하려는 엑셀의 로우와 상황에 따라 다르겠지만 VO 클래스를 사용했다.

public class Fruit {
	String name;
	long price;
	int quantity;

	public Fruit() {
	}

	public Fruit(String name, long price, int quantity) {
		this.name = name;
		this.price = price;
		this.quantity = quantity;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public long getPrice() {
		return price;
	}

	public void setPrice(long price) {
		this.price = price;
	}

	public int getQuantity() {
		return quantity;
	}

	public void setQuantity(int quantity) {
		this.quantity = quantity;
	}

	@Override
	public String toString() {
		return "Fruit [name=" + name + ", price=" + price + ", quantity=" + quantity + "]";
	}

}

---

하나의 엑셀 객체는 SXSSFWorkbook 이다.

@RequestMapping(value = "/downloadExcelFile", method = RequestMethod.POST)
    public String downloadExcelFile(Model model) {
        String[] names = {"자몽", "애플망고", "멜론", "오렌지"};
        long[] prices = {5000, 10000, 7000, 6000};
        int[] quantities = {50, 50, 40, 40};
        List<Fruit> list = excelService.makeFruitList(names, prices, quantities);
        
        //엑셀 객체
        SXSSFWorkbook workbook = excelService.excelFileDownloadProcess(list);
        
        System.out.println(workbook);
        
        model.addAttribute("locale", Locale.KOREA);
        model.addAttribute("workbook", workbook);//엑셀파일 저장
        model.addAttribute("workbookName", "과일표");//엑셀제목 저장
        
        return "excelDownloadView";
    }

---

SXSSFSheet 는 하나의 시트를 의미한다.

Row 는 시트상의 행을 의미하며 SXSSFSheet 로 만든다. 매개값으로 로우 번 수를 매긴다.

Cell 은 시트상의 Row 의 Cell을 의미하며 Row 로 만든다. 매개값으로 셀 번수를 매긴다.

/**
	 * 여기가 중요함, 엑셀 객체 생성 SXSSFWorkbook
	 * @param list 엑셀에 맵핑할 VO
	 * @return SXSSFWorkbook 엑셀 객체
	 */
	public SXSSFWorkbook makeSimpleExcelWorkBook(List<Fruit> list) {
		//엑셀 객체 생성
		SXSSFWorkbook workbook  =new SXSSFWorkbook();
		
		// 시트 생성
		SXSSFSheet sheet = workbook.createSheet("과일표");
		
		// 시트 열 너비
		sheet.setColumnWidth(0, 1500);
		sheet.setColumnWidth(0, 3000);
		sheet.setColumnWidth(0, 3000);
		sheet.setColumnWidth(0, 1500);
		
		// 첫번째 행 생성
		Row headerRow = sheet.createRow(0);
		//첫번째 열의 첫 셀 생성
		Cell headCell = headerRow.createCell(0);
		headCell.setCellValue("번호");
		//첫번째 열의 두번쨰 셀 생성
		headCell = headerRow.createCell(1);
		headCell.setCellValue("과일이름");
		//첫번째 열의 세번째 셀 생성
		headCell = headerRow.createCell(2);
		headCell.setCellValue("가격");
		//첫번째 열의 네번째 셀 생성
		headCell = headerRow.createCell(3);
		headCell.setCellValue("수량");
		
		// 과일 표의 내용 및 셀 생성
		Row bodyRow = null;
		Cell bodyCell = null;
		for (int i=0; i<list.size(); i++) {
			Fruit fruit = list.get(i);
			
			//행 생성
			bodyRow = sheet.createRow(i+1); //1부터 시작하므로
			//데이터 번호 표시
			bodyCell = bodyRow.createCell(0);
			bodyCell.setCellValue(i+1);
			//데이터 이름 표시
			bodyCell = bodyRow.createCell(1);
			bodyCell.setCellValue(fruit.getName());
			//데이터 가격 표시
			bodyCell = bodyRow.createCell(2);
			bodyCell.setCellValue(fruit.getPrice());
			//데이터 수량 표시
			bodyCell = bodyRow.createCell(3);
			bodyCell.setCellValue(fruit.getQuantity());
		}
		return workbook;
	}

---

엑셀을 파싱한 값을 뿌리는 커스텀 뷰, BeanNameViewResolver 가 0순위에 있는 상태다.

 

response 에서 OutputStream을 가지고 XSSFWorkbook << 엑셀객체 를 내보낸다.

// 커스텀 뷰 객체를 만들떄 사용
public class ExcelDownloadView extends AbstractView{

	@Override
	protected void renderMergedOutputModel(Map<String, Object> model, HttpServletRequest request,
			HttpServletResponse response) throws Exception {
		Locale locale = (Locale)model.get("locale");
		String workbookName = (String)model.get("workbookName");
		
		Date date = new Date();
		SimpleDateFormat dayFormat = new SimpleDateFormat("yyyyMMdd");
		SimpleDateFormat hourFormat = new SimpleDateFormat("hhmmss");
		
		String day = dayFormat.format(date);
		String hour = hourFormat.format(date);
		String fileName = workbookName + "_" + day + "_" + hour + ".xlsx";
		
		// 브라우저에 따른 파일 이름 인코딩
		
		String browser = request.getHeader("User-Agent");
		if(browser.indexOf("MSIE") > -1) {// 익스플로러 라면
			fileName = URLEncoder.encode(fileName,"UTF-8").replaceAll("\\", "%20");
		} else if (browser.indexOf("Trident") > -1) {// 엣지라면
			fileName = URLEncoder.encode(fileName,"UTF-8").replaceAll("\\", "%20");
		} else if (browser.indexOf("Firefox") > -1) {// 파이어 폭스라면
			fileName = "\""+ new String(fileName.getBytes("UTF-8"), "8859_1") + "\"";
		} else if (browser.indexOf("Opera") > -1) {// 파이어 폭스라면
			fileName = "\""+ new String(fileName.getBytes("UTF-8"), "8859_1") + "\"";
		} else if (browser.indexOf("Chrome") > -1) {// 크롬브라우저라면
			//스트링 버퍼는 스레드 세이프
			StringBuffer sb = new StringBuffer();
			for(int i=0; i< fileName.length(); i++) {
				char c = fileName.charAt(i);
				if(c > '~') {
					sb.append(URLEncoder.encode(""+c, "UTF-8"));
				} else {
					sb.append(c);
				}
			}
			fileName = sb.toString();
		} else if (browser.indexOf("Safari") > -1) {
			fileName = "\""+new String(fileName.getBytes("UTF-8"),"8859_1")+"\"";
		} else {
			fileName = "\""+new String(fileName.getBytes("UTF-8"),"8859_1")+"\"";
		}
		
		//응답정보 수정작업
		response.setContentType("application/download;charset=utf-8");
		response.setHeader("Content-Disposition", "attachment; fileName=\""+fileName+"\";");
		response.setHeader("Content-Transfer-Encoding", "binary");
		
		OutputStream os = null;
		SXSSFWorkbook workbook = null;
		try {
		workbook = (SXSSFWorkbook)model.get("workbook");
		os = response.getOutputStream();
		
		// 응답의 OutputStream 으로 SXSSWorkbook 을 내보냄
		workbook.write(os);
		}catch(Exception e) {
			e.getStackTrace();
		} finally {// 자원반납
			if(workbook != null) {
				try {
				workbook.close();
				}catch(Exception e) {
					e.printStackTrace();
				}
			}
			if(os != null) {
				try {
					os.close();
				}catch(Exception e) {
					e.printStackTrace();
				}
			}
		}
	}

}

---

업로드

하나의 엑셀 파일을 받아서 업로드를 진행하고 결과를 MappingJackson2JsonView 로 넘긴다.

@RequestMapping(value = "/uploadExcelFile", method = RequestMethod.POST)
	public String uploadExcelFile(MultipartHttpServletRequest request, Model model) {
		MultipartFile file = null;
		Iterator<String> iterator = request.getFileNames();
		if(iterator.hasNext()) {
			file = request.getFile(iterator.next());
		}
		//파일 이름을 가지고 업로드 진행
		List<Fruit> list = excelService.uploadExcelFile(file);
		
		list.forEach( item -> System.out.println(item));
		
		model.addAttribute("list", list);
		return "jsonView";
	}

---

OPCPackage 는 MultiPartFile 에서 IS 를 열어 가져온다.

OPCPackage 를 매개로 XSSFWorkbook 로 엑셀파일을 객체로 읽어들인다.

XSSFSheet 는 엑셀객체의 하나의 시트를 의미하며 XSSFWorkbook 으로 읽어들인다.

XSSFROW 는 읽어들인 하나의 행을 의미하며 XSSFSheet 으로 읽어들인다. header 를 제외하여 1부터 시작하고 있다. rownum 이 끝이므로 + 1 을 해야한다.

XSSFCELL 은 읽어들인 XSSFROW의 하나의 셀을 의미한다. XSSFROW 로 읽어들인다.

 

/**
	 * 업로드한 엑셀 파일을 클래스 리스트로 만든다.
	 * @param file 멀티파트 파일 객체
	 * @return 클래스 리스트
	 */
	public List<Fruit> uploadExcelFile(MultipartFile file){
		List<Fruit> list = new ArrayList<Fruit>();
		try {
			//OPCPackage 로 업로드한 파일을 읽어들임
			OPCPackage opcPackage = OPCPackage.open(file.getInputStream());
			XSSFWorkbook workbook = new XSSFWorkbook(opcPackage);
			
			// 첫번째 시트 불러오기
			XSSFSheet sheet = workbook.getSheetAt(0);
			
			for(int i=1; i<sheet.getLastRowNum() + 1; i++) {
				Fruit fruit = new Fruit();
				//첫번째 줄
				XSSFRow row = sheet.getRow(i);
				
				if(null == row) {
					continue;
				}
				
				// 해당 행의 두번쨰 열
				XSSFCell cell = row.getCell(1);
				if(null != cell && cell.getCellType() == CellType.STRING) {
					fruit.setName(cell.getStringCellValue());
				}
				//해당 행의 세번째 열
				cell = row.getCell(2);
				if(null != cell ) {
					fruit.setPrice((long)cell.getNumericCellValue());
				}
				//해당 행의 네번째 열
				cell = row.getCell(2);
				if(null != cell) {
					fruit.setQuantity((int)cell.getNumericCellValue());
				}
				list.add(fruit);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}
		return list;
	}

 

다음 작업은 데이터 베이스 공정이다.