在SSM下实现导出Excel

首先准备jar包

poi、poi-ooxml、poi-ooxml-schemas

 前端页面

<div>
            <button onclick="d()">导出</button>

</div> 

<script type="text/javascript">
          function d(){
              window.location.href="${pageContext.request.contextPath}/sys/customerMassWebController/excel";
          }
      </script>

后台

Controller层

@RequestMapping("/excel")
	public void excel(HttpServletResponse response )throws IOException {
		 response.setCharacterEncoding("UTF-8");  
		 List<CustomerMass> list = customerMassService.queryExcelInfo();
	        System.out.println(list);
	      //创建excel文件
	        HSSFWorkbook wb = new HSSFWorkbook();
	        //创建sheet页
	        HSSFSheet sheet = wb.createSheet("客户信息表");
	        //创建标题行
	        HSSFRow titleRow = sheet.createRow(0);
	        titleRow.createCell(0).setCellValue("日期");
	        titleRow.createCell(1).setCellValue("客户名称");
	        titleRow.createCell(2).setCellValue("类型");
	        titleRow.createCell(3).setCellValue("状态");
	        titleRow.createCell(4).setCellValue("客户阶段");
	        titleRow.createCell(5).setCellValue("消费总额");
	        titleRow.createCell(6).setCellValue("欠款金额");
	        titleRow.createCell(7).setCellValue("维护费用");
	        titleRow.createCell(8).setCellValue("业务");
//	        titleRow.createCell(9).setCellValue("创建时间 ");
//	        titleRow.createCell(10).setCellValue("更新时间");
	        //遍历将数据放到excel列中
            for (CustomerMass user : list) {
                HSSFRow dataRow = sheet.createRow(sheet.getLastRowNum()+1);
                dataRow.createCell(0).setCellValue(user.getCustomdate());
                dataRow.createCell(1).setCellValue(user.getCustomname());
                dataRow.createCell(2).setCellValue(user.getCustomtype());
                dataRow.createCell(3).setCellValue(user.getCustomstatus());
                dataRow.createCell(4).setCellValue(user.getCustomstage());
                dataRow.createCell(5).setCellValue(user.getTotalconsumption());
                dataRow.createCell(6).setCellValue(user.getAmountsowed());
                dataRow.createCell(7).setCellValue(user.getMaintenancecosts());
                dataRow.createCell(8).setCellValue(user.getBusiness());
//                dataRow.createCell(9).setCellValue(user.getCreateDateStart());
//                dataRow.createCell(10).setCellValue(user.getCreateDateEnd());
             
            }
            /*   // 设置下载时客户端Excel的名称  
            String filename =new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()) + ".xls";  
               response.setContentType("application/vnd.ms-excel");  
               response.setHeader("Content-disposition", "attachment;filename=" + filename);  */

           // 设置下载时客户端Excel的名称   （上面注释的改进版本，上面的中文不支持）
                   response.setContentType("application/octet-stream;charset=utf-8");
                   response.setHeader("Content-Disposition", "attachment;filename="
                           + new String("客户名单".getBytes(),"iso-8859-1") + ".xls");


               OutputStream ouputStream = response.getOutputStream();  
               wb.write(ouputStream);  
               ouputStream.flush();  
               ouputStream.close();
	   
	        
	}

 实体类

package com.java.entity;

import com.fasterxml.jackson.annotation.JsonFormat;
import org.springframework.format.annotation.DateTimeFormat;

import com.java.sys.basic.entity.BaseEntity;
import java.util.Date;

public class CustomerMass extends BaseEntity {
	
	private static final long serialVersionUID = 1L;
	
	private Date customdate;	//日期
	private String customname;	//客户名称
	private String customtype;	//类型
	private String customstatus;	//状态
	private String customstage;	//客户阶段
	private String totalconsumption;	//消费总额
	private String amountsowed;	//欠款金额
	private String maintenancecosts;	//
	private String business;	//业务
	
	public CustomerMass() {
		super();
	}
	
    
	@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    public Date getCustomdate() {
    	return customdate;
    }
    public void setCustomdate(Date customdate) {
    	this.customdate = customdate;
    }
    
    public String getCustomname() {
    	return customname;
    }
    public void setCustomname(String customname) {
    	this.customname = customname;
    }
    
    public String getCustomtype() {
    	return customtype;
    }
    public void setCustomtype(String customtype) {
    	this.customtype = customtype;
    }
    
    public String getCustomstatus() {
    	return customstatus;
    }
    public void setCustomstatus(String customstatus) {
    	this.customstatus = customstatus;
    }
    
    public String getCustomstage() {
    	return customstage;
    }
    public void setCustomstage(String customstage) {
    	this.customstage = customstage;
    }
    
    public String getTotalconsumption() {
    	return totalconsumption;
    }
    public void setTotalconsumption(String totalconsumption) {
    	this.totalconsumption = totalconsumption;
    }
    
    public String getAmountsowed() {
    	return amountsowed;
    }
    public void setAmountsowed(String amountsowed) {
    	this.amountsowed = amountsowed;
    }
    
    public String getMaintenancecosts() {
    	return maintenancecosts;
    }
    public void setMaintenancecosts(String maintenancecosts) {
    	this.maintenancecosts = maintenancecosts;
    }
    
    public String getBusiness() {
    	return business;
    }
    public void setBusiness(String business) {
    	this.business = business;
    }
	
}

Dao层

@Repository
public interface CustomerMassDao {
	
	List<CustomerMass> queryExcelInfo();
}

mapper.xml

<select id="queryExcelInfo" resultType="CustomerMass">
		select * from customer_mass 
	</select>

service

public List<CustomerMass> queryExcelInfo(){
		return customerMassDao.queryExcelInfo();
	}

展示