Issue with Insert Picture in Excel when text is wrapped

Aspose.Cells Product Family
1

Kindly find below the code snippet which prints a table in Excel.We are using aspose-cells-2.5.1

Issue is that when i set setTextWrapped(true) for a cell,then any image that has
been inserted in the adjacent cells expands to fit the cell height.

Why is the image getting expanded ? Can u please help us out here.

           //Instantiating a Workbook object
	Workbook workbook = new Workbook();
	//Adding a new worksheet to the Excel object
	int sheetIndex = workbook.getWorksheets().addSheet().getIndex();
	//Obtaining the reference of the newly added worksheet by passing its sheet index
	Worksheet worksheet = workbook.getWorksheets().getSheet(sheetIndex);
	//Adding a sample value to "A1" cell
	Cell w_cell = worksheet.getCells().getCell("A1");
	Style w_cellStyle = w_cell.getStyle();
	
	w_cell.setValue("Khushbu Ruchita Seema Ajay Goshnat Pankaj Tarun");
	w_cellStyle.setTextWrapped(true);
	w_cell.setStyle(w_cellStyle);
	
	String w_fileName = MetricComponentHelper.getImageFileName("de-icon-green_signal");
	String w_filePath = w_req.getSession().getServletContext().getRealPath("/");
	w_filePath += SystemProperties.FILE_SEPERATOR + "common"+ SystemProperties.FILE_SEPERATOR + "images" + SystemProperties.FILE_SEPERATOR +  w_fileName ;
	
	int w_picIndex = worksheet.getPictures().add(0,1,w_filePath);
	Picture w_pic = worksheet.getPictures().get(w_picIndex);
	//w_pic.setHeight(16);
	//w_pic.setWidth(16);
	w_pic.setLockAspectRatio(true);
	w_pic.setLocked(true);
	
	//Saving the Excel file
	workbook.save("C:\\temp\\book1.xls");

Sample.png (61.9 KB)

2

@sakshe,

Could you try to add the following line if it makes any difference:
e.g
Sample code:

.........
w_pic.setPlacement(PlacementType.FREE_FLOATING); 
......

Moreover, I did evaluate the issue using our latest version (we cannot evaluate or test issues using older versions, neither we can include fixes against issues (if found) to older versions. The fixes are based on latest APIs only) and found it also does not work good in latest APIs, we need to evaluate it further though:
e.g
Sample code:

Workbook workbook = new Workbook();

		Worksheet worksheet = workbook.getWorksheets().get(0);
		//Adding a sample value to "A1" cell
		Cell w_cell = worksheet.getCells().get("A1");
		Style w_cellStyle = w_cell.getStyle();
		
		w_cell.setValue("Khushbu Ruchita Seema Ajay Goshnat Pankaj Tarun");
		w_cellStyle.setTextWrapped(true);
		w_cell.setStyle(w_cellStyle);
		
		String w_filePath = "e:\\test\\School.jpg";

		int w_picIndex = worksheet.getPictures().add(0,1,w_filePath);
		Picture w_pic = worksheet.getPictures().get(w_picIndex);
		w_pic.setPlacement(PlacementType.FREE_FLOATING);
		//w_pic.setHeight(16);
		//w_pic.setWidth(16);
		w_pic.setLockAspectRatio(true);
		w_pic.setLocked(true);
		
		//Saving the Excel file
		workbook.save("f:\\files\\out1.xls");

Hope, this helps a bit.

3

Hi Amjad,

It did not work even after setting Placement to FLOAT.

4

@sakshe,

As we already did evaluate the issue using our latest version and sample code and found it also does not work good in latest APIs, the image in the cell is expanded a bit when text is wrapped in the adjacent cell in the worksheet. I have logged a ticket with an id “CELLSJAVA-42877” for your issue. We will look into it soon.

Once we have an update on it, we will let you know here.

5

Ok…
Thanks for the immediate reply.
By when can I get to know the next update?

6

@sakshe,

Please spare us little time (3-5 days or so) to analyze your issue in details. We will get back to you in the next week with latest updates or fix.

7

@sakshe,

We have investigated it little bit. Please auto fit row height before adding pictures.

See following code:

AutoFitterOptions options = new AutoFitterOptions();
options.setOnlyAuto(true);
worksheet.autoFitRows(options);
String w_filePath = dataDir + "timg.jpg";
int w_picIndex = worksheet.getPictures().add(0,1,w_filePath);
Picture w_pic = worksheet.getPictures().get(w_picIndex);
w_pic.setPlacement(PlacementType.FREE_FLOATING);
//w_pic.setHeight(16);
//w_pic.setWidth(16);
w_pic.setLockAspectRatio(true);
w_pic.setLocked(true);

Let us know your feedback.

8

@ahsaniqbalsidiqui

Hi
This AutoFitterOptions is introduced in which version?
I am not findinng this class in my aspose-cells-2.5.1 version.

So not able to check…

9

@sakshe,

I am afraid, AutoFitterOptions have been included in recent versions, so you got to upgrade to latest version if you can. By the way, you may give it a try by using the line if it works fine:
e.g
Sample code:

..........
worksheet.autoFitRows();
...........

In case the above workaround does not fix the issue, I am afraid, there is no other alternative but to upgrade to latest APIs set as we cannot include any enhancements to older versions.

10

@Amjad_Sahi

Hi,

I have used Row.setAutoFitHeight(false);.
This resolved my problem. Thank you very much.
I need one more help, I want to set the height of the row such that all the content in the cell will be visible at one glance.
This this used to happen previously as AutoFitHeight was not set to false. and row height was used to increased because wrappedText.
I used row.setHeight(value) to increase the height of the row with appropriate value, its useful for me.
But I need one proper formula, so that I can adjust the height of the row depending upon the cell content.

Please help me out.

11

@sakshe,

Good to know that your original issue is sorted out now.

Regarding your newer query, it looks like you want to get the height of the content. The feature was not included in older version. I am afraid if you want to avail the feature (get height/width of cell value string) you have to upgrade to latest version/fix.

12

Ok,

Can you help me with the formula which is used internally to set the RowHeight when setWrappedText(true) is used.

I have columnWidth, value in a cell and fontsize as a input parameters. There should be some corelation between these parameters to calculate rowHeight.

13

@sakshe,

Well if Row.AutoFitHeight is true, MS Excel (being an application) will automatically expand row height to show the content of the cell. Aspose.Cells just calculates row heights with the classes of java.awt package. Also, it’s very complex and we cannot evaluate on how to calculate row heights with some simple codes.

14

Ok,

Any ways…Thanks a lot for the help.

15

@sakshe,

You are welcome.