java poi动态列导出excel

最新推荐文章于 2024-08-03 23:38:17 发布

一只皮皮兔

最新推荐文章于 2024-08-03 23:38:17 发布

阅读量2.6k

点赞数

分类专栏： java下载

本文链接：https://blog.csdn.net/m18066975852/article/details/96442092

版权

java下载专栏收录该内容

2 篇文章 0 订阅

订阅专栏

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

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;

import com.google.common.collect.Lists;
i

public static void exportBooking(Train train1, HttpServletResponse response) throws IOException {
       OutputStream outputStream = null;
       HSSFWorkbook workbook = null;
       try {
           List<String> titleList = Lists.newArrayList();
           // 1.获得日期内所有班次
           Train tempTrain = new Train();
           tempTrain.setBeginDate(DateUtils.addHours(train1.getBeginDate(), -8));
           tempTrain.setEndDate(DateUtils.addHours(train1.getEndDate(), -8));
           List<Train> trainList = trainService.exportFindList(tempTrain);
           if (trainList != null && trainList.size() > 0) {
               // 2.获取这些班次订舱的客户，作为表头
               List<Company> companyList = companyService.exportFindList(trainList);
               for (Company company : companyList) {
                   titleList.add(company.getName());
               }
               workbook = new HSSFWorkbook();
               // 创建合并单元格
               CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, 1 + titleList.size());// 起始行,结束行,起始列,结束列
               // 创建单元格样式
               HSSFCellStyle titleStyle = ExportExcel.createCellStyle(workbook, "宋体", (short) 14, false, true, false, "",
                       true);
               HSSFCellStyle colStyle = ExportExcel.createCellStyle(workbook, "宋体", (short) 11, false, true, false, "",
                       true);
               HSSFCellStyle colLeftStyle = ExportExcel.createCellStyle(workbook, "宋体", (short) 11, false, false, false, "",
                       true);
               HSSFCellStyle blueStyle = ExportExcel.createCellStyle(workbook, "宋体", (short) 11, false, true, false, "",
                       true);
               HSSFFont blueFont = workbook.createFont();
               blueFont.setColor(HSSFColor.BLUE.index);
               blueStyle.setFont(blueFont);
               HSSFCellStyle redStyle = ExportExcel.createCellStyle(workbook, "宋体", (short) 11, false, true, false, "",
                       true);
               HSSFFont redFont = workbook.createFont();
               redFont.setColor(HSSFColor.RED.index);
               redStyle.setFont(redFont);
               HSSFCellStyle goBlueStyle = ExportExcel.createCellStyle(workbook, "宋体", (short) 11, false, false, false, "blue",
                       true);
               HSSFCellStyle backYelloStyle = ExportExcel.createCellStyle(workbook, "宋体", (short) 11, false, false, false, "yello",
                       true);
               // 创建工作表
               HSSFSheet sheet = workbook.createSheet("sheet1");
               sheet.getPrintSetup().setLandscape(true);
               sheet.setAutobreaks(true);
               // 加载合并单元格对象
               sheet.addMergedRegion(cellRangeAddress);
               //锁定前两行
               sheet.createFreezePane(0,2,0,2);
               // 设置默认列宽
               sheet.setDefaultColumnWidth(12);
               sheet.setColumnWidth(0, 252*62+323);
               sheet.setColumnWidth(1, 252*20+323);
               // 创建行
               // 创建头标题行;并且设置头标题
               HSSFRow titleRow = sheet.createRow(0);
               titleRow.setHeightInPoints((short) 30);
               HSSFCell titleCell = titleRow.createCell(0);
               titleCell.setCellStyle(titleStyle);
               titleCell.setCellValue(DateUtils.formatDate(train1.getBeginDate(), "MMdd")+"-"+DateUtils.formatDate(train1.getEndDate(), "MMdd")
               +"班列订舱情况（截止"+DateUtils.getDate("yyyy年MM月dd日HH:mm:ss")+"）");
               //创建小表头
               HSSFRow row1 = sheet.createRow(1);
               row1.setHeightInPoints((short) 30);
               HSSFCell row1cell = row1.createCell(0);
               row1cell.setCellStyle(colLeftStyle);
               row1cell.setCellValue("班次/客户订舱");
               row1cell = row1.createCell(1);
               row1cell.setCellStyle(colStyle);
               row1cell.setCellValue("总计");
               for (int i = 0; i < titleList.size(); i++) {
                   row1cell = row1.createCell(2+i);
                   row1cell.setCellStyle(colStyle);
                   row1cell.setCellValue(titleList.get(i));
               }
               // 3.正式循环，输出数据，每个班次一横行数据
               for (int i = 0; i < trainList.size(); i++) {
                   Train train = trainList.get(i);
                   HSSFRow row = sheet.createRow(2+i);
                   row.setHeightInPoints((short) 30);
                   HSSFCell cell0=row.createCell(0);
                   String temp = "";
                   if (train.getLine()!=null && StringUtils.isNotBlank(train.getLine().getName())) {
                       if(train.getLine().getGoOrBack() !=null && train.getLine().getGoOrBack()==1){
                           cell0.setCellStyle(goBlueStyle);
                           temp = "WB";
                       }else{
                           cell0.setCellStyle(backYelloStyle);
                           temp = "EB";
                       }
                       cell0.setCellValue(temp+DateUtils.formatDate(train.getDepartureDate(), "MMdd")
                       +" （"+DateUtils.getWeekOfDate(train.getDepartureDate())+"）"+train.getLine().getName());

                   }else{
                       cell0.setCellStyle(colStyle);
                       cell0.setCellValue(train.getTrainCode()+"班次所在线路查询不到！");
                   }

                   HSSFCell cell1=row.createCell(1);
                   cell1.setCellStyle(colStyle);
                   cell1.setCellValue(String.valueOf(train.getTotleSpaceNum()-train.getSurplusSpaceNum()));
                   // 该班次下所有订舱数量，根据客户id分组 spaceNum company.id
                   List<Order> orders = orderService.findOrderCompany(train);
                   for (int j = 0; j < titleList.size(); j++) {
                       HSSFCell cell=row.createCell(2+j);
                       int num = 0;
                       for (int k = 0; k < orders.size(); k++) {
                           if (orders.get(k).getCompany().getName().equals(titleList.get(j))) {
                               num = orders.get(k).getSpaceNum();
                           }
                       }
                       if (num!=0) {
                           cell.setCellStyle(blueStyle);
                       }else{
                           cell.setCellStyle(colStyle);
                       }
                       cell.setCellValue(num+"");
                   }
               }
               //总计
               HSSFRow zjRow = sheet.createRow(trainList.size()+2);
               zjRow.setHeightInPoints((short) 30);
   HSSFCell zjCell0 = zjRow.createCell(0);
   zjCell0.setCellStyle(redStyle);
   zjCell0.setCellValue("总计（"+DateUtils.formatDate(train1.getBeginDate(), "MMdd")+"-"+DateUtils.formatDate(train1.getEndDate(), "MMdd")+"）订舱");
   //总计数据
   HSSFCell zjCell1 = zjRow.createCell(1);
   zjCell1.setCellType(HSSFCell.CELL_TYPE_FORMULA);
   colStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
   zjCell1.setCellStyle(colStyle);
   String all = "";
   for(int i=0;i<trainList.size();i++){
       all = all + "B" + (i+3) + "+";
   }
   if (all!="") {
       zjCell1.setCellFormula(all.substring(0,all.length()-1));
               }
   //每行循环总计数据
   char begin_letter='B';
   HSSFCell cellRows2=null;
   for(int k=0;k<titleList.size();k++){
   cellRows2=zjRow.createCell(2+k);
   colStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
   cellRows2.setCellStyle(colStyle);
   cellRows2.setCellType(HSSFCell.CELL_TYPE_BLANK);
   String basePrice="";
   char char2;
   char2=(char) (begin_letter +1);
   for(int j=0;j<trainList.size();j++){
   basePrice+=(char2)+""+(3+j)+"+";
   }
   begin_letter=char2;
   if (basePrice!="") {
       cellRows2.setCellFormula(basePrice.substring(0,basePrice.length()-1));
                   }
   }

           }
           /**
* 下载文件
*/
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setHeader( "Content-Disposition", "attachment; filename="+ Encodes.urlEncode("班列订舱情况统计表.xls"));
outputStream= response.getOutputStream();
workbook.write(outputStream);
       } catch (Exception e) {
           e.printStackTrace();
       } finally {
           if (outputStream != null) {
               outputStream.close();
           }
       }

   }
   /**
*
* @param workbook
* @param fontsize
* @return 单元格样式
*/
public static HSSFCellStyle createCellStyle(HSSFWorkbook workbook,String fontName, short fontsize, boolean flag, boolean flag1,boolean flag3,String color,boolean flag4) {
// TODO Auto-generated method stub
HSSFCellStyle style = workbook.createCellStyle();
//是否水平居中
if(flag1){
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
} else if(flag3){
style.setVerticalAlignment(HSSFCellStyle.ALIGN_LEFT);//垂直居中
}
if(flag4){
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setTopBorderColor(HSSFColor.BLACK.index);
style.setBottomBorderColor(HSSFColor.BLACK.index);
style.setLeftBorderColor(HSSFColor.BLACK.index);
style.setRightBorderColor(HSSFColor.BLACK.index);
}
//创建字体
HSSFFont font = workbook.createFont();
font.setFontHeightInPoints((short)11);
if(StringUtils.isNotEmpty(color)&&color.equals("blue")){
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
}
if(StringUtils.isNotEmpty(color)&&color.equals("green")){
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setFillForegroundColor(HSSFColor.LIME.index);
}
if(StringUtils.isNotEmpty(color)&&color.equals("yello")){
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setFillForegroundColor(HSSFColor.YELLOW.index);
}
if(StringUtils.isNotEmpty(color)&&color.equals("gray")){
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
}
if(StringUtils.isNotEmpty(color)&&color.equals("orange")){
   style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
   style.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index);
}
//是否加粗字体
if(flag){
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
}
if(StringUtils.isNotEmpty(fontName)){
font.setFontName(fontName);
}
font.setFontHeightInPoints(fontsize);
//加载字体
style.setFont(font);
return style;
}