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();
}
}
}
}