框架导出excel"/>
基于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 | ||
发车时 | |||||||
证件 | |||||||
行驶证 | √ | 养路费 | √ | 保险卡 | √ | 地图 | √ |
随行小包 | √ | 维修手册 | √ | 车内清洁 | √ | ||
工具 | |||||||
千斤顶 | √ | 轮胎扳手 | √ | 灭火器 | √ | ||
配置 | |||||||
仪表 | √ | 音响 | √ | 空调 | √ | 遥控器 | √ |
防盗锁 | √ | 防盗器 | √ | 点烟器 | √ | 备胎 | √ |
天线 | √ | 车内拉手 | √ | 雨刷器 | √ | 车载导航 | √ |
电动窗 | √ | 座套 | √ | 座椅 | √ | ||
车辆基本状况 | |||||||
灯光 | √ | 刹车 | √ | 手刹 | √ | 蓄电池 | √ |
油箱盖 | √ | 水箱盖 | √ | 机油 | √ | 防盗液 | √ |
转向机 | √ | ||||||
备注 |
更多推荐
基于ssm框架导出excel
发布评论