2

I'm using SpreadSheetAddImage in ColdFusion 10 to add an image to the header are of my spreadsheet. The problem I'm running in to is the function takes an anchor position for example if I want to run the image from cell 1 row 1 to cell 5 row 5 I would supply "1,1,5,5" as the anchor argument. However if my table has small data in those cells the image gets crunched. How can I add the image with a set width and height? 

SpreadsheetAddImage(excelSheet,"C:\myimagepath\excelReportHeaderImage.jpg","1,1,5,5");
Phil
  • 4,029
  • 9
  • 62
  • 107
  • AFAIK, CF does not support explicit sizing. The only way to change the width and height is to change the width/height of the anchored rows and columns. It can be done with POI, but Excel's measurement system is a bit convoluted, so [explicit sizing is not as simple as you might think](http://stackoverflow.com/questions/759856/poi-3-2-image-height-width-controlling). If you do not care about overflow, you could try anchoring the image to the top left corner of a cell and let it size as needed. A bit simpler than the other method, but not as exact and still requires POI. – Leigh Jun 15 '16 at 03:21
  • See also [Apache POI image cell in HSSF and XSSF](http://stackoverflow.com/questions/759856/poi-3-2-image-height-width-controlling) – Leigh Jun 15 '16 at 03:27
  • @Leigh - I'm fine with overflow, but how would I anchor it just to the top left cell (A1) and let the image span across as it needs to – Phil Jun 15 '16 at 12:37
  • @Leigh - got it figured out. Thank you so much for your help! – Phil Jun 15 '16 at 15:00

1 Answers1

1

Figured it out, thanks to the help from Leigh. In ColdFusion 10, here is what I did...

// Add our report header (must add AFTER the columns have been resized or it will reset the image size)
headerImageBytes = FileReadBinary(image_file_path);
headerImageIndex = poiWorkbook.addPicture( headerImageBytes, poiWorkbook.PICTURE_TYPE_JPEG );
helper = poiWorkbook.getCreationHelper();
drawing = poiSheet.createDrawingPatriarch();
anchor = helper.createClientAnchor();
anchor.setCol1(0);
anchor.setCol2(5);
anchor.setRow1(0);
anchor.setRow2(5);
picture = drawing.createPicture( anchor, headerImageIndex );
picture.resize();

Now the image shows in Cells A1:E5 and it is the TRUE size of the image. If you need to adjust the size, I believe you can pass in a scale into the resize method.

Phil
  • 4,029
  • 9
  • 62
  • 107
  • Nice work! Now why does not CF do that out of the box ;-)? – Leigh Jun 15 '16 at 16:39
  • @Leigh - I have no idea. I wish the folks at Adobe would really think these through a little better. – Phil Jun 15 '16 at 17:19
  • Yes. I vacillate between sympathy for the challenges of integrating a third party library and exasperation that a number of basic features are either unavailable or do not work the way you would expect. Exasperation usually wins out ;-) – Leigh Jun 15 '16 at 18:30