how to get Image and data in same cell in xssfworkbook

Question

I am trying to insert iamge and some data in same cell using apcahe poi xssf work book. I have tried something Like below

        Workbook wb = new XSSFWorkbook();
    XSSFSheet sheet = (XSSFSheet)wb.createSheet();
    Row row = sheet.createRow(rowNum++);
       row.setHeight((short) 1000);
   String logoPath = confBean.getFacilityLogoByfacilityId(usersession);

    /* Read input PNG / JPG Image into FileInputStream Object*/
    InputStream logo = new FileInputStream(logoPath);
    /* Convert picture to be added into a byte array */
    byte[] bytes = IOUtils.toByteArray(logo);
    /* Add Picture to Workbook, Specify picture type as PNG and Get an Index */
    int my_picture_id = wb.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
    /* Close the InputStream. We are ready to attach the image to workbook now */
    logo.close();
    /* Create the drawing container */
    XSSFDrawing drawing = (XSSFDrawing) sheet.createDrawingPatriarch();
    /* Create an anchor point */
    //============= Inserting image - END

    //========adding image START
    XSSFClientAnchor my_anchor = new XSSFClientAnchor();
    /* Define top left corner, and we can resize picture suitable from there */

    my_anchor.setCol1(1); 
    my_anchor.setRow1(1); 
    my_anchor.setCol2(2); 
    my_anchor.setRow2(4); 

    /* Invoke createPicture and pass the anchor point and ID */
    XSSFPicture my_picture = drawing.createPicture(my_anchor, my_picture_id);
    Row row1=sheet.createRow(1);
    row1.setHeight((short) 1000);
    row1.createCell(1).setCellValue(proxy.getAddress()+","+proxy.getCity()+","+proxy.getState()+","+proxy.getCountry()+"-"+proxy.getZip()+".\n Phone:"+proxy.getPhone()+"EMAIL:"+proxy.getEmail());

I am not sure I will get output as shown below, can someone please help.

Axel Richter · Accepted Answer · 2020-04-26T14:22:16.067

Pictures in Excel sheets are not cell contents. They are in a separate layer, called drawing, and are only anchored to the cells of the sheet. So pictures hover over the cells and also over the cell contents. If text and pictures shall not overlap, the text must be placed or aligned accordingly.

So what your screen shot shows is a picture anchored to A1 and resized, so it fits into the first 4 rows in height. For the text, the range A1:H4 is merged. Merged ranges show the content of top left cell. So the text must be set into cell A1. A cell style to set horizontal alignment, vertical alignment and wrap text is needed. This style leads to horizontal and vertical centered text in merged range A1:H4. So the text and the picture do not overlap.

Complete Example:

import java.io.InputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.IOUtils;
import org.apache.poi.util.Units;

class CreateExcelPictureAndText {

 public static void main(String[] args) throws Exception {

  //Workbook workbook = new HSSFWorkbook(); String filePath = "./Excel.xls";
  Workbook workbook = new XSSFWorkbook(); String filePath = "./Excel.xlsx";

  Sheet sheet = workbook.createSheet();
  Row row = null;

  //create cell style horizontal alignment - center, vertical alignment - center, wrap text
  CellStyle cellStyle = workbook.createCellStyle();
  cellStyle.setAlignment(HorizontalAlignment.CENTER);
  cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
  cellStyle.setWrapText(true);

  //insert picture's media into workbook
  InputStream inputStream = new FileInputStream("./logo.png");
  byte[] imageBytes = IOUtils.toByteArray(inputStream);
  int pictureureIdx = workbook.addPicture(imageBytes, Workbook.PICTURE_TYPE_PNG);
  inputStream.close();

  //insert picture anchored over the cells of the sheet
  CreationHelper helper = workbook.getCreationHelper();
  Drawing drawing = sheet.createDrawingPatriarch();
  ClientAnchor anchor = helper.createClientAnchor();
  anchor.setCol1(0); //col A
  anchor.setRow1(0); //row 1
  Picture pict = drawing.createPicture(anchor, pictureureIdx);
  pict.resize(); //now picture is anchored at A1 and sized to it's original size

  //get picture's original size
  int pictOriginalWidthInPixels = pict.getImageDimension().width;
  int pictOriginalHeightInPixels = pict.getImageDimension().height;

  //get height of row 1 to 4
  float rowHeightInPixels = 0f;
  for (int r = 0; r < 4; r++) {
   row = sheet.getRow(r); if (row == null) row = sheet.createRow(r);
   float rowHeightInPoints = row.getHeightInPoints(); 
   rowHeightInPixels += rowHeightInPoints * Units.PIXEL_DPI / Units.POINT_DPI;
  }
  //we want scaling in aspect ratio
  float scale = rowHeightInPixels / pictOriginalHeightInPixels;
  pict.resize(scale, scale); //now picture is resized to fit into the first 4 rows

  //create merged cells for heading
  sheet.addMergedRegion(new CellRangeAddress(0,3,0,7)); //merged region A1:H4

  //set text for merged region in A1
  row = sheet.getRow(0);
  Cell cell = row.createCell(0);
  cell.setCellValue("Golden Heights, 9/1, sector 3, Huda Techno Enclave,\n"
                   +"Madhapur (HITEC city), Hyderabad, Telangana - 500 081, India.\n"
                   +"Phone: 91 40.23116868 Email: info@sysintelli.com"); 
  cell.setCellStyle(cellStyle);

  //set column widths
  for (int c = 0; c < 8; c++) {
   sheet.setColumnWidth(c, 15*256); //column width 15 default character widths
  }

  FileOutputStream out = new FileOutputStream(filePath);
  workbook.write(out);
  out.close();
  workbook.close();

 }
}

Result:

Thanks @Axel Richter for sharing all the knowledge, this is the exact result I am lookin for — phani kumar, Apr 26 '20 at 15:50

how to get Image and data in same cell in xssfworkbook

1 Answers1