DB 쿼리 결과값을 xls(excel) 파일로 저장해보자.

===== JSP =====
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.io.*" %>
<%@ page import="Test.dbtest" %>
<%
 request.setCharacterEncoding("UTF-8");
 response.setCharacterEncoding("UTF-8");
 
  String table = "table_name";
//table 명
 String columns = "idx, name, addr, tel, zipcode, id, pwd, date";
//컬럼명
 String where = "ORDER BY idx ASC LIMIT 0, 100";
//조건
 
 dbtest dbt = dbtest.getInstance();
 boolean result = dbt.XLS(table, columns, where);
//실제 파일을 생성해 줄 메소드 호출
 
 if(result == true){
  
  String path = "C:\\";
  String name = table + ".xls";
//경로 및 파일명 설정
 
  response.setContentType("application/x-msdownload");
//다운로드 컨트롤 실행
  response.setHeader("Content-Disposition", "attachment;filename=" + name + ";");
//헤더에 파일이름 세팅
  
  File file = new File(path + name);
 
  byte b[] = new byte[(int)file.length()];
 
  if(file.length() > 0 && file.isFile()){ // 0byte이상이고, 해당 파일이 존재할 경우
   BufferedInputStream fin = new BufferedInputStream(new FileInputStream(file)); // 인풋객체생성
   BufferedOutputStream outs = new BufferedOutputStream(response.getOutputStream()); // 응답객체생성
   int read = 0;
   
   try{
    out.clear();
    out = pageContext.pushBody();
 
    while((read = fin.read(b)) > 0){
     outs.write(b,0,read);
    }
   }catch(Exception e) {
    e.printStackTrace();
   }finally {
    if(outs!=null) try{outs.close();}catch(Exception e){}
    if(fin!=null) try{fin.close();}catch(Exception e){}
    new File(path + name).delete();
   }
  }else{
   System.out.println("File Not Found!!!");
  }
 }else{
  System.out.println("False");
 }
%>


===== Java =====

package Test;

import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import util.ConnectionDao;

public class dbtest {
 private static dbtest instance = new dbtest();
 public static dbtest getInstance(){
  return instance;
 }
 private dbtest(){}
 
 public boolean XLS(String table, String columns, String where) throws Exception{
  Connection conn = null;
  PreparedStatement pstmt = null;
  ResultSet rs = null;
  String sql = "";
  boolean result = true;
  String[] col = columns.split(",");
  System.out.println(table);
  System.out.println(columns);
  try{
   HSSFWorkbook wb = new HSSFWorkbook();
   HSSFSheet sheet = wb.createSheet(table);
   HSSFRow row = null;
   
   conn = ConnectionDao.getInstance().getConnection();
//미리 만들어 놓은 db connection

   if(where == null || where == "" || where.equals("")){
    sql = "SELECT " + columns + " FROM " + table;
   }else{
    sql = "SELECT " + columns + " FROM " + table + " WHERE " + where;
   }
   pstmt = conn.prepareStatement(sql);
   rs = pstmt.executeQuery();
   
   row = sheet.createRow(0);
   for(int i = 0; i < col.length; i++){
    row.createCell(i).setCellValue(col[i].toString());
   }
   
   while(rs.next()){
    row = sheet.createRow(rs.getRow());
    for(int i = 0; i < col.length; i++){
     row.createCell(i).setCellValue(col[i].toString());
    }
    for(int i = 0; i < col.length; i++){
     row.createCell(i).setCellValue(rs.getString(col[i].toString()));
    }
   }
   FileOutputStream fileOut = new FileOutputStream("C:/" + table + ".xls");
   wb.write(fileOut);
   fileOut.close();

  }catch(IOException ex){
   result = false;
   ex.printStackTrace();
  }catch(Exception ex){
   result = false;
   ex.printStackTrace();
  }finally{
   System.out.println("메소드 끝");
   if(rs != null) try{rs.close();}catch(SQLException ex){}
   if(pstmt != null) try{pstmt.close();}catch(SQLException ex){}
   if(conn != null) try{conn.close();}catch(SQLException ex){}
  }
  return result;
 }
}

※ 유용하게(?) 사용하시기 전에 클릭한번만 ㅠ