memory leak using activex to save multiple excel files

Question

Rhys 2012-11-30

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/55310-memory-leak-using-activex-to-save-multiple-excel-files

I have been trying to make a code that will save 1000's of excel files containing data analysed from csv files. I have a base excel file I write the data to, run several macros to make charts etc and then I save the file. I then make a copy of the file (which I store away) and I clear the data in my base excel file by running a macro called 'cleanup' so I'm ready to repeat these steps with the next set of data. I have done it this way to avoid opening and closing connections all over the place in order to make things run faster. However there appears to be a memory leak and although things start off fast the code eventually slows to a crawl as excel starts to hog well over a GB of memory. Is there a way to stop the memory build up in excel - there should only ever be the one connection so I don't understand how it uses an increasing amount of memory. (edit: the individual files made are fairly small ~ 1MB)

Thanks.

The initial connection is set up as:

if true
e = actxserver ('Excel.Application');
set(e,'DisplayAlerts',0);
set(e, 'Visible', 0);
invoke(e.Workbooks,'Open',file_template);
end

and for every file the following is called:

if true
function excelwrite( file , data , macro , macroName , e)
% write data to the excel file      
[m,n] = size(data);
range = '';
%calcrange is borrowed from 'xlswrite'.
range = calcrange(range,m,n);
Select(Range(e,sprintf('%s',range)));
set(e.selection,'Value',data);
% if macro is set to 1 then run the macros in the array: 'macroName'
if macro == 1
  for i = 1:length(macroName(:,1))
      e.Run(macroName(i,:));  
  end
end
%save, make a copy, and delete data from active workbook.
e.ActiveWorkbook.Save
copyfile(e.activeWorkbook.fullname,file_out);
e.Run('cleanup');
end

2 个评论
显示无隐藏无

Jan 2012-12-3

The "if true" is useless. I suggest to omit it, when you post code in a forum.

Are you sure that Matlab occupies the memory? Or could it be Excel, which does not cleanup as wanted? In the later case, an Excel forum would be a better location to ask.

Rhys 2012-12-4

Thanks for taking a look. It is indeed the Excel application that can be seen to use increasingly large amounts of memory even though the amount of data in the file doesn't increase. I was just wondering if this was a known issue with using ActiveX within from Matlab or if there was something obviously wrong with the code that I am not spotting. I'll try and look into the Excel part of it more.

请先登录，再进行评论。

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

Answer 1

Mark Whirdy 2012-12-4

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/55310-memory-leak-using-activex-to-save-multiple-excel-files#answer_67479

are you not closing the workbooks?

2 个评论
显示无隐藏无

Image Analyst 2012-12-4

That would certainly cause the problem!

Rhys 2012-12-6

There is only ever one workbook open and I keep it open for the duration of the program. I perform a process along the lines of: write data to the open workbook, save the workbook, make copy of the excel file (that gets kept for later), clear the workbook (via a macro), write new data to the workbook, save it, and so on. In task manager you can see that the memory being used by the instance of Excel keeps on increasing. I have had a look on some Excel forums and found a few similar issues, but no definitive solutions. Currently I have "fixed" the issue by periodically closing and reopening the excel file with activex after every 100 writes. This is a bit of a fudge, but gives me the speed increase of not opening and closing the workbook constantly whilst not killing my computer with memory issues.

请先登录，再进行评论。