Hongtting

import org.apache.poi.hssf.usermodel.HSSFCellStyle;

import org.apache.poi.hssf.util.HSSFColor;

import org.apache.poi.ss.usermodel.CellStyle;

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 java.io.OutputStream;

import java.text.SimpleDateFormat;

  @RequestMapping(value = “/test/test/excel.do”)

public void excel(HttpServletResponse response) throws Exception {

List<SomereportVO> list = statisticsService.selectExcelList();

SimpleDateFormat format1 = new SimpleDateFormat (“yyyyMMddHHmmssSSS”);

Date time = new Date();

String TODAYTXT = format1.format(time);

SXSSFWorkbook wb = new SXSSFWorkbook(100);

SXSSFSheet  sheet = wb.createSheet(“TEST_”+TODAYTXT);

SXSSFRow  row = sheet.createRow(0);

SXSSFCell cell = row.createCell(1);

//SXSSFWorkbook wb = new SXSSFWorkbook(100);

//Sheet sheet = wb.createSheet(“TEST”); // Sheet Name

//Row row = null;

//Cell cell = null;

int rowNo = 0;

String[] columns_head = new String[]{“매출일자”, “상품 ID”, “고객 ID”,”TEST1″,”TEST2″,”TEST3″,”TEST4″,”TEST5″};

int[] columns_width = new int[]{1000, 5000, 3000, 1000 , 2000, 3000,1000 , 500};

try {

// 테이블 헤더용 스타일

CellStyle headStyle = wb.createCellStyle();

headStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

headStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);

headStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);

headStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

headStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);

headStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

// 데이터용 경계 스타일

CellStyle bodyStyle = wb.createCellStyle();

bodyStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

bodyStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);

bodyStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);

bodyStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

// 헤더 부분 생성

row = sheet.createRow(rowNo++);

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

{

sheet.setColumnWidth(i, (sheet.getColumnWidth(i)) + columns_width[i]);

cell = row.createCell(i);

cell.setCellStyle(headStyle);

cell.setCellValue(columns_head[i]);

}

// 데이터 부분 생성

for(SaleFigureMDreportVO vo : list) {

row = sheet.createRow(rowNo++);

for(int j = 0; j < columns_head.length ; j++) 

{

cell = row.createCell(j);

cell.setCellStyle(bodyStyle);

if( j == 0 ) { cell.setCellValue(vo.getSDt()); }

if( j == 1 ) { cell.setCellValue(vo.getGId()); }

if( j == 2 ) { cell.setCellValue(vo.getNo()); }

if( j == 3 ) { cell.setCellValue(vo.getBKo()); }

if( j == 4 ) { cell.setCellValue(vo.getCNo()); }

if( j == 5 ) { cell.setCellValue(vo.getCNm()); }

if( j == 6 ) { cell.setCellValue(vo.getONo()); }

if( j == 7 ) { cell.setCellValue(vo.getINm()); }

}

}

// 엑셀 출력

response.setHeader(“Set-Cookie”, “fileDownload=true; path=/”);

response.setHeader(“Content-Disposition”, String.format(“attachment; filename=\”test_”+TODAYTXT+”.xlsx\””)); // 파일명

wb.write(response.getOutputStream());

}   catch(Exception e) {

response.setHeader(“Set-Cookie”, “fileDownload=false; path=/”);

response.setHeader(“Cache-Control”, “no-cache, no-store, must-revalidate”);

response.setHeader(“Content-Type”,”text/html; charset=utf-8″);

OutputStream out = null;

try {

out = response.getOutputStream();

byte[] data = new String(“fail..”).getBytes();

out.write(data, 0, data.length);

} catch(Exception ignore) {

ignore.printStackTrace();

} finally {

if(out != null) try { out.close(); } catch(Exception ignore) {}

}

} finally {

// 디스크 적었던 임시파일을 제거합니다.

wb.dispose();

try { wb.close(); } catch(Exception ignore) {}

}

}