######POI导出：现有模板的任意位置 追加Cell区域。【同事写的ok案例】

==========Java代码：

/**
     * 导出排期模板
     * @param scheduleDTO
     */
    @RequestMapping("exportSchedule")
    public void exportSchedule(ScheduleDTO scheduleDTO,HttpServletRequest request,HttpServletResponse response)throws IOException{
        Campaign campaign = campaignService.findCampaignDeletedById(scheduleDTO.getCampaignId());
        List<OrderItemDTO> listInfo = adOrderItemService.selectOrderItemByCampaId(scheduleDTO.getCampaignId());
        setDicDataInfo(listInfo);
        List<DictDO> mediaTypeNameDictDOList = dictService.selectDictSet("media_type");
        //每个订单项对应的排期
        Map<Integer, List<AdScheduleUnitDTO>> adScheduleUnitDOListMap = new HashedMap();
        for (OrderItemDTO orderItemDTO : listInfo) {
            for(int i=0;i<mediaTypeNameDictDOList.size();i++){
                if(orderItemDTO.getMediaType().equals(mediaTypeNameDictDOList.get(i).getDictKey())){
                    orderItemDTO.setMediaTypeName(mediaTypeNameDictDOList.get(i).getDictValue());
                }
            }
            adScheduleUnitDOListMap.put(orderItemDTO.getId(), adScheduleUnitService.listAdScheduleUnitDTOByOrderItemId(orderItemDTO.getId()));
        }

        //Map，key为Excel中对应的列, value为项目开始日期到结束日期
        Map<Integer, String> dateMap = new LinkedHashMap<>();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(campaign.getStartDate());
        //日期开始的列数
        Integer column = 21;
        while (calendar.getTime().before(campaign.getEndDate()) || calendar.getTime().equals(campaign.getEndDate())) {
            String str = sdf.format(calendar.getTime());
            dateMap.put(column++, str);
            calendar.add(Calendar.DATE, 1);
        }

        File newFile = DownloadFileUtil.createNewFile(request
                ,"\\resource\\template\\schedule\\TD排期模板_new_1.xlsx"
                ,"\\resource\\template\\schedule\\temp\\",campaign.getName());
        XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(newFile));
        setRow(workbook,listInfo,campaign,scheduleDTO.getId(),adScheduleUnitDOListMap,dateMap);
        Date d =new Date();
        String xlsName=String.valueOf(campaign.getName()+"_Spotplan"  +".xlsx");
        DownloadFileUtil.downloadXLSFile(request, response, workbook, xlsName);
        DownloadFileUtil.deleteFile(newFile);//===》导出完毕，删除临时生成的Excel文件。减轻项目多余文件。
    } 

/**
     * 填充数据
     * @param workbook
     * @param listInfo
     */
    public void setRow(XSSFWorkbook workbook,List<OrderItemDTO> listInfo,Campaign campaign,Integer id,Map<Integer, List<AdScheduleUnitDTO>> adScheduleUnitDOListMap, Map<Integer, String> dateMap){
        XSSFSheet sheet = workbook.getSheetAt(0);
        XSSFCellStyle style = workbook.createCellStyle();
        XSSFFont font = workbook.createFont();
        //单元格样式
        setCellStyle(style,font);
        //设置表头
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
        XSSFRow row_0 = sheet.getRow(0);
        row_0.createCell(5).setCellValue(listInfo.get(0).getAdvertiserName());//填充广告主名。
        XSSFRow row_1 = sheet.getRow(1);
        row_1.createCell(5).setCellValue(campaign.getName());
        XSSFRow row_2 = sheet.getRow(2);
        row_2.createCell(5).setCellValue(sdf.format(campaign.getStartDate()));
        XSSFRow row_3 = sheet.getRow(3);
        row_3.createCell(5).setCellValue(sdf.format(campaign.getEndDate()));
        XSSFRow row_4 = sheet.getRow(4);
        row_4.createCell(5).setCellValue(id);//====》前五行   数据+样式填充完毕

        //生成日期。排期。
        XSSFRow row_5 = sheet.getRow(5);
        XSSFRow row_6 = sheet.getRow(6);
        Iterator it = dateMap.entrySet().iterator();
        while (it.hasNext()) {
            Map.Entry entry = (Map.Entry) it.next();
            XSSFCell cellInfo_5 = row_5.createCell(((Integer) entry.getKey()).intValue());
            XSSFCell cellInfo = row_6.createCell(((Integer) entry.getKey()).intValue());
            cellInfo.setCellValue(((String)entry.getValue()));
            cellInfo.setCellStyle(style);
            cellInfo_5.setCellStyle(style);
        }
        sheet.addMergedRegion(new CellRangeAddress(5,5,21,dateMap.size()+21));
        row_5.getCell(21).setCellValue("排期");
        row_5.getCell(21).setCellStyle(style);//====》排期表头   数据+样式填充完毕

        //设置Excel表体
        for(int i=0; i<listInfo.size();i++){
            XSSFRow row = sheet.createRow(i+7);
            row.createCell(0).setCellValue(listInfo
                    .get(i).getMediaTypeName()==null?"":listInfo.get(i).getMediaTypeName());
            row.createCell(1).setCellValue(listInfo
                    .get(i).getMediaName()==null?"":listInfo.get(i).getMediaName());
            row.createCell(2).setCellValue(listInfo
                    .get(i).getChannelType()==null?"":listInfo.get(i).getChannelType());
            row.createCell(3).setCellValue(listInfo
                    .get(i).getSlotName()==null?"":listInfo.get(i).getSlotName());
            row.createCell(4).setCellValue(listInfo
                    .get(i).getStandard()==null?"":listInfo.get(i).getStandard());
            row.createCell(5).setCellValue(listInfo
                    .get(i).getOrderItemTypeValue()=="-1"?"":listInfo.get(i).getOrderItemTypeValue());
            row.createCell(6).setCellValue(listInfo
                    .get(i).getRateCardPrice()==null?"":listInfo.get(i).getRateCardPrice().toString());
            row.createCell(7).setCellValue(listInfo.get(i)
                    .getSettleTypeValue()=="-1"?"":listInfo.get(i).getSettleTypeValue());
            if(1==listInfo.get(i).getSettleType()){
                row.createCell(8).setCellValue(listInfo
                        .get(i).getQuantity()==(-1)?0:listInfo.get(i).getQuantity()/1000);
            } else {
                row.createCell(8).setCellValue(listInfo
                        .get(i).getQuantity()==(-1)?0:listInfo.get(i).getQuantity());
            }
            row.createCell(9).setCellValue(listInfo
                    .get(i).getNetPrice()==null?"":listInfo.get(i).getNetPrice().toString());
            long quantity = 0;
            if(1==listInfo.get(i).getSettleType()){
                quantity = listInfo.get(i).getQuantity()/1000;
            } else{
                quantity = listInfo.get(i).getQuantity();
            }
            BigDecimal quantityBigDic = new BigDecimal(quantity);
            if(null == quantityBigDic){
                quantityBigDic = new BigDecimal(0);
            }
            BigDecimal rateCardPriceInfo = listInfo.get(i).getRateCardPrice();
            if(null == rateCardPriceInfo){
                rateCardPriceInfo = new BigDecimal(0);
            }
            row.createCell(10).setCellValue(((rateCardPriceInfo).multiply(quantityBigDic)).toString());
            row.createCell(11).setCellValue(listInfo
                    .get(i).getOrderItemDiscountRate()==null?"":listInfo.get(i).getOrderItemDiscountRate().toString());
            row.createCell(12).setCellValue(listInfo
                    .get(i).getBudget()==null?"":listInfo.get(i).getBudget().toString());
            row.createCell(13).setCellValue(listInfo
                    .get(i).getBonusRate()==null?"":listInfo.get(i).getBonusRate().toString());
            
            row.createCell(14).setCellValue(listInfo
                    .get(i).getImpression()==null?0:listInfo.get(i).getImpression());
            row.createCell(15).setCellValue(listInfo
                    .get(i).getClick()==null?0:listInfo.get(i).getClick());
            row.createCell(16).setCellValue(listInfo.get(i)
                    .getTotalImpression()==null?0:listInfo.get(i).getTotalImpression());
            row.createCell(17).setCellValue(listInfo
                    .get(i).getTotalClick()==null?0:listInfo.get(i).getTotalClick());
            row.createCell(18).setCellValue(listInfo
                    .get(i).getCTR()==null?0:listInfo.get(i).getCTR());
            row.createCell(19).setCellValue(listInfo
                    .get(i).getCPM()==null?0:listInfo.get(i).getCPM());
            row.createCell(20).setCellValue(listInfo
                    .get(i).getCPC()==null?0:listInfo.get(i).getCPC());//===》普通数据 填充完毕。【普通数据表头 是模板自带,不用填充】

            //填充排期
            List<AdScheduleUnitDTO> adScheduleUnitDTOList = adScheduleUnitDOListMap.get(listInfo.get(i).getId());
            for (AdScheduleUnitDTO adScheduleUnitDTO : adScheduleUnitDTOList) {
                if (adScheduleUnitDTO.getDate() != null) {
                    Date date = adScheduleUnitDTO.getDate();
                    String dateStr = sdf.format(date);
                    Integer column = getKey(dateMap, dateStr);
                    if (column != null) {
                        if(1==adScheduleUnitDTO.getSettleTypeId()){
                            row.createCell(column).setCellValue(adScheduleUnitDTO.getQuantity()/1000);
                        } else {
                            row.createCell(column).setCellValue(adScheduleUnitDTO.getQuantity());
                        }
                    }
                }
            }//===》  排期数据 填充完毕。

        }//====》第七行   数据+样式填充完毕
    }



    /**
     * 根据map的value获取map的key
     * @param map
     * @param value
     * @return
     */
    public Integer getKey(Map<Integer,String> map,String value){
        Integer key = null;
        for (Map.Entry<Integer, String> entry : map.entrySet()) {
            if (value.equals(entry.getValue())) {
                key = entry.getKey();
            }
        }
        return key;
    }

    /**
     * 设置单元格样式
     * @param style
     */
    public void setCellStyle(XSSFCellStyle style,XSSFFont font){
        style.setFillBackgroundColor(IndexedColors.RED.getIndex());
        style.setFillForegroundColor(IndexedColors.TAN.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.CENTER);
        font.setFontName("微软雅黑");
        font.setFontHeightInPoints((short) 12);
        style.setFont(font);
    }

/**
 * 获取单元格
 * @param row
 * @param index
 * @return
 */
public XSSFCell getCell(XSSFRow row, int index) {
    // 取得分发日期单元格
    XSSFCell cell = row.getCell(index);
    // 如果单元格不存在
    if (cell == null) {
        // 创建单元格
        cell = row.createCell(index);
    }
    return cell;
}

======效果：

模板：

导出效果：

1、

2、排期部分。###动态添加区域。 新get的POI导出Excel 知识点。

=====补充：DownloadFileUtil代码：

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;

public class DownloadFileUtil {


    /**
     * 浏览器下载
     * @param request   请求
     * @param response  响应
     * @param workbook  excel对象
     * @param fileName  文件名称
     */
    public static void downloadXLSFile(HttpServletRequest request, HttpServletResponse response, XSSFWorkbook workbook, String fileName) {
        OutputStream os = null;
        BufferedOutputStream bos = null;
        try {
            //重点突出(特别注意),通过response获取的输出流，作为服务端往客户端浏览器输出内容的一个通道
            os = response.getOutputStream();
            bos = new BufferedOutputStream(os);
            // 处理下载文件名的乱码问题(根据浏览器的不同进行处理)
            if (request.getHeader("User-Agent").toLowerCase().indexOf("firefox") > 0) {
                fileName = new String(fileName.getBytes("GB2312"),"ISO-8859-1");
            } else {
                // 对文件名进行编码处理中文问题
                // 处理中文文件名的问题
                fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
                // 处理中文文件名的问题
                fileName = new String(fileName.getBytes("UTF-8"), "GBK");
            }
            response.reset();
            response.setCharacterEncoding("UTF-8");
            // 不同类型的文件对应不同的MIME类型
            response.setContentType("application/x-msdownload");
            // inline在浏览器中直接显示，不提示用户下载
            // attachment弹出对话框，提示用户进行下载保存本地
            // 默认为inline方式
            response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
            //  response.setHeader("Content-Disposition", "attachment; filename="+fileName); // 重点突出
            workbook.write(bos);
        } catch (Exception ex) {
            throw new RuntimeException(ex.getMessage());
        } finally {
            // 特别重要
            // 1. 进行关闭是为了释放资源
            // 2. 进行关闭会自动执行flush方法清空缓冲区内容
            try {
                if (null != bos) {
                    bos.close();
                    bos = null;
                }
                if (null != os) {
                    os.close();
                    os = null;
                }
            } catch (Exception ex) {
                throw new RuntimeException(ex.getMessage());
            }
        }
    }

    /**
     * 读取excel模板，并复制到新文件中供写入和下载
     * @param request
     * @param filename
     * @return
     */
    public static File createNewFile(HttpServletRequest request, String templateUrl,String tempUrl, String filename){
        //读取模板，并赋值到新文件
        //文件模板路径
        String path = request.getSession().getServletContext().getRealPath("/");
        //String fileName="\\resource\\template\\schedule\\TD排期模板.xlsx";
        String fileName = templateUrl;
        File file=new File(path+"/"+fileName);
        //保存文件的路径
        String realPath = request.getSession().getServletContext().getRealPath("/") + tempUrl;
        //新的文件名
        String newFileName = filename + System.currentTimeMillis() + ".xlsx";
        //判断路径是否存在
        File dir = new File(realPath);
        if(!dir.exists()){
            dir.mkdirs();
        }
        //写入到新的excel
        File newFile = new File(realPath, newFileName);
        try {
            newFile.createNewFile();
            //复制模板到新文件
            DownloadFileUtil.fileChannelCopy(file, newFile);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return newFile;
    }

    /**
     * 复制文件
     * @param s 旧文件
     * @param t 新文件
     */
    public static void fileChannelCopy(File s, File t) {
        try {
            InputStream in = null;
            OutputStream out = null;
            try {
                in = new BufferedInputStream(new FileInputStream(s),1024);
                out = new BufferedOutputStream(new FileOutputStream(t),1024);
                byte[] buffer = new byte[1024];
                int len;
                while ((len=in.read(buffer))!=-1) {
                    out.write(buffer,0,len);
                }
            } finally {
                if (null != in) {
                    in.close();
                }
                if (null != out) {
                    out.close();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 下载成功后删除
     * @param files
     */
    public static void deleteFile(File... files) {
        for (File file : files) {
            if (file.exists()) {
                file.delete();
            }
        }
    }



}

===模板文件位置：永久牌链接。

https://pan.baidu.com/s/1813kDuRbKpNZwPcSSVkn-A

=======