org.apache.poi.ss.usermodel.Drawing Java Examples

private void setImageValue(CellData cellData, Cell cell) {
    Sheet sheet = cell.getSheet();
    int index = sheet.getWorkbook().addPicture(cellData.getImageValue(), HSSFWorkbook.PICTURE_TYPE_PNG);
    Drawing drawing = sheet.getDrawingPatriarch();
    if (drawing == null) {
        drawing = sheet.createDrawingPatriarch();
    }
    CreationHelper helper = sheet.getWorkbook().getCreationHelper();
    ClientAnchor anchor = helper.createClientAnchor();
    anchor.setDx1(0);
    anchor.setDx2(0);
    anchor.setDy1(0);
    anchor.setDy2(0);
    anchor.setCol1(cell.getColumnIndex());
    anchor.setCol2(cell.getColumnIndex() + 1);
    anchor.setRow1(cell.getRowIndex());
    anchor.setRow2(cell.getRowIndex() + 1);
    anchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);
    drawing.createPicture(anchor, index);
}
 

/**
 * 创建List之后的各个Cells
 * @param styles 
 */
private static void createListCells(Drawing patriarch, int index, int cellNum, Object obj,
		List<ExcelExportEntity> excelParams, Sheet sheet,
		HSSFWorkbook workbook, Map<String, HSSFCellStyle> styles) throws Exception {
	ExcelExportEntity entity;
	Row row;
	if(sheet.getRow(index)==null){
		row = sheet.createRow(index);
		row.setHeight((short) 350);
	}else{
		row = sheet.getRow(index);
	}
	for (int k = 0, paramSize = excelParams.size(); k < paramSize; k++) {
		entity = excelParams.get(k);
		Object value = getCellValue(entity,obj);
		if (entity.getType() == 1) {
			createStringCell(row, cellNum++,
					value == null ? "" : value.toString(),
							row.getRowNum() % 2 == 0 ?  getStyles(styles, false, entity.isWrap())
									: getStyles(styles,true, entity.isWrap()),entity);
		}else {
			createImageCell(patriarch,entity, row, cellNum++, value == null ? ""
					: value.toString(),obj);
		}
	}
}
 

/**
 * 创建List之后的各个Cells
 * 
 * @param styles
 */
public void createListCells(Drawing patriarch, int index, int cellNum, Object obj, List<ExcelExportEntity> excelParams, Sheet sheet, Workbook workbook) throws Exception {
	ExcelExportEntity entity;
	Row row;
	if (sheet.getRow(index) == null) {
		row = sheet.createRow(index);
		row.setHeight(getRowHeight(excelParams));
	} else {
		row = sheet.getRow(index);
	}
	for (int k = 0, paramSize = excelParams.size(); k < paramSize; k++) {
		entity = excelParams.get(k);
		Object value = getCellValue(entity, obj);
		if (entity.getType() == 1) {
			createStringCell(row, cellNum++, value == null ? "" : value.toString(), row.getRowNum() % 2 == 0 ? getStyles(false, entity) : getStyles(true, entity), entity);
		} else if (entity.getType() == 4){
			createNumericCell(row, cellNum++, value == null ? "" : value.toString(), index % 2 == 0 ? getStyles(false, entity) : getStyles(true, entity), entity);
		}  else{
			createImageCell(patriarch, entity, row, cellNum++, value == null ? "" : value.toString(), obj);
		}
	}
}
 

/**
 * 创建List之后的各个Cells
 * 
 * @param styles
 */
private static void createListCells(Drawing patriarch, int index,
		int cellNum, Object obj, List<ExcelExportEntity> excelParams,
		Sheet sheet, Workbook workbook) throws Exception {
	ExcelExportEntity entity;
	Row row;
	if (sheet.getRow(index) == null) {
		row = sheet.createRow(index);
		row.setHeight((short) 350);
	} else {
		row = sheet.getRow(index);
	}
	for (int k = 0, paramSize = excelParams.size(); k < paramSize; k++) {
		entity = excelParams.get(k);
		Object value = getCellValue(entity, obj);
		if (entity.getType() != 2) {
			createStringCell(row, cellNum++,
					value == null ? "" : value.toString(), entity, workbook);
		} else {
			createImageCell(patriarch, entity, row, cellNum++,
					value == null ? "" : value.toString(), obj, workbook);
		}
	}
}
 

/**
 * 往Sheet 填充正常数据
 * 
 * @param params
 * @param pojoClass
 * @param dataSet
 * @param workbook
 */
private static void addDataToSheet(TemplateExportParams params,
		Class<?> pojoClass, Collection<?> dataSet, Sheet sheet,
		Workbook workbook) throws Exception {
	Drawing patriarch = sheet.createDrawingPatriarch();
	List<ExcelExportEntity> excelParams = new ArrayList<ExcelExportEntity>();
	// 得到所有字段
	Field fileds[] = ExcelPublicUtil.getClassFields(pojoClass);
	ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class);
	String targetId = null;
	if (etarget != null) {
		targetId = etarget.id();
	}
	getAllExcelField(targetId, fileds, excelParams, pojoClass, null);
	sortAllParams(excelParams);
	Iterator<?> its = dataSet.iterator();
	int index = sheet.getLastRowNum();
	while (its.hasNext()) {
		Object t = its.next();
		index += createCells(patriarch, index, t, excelParams, sheet,
				workbook);
	}
}
 

/**
 * 创建List之后的各个Cells
 * 
 * @param styles
 */
public void createListCells(Drawing patriarch, int index, int cellNum, Object obj,
                            List<ExcelExportEntity> excelParams, Sheet sheet, Workbook workbook)
                                                                                                throws Exception {
    ExcelExportEntity entity;
    Row row;
    if (sheet.getRow(index) == null) {
        row = sheet.createRow(index);
        row.setHeight(getRowHeight(excelParams));
    } else {
        row = sheet.getRow(index);
    }
    for (int k = 0, paramSize = excelParams.size(); k < paramSize; k++) {
        entity = excelParams.get(k);
        Object value = getCellValue(entity, obj);
        if (entity.getType() == 1) {
            createStringCell(row, cellNum++, value == null ? "" : value.toString(),
                row.getRowNum() % 2 == 0 ? getStyles(false, entity) : getStyles(true, entity),
                entity);
        } else {
            createImageCell(patriarch, entity, row, cellNum++,
                value == null ? "" : value.toString(), obj);
        }
    }
}
 

/**
 * 往Sheet 填充正常数据,根据表头信息 使用导入的部分逻辑,坐对象映射
 * 
 * @param teplateParams
 * @param pojoClass
 * @param dataSet
 * @param workbook
 */
private void addDataToSheet(Class<?> pojoClass, Collection<?> dataSet, Sheet sheet, Workbook workbook) throws Exception {

	if (workbook instanceof XSSFWorkbook) {
		super.type = ExcelType.XSSF;
	}
	// 获取表头数据
	Map<String, Integer> titlemap = getTitleMap(sheet);
	Drawing patriarch = sheet.createDrawingPatriarch();
	// 得到所有字段
	Field[] fileds = PoiPublicUtil.getClassFields(pojoClass);
	ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class);
	String targetId = null;
	if (etarget != null) {
		targetId = etarget.value();
	}
	// 获取实体对象的导出数据
	List<ExcelExportEntity> excelParams = new ArrayList<ExcelExportEntity>();
	getAllExcelField(null, targetId, fileds, excelParams, pojoClass, null);
	// 根据表头进行筛选排序
	sortAndFilterExportField(excelParams, titlemap);
	short rowHeight = getRowHeight(excelParams);
	int index = teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(), titleHeight = index;
	// 下移数据,模拟插入
	sheet.shiftRows(teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(), sheet.getLastRowNum(), getShiftRows(dataSet, excelParams), true, true);
	if (excelParams.size() == 0) {
		return;
	}
	Iterator<?> its = dataSet.iterator();
	while (its.hasNext()) {
		Object t = its.next();
		index += createCells(patriarch, index, t, excelParams, sheet, workbook, rowHeight);
	}
	// 合并同类项
	mergeCells(sheet, excelParams, titleHeight);
}
 

private Comment createCellComment( String author, String comment ) {
  // comments only supported for XLSX
  if ( data.sheet instanceof XSSFSheet ) {
    CreationHelper factory = data.wb.getCreationHelper();
    Drawing drawing = data.sheet.createDrawingPatriarch();

    ClientAnchor anchor = factory.createClientAnchor();
    Comment cmt = drawing.createCellComment( anchor );
    RichTextString str = factory.createRichTextString( comment );
    cmt.setString( str );
    cmt.setAuthor( author );
    return cmt;
  }
  return null;
}
 

public static void createSheetInUserModel2File(HSSFWorkbook workbook,
		ExcelTitle entity, Class<?> pojoClass, Collection<?> dataSet) {
	try {
		Sheet sheet = workbook.createSheet(entity.getSheetName());
		//创建表格属性
		Map<String,HSSFCellStyle> styles = createStyles(workbook);
		Drawing patriarch = sheet.createDrawingPatriarch();
		List<ExcelExportEntity> excelParams = new ArrayList<ExcelExportEntity>();
		// 得到所有字段
		Field fileds[] = ExcelPublicUtil.getClassFields(pojoClass);
		ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class);
		String targetId = null;
		if (etarget != null) {
			targetId = etarget.id();
		}
		getAllExcelField(targetId, fileds, excelParams, pojoClass, null);
		sortAllParams(excelParams);
		int index = 0;
		int feildWidth = getFieldWidth(excelParams);
		if (entity.getTitle() != null) {
			int i =  createHeaderRow(entity, sheet, workbook, feildWidth);
			sheet.createFreezePane(0, 2+i, 0, 2+i);
			index += i;
		} else {
			sheet.createFreezePane(0, 2, 0, 2);
		}
		createTitleRow(entity,sheet, workbook, index, excelParams);
		index += 2;
		setCellWith(excelParams, sheet);
		Iterator<?> its = dataSet.iterator();
		while (its.hasNext()) {
			Object t = its.next();
			index += createCells(patriarch,index, t, excelParams, sheet, workbook
					,styles);
		}
	} catch (Exception e) {
		e.printStackTrace();
	}
}
 

/**
 * 往Sheet 填充正常数据,根据表头信息 使用导入的部分逻辑,坐对象映射
 * 
 * @param teplateParams
 * @param pojoClass
 * @param dataSet
 * @param workbook
 */
private void addDataToSheet(Class<?> pojoClass, Collection<?> dataSet, Sheet sheet,
                            Workbook workbook) throws Exception {

    if (workbook instanceof XSSFWorkbook) {
        super.type = ExcelType.XSSF;
    }
    // 获取表头数据
    Map<String, Integer> titlemap = getTitleMap(sheet);
    Drawing patriarch = sheet.createDrawingPatriarch();
    // 得到所有字段
    Field[] fileds = PoiPublicUtil.getClassFields(pojoClass);
    ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class);
    String targetId = null;
    if (etarget != null) {
        targetId = etarget.value();
    }
    // 获取实体对象的导出数据
    List<ExcelExportEntity> excelParams = new ArrayList<ExcelExportEntity>();
    getAllExcelField(null, targetId, fileds, excelParams, pojoClass, null);
    // 根据表头进行筛选排序
    sortAndFilterExportField(excelParams, titlemap);
    short rowHeight = getRowHeight(excelParams);
    int index = teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(), titleHeight = index;
    //下移数据,模拟插入
    sheet.shiftRows(teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(),
        sheet.getLastRowNum(), getShiftRows(dataSet, excelParams), true, true);
    if (excelParams.size() == 0) {
        return;
    }
    Iterator<?> its = dataSet.iterator();
    while (its.hasNext()) {
        Object t = its.next();
        index += createCells(patriarch, index, t, excelParams, sheet, workbook, rowHeight);
    }
    // 合并同类项
    mergeCells(sheet, excelParams, titleHeight);
}
 

private Comment createCellComment( String author, String comment ) {
  // comments only supported for XLSX
  if ( data.sheet instanceof XSSFSheet ) {
    CreationHelper factory = data.wb.getCreationHelper();
    Drawing drawing = data.sheet.createDrawingPatriarch();

    ClientAnchor anchor = factory.createClientAnchor();
    Comment cmt = drawing.createCellComment( anchor );
    RichTextString str = factory.createRichTextString( comment );
    cmt.setString( str );
    cmt.setAuthor( author );
    return cmt;
  }
  return null;
}
 

@Test
public void test2() throws IOException {
	 //create a new workbook
	XSSFWorkbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
    String imgPath = "D:\\Xiaoi\\logo\\logo.png";
    //add picture data to this workbook.
    InputStream is = new FileInputStream(imgPath);
    byte[] bytes = IOUtils.toByteArray(is);
    int pictureIdx = wb.addPicture(bytes, XSSFWorkbook.PICTURE_TYPE_PNG);
    is.close();

    CreationHelper helper = wb.getCreationHelper();

    //create sheet
    Sheet sheet = wb.createSheet();

    // Create the drawing patriarch.  This is the top level container for all shapes. 
    Drawing drawing = sheet.createDrawingPatriarch();

    //add a picture shape
    ClientAnchor anchor = helper.createClientAnchor();
    //set top-left corner of the picture,
    //subsequent call of Picture#resize() will operate relative to it
    anchor.setCol1(3);
    anchor.setRow1(2);
    anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE);
    Picture pict = drawing.createPicture(anchor, pictureIdx);

    //auto-size picture relative to its top-left corner
    pict.resize();

    //save workbook
    String file = "E:\\ConvertTester\\excel\\picture.xls";
    if(wb instanceof XSSFWorkbook) file += "x";
    try (OutputStream fileOut = new FileOutputStream(file)) {
        wb.write(fileOut);
    }
}
 

/**
 * 往Sheet 填充正常数据,根据表头信息 使用导入的部分逻辑,坐对象映射
 * 
 * @param teplateParams
 * @param pojoClass
 * @param dataSet
 * @param workbook
 */
private void addDataToSheet(Class<?> pojoClass, Collection<?> dataSet, Sheet sheet, Workbook workbook) throws Exception {

	if (workbook instanceof XSSFWorkbook) {
		super.type = ExcelType.XSSF;
	}
	// 获取表头数据
	Map<String, Integer> titlemap = getTitleMap(sheet);
	Drawing patriarch = sheet.createDrawingPatriarch();
	// 得到所有字段
	Field[] fileds = PoiPublicUtil.getClassFields(pojoClass);
	ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class);
	String targetId = null;
	if (etarget != null) {
		targetId = etarget.value();
	}
	// 获取实体对象的导出数据
	List<ExcelExportEntity> excelParams = new ArrayList<ExcelExportEntity>();
	getAllExcelField(null, targetId, fileds, excelParams, pojoClass, null);
	// 根据表头进行筛选排序
	sortAndFilterExportField(excelParams, titlemap);
	short rowHeight = getRowHeight(excelParams);
	int index = teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(), titleHeight = index;
	// 下移数据,模拟插入
	sheet.shiftRows(teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(), sheet.getLastRowNum(), getShiftRows(dataSet, excelParams), true, true);
	if (excelParams.size() == 0) {
		return;
	}
	Iterator<?> its = dataSet.iterator();
	while (its.hasNext()) {
		Object t = its.next();
		index += createCells(patriarch, index, t, excelParams, sheet, workbook, rowHeight);
	}
	// 合并同类项
	mergeCells(sheet, excelParams, titleHeight);
}
 

@Override
public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
    Integer relativeRowIndex, Boolean isHead) {
    if (isHead) {
        Sheet sheet = writeSheetHolder.getSheet();
        Drawing<?> drawingPatriarch = sheet.createDrawingPatriarch();
        // 在第一行 第二列创建一个批注
        Comment comment =
            drawingPatriarch.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short)1, 0, (short)2, 1));
        // 输入批注信息
        comment.setString(new XSSFRichTextString("创建批注!"));
        // 将批注添加到单元格对象中
        sheet.getRow(0).getCell(1).setCellComment(comment);
    }
}
 

@Override
public Drawing getDrawingPatriarch() {
    throw new UnsupportedOperationException();
}
 

public Drawing getDrawingPatriarch() {
  if ( patriarch == null ) {
    patriarch = getSheet().createDrawingPatriarch();
  }
  return patriarch;
}
 

/**
 * コメントの位置、サイズを作成する。
 * @param drawing
 * @param text 書き込むコメント
 * @param cell 書込み対象のセル
 * @param commentOption コメントのオプション
 * @return コメントの表示位置
 */
protected ClientAnchor createAnchor(final Drawing<?> drawing, final String text, final Cell cell,
        final Optional<XlsCommentOption> commentOption) {
    final CellPosition address = CellPosition.of(cell);
    
    // コメントを開業で分割し、最長の行を取得する。
    String[] split = text.split("\r\n|\r|\n");
    int maxLength = Arrays.stream(split)
            .mapToInt(str -> str.getBytes(Charset.forName("Windows-31j")).length)
            .max().orElse(0);
    
    /*
     * コメントの横サイズ。文字数（バイト数）をもとに決定。
     * ・1セルの文字数を元に出す。
     * ・columnWidthは、1文字の幅を1/256にしたものが単位となる。
     * ・最大3列分とする。
     */
    int charPerColumn = cell.getSheet().getColumnWidth(cell.getColumnIndex())/256;
    int commentColumnSize = (int)Math.ceil(maxLength*1.0 / charPerColumn);
    
    int columnSize = commentColumnSize;
    int lineWrappingCount = 0;
    if(commentColumnSize > maxHorizontalSize) {
        columnSize = maxHorizontalSize;
        // 行の折り返し回数を計算する
        lineWrappingCount = commentColumnSize / maxHorizontalSize;
    }
    
    if(commentOption.isPresent() && commentOption.get().horizontalSize() > 0) {
        // 直接指定されている場合
        columnSize = commentOption.get().horizontalSize();
        // 行の折り返し回数を計算する
        lineWrappingCount = columnSize / maxHorizontalSize;
    }
    
    // コメントの縦サイズ。行数をもとに決定。
    int rowSize = split.length + lineWrappingCount > maxVerticalSize ? maxVerticalSize : split.length + lineWrappingCount;
    if(commentOption.isPresent() && commentOption.get().verticalSize() > 0) {
        // 直接指定されている場合
        rowSize = commentOption.get().verticalSize();
    }
    
    return drawing.createAnchor(
            0, 0, 0, 0,
            address.getColumn() + horizontalPrefix, address.getRow() + vertialPrefix,
            address.getColumn() + horizontalPrefix + columnSize, address.getRow() + vertialPrefix + rowSize);
}
 

@Override
public void handleSave(final Cell cell, final Optional<String> text, final Optional<XlsCommentOption> commentOption) {
    
    if(!text.isPresent()) {
        // コメントが空のとき
        commentOption.ifPresent(option -> {
            if(option.removeIfEmpty()) {
                // コメントが空のとき既存のコメントを削除する
                cell.removeCellComment();
            }
        });
        return;
    }
    
    final Sheet sheet = cell.getSheet();
    final CreationHelper helper = sheet.getWorkbook().getCreationHelper();
    final Drawing<?> drawing = sheet.createDrawingPatriarch();
    
    final Comment comment;
    RichTextString richText = helper.createRichTextString(text.get());
    if(cell.getCellComment() == null) {
        ClientAnchor anchor = createAnchor(drawing, text.get(), cell, commentOption);
        comment = drawing.createCellComment(anchor);
        applyCommentFormat(richText, cell);
    } else {
        // 既存のコメントが存在する場合は、書式やサイズをコピーして使用する。
        comment = cell.getCellComment();
        RichTextString orgText = comment.getString();
        if(orgText.numFormattingRuns() > 0) {
            copyCommentFormat(richText, orgText);
        } else {
            applyCommentFormat(richText, cell);
        }
    }
    
    comment.setString(richText);
    
    // コメントの表示状態の更新
    commentOption.ifPresent(option -> comment.setVisible(option.visible()));
    
    cell.setCellComment(comment);
    
}
 

/**
 * Not supported
 */
@Override
public Drawing createDrawingPatriarch() {
  throw new UnsupportedOperationException();
}
 

/**
 * Not supported
 */
@Override
public Drawing getDrawingPatriarch() {
  throw new UnsupportedOperationException();
}
 

/**
 * <p>
 * Process a CellImage from the images list and place the image on the sheet.
 * </p><p>
 * This involves changing the row height as necesssary and determining the column spread of the image.
 * </p>
 * @param cellImage
 * The image to be placed on the sheet.
 */
private void processCellImage( HandlerState state, Drawing drawing, CellImage cellImage ) {
	Coordinate location = cellImage.location;
	
	Cell cell = state.currentSheet.getRow( location.getRow() ).getCell( location.getCol() );

	IImageContent image = cellImage.image;		
	
	StyleManagerUtils smu = state.getSmu();
	float ptHeight = cell.getRow().getHeightInPoints();
	if( image.getHeight() != null ) {
		ptHeight = smu.fontSizeInPoints( image.getHeight().toString() );
	}

	// Get image width
	int endCol = cell.getColumnIndex();
       double lastColWidth = ClientAnchorConversions.widthUnits2Millimetres( (short)state.currentSheet.getColumnWidth( endCol ) )
       		+ 2.0;
       int dx = smu.anchorDxFromMM( lastColWidth, lastColWidth );
       double mmWidth = 0.0;
       if( smu.isAbsolute(image.getWidth())) {
           mmWidth = image.getWidth().convertTo(DimensionType.UNITS_MM);
       } else if(smu.isPixels(image.getWidth())) {
           mmWidth = ClientAnchorConversions.pixels2Millimetres( image.getWidth().getMeasure() );
       }
	// Allow image to span multiple columns
	CellRangeAddress mergedRegion = getMergedRegionBegunBy( state.currentSheet, location.getRow(), location.getCol() );
	if( (cellImage.spanColumns) || ( mergedRegion != null ) ) {
        log.debug( "Image size: ", image.getWidth(), " translates as mmWidth = ", mmWidth );
        if( mmWidth > 0) {
            double mmAccumulatedWidth = 0;
            int endColLimit = cellImage.spanColumns ? 256 : mergedRegion.getLastColumn();
            for( endCol = cell.getColumnIndex(); mmAccumulatedWidth < mmWidth && endCol < endColLimit; ++ endCol ) {
                lastColWidth = ClientAnchorConversions.widthUnits2Millimetres( (short)state.currentSheet.getColumnWidth( endCol ) )
                		+ 2.0;
                mmAccumulatedWidth += lastColWidth;
                log.debug( "lastColWidth = ", lastColWidth, "; mmAccumulatedWidth = ", mmAccumulatedWidth);
            }
            if( mmAccumulatedWidth > mmWidth ) {
                mmAccumulatedWidth -= lastColWidth;
                --endCol;
                double mmShort = mmWidth - mmAccumulatedWidth;
                dx = smu.anchorDxFromMM( mmShort, lastColWidth );
            }
        }
	} else {
		float widthRatio = (float)(mmWidth / lastColWidth);
		ptHeight = ptHeight / widthRatio;
	}

	int rowsSpanned = state.findRowsSpanned( cell.getRowIndex(), cell.getColumnIndex() );
	float neededRowHeightPoints = ptHeight;
	
	for( int i = 0; i < rowsSpanned; ++i ) {
		int rowIndex = cell.getRowIndex() + 1 + i;
		neededRowHeightPoints -= state.currentSheet.getRow(rowIndex).getHeightInPoints();
	}
	
	if( neededRowHeightPoints > cell.getRow().getHeightInPoints()) {
		cell.getRow().setHeightInPoints( neededRowHeightPoints );
	}
	
	// ClientAnchor anchor = wb.getCreationHelper().createClientAnchor();
	ClientAnchor anchor = state.getWb().getCreationHelper().createClientAnchor();
       anchor.setCol1(cell.getColumnIndex());
       anchor.setRow1(cell.getRowIndex());
       anchor.setCol2(endCol);
       anchor.setRow2(cell.getRowIndex() + rowsSpanned);
       anchor.setDx2(dx);
       anchor.setDy2( smu.anchorDyFromPoints( ptHeight, cell.getRow().getHeightInPoints() ) );
       anchor.setAnchorType(ClientAnchor.MOVE_DONT_RESIZE);
    drawing.createPicture(anchor, cellImage.imageIdx);
}
 

@Override
public void endPage(HandlerState state, IPageContent page) throws BirtException {
	
	if( EmitterServices.booleanOption( state.getRenderOptions(), page, ExcelEmitter.SINGLE_SHEET, false )  
		&& ! state.reportEnding ) {
		return ;
	}		
	
	if( EmitterServices.booleanOption( state.getRenderOptions(), page, ExcelEmitter.STRUCTURED_HEADER, false ) ) {
		outputStructuredHeaderFooter(state, page.getFooter());
	} 
	
	String sheetName = prepareSheetName( state );
	if( sheetName != null ) {
		log.debug("Attempting to name sheet ", ( state.getWb().getNumberOfSheets() - 1 ), " \"", sheetName, "\" ");
		int existingSheetIndex = -1;
		for( int i = 0; i < state.getWb().getNumberOfSheets() - 1; ++i ) {
			if( state.getWb().getSheetName(i).equals(sheetName)) {
				log.debug("Found matching sheet at ", i, " \"", state.getWb().getSheetName(i), "\"" );
				existingSheetIndex = i;
				break;
			}
		}
		if (existingSheetIndex >= 0) {
			log.debug("Deleting sheet at ", existingSheetIndex, " \"", state.getWb().getSheetName(existingSheetIndex), "\"" );
			state.getWb().removeSheetAt(existingSheetIndex);
		}
		state.getWb().setSheetName(state.getWb().getNumberOfSheets() - 1, sheetName);
		if (existingSheetIndex >= 0) {
			state.getWb().setSheetOrder(sheetName,existingSheetIndex);
		}
		state.sheetName = null;
	} 
	if( state.sheetPassword != null ) {
		log.debug("Attempting to protect sheet ", ( state.getWb().getNumberOfSheets() - 1 ) );
		state.currentSheet.protectSheet( state.sheetPassword );
		state.sheetPassword = null;
	}

	Drawing drawing = null;
	if( ! state.images.isEmpty() ) {
		drawing = state.currentSheet.createDrawingPatriarch();
	}
	for( CellImage cellImage : state.images ) {
		processCellImage(state,drawing,cellImage);
	}
	state.images.clear();
	state.rowNum = 0;
	state.colNum = 0;
	state.clearRowSpans();
	state.areaBorders.clear();
	
	state.currentSheet = null;
}
 

public Drawing createDrawingPatriarch() {
	return sheet.createDrawingPatriarch();
}
 

/**
 * 创建 最主要的 Cells
 * 
 * @param styles
 * @param rowHeight
 * @throws Exception
 */
public int createCells(Drawing patriarch, int index, Object t,
                       List<ExcelExportEntity> excelParams, Sheet sheet, Workbook workbook,
                       short rowHeight) throws Exception {
    ExcelExportEntity entity;
    Row row = sheet.createRow(index);
    row.setHeight(rowHeight);
    int maxHeight = 1, cellNum = 0;
    int indexKey = createIndexCell(row, index, excelParams.get(0));
    cellNum += indexKey;
    for (int k = indexKey, paramSize = excelParams.size(); k < paramSize; k++) {
        entity = excelParams.get(k);
        if (entity.getList() != null) {
            Collection<?> list = getListCellValue(entity, t);
            int listC = 0;
            for (Object obj : list) {
                createListCells(patriarch, index + listC, cellNum, obj, entity.getList(),
                    sheet, workbook);
                listC++;
            }
            cellNum += entity.getList().size();
            if (list != null && list.size() > maxHeight) {
                maxHeight = list.size();
            }
        } else {
            Object value = getCellValue(entity, t);
            if (entity.getType() == 1) {
                createStringCell(row, cellNum++, value == null ? "" : value.toString(),
                    index % 2 == 0 ? getStyles(false, entity) : getStyles(true, entity), entity);
            } else {
                createImageCell(patriarch, entity, row, cellNum++,
                    value == null ? "" : value.toString(), t);
            }
        }
    }
    // 合并需要合并的单元格
    cellNum = 0;
    for (int k = indexKey, paramSize = excelParams.size(); k < paramSize; k++) {
        entity = excelParams.get(k);
        if (entity.getList() != null) {
            cellNum += entity.getList().size();
        } else if (entity.isNeedMerge()) {
            for (int i = index + 1; i < index + maxHeight; i++) {
                sheet.getRow(i).createCell(cellNum);
                sheet.getRow(i).getCell(cellNum).setCellStyle(getStyles(false, entity));
            }
            sheet.addMergedRegion(new CellRangeAddress(index, index + maxHeight - 1, cellNum,
                cellNum));
            cellNum++;
        }
    }
    return maxHeight;

}
 

/**
 * Not supported
 */
@Override
public Drawing createDrawingPatriarch() {
    throw new UnsupportedOperationException();
}
 

/**
 * 创建 最主要的 Cells
 * @param styles 
 * @throws Exception
 */
private static int createCells(Drawing patriarch, int index, Object t,
		List<ExcelExportEntity> excelParams, Sheet sheet,
		HSSFWorkbook workbook, Map<String, HSSFCellStyle> styles) throws Exception {
	ExcelExportEntity entity;
	Row row = sheet.createRow(index);
	row.setHeight((short) 350);
	int maxHeight = 1, cellNum = 0;
	for (int k = 0, paramSize = excelParams.size(); k < paramSize; k++) {
		entity = excelParams.get(k);
		if (entity.getList() != null) {
			Collection<?> list = (Collection<?>) entity.getGetMethod()
					.invoke(t, new Object[] {});
			int listC = 0;
			for (Object obj : list) {
				createListCells(patriarch,index + listC, cellNum, obj,
						entity.getList(), sheet, workbook,styles);
				listC++;
			}
			cellNum += entity.getList().size();
			if (list!=null&&list.size() > maxHeight) {
				maxHeight = list.size();
			}
		} else {
			Object value = getCellValue(entity,t);
			if (entity.getType() == 1) {
				createStringCell(row, cellNum++,
						value == null ? "" : value.toString(),
						index % 2 == 0 ? getStyles(styles, false, entity.isWrap())
								: getStyles(styles, true, entity.isWrap()),entity);
			} else {
				createImageCell(patriarch,entity, row, cellNum++, value == null ? ""
						: value.toString(),t);
			}
		}
	}
	//合并需要合并的单元格
	cellNum = 0;
	for(int k = 0, paramSize = excelParams.size(); k < paramSize; k++){
		entity = excelParams.get(k);
		if (entity.getList() != null) {
			cellNum += entity.getList().size();
		}else if (entity.isNeedMerge()) {
			sheet.addMergedRegion(new CellRangeAddress(index, index + maxHeight-1, cellNum,
					cellNum));
			cellNum++;
		}
	}
	return maxHeight;

}
 

/**
 * 创建 最主要的 Cells
 * 
 * @param styles
 * @param rowHeight
 * @throws Exception
 */
public int createCells(Drawing patriarch, int index, Object t, List<ExcelExportEntity> excelParams, Sheet sheet, Workbook workbook, short rowHeight) throws Exception {
	ExcelExportEntity entity;
	Row row = sheet.createRow(index);
	row.setHeight(rowHeight);
	int maxHeight = 1, cellNum = 0;
	int indexKey = createIndexCell(row, index, excelParams.get(0));
	cellNum += indexKey;
	for (int k = indexKey, paramSize = excelParams.size(); k < paramSize; k++) {
		entity = excelParams.get(k);
		if (entity.getList() != null) {
			Collection<?> list = getListCellValue(entity, t);
			int listC = 0;
			for (Object obj : list) {
				createListCells(patriarch, index + listC, cellNum, obj, entity.getList(), sheet, workbook);
				listC++;
			}
			cellNum += entity.getList().size();
			if (list != null && list.size() > maxHeight) {
				maxHeight = list.size();
			}
		} else {
			Object value = getCellValue(entity, t);
			if (entity.getType() == 1) {
				createStringCell(row, cellNum++, value == null ? "" : value.toString(), index % 2 == 0 ? getStyles(false, entity) : getStyles(true, entity), entity);
			} else if (entity.getType() == 4){
				createNumericCell(row, cellNum++, value == null ? "" : value.toString(), index % 2 == 0 ? getStyles(false, entity) : getStyles(true, entity), entity);
			} else {
				createImageCell(patriarch, entity, row, cellNum++, value == null ? "" : value.toString(), t);
			}
		}
	}
	// 合并需要合并的单元格
	cellNum = 0;
	for (int k = indexKey, paramSize = excelParams.size(); k < paramSize; k++) {
		entity = excelParams.get(k);
		if (entity.getList() != null) {
			cellNum += entity.getList().size();
		} else if (entity.isNeedMerge()) {
			for (int i = index + 1; i < index + maxHeight; i++) {
				sheet.getRow(i).createCell(cellNum);
				sheet.getRow(i).getCell(cellNum).setCellStyle(getStyles(false, entity));
			}
			sheet.addMergedRegion(new CellRangeAddress(index, index + maxHeight - 1, cellNum, cellNum));
			cellNum++;
		}
	}
	return maxHeight;

}
 

/**
 * 图片类型的Cell
 * 
 * @param patriarch
 * 
 * @param entity
 * @param row
 * @param i
 * @param string
 * @param obj
 * @param workbook
 * @throws Exception
 */
private static void createImageCell(Drawing patriarch,
		ExcelExportEntity entity, Row row, int i, String field,
		Object obj, Workbook workbook) throws Exception {
	if(StringUtils.isEmpty(field)){return;}
	row.setHeight((short) (50 * entity.getHeight()));
	row.createCell(i);
	ClientAnchor anchor = workbook instanceof HSSFWorkbook?
			new HSSFClientAnchor(0, 0, 0, 0, (short) i,
			row.getRowNum(), (short) (i + 1), row.getRowNum() + 1):
				new XSSFClientAnchor(0, 0, 0, 0, (short) i,
						row.getRowNum(), (short) (i + 1), row.getRowNum() + 1);
	if (entity.getExportImageType() == 1) {
		ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
		BufferedImage bufferImg;
		try {
			String path = ExcelExportOfTemplateUtil.class.getClassLoader()
					.getResource("") + field;
			path = path.replace("WEB-INF/classes/", "");
			path = path.replace("file:/", "");
			bufferImg = ImageIO.read(new File(path));
			ImageIO.write(
					bufferImg,
					field.substring(field.indexOf(".") + 1,
							field.length()), byteArrayOut);
			patriarch.createPicture(
					anchor,
					row.getSheet()
							.getWorkbook()
							.addPicture(byteArrayOut.toByteArray(),
									Workbook.PICTURE_TYPE_JPEG));
		} catch (IOException e) {
			e.printStackTrace();
		}
	} else {
		byte[] value = (byte[]) (entity.getGetMethods() != null ? getFieldBySomeMethod(
				entity.getGetMethods(), obj) : entity.getGetMethod()
				.invoke(obj, new Object[] {}));
		if (value != null) {
			patriarch.createPicture(anchor, row.getSheet().getWorkbook()
					.addPicture(value, Workbook.PICTURE_TYPE_JPEG));
		}
	}

}
 

/**
 * 创建 最主要的 Cells
 * 
 * @param styles
 * @throws Exception
 */
private static int createCells(Drawing patriarch, int index, Object t,
		List<ExcelExportEntity> excelParams, Sheet sheet, Workbook workbook)
		throws Exception {
	ExcelExportEntity entity;
	Row row = sheet.createRow(index);
	row.setHeight((short) 350);
	int maxHeight = 1, cellNum = 0;
	for (int k = 0, paramSize = excelParams.size(); k < paramSize; k++) {
		entity = excelParams.get(k);
		if (entity.getList() != null) {
			Collection<?> list = (Collection<?>) entity.getGetMethod()
					.invoke(t, new Object[] {});
			int listC = 0;
			for (Object obj : list) {
				createListCells(patriarch, index + listC, cellNum, obj,
						entity.getList(), sheet, workbook);
				listC++;
			}
			cellNum += entity.getList().size();
			if (list != null && list.size() > maxHeight) {
				maxHeight = list.size();
			}
		} else {
			Object value = getCellValue(entity, t);
			if (entity.getType() != 2) {
				createStringCell(row, cellNum++, value.toString(), entity,
						workbook);
			} else {
				createImageCell(patriarch, entity, row, cellNum++,
						value == null ? "" : value.toString(), t, workbook);
			}
		}
	}
	// 合并需要合并的单元格
	cellNum = 0;
	for (int k = 0, paramSize = excelParams.size(); k < paramSize; k++) {
		entity = excelParams.get(k);
		if (entity.getList() != null) {
			cellNum += entity.getList().size();
		} else if (entity.isNeedMerge()) {
			sheet.addMergedRegion(new CellRangeAddress(index, index
					+ maxHeight - 1, cellNum, cellNum));
			cellNum++;
		}
	}
	return maxHeight;

}
 

private void setImage(Td td, Sheet sheet) {
    if (td.getFile() == null) {
        return;
    }
    try {
        if (createHelper == null) {
            createHelper = workbook.getCreationHelper();
        }
        byte[] bytes = Files.readAllBytes(td.getFile().toPath());
        String fileName = td.getFile().getName();
        int format;
        String suffix = fileName.substring(fileName.lastIndexOf(".") + 1).toLowerCase();
        switch (suffix) {
            case "jpg":
            case "jpeg":
                format = Workbook.PICTURE_TYPE_JPEG;
                break;
            case "png":
                format = Workbook.PICTURE_TYPE_PNG;
                break;
            case "dib":
                format = Workbook.PICTURE_TYPE_DIB;
                break;
            case "emf":
                format = Workbook.PICTURE_TYPE_EMF;
                break;
            case "pict":
                format = Workbook.PICTURE_TYPE_PICT;
                break;
            case "wmf":
                format = Workbook.PICTURE_TYPE_WMF;
                break;
            default:
                throw new IllegalArgumentException("Invalid image type");
        }
        int pictureIdx = workbook.addPicture(bytes, format);
        Drawing drawing = sheet.createDrawingPatriarch();
        ClientAnchor anchor = createHelper.createClientAnchor();
        anchor.setCol1(td.getCol());
        anchor.setRow1(td.getRow());
        Picture pict = drawing.createPicture(anchor, pictureIdx);
        pict.resize(1, 1);
    } catch (IOException e) {
        throw new RuntimeException(e);
    }
}