on addding image and text in same cell in excel us

问题:

I am trying to add the image to cell(which is xssfcell inside XSSFSheet) which already contain a text by using an

 anchor.setCol1(1);
     anchor.setRow1(1); 
     anchor.setCol2(2); 
     anchor.setRow2(1); 
     Picture pict = drawing.createPicture(anchor, pictureIdx);
     pict.resize();

but the the image override the text present in that cell. Is there any way to add image with text one after other in single cell without having in multiple line. I have also used autoSizeColumn() method but not worked out.

回答1:

In Excel sheets pictures are not in cells but hovers in a layer over the cells. They are anchored to the cells in the following manner:

A one cell anchor determines the upper left position of the picture. If used, the picture must be resized to its native size.

A two cell anchor determines the upper left position and the size of the picture. The first anchor determines the upper left position while the second anchor determines the bottom right position. So the size is given.

Each anchor can have a row and column given but also a dx and dy. The dx and dy will be added to the column's and row's position to determine the final position. The measurement unit for dx and dy is EMU. Apache poi provides org.apache.poi.util.Units to calculate EMU from pixels for example.

Example:

import java.io.*;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;
import org.apache.poi.util.IOUtils;
import org.apache.poi.util.Units;

public class ExcelDrawImagesOnCellLeft {

 private static void drawImageOnExcelSheet(XSSFSheet sheet, int row, int col, 
  int height, int width, int pictureIdx) throws Exception {

  CreationHelper helper = sheet.getWorkbook().getCreationHelper();

  Drawing drawing = sheet.createDrawingPatriarch();

  ClientAnchor anchor = helper.createClientAnchor();
  anchor.setAnchorType(AnchorType.MOVE_AND_RESIZE);

  anchor.setCol1(col); //first anchor determines upper left position
  anchor.setRow1(row);

  anchor.setRow2(row); //second anchor determines bottom right position
  anchor.setCol2(col);
  anchor.setDx2(Units.toEMU(width)); //dx = left + wanted width
  anchor.setDy2(Units.toEMU(height)); //dy= top + wanted height

  drawing.createPicture(anchor, pictureIdx);

 }

 public static void main(String[] args) throws Exception {
  Workbook wb = new XSSFWorkbook();
  Sheet sheet = wb.createSheet();

  InputStream is = new FileInputStream("samplePict.jpeg");
  byte[] bytes = IOUtils.toByteArray(is);
  int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
  is.close();

  String gap = "      ";

  for (int r = 0; r < 10; r++ ) {
   sheet.createRow(r).createCell(1).setCellValue(gap + "Picture " + (r+1));
   drawImageOnExcelSheet((XSSFSheet)sheet, r, 1, 12, 12, pictureIdx);
  }

  sheet.autoSizeColumn(1);

  wb.write(new FileOutputStream("ExcelDrawImagesOnCellLeft.xlsx"));
  wb.close();
 }
}

Result: