poi 导出excel (test 版) 2. 读取模板，写入数据

 

1. 基本导出

2. 读取模板，填写数据

 

 

 1.  基本导出

  /**
     * query and filter  req  by Sun
     * @throws Exception
     */
    @Test
    public void testQueryWorkNos() throws Exception {
        System.err.println("start query data from wk table");
//            List<String> bookingNos=Arrays.asList("UFL-BK180011447","UFL-BK180011448","UFL-BK180011450","UFL-BK180011467","UFL-BK180011477","UFL-BK180011478");
//            List<MilestoneInfo> milelist = workTableRepository.findByBookingNoIn(bookingNos);
            TreeSet<String> courierBillNos = passExcel("/home/sea/Downloads/exceptional_milestone_list.xlsx");
            List<MilestoneInfo> milelist = workTableRepository.findByCourierBillNoIn(courierBillNos);
            
          
            Map<String, List<MilestoneInfo>> statusAndDatalistMap = milelist.stream().collect(Collectors.groupingBy(MilestoneInfo::getStatus));
            SXSSFWorkbook workbook = new SXSSFWorkbook(10000);
             // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
            statusAndDatalistMap.forEach((status,milesList)->{
                String sheetName = status;
                SXSSFSheet sheet = workbook.createSheet(sheetName);
                 //写入数据
                setContentForSheet(workbook,sheet,milesList,"this is all status :"+status);
            });
             
//            Workbook workbook = genStatisticsReportHandler.createExcel(statusAndDatalistMap,criteria);
            String filename = "/home/sea/Desktop/lzdworkTB/lkk.xlsx";
            FileOutputStream out = new FileOutputStream(filename);
            workbook.write(out);
            out.close();
            System.err.println(filename);
            System.err.println("end query data from wk table");
            System.err.println("end query data from wk table");
            
        
    }
    
    
    
    
    
    
    /**
      * @Desc generate  one sheet data
      * @param workbook
      * @param sheet
      * @param reportdata
      * @param courier
      */
    private void setContentForSheet(SXSSFWorkbook workbook,SXSSFSheet sheet,List<MilestoneInfo> mileslist  ,String criteria) 
    {    
        //set first 10 column width is 20 
        for(int i=0;i<10;i++){
            sheet.setColumnWidth(i, 252*22+323);//width=22   //设置列宽
        }
        /*&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&" line 1 top  tip  &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&*/
        int contentRow = 0;
        String  tipInfo="the query criteria is "+criteria;
        setCellValue(sheet,contentRow++,0,tipInfo,null);
    /*&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& set  detail info data  start  &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&*/
        int  detailIndex=contentRow+3;
        contentRow++;
        contentRow++;
        contentRow++;
           // Freeze the title row
             /**
              * cellNum:表示要冻结的列数； rowNum:表示要冻结的行数； firstCellNum:表示被固定列上边第一列的列号；
              * firstRollNum :表示被固定行下边第一列的行号;
              */
//        sheet.createFreezePane(0, detailIndex+1, 0, detailIndex+1);
             sheet.setAutoFilter(CellRangeAddress.valueOf("A"+(detailIndex+1)+":F"+(detailIndex+1)));
              // ################# set title  ################
             String[] title = { "BookingNo", " MawbNo "," courierBillNo ", " status","response"};
       // set content
//         milesList.sort(Comparator.comparing(MilestoneInfo::getCreateTimeGmt));
             
             
　　　　　　　　for (int j=0; j<=mileslist.size();j++) 
             //for (MilestoneInfo milestone : mileslist) 
             {
                 Row contentRowline = sheet.createRow(contentRow++);
                 // ################# set title styless ################
                if (j == 0) {
                    for (int titleColumn = 0; titleColumn < title.length; titleColumn++) {
                        Cell titleCell = contentRowline.createCell(titleColumn);
                        titleCell.setCellStyle(getTitleCellStyle(workbook));
                        titleCell.setCellValue(title[titleColumn]);
                    }
                    continue;
                }
                // ################# set title end ################
                //********************* set body content **************************************
　　　　　　　　　　MilestoneInfo milestone = mileslist.get(j-1);

                int i=0;
                contentRowline.createCell(i++).setCellValue(milestone.getBookingNo());
                contentRowline.createCell(i++).setCellValue(milestone.getMawb());
                contentRowline.createCell(i++).setCellValue(milestone.getCourierBillNo());
                contentRowline.createCell(i++).setCellValue(milestone.getStatus());
                contentRowline.createCell(i++).setCellValue(milestone.getRemark());
                //********************* set body content **************************************
            }
    }

  //##############################


/**
* @param <T>
* @param sheet
* @param rownum
* @param cellColNum
* @param cellValue
* @param cellstyle
*/
    private <T> void  setCellValue(SXSSFSheet sheet, int rownum, int cellColNum, T cellValue,
            CellStyle cellstyle) {
        
        SXSSFRow row = sheet.getRow(rownum);
        if(null==sheet.getRow(rownum)){
            row= sheet.createRow(rownum);
        }
        SXSSFCell cell= row.getCell(cellColNum);
         if(null==row.getCell(cellColNum)){
             cell = row.createCell(cellColNum);
         }
        cell.setCellStyle(cellstyle);
        cell.setCellValue(cellValue.toString());
    }

    
    
    public static  CellStyle getTitleCellStyle(Workbook workbook) {
        // 用于格式化单元格的数据
//        DataFormat format = workbook.createDataFormat();
        // 设置字体
        Font font = workbook.createFont();
//      font.setFontHeightInPoints((short) 20); // 字体高度
//      font.setColor(Font.COLOR_RED); // 字体颜色
        font.setFontName("黑体"); // 字体
        font.setBold(true); // 加粗
//      font.setBoldweight(Font.BOLDWEIGHT_BOLD); // 宽度
        font.setItalic(true); // 是否使用斜体
//        font.setStrikeout(true); //是否使用划线
        // 设置单元格类型
        CellStyle titleCellStyle = workbook.createCellStyle();
        titleCellStyle.setBorderBottom(BorderStyle.THIN); // 下边框
        titleCellStyle.setBorderLeft(BorderStyle.THIN);// 左边框
        titleCellStyle.setBorderTop(BorderStyle.THIN);// 上边框
        titleCellStyle.setBorderRight(BorderStyle.THIN);// 右边框
//      titleCellStyle.setFillForegroundColor(HSSFColor.GREEN.index);    //
//      titleCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); //填充形式
        titleCellStyle.setFont(font);
        titleCellStyle.setAlignment(HorizontalAlignment.CENTER); // 水平布局：居中
        titleCellStyle.setWrapText(true);

        return titleCellStyle;
    }
    

 

 

2. 读取模板，填写数据

 

    public XSSFWorkbook genManifestReport(List<SmallPkgDetailOdsDO>  smallPkgs) throws Exception
    {
        InputStream ipts = this.getClass().getClassLoader().getResourceAsStream(MANIFEST_API_TEMPLATE_PATH);
        XSSFWorkbook workbook = new XSSFWorkbook(ipts);
        XSSFSheet sheet = workbook.getSheetAt(0);
        //set value  for  3 行 4 列  date
        sheet.getRow(2).getCell(3).setCellValue(DateFormatUtils.format(System.currentTimeMillis(),TIME_PATTERN, Locale.CHINA));
        int index = 0;
        for (int line = 6; line < smallPkgs.size()+6; line++)
        {
            Row contentRow = sheet.createRow(line);
//            sheet.autoSizeColumn((short) line); // 自动调整该列的宽度
for (int col = 0; col <10 ; col++) {
                contentRow.createCell(col).setCellValue(col+line);
            }
        }
//        FileOutputStream out = new FileOutputStream("seatest1.xlsx");
//        workbook.write(out);
        return  workbook;
    }