Maven
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.10-FINAL</version>
<type>jar</type>
</dependency>
views.properties
InsureExcelView.(class)=com.nanshan.vip.backend.report.InsureExcelView
其實這裡也可以設定到 spring.xml ( 不過我還沒試過這個，建議先用properties 紹)
<bean name="InsureExcelView" class="com.nanshan.vip.backend.report.InsureExcelView"/>
controller
/*
* 保戶訂單報表下載
*/
@Login
@RequestMapping(value="/insureDownload", produces = "text/html;charset=UTF-8")
public ModelAndView insureDownload(Model model, HttpServletRequest request, HttpServletResponse response, HttpSession session) throws Exception {
logger.info("*** INTO insureDownload Controller ***");
//檢核欄位
String msg = insureValid(request);
String returnPage = "report/reportManagement";
try{
if(StringUtils.isBlank(msg)){
String startDate = getParameter(request, "insureStartDate");
String endDate = getParameter(request, "insureEndDate");
InsureManager insureManager = new InsureManager();
insureManager.setInquireStartDate(startDate);
insureManager.setInquireEndDate(endDate);
//Query 保戶&生日禮讚
List<OrderInsure> orderBirthdayList = orderInsureService.orderBirthdayReport(insureManager);
List<OrderInsure> orderInsureList = orderInsureService.orderInsureReport(insureManager);
List<OrderInsure> insureList = new ArrayList<OrderInsure>();
logger.info("orderBirthdayList size="+orderBirthdayList.size());
logger.info("orderInsureList size="+orderInsureList.size());
String filename = "保戶訂單報表_"+startDate+"_"+endDate+".xls";
String headerFileName = new String(filename.getBytes(), "ISO8859-1");
if(orderBirthdayList.size() != 0 ){
for(int i = 0 ; i < orderBirthdayList.size() ; i++){
OrderInsure insureModel = new OrderInsure();
insureModel.setInsureId(orderBirthdayList.get(i).getInsureId());
insureModel.setOrderTime(ProductUtil.formatDate(orderBirthdayList.get(i).getOrderTime()));
insureModel.setProdName(orderBirthdayList.get(i).getProdName());
insureModel.setStoreName(orderBirthdayList.get(i).getStoreName());
insureModel.setQuantity(orderBirthdayList.get(i).getQuantity());
if("Y".equals(orderBirthdayList.get(i).getVipProd())){
insureModel.setVipProd("是");
}else{
insureModel.setVipProd("否");
}
insureModel.setSource("brithday");
insureList.add(insureModel);
}
}else{
model.addAttribute("msg", "生日禮讚日期範圍內查無資料");
}
//處理保戶List
if(orderInsureList.size() != 0 ){
for(int i = 0 ; i < orderInsureList.size() ; i++){
OrderInsure insureModel = new OrderInsure();
insureModel.setInsureId(orderInsureList.get(i).getInsureId());
insureModel.setOrderTime(ProductUtil.formatDate(orderInsureList.get(i).getOrderTime()));
insureModel.setProdName(orderInsureList.get(i).getProdName());
insureModel.setStoreName(orderInsureList.get(i).getStoreName());
insureModel.setQuantity(orderInsureList.get(i).getQuantity());
insureModel.setProductType(ProdTypeEnum.getEnumValueByKey(orderInsureList.get(i).getProductType()));
if("Y".equals(orderInsureList.get(i).getVipProd())){
insureModel.setVipProd("是");
}else{
insureModel.setVipProd("否");
}
insureModel.setSource("insure");
insureList.add(insureModel);
}
}else{
model.addAttribute("msg", "保戶日期範圍內查無資料");
}
response.setHeader("Content-Disposition", "attachment; filename="+headerFileName);
logger.info("before ModelAndView insureList siz ->"+insureList.size());
ModelAndView modelAndView = new ModelAndView("InsureExcelView", "insureList", insureList);
return modelAndView;
}else{
logger.info("檢核失敗");
model.addAttribute("msg", msg);
}
}catch (Exception e){
e.printStackTrace();
model.addAttribute("msg", "Export Report Fail:"+e.getMessage());
logger.error("Export Report Fail="+e.getMessage());
return new ModelAndView(returnPage);
}
return new ModelAndView(returnPage);
}
ExcelView
我這裡資料是寫死的，其實不太好，View 應該主要負責處理產出就好
資料應該都要在傳過來前處理好
package com.nanshan.vip.backend.report;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
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.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.springframework.web.servlet.view.document.AbstractExcelView;
import com.nanshan.vip.backend.model.OrderInsure;
public class InsureExcelView extends AbstractExcelView {
@Override
protected void buildExcelDocument(Map model, HSSFWorkbook workbook, HttpServletRequest request,
HttpServletResponse response) throws Exception {
logger.info("=== INTO buildExcelDocument insureList ===");
@SuppressWarnings("unchecked")
List<OrderInsure> insureList = (List<OrderInsure>) model.get("insureList");
HSSFSheet sheet1 = workbook.createSheet("保戶優惠");
HSSFSheet sheet2 = workbook.createSheet("生日禮讚");
sheet1.setColumnWidth(0, 15 * 256);
sheet1.setColumnWidth(1, 20 * 256);
sheet1.setColumnWidth(2, 25 * 256);
sheet1.setColumnWidth(3, 55 * 256);
sheet1.setColumnWidth(4, 13 * 256);
sheet1.setColumnWidth(5, 13 * 256);
sheet1.setColumnWidth(6, 13 * 256);
sheet2.setColumnWidth(0, 17 * 256);
sheet2.setColumnWidth(1, 24 * 256);
sheet2.setColumnWidth(2, 30 * 256);
sheet2.setColumnWidth(3, 60 * 256);
sheet2.setColumnWidth(4, 13 * 256);
sheet2.setColumnWidth(5, 13 * 256);
//保護優惠
HSSFRow header = sheet1.createRow(0);
header.createCell(0).setCellValue("保戶編號");
header.createCell(1).setCellValue("兌換日期");
header.createCell(2).setCellValue("商店名稱");
header.createCell(3).setCellValue("優惠名稱");
header.createCell(4).setCellValue("優惠種類");
header.createCell(5).setCellValue("兌換數量");
header.createCell(6).setCellValue("VIP");
//生日禮讚
HSSFRow header2 = sheet2.createRow(0);
header2.createCell(0).setCellValue("保戶編號");
header2.createCell(1).setCellValue("兌換日期");
header2.createCell(2).setCellValue("商店名稱");
header2.createCell(3).setCellValue("優惠名稱");
header2.createCell(4).setCellValue("兌換數量");
header2.createCell(5).setCellValue("VIP");
int sheet1Count = 1;
int sheet2Count = 1;
for (OrderInsure e : insureList) {
if("insure".equals(e.getSource())){
HSSFRow row = sheet1.createRow(sheet1Count++);
row.createCell(0).setCellValue(e.getInsureId());
row.createCell(1).setCellValue(e.getOrderTime());
row.createCell(2).setCellValue(e.getStoreName());
row.createCell(3).setCellValue(e.getProdName());
row.createCell(4).setCellValue(e.getProductType());
row.createCell(5).setCellValue(Long.valueOf(e.getQuantity()));
row.createCell(6).setCellValue(e.getVipProd());
}
if("brithday".equals(e.getSource())){
HSSFRow row = sheet2.createRow(sheet2Count++);
row.createCell(0).setCellValue(e.getInsureId());
row.createCell(1).setCellValue(e.getOrderTime());
row.createCell(2).setCellValue(e.getStoreName());
row.createCell(3).setCellValue(e.getProdName());
row.createCell(4).setCellValue(Long.valueOf(e.getQuantity()));
row.createCell(5).setCellValue(e.getVipProd());
}
}
}
}