spring boot导出数据到Excel表格

所需依赖：

<!-- Excel导入 -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.6</version>
		</dependency>

		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.6</version>
		</dependency>
        <dependency>
            <groupId>org.jetbrains</groupId>
            <artifactId>annotations</artifactId>
            <version>RELEASE</version>
            <scope>compile</scope>
        </dependency>

实体类：CustomerInfo

package com.nz.entity;

import java.util.Date;

/**
 * @Author xxs
 * @Date 2020/4/16 17:29
 */
public class CustomerInfo {
    private Integer id;
    private String customerName;
    private String customerPhone;
    private String text;
    private String type;
    private Date addTime;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getCustomerName() {
        return customerName;
    }

    public void setCustomerName(String customerName) {
        this.customerName = customerName;
    }

    public String getCustomerPhone() {
        return customerPhone;
    }

    public void setCustomerPhone(String customerPhone) {
        this.customerPhone = customerPhone;
    }

    public String getText() {
        return text;
    }

    public void setText(String text) {
        this.text = text;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    public Date getAddTime() {
        return addTime;
    }

    public void setAddTime(Date addTime) {
        this.addTime = addTime;
    }
}

导出表格所要显示的信息实体：downloadProfitStatementExcel

package com.nz.entity;

import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * @Author xxs
 * @Date 2020/4/17 14:58
 */
public class downloadProfitStatementExcel {
    private String customerName;
    private String customerPhone;
    private String text;
    private String addTime;

    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    public downloadProfitStatementExcel(String customerName, String customerPhone,
                                        String text,String addTime) {
        super();
        this.customerName = customerName;
        this.customerPhone = customerPhone;
        this.text = text;
        this.addTime = addTime;
    }
    public static downloadProfitStatementExcel getInit() {
        return new downloadProfitStatementExcel("用户名", "手机号", "信息","提交时间");
    }

    public downloadProfitStatementExcel() {
        super();
    }
    public String getCustomerName() {
        return customerName;
    }
    public void setCustomerName(String customerName) {
        this.customerName = customerName;
    }
    public String getCustomerPhone() {
        return customerPhone;
    }
    public void setCustomerPhone(String customerPhone) {
        this.customerPhone = customerPhone;
    }
    public String getText() {
        return text;
    }
    public void setText(String text) {
        this.text = text;
    }
    public String getAddTime() {
        return addTime;
    }
    public void setAddTime(Date addTime) {
        this.addTime = sdf.format(addTime);
    }

}

CustomerInfoService接口：

package com.nz.service;

import com.nz.entity.CustomerInfo;

import java.util.List;

/**
 * @Author xxs
 * @Date 2020/4/17 10:36
 */
public interface CustomerInfoService {
    public List<CustomerInfo> selectAll();
    public List<CustomerInfo> selectAllByType(String type);
    int insert(CustomerInfo customerInfo);
}

CustomerInfoServiceImpl实现类：

package com.nz.service.Impl;

import com.nz.dao.CustomerInfoDao;
import com.nz.entity.CustomerInfo;
import com.nz.service.CustomerInfoService;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;

/**
 * @Author xxs
 * @Date 2020/4/17 10:39
 */
@Service
public class CustomerInfoServiceImpl implements CustomerInfoService {

    @Resource
    private CustomerInfoDao customerInfoDao;

    @Override
    public List<CustomerInfo> selectAll() {
        return customerInfoDao.selectAll();
    }

    @Override
    public List<CustomerInfo> selectAllByType(String type) {
        return customerInfoDao.selectAllByType(type);
    }

    @Override
    public int insert(CustomerInfo customerInfo) {
        return customerInfoDao.insert(customerInfo);
    }
}

CustomerInfoDao接口：

package com.nz.dao;

import com.nz.entity.CustomerInfo;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * @Author xxs
 * @Date 2020/4/17 10:40
 */
public interface CustomerInfoDao {

    @Select("select * from customer_info")
    @Results(id="resultMap", value={
            @Result(column="id", property="id", id=true),
            @Result(column="customer_name", property="customerName"),
            @Result(column="customer_phone", property="customerPhone"),
            @Result(column="text", property="text"),
            @Result(column="type", property="type"),
            @Result(column="addtime", property="addTime")
    })
    public List<CustomerInfo> selectAll();

    @Select("select * from customer_info where type =#{type}")
    @ResultMap(value={"resultMap"})
    public List<CustomerInfo> selectAllByType(String type);

    @Insert("insert INTO  customer_info (customer_name,customer_phone,text,type,addtime) values(#{customerName},#{customerPhone},#{text},#{type},#{addTime})")
    int insert(CustomerInfo customerInfo);

}

controller层导出方法：

package com.nz.controller;

import com.nz.entity.CustomerInfo;
import com.nz.entity.downloadProfitStatementExcel;
import com.nz.response.ResponseMess;
import com.nz.service.CustomerInfoService;
import com.nz.util.ExcelUtil;
import org.springframework.beans.BeanUtils;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @Author xxs
 * @Date 2020/4/17 12:36
 */
@RestController
@ResponseBody
@RequestMapping("/customerInfo")
public class CustomerInfoController {

    @Resource
    private CustomerInfoService customerInfoService;

   
    /**
     * 导出信息表excel
     * @param type 0-，1-
     * @param response
     * @returns
     */
    @RequestMapping("/downloadCustomerInfo")
    @ResponseBody
    public ResponseMess downloadQueryProfitStatementEXCEL(String type, HttpServletResponse response) {
        if(type.equals("")||type==null) {
            return new ResponseMess("请求参数异常");
        }

        List<CustomerInfo> qs = customerInfoService.selectAllByType(type);
        List<downloadProfitStatementExcel> list = new ArrayList<>(qs.size()+1);
        list.add(downloadProfitStatementExcel.getInit());
        for(CustomerInfo q:qs) {
            downloadProfitStatementExcel d = new downloadProfitStatementExcel();
            try {
                BeanUtils.copyProperties(q, d);
            } catch (Exception e) {
                e.printStackTrace();
            }
            list.add(d);
        }
        ExcelUtil e = new ExcelUtil();

        //设置文件输出类型
        response.setHeader("Content-disposition", "attachment; filename="
                + new Date().getTime() + ".xls");

        String s = "";
        if(type.equals("0")){
            s = "1类型提交资料";
        }else{
            s = "2类型提交资料";
        }
        String[] strs = new String[] {s};
        try {
            e.exportDataToExcel(list, strs, s, response.getOutputStream());
        } catch (IOException e1) {
            e1.printStackTrace();
            return new ResponseMess("失败");
        }
        return new ResponseMess(1,"成功");
    }



}

ExcelUtil 表格工具类：

package com.nz.util;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

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.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.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;




/** 
* @ClassName: ExcelUtil 
* @Description: Excel导入导出工具类
*/
public class ExcelUtil {
    
    /** 
    * @Title: createWorkbook 
    * @Description: 判断excel文件后缀名，生成不同的workbook 
    * @param @param is
    * @param @param excelFileName
    * @param @return
    * @param @throws IOException
    * @return Workbook
    * @throws 
    */
    public Workbook createWorkbook(InputStream is,String excelFileName) throws IOException{
        if (excelFileName.endsWith(".xls")) {
            return new HSSFWorkbook(is);
        }else if (excelFileName.endsWith(".xlsx")) {
            return new XSSFWorkbook(is);
        }
        return null;
    }

    /** 
    * @Title: getSheet 
    * @Description: 根据sheet索引号获取对应的sheet
    * @param @param workbook
    * @param @param sheetIndex
    * @param @return
    * @return Sheet
    * @throws 
    */
    public Sheet getSheet(Workbook workbook,int sheetIndex){
        return workbook.getSheetAt(0);        
    }
    

    /** 
    * @Title: isHasValues 
    * @Description: 判断一个对象所有属性是否有值，如果一个属性有值(分空)，则返回true
    * @param @param object
    * @param @return
    * @return boolean
    * @throws 
    */
    public boolean isHasValues(Object object){
        Field[] fields = object.getClass().getDeclaredFields();
        boolean flag = false;
        for (int i = 0; i < fields.length; i++) {
            String fieldName = fields[i].getName();
            String methodName = "get"+fieldName.substring(0, 1).toUpperCase()+fieldName.substring(1);
            Method getMethod;
            try {
                getMethod = object.getClass().getMethod(methodName);
                Object obj = getMethod.invoke(object);
                if (null != obj || !"".equals(obj)) {
                    flag = true;
                    break;
                }
            } catch (Exception e) {

            }
            
        }
        return flag;
        
    }
    
    public <T> void exportDataToExcel(List<T> list,String[] headers,String title,OutputStream os){
        HSSFWorkbook workbook = new HSSFWorkbook();
        //生成一个表格
        HSSFSheet sheet = workbook.createSheet(title);
        //设置表格默认列宽15个字节
        sheet.setDefaultColumnWidth(15);
        //生成一个样式
        HSSFCellStyle style = this.getCellStyle(workbook);
        //生成一个字体
        HSSFFont font = this.getFont(workbook);
        //把字体应用到当前样式
        style.setFont(font);
        
        //生成表格标题
        HSSFRow row = sheet.createRow(0);
        row.setHeight((short)300);
        HSSFCell cell = null;
        
        for (int i = 0; i < headers.length; i++) {        
            cell = row.createCell(i);
            cell.setCellStyle(style);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
        // 合并日期占两行(4个参数，分别为起始行，结束行，起始列，结束列)
        CellRangeAddress region = new CellRangeAddress(0, 0, 0, 4);
        sheet.addMergedRegion(region);
        
        //将数据放入sheet中
        for (int i = 0; i < list.size(); i++) {
        	HSSFRow r2 = sheet.createRow(i+1);
            T t = list.get(i);
            
            //利用反射，根据JavaBean属性的先后顺序，动态调用get方法得到属性的值
            Field[] fields = t.getClass().getDeclaredFields();
            try {
                for (int j = 0; j < fields.length; j++) {
                	HSSFCell c2 = r2.createCell(j);
                    Field field = fields[j];
                    String fieldName = field.getName();
                    String methodName = "get"+fieldName.substring(0, 1).toUpperCase()+fieldName.substring(1);
                    Method getMethod = t.getClass().getMethod(methodName,new Class[]{});
                    Object value = getMethod.invoke(t, new Object[]{});
                    
                    if(null == value)
                        value ="";
                    c2.setCellValue(value.toString());
                    
                }
            } catch (Exception e) {

            }
        }
        
        try {
            workbook.write(os);
        } catch (Exception e) {

        }finally{
            try {
                os.flush();
                os.close();
            } catch (IOException e) {

            }
        }
        
    }
    
    /** 
    * @Title: getCellStyle 
    * @Description: 获取单元格格式
    * @param @param workbook
    * @param @return
    * @return HSSFCellStyle
    * @throws 
    */
    public HSSFCellStyle getCellStyle(HSSFWorkbook workbook){
        HSSFCellStyle style = workbook.createCellStyle();
        style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setLeftBorderColor(HSSFCellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        
        return style;
    }

    /** 
    * @Title: getFont 
    * @Description: 生成字体样式
    * @param @param workbook
    * @param @return
    * @return HSSFFont
    * @throws 
    */
    public HSSFFont getFont(HSSFWorkbook workbook){
        HSSFFont font = workbook.createFont();
        font.setColor(HSSFColor.WHITE.index);
        font.setFontHeightInPoints((short)12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        return font;
    }
    
    public boolean isIE(HttpServletRequest request){
        return request.getHeader("USER-AGENT").toLowerCase().indexOf("msie")>0?true:false;    
    }
}

看一下数据库我们需要导出的数据：

那么我们调导出方法测试一下：