How to Fill an Excel Shape with a Picture using ActiveX?

Question

Rakeshkumar Karunakaran 2021-1-17

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/719040-how-to-fill-an-excel-shape-with-a-picture-using-activex

Hi all,

I began exploring ActiveX on MATLAB recently in an attempt to do the following:

Make several rectangles and copy images from clipboard into the rectangles.
Set the transperancy of the rectangles from step 1 to 50%.

I was successful in creating multiple shapes through commands on MATLAB but I am not able to understand how to fill the rectangles with images. I did not find any information online pertaining to this. Can you please advice me as to how to proceed? Also, is there a manual for such linkage between Excel and MATLAB? It seems information relating to ActiveX is all over the place, and I wasn't able to understand much from MATLAB documentation.

The code I came up with so far is mentioned below:

% Reference: https://www.mathworks.com/matlabcentral/answers/42079-writing-matlab-image-to-excel-file-at-a-specific-position
clc;
clear;
% User input:
excelFileName = 'Figures.xlsx';
Data = readtable('Analysis Data_v7.xlsx');
% Get the name of the workbook you want to paste a picture into.
folder = pwd;
fullFileName = fullfile(folder, excelFileName);
if ~exist(fullFileName, 'file')
  message = sprintf('Existing Excel workbook not found"\n%s', fullFileName);
  uiwait(errordlg(message));
  return;
end
% Open Excel as an ActiveX server.
    objExcel = actxserver('Excel.Application');
    objExcel.Visible = true;
    % Open the workbook we want to paste the image onto.
    ExcelWorkbook = objExcel.Workbooks.Open(fullFileName); % Full path is necessary!
    oSheet = objExcel.ActiveSheet;
    
for i=1:1:6
    SampleNumber = i;
    path = char(pwd + string(Data.StoreDirectory(SampleNumber)));
    oSheet.Range('A1').Select;
    % Get the name of the image file.
    imageFullFileName = fullfile(path, 'Variance.jpg');
    % Get a handle to Shapes for Sheet 1
    Shapes = oSheet.Shapes;
    % Add image  by importing one from an image file on disk.
    leftPlacement=0;
    topPlacement=150*(i-1);
    imgWidth=143.88489; % width corresponding to 2"
    imgHeight=143.88489;
    Shapes.AddPicture(imageFullFileName, 0, 1,leftPlacement,topPlacement,imgWidth,imgHeight);
    % Add image  by importing one from an image file on disk.
    leftPlacement=0+500;
    topPlacement=150*(i-1);
    imgWidth=143.88489; % width corresponding to 2"
    imgHeight=143.88489;
    Shapes.AddShape(1,leftPlacement,topPlacement,imgWidth,imgHeight); % 1 is to call for rectangle
    
end
for i=7:1:12
    SampleNumber = i;
    path = char(pwd + string(Data.StoreDirectory(SampleNumber)));
    oSheet.Range('A1').Select;
    % Get the name of the image file.
    imageFullFileName = fullfile(path, 'Variance.jpg');
    % Get a handle to Shapes for Sheet 1
    Shapes = oSheet.Shapes;
    % Add image  by importing one from an image file on disk.
    leftPlacement=150;
    topPlacement=150*(i-7);
    imgWidth=143.88489; % width corresponding to 2"
    imgHeight=143.88489;
    Shapes.AddPicture(imageFullFileName, 0, 1,leftPlacement,topPlacement,imgWidth,imgHeight);
    % Add image  by importing one from an image file on disk.
    leftPlacement=150+500;
    topPlacement=150*(i-7);
    imgWidth=143.88489; % width corresponding to 2"
    imgHeight=143.88489;
    Shapes.AddShape(1,leftPlacement,topPlacement,imgWidth,imgHeight);
end
for i=13:1:18
    SampleNumber = i;
    path = char(pwd + string(Data.StoreDirectory(SampleNumber)));
    oSheet.Range('A1').Select;
    % Get the name of the image file.
    imageFullFileName = fullfile(path, 'Variance.jpg');
    % Get a handle to Shapes for Sheet 1
    Shapes = oSheet.Shapes;
    % Add image  by importing one from an image file on disk.
    leftPlacement=300;
    topPlacement=150*(i-13);
    imgWidth=143.88489; % width corresponding to 2"
    imgHeight=143.88489;
    Shapes.AddPicture(imageFullFileName, 0, 1,leftPlacement,topPlacement,imgWidth,imgHeight);
    % Add image  by importing one from an image file on disk.
    leftPlacement=300+500;
    topPlacement=150*(i-13);
    imgWidth=143.88489; % width corresponding to 2"
    imgHeight=143.88489;
    Shapes.AddShape(1,leftPlacement,topPlacement,imgWidth,imgHeight);
end
% Save the workbook.
% Tell it to not wait and pop up alerts like "This file exists.  Do you want to overwrite it."
objExcel.DisplayAlerts = false;
% Save this workbook we just created to disk.  Image will be saved with the workbook.
ExcelWorkbook.SaveAs(fullFileName);
% Close the workbook.  Excel will still be open though.
% ExcelWorkbook.Close(false);
% objExcel.Quit;  % Shut down Excel.