java - 如何使用 Java Apache poi 在 Excel 文件中插入多张图片？

Question

我创建了一个java来生成Excel文件，其中的单元格包含图片jpg。逐行显示一张图片仅显示最后一行的最后一张图片

第二部分中我的java编码的一部分

你能帮忙获取所有图片吗？

致以诚挚的问候

<小时/>

    import java.net.URL;
    import java.net.URLConnection;

    import org.apache.poi.hssf.usermodel.*;
    import org.apache.poi.hssf.util.*;
    import org.apache.poi.hssf.usermodel.HSSFClientAnchor;

    @SuppressWarnings("serial")
    public class ExcelCaracAssocModelFileBatch extends AbstractBusinessBatch {

    public int rowIdx = 0;
    public int rowId1 = 0;
    public short cellIdx = 0;
    public short cellId2 = 15;  
    public String _item_codref = "";  /
    private HSSFWorkbook wb = new HSSFWorkbook();
    private HSSFSheet sheet1 = wb.createSheet("CONTEXT");
    private HSSFRow hssfHeader; 
    private HSSFRow hssfHead2;
    private HSSFSheet sheet = wb.createSheet("DATA");
    private HSSFCellStyle cellStyle = wb.createCellStyle();
    private HSSFCellStyle cellStyle2 = wb.createCellStyle();
    public  HSSFRow r2;
    private HSSFPicture my_picture;
    private HSSFCell Celldescarac; 
    private HSSFCell Cellcodcarac;

    public ExcelCaracAssocModelFileBatch(ContexteMetier contexteMetier) {
            super(contexteMetier);
        }

    }


        @SuppressWarnings("deprecation")
        public RowSetStateHolder select(ParameterAccess parameterAccess) throws CoreBatchException {

             HSSFFont font1 = wb.createFont();
                   font1.setFontHeightInPoints((short)14);
                   font1.setFontName("Arial");
                   font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
                   cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                   cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
                   cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                   cellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
                   cellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
                   cellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
                   cellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
                   cellStyle.setFont(font1);

                   cellStyle2.setAlignment(HSSFCellStyle.ALIGN_LEFT);
                   cellStyle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
                   cellStyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
                   cellStyle2.setBorderRight(HSSFCellStyle.BORDER_THIN);
                   cellStyle2.setBorderTop(HSSFCellStyle.BORDER_THIN);
                   cellStyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN);           


            SqlBindingHelper sql = new SqlBindingHelper();

       sql.appendSql("SELECT w_1, w_2, w_3, w_4, w_5, w_6, w_7, w_8, w_9, w_10,");  
                sql.appendSql("w_11, w_12, w_13, w_14, w_15, w_16, w_17, w_18, w_19, w_20,");  
                sql.appendSql("w_21, w_22, w_23, w_24, w_25, w_26, w_27, w_28, w_29, w_30,");  
                sql.appendSql("num01, num02");
                sql.appendSql(" FROM PHX_GDT2_CARAS3 ");  
                sql.appendSql(" WHERE ");
                sql.appendSql(" w_1 = ? "); sql.addParamValue(_w_1); 
                sql.addParamValue( _w_2 );

                  } */

                return createRowSetStateHolderFromSql(sql);
        }


    public long execute(RowSetStateHolder rowSetStateHolder) throws CoreBatchException {

        int cpt = 0;

                   Row row;
                    while(rowSetStateHolder.getRowSet().hasNext())
                    {
                        row = rowSetStateHolder.getRowSet().next();

                   String item_cod = (String)row.getAttribute("w_8");


                   rowIdx = rowId1++;
                   r2 = sheet.createRow(rowIdx);
                   r2.setHeight((short) 1800);


                   HSSFCell r2c0 = r2.createCell((short) 0);
                   r2c0.setCellValue((String)row.getAttribute("w_3"));
                   r2c0.setCellStyle(cellStyle2);
                   HSSFCell r2c1 = r2.createCell((short) 1);
                   r2c1.setCellValue((String)row.getAttribute("w_4"));  
                   r2c1.setCellStyle(cellStyle2);
                   HSSFCell r2c2 = r2.createCell((short) 2);
                   r2c2.setCellValue((String)row.getAttribute("w_5")); 
                   r2c2.setCellStyle(cellStyle2);
                   HSSFCell r2c3 = r2.createCell((short) 3);
                   r2c3.setCellValue((String)row.getAttribute("w_6")); 
                   r2c3.setCellStyle(cellStyle2);
                   HSSFCell r2c4 = r2.createCell((short) 4);
                   r2c4.setCellValue((String) row.getAttribute("w_27"));  
                   r2c4.setCellStyle(cellStyle2);
                   HSSFCell r2c5 = r2.createCell((short) 5);
                   r2c5.setCellValue((String)row.getAttribute("w_18")); 
                   r2c5.setCellStyle(cellStyle2);
                   HSSFCell r2c6 = r2.createCell((short) 6);
                   r2c6.setCellValue((String)row.getAttribute("w_19")); 
                   r2c6.setCellStyle(cellStyle2);
                   HSSFCell r2c7 = r2.createCell((short) 7);
                   r2c7.setCellValue((String)row.getAttribute("w_8")); 
                   r2c7.setCellStyle(cellStyle2);
                   HSSFCell r2c8 = r2.createCell((short) 8);
                   r2c8.setCellValue((String) row.getAttribute("w_9")); 
                   r2c8.setCellStyle(cellStyle2);
                   HSSFCell r2c9 = r2.createCell((short) 9);
                   r2c9.setCellValue((String) row.getAttribute("w_10")); 
                   r2c9.setCellStyle(cellStyle2);
                   HSSFCell r2c10 = r2.createCell((short) 10);
                   r2c10.setCellValue((String) row.getAttribute("w_11")); 
                   r2c10.setCellStyle(cellStyle2);
                   HSSFCell r2c11 = r2.createCell((short) 11);
                   r2c11.setCellValue((String) row.getAttribute("w_12")); 
                   r2c11.setCellStyle(cellStyle2);
                   HSSFCell r2c12 = r2.createCell((short) 12);
                   r2c12.setCellValue((String) row.getAttribute("w_13")); 
                   r2c12.setCellStyle(cellStyle2);



                   String urlmedia = (String) row.getAttribute("w_30"); 
                   try {
                       //test url image 
                       URL url = new URL((String) row.getAttribute("w_30"));
                       URLConnection connection = url.openConnection();
                       int fileLength = connection.getContentLength();

                    // URL ok
                    if (fileLength != -1)
                    {
                        InputStream input = null;
                       input = connection.getInputStream();
                       //Get the contents of an InputStream as a byte[].
                       //byte[] bytes = IOUtils.toByteArray(input);
                       ByteArrayOutputStream img_bytes = new ByteArrayOutputStream();
                        int b;
                        while ((b = input.read()) != -1)
                            img_bytes.write(b);
                        input.close();
                       //Adds a picture to the workbook
                       //int pictureIdx = wb.addPicture(bytes, HSSFWorkbook.PICTURE_TYPE_JPEG);
                       int pictureIdx = wb.addPicture(img_bytes.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG);
                       //close the input stream
                       input.close();

                       /* Create the drawing container */
                       HSSFPatriarch drawing = sheet.createDrawingPatriarch();
                       /* Create an anchor point */

                       /*
                        dx1 - the x coordinate within the first cell.
                            dy1 - the y coordinate within the first cell.
                            dx2 - the x coordinate within the second cell.
                            dy2 - the y coordinate within the second cell.
                            col1 - the column (0 based) of the first cell.
                            row1 - the row (0 based) of the first cell.
                            col2 - the column (0 based) of the second cell.
                            row2 - the row (0 based) of the second cell. 
                        */


                       HSSFClientAnchor my_anchor = new HSSFClientAnchor();
                       my_anchor.setDx1(10);
                       my_anchor.setDx2(10);
                       my_anchor.setDy1(10);
                       my_anchor.setDy2(10);
                       short pictcol1 = (short) rowIdx;
                       short pictcol2 = (short) (pictcol1 + 1);
                       my_anchor.setCol1((short) 13);
                       my_anchor.setCol2((short) 14);
                       my_anchor.setRow1(pictcol1);
                       my_anchor.setRow2(pictcol2);
                       my_anchor.setAnchorType((int) 2);

                       setMy_picture(drawing.createPicture(my_anchor, pictureIdx));

                   }
                   }
                    catch (IOException e)
                    {
                         _log.info("Error while trying to download the file "+ urlmedia, e );
                    }
                         cellIdx = 14;         
                        _item_codref = item_cod;
                   }

                   cellId2 = cellIdx++;
                   HSSFCell r2c4 = r2.createCell(cellId2);
                   r2c4.setCellValue((String)row.getAttribute("w_17")); 
                   r2c4.setCellStyle(cellStyle2);

                   }
                  }

               cpt++;


                    }
            return cpt;
        }

        @Override
        public synchronized void tearDown() throws CoreBatchException {
            try 
            {   
                  //Write the Excel file
                    String nomfic = FILE_NAME_PREFIX + "_" + getCtxMetier().getNumedi() + ".xls";
                    FileOutputStream fileOut = null;
                    fileOut = new FileOutputStream(nomfic);
                    wb.write(fileOut);
                    fileOut.close();                 
            }
                  catch (Exception e) 
                  {
                       _log.error(e);
                      throw new CoreBatchException(e);
                  }

            finally
            {
                super.tearDown();
            }
        }

Answer 1

createDrawingPatriarch method可以具有删除工作表上所有绘图的效果，包括预先存在的图像。

Note that this will normally have the effect of removing any existing drawings on this sheet.

您正在 execute 方法中的 while 循环内调用 createDrawingPatriarch，因此您以前的图像在每次迭代中都会被删除，并且仅最后一张图像幸存下来。

要保持所有图像完好无损，请在 while 循环之前创建一次父对象，并在 while 循环的每次迭代中重用它。

/* Create the drawing container */
HSSFPatriarch drawing = sheet.createDrawingPatriarch();
while(rowSetStateHolder.getRowSet().hasNext())
{
    // Other code...

    // Use it here as normal.
    setMy_picture(drawing.createPicture(my_anchor, pictureIdx));
}