springboot中将数据导出到excl

maven依赖

    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.17</version>
    </dependency>

    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.17</version>
    </dependency>

具体实现

package cn.tcmp.sizu.finance.utils;
import cn.tcmp.sizu.finance.entity.ArrivalaccountXP;
import cn.tcmp.sizu.finance.mapper.ArrivalaccountXPMapper;
import cn.tcmp.sizu.finance.service.ArrivalaccountXPService;
import cn.tcmp.sizu.finance.service.ArrivalaccountXPServiceImpl;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.List;

/**
 *  Excl导出全部
 */
public class ExclUtils {

    /**
     *
     * @param list 导出的数据集合
     * @param exclName 导出后Excl的sheet文件名称及文件名称
     * @param lName 导出的Excl表的数据列名
     * @return
     */
    public boolean exportFile(List<ArrivalaccountXP> list, String exclName,String[] lName) {
    
        SimpleDateFormat df =new SimpleDateFormat("yyyy-mm-dd");
        //1、创建工作簿
        Workbook wb = new XSSFWorkbook();
        //1.1、设置表格的格式----居中
        CellStyle cs = wb.createCellStyle();
        cs.setAlignment(HorizontalAlignment.CENTER);
        //2.1、创建工作表
        Sheet sheet = wb.createSheet(exclName);
        //2.2、合并单元格
        //sheet.addMergedRegion(new CellRangeAddress(4, 8, 5, 9));
        //3.1、创建行----表头行
        Row row = sheet.createRow(0);
        //4、创建格
        Cell cell = row.createCell(0);
        cell.setCellValue("序号");
        cell.setCellStyle(cs);
        for (int i=0;i<lName.length; i++) {
            cell = row.createCell(i+1);
            cell.setCellValue(lName[i]);
            cell.setCellStyle(cs);
        }

        //5、写入实体数据
        for (int i = 0; i < list.size(); i++) {
            //3.2、创建行----内容行
            row = sheet.createRow(i+1);
            ArrivalaccountXP us = (ArrivalaccountXP)list.get(i);
            //第几行第几格
            String ctType = "";
            if(us.getCtId().getCtType()==1){
                ctType = "正常客户";
            }else{
                ctType = "不正常客户";
            }
            String ctCertificatestype = "";
            if(us.getCtId().getCtCertificatestype()==1){
                ctCertificatestype = "身份证";
            }else if(us.getCtId().getCtCertificatestype()==2){
                ctCertificatestype = "军人证";
            }else{
                ctCertificatestype = "黑人";
            }
            String arrstatus = "";
            if(us.getAptId().getAptArrivalstatus()==1){
                arrstatus = "未到账";
            }else if(us.getAptId().getAptArrivalstatus()==2){
                arrstatus = "确认中";
            }else if(us.getAptId().getAptArrivalstatus()==3){
                arrstatus = "部分到账";
            }else{
                arrstatus = "足额到账";
            }
            String faceStatus = "";
            if(us.getAptId().getAptFacestatus()==1){
                faceStatus = "创建状态未预约";
            }else if(us.getAptId().getAptFacestatus()==2){
                faceStatus = "视频预约";
            }else if(us.getAptId().getAptFacestatus()==3){
                faceStatus = "申请中";
            }else if(us.getAptId().getAptFacestatus()==4){
                faceStatus = "面签成功";
            }else if(us.getAptId().getAptFacestatus()==5){
                faceStatus = "面签失败";
            }else{
                faceStatus = "待整改";
            }
            String rStatus = "";
            if(us.getAptId().getAptReturnvisit()==1){
                rStatus = "未回访";
            }else if(us.getAptId().getAptReturnvisit()==2){
                rStatus = "回访失败";
            }else{
                rStatus = "回访成功";
            }

            String eStatus = "";
            if(us.getAptId().getAptEstablishstatus()==1){
                eStatus = "成立中";
            }else if(us.getAptId().getAptEstablishstatus()==2){
                eStatus = "未成立";
            }else if(us.getAptId().getAptEstablishstatus()==3){
                eStatus = "成立失败";
            }else{
                eStatus = "成立成功";
            }
            String bStatus = "";
            if(us.getAptId().getAptBusinesstype()==1){
                bStatus = "信托";
            }else if(us.getAptId().getAptBusinesstype()==1){
                bStatus = "房地产";
            }else{
                bStatus = "其它";
            }

# 以上为数据类型判断可自定义
			// 给一行中的单元格赋值（我的是22个单元格）
            row.createCell(0).setCellValue(i+1);
            row.createCell(1).setCellValue("核对批注");
            row.createCell(2).setCellValue(ctType);
            row.createCell(3).setCellValue(us.getCtId().getCtName());
            row.createCell(4).setCellValue(ctCertificatestype);
            row.createCell(5).setCellValue(us.getCtId().getCtCertificatesNumber());
            row.createCell(6).setCellValue(us.getCtId().getCtFinancialmanager());
            row.createCell(7).setCellValue(us.getCtId().getCtFinancialmanagertxt());
            row.createCell(8).setCellValue(us.getAptId().getAptProjectName());
            row.createCell(9).setCellValue(us.getAptId().getAptTomoney());
            row.createCell(10).setCellValue(us.getAvtNumber());
            row.createCell(11).setCellValue(us.getAptId().getAptSubscription()+"%");
            row.createCell(12).setCellValue(us.getAptId().getAptTodate());
            row.createCell(13).setCellValue(us.getAptId().getAptLineUp().toString());
            row.createCell(14).setCellValue(us.getAptId().getAptStatustime().toString());
            row.createCell(15).setCellValue(bStatus);
            row.createCell(16).setCellValue(arrstatus);
            row.createCell(17).setCellValue("确认中");
            row.createCell(18).setCellValue(faceStatus);
            row.createCell(19).setCellValue(rStatus);
            row.createCell(20).setCellValue(eStatus);
            row.createCell(21).setCellValue("尚未编号");

        }

        //6、将文件储存到指定位置
        try {
            FileOutputStream fout = new FileOutputStream("D:\\到账明细.xlsx");
            wb.write(fout);
            fout.close();
            return true;
        } catch (IOException e) {
            e.printStackTrace();
            return  false;
        }
    }
}