jsp model 1 excel(xls, xlsx) file download

 

 

참고 : 개인적인 참고용으로 작성된 글 이며, 관련 부분에 대한 지식이 짧아 자세한 내용은 담고 있지 않습니다.

 

 

 

 

jsp model 1 에서 xls 나 xlsx 파일을 생성 하기 위해서는 다음과 같은 jar library 가 필요 하다.

 

commons-collections4-4.3.jar

0.72MB

commons-compress-1.20.jar

0.60MB

poi-4.1.2.jar

2.78MB

poi-ooxml-4.1.2.jar

1.80MB

poi-ooxml-schemas-4.1.2.jar

7.55MB

xmlbeans-3.1.0.jar

2.46MB

 

첨부된 파일을 다운로드 받거나, 첨부된 파일이 찝찝한 경우 https://mvnrepository.com/ 에서 버전을 맞춰 다운로드 받으면 된다.

 

 

1. mysql 에서 얻어진 결과를 xls 파일을 생성 하는 jsp 코드

<%@ page contentType="text/html;charset=UTF-8" language="java"  pageEncoding="UTF-8" %>
<%@ page import="kr.pe.freecatz.jsp.MyUtil" %>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFWorkbook" %>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFSheet" %>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFRow" %>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFCell" %>
<%@ page import="java.io.OutputStream" %>
<%@ page import="java.sql.*" %>
<%@ page import="java.net.URLEncoder" %>
<%
    request.setCharacterEncoding("UTF-8");
    response.setHeader("Cache-Control", "no-cache");
    response.setHeader("Pragma", "no-cache");
    response.setDateHeader("Expires", 0);

    Connection conn = MyUtil.getMySQLConnection();
    PreparedStatement pstmt = null;
    ResultSet rs;

    try{

        StringBuilder sql = new StringBuilder();
        int mxf = 30;
        for(int i=0 ; i <= mxf; i++){
            sql.append("SELECT SUBSTR(UUID(), 1, 8) AS UID \n");
            sql.append("     , FLOOR(1 + (RAND() * " + (i + 1) + ")) AS RID \n");
            sql.append(" UNION ALL \n");
        }
        sql.append("SELECT @@hostname AS UID,  @@version AS RID UNION ALL \n");
        sql.append("SELECT ? AS UID,  ? AS RID UNION ALL \n");
        sql.append("SELECT '안녕' AS UID,  '하세요?' AS RID  \n");
        sql.append("ORDER BY RAND(NOW()) \n");

        pstmt = conn.prepareStatement(String.valueOf(sql), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
        pstmt.setString(1, "Hello");
        pstmt.setString(2, "World");

        rs = pstmt.executeQuery();

        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet worksheet = workbook.createSheet("테스트시트");
        HSSFRow row = worksheet.createRow((short)0);
        HSSFCell cell = null;

        // 첫번째 행의 셀 값
        row.createCell(0).setCellValue("RID");
        row.createCell(1).setCellValue("UID");

        worksheet.createFreezePane(0, 1);	// 행고정

        if(rs.next()){
            int i = 1;
            while(rs.next()){
                 System.out.println(i + "\t" + rs.getString("RID") + "\t" + rs.getString("UID"));

                row = worksheet.createRow((short)i);

                cell = row.createCell(0);
                cell.setCellValue(rs.getString("RID") );

                cell = row.createCell(1);
                cell.setCellValue(rs.getString("UID") );

                i++;
            }
        }

        out.clear();
        out = pageContext.pushBody();
        OutputStream outs = response.getOutputStream();
        outs.write(workbook.getBytes());

        response.setContentType("Application/vnd.ms-excel");
        response.setHeader("Content-Transfer-Encoding", "binary");
        response.setHeader("Content-Type", "application/octet-stream");
        response.setHeader("Content-Disposition", "Attachment;Filename=" + URLEncoder.encode("TEST", "UTF-8") + ".xls");

    } catch(Exception e) {
        System.out.print(e.getMessage());
    } finally{
        try { pstmt.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); }
    }

%>

 

 

2. mysql 에서 얻어진 결과를 xlsx 파일을 생성 하는 jsp 코드

<%@ page contentType="text/html;charset=UTF-8" language="java"  pageEncoding="UTF-8" %>
<%@ page import="kr.pe.freecatz.jsp.MyUtil" %>
<%@ page import="java.io.OutputStream" %>
<%@ page import="java.sql.*" %>
<%@ page import="java.net.URLEncoder" %>
<%@ page import="org.apache.poi.xssf.usermodel.XSSFWorkbook" %>
<%@ page import="org.apache.poi.xssf.usermodel.XSSFSheet" %>
<%@ page import="org.apache.poi.xssf.usermodel.XSSFRow" %>
<%@ page import="org.apache.poi.xssf.usermodel.XSSFCell" %>
<%@ page import="java.io.ByteArrayOutputStream" %>
<%
    request.setCharacterEncoding("UTF-8");
    response.setHeader("Cache-Control", "no-cache");
    response.setHeader("Pragma", "no-cache");
    response.setDateHeader("Expires", 0);

    Connection conn = MyUtil.getMySQLConnection();
    PreparedStatement pstmt = null;
    ResultSet rs;

    try{

        StringBuilder sql = new StringBuilder();
        int mxf = 30;
        for(int i=0 ; i <= mxf; i++){
            sql.append("SELECT SUBSTR(UUID(), 1, 8) AS UID \n");
            sql.append("     , FLOOR(1 + (RAND() * " + (i + 1) + ")) AS RID \n");
            sql.append(" UNION ALL \n");
        }
        sql.append("SELECT @@hostname AS UID,  @@version AS RID UNION ALL \n");
        sql.append("SELECT ? AS UID,  ? AS RID UNION ALL \n");
        sql.append("SELECT '안녕' AS UID,  '하세요?' AS RID  \n");
        sql.append("ORDER BY RAND(NOW()) \n");

        pstmt = conn.prepareStatement(String.valueOf(sql), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
        pstmt.setString(1, "Hello");
        pstmt.setString(2, "World");

        rs = pstmt.executeQuery();

        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet worksheet = workbook.createSheet("테스트시트");
        XSSFRow row = worksheet.createRow((short)0);
        XSSFCell cell = null;

        // 첫번째 행의 셀 값
        row.createCell(0).setCellValue("RID");
        row.createCell(1).setCellValue("UID");

        worksheet.createFreezePane(0, 1);	// 행고정

        if(rs.next()){
            int i = 1;
            while(rs.next()){
                System.out.println(i + "\t" + rs.getString("RID") + "\t" + rs.getString("UID"));

                row = worksheet.createRow((short)i);

                cell = row.createCell(0);
                cell.setCellValue(rs.getString("RID") );

                cell = row.createCell(1);
                cell.setCellValue(rs.getString("UID") );

                i++;
            }
        }

        out.clear();
        out = pageContext.pushBody();
        OutputStream outs = response.getOutputStream();

        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        try {
            workbook.write(bos);
        } finally {
            bos.close();
        }
        byte[] bytes = bos.toByteArray();

        outs.write(bytes);

        response.setContentType("Application/vnd.ms-excel");
        response.setHeader("Content-Transfer-Encoding", "binary");
        response.setHeader("Content-Type", "application/octet-stream");
        response.setHeader("Content-Disposition", "Attachment;Filename=" + URLEncoder.encode("TEST", "UTF-8") + ".xlsx");

    } catch(Exception e) {
        System.out.print(e.getMessage());
    } finally{
        try { pstmt.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); }
    }

%>