今天分享在Spring MVC中輸出Excel，原理與JSON/XML相同，皆是透過o.s.web.servlet.view.ContentNegotiatingViewResolver，產生Excel，首先需要準備Apache POI相關Dependency於pom.xml：

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>3.9</version>
</dependency>

接著Springframework提供o.s.web.servlet.view.document.AbstractExcelView供開發者使用，新增ExcelView繼承此class，Apache POI Excel API可參考官網，基本上要先新增Row(列)後，再新增各個Cell(儲存格)後在設定Cell的數值，或是CellStyle如儲存格顏色或是自行顏色等，

public class ExcelView extends AbstractExcelView {
	
	@Autowired
	DCNService dcnService;
	
	@Override
	protected void buildExcelDocument(Map<String, Object> model,
			HSSFWorkbook workbook, HttpServletRequest request,
			HttpServletResponse response) throws Exception {
		// TODO Auto-generated method stub
		
		Sheet sht=workbook.createSheet("DCNList"); //有workbook後必須建立sheet
		List<DCN> dcnList=dcnService.findAll(); //取得DCN所有Record
		
		Row row=null;
		Cell cell=null;
		int currentRow=0; //需要兩個變數來控制新增的列與欄
		int currentCell=0;	
		row= sht.createRow(currentRow++); //新增一列
		//以下建立標題列
		cell=row.createCell(currentCell++);//新增一個儲存格 
		cell.setCellValue("Serial");
		
		cell=row.createCell(currentCell++);
		cell.setCellValue("DCN No");
		
		cell=row.createCell(currentCell++);
		cell.setCellValue("DCN Rev");
		
		cell=row.createCell(currentCell++);
		cell.setCellValue("Category");
		
		cell=row.createCell(currentCell++);
		cell.setCellValue("Tracking Number");
		
		cell=row.createCell(currentCell++);
		cell.setCellValue("Issued Date");
		
		cell=row.createCell(currentCell++);
		cell.setCellValue("Completed Date");
		
		for(DCN dcn:dcnList){
			row=sht.createRow(currentRow++); //新增一列
			currentCell=0;
			row.createCell(currentCell++).setCellValue(currentRow-1);
			row.createCell(currentCell++).setCellValue(dcn.getNo());
			row.createCell(currentCell++).setCellValue(dcn.getRev());
			row.createCell(currentCell++).setCellValue(dcn.getCategory());
			row.createCell(currentCell++).setCellValue(dcn.gettrackNumber());
			row.createCell(currentCell++).setCellValue(dcn.getIssuedDate());
			row.createCell(currentCell++).setCellValue(dcn.getCompletedDate());
		}
		for(int i=1;i<7;i++){
			sht.autoSizeColumn(i);//自動調整欄寬
		}
	}
}

接著需要新增ExcelView bean於dispatchservlet.xml中，並加入defaultView的清單中，code如下：

....
<!-- 新增Excel View bean並指定對應的class -->
	<bean id="excelView" class=" tw.blogger.springtech.springmvc.view.ExcelView"/>
	
	<bean
		class="org.springframework.web.servlet.view.ContentNegotiatingViewResolver">
		<property name="defaultViews">
			<list>
				<ref bean="jsonView" />
				<ref bean="xmlView" />
				<ref bean="excelView"/>
			</list>
		</property>
	</bean>
....

新增Export超連結於DCNList.jsp 表格下面

<a href="<spring:url value="/dcn.xls"/>" class="button info">Export To Excel</a>

啟動Server，相關畫面如下：

Console:

23:52:21 [http-nio-8080-exec-6] DispatcherServlet - DispatcherServlet with name 'dispatcher' processing GET request for [/SpringMVC/dcn.xls]
23:52:21 [http-nio-8080-exec-6] RequestMappingHandlerMapping - Looking up handler method for path /dcn.xls
23:52:21 [http-nio-8080-exec-6] RequestMappingHandlerMapping - Returning handler method [public java.lang.String tw.blogger.springtech.springmvc.controller.DCNController.DCNList(org.springframework.ui.Model)]
23:52:21 [http-nio-8080-exec-6] DispatcherServlet - Last-Modified value for [/SpringMVC/dcn.xls] is: -1
Hibernate: select dcn0_.prikey as prikey1_0_, dcn0_.category as category2_0_, dcn0_.completedDate as complete3_0_, dcn0_.issuedDate as issuedDa4_0_, dcn0_.no as no5_0_, dcn0_.rev as rev6_0_, dcn0_.trackNumber as trackNum7_0_ from DCN dcn0_
23:52:22 [http-nio-8080-exec-6] ContentNegotiatingViewResolver - Requested media types are [application/vnd.ms-excel] based on Accept header types and producible media types [*/*])
23:52:22 [http-nio-8080-exec-6] ContentNegotiatingViewResolver - Returning [tw.blogger.springtech.springmvc.view.ExcelView: name 'excelView'] based on requested media type 'application/vnd.ms-excel'
23:52:22 [http-nio-8080-exec-6] DispatcherServlet - Rendering view [tw.blogger.springtech.springmvc.view.ExcelView: name 'excelView'] in DispatcherServlet with name 'dispatcher'
Hibernate: select dcn0_.prikey as prikey1_0_, dcn0_.category as category2_0_, dcn0_.completedDate as complete3_0_, dcn0_.issuedDate as issuedDa4_0_, dcn0_.no as no5_0_, dcn0_.rev as rev6_0_, dcn0_.trackNumber as trackNum7_0_ from DCN dcn0_
23:52:23 [http-nio-8080-exec-6] DispatcherServlet - Successfully completed request

明天繼續分享PDF部分