[Spring] 스프링 엑셀파일 업로드 다운로드 기본(POI 라이브러리 1편) :: 마이자몽

엑셀파일 업로드 다운로드

어느 프로젝트를 진행하던간에 웹으로 데이터를 관리하는데 엑셀파일 기능이 빠질 수 없다.

DB데이터나 어떠한 로직에 의한 결과값을 엑셀파일 형태로 다운로드를 받거나 엑셀로 정리한 파일을 업로드해서 API에 해당 데이터를 던지거나  DB에 저장하는 작업은 꼭 필요하다.

엑셀만 잘하면 프로그램이 필요없다는 말이 있듯이, 웹상에서 원활하게 엑셀파일을 활용할 수 있도록 관리도구를 만들어주는 프로그램은 꼭 필요하다. 이전에 올린 파이썬으로 엑셀파일을 다로는 법(https://myjamong.tistory.com/51) 보다는 조금 더 복잡한 구조로 되어 있지만, 아직은 자바 스프링 프레임워크를 사용하는 공공 업체들이 많아 꼭 알아놔야한다고 생각한다.

스프링을 활용한 엑셀 파일 업로드 다운로드를 테마로 잡고 몇개의 글로 나눠서 기본부터 고급내용까지 설명 및 실습을 진행할 예정이다.

 

 

POI 라이브러리

스프링 프레임워크에서 엑셀 파일을 다루기 위해 사용되는 라이브러리가 바로 POI이다. POI 라이브러리 없이도 단순히 텍스트만 입력되어 있는 엑셀파일을 불러올려면 HTML 형태를 xlsx 확장자로 받아버리면 해결되지만, POI 라이브러리를 사용하면 셀의 스타일, 셀의 너비 등을 로직 내에서 구현이 가능하고, 수식 및 엑셀의 여러 기능을 같이 사용할 수 있다.

 

maven pom.xml

poi 라이브러리와 함께 이번 실습을 진행하기 위해

json형태로 업로드한 엑셀 데이터를 뿌려주기 위해 jackson 라이브러리 그리고

파일을 업로드 해야하니까 MultipartFile로 request를 받아주기 위해 commons-fileupload 라이브러리를 maven을 통해 받아준다.

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.0.0</version>
</dependency>    

<!-- https://mvnrepository.com/artifact/com.fasterxml.jackson.core/jackson-core -->
<dependency>
    <groupId>com.fasterxml.jackson.core</groupId>
    <artifactId>jackson-core</artifactId>
    <version>2.9.0.pr4</version>
</dependency>
    
<!-- https://mvnrepository.com/artifact/com.fasterxml.jackson.core/jackson-databind -->
<dependency>
    <groupId>com.fasterxml.jackson.core</groupId>
    <artifactId>jackson-databind</artifactId>
    <version>2.7.9.1</version>
</dependency>  

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

 

 

 

 

 

servlet-context

컨트롤러에서 view로 바로 매핑되지 않고 해당 객체로 바로 매핑해주기 위해 servlet-context에서 빈즈를 등록해줘야한다.

여기서 주요한거는 BeanNameViewResolver 빈즈의 순서가 InternalResourceViewResolver보다 우선이어야한다.

InternalResourceViewResolver가 우선순위를 갖으면 우리가 원하는 json 형태의 반환이나, 엑셀다운로드를 진행하지 않고 해당 매핑이름에 .jsp를 달고 view를 찾게된다(ex : excelDownloadView.jsp를 찾느게 아니라  ExcelDownloadView 클래스파일의 실행이 필요한것이다.)

<?xml version="1.0" encoding="UTF-8"?>
<beans:beans xmlns="http://www.springframework.org/schema/mvc"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:beans="http://www.springframework.org/schema/beans"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc.xsd
        http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">

    <annotation-driven />

    <resources mapping="/resources/**" location="/resources/" />

    <beans:bean id="beanNameViewResolver" class="org.springframework.web.servlet.view.BeanNameViewResolver">
        <beans:property name="order" value="0"/>
    </beans:bean>

    <beans:bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
        <beans:property name="prefix" value="/WEB-INF/views/" />
        <beans:property name="suffix" value=".jsp" />
        <beans:property name="order" value="1"></beans:property>
    </beans:bean>
    
    <beans:bean id="excelDownloadView" class="com.test.excel.util.ExcelDownloadView"/>
    
    <beans:bean id="jsonView" class="org.springframework.web.servlet.view.json.MappingJackson2JsonView"/>
    
    <beans:bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver"/>
    
    <context:component-scan base-package="com.*" />
    
</beans:beans>

 

 

 

엑셀 업로드 다운로드 프로젝트

해당 프로젝트의 자바 구조는 아래와같이 생성했다.

 

 

 

Fruit.java

과일객체가 담긴 리스트를 작성하여 엑셀파일로 다운로드하는 작업과

다운받은 엑셀 파일을 업로드하여 과일객체가 담긴 리스트에 담아 화면에 Json형태로 뿌려주는 작업까지 진행할거다.

package com.test.excel.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 long getPrice() {
        return price;
    }
    
    public int getQuantity() {
        return quantity;
    }
    
    public void setName(String name) {
        this.name = name;
    }
    
    public void setPrice(long price) {
        this.price = price;
    }
    
    public void setQuantity(int quantity) {
        this.quantity = quantity;
    }
}

 

 

 

 

 

ExcelDownloadView.java

엑셀 다운로드하는데 있어서 가장 중요한 클래스이다. 대부분의 프로젝트에서 해당 클래스는 코드 수정되는 부분은 위에 파일 이름 설정하는 부분 빼고는 거의 동일할것이다.

위 서블릿 설정에서 봤듯이, excelDownloadView로 컨트롤러에서 매핑시 해당 클래스로 바로 매핑해준다.

package com.test.excel.util;

import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Locale;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.web.servlet.view.AbstractView;

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", locale);
        SimpleDateFormat hourformat = new SimpleDateFormat("hhmmss", locale);
        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) {       // IE11
            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();
           
           // 파일생성
           workbook.write(os);
       }catch (Exception e) {
           e.printStackTrace();
       } finally {
           if(workbook != null) {
               try {
                   workbook.close();
               } catch (Exception e) {
                   e.printStackTrace();
               }
           }
           
           if(os != null) {
               try {
                   os.close();
               } catch (Exception e) {
                   e.printStackTrace();
               }
           }
       }
    }
}

 

 

 

 

스프링 엑셀 다운로드 업로드

view

자바로직에 앞서 출력되는 view다.

css 적용 없이 매우 간단하게 스크립트부분을 만들어줬다. 특이사항으로 파일을 다루는 폼 이므로 form의  enctype은 꼭 multipart/form-data로 해준다.

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<html>
<head>
    <title>Home</title>
    <script src="https://code.jquery.com/jquery-3.2.1.min.js"></script>
</head>
<body>
<script type="text/javascript">
    function doExcelUploadProcess(){
        var f = new FormData(document.getElementById('form1'));
        $.ajax({
            url: "uploadExcelFile",
            data: f,
            processData: false,
            contentType: false,
            type: "POST",
            success: function(data){
                console.log(data);
                document.getElementById('result').innerHTML = JSON.stringify(data);
            }
        })
    }
    
    function doExcelDownloadProcess(){
        var f = document.form1;
        f.action = "downloadExcelFile";
        f.submit();
    }
</script>
<form id="form1" name="form1" method="post" enctype="multipart/form-data">
    <input type="file" id="fileInput" name="fileInput">
    <button type="button" onclick="doExcelUploadProcess()">엑셀업로드 작업</button>
    <button type="button" onclick="doExcelDownloadProcess()">엑셀다운로드 작업</button>
</form>
<div id="result">
</div>
</body>
</html>

 

 

 

엑셀 다운로드

과일 객체를 몇개 만들어 리스트에 담고 해당 리스트의 내용을 엑셀파일로 다운받는 작업을 진행할것이다.

 

ExcelController 매핑

총 4개의 과일정보를 담은 객체를 리스트에 넣어 엑셀 워크북으로 가공 후 ExcelDownloadView 클래스로 매핑시킨다.

@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 = service.makeFruitList(names, prices, quantities);
        
        SXSSFWorkbook workbook = service.excelFileDownloadProcess(list);
        
        model.addAttribute("locale", Locale.KOREA);
        model.addAttribute("workbook", workbook);
        model.addAttribute("workbookName", "과일표");
        
        return "excelDownloadView";
    }

 

 

 

 ExcelService 엑셀 시트 생성 로직

엑셀 워크북을 만드는과정은 주석으로 설명이 되어있다.

전체 흐름을 보면 별로 어려운 부분은 없다. 시트를 생성하고, 행을 생성하고, 셀을 생성하고 셀안의 내용을 넣어주면 끝이다.

   /**
     * 엑셀파일로 만들 리스트 생성
     * @param names
     * @param prices
     * @param quantities
     * @return 엑셀파일 리스트
     */
    public ArrayList<Fruit> makeFruitList(String[] names, long[] prices, int[] quantities){
        ArrayList<Fruit> list = new ArrayList<Fruit>();
        for(int i=0; i< names.length; i++) {
            Fruit fruit = new Fruit(names[i], prices[i], quantities[i]);
            list.add(fruit);
        }
        return list;
    }
    
    /**
     * 과일 리스트를 간단한 엑셀 워크북 객체로 생성
     * @param list
     * @return 생성된 워크북
     */
    public SXSSFWorkbook makeSimpleFruitExcelWorkbook(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 headerCell = headerRow.createCell(0);
        headerCell.setCellValue("번호");
        // 해당 행의 두번째 열 셀 생성
        headerCell = headerRow.createCell(1);
        headerCell.setCellValue("과일이름");
        // 해당 행의 세번째 열 셀 생성
        headerCell = headerRow.createCell(2);
        headerCell.setCellValue("가격");
        // 해당 행의 네번째 열 셀 생성
        headerCell = headerRow.createCell(3);
        headerCell.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);
            // 데이터 번호 표시
            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;
    }
    
    /**
     * 생성한 엑셀 워크북을 컨트롤레에서 받게해줄 메소
     * @param list
     * @return
     */
    public SXSSFWorkbook excelFileDownloadProcess(List<Fruit> list) {
        return this.makeSimpleFruitExcelWorkbook(list);
    }

 

 

 

result

원하는 형식으로 출력이 된것을 확인할 수 있다.

 

 

 

엑셀 업로드

특정 틀에 맞는 엑셀파일을 업로드하여 과일객체에 담고 리스트로 만들어 화면에 최종적으로 뿌려주는 작업을 진행한다.

 

 

ExcelController 매핑

MultipartFile에 엑셀파일을 담아 받고 Json형태로 response 해주기 위해 jsonView 빈즈로 매핑한다.

    @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 = service.uploadExcelFile(file);
        
        model.addAttribute("list", list);
        return "jsonView";
    }

 

 

 

ExcelService 엑셀파일 업로드 후 과일 리스트로 반환 로직

엑셀파일을 읽어들이는 로직이다.

엑셀파일을 생성했을 때와 마찬가지로 시트를 불러오고 행과 셀을 불러와 작업을 진행한다.

불러올때는 각 행과 셀의 위치를 번호로 받아온다.

/**
     *업로드한 엑셀파일을 과일 리스트로 만들기
     * @param excelFile
     * @return 생성한 과일 리스트
     */
    public List<Fruit> uploadExcelFile(MultipartFile excelFile){
        List<Fruit> list = new ArrayList<Fruit>();
        try {
            OPCPackage opcPackage = OPCPackage.open(excelFile.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) fruit.setName(cell.getStringCellValue());
                // 행의 세번째 열 받아오기
                cell = row.getCell(2);
                if(null != cell) fruit.setPrice((long)cell.getNumericCellValue());
                // 행의 네번째 열 받아오기
                cell = row.getCell(3);
                if(null != cell) fruit.setQuantity((int)cell.getNumericCellValue());
                
                list.add(fruit);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

 

 

 

result

파일을 선택해서 다운로드받은 엑셀파일을 그대로 선택해주고, 엑셀업로드를 진행하여 화면에 Json형태로 출력됬다.

 

 

POI 라이브러리 1편에서는 간단하게 파일을 업로드하고 다운로드하는 기본적인 작업을 진행했다. 어려운 세팅 부분은 다 완료되었다. 이제는 해당 로직을 각각 사이트에 맞춰서 사용해주면 데이터 작업은 끝났다. 이제 다음 글들에서는 POI 라이브러리를 사용해서 엑셀 파일을 다운 받을때 스타일을 입히거나 수식을 사용하는 로직을 구현해볼 예정이다.