Insert an image into a excel cell in MATLAB

Question

I was following this post and I need to do about the same thing only I want to put an image (m by n by 3 matrix) into a cell in excel.

this line wont work because my image im is a matrix and not a handle:

print(im, '-dbitmap');

Do I need to somehow create a handle for the image? Is there another\better way?

Eventually I want to change the cell so that it can fit around the image (without changing the image's size).

You want to insert a matrix *as an image* into an Excel document? Why not insert the raw data? — Wolfie, Aug 22 '17 at 16:32
An image is a matrix. And this image is an output of matlab manipulations I made — havakok, Aug 22 '17 at 16:55

gnovice · Accepted Answer · 2017-08-23T20:58:54.720

The print statement prints the contents of a figure window to a file, so you have to plot your image first:

image(im);                        % Plot image
set(gca, 'Visible', 'off', ...    % Turn off axes visibility
         'Position', [0 0 1 1]);  %   and make axes fill figure window
hFigure = gcf;                    % Get handle to figure
pos = get(hFigure, 'Position');   % Get current figure position
set(hFigure, 'Position', [pos(1:2) size(im, 2) size(im, 1)]);  % Set position so image
                                                               %   is scaled properly

Then you can create a COM server and print the figure to an Excel file like so:

excel = actxserver('Excel.Application');  % Create server object
excelWorkbook = excel.Workbooks.Add(1);   % Add a workbook
excelSheet = excel.ActiveSheet;           % Get the active sheet

dpi = get(groot, 'ScreenPixelsPerInch');  % Get screen dpi
print(hFigure, sprintf('-r%d', dpi), ...  % Print the figure at the screen resolution
      '-clipboard', '-dbitmap');          %   to the clipboard as a bitmap
excelSheet.Range('B2').PasteSpecial();    % Paste from clipboard (top left corner
                                          %   of image will be in the cell 'B2')

excelSheet.Range('B2').RowHeight = ...    % Set cell height to image height
  excelSheet.Shapes.Item(1).Height;
widthScale = excelSheet.Range('B2').ColumnWidth./...  % Column width (in characters)
             excelSheet.Range('B2').Width;            % Column width (in points)
excelSheet.Range('B2').ColumnWidth = ...  % Set cell width to scaled image width
  excelSheet.Shapes.Item(1).Width.*widthScale;

excelWorkbook.SaveAs('figtest.xlsx');  % Save workbook to a file
excelWorkbook.Close();                 % Close workbook
excel.Quit();                          % Quit server
excel.delete();                        % Delete server object

The above attempts to scale the cell to fit the entire image. This works for the row height, but the column width is off for some reason. There seems to be a lot of difficulty in determining the proper scaling, since the column width is defined in terms of characters and the image width is defined in terms of points/inches. I'm not sure if there's a good workaround for this.

As an example, here's what the results look like for the sample MATLAB image 'peppers.png':

This just floats the image inside the excel it does not fit the image into a cell. Is there any way to fit it into a the cell? — havakok, Aug 22 '17 at 18:33
@havakok: Not sure. I tried a few things so far that didn't work. It'll take a little more looking into. — gnovice, Aug 22 '17 at 19:19
@havakok: I updated the answer showing how to scale the image height to the cell height to fit the image in the cell. Is this what you wanted, or did you want to scale the cell size to fit the image? — gnovice, Aug 23 '17 at 16:51
first of all thanks a lot. I did wanted the cell size to fit the image and not the other way around. I tried just flipping sides as in `excelSheet.Range('B2').Height = excelSheet.Shapes.Item(1).Height;`. I will try to find a work around. — havakok, Aug 23 '17 at 17:29
@havakok: I updated the answer to fit the cell size to the image, but it doesn't quite get the column width right. I think this is a larger issue with Excel in general, since it seems like there is no good, well-defined way to draw parallels between widths in characters and widths in points/inches. — gnovice, Aug 23 '17 at 19:54

Sardar Usama · Answer 2 · 2017-08-23T21:32:58.877

_{I'm borrowing some code from gnovice's answer. It was really helpful for me in writing this answer.}

Here is a working example to insert peppers.png in the cell B2 :

im=imread('peppers.png');
imshow(im);

dpi = get(groot, 'ScreenPixelsPerInch');  % Get screen dpi
print(gcf, sprintf('-r%d', dpi), ...      % Print the figure at the screen resolution
      '-clipboard', '-dbitmap');          %   to the clipboard as a bitmap

excel = actxserver('Excel.Application');  % Create server object
excelWorkbook = excel.Workbooks.Add(1);   % Add a workbook
excelSheet = excel.ActiveSheet;           % Get the active sheet
excelSheet.Range('B2').PasteSpecial();    % Paste from clipboard (top left corner
                                          %   of image will be in the cell 'B2')   
%%%%%%%%%%%%%%%% My contribution %%%%%%%%%%%%%%%%
excelSheet.Shapes.Item(1).LockAspectRatio='msoFalse';            %Unlocking aspect ratio
excelSheet.Shapes.Item(1).Width=excelSheet.Range('B2').Width;    %Adjusting width
excelSheet.Shapes.Item(1).Height=excelSheet.Range('B2').Height;  %Adjusting height
%Uncomment the next line if you want the cell to keep the image fit in its particular 
%cell even if the size of the cell is changed later
% excelSheet.Shapes.Item(1).Placement='xlMoveandSize';
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

excelWorkbook.SaveAs('figtest.xlsx');     % Save workbook to a file
excelWorkbook.Close();                    % Close workbook
excel.Quit();                             % Quit server
excel.delete();                           % Delete server object

Output:

Insert an image into a excel cell in MATLAB

2 Answers2

Linked

Related