Excel download & upload

excel 라이브러리 종류 poi , jxl

엑셀다운받을대는 poi <--대용량속도가 느리지만 엑셀형태로 정확히나와 엑셀 작업하기 좋음

엑셀업로드할대는 jxl <--대용량 속도가 빠름

but 엑셀 업로드 할때 jxl은 xls만 지원되기때문에 xls,xlxs 둘다 사용할수있는 poi로 작업함

-----엑셀 다운할때---

1. ServetOutPutStream out = response.getOutPutStream(); <--에러남

에러문구: java getOutPutStream() has already been called for this response

해결방안: jsp 파일에 out.clear(); ...해주면 되는데 사용안함

2. File file = new File(filename) 로컬에서는 될지라도 웹에서 다운받는 형식이면 사용못함

3. AbstractExcelView 상속받아서 쓴다.

좋은점이 자동으로 response 해준다. 간단하게 해결가능

주의해야할 점은 ajax로 하면 안됨..에러남 form으로 submit 해주면 된다,

---엑셀 다운 적용 소스---

ModelAndView mav = new ModelAndView();

List orderinfoList = adminService.getorderinfoExcelList(rMap);

map.put("downsize", orderinfoList.size());

mav.setViewName("orderInfoExcelView");

mav.addObject("orderInfoList",orderinfoList);

return mav;

<pom.xml>

<groupId>org.apache.poi</groupId>

</dependency>

<groupId>org.apache.poi</groupId>

<artifactId>poi-ooxml</artifactId>

</dependency>

<servlet.xml>

</bean>

</bean>

package redshop4.admin.controller;

import java.util.List;

import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

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

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

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;

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

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

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

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

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

import org.springframework.web.servlet.view.document.AbstractExcelView;

import redshop4.order.model.OrderInfoExcel;

public class OrderInfoExcelView extends AbstractExcelView{

/**

* 생성자

public OrderInfoExcelView() {

}

@Override

protected void buildExcelDocument(Map<String, Object> model,

HSSFWorkbook workbook, HttpServletRequest request,

HttpServletResponse response) throws Exception {

// TODO Auto-generated method stub

String fileName = "주문내역.xls";

String encodedFileName = new String(fileName.getBytes("euc-kr"), "8859_1");

List orderinfoList = (List)model.get("orderInfoList");

// Workbook 생성 (참고: http://poi.apache.org/spreadsheet/how-to.html)

//Workbook xlsWb = new HSSFWorkbook(); // Excel 2007 이전 버전

//Workbook xlsxWb = new XSSFWorkbook(); // Excel 2007 이상

//SXSSFWorkbook sxssfWb = new SXSSFWorkbook(-1); // turn off auto-flushing and accumulate all rows in memory

//sxssfWb.setCompressTempFiles(true); // temp files will be gzipped

// *** Sheet-------------------------------------------------

// Sheet 생성

HSSFSheet sheet1 = workbook.createSheet("firstSheet");

// 컬럼 너비 설정

sheet1.setColumnWidth(0, 10000);

sheet1.setColumnWidth(9, 10000);

// ----------------------------------------------------------

// *** Style--------------------------------------------------

// Cell 스타일 생성

//CellStyle cellStyle = xlsWb.createCellStyle();

// 줄 바꿈

//cellStyle.setWrapText(false);

//색 설정

//cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);

HSSFRow row = null;

HSSFCell cell = null;

//----------------------------------------------------------

int cellIndex = 0;

if(orderinfoList != null){

row = sheet1.createRow(0);

row.createCell(0).setCellValue(getString("주문번호"));

row.createCell(1).setCellValue(getString("주문일시"));

row.createCell(2).setCellValue(getString("주문처리상태"));

row.createCell(3).setCellValue(getString("주문자명(ID)"));

row.createCell(4).setCellValue(getString("주문자 전화번호"));

row.createCell(5).setCellValue(getString("주문자 이메일"));

row.createCell(6).setCellValue(getString("수취인명"));

row.createCell(7).setCellValue(getString("수취인 휴대전화"));

row.createCell(8).setCellValue(getString("수취인 자택전화"));

row.createCell(9).setCellValue(getString("배송지 우편번호"));

row.createCell(10).setCellValue(getString("배송지 주소"));

row.createCell(11).setCellValue(getString("배송지 요청사항"));

row.createCell(12).setCellValue(getString("상품명"));

row.createCell(13).setCellValue(getString("옵션"));

row.createCell(14).setCellValue(getString("수량"));

row.createCell(15).setCellValue(getString("상품별금액"));

row.createCell(16).setCellValue(getString("구매금액"));

row.createCell(17).setCellValue(getString("사용적립금"));

row.createCell(18).setCellValue(getString("배송비"));

row.createCell(19).setCellValue(getString("총결제금액"));

row.createCell(20).setCellValue(getString("결제방법"));

row.createCell(21).setCellValue(getString("결제상태"));

row.createCell(22).setCellValue(getString("결제메모"));

row.createCell(23).setCellValue(getString("배송업체"));

row.createCell(24).setCellValue(getString("송장번호"));

for(int i=0; i<orderinfoList.size(); i++){

cellIndex = 0; //초기화

OrderInfoExcel orderInfoExcel = (OrderInfoExcel)orderinfoList.get(i);

//줄

row = sheet1.createRow((i+1));

//셀--------------------------------------------

// 0 주문번호