利用poi修改excel模板内容并下载

需要用到的包，如果poi版本较低，sheet.shiftRows和sheet.createDrawingPatriarch方法可能会报错

<dependency>
     <groupId>org.apache.poi</groupId>
     <artifactId>poi</artifactId>
     <version>3.10.1</version>
</dependency>

主方法入口，excelDataMap为封装好的需要替换excel表内容的数据

try {
      InputStream input = this.getClass().getResourceAsStream("/excel_templates/B版.xls");//excel模板
      excelManager.repleace_excel_content2(excelDataMap,input, output);//替换excel模板内容
} catch (Exception e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
}

替换方法

public void repleace_excel_content2(Map<String,Object> data_map,InputStream input,OutputStream output) throws Exception {
        //InputStreamCacher(input);
        if(data_map == null || data_map.isEmpty()) {
            throw new OperationDeniedException("导出数据错误,数据集为null或为空!");
        }
        byte[] buf = IOUtils.toByteArray(input);//读取流内容
        HSSFWorkbook workBook = new HSSFWorkbook(new ByteArrayInputStream(buf));//获取HSSFWorkbook
        deal_shell4(workBook,data_map); // 开始处理第一个shell
        try {
            workBook.write(output);//导出替换完内容的excel
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

public void deal_shell4(HSSFWorkbook workBook,Map<String,Object> data_map) {
        // 自定义样式
        CellStyle style = workBook.createCellStyle();
        // 设置边框
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置字体
        Font font = workBook.createFont();
        font.setColor(HSSFColor.BLACK.index);
        font.setFontHeightInPoints((short) 9);
        //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        style.setFont(font); // 把字体应用到当前的样式
        
        // 读取sheet
         HSSFSheet sheet = workBook.getSheetAt(0);
        // 总列
        int colNum = 0;
        // 开始列
        int startCol = 0;
        // head数据，为excel表头描述跟字段的对应关系如：map.put("计费日期","amtTime")
        Map<String,String> head_map = (Map<String, String>) data_map.get("head");
        // 列表数据
        List<Map<String,Object>> list_map = (List<Map<String, Object>>) data_map.get("list");
        // 总行
        int rowNum = list_map.size();
        Integer[] replaceRowArr = new Integer[] {2,3,13};
        for(Integer index : replaceRowArr) {
            Row row = sheet.getRow(index);
            if (row != null) {
                colNum = sheet.getRow(2).getPhysicalNumberOfCells(); // 第一行(标题行为总长度)
                for (int j = startCol; j < colNum; j++) {
                    Cell cell = row.getCell(j);
                    if (cell != null) {
                        String cellValue = getCellValue(cell) == null ? "" : getCellValue(cell);
                        if(StringUtils.isBlank(cellValue)) continue;
                        for(String key : data_map.keySet()) {
                            if(cellValue.contains(key)) {
                                cell.setCellValue(cellValue.replace(key, data_map.get(key).toString()));//替换每个cell的关键字
                            }
                        }
                    }
                }
            }
        }
        sheet.shiftRows(5,rowNum+6,rowNum,false,false,true);//根据列表内容插入行
        // 获取列表标题行
        Row head_row = sheet.getRow(4);
        Map<Integer,String> head_index_Map = new HashMap<>();
        int head_colNum = head_row.getPhysicalNumberOfCells(); // 第一行(标题行为总长度)
        for (int j = startCol; j < head_colNum; j++) {
            Cell cell = head_row.getCell(j);
            head_index_Map.put(j, cell.getStringCellValue());//每个列表cell对应的excel表头描述，用于后面列表取值
        }
        if (rowNum > 0) {
            for (int i = 5; i < rowNum+5; i++) {
                // 获取一行
                Row row = sheet.createRow(i);
                Map<String,Object> list_data_map = list_map.get(i-5);
                if (row != null) {
                    colNum = sheet.getRow(4).getPhysicalNumberOfCells(); // 第一行(标题行为总长度)
                    for (int j = startCol; j < colNum; j++) {
                        Cell cell = row.createCell(j);
                        cell.setCellStyle(style);
                        if(j == 0) {
                            cell.setCellValue(i-1);
                        }else {
                            cell.setCellValue(StringToolUtils.get_if_null_str(list_data_map.get(head_map.get(head_index_Map.get(j)))));//取值，默认为空
                        }
                    }
                }
            }
            //合并单元格
            CellRangeAddress bottom = new CellRangeAddress(rowNum+4,rowNum+4,1,4);
            sheet.addMergedRegion(bottom);
        }
        //插入图片 chapter
        try {
            InputStream input = this.getClass().getResourceAsStream("/excel_templates/code.png");//图片源
            ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream(); //创建字节数组输出流
            BufferedImage bufferImg = ImageIO.read(input);//将图片加载到内存当中
            ImageIO.write(bufferImg, "png", byteArrayOut);//将图片写入到字节数组输出流
            Drawing patriarch = sheet.createDrawingPatriarch(); //获取图片管理器
            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0,(short) 3, rowNum+6, (short) 4, rowNum+10);//设置图片位置
            patriarch.createPicture(anchor, workBook.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));//绘图
            
            InputStream input2 = this.getClass().getResourceAsStream("/excel_templates/chapter.png");
            ByteArrayOutputStream byteArrayOut2 = new ByteArrayOutputStream(); 
            BufferedImage bufferImg2 = ImageIO.read(input2);
            ImageIO.write(bufferImg2, "png", byteArrayOut2);
            Drawing patriarch2 = sheet.createDrawingPatriarch(); 
            HSSFClientAnchor anchor2 = new HSSFClientAnchor(0, 0, 0, 0,(short) 5, rowNum+10, (short) 7, rowNum+18);
            patriarch2.createPicture(anchor2, workBook.addPicture(byteArrayOut2.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } 
    }