SpringMVC杂记(十一) 使用Excel视图

SpringMVC杂记(十一) 使用Excel视图

一) 其实这个功能在spring2.x时代就提供了。一直没用过，今天在spring-mvc3.2.x的环境下试验了一次。还算简单易用。
二) 依赖。
spring依赖POI或jExcel来实现对excel输出的支持，前者是apache出品，貌似名气更大，本例使用第一个。

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

三) spring提供了一个AbstractExcelView作为自己实现的视图的父类。实例代码如下。
poi的api一般人不会太熟悉，可以参考apache-poi官方文档

package ying.car.view;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;

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

import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.joda.time.DateTime;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.servlet.view.document.AbstractExcelView;

import ying.car.binding.DateRange;
import ying.car.domain.RefuelingRecord;

public class RefuelingRecordExcelView extends AbstractExcelView {

	private static final Logger LOGGER = LoggerFactory.getLogger(RefuelingRecordExcelView.class);
	private static final DateFormat DATE_FORMAT = new SimpleDateFormat("yyyyMMdd");
	
	@Override
	@SuppressWarnings({"unchecked"})
	protected void buildExcelDocument(
			Map<String, Object> model,		// MVC中的M就在这里了
			HSSFWorkbook workbook,
			HttpServletRequest request,
			HttpServletResponse response) throws Exception
	{
		List<RefuelingRecord> rrl = (List<RefuelingRecord>) model.get("refuelingRecordList");
		DateRange dr = (DateRange) model.get("dateRange");
		
		if (LOGGER.isDebugEnabled()) {
			LOGGER.debug("refueling-record-list-size: {}", rrl.size());
			LOGGER.debug("dateRange: {}", dr);
			if (dr != null) {
				LOGGER.debug("start: {}", new DateTime(dr.getStart()).toString("yyyy/MM/dd"));
				LOGGER.debug("end: {}", new DateTime(dr.getEnd()).toString("yyyy/MM/dd"));
			}
		}

		HSSFSheet sheet = workbook.createSheet(DATE_FORMAT.format(dr.getStart()) + "-" + DATE_FORMAT.format(dr.getEnd()));

		setColumnsWidth(sheet);
		fillTableHeader(workbook, sheet);
		fillTableBody(workbook, sheet, rrl);
	}
	
	private void setColumnsWidth(HSSFSheet sheet) {
		final int[] warr = new int[] {
			500,  // <空>
			4500, // 日期
			4500, // 车辆
			4500, // 燃油种类
			4500, // 燃油单价
			4500, // 加油方式
			4500, // 加油量
			3000, // 花费
			12000  // 备注
		};
		for (int i = 0; i < warr.length; i ++) {
			sheet.setColumnWidth(i, warr[i]);
		}
	}

	// 填充表格头
	private void fillTableHeader(HSSFWorkbook workbook, HSSFSheet sheet) {
		final String[] contents = new String[] {
			"日期",
			"车辆",
			"燃油种类",
			"燃油单价(元/升)",
			"加油方式",
			"加油量(升)",
			"花费(元)",
			"备注"
		};
		
		int r = 1;
		int c = 1;
		
		CellStyle style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());		// 填充黄色
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);			// 填充方式
		
		// 设置border
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setBorderBottom(CellStyle.BORDER_THIN);

		for (int i = 0; i < contents.length; i ++) {
			Cell cell = getCell(sheet, r, c + i);
			cell.setCellValue(contents[i]);
			cell.setCellStyle(style);
		}
	}
	
	private void fillTableBody(HSSFWorkbook workbook, HSSFSheet sheet, List<RefuelingRecord> records) {
		// 通用style
		CellStyle style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.WHITE.getIndex());		// 填充白色
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);					// 填充方式
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setBorderBottom(CellStyle.BORDER_THIN);
		
		// 日期style
		CellStyle dateStyle = workbook.createCellStyle();
		dateStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());		// 填充白色
		dateStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);					// 填充方式
		dateStyle.setBorderLeft(CellStyle.BORDER_THIN);
		dateStyle.setBorderRight(CellStyle.BORDER_THIN);
		dateStyle.setBorderTop(CellStyle.BORDER_THIN);
		dateStyle.setBorderBottom(CellStyle.BORDER_THIN);
		dateStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
		
		int r = 2;
		int c = 1;
		Cell cell = null;
		
		for (int i = 0; i < records.size(); i ++) {
			RefuelingRecord rr = records.get(i);
			
			// 日期
			cell = getCell(sheet, r, c + 0);
			if (rr.getDate() != null)
			cell.setCellValue(rr.getDate());
			cell.setCellStyle(dateStyle);

			// 车辆
			cell = getCell(sheet, r, c + 1);
			if (rr.getVehicle().getNickname() != null)
			cell.setCellValue(rr.getVehicle().getNickname());
			cell.setCellStyle(style);
			
			// 燃油种类
			cell = getCell(sheet, r, c + 2);
			if (rr.getGasType() != null) {
				String s = null;
				switch (rr.getGasType()) {
				case _0: s  = "0号柴油"; break;
				case _93: s = "93号汽油"; break;
				case _97: s = "97号汽油"; break;
				case _98: s = "98号汽油"; break;
				}
				cell.setCellValue(s);
			}
			cell.setCellStyle(style);
			
			// 单价
			cell = getCell(sheet, r, c + 3);
			if (rr.getPriceOfGas() != null)
			cell.setCellValue(rr.getPriceOfGas());
			cell.setCellStyle(style);
			
			// 加油方式
			cell = getCell(sheet, r, c + 4);
			if (rr.getRefuelingType() != null) {
				String s = null;
				switch (rr.getRefuelingType()) {
				case FIXED_CUBAGE:
					s = "固定容积"; break;
				case FIXED_MONEY:
					s = "固定金额"; break;
				case FULL:
					s = "加满"; break;
				}
				cell.setCellValue(s);
			}
			cell.setCellStyle(style);
			
			// 加油量
			cell = getCell(sheet, r, c + 5);
			if (rr.getCubageOfGas() != null)
			cell.setCellValue(rr.getCubageOfGas());
			cell.setCellStyle(style);
			
			// 花费
			cell = getCell(sheet, r, c + 6);
			if (rr.getSumOfMoney() != null)
			cell.setCellValue(rr.getSumOfMoney());
			cell.setCellStyle(style);
			
			// 备注
			cell = getCell(sheet, r, c + 7);
			if (rr.getComment() != null)
			cell.setCellValue(rr.getComment());
			cell.setCellStyle(style);

			r ++;
		}
	}
}

四) Controller中返回逻辑视图名 (代码片段)

@RequiresUser		// 安全框架用元注释
@RequiresRoles({"ROLE_USER"})
@RequestMapping(value = "/list/excel", method = RequestMethod.GET)
public String listByExcel(
		@DateRangeFormat(pattern = "yyyy-MM-dd") @RequestParam("dateRange") DateRange dateRange,
		ModelMap modelMap
	)
{
	Integer currentUserId = ShiroUtils.getPrincipalDomainId();

	if (LOGGER.isDebugEnabled()) {
		LOGGER.debug("currentUserId: {}", currentUserId);
		if (dateRange != null) {
			LOGGER.debug("start: {}", new DateTime(dateRange.getStart()).toString("yyyy/MM/dd"));
			LOGGER.debug("end: {}", new DateTime(dateRange.getEnd()).toString("yyyy/MM/dd"));
		}
	}

	// 放入model
	modelMap.put("dateRange", dateRange);
	modelMap.put("refuelingRecordList", gasService.findRefuelingRecordByDateRange(currentUserId, dateRange));
	
	return "refueling-record-list"; // 最终返回逻辑视图名
}

五) 为spring-mvc配置多个视图解析器。
spring-mvc.xml(片段)

<bean class="org.springframework.web.servlet.view.XmlViewResolver">
	<property name="order" value="1" /> <!-- order很重要 -->
	<property name="location" value="classpath:/META-INF/views.xml" />
</bean>
<bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
	<property name="order" value="9999" />
	<property name="viewClass" value="org.springframework.web.servlet.view.JstlView" />
	<property name="prefix" value="/WEB-INF/jsp/"/>
	<property name="suffix" value=".jsp"/>
</bean>

views.xml

<bean name="refueling-record-list" class="ying.car.view.RefuelingRecordExcelView" />

六) 效果图