利用AbstractJExcelView生成Excel的过程

1.spring控制器继承的是AbstractJExcelView，并实现其buildExcelDocument方法。

2.下面是具体实现：

import java.net.URLEncoder;
import java.util.List;
import java.util.Map;

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

import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.apache.commons.beanutils.BeanUtils;
import org.springframework.web.servlet.view.document.AbstractJExcelView;

import com.smartpay.commons.base.SystemException;

public class ListForExcelView extends AbstractJExcelView {

 /**
  * 不同交易类型对应标题栏信息映射
  */
 private Map<String, ExcelColVO[]> excelColVOMap;

 /**
  * 头信息在数据模型中的名称
  */
 private String headInfoName;

 /**
  * Excel头信息数组
  */
 private ExcelColVO[] headInfoRowArr;

 /**
  * 信息列表在数据模型中的名称
  */
 private String listNameInMode;

 /**
  * 列表类型名在数据模型中的名称
  */
 private String listTypeNameInMode;

 /**
  * 下载文件的名称
  */
 private String downloadFileName;

 /**
  * Excel表名称
  */
 private String sheetName;

 @SuppressWarnings("unchecked")
 @Override
 protected void buildExcelDocument(Map mode, WritableWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception {
  //取得交易信息
  String transactionType = (String) mode.get(listTypeNameInMode);
  List transactionInfoList = (List) mode.get(listNameInMode);
  if (transactionInfoList == null) {
   throw new SystemException("未找到查询到的交易列表信息");
  }

  //设置文件响应信息
  String showFileName = URLEncoder.encode(downloadFileName + ".xls", "UTF-8");
  showFileName = new String(showFileName.getBytes("iso8859-1"), "gb2312");
  response.setContentType("application/msexcel");// 定义输出类型
  response.setHeader("Pragma", "public");
  response.setHeader("Cache-Control", "max-age=30");
  response.setHeader("Content-disposition", "attachment; filename=" + new String(showFileName.getBytes("gb2312"), "iso8859-1"));

  //初始化参数
  WritableSheet sheet = workbook.createSheet(sheetName, 1);
  int i = 0;//行计数器

  //输出Excel头信息
  WritableFont wfTitle = new WritableFont(WritableFont.ARIAL, 12);
  WritableCellFormat contentFormat = new WritableCellFormat(wfTitle);
  contentFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
  if (headInfoName != null && headInfoRowArr != null) {
   Object headInfoVO = mode.get(headInfoName);
   for (int j = 0; j < headInfoRowArr.length; j++) {
    ExcelColVO headInfoRow = headInfoRowArr[j];
    String propertyValue = BeanUtils.getProperty(headInfoVO, headInfoRow.getColProperty());
    sheet.addCell(new Label(0, i, headInfoRow.getColTitle(), contentFormat));
    sheet.addCell(new Label(1, i, propertyValue, contentFormat));
    i++;
   }
   i++;
  }

  //输出标题栏信息
  ExcelColVO[] excelColVOList = getExcelColVOArr(transactionType);
  WritableFont wf = new WritableFont(WritableFont.ARIAL, 16, WritableFont.BOLD, false);
  WritableCellFormat titleFormat = new WritableCellFormat(wf);
  for (int j = 0; j < excelColVOList.length; j++) {
   sheet.addCell(new Label(j, i, excelColVOList[j].getColTitle(), titleFormat));
  }

  //输出内容体
  for (Object transactionInfoVO : transactionInfoList) {
   i++;
   for (int j = 0; j < excelColVOList.length; j++) {
    String propertyValue = BeanUtils.getProperty(transactionInfoVO, excelColVOList[j].getColProperty());
    sheet.addCell(new Label(j, i, propertyValue, contentFormat));
   }
  }
 }

 /**
  * 取得列信息
  *
  * @param transactionType
  * @return
  */
 private ExcelColVO[] getExcelColVOArr(String transactionType) {
  ExcelColVO[] excelColVOList = excelColVOMap.get(transactionType);
  if (excelColVOList == null) {
   excelColVOList = excelColVOMap.get(null);//帮助类似于[TRANSACTION_DEPOSIT ...]等使用抽象交易信息的显示
   if (excelColVOList == null) {
    throw new SystemException("无法取得交易类型[" + transactionType + "]对应的标题栏信息!");
   }
  }
  return excelColVOList;
 }

 /**
  * @return the excelColVOMap
  */
 public Map<String, ExcelColVO[]> getExcelColVOMap() {
  return excelColVOMap;
 }

 /**
  * @param excelColVOMap the excelColVOMap to set
  */
 public void setExcelColVOMap(Map<String, ExcelColVO[]> excelColVOMap) {
  this.excelColVOMap = excelColVOMap;
 }

 /**
  * @return the listNameInMode
  */
 public String getListNameInMode() {
  return listNameInMode;
 }

 /**
  * @param listNameInMode the listNameInMode to set
  */
 public void setListNameInMode(String listNameInMode) {
  this.listNameInMode = listNameInMode;
 }

 /**
  * @return the listTypeNameInMode
  */
 public String getListTypeNameInMode() {
  return listTypeNameInMode;
 }

 /**
  * @param listTypeNameInMode the listTypeNameInMode to set
  */
 public void setListTypeNameInMode(String listTypeNameInMode) {
  this.listTypeNameInMode = listTypeNameInMode;
 }

 /**
  * @return the downloadFileName
  */
 public String getDownloadFileName() {
  return downloadFileName;
 }

 /**
  * @param downloadFileName the downloadFileName to set
  */
 public void setDownloadFileName(String downloadFileName) {
  this.downloadFileName = downloadFileName;
 }

 /**
  * @return the sheetName
  */
 public String getSheetName() {
  return sheetName;
 }

 /**
  * @param sheetName the sheetName to set
  */
 public void setSheetName(String sheetName) {
  this.sheetName = sheetName;
 }

 /**
  * @return the headInfoColArr
  */
 public ExcelColVO[] getHeadInfoRowArr() {
  return headInfoRowArr;
 }

 /**
  * @param headInfoColArr the headInfoColArr to set
  */
 public void setHeadInfoRowArr(ExcelColVO[] headInfoColArr) {
  this.headInfoRowArr = headInfoColArr;
 }

 /**
  * @return the headInfoName
  */
 public String getHeadInfoName() {
  return headInfoName;
 }

 /**
  * @param headInfoName the headInfoName to set
  */
 public void setHeadInfoName(String headInfoName) {
  this.headInfoName = headInfoName;
 }

}

 

 

 

import java.beans.PropertyEditorSupport;

import com.smartpay.commons.base.SystemException;
import com.smartpay.commons.util.StringUtil;
 

public class ExcelColVOPropertyEditor extends PropertyEditorSupport {

 /* (non-Javadoc)
  * @see java.beans.PropertyEditorSupport#setAsText(java.lang.String)
  */
 @Override
 public void setAsText(String excelColInfo) throws IllegalArgumentException {
  //验证参数是否为空
  if (StringUtil.isEmptyStr(excelColInfo)) {
   throw new SystemException("空信息无法转换为ExcelColVO对象");
  }

  //验证参数格式是否正确(是否包含"=")
  if (excelColInfo.indexOf('=') == -1) {
   throw new SystemException("构造ExcelColVO对象的原信息格式为(例:交易号=transactionId)样式,您提供的信息有误[" + excelColInfo + "]");
  }

  //验证参数格式是否正确
  String[] excelColInfoArr = excelColInfo.split("=");
  if (excelColInfoArr.length != 2) {
   throw new SystemException("信息[" + excelColInfo + "]使用'='分割出的字符数组长度不为2!");
  }

  //定位各个域的值
  String colTitle = excelColInfoArr[0];
  String colProperty = excelColInfoArr[1];

  //构造对象
  ExcelColVO excelColVO = new ExcelColVO();
  excelColVO.setColProperty(colProperty);
  excelColVO.setColTitle(colTitle);

  //设置对象信息
  setValue(excelColVO);
 }

}

 

 

 

public class ExcelColVO {
 /**
  * 列名称
  */
 private String colTitle;

 /**
  * 从行对象取得列信息的属性
  */
 private String colProperty;

 /**
  * @return the colName
  */
 public String getColTitle() {
  return colTitle;
 }

 /**
  * @param colName the colName to set
  */
 public void setColTitle(String colName) {
  this.colTitle = colName;
 }

 /**
  * @return the colProperties
  */
 public String getColProperty() {
  return colProperty;
 }

 /**
  * @param colProperties the colProperties to set
  */
 public void setColProperty(String colProperties) {
  this.colProperty = colProperties;
 }
}

spring 相关配置----------------------------------------------------------------------------------------------------------------------------------

 <bean name="transactionQueryViewForExcel" class="com.smartpay.websharetools.springmvcview.excel.ListForExcelView">
  <property name="downloadFileName" value="交易查询"></property>
  <property name="sheetName" value="交易查询"></property>
  <property name="listTypeNameInMode" value="transactionType"></property>
  <property name="listNameInMode" value="transactionInfoList"></property>
  <property name="excelColVOMap">
   <map>
    <entry>
     <key>
      <null></null>
     </key>
     <list >
      <value>交易号=transactionId</value>
      <value>创建时间=beginTime</value>
      <value>结束时间=endTime</value>
      <value>交易类型=transactionTypeName</value>
      <value>交易对方=otherSideName</value>
      <value>金额(元)=transactionAmountForShow</value>
      <value>交易状态=transactionStatusName</value>
     </list>
    </entry>
    <entry key="TRANSACTION_TOPUP">
     <list>
      <value>交易号=id</value>
      <value>充值类型=topupType</value>
      <value>提交时间=checkinTime</value>
      <value>完成时间=operatorTime</value>
      <value>被充号码=topupMp</value>
      <value>请求(元)=topupAmount</value>
      <value>实际(元)=factTopupAmount</value>
      <value>支付(元)=transactionAmount</value>
      <value>方式=channelTypeName</value>
      <value>状态=statusName</value>
     </list>
    </entry>
    <entry key="TRANSACTION_PAYMENT">
     <list>
      <value>交易号=transactionId</value>
      <value>订单号=merchantTxSeqNo</value>
      <value>商户名称=merchantName</value>
      <value>商品名称=itemName</value>
      <value>交易开始时间=beginTime</value>
      <value>交易结束时间=endTime</value>
      <value>支付手机号=userMp</value>
      <value>交易金额=transactionAmount</value>
      <value>退款金额=paybackAmount</value>
      <value>交易状态=transactionStatusName</value>
     </list>
    </entry>
   </map>
  </property>
 </bean>

 

 

 
 <bean name="transferQueryViewForExcel" class="com.smartpay.websharetools.springmvcview.excel.ListForExcelView">
  <property name="downloadFileName" value="账务明细查询"></property>
  <property name="sheetName" value="账务明细查询"></property>
  <property name="listTypeNameInMode" value="aaa"></property>
  <property name="listNameInMode" value="transferQueryTOList"></property>
  <property name="excelColVOMap">
   <map>
    <entry>
     <key>
      <null></null>
     </key>
     <list >
      <value>日期和时间=transferTime</value>
      <value>交易号=transactionId</value>
      <value>交易对方=otherSideName</value>
      <value>转账类型=transferTypeName</value>
      <value>收入(元)=receiptAmountForShow</value>
      <value>支出(元)=payoutAmountForShow</value>
      <value>余额(元)=balance</value>
     </list>
    </entry>
   </map>
  </property>
 </bean>