[Spring] 개발환경 구축 - 대용량 Excel 다운로드

SimpleSpring4.zip

0.08MB

Spring 개발환경을 갑자기 구축하려고 하다보니, 막막합니다.

하나 하나 구축해 보겠습니다

---

개발환경

Spring 4.3.30.RELEASE

MAVEN 3.8.4

Logback 1.2.9

commons-fileupload 1.4

commons-io 2.6

commons-dbcp 1.4

postgresql driver 42.5.0

poi 4.1.2

---

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>SimpleSpring4</groupId>
    <artifactId>SimpleSpring4</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>war</packaging>
    <name>SimpleSpring4</name>
    <description>Simple Spring4</description>

    <properties>
        <springframework.version>4.3.30.RELEASE</springframework.version>

        <org.slf4j-version>1.7.30</org.slf4j-version><!-- 1.7.26 -->
        <ch.qos.logback-version>1.2.9</ch.qos.logback-version>
    </properties>

    <dependencies>
        <!-- Spring -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>${springframework.version}</version>
            <exclusions>
                <!-- Exclude Commons Logging in favor of SLF4j -->
                <exclusion>
                    <groupId>commons-logging</groupId>
                    <artifactId>commons-logging</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-webmvc</artifactId>
            <version>${springframework.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>${springframework.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>${springframework.version}</version>
        </dependency>
        
        <!-- slf4j -->
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>${org.slf4j-version}</version>
        </dependency>

        <!-- Bridge -->
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>log4j-over-slf4j</artifactId>
            <version>${org.slf4j-version}</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>jul-to-slf4j</artifactId>
            <version>${org.slf4j-version}</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>jcl-over-slf4j</artifactId>
            <version>1.7.25</version>
        </dependency>

        <!-- logback -->
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-classic</artifactId>
            <version>${ch.qos.logback-version}</version>
            <exclusions>
                <exclusion>
                    <groupId>org.slf4j</groupId>
                    <artifactId>slf4j-api</artifactId><!-- logback을 slf4j-api에서 사용함을 의미 -->
                </exclusion>
            </exclusions>
            <scope>runtime</scope><!-- runtime 시 사용함 -->
        </dependency>

        <!-- https://mvnrepository.com/artifact/javax.annotation/javax.annotation-api -->
        <dependency>
            <groupId>javax.annotation</groupId>
            <artifactId>javax.annotation-api</artifactId>
            <version>1.3</version>
        </dependency>
        <!-- For user input validation
        <dependency>
            <groupId>javax.validation</groupId>
            <artifactId>validation-api</artifactId>
            <version>1.1.0.Final</version>
        </dependency>
 -->
        <!-- commons-xxxx -->
        <dependency>
            <groupId>commons-fileupload</groupId>
            <artifactId>commons-fileupload</artifactId>
            <version>1.4</version>
        </dependency>
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.6</version>
        </dependency>
        <dependency>
            <groupId>commons-dbcp</groupId>
            <artifactId>commons-dbcp</artifactId>
            <version>1.4</version>
        </dependency>
        
        <!-- Servlet -->
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>javax.servlet-api</artifactId>
            <version>3.1.0</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>jstl</artifactId>
            <version>1.2</version>
        </dependency>
        
		<!-- https://mvnrepository.com/artifact/org.postgresql/postgresql -->
		<dependency>
		    <groupId>org.postgresql</groupId>
		    <artifactId>postgresql</artifactId>
		    <version>42.5.0</version>
		</dependency>
        
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
        	<groupId>org.apache.poi</groupId>
        	<artifactId>poi</artifactId>
        	<version>4.1.2</version>
        </dependency>
        
        <dependency>
        	<groupId>org.apache.poi</groupId>
        	<artifactId>poi-ooxml</artifactId>
        	<version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.1.2</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.1</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
            <plugin>
                <artifactId>maven-war-plugin</artifactId>
                <version>3.2.3</version>
            </plugin>
        </plugins>
    </build>
</project>

DispatcherServlet.xml

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

    <mvc:annotation-driven />

    <!-- 빈 설정을 어노테이션 기반으로 하겠다는 설정태그 -->
    <context:component-scan base-package="simple">
        <context:include-filter type="annotation" expression="org.springframework.stereotype.Controller" />
    </context:component-scan>

    <!-- CSS, JS, 이미지 등의 자원은 거의 변하지 않기 때문에, 웹 브라우저에 캐시를 하면
         네트워크 사용량, 서버 사용량, 웹 브라우저의 반응 속도 등을 개선할 수 있다. -->
    <mvc:resources mapping="/static/**" location="/static/" />
    <mvc:resources location="/resources/**" mapping="/resources/"></mvc:resources>
    
    <!--
     Mulitipart Resolver
     maxUploadSize는 최대 업로드 가능한 파일의 바이트 크기
     maxInMemorySize는 디스크에 임시 파일을 생성하기 전 메모리에 보관할 수 있는 최대 바이트 크기
     -->
    <bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
        <property name="defaultEncoding" value="UTF-8" />
        <property name="maxUploadSize" value="104857600" /> <!-- 10M -->
        <property name="maxInMemorySize" value="1048576" /> <!-- 1M -->
    </bean>

    <bean id="uploadPath" class="java.lang.String">
        <constructor-arg value="C:/project/upload/"></constructor-arg>
    </bean>
    
    
    <bean id="messageSource" class="org.springframework.context.support.ReloadableResourceBundleMessageSource">
        <!-- Encoding 설정 -->
        <property name="defaultEncoding" value="UTF-8"/> 
        <!-- Reload Cache 설정 -->
        <property name="cacheSeconds" value="5"/>
        <!-- basenames 설정: 아래처럼 하면 WEB-INF 밑의 message 폴더 아래의 labels로 시작하는 모든 Property-->
        <property name="basenames">
            <list>
                <value>/WEB-INF/messages/message</value>
                <value>classpath:/messages/message</value>
            </list>
        </property>       
    </bean>
     
    <!-- MessageSource를 사용하기 위한 Accessor 설정 -->
    <bean id="messageSourceAccessor" class="org.springframework.context.support.MessageSourceAccessor">
        <constructor-arg ref="messageSource"/>
    </bean>

    <!-- Default Location 설정 -->
    <bean id="localeResolver" class="org.springframework.web.servlet.i18n.SessionLocaleResolver">
        <property name="defaultLocale" value="ko"></property>
    </bean>
    
    <!--
     mvc:default-servlet-handler
     요청 경로와 일치하는 컨트롤러를 찾는다.
     컨트롤러가 존재하지 않으면, 디폴트 서블릿 핸들러에 전달한다.
     DispatcherSerlvet이 처리하지 못한 요청을 DefaultSerlvet에게 넘겨주는 역할을 하는 핸들러
     *.css와 같은 컨트롤러에 매핑되어 있지 않은 URL 요청은 최종적으로 Default Servlet에 전달되어 처리하는 역할
     
     
     web.xml 파일의 dispatcher 가 / 의 경우 설정한다.
     <servlet-mapping>
        <servlet-name>dispatcher</servlet-name>
        <url-pattern>/</url-pattern>
    </servlet-mapping>
    -->
    <mvc:default-servlet-handler />

    <!-- beanNameResolver는 우선순위를 0으로 해서 ViewPage를 찾을 때 우선 탐색한다. 
    만약 BeanNameViewResolver에서 없으면 UrlBasedViewResolve에서 탐색한다. -->
    <bean id="beanNameResolver" class="org.springframework.web.servlet.view.BeanNameViewResolver">
        <property name="order" value="0"/>
    </bean>

    <bean id="viewResolver" class="org.springframework.web.servlet.view.UrlBasedViewResolver">
        <property name="viewClass" value="org.springframework.web.servlet.view.JstlView" />
        <property name="prefix" value="/WEB-INF/view/" />
        <property name="suffix" value=".jsp" />
        <property name="order" value="1" /> <!-- 순서를 지정하는 property -->
    </bean>

    <!-- 파일다운로드 -->
    <bean id="fileDownload" class="simple.spring.view.FileDownloadView" />
    
    <!-- 엑셀다운로드 -->
    <bean id="excelDownload" class="simple.spring.view.ExcelDownView" />

</beans>

ExcelDownload.java

package simple.spring.view;

import java.net.URLEncoder;
import java.util.List;
import java.util.Map;

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

import org.apache.poi.openxml4j.util.ZipSecureFile;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils;
import org.springframework.web.servlet.view.AbstractView;

public class ExcelDownView extends AbstractView {

    private final Logger logger;

    private SXSSFWorkbook workbook = null;

    public ExcelDownView() {
        logger = LoggerFactory.getLogger(this.getClass());
    }

    @Override
    protected void renderMergedOutputModel( Map<String, Object> model, HttpServletRequest request,
            HttpServletResponse response ) throws Exception {

        logger.debug("model :: {}", model);

        String excelName = (String) model.get("excelName");

        String userAgent = request.getHeader("User-Agent");
        if ( userAgent.contains("Trident") || (userAgent.indexOf("MSIE") > -1) ) {
            excelName = URLEncoder.encode(excelName, "UTF-8").replaceAll("\\+", "%20");
        } else if ( userAgent.contains("Chrome") || userAgent.contains("Opera") || userAgent.contains("Firefox") ) {
            excelName = new String(excelName.getBytes("UTF-8"), "ISO-8859-1");
        }

        String excelTitle = (String) model.get("excelTitle");

        model.put("excelName", excelName);
        model.put("title", excelTitle);

        if ( StringUtils.isEmpty(excelName) ) {
            return;
        }

        // .xlsx 파일은 XML 파일이 포함된 압축 파일이기 때문에 POI에서 "Zip Bomb" 관련한 취약점이 발생할 가능성이 있다고 한다.
        // 최소 압축비율을 지정한다.
        ZipSecureFile.setMinInflateRatio(0);

        SXSSFWorkbook wb = generateUserExcel(model);

        response.setContentType("application/msexcel;charset=UTF-8");
        response.setHeader("Content-Disposition",
                "attachment; filename=" + URLEncoder.encode(excelName, "UTF-8").replaceAll("\\+", " ") + ";");

        ServletOutputStream os = response.getOutputStream();
        wb.write(os);
        wb.close();
        wb.dispose();
    }

    public SXSSFWorkbook generateUserExcel( Map<String, Object> model ) throws Exception {
        String excelName = (String) model.get("excelName");

        workbook = new SXSSFWorkbook();
        workbook.setCompressTempFiles(true);

        SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet(excelName);
        sheet.setRandomAccessWindowSize(3000);    // 메모리 행 3000개로 제한, 초과 시 Disk로 Flush

        // 1. Title 생성
        setTitle(sheet, model);

        sheet.createRow(sheet.getLastRowNum() + 1);         // Last Row

        // 2. 헤더 생성
        setHeader(sheet, model);

        // 3. Data 생성
        setData(sheet, model);

        return workbook;
    }

    /**
     * <pre>
     * Title Style
     * </pre>
     * 
     * @param sheet
     * @param model
     */
    private void setTitle( SXSSFSheet sheet, Map<String, Object> model ) {
        String   excelTitle = (String) model.get("excelTitle");
        String[] headerName = (String[]) model.get("headerName");       // excel 컬럼명

        SXSSFRow  row  = sheet.createRow(0);
        SXSSFCell cell = null;

        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle = getFontStyle(cellStyle, 20, true);
        cellStyle = getCellStyle(cellStyle, "center");

        for ( int i = 0; i < headerName.length; i++ ) {
            cell = row.createCell(i);
            cell.setCellStyle(cellStyle);
        }

        row.getCell(0).setCellValue(new XSSFRichTextString(excelTitle));
        // 셀병합
        // sheet.addMergedRegion(new CellRangeAddress(첫행, 마지막행, 첫열, 마지막열));
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headerName.length - 1));
    }

    /**
     * <pre>
     * Header Style
     * </pre>
     * 
     * @param sheet
     * @param model
     */
    private void setHeader( SXSSFSheet sheet, Map<String, Object> model ) {

        SXSSFRow row = sheet.createRow(sheet.getLastRowNum() + 1);         // Last Row

        String[] headerName = (String[]) model.get("headerName");       // excel 컬럼명

        SXSSFCell cell = null;

        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle = getFontStyle(cellStyle, 13, true);
        cellStyle = getCellStyle(cellStyle, "center");
        cellStyle = getColorStyle(cellStyle, "grey25");

        for ( int i = 0; i < headerName.length; i++ ) {
            cell = row.createCell(i);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(new XSSFRichTextString(headerName[i]));

            sheet.setColumnWidth(i, 4000);
        }
    }

    /**
     * <pre>
     * Data Style
     * </pre>
     * 
     * @param sheet
     * @param model
     */
    @SuppressWarnings("unchecked")
    private void setData( SXSSFSheet sheet, Map<String, Object> model ) {

        CreationHelper helper      = workbook.getCreationHelper();
        CellStyle      numberStyle = getNumberStyle(helper);
        CellStyle      intStyle    = getIntStyle(helper);
        CellStyle      dateStyle   = getDateStyle(helper);
        CellStyle      stringStyle = getStringStyle();

        String[] headerKey = (String[]) model.get("headerKey");;                               // table field 명
        List<Map<String, Object>> data = (List<Map<String, Object>>) model.get("data");;       // Data

        SXSSFCell cell = null;
        SXSSFRow  row  = null;

        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle = getFontStyle(cellStyle, 10, true);
        cellStyle = getCellStyle(cellStyle, "center");

        if ( data != null ) {
            //
            for ( int j = 0; j < data.size(); j++ ) {
                Map<String, Object> rowData = data.get(j);

                row = sheet.createRow(sheet.getLastRowNum() + 1);         // Last Row

                for ( int i = 0; i < headerKey.length; i++ ) {

                    if ( headerKey[i].equals("NUM") ) {
                        cell = row.createCell(i);
                        cell.setCellStyle(intStyle);
                        cell.setCellValue(new XSSFRichTextString((String) rowData.get(headerKey[i])));
                    } else if ( headerKey[i].equals("FLT") ) {
                        cell = row.createCell(i);
                        cell.setCellStyle(numberStyle);
                        cell.setCellValue(new XSSFRichTextString((String) rowData.get(headerKey[i])));
                    } else if ( headerKey[i].equals("STR") ) {
                        cell = row.createCell(i);
                        cell.setCellStyle(stringStyle);
                        cell.setCellValue(new XSSFRichTextString((String) rowData.get(headerKey[i])));
                    } else if ( headerKey[i].equals("DT") ) {
                        cell = row.createCell(i);
                        cell.setCellStyle(dateStyle);
                        cell.setCellValue(new XSSFRichTextString((String) rowData.get(headerKey[i])));
                    } else if ( headerKey[i].equals("NUL") ) {
                        cell = row.createCell(i);
                        cell.setCellStyle(cellStyle);
                        cell.setCellValue(new XSSFRichTextString(""));
                    }
                }
            }
        }
    }

    /**
     * <pre>
     * Font Style
     * </pre>
     * 
     * @param Sheet
     */
    public CellStyle getFontStyle( CellStyle cellStyle, int fontSize, boolean boldYn ) {
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) fontSize);
        font.setBold(boldYn);
        cellStyle.setFont(font);

        return cellStyle;
    }

    /**
     * <pre>
     * Color Style
     * </pre>
     * 
     * @param Sheet
     */
    public CellStyle getColorStyle( CellStyle cellStyle, String color ) {
        if ( color.equals("grey25") ) {
            cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        } else if ( color.equals("yellow") ) {
            cellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
        }

        cellStyle.setFillPattern(FillPatternType.FINE_DOTS);

        return cellStyle;
    }

    /**
     * <pre>
     * Font Style
     * </pre>
     * 
     * @param Sheet
     */
    private CellStyle getCellStyle( CellStyle cellStyle, String align ) {
        if ( align.equals("center") ) {
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
        } else if ( align.equals("left") ) {
            cellStyle.setAlignment(HorizontalAlignment.LEFT);
        } else if ( align.equals("right") ) {
            cellStyle.setAlignment(HorizontalAlignment.RIGHT);
        } else {
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
        }

        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);

        return cellStyle;
    }

    /**
     * <pre>
     * Cell Style - 문자열 스타일
     * </pre>
     * 
     * @return
     */
    private CellStyle getStringStyle() {
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.LEFT);

        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);

        return cellStyle;
    }

    /**
     * <pre>
     * Cell Style - 숫자 스타일
     * </pre>
     * 
     * @return
     */
    private CellStyle getIntStyle( CreationHelper helper ) {
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setDataFormat(helper.createDataFormat().getFormat("#,##0"));
        cellStyle.setAlignment(HorizontalAlignment.RIGHT);

        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);

        return cellStyle;
    }

    /**
     * <pre>
     * Cell Style - 숫자 스타일
     * </pre>
     * 
     * @return
     */
    private CellStyle getNumberStyle( CreationHelper helper ) {
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setDataFormat(helper.createDataFormat().getFormat("#,##0.0#"));
        cellStyle.setAlignment(HorizontalAlignment.RIGHT);

        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);

        return cellStyle;
    }

    /**
     * <pre>
     * Cell Style - 날짜 스타일
     * </pre>
     * 
     * @return
     */
    private CellStyle getDateStyle( CreationHelper helper ) {
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setDataFormat(helper.createDataFormat().getFormat("yyyy-mm-dd"));
        cellStyle.setAlignment(HorizontalAlignment.CENTER);

        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);

        return cellStyle;
    }
}

ExcelDownloadController.java

package simple.spring.file;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

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

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;

@Controller
public class ExcelDownloadController {

    private static final Logger logger = LoggerFactory.getLogger(ExcelDownloadController.class);

    @RequestMapping(value = "/excelDownload")
    public String excelDownload( HttpServletRequest request, HttpServletResponse response, ModelMap model )
            throws Exception {

        logger.debug("[ExcelDownloadController.excelDownload] 다운로드 시작 .....................................");

        String   excelName  = "ExcelDownloadFile.xlsx";
        String   excelTitle = "Excel 다운로드 테스트";
        String[] headerName = { "정수", "숫자", "문자", "날짜", "공백" };       // excel 컬럼명
        String[] headerKey  = { "NUM", "FLT", "STR", "DT", "NUL" };            // table field 명

        // 조회 데이터
        List<Map<String, Object>> data = new ArrayList<>();
        Map<String, Object>       map  = null;

        map = new HashMap<>();
        map.put("NUM", "1");
        map.put("FLT", "1.1");
        map.put("STR", "문자열1");
        map.put("DT", "2023-01-01");
        map.put("NUL", "");

        data.add(map);

        map = new HashMap<>();
        map.put("NUM", "2");
        map.put("FLT", "2.2");
        map.put("STR", "문자열2");
        map.put("DT", "2023-01-02");
        map.put("NUL", null);

        data.add(map);

        map = new HashMap<>();
        map.put("NUM", "3");
        map.put("FLT", "3.3");
        map.put("STR", "문자열3");
        map.put("DT", "2023-01-03");
        map.put("NUL", " ");

        data.add(map);

        model.put("excelName", excelName);
        model.put("excelTitle", excelTitle);
        model.put("headerName", headerName);
        model.put("headerKey", headerKey);
        model.put("data", data);

        return "excelDownload";
    }
}

---

---

https://hermeslog.tistory.com/653

[Spring] 개발환경 구축 - DBCP Connection Test