List<Buyer_Live_Apply> list=buyerliveapplyService.getListByApplyIds(applyIds);//此为我要导出的数据,这里写你的要导出数据list
// 第一步,创建一个webbook,对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
// 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet("买手表");
// 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
//下面样式可作为导出左右分栏的表格模板
sheet.setColumnWidth((short) 0, (short) 2400);// 设置列宽
sheet.setColumnWidth((short) 1, (short) 2400);
sheet.setColumnWidth((short) 2, (short) 4300);
sheet.setColumnWidth((short) 3, (short) 2400);
sheet.setColumnWidth((short) 4, (short) 4800);
sheet.setColumnWidth((short) 5, (short) 4000);// 空列设置小一些
sheet.setColumnWidth((short) 6, (short) 4600);// 设置列宽
sheet.setColumnWidth((short) 7, (short) 4400);
sheet.setColumnWidth((short) 8, (short) 4300);
sheet.setColumnWidth((short) 9, (short) 4600);
sheet.setColumnWidth((short) 10, (short) 4800);
sheet.setColumnWidth((short) 11, (short) 4800);
HSSFRow row = sheet.createRow((int) 0);
row.setHeightInPoints(20);
// 第四步,创建单元格,并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
HSSFFont font=wb.createFont();
font.setColor(HSSFColor.RED.index);//HSSFColor.VIOLET.index //字体颜色
style.setFont(font);
style.setAlignment(HSSFCellStyle.ALIGN_LEFT);//居左
HSSFCell cell = row.createCell((short) 0);
cell.setCellValue("编号");
cell = row.createCell((short) 1);
cell.setCellValue("行程编号");
cell = row.createCell((short) 2);
cell.setCellValue("代购类型");
cell = row.createCell((short) 3);
cell.setCellValue("用户编号");
cell = row.createCell((short) 4);
cell.setCellValue("目标城市");
cell = row.createCell((short) 5);
cell.setCellValue("手机号");
cell = row.createCell((short) 6);
cell.setCellValue("姓名");
cell = row.createCell((short) 7);
cell.setCellValue("出行日期");
cell = row.createCell((short) 8);
cell.setCellValue("回程时间");
cell = row.createCell((short) 9);
cell.setCellValue("微信号");
cell = row.createCell((short) 10);
cell.setCellValue("申请时间");
cell = row.createCell((short) 11);
cell.setCellValue("申请状态");
for(int i=0;i<cell.getColumnIndex()+1;i++){//第一行每一个单元给样式
cell.getRow().getCell(i).setCellStyle(style);
}
// 第五步,写入到excel
for(int i=0;i<list.size();i++){
style = wb.createCellStyle();
row = sheet.createRow((int) i + 1);
row.setHeightInPoints(20);
// 第四步,创建单元格,并设置值
row.createCell((short) 0).setCellValue(list.get(i).getId()+"");
row.createCell((short) 1).setCellValue(list.get(i).getTripId()+"");
if(list.get(i).getApplyType().equals(0)){
row.createCell((short) 2).setCellValue("人肉买手");
}else{
row.createCell((short) 2).setCellValue("本土买手");
}
row.createCell((short) 3).setCellValue(list.get(i).getBuyerId()+"");
row.createCell((short) 4).setCellValue(list.get(i).getFromCity());
row.createCell((short) 5).setCellValue(list.get(i).getMobile());
row.createCell((short) 6).setCellValue(list.get(i).getRealname());
if(list.get(i).getTripStime()!=null){
String dateString=DateUtil.getDateStrFromDate(list.get(i).getTripStime());
row.createCell((short) 7).setCellValue(dateString);
}else{
row.createCell((short) 7).setCellValue("");
}
if(list.get(i).getTripEtime()!=null){
String dateString=DateUtil.getDateStrFromDate(list.get(i).getTripEtime());
row.createCell((short) 8).setCellValue(dateString);
}else{
row.createCell((short) 8).setCellValue("");
}
row.createCell((short) 9).setCellValue(list.get(i).getWxSignal());
if(list.get(i).getCreateTime()!=null){
String dateString=DateUtil.getLongStrFromDate(list.get(i).getCreateTime());
row.createCell((short) 10).setCellValue(dateString);
}else{
row.createCell((short) 10).setCellValue("");
}
if(list.get(i).getStatus().equals(ApplyStatus.cancel.getValue())){
row.createCell((short) 11).setCellValue("已取消");
}else if(list.get(i).getStatus().equals(ApplyStatus.NotExa.getValue())){
row.createCell((short) 11).setCellValue("待审核");
}else if(list.get(i).getStatus().equals(ApplyStatus.success.getValue())){
row.createCell((short) 11).setCellValue("审核成功");
}else if(list.get(i).getStatus().equals(ApplyStatus.Exafail.getValue())){
row.createCell((short) 11).setCellValue("审核失败");
}else if(list.get(i).getStatus().equals(ApplyStatus.end.getValue())){
row.createCell((short) 11).setCellValue("结束");
}
}
// 第六步,将文件存到指定位置
try
{ //输出Excel文件
OutputStream output=response.getOutputStream();
response.reset();
response.setHeader("Content-disposition", "attachment; filename=apply.xls");
response.setContentType("application/msexcel");
wb.write(output);
output.close();
wb.close();
}catch (Exception e) {
e.printStackTrace();
}
}
// 第一步,创建一个webbook,对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
// 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet("买手表");
// 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
//下面样式可作为导出左右分栏的表格模板
sheet.setColumnWidth((short) 0, (short) 2400);// 设置列宽
sheet.setColumnWidth((short) 1, (short) 2400);
sheet.setColumnWidth((short) 2, (short) 4300);
sheet.setColumnWidth((short) 3, (short) 2400);
sheet.setColumnWidth((short) 4, (short) 4800);
sheet.setColumnWidth((short) 5, (short) 4000);// 空列设置小一些
sheet.setColumnWidth((short) 6, (short) 4600);// 设置列宽
sheet.setColumnWidth((short) 7, (short) 4400);
sheet.setColumnWidth((short) 8, (short) 4300);
sheet.setColumnWidth((short) 9, (short) 4600);
sheet.setColumnWidth((short) 10, (short) 4800);
sheet.setColumnWidth((short) 11, (short) 4800);
HSSFRow row = sheet.createRow((int) 0);
row.setHeightInPoints(20);
// 第四步,创建单元格,并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
HSSFFont font=wb.createFont();
font.setColor(HSSFColor.RED.index);//HSSFColor.VIOLET.index //字体颜色
style.setFont(font);
style.setAlignment(HSSFCellStyle.ALIGN_LEFT);//居左
HSSFCell cell = row.createCell((short) 0);
cell.setCellValue("编号");
cell = row.createCell((short) 1);
cell.setCellValue("行程编号");
cell = row.createCell((short) 2);
cell.setCellValue("代购类型");
cell = row.createCell((short) 3);
cell.setCellValue("用户编号");
cell = row.createCell((short) 4);
cell.setCellValue("目标城市");
cell = row.createCell((short) 5);
cell.setCellValue("手机号");
cell = row.createCell((short) 6);
cell.setCellValue("姓名");
cell = row.createCell((short) 7);
cell.setCellValue("出行日期");
cell = row.createCell((short) 8);
cell.setCellValue("回程时间");
cell = row.createCell((short) 9);
cell.setCellValue("微信号");
cell = row.createCell((short) 10);
cell.setCellValue("申请时间");
cell = row.createCell((short) 11);
cell.setCellValue("申请状态");
for(int i=0;i<cell.getColumnIndex()+1;i++){//第一行每一个单元给样式
cell.getRow().getCell(i).setCellStyle(style);
}
// 第五步,写入到excel
for(int i=0;i<list.size();i++){
style = wb.createCellStyle();
row = sheet.createRow((int) i + 1);
row.setHeightInPoints(20);
// 第四步,创建单元格,并设置值
row.createCell((short) 0).setCellValue(list.get(i).getId()+"");
row.createCell((short) 1).setCellValue(list.get(i).getTripId()+"");
if(list.get(i).getApplyType().equals(0)){
row.createCell((short) 2).setCellValue("人肉买手");
}else{
row.createCell((short) 2).setCellValue("本土买手");
}
row.createCell((short) 3).setCellValue(list.get(i).getBuyerId()+"");
row.createCell((short) 4).setCellValue(list.get(i).getFromCity());
row.createCell((short) 5).setCellValue(list.get(i).getMobile());
row.createCell((short) 6).setCellValue(list.get(i).getRealname());
if(list.get(i).getTripStime()!=null){
String dateString=DateUtil.getDateStrFromDate(list.get(i).getTripStime());
row.createCell((short) 7).setCellValue(dateString);
}else{
row.createCell((short) 7).setCellValue("");
}
if(list.get(i).getTripEtime()!=null){
String dateString=DateUtil.getDateStrFromDate(list.get(i).getTripEtime());
row.createCell((short) 8).setCellValue(dateString);
}else{
row.createCell((short) 8).setCellValue("");
}
row.createCell((short) 9).setCellValue(list.get(i).getWxSignal());
if(list.get(i).getCreateTime()!=null){
String dateString=DateUtil.getLongStrFromDate(list.get(i).getCreateTime());
row.createCell((short) 10).setCellValue(dateString);
}else{
row.createCell((short) 10).setCellValue("");
}
if(list.get(i).getStatus().equals(ApplyStatus.cancel.getValue())){
row.createCell((short) 11).setCellValue("已取消");
}else if(list.get(i).getStatus().equals(ApplyStatus.NotExa.getValue())){
row.createCell((short) 11).setCellValue("待审核");
}else if(list.get(i).getStatus().equals(ApplyStatus.success.getValue())){
row.createCell((short) 11).setCellValue("审核成功");
}else if(list.get(i).getStatus().equals(ApplyStatus.Exafail.getValue())){
row.createCell((short) 11).setCellValue("审核失败");
}else if(list.get(i).getStatus().equals(ApplyStatus.end.getValue())){
row.createCell((short) 11).setCellValue("结束");
}
}
// 第六步,将文件存到指定位置
try
{ //输出Excel文件
OutputStream output=response.getOutputStream();
response.reset();
response.setHeader("Content-disposition", "attachment; filename=apply.xls");
response.setContentType("application/msexcel");
wb.write(output);
output.close();
wb.close();
}catch (Exception e) {
e.printStackTrace();
}
}