java里execl表导出

结构：

控制器：

//礼包码导出
    @RequestMapping("/codeExport")
    public void codeExport(gift_code giftCode) throws Exception {
        List<gift_code> codeList = giftManageService.selectByPID(giftCode);
        execlExportFile execlExportFile = new execlExportFile();
        execlExportFile.codeExport(codeList);
    }

实现类：

//根据PID查询礼包码
    @Override
    public List<gift_code> selectByPID(gift_code giftCode) {
        List<gift_code> codeList = null;
        try {
            codeList = giftCM.selectByPID(giftCode);  //从数据库查出要导出的数据
        }catch (Exception e){
            e.printStackTrace();
        }
        return codeList;
    }

导出：

package com.umi.ga.execlExport;

import com.umi.ga.pojo.gift_code;
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.xssf.streaming.SXSSFWorkbook;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.io.FileOutputStream;
import java.sql.Date;
import java.text.SimpleDateFormat;
import java.util.List;

public class execlExportFile {
    //礼包码导出
    @SuppressWarnings({ "null", "unchecked" })
    public void codeExport(Object obj) throws Exception{
        HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest();
        String projectPath = request.getSession().getServletContext().getRealPath("/webapps");
        String tomcatPath = new File(projectPath ).getParentFile().getParentFile().getAbsolutePath();
        String xlsFile = tomcatPath+"/upload/";        //输出文件
        File file = new File(xlsFile);
        // 如果文件夹不存在，则创建
        if (!file.exists()) {
            file.mkdirs();
        }
        xlsFile = file+"/log.xlsx";
        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        //内存中只创建100个对象，写临时文件，当超过100条，就将内存中不用的对象释放。
        Workbook wb = new SXSSFWorkbook(100);            //关键语句
        Sheet sheet = null;        //工作表对象
        Row nRow = null;        //行对象
        Cell nCell = null;        //列对象
        long  startTime = System.currentTimeMillis();    //开始时间
        System.out.println("strat execute time: " + startTime);
        int rowNo = 0;        //总行号
        int pageRowNo = 0;    //页行号
        List<gift_code> au = (List<gift_code>) obj;
        if(au.size() > 0) {
            for(int i = 0;i < au.size();i++)
            {
                //打印300000条后切换到下个工作表，可根据需要自行拓展，2百万，3百万...数据一样操作，只要不超过1048576就可以
                if(rowNo%300000==0){
                    System.out.println("Current Sheet:" + rowNo/300000);
                    sheet = wb.createSheet("我的第"+(rowNo/300000)+"个工作簿");//建立新的sheet对象
                    sheet = wb.getSheetAt(rowNo/300000);        //动态指定当前的工作表
                    pageRowNo = 1;        //每当新建了工作表就将当前工作表的行号重置为0
                    nRow = sheet.createRow(0);
                    Cell cel0 = nRow.createCell(0);
                    cel0.setCellValue("礼包码");
                    Cell cel1 = nRow.createCell(1);
                    cel1.setCellValue("礼包编号");
                    Cell cel2 = nRow.createCell(2);
                    cel2.setCellValue("渠道");
                    Cell cel3 = nRow.createCell(3);
                    cel3.setCellValue("生成日期");
                    Cell cel4 = nRow.createCell(4);
                    cel4.setCellValue("开始时间");
                    Cell cel5 = nRow.createCell(5);
                    cel5.setCellValue("结束时间");
                    Cell cel6 = nRow.createCell(6);
                    cel6.setCellValue("最大可领");
                    Cell cel7 = nRow.createCell(7);
                    cel7.setCellValue("礼包码状态");
                    Cell cel8 = nRow.createCell(8);
                    cel8.setCellValue("礼包码批次");

                }
                rowNo++;
                nRow = sheet.createRow(pageRowNo++);    //新建行对象

                if (au.get(i).getCodeCdk() == null)
                    au.get(i).setCodeCdk("0");
                if (au.get(i).getPackageId() == null)
                    au.get(i).setPackageId(0);
                if (au.get(i).getChannel() == null)
                    au.get(i).setChannel(0);
                if (au.get(i).getLogTime() == null)
                    au.get(i).setLogTime(Date.valueOf("0"));
                if (au.get(i).getStartTime() == null)
                    au.get(i).setStartTime(Date.valueOf("0"));
                if (au.get(i).getEndTime() == null)
                    au.get(i).setEndTime(Date.valueOf("0"));
                if (au.get(i).getMaxReceive() == null)
                    au.get(i).setMaxReceive(0);
                if (au.get(i).getCodeState() == null)
                    au.get(i).setCodeState(0);
                if (au.get(i).getCodeBatch() == null)
                    au.get(i).setCodeState(0);


                nCell = nRow.createCell(0);
                nCell.setCellValue( au.get(i).getCodeCdk());
                nCell = nRow.createCell(1);
                nCell.setCellValue( au.get(i).getPackageId());
                nCell = nRow.createCell(2);
                nCell.setCellValue( au.get(i).getChannel());
                nCell = nRow.createCell(3);
                nCell.setCellValue( formatter.format(au.get(i).getLogTime()));
                nCell = nRow.createCell(4);
                nCell.setCellValue( formatter.format(au.get(i).getStartTime()));
                nCell = nRow.createCell(5);
                nCell.setCellValue( formatter.format(au.get(i).getEndTime()));
                nCell = nRow.createCell(6);
                nCell.setCellValue( au.get(i).getMaxReceive());
                nCell = nRow.createCell(7);
                nCell.setCellValue( au.get(i).getCodeState());
                nCell = nRow.createCell(8);
                nCell.setCellValue( au.get(i).getCodeBatch());



                if(rowNo%10000==0){
                    System.out.println("row no: " + rowNo);
                }
//			        Thread.sleep(1);    //休息一下，防止对CPU占用，其实影响不大

            }}else if(au.size() == 0) {
            //打印300000条后切换到下个工作表，可根据需要自行拓展，2百万，3百万...数据一样操作，只要不超过1048576就可以
            if(rowNo%300000==0){
                System.out.println("Current Sheet:" + rowNo/300000);
                sheet = wb.createSheet("我的第"+(rowNo/300000)+"个工作簿");//建立新的sheet对象
                sheet = wb.getSheetAt(rowNo/300000);        //动态指定当前的工作表
                pageRowNo = 1;        //每当新建了工作表就将当前工作表的行号重置为0
                nRow = sheet.createRow(0);
                Cell cel0 = nRow.createCell(0);
                cel0.setCellValue("礼包码");
                Cell cel1 = nRow.createCell(1);
                cel1.setCellValue("礼包编号");
                Cell cel2 = nRow.createCell(2);
                cel2.setCellValue("渠道");
                Cell cel3 = nRow.createCell(3);
                cel3.setCellValue("生成日期");
                Cell cel4 = nRow.createCell(4);
                cel4.setCellValue("开始时间");
                Cell cel5 = nRow.createCell(5);
                cel5.setCellValue("结束时间");
                Cell cel6 = nRow.createCell(6);
                cel6.setCellValue("最大可领");
                Cell cel7 = nRow.createCell(7);
                cel7.setCellValue("礼包码状态");
                Cell cel8 = nRow.createCell(8);
                cel8.setCellValue("礼包码批次");

            }
            rowNo++;
            nRow = sheet.createRow(pageRowNo++);    //新建行对象

            if(rowNo%10000==0){
                System.out.println("row no: " + rowNo);
            }
        }
        long finishedTime = System.currentTimeMillis();    //处理完成时间
        System.out.println("finished execute  time: " + (finishedTime - startTime)/1000 + "m");

        FileOutputStream fOut = new FileOutputStream(xlsFile);
        wb.write(fOut);
        fOut.flush();        //刷新缓冲区
        fOut.close();

        long stopTime = System.currentTimeMillis();        //写文件时间
        System.out.println("write xlsx file time: " + (stopTime - startTime)/1000 + "m");
    }
}

实体类：

package com.umi.ga.pojo;

import java.util.Date;

public class gift_code {
    private String codeCdk; //礼包码

    private Integer packageId; //礼包id

    private Integer channel; //通道

    private Date logTime;

    private Date startTime;

    private Date endTime;

    private Integer maxReceive; //最大可领取次数

    private Integer codeState; //状态

    private Integer codeBatch;

    private String prefix; //礼包前缀

    private Integer codeNumber; //礼包数量

    public String getCodeCdk() {
        return codeCdk;
    }

    public void setCodeCdk(String codeCdk) {
        this.codeCdk = codeCdk == null ? null : codeCdk.trim();
    }

    public Integer getPackageId() {
        return packageId;
    }

    public void setPackageId(Integer packageId) {
        this.packageId = packageId;
    }

    public Integer getChannel() {
        return channel;
    }

    public void setChannel(Integer channel) {
        this.channel = channel;
    }

    public Date getLogTime() {
        return logTime;
    }

    public void setLogTime(Date logTime) {
        this.logTime = logTime;
    }

    public Date getStartTime() {
        return startTime;
    }

    public void setStartTime(Date startTime) {
        this.startTime = startTime;
    }

    public Date getEndTime() {
        return endTime;
    }

    public void setEndTime(Date endTime) {
        this.endTime = endTime;
    }

    public Integer getMaxReceive() {
        return maxReceive;
    }

    public void setMaxReceive(Integer maxReceive) {
        this.maxReceive = maxReceive;
    }

    public Integer getCodeState() {
        return codeState;
    }

    public void setCodeState(Integer codeState) {
        this.codeState = codeState;
    }

    public Integer getCodeBatch() {
        return codeBatch;
    }

    public void setCodeBatch(Integer codeBatch) {
        this.codeBatch = codeBatch;
    }

    public String getPrefix() {
        return prefix;
    }

    public void setPrefix(String prefix) {
        this.prefix = prefix;
    }

    public Integer getCodeNumber() {
        return codeNumber;
    }

    public void setCodeNumber(Integer codeNumber) {
        this.codeNumber = codeNumber;
    }
}