Bug 53227 - Scaled image not visible in SXSSFWorkbook
Summary: Scaled image not visible in SXSSFWorkbook
Alias: None
Product: POI
Classification: Unclassified
Component: SXSSF (show other bugs)
Version: 3.8-FINAL
Hardware: PC All
: P2 major (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2012-05-14 08:10 UTC by Edwin Roth
Modified: 2012-05-21 15:29 UTC (History)
0 users

not working XSL (5.91 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2012-05-14 08:10 UTC, Edwin Roth
working workbook (5.50 KB, application/vnd.ms-excel)
2012-05-14 08:11 UTC, Edwin Roth
AddDimensionedImage modifed to work with OOXML (54.93 KB, application/octet-stream)
2012-05-15 11:06 UTC, Mark B
Corrected AddDimensionedImage.java to support XSSF/SXSSF (54.22 KB, application/octet-stream)
2012-05-17 15:13 UTC, Mark B

Note You need to log in before you can comment on or make changes to this bug.
Description Edwin Roth 2012-05-14 08:10:33 UTC
Created attachment 28774 [details]
not working XSL

After replacing org.apache.poi.hssf.usermodel.HSSFWorkbook by its streaming equivalent org.apache.poi.xssf.streaming.SXSSFWorkbook in 
the AddDimensionedImage.java sample:

package org.apache.poi.ss.examples;
public class AddDimensionedImage {
	public static void main(String[] args) {
		Workbook workbook = null;
            //workbook = new HSSFWorkbook();
            workbook=new SXSSFWorkbook(100);

the image supplied as command parameter is no longer rendered at all, i.e. the cell is resized correctly but the image is not visible.

The used POI version is the latest stable one (3.8-20120326).
Comment 1 Edwin Roth 2012-05-14 08:11:11 UTC
Created attachment 28775 [details]
working workbook
Comment 2 Mark B 2012-05-14 16:11:06 UTC
The AddDimensionedImmage example applied to the older binary workbook format accessible through HSSF. SXSSF addresses the OOXML file format and I never tested it for that version of the file format. Can I ask you please to undertake a small test? Would you use XSSF - not SXSSF - to create a workbook and use the AddDimensionedImage approach to add an image to a sheet within a workbook? If you are still unable to see the image in that workbook when you open it with Excel then it may well be that the approach outlined in AddDimensionedImage is not applicable to OOXML workbooks.

Thinking about it slightly further, it is likely that you cannot use this approach as I do know that the image will be dimensioned in EMU's - English Metric Units - in the OOXML workbook. Still, I would ask you to try if you are willing please.


Mark B
Comment 3 Mark B 2012-05-14 16:54:55 UTC
Just had a quick look at your example workbook and I am beginning to think that it is a units issue. Looking at the markup in drawing1.xml it seems as though the image has been added to the worksheet successfully - the information for the anchor is there and shows the image occupying the whoe of a cell with the offsets 255 and 1023 but I wonder if this is actually correct. I need to create a workbook with an image that occupies the whole of a cell and look at the markup for that. Will post once I find a definitive answer.

Mark B
Comment 4 Mark B 2012-05-14 17:04:24 UTC
Just had a check and it is a problem with the way the offset is specified when the image occupies the whole of a cell.

The older binary format used a different system and the values 255 and 1023 were enough to indicate the full width and height of the cell. With the move to use EMU in OOXML these values no longer hold true and I will need to amend the code to set the values appropriately. Without taking a closer look, I think that it should just be a matter of converting from the older units into EMU's but am not sure and need to take a closer look. Sadly, I do not know when I will have the time to devote to this task so it might be worthwhile taking a look at it yourself in the interim. If I make any progress I will post to this thread and would ask that you do likewise if you make any progress.

Mark B
Comment 5 Mark B 2012-05-15 06:42:11 UTC
If you take a look at the XSSFShape class, there is a constant defined called something like EMUS_PER_PIXEL. As a quick fix, I think all that you need to do is to multiply the offsets - the cx1, dy1, etc values - by this to see your image correctly sized.

Later today, I should have the chance to get my hands on a copy of the source for AddDimensionedImage and will try this fix to prove that it does work. If it does, the updated version will be attached to this bug report later today or early tomorrow morning.

Thanks for bringing this problem to my attention.

Mark B
Comment 6 Mark B 2012-05-15 11:06:36 UTC
Created attachment 28780 [details]
AddDimensionedImage modifed to work with OOXML

Temporary fix for the missing image problem when working with OOXML files. The complete fix needs a modification to the way offsets are calculated and will be ready by the end of the week.
Comment 7 Mark B 2012-05-17 15:13:07 UTC
Created attachment 28799 [details]
Corrected AddDimensionedImage.java to support XSSF/SXSSF

Modified AddDimensionedImage.java to support XSSF/SXSSF. It still is not quite as accurate as the HSSF version as testing has revealed discrepancies of up to 2mm when setting the size of the image. Further investigation will be needed to correct this and I do suspect a rounding issue compounded by the relatively large number of EMUs per mm (36000) compounding to create a reasonable error. Still, I feel it is satisfactory for most purposes now.
Comment 8 Yegor Kozlov 2012-05-18 08:22:48 UTC
Correction applied in r1340027

Thanks, Mark
Comment 9 Edwin Roth 2012-05-21 11:07:17 UTC
Thanks a lot, Mark, for the fix! The precision issue does not matter, the solution is satisfactory for our purposes. Regards Edwin
Comment 10 Mark B 2012-05-21 15:29:47 UTC
Good to know and thanks for that. Sorry you had the issue, I ought to have been far more through with testing the code after it had been ported to the ss.usermodel but it sort of fell through the cracks so to speak.


Mark B