spring mvc 导出 excel

// js 触发导出 excel 方法  导出当前页的数据 含有条件查询的结果
// js 框架使用的 是 easyui 
function doExport(){
 
     
    var optins = $("#grid").datagrid("getPager").data("pagination").options;
    var page = optins.pageNumber;
    var rows = optins.pageSize;
    var cpname=$("#cpname_id").val();
    var adname=$("#adname_id").val();
    var start = $("#start_id").val();
    var end = $("#end_id").val();
    $("<form>").attr({
        "action":"${ctx}/rest/trafficbill/doexporttrafficaccount",
        "method":"POST"
    }).append("<input type='text' name='page' value='"+page+"'/>")
      .append("<input type='text' name='cpname' value='"+cpname+"'/>")
      .append("<input type='text' name='adname' value='"+adname+"'/>")
      .append("<input type='text' name='startTime' value='"+start+"'/>")
      .append("<input type='text' name='endTime' value='"+end+"'/>")
    .append("<input type='text' name='rows' value='"+rows+"'/>").submit();
}
 
 
 
 
 
// poi 操作 excel 的 工具类
 
 
package com.manage.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.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.web.servlet.view.document.AbstractExcelView;
 
import com.pojo.subaccount.PageData;
 
/**
 * 
 * @author jemond
 *
 * 2015年6月26日上午9:42:48
 */
public class ObjectExcelView extends AbstractExcelView{
 
    @SuppressWarnings("deprecation")
    @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");
        HSSFSheet sheet;
        HSSFCell cell;
        response.setContentType("application/octet-stream");
        response.setHeader("Content-Disposition", "attachment;filename="+filename+".xls");
        sheet = workbook.createSheet("sheet1");
         
        @SuppressWarnings("unchecked")
        List<String> titles = (List<String>) model.get("titles");
        int len = titles.size();
        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)11);
        headerStyle.setFont(headerFont);
        short width = 20,height=25*20;
        sheet.setDefaultColumnWidth(width);
        for(int i=0; i<len; i++){ //设置标题
            String title = titles.get(i);
            cell = getCell(sheet, 0, i);
            cell.setCellStyle(headerStyle);
            setText(cell,title);
        }
        sheet.getRow(0).setHeight(height);
         
        HSSFCellStyle contentStyle = workbook.createCellStyle(); //内容样式
        contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        @SuppressWarnings("unchecked")
        List<PageData> varList = (List<PageData>) model.get("varList");
        int varCount = varList.size();
        for(int i=0; i<varCount; i++){
            PageData vpd = varList.get(i);
            for(int j=0;j<len;j++){
                String varstr = vpd.getString("var"+(j+1)) != null ? vpd.getString("var"+(j+1)) : "";
                cell = getCell(sheet, i+1, j);
                cell.setCellStyle(contentStyle);
                setText(cell,varstr);
            }
             
        }
         
    }
 
 
     
}
 
 
 
 
 
// controller 导出excel 
 
 
 @RequestMapping("/doexporttrafficaccount")
    public ModelAndView doExportTrafficAccount(@RequestParam(value = "page", defaultValue = "1") Integer page,
            @RequestParam(value = "rows", defaultValue = "30") Integer rows,String cpname,String adname,String startTime,String endTime) throws Exception {
 
        // 构造一个查询条件
        TvgameBillingTraffic tbt = new TvgameBillingTraffic();
        
        if(StringUtils.isNotBlank(cpname)){
           tbt.setCpname(cpname);
        }
        if(StringUtils.isNotBlank(adname)){
           tbt.setAdname(adname);
        }
        if(StringUtils.isNotBlank(startTime)){
           tbt.setStartTime(DateFormatUtil.dateToStartTime(startTime));
        }
        if(StringUtils.isNotBlank(endTime)){
           tbt.setEndTime(DateFormatUtil.dateToEndTime(endTime));
            
        }
        // 得到 当前页的所有数据
         EasyUIResult account = this.trafficAccountService.queryTrafficAccount(page, rows, tbt);
 
        @SuppressWarnings({"unchecked"})
        List<TvgameBillingTraffic> userList = (List<TvgameBillingTraffic>)account.getRows();
 
        // 封装 excel 参数
        try {
 
            // 参数 MAP
            Map<String, Object> dataMap = new HashMap<String, Object>();
            
            // 设置标题
            List<String> titles = new ArrayList<String>();
            titles.add("账务编号"); // 1
            titles.add("CP名称"); // 2
            titles.add("计费策略"); // 3
            titles.add("流量名称"); // 4
            titles.add("计价金额"); // 5
            titles.add("流量流水"); // 6
            titles.add("创建时间"); // 7
            titles.add("结算状态"); // 8
            dataMap.put("titles", titles);
            
            // 存放内容的集合
            List<PageData> varList = new ArrayList<PageData>();
 
            for (TvgameBillingTraffic p : userList) {
                PageData vpd = new PageData();
                vpd.put("var1", String.valueOf(p.getId())); // 1
                vpd.put("var2", p.getCpname()); // 2
                vpd.put("var3", p.getChargingname()); // 3
                vpd.put("var4", p.getAdname()); // 4
                vpd.put("var5", String.valueOf(p.getPrice())); // 5
                vpd.put("var6", p.getTraffic()); // 6
                 
                // 格式化时间  存在数据库是 毫秒 值
                long createTime = p.getCreateTime();
                String data = DateFormatUtils.getData(createTime);
                vpd.put("var7", data); // 7
                 
                 
             // 格式化结算状态 存在数据 是 0 或1
                String settString = "" ;
                byte settlement = p.getSettlement();
                if(settlement == 0){
                    settString = "未结算";
                }else if (settlement == 1) {
                     
                    settString = "已经结算";
                }else{
                    settString = "未知状态";
                }
                vpd.put("var8", settString); // 8
                varList.add(vpd);
            }
 
            dataMap.put("varList", varList);
             
            // 执行excel操作  是一个视图 
            ObjectExcelView erv = new ObjectExcelView(); 
            ModelAndView mv = new ModelAndView(erv, dataMap);
            return mv;
        } catch (Exception e) {
            LOGGER.error("错误");
            return null;
        }
 
    }
     
    
 
 
 
// 操作时间的 工具类
 
/**
 * 
 * @author jemond
 *
 * 2015年6月20日上午9:42:48
 */
package com.common.util;
 
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
 
 
 
public class DateFormatUtils {
 
    public static int getSecond(){
        String secondStr = String.valueOf(System.currentTimeMillis());
        String second = secondStr.substring(0,secondStr.length()-3);
        return Integer.valueOf(second);
    }
     
    public static String getData(long time){
        time = time * 1000 ;
        SimpleDateFormat sdf= new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
        String format = sdf.format(new Date(time));
        return format;
    }
     
    public static String getDateShout(long time){
         time = time * 1000 ;
            SimpleDateFormat sdf= new SimpleDateFormat("yyyy-MM-dd");  
            String format = sdf.format(new Date(time));
            return format;
    }
     
    public static long getDateByLong(){
         
        SimpleDateFormat sdf= new SimpleDateFormat("yyyy-MM-dd");  
        long time=0;
        try {
             
            Calendar c = Calendar.getInstance();  
            c.add(Calendar.DATE, - 7);  
            Date monday = c.getTime();
            String preMonday = sdf.format(monday);
             
            time=sdf.parse(preMonday).getTime()/1000;
              
              
        } catch (ParseException e) {
             
            e.printStackTrace();
        }
        return time;
         
    }
     
     
     
     
     
    /*public static void main(String[] args) {
        Float p = 565654656565656645645645454564.2265665656f;
            System.out.println(getData(1435547609)+"=="+p);
             
        }*/
}
  
 
 
 
// spring mvc 的 部分 配置 
<!-- 配置视图解析器 -->
    <bean
        class="org.springframework.web.servlet.view.InternalResourceViewResolver">
        <!-- 前缀 -->
        <property name="prefix" value="/WEB-INF/views/" />
        <!-- 后缀 -->
        <property name="suffix" value=".jsp" />
        <!-- 查找顺序 -->
        <!-- <property name="order" value="2"/> -->
    </bean>
    
    
  <!-- 定义Excel视图对象 -->
 <!--    <bean name="utilView" class="com.manage.util.ObjectExcelView "/>
     
    定义，根据bean的名称查找视图
    <bean class="org.springframework.web.servlet.view.BeanNameViewResolver">
        查找顺序
        <property name="order" value="1"/>
    </bean> -->