[JXLS] 엑셀 다운로드

JXLS =  JXLS은 템플릿을 기반으로 최종 엑셀파일을 생성

데이터 (java 객체) + 엑셀 템플릿 = 엑셀결과 출력

엑셀을 그대로 사용하기 때문에 엑셀의 서식, 차트, 수식등을 넣을 수 있다.

pom.xml

<!-- Create Excel -->
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>3.13</version>
</dependency>

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.13</version>
</dependency>

<dependency> 
	<groupId>org.apache.poi</groupId> 
	<artifactId>poi-ooxml-schemas</artifactId> 
	<version>3.13</version> 
</dependency> 

<dependency> 
	<groupId>org.apache.poi</groupId> 
	<artifactId>poi-contrib</artifactId> 
	<version>3.7-beta3</version> 
</dependency> 

<!-- apache poi -->
<dependency> 
	<groupId>commons-beanutils</groupId> 
	<artifactId>commons-beanutils</artifactId> 
	<version>1.9.2</version> 
</dependency>

<dependency> 
	<groupId>commons-digester</groupId> 
	<artifactId>commons-digester</artifactId> 
	<version>2.0</version> 
</dependency>

<dependency> 
	<groupId>org.apache.commons</groupId> 
	<artifactId>commons-jexl</artifactId> 
	<version>2.1.1</version> 
</dependency>

<dependency> 
	<groupId>net.sf.jxls</groupId> 
	<artifactId>jxls-core</artifactId> 
	<version>1.0.5</version> 
</dependency>

 

ExcelView.java

import java.io.OutputStream;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.core.io.ClassPathResource;
import org.springframework.web.servlet.view.document.AbstractXlsxStreamingView;

import net.sf.jxls.transformer.XLSTransformer;

public class ExcelView extends AbstractXlsxStreamingView {

	private static final String sample = "/excel/sample.xlsx";// 클래스패스에 있는 Resource 경로 
	

	@Override
	protected void buildExcelDocument(final Map<String, Object> model, final Workbook workbook, final HttpServletRequest request,
			final HttpServletResponse response) throws Exception {

		OutputStream os = null;
		InputStream is = null;

		try {
			String fileName = "excelTest";
		
			is = new ClassPathResource(sample).getInputStream();
			
			response.setHeader("Content-Type", "application/octet-stream");
			response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");

			os = response.getOutputStream();

			XLSTransformer transformer = new XLSTransformer();

			Workbook excel = transformer.transformXLS(is, model);
			excel.write(os);
			os.flush();

		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		} finally {
			if (os != null) {
				try {
					os.close();
				} catch (IOException e) {
				}
			}
			if (is != null) {
				try {
					is.close();
				} catch (IOException e) {
				}
			}
		}
	}
}

 

ExcelController.java (데이터를 생성하여 ExcelView를 리턴)

@Controller
public class ExcelController {
	@RequestMapping(value = "/api/excel")
	public View reportExcelDownload2(final HttpServletResponse response,
			@RequestParam(value = "reqData", required = false, defaultValue = "") final String reqData, final Model model) throws Exception {

		try {
			//데이터 만들기
			List<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
			
			HashMap<String, Object> obj = null;
			
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy.MM.dd HH:mm:ss");

			obj = new HashMap<String, Object>();
			obj.put("name", "가트루");
			obj.put("age", "20");
			obj.put("address", "대전");
			list.add(obj);
				
			obj = new HashMap<String, Object>();
			obj.put("name", "나트루");
			obj.put("age", "27");
			obj.put("address", "서울");
			list.add(obj);
				
			obj = new HashMap<String, Object>();
			obj.put("name", "다트루");
			obj.put("age", "50");
			obj.put("address", "부산");
			list.add(obj);
				
			obj = new HashMap<String, Object>();
			obj.put("name", "라트루");
			obj.put("age", "100");
			obj.put("address", "제주");
			list.add(obj);
			
			model.addAttribute("list", list);
			model.addAttribute("count", list.size());
			model.addAttribute("DownloadDate", sdf.format(new Date()));
	

		} catch (Exception e) {
		e.printStackTrace();
		}
		return new ExcelView();
	}
}

 

sample.xlsx 파일 

셀에 치환할 데이터를 넣기 위해서는 ${} 형식 사용. (JSTL과 매우 흡사) 

<jx:forEach>태그내의 내용을 아이템 개수 만큼 반복하는 방식. 

<jx:forEach items="${list}" var="info">
	${info.age}
</jx:forEach>

 

sample.xlsx

 

 class path Resource 경로대로 위에서 생성한 .xlsx 파일을 넣어준다.

 

결과 화면 

 

 

 

 

 

참고 :

JXLS -

Java로 엑셀 다운로그 구현하기