POI를 사용하여 Java 서블릿에서 Excel File을 Stream으로 전송하기

Java또는 JSP, 서블릿등에서 DB에 있는 데이타를 Excel File로 Client로 전송하는 방법 입니다.

 

물론 Server에서는 Excel File을 생성다음 File을 다시 Http Multipart로 전송하는 고전적인 방법을 쓰지 않습니다. 파일생성 없이 전송 합니다.

 

서블릿 CLASS

import! java.io.IOException;
import! java.io.PrintWriter;
import! javax.servlet.http.HttpServlet;
import! javax.servlet.http.HttpServletRequest;
import! javax.servlet.http.HttpServletResponse;

public class svExcel extends HttpServlet {
  public void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException
 {
	doPost(req, resp);
 }

 public void doPost(HttpServletRequest req, HttpServletResponse resp) throws IOException
 {
   String sFilename = "excel_20120423.xls";
   int    nFileSize = 0;
   String responseData = "";

/******************************************************
* EXCEL File Create  
******************************************************/ 
resp.setContentType("application/octet-stream");
resp.setHeader("Content-Disposition", "attachment; filename="+ sFilename +";");
  
   exExcel excel = new exExcel();
   excel.exec(sFilename, "테스트 쉬트");        
   excel.getExcelStream(resp.getOutputStream());
 } 
}

 

EXCEL 생성 CLASS

import! java.io.FileNotFoundException;
import! java.io.FileOutputStream;
import! java.io.OutputStream;
import! javax.servlet.ServletOutputStream;
import! org.apache.poi.hssf.usermodel.HSSFCell;
import! org.apache.poi.hssf.usermodel.HSSFCellStyle;
import! org.apache.poi.hssf.usermodel.HSSFFont;
import! org.apache.poi.hssf.usermodel.HSSFRow;
import! org.apache.poi.hssf.usermodel.HSSFSheet;
import! org.apache.poi.hssf.usermodel.HSSFWorkbook;
import! org.apache.poi.hssf.util.HSSFColor;

public class exExcel {

HSSFWorkbook  wb = null; /* Excel Work Book (excel file) */
HSSFSheet  sheet = null; /* Excel work Sheet */
HSSFFont   font  = null;    /* Excel Work Sheet Font Style */

public exExcel()
{
  HSSFWorkbook  wb = null; /* Excel Work Book (excel file) */
  HSSFSheet  sheet = null; /* Excel work Sheet */
  HSSFFont   font  = null;    /* Excel Work Sheet Font Style */
}


public int getContentsLength() { return wb.getBytes().length; }


public void getExcelStream(OutputStream sos)
{
 try
 { 
   wb.write(sos);
   sos.flush();
   sos.close(); 
 }
 catch(FileNotFoundException enfe)
 {
   enfe.printStackTrace();
 }
 catch(Exception e)
 {
   e.printStackTrace();
 }
}


public void exec(String sExcelFile, String sExcelSheet) 
{
  createSheet(sExcelFile,sExcelSheet); 
}


private void createWorkBook(String s)
{

  /*새로운 Excel 파일 및 Shhet 생성 */
  wb = new HSSFWorkbook();
  sheet = wb.createSheet(s);
  /* Cells Size Define */
  sheet.setColumnWidth(0, 1*3200);
  sheet.setColumnWidth(1, 1*3200);
  sheet.setColumnWidth(2, 1*3200);
  font = wb.createFont();
  font.setFontName("고딕체"); /* Font Name */
  font.setFontHeightInPoints((short)10);   /* Font Size */
  font.setColor(HSSFColor.BLACK.index);/* Font Color */                
}



private void createSheet(String sExcelFile, String sExcelSheet)
{
  createWorkBook(sExcelSheet);
  int nRow = 100;
  int nCol = 5;
  /* Row Loop */
  for(int i=0;i<nRow;i++) HSSFRow="" row="sheet.getRow(i);" Get="" Excel="" Sheet="" Row="" New="" Create="" Col="" Loop="" forint="" j="0;j<nCol;j++)" HSSFCell="" cell="row.createCell(j);" Cell="" HSSFCellStyle="" style="wb.createCellStyle();" Style="" Background="" Color="" Font="" Color="" style.setFillBackgroundColorHSSFColor.BLUE_GREY.index="" style.setFillForegroundColorHSSFColor.YELLOW.index="" style.setFillPatternHSSFCellStyle.SOLID_FOREGROUND="" style.setFontfont="" Box="" Line="" style.setBorderBottomHSSFCellStyle.BORDER_THIN="" style.setBottomBorderColorHSSFColor.BLACK.index="" style.setBorderLeftHSSFCellStyle.BORDER_THIN="" style.setLeftBorderColorHSSFColor.BLACK.index="" style.setBorderRightHSSFCellStyle.BORDER_THIN="" style.setRightBorderColorHSSFColor.BLACK.index="" style.setBorderTopHSSFCellStyle.BORDER_THIN="" style.setTopBorderColorHSSFColor.BLACK.index="" style.setAlignmentHSSFCellStyle.ALIGN_CENTER="" style.setVerticalAlignmentHSSFCellStyle.VERTICAL_CENTER="" cell.setCellTypeHSSFCell.CELL_TYPE_STRING="" Type="" String="" or="" Date="" Number="" Define="" cell.setCellStylestyle="" cell.setCellValueString.formati="%d,j=%d"," i="" j="" Sample="" Data="" pre="">

poi-3.8-20120326.jar

다운로드

 

</nRow;i++)>