Resize image before export to excel

Question

Clement E 2018-6-20

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/406605-resize-image-before-export-to-excel

评论： Mohamed Eldin 2023-10-16

Hello,

I would like to resize my image using a factor before exporting it to excel. I'm using the script xlswritefig.m

function xlswritefig(hFig,filename,sheetname,xlcell)
% XLSWRITEFIG  Write a MATLAB figure to an Excel spreadsheet
%
% xlswritefig(hFig,filename,sheetname,xlcell)
%
% All inputs are optional:
%
%    hFig:      Handle to MATLAB figure.  If empty, current figure is
%                   exported
%    filename   (string) Name of Excel file, including extension.  If not specified, contents will
%                  be opened in a new Excel spreadsheet. 
%    sheetname:  Name of sheet to write data to. The default is 'Sheet1'
%                       If specified, a sheet with the specified name must
%                       exist
%    xlcell:     Designation of cell to indicate the upper-left corner of
%                  the figure (e.g. 'D2').  Default = 'A1'
%
% Requirements: Must have Microsoft Excel installed.  Microsoft Windows
% only.
%
% Ex:
% Paste the current figure into a new Excel spreadsheet which is left open.
%         plot(rand(10,1))
%         drawnow    % Maybe overkill, but ensures plot is drawn first
%         xlswritefig
%
% Specify all options.  
%         hFig = figure;      
%         surf(peaks)
%         xlswritefig(hFig,'MyNewFile.xlsx','Sheet2','D4')
%         winopen('MyNewFile.xlsx')   
% Michelle Hirsch
% The MathWorks
% mhirsch@mathworks.com
%
% Is this function useful?  Drop me a line to let me know!
if nargin==0 || isempty(hFig)
    hFig = gcf;
end
if nargin<2 || isempty(filename)
    filename ='';
    dontsave = true;
else
    dontsave = false;
      % Create full file name with path
      filename = fullfilename(filename);
  end
if nargin < 3 || isempty(sheetname)
    sheetname = 'Sheet1';
end;
if nargin<4
    xlcell = 'A1';
end;
% Put figure in clipboard
% New graphics system in R2014b changed the default renderer from painters
% to opengl, which impacts figure export. Manually setting to Painters
% seems to work pretty well.
r = get(hFig,'Renderer');
set(hFig,'Renderer','opengl')
drawnow
hgexport(hFig,'-clipboard')
set(hFig,'Renderer',r)
% Open Excel, add workbook, change active worksheet,
% get/put array, save.
% First, open an Excel Server.
Excel = actxserver('Excel.Application');
% Two cases:
% * Open a new workbook, save with given file name
% * Open an existing workbook
if exist(filename,'file')==0
    % The following case if file does not exist (Creating New File)
    op = invoke(Excel.Workbooks,'Add');
    %     invoke(op, 'SaveAs', [pwd filesep filename]);
    new=1;
else
    % The following case if file does exist (Opening File)
    %     disp(['Opening Excel File ...(' filename ')']);
    op = invoke(Excel.Workbooks, 'open', filename);
    new=0;
end
% set(Excel, 'Visible', 0);
% Make the specified sheet active.
try
    Sheets = Excel.ActiveWorkBook.Sheets;
    target_sheet = get(Sheets, 'Item', sheetname);
catch %#ok<CTCH>   Suppress so that this function works in releases without MException
    % Add the sheet if it doesn't exist
    target_sheet = Excel.ActiveWorkBook.Worksheets.Add();
    target_sheet.Name = sheetname;
end;
invoke(target_sheet, 'Activate');
Activesheet = Excel.Activesheet;
% Paste to specified cell
factor=0.5;
oldH=Activesheet.Shapes.Item(1).Height;
oldW=Activesheet.Shapes.Item(1).Width;
Activesheet.Shapes.Item(1).Height = oldH/factor;
Activesheet.Shapes.Item(1).Width = oldW/factor;
Paste(Activesheet,get(Activesheet,'Range',xlcell,xlcell))
% Save and clean up
if new && ~dontsave
    invoke(op, 'SaveAs', filename);
elseif ~new
    invoke(op, 'Save');
else  % New, but don't save
    set(Excel, 'Visible', 1);
    return  % Bail out before quitting Excel
end
invoke(Excel, 'Quit');
delete(Excel)
end
function filename = fullfilename(filename)
[filepath, filename, fileext] = fileparts(filename);
if isempty(filepath)
    filepath = pwd;
end
if isempty(fileext)
    fileext = '.xlsx';
end
filename = fullfile(filepath, [filename fileext]);
end

I tried to modify the script to be able to do what i want but i can't make it work.

   factor=0.5;
    oldH=Activesheet.Shapes.Item(1).Height;
    oldW=Activesheet.Shapes.Item(1).Width;
    Activesheet.Shapes.Item(1).Height = oldH/factor;
    Activesheet.Shapes.Item(1).Width = oldW/factor;

Thanks in advance for the help.

Sincerely

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

Guillaume 2018-7-16

在 MATLAB Online 中打开

Not related to the question but for better reliability, I would:

replace the line

Sheets = Excel.ActiveWorkBook.Sheets;

by

Sheets = op.Sheets;

get rid of the two lines

invoke(target_sheet, 'Activate');
Activesheet = Excel.Activesheet;

and replace all subsequent instances of Activesheet by target_sheet.

In general, it's not a good idea to rely on ActiveWorkbook/ActiveSheet as macros, antiviruses, the user can change these under your feet. In any case, you've got a reference to the actual workbook/worksheet.

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Michelle Hirsch 2018-7-16

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/406605-resize-image-before-export-to-excel#answer_328980

image.png

The easiest way to adjust the size is to change the size of the figure before it is copied to the clipboard because hgexport honors the figure size. Just add a call like

hFig.Position(3:4) = hFig.Position(3:4)/factor % Scale width and height by factor

somewhere before the line

hgexport(hFig,'-clipboard')