【无标题】JAVA利用POI插入Excel图片

最近遇到导出特定格式的Excel，这个方法可以实现特定的Excel格式，直接上代码：

        <!-- POI -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.16</version>
        </dependency>

 public static void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //创建HSSFWorkbook对象
        HSSFWorkbook wb = new HSSFWorkbook();
        //创建一个字体
        Font font=wb.createFont();
//        font.setFontHeightInPoints((short) 24);
        font.setFontName("宋体");
        font.setItalic(true);
        font.setStrikeout(true);

        CellStyle style=wb.createCellStyle();
        style.setFont(font);
        style.setWrapText(true);


        //建立sheet对象
        HSSFSheet sheet=wb.createSheet("成绩表");
        //在sheet里创建第一行，参数为行索引
        HSSFRow row1=sheet.createRow(0);
        //创建单元格
        HSSFCell cell=row1.createCell(0);
        //设置单元格内容
        cell.setCellValue("学生成绩表");
        cell.setCellStyle(style);
        //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
        sheet.addMergedRegion(new CellRangeAddress(0,2,0,6));



        //在sheet里创建第二行
        HSSFRow row2=sheet.createRow(1);
        //创建单元格并设置单元格内容
        row2.createCell(0).setCellValue("姓名");
        row2.createCell(1).setCellValue("班级");
        row2.createCell(2).setCellValue("语文成绩");
        row2.createCell(3).setCellValue("数学成绩");
        row2.createCell(4).setCellValue("英语成绩");
        row2.setRowStyle(style);

        //在sheet里创建第三行
        HSSFRow row3=sheet.createRow(2);
        row3.createCell(0).setCellValue("小明");
        row3.createCell(1).setCellValue("1班");
        row3.createCell(2).setCellValue(80);
        row3.createCell(3).setCellValue(75);
        row3.createCell(4).setCellValue(88);
        HSSFRow row4=sheet.createRow(3);
        row4.createCell(0).setCellValue("小红");
        row4.createCell(1).setCellValue("1班");
        row4.createCell(2).setCellValue(82);
        row4.createCell(3).setCellValue(70);
        row4.createCell(4).setCellValue(90);

        HSSFSheet sheet2=wb.createSheet("图片");
        HSSFRow row = sheet2.createRow(0);
        sheet2.setColumnWidth(0, 10000);
        //自定义行高
//        row.setHeight((short)1000);
        HSSFCell cell1 = row.createCell(0);
        cell1.setCellValue("图片");
        String picturePath ="C:\\Users\\admin\\Desktop\\e.jpg";
        List<String> paths = new ArrayList<>();
        paths.add("C:\\Users\\admin\\Desktop\\e.jpg");
        paths.add("C:\\Users\\admin\\Desktop\\e.jpg");
        paths.add("C:\\Users\\admin\\Desktop\\e.jpg");
//        paths.add("C:\\Users\\admin\\Desktop\\2.webp");
        int row22 = 14;
        int row11 = 1;
        for (String path : paths) {
            Excelutil2.writePicture(wb,sheet2,sheet2.createDrawingPatriarch(),path,0,row11,1,row22);
            row22+=14;
            row11+=15;
        }


        //输出Excel文件
        OutputStream output=response.getOutputStream();
        response.reset();
        //设置响应头，
        response.setHeader("Content-disposition", "attachment; filename=Student.xls");
        response.setContentType("application/msexcel");
        try {
            wb.write(output);
            output.close();
        } catch (IOException e) {
            e.printStackTrace();
        }


    }

工具类：

public static  void main(String[] args) {
            String picturePath ="C:\\Users\\admin\\Desktop\\e.jpg";
            try {
                HSSFWorkbook wb = new HSSFWorkbook();
                HSSFSheet sheet1 = wb.createSheet("test picture");
                //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
                HSSFPatriarch patriarch = sheet1.createDrawingPatriarch();
//		插入图片
//                writePicture(wb, sheet1,patriarch, picturePath,3,3);
//                writePicture(wb, sheet1,patriarch, picturePath,3,4);
//                writePicture(wb, sheet1,patriarch, picturePath,3,5);
                FileOutputStream fileOut;
                fileOut = new FileOutputStream("C:\\Users\\admin\\Desktop\\测试Excel.xls");
                wb.write(fileOut);
                System.out.println("----Excle文件已生成------");
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        /**
         *
         * @param wb 文档对象
         * @param sheet1 sheet页
         * @param patriarch 图片对象
         * @param picturePath 图片路径
         * @param index 纵坐标
         * @param index1 横坐标
         */
        public static void writePicture(HSSFWorkbook wb, HSSFSheet sheet1, HSSFPatriarch patriarch, String picturePath, int index, int index1,int row1,int row2) {
            FileOutputStream fileOut = null;
            BufferedImage bufferImg = null;
            //先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
            try {
                ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
                bufferImg = ImageIO.read(new File(picturePath));
                //图片后缀
                String pictureType = picturePath.substring(picturePath.lastIndexOf(".")+1);
                ImageIO.write(bufferImg, pictureType, byteArrayOut);
                //anchor主要用于设置图片的属性
                HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255,(short) index, index1, (short) row1, row2);
//                anchor.setAnchorType(3);
                //插入图片
                patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
            } catch (Exception e) {
                e.printStackTrace();
            }finally{
                if(fileOut != null){
                    try {
                        fileOut.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }
        }

效果图：

注：目前response响应头会有跨域问题，改一个允许跨域的响应头即可。