基于ssm框架导出excel

            jsp：一定用window.location.href=url 才会使用response输出流下载

function goView(ID,ORDERZT,YCLX){
	window.location.href='http://127.0.0.1:8080/sfc/excel.do?ID='+ID+'&ORDERZT='+ORDERZT+'&YCLX='+YCLX;
	}

 /** export excel

* @return
* @throws Exception
* @author fengbin
*/
@RequestMapping(value="/excel")
public ModelAndView excel()throws Exception{
logBefore(logger, Jurisdiction.getUsername()+"导出sfc到excel");
if(!Jurisdiction.buttonJurisdiction(menuUrl, "cha")){return null;}
Map<String,Object> dataMap = new HashMap<String,Object>();
PageData pd = new PageData();

PageData pd1 = new PageData();

pd = this.getPageData();//封装的取页面参数
pd1 = pd;
try {
pd =sfcService.findFaById(pd); //根据ID读取发车
pd1 = sfcService.findShById(pd1); //根据ID读取收车
} catch (Exception e) {
e.printStackTrace();
}
//防止没有发车时打印excel报空指针,没有数据打印表格样式，没有数据
PageData flashpd = new PageData();
flashpd.put("result", "结果为空");
if(pd==null){
pd=flashpd;
}
if(pd1==null){
pd1 =flashpd;
}
dataMap.put("pd", pd);
dataMap.put("pd1", pd1);
ObjectExcelView2 ob2 = new ObjectExcelView2();
ModelAndView mv = new ModelAndView(ob2,dataMap);
return mv;

}

绑定excel 参数

package com.easyeon.util;


import java.util.Date;
import java.util.List;
import java.util.Map;


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


import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFCellUtil;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.web.servlet.view.document.AbstractExcelView;


/**收发车复杂excel样式生成
 * @author fengbin
 *
 */
public class ObjectExcelView2 extends AbstractExcelView {


@Override
protected void buildExcelDocument(Map<String, Object> model,
HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response)
throws Exception {
Date date = new Date();
String filename = Tools.date2Str(date, "yyyyMMddHHmmss");
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment;filename="+filename+".xls");
PageData pd =  (PageData) model.get("pd");//发车数据
PageData pd1 =  (PageData) model.get("pd1");//收车数据
HSSFSheet sheet = workbook.createSheet("发车验车单");
HSSFSheet sheet2 = workbook.createSheet("收车验车单");
HSSFCellStyle headerStyle = workbook.createCellStyle(); //标题样式
headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
HSSFFont headerFont = workbook.createFont(); //标题字体
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerFont.setFontHeightInPoints((short)25);
headerStyle.setFont(headerFont);
HSSFCellStyle border = workbook.createCellStyle();
//设置边框
border.setBorderBottom((short) 1);
border.setBorderLeft((short) 1);
border.setBorderRight((short) 1);
border.setBorderTop((short) 1);
border.setAlignment(HSSFCellStyle.ALIGN_CENTER);
String title="发车验车单";
if("收车验车单".equals(title)){
pd1.put("FCGLS", pd.getString("FCGLS"));
String FCCYLNAME =pd1.getString("FCCYLNAME");
pd1.put("FCCYLNAME", pd.getString("FCCYLNAME"));
pd1.put("FCSJ", pd.getString("FCSJ"));
pd = pd1;
}else{
pd.put("SCGLS", pd1.getString("SCGLS"));
String SCCYLNAME =pd1.getString("SCCYLNAME");
pd.put("SCCYLNAME", pd1.getString("SCCYLNAME"));
pd.put("SCSJ", pd1.getString("SCSJ"));
}
sheet = base(sheet, headerStyle,"发车验车单", border,pd);
title="收车验车单";
if("收车验车单".equals(title)){
pd1.put("FCGLS", pd.getString("FCGLS"));
pd1.put("FCCYLNAME", pd.getString("FCCYLNAME"));
pd1.put("FCSJ", pd.getString("FCSJ"));
pd = pd1;
}else{
pd.put("SCGLS", pd1.getString("SCGLS"));
pd.put("SCCYLNAME", pd1.getString("SCCYLNAME"));
pd.put("SCSJ", pd1.getString("SCSJ"));
}
sheet2 = base(sheet2, headerStyle,"收车验车单", border,pd);
for (Row row : sheet) {  
     //遍历一行中的所有的单元格  
     for (Cell c : row) {  
       // 你需要实现功能的代码  
    if(c.equals(sheet.getRow(0).getCell(0))){
continue;
}
    c.setCellStyle(border);
     }      
}
for (Row row : sheet2) {  
//遍历一行中的所有的单元格  
for (Cell c : row) {  
// 你需要实现功能的代码  
if(c.equals(sheet2.getRow(0).getCell(0))){
continue;
}
c.setCellStyle(border);
}      
}
}


/**创建表格
* @param sheet
* @param headerStyle
* @param title 只能为：收车验车单 或 发车验车单
* @return
*/
public HSSFSheet base(HSSFSheet sheet,HSSFCellStyle headerStyle,String title,HSSFCellStyle border,PageData pd){
short width = 9,height=25*20;
sheet.setDefaultColumnWidth(width);
sheet.setDefaultRowHeight(height);
HSSFRow row0 = sheet.createRow(0);
HSSFCell cell1 = row0.createCell(0);
cell1.setCellStyle(headerStyle);
row0.setHeight(height);
row0.setRowStyle(headerStyle);
cell1.setCellValue(title);
HSSFRow row1 = sheet.createRow(1);
row1.createCell(0).setCellValue("合同号");
row1.createCell(1).setCellValue(pd.getString("NUMBER"));
row1.createCell(2).setCellValue("车辆号");
row1.createCell(3).setCellValue(pd.getString("CARNO"));
row1.createCell(4).setCellValue("车型");
row1.createCell(5).setCellValue(pd.getString("CARNAME"));
row1.createCell(6).setCellValue("颜色");
row1.createCell(7).setCellValue(pd.getString("COLOR"));
row1.setHeight(height);
HSSFRow row2 = sheet.createRow(2);
row2.createCell(0).setCellValue("承租方");
String str = new String();
if(!"".equals(pd.getString("JT"))&&pd.get("JT")!=null){str =pd.getString("JT")+"/";}
if(!"".equals(pd.getString("QY"))&&pd.get("QY")!=null){str =str+pd.getString("JT")+"/";}
if(!"".equals(pd.getString("BM"))&&pd.get("BM")!=null){str =str+pd.getString("JT")+"/";}
if(!"".equals(pd.getString("DCR"))&&pd.get("DCR")!=null){str =str+pd.getString("JT");}
if(str==null){
row2.createCell(1).setCellValue("");
}
row2.createCell(1).setCellValue(str);
row2.createCell(2).setCellValue("");
row2.createCell(3).setCellValue("");
row2.createCell(4).setCellValue("电话");
row2.createCell(5).setCellValue(pd.getString("DCDH"));
row2.createCell(6).setCellValue("");
row2.createCell(7).setCellValue("");
row2.setHeight(height);
HSSFRow row3 = sheet.createRow(3);
row3.createCell(0).setCellValue("取车地方");
row3.createCell(1).setCellValue(pd.getString("QCDZ"));
row3.createCell(2).setCellValue("");
row3.createCell(3).setCellValue("");
row3.createCell(4).setCellValue("还车地方");
row3.createCell(5).setCellValue(pd.getString("HCDZ"));
row3.createCell(6).setCellValue("");
row3.createCell(7).setCellValue("");
row3.setHeight(height);
HSSFRow row4 = sheet.createRow(4);
row4.createCell(0).setCellValue("发车公里数");
row4.createCell(1).setCellValue(pd.getString("FCGLS"));
row4.createCell(2).setCellValue("发车存油量");
row4.createCell(3).setCellValue(pd.getString("FCCYLNAME"));
row4.createCell(4).setCellValue("发车时间");
row4.createCell(5).setCellValue(pd.getString("FCSJ"));
row4.createCell(6).setCellValue("");
row4.createCell(7).setCellValue("");
row4.setHeight(height);
HSSFRow row5 = sheet.createRow(5);
row5.createCell(0).setCellValue("收车公里数");
row5.createCell(1).setCellValue(pd.getString("SCGLS"));
row5.createCell(2).setCellValue("收车存油量");
row5.createCell(3).setCellValue(pd.getString("SCCYLNAME"));
row5.createCell(4).setCellValue("收车时间");
row5.createCell(5).setCellValue(pd.getString("SCSJ"));
row5.createCell(6).setCellValue("");
row5.createCell(7).setCellValue("");
row5.setHeight(height);
HSSFRow row6 = sheet.createRow(6);
HSSFCell rc6 = row6.createCell(0);
if("收车验车单".equals(title)){
rc6.setCellValue("收车时");
}else{
rc6.setCellValue("发车时");
}
rc6.setCellStyle(headerStyle);
row6.setHeight(height);
HSSFRow row7 = sheet.createRow(7);
row7.createCell(0).setCellValue("证件");
row7.setHeight(height);
HSSFRow row8 = sheet.createRow(8);
row8.createCell(0).setCellValue("行驶证");
if("1".equals(pd.getString("XSZ"))){
row8.createCell(1).setCellValue("√");
}else{
row8.createCell(1).setCellValue("");
}
row8.createCell(2).setCellValue("养路费");
if("1".equals(pd.getString("YLF"))){
row8.createCell(3).setCellValue("√");
}else{
row8.createCell(3).setCellValue("");
}
row8.createCell(4).setCellValue("保险卡");
if("1".equals(pd.getString("YBK"))){
row8.createCell(5).setCellValue("√");
}else{
row8.createCell(5).setCellValue("");
}
row8.createCell(6).setCellValue("地图");
if("1".equals(pd.getString("DT"))){
row8.createCell(7).setCellValue("√");
}else{
row8.createCell(7).setCellValue("");
}
row8.setHeight(height);
HSSFRow row9 = sheet.createRow(9);
row9.createCell(0).setCellValue("随行小包");
if("1".equals(pd.getString("SXXB"))){
row9.createCell(1).setCellValue("√");
}else{
row9.createCell(1).setCellValue("");
}
row9.createCell(2).setCellValue("维修手册");
if("1".equals(pd.getString("WXSC"))){
row9.createCell(3).setCellValue("√");
}else{
row9.createCell(3).setCellValue("");
}
row9.createCell(4).setCellValue("车内清洁");
if("1".equals(pd.getString("CNQJ"))){
row9.createCell(5).setCellValue("√");
}else{
row9.createCell(5).setCellValue("");
}
row9.createCell(6).setCellValue("");
row9.createCell(7).setCellValue("");
row9.setHeight(height);
HSSFRow row10 = sheet.createRow(10);
row10.createCell(0).setCellValue("工具");
row10.setHeight(height);
HSSFRow row11 = sheet.createRow(11);
row11.setHeight(height);
row11.createCell(0).setCellValue("千斤顶");
if("1".equals(pd.getString("QJD"))){
row11.createCell(1).setCellValue("√");
}else{
row11.createCell(1).setCellValue("");
}
row11.createCell(2).setCellValue("轮胎扳手");
if("1".equals(pd.getString("LTBS"))){
row11.createCell(3).setCellValue("√");
}else{
row11.createCell(3).setCellValue("");
}

row11.createCell(4).setCellValue("灭火器");
if("1".equals(pd.getString("MHQ"))){
row11.createCell(5).setCellValue("√");
}else{
row11.createCell(5).setCellValue("");
}
row11.createCell(6).setCellValue("");
row11.createCell(7).setCellValue("");
row11.setHeight(height);
HSSFRow row12 = sheet.createRow(12);
row12.createCell(0).setCellValue("配置");
row12.setHeight(height);
HSSFRow row13 = sheet.createRow(13);
row13.setHeight(height);
row13.createCell(0).setCellValue("仪表");
if("1".equals(pd.getString("YB"))){
row13.createCell(1).setCellValue("√");
}else{
row13.createCell(1).setCellValue("");
}

row13.createCell(2).setCellValue("音响");
if("1".equals(pd.getString("YX"))){
row13.createCell(3).setCellValue("√");
}else{
row13.createCell(3).setCellValue("");
}
row13.createCell(4).setCellValue("空调");
if("1".equals(pd.getString("KT"))){
row13.createCell(5).setCellValue("√");
}else{
row13.createCell(5).setCellValue("");
}

row13.createCell(6).setCellValue("遥控器");
if("1".equals(pd.getString("YKQ"))){
row13.createCell(7).setCellValue("√");
}else{
row13.createCell(7).setCellValue("");
}
HSSFRow row14 = sheet.createRow(14);
row14.setHeight(height);
row14.createCell(0).setCellValue("防盗锁");
if("1".equals(pd.getString("FDS"))){
row14.createCell(1).setCellValue("√");
}else{
row14.createCell(1).setCellValue("");
}

row14.createCell(2).setCellValue("防盗器");
if("1".equals(pd.getString("FDQ"))){
row14.createCell(3).setCellValue("√");
}else{
row14.createCell(3).setCellValue("");
}
row14.createCell(4).setCellValue("点烟器");
if("1".equals(pd.getString("DYQ"))){
row14.createCell(5).setCellValue("√");
}else{
row14.createCell(5).setCellValue("");
}
row14.createCell(6).setCellValue("备胎");
if("1".equals(pd.getString("BT"))){
row14.createCell(7).setCellValue("√");
}else{
row14.createCell(7).setCellValue("");
}
HSSFRow row15 = sheet.createRow(15);
row15.setHeight(height);
row15.createCell(0).setCellValue("天线");
if("1".equals(pd.getString("TX"))){
row15.createCell(1).setCellValue("√");
}else{
row15.createCell(1).setCellValue("");
}
row15.createCell(2).setCellValue("车内拉手");
if("1".equals(pd.getString("CNLS"))){
row15.createCell(3).setCellValue("√");
}else{
row15.createCell(3).setCellValue("");
}
row15.createCell(4).setCellValue("雨刷器");
if("1".equals(pd.getString("YSQ"))){
row15.createCell(5).setCellValue("√");
}else{
row15.createCell(5).setCellValue("");
}
row15.createCell(6).setCellValue("车载导航");
if("1".equals(pd.getString("CZDH"))){
row15.createCell(7).setCellValue("√");
}else{
row15.createCell(7).setCellValue("");
}
HSSFRow row16 = sheet.createRow(16);
row16.setHeight(height);
row16.createCell(0).setCellValue("电动窗");
if("1".equals(pd.getString("DDC"))){
row16.createCell(1).setCellValue("√");
}else{
row16.createCell(1).setCellValue("");
}
row16.createCell(2).setCellValue("座套");
if("1".equals(pd.getString("ZT"))){
row16.createCell(3).setCellValue("√");
}else{
row16.createCell(3).setCellValue("");
}
row16.createCell(4).setCellValue("座椅");
if("1".equals(pd.getString("ZY"))){
row16.createCell(5).setCellValue("√");
}else{
row16.createCell(5).setCellValue("");
}
row16.createCell(6).setCellValue("");
row16.createCell(7).setCellValue("");
HSSFRow row17 = sheet.createRow(17);
row17.createCell(0).setCellValue("车辆基本状况");
row17.setHeight(height);
HSSFRow row18 = sheet.createRow(18);
row18.setHeight(height);
row18.createCell(0).setCellValue("灯光");
if("1".equals(pd.getString("DG"))){
row18.createCell(1).setCellValue("√");
}else{
row18.createCell(1).setCellValue("");
}
row18.createCell(2).setCellValue("刹车");
if("1".equals(pd.getString("SC"))){
row18.createCell(3).setCellValue("√");
}else{
row18.createCell(3).setCellValue("");
}
row18.createCell(4).setCellValue("手刹");
if("1".equals(pd.getString("SS"))){
row18.createCell(5).setCellValue("√");
}else{
row18.createCell(5).setCellValue("");
}
row18.createCell(6).setCellValue("蓄电池");
if("1".equals(pd.getString("XDC"))){
row18.createCell(7).setCellValue("√");
}else{
row18.createCell(7).setCellValue("");
}

HSSFRow row19 = sheet.createRow(19);
row19.setHeight(height);
row19.createCell(0).setCellValue("油箱盖");
if("1".equals(pd.getString("YXG"))){
row19.createCell(1).setCellValue("√");
}else{
row19.createCell(1).setCellValue("");
}
row19.createCell(2).setCellValue("水箱盖");
if("1".equals(pd.getString("SXG"))){
row19.createCell(3).setCellValue("√");
}else{
row19.createCell(3).setCellValue("");
}
row19.createCell(4).setCellValue("机油");
if("1".equals(pd.getString("JY"))){
row19.createCell(5).setCellValue("√");
}else{
row19.createCell(5).setCellValue("");
}
row19.createCell(6).setCellValue("防盗液");
if("1".equals(pd.getString("FDY"))){
row19.createCell(7).setCellValue("√");
}else{
row19.createCell(7).setCellValue("");
}

HSSFRow row20 = sheet.createRow(20);
row20.setHeight(height);
row20.createCell(0).setCellValue("转向机");
if("1".equals(pd.getString("ZXJ"))){
row20.createCell(1).setCellValue("√");
}else{
row20.createCell(1).setCellValue("");
}
row20.createCell(2).setCellValue("");
row20.createCell(3).setCellValue("");
row20.createCell(4).setCellValue("");
row20.createCell(5).setCellValue("");
row20.createCell(6).setCellValue("");
row20.createCell(7).setCellValue("");
HSSFRow row21 = sheet.createRow(21);
row21.setHeight(height);
row21.createCell(0).setCellValue("备注");
HSSFRow row22 = sheet.createRow(22);
row22.setHeight(height);
HSSFRow row23 = sheet.createRow(23);
row23.setHeight(height);
//合并单元格
Region region1 = new Region(2, (short)1, 2, (short)3); 
Region region2 = new Region(2, (short)5, 2, (short)7); 
Region region3 = new Region(3, (short)1, 3, (short)3); 
Region region4 = new Region(3, (short)5, 3, (short)7); 
Region region5 = new Region(4, (short)5, 4, (short)7); 
Region region6 = new Region(5, (short)5, 5, (short)7); 
Region region7 = new Region(7, (short)0, 7, (short)7); 
Region region8 = new Region(10, (short)0, 10, (short)7);
Region region9 = new Region(0, (short)0, 0, (short)7);
Region region10 = new Region(12, (short)0, 12, (short)7);
Region region11 = new Region(17, (short)0, 17, (short)7);
Region region12 = new Region(21, (short)0, 21, (short)7);
Region region13 = new Region(22, (short)0, 23, (short)7);
Region region14 = new Region(6, (short)0, 6, (short)7); 
sheet.addMergedRegion(region1); 
sheet.addMergedRegion(region2); 
sheet.addMergedRegion(region3); 
sheet.addMergedRegion(region4); 
sheet.addMergedRegion(region5); 
sheet.addMergedRegion(region6); 
sheet.addMergedRegion(region7); 
sheet.addMergedRegion(region8); 
sheet.addMergedRegion(region9); 
sheet.addMergedRegion(region10); 
sheet.addMergedRegion(region11); 
sheet.addMergedRegion(region12); 
sheet.addMergedRegion(region13); 
sheet.addMergedRegion(region14); 
setRegionStyle(sheet, region1, border);
setRegionStyle(sheet, region2, border);
setRegionStyle(sheet, region3, border);
setRegionStyle(sheet, region4, border);
setRegionStyle(sheet, region5, border);
setRegionStyle(sheet, region6, border);
setRegionStyle(sheet, region7, border);
setRegionStyle(sheet, region8, border);
setRegionStyle(sheet, region9, border);
setRegionStyle(sheet, region10, border);
setRegionStyle(sheet, region11, border);
setRegionStyle(sheet, region12, border);
setRegionStyle(sheet, region13, border);
setRegionStyle(sheet, region14, border);
return sheet;
}

/**解决合并单元的样式问题
* @param sheet
* @param region
* @param cs
*/
public static void setRegionStyle(HSSFSheet sheet, Region region,
            HSSFCellStyle cs) {
        for (int i = region.getRowFrom(); i <= region.getRowTo(); i++) {
            HSSFRow row = HSSFCellUtil.getRow(i, sheet);
            for (int j = region.getColumnFrom(); j <= region.getColumnTo(); j++) {
                HSSFCell cell = HSSFCellUtil.getCell(row, (short) j);
                cell.setCellStyle(cs);


                        }
        }
    }

/**解决合并单元的样式问题
* @param sheet
* @param region
* @param cs
*/
public static void setRegionStyle(HSSFSheet sheet, CellRangeAddress region,
            HSSFCellStyle cs) {


        for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {


            HSSFRow row = sheet.getRow(i);
            if (row == null)
                row = sheet.createRow(i);
            for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
                HSSFCell cell = row.getCell(j);
                if (cell == null) {
                    cell = row.createCell(j);
                    cell.setCellValue("");
                }
                cell.setCellStyle(cs);


            }
        }
    }


}

效果截图

发车验车单
合同号	10769	车辆号		车型	A6L	颜色
承租方	中信集团/中信集团/中信集团/中信集团			电话	13811031203
取车地方	丰台区卢沟桥乡808号			还车地方	北京
发车公里数	100	发车存油量	1/2箱油	发车时间	2017-12-18 17:17:24
收车公里数	120	收车存油量	1/4箱油	收车时间	2017-12-19 11:30:07
发车时
证件
行驶证	√	养路费	√	保险卡	√	地图	√
随行小包	√	维修手册	√	车内清洁	√
工具
千斤顶	√	轮胎扳手	√	灭火器	√
配置
仪表	√	音响	√	空调	√	遥控器	√
防盗锁	√	防盗器	√	点烟器	√	备胎	√
天线	√	车内拉手	√	雨刷器	√	车载导航	√
电动窗	√	座套	√	座椅	√
车辆基本状况
灯光	√	刹车	√	手刹	√	蓄电池	√
油箱盖	√	水箱盖	√	机油	√	防盗液	√
转向机	√
备注