导出数据到表格

utiles

import com.bean.Aword;
import com.bean.Game;
import com.bean.Joingame;
import com.bean.Users;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.List;

public class ExcelUtil {
    //1、创建Excel对象
    public HSSFWorkbook excel = new HSSFWorkbook();
    //2、创建Sheet文件
    public HSSFSheet sheet=excel.createSheet("sheet1");

    //3、创建保存文件头目录的数组
    private  String[] headers=null;

    //5、创建第一行
    public  void createhead(){
        HSSFRow row =sheet.createRow(0);
        for (int i=0;i<headers.length;i++){
            //写入第一列的内容
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(headers[i]);
        }
    }


    public String[] getHeaders() {
        return headers;
    }

    public void setHeaders(String[] headers) {
        this.headers = headers;
    }


    //6、创建其他行 list保存的是要导出的数据
    //----------------------------111111111111111
    public void gameExport(List list){
        for (int i=0;i<list.size();i++){
            Joingame joingame =(Joingame) list.get(i);
            //创建行,头信息占用一行,从第二行开始
            // 也就是下标为1
            HSSFRow row=sheet.createRow(i+1);
            HSSFCell cell1=row.createCell(0);
            HSSFCell cell2=row.createCell(1);
            HSSFCell cell3=row.createCell(2);





            //得到的数据写入相应的单元
            cell1.setCellValue(joingame.getGid());
            cell2.setCellValue(joingame.getGame().getGname());
            cell3.setCellValue(joingame.getUsers().getUname());

        }
    }

    public void userExport(List list){
        for (int i=0;i<list.size();i++){
            Users users =(Users) list.get(i);
            //创建行,头信息占用一行,从第二行开始
            // 也就是下标为1
            HSSFRow row=sheet.createRow(i+1);
            HSSFCell cell1=row.createCell(0);
            HSSFCell cell2=row.createCell(1);





            //得到的数据写入相应的单元
            cell1.setCellValue(users.getUid());
            cell2.setCellValue(users.getUname());

        }
    }

    public void awordExport(List list){
        for (int i=0;i<list.size();i++){
            Aword aword=(Aword) list.get(i);
            //创建行,头信息占用一行,从第二行开始
            // 也就是下标为1
            HSSFRow row=sheet.createRow(i+1);
            HSSFCell cell1=row.createCell(0);
            HSSFCell cell2=row.createCell(1);
            HSSFCell cell3=row.createCell(2);
            HSSFCell cell4=row.createCell(3);
            HSSFCell cell5=row.createCell(4);

            //转换时间格式
            SimpleDateFormat simpleDateFormat=new SimpleDateFormat("yyyy-MM-dd hh-mm-ss");

            String time=simpleDateFormat.format(aword.getAtime());



            //得到的数据写入相应的单元
            cell1.setCellValue(aword.getAid());
            cell2.setCellValue(aword.getGame().getGname());
            cell3.setCellValue(aword.getUsers().getUname());
            cell4.setCellValue(aword.getAgrade()+"等奖");
            cell5.setCellValue(time);
        }
    }



    //7、设置io流和excel的关系
    public  void export(OutputStream out){
        try {
            sheet.setGridsPrinted(true);
            excel.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

}

调用方式

//获奖信息导出
    @Override
    public void awordexport() {
        //创建集合list
        List<Aword> list=new ArrayList<>();

        //查询所有获奖信息,并把数据保存到list
        list=awordMapper.selectAwordList();


        ExcelUtil excelUtil=new ExcelUtil();
        //创建表头
        excelUtil.setHeaders(new String[]{"获奖编号","比赛名称","获奖者","获奖等级","获奖时间"});

        excelUtil.createhead();
        excelUtil.awordExport(list);

        SimpleDateFormat simpleDateFormat=new SimpleDateFormat("yyyyMMddhhmmss");
        String date=simpleDateFormat.format(new Date());
        FileOutputStream fileOutputStream=null;
        try {
            fileOutputStream=new FileOutputStream("F:\\获奖信息表"+date+".xls");
            excelUtil.export(fileOutputStream);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }finally {
            if(fileOutputStream!=null){
                try {
                    fileOutputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }

        }
    }