Writing data to multiple Excel ranges

I am using an Excel document as the basis for a test report. The 'Data' tab contains some named ranges for meta data (date, sample ID, batch, etc); these are single cells. The sheet also contains named ranges in which to record measured data. These ranges are variously columns (1D), or blocks (2D). I am trying to intentionally write to each of these ranges discretely, to maintain flexibility (so the code does not have to change if the Excel document changes). If I use a series of writecell/writematrix calls, MATLAB seems to sporadically get hung up on file permissions. It seems that the writecell/writematrix functions do not always properly release the file upon completion.

I am open to solutions with native MATLAB code, or using the Excel .NET interface. As ActiveX is depricated, I am trying to avoid it.

MATLAB 2024B, Excel 2016, on Windows 11 Enterprise.

% These are nested functions; fileOut is shared with the parent function.
function writeNumeric(sheet, range, data)
    writematrix(data, fileOut,...
        "FileType", "spreadsheet",...
        "Sheet",    sheet,...
        "Range",    range,...
        "AutoFitWidth", false)
end

function writeString(sheet, range, s)
    writecell(s, fileOut,...
        "FileType", "spreadsheet",...
        "Sheet",    sheet,...
        "Range",    range,...
        "AutoFitWidth", false)
    end

Answer 1

[dpb]

I don't think the MATLAB functions actually leave the file open but the time between successive calls to the same file may be faster than the OS actually writes, closes, and releases the underlying file handle.

I've had better luck if set the 'UseExcel',1 optional named parameter, but it's not necessarily a panacea, either.

I try to avoid having such tight loops by saving up changes in memory and then committing them in a batch operation but this may not always be a feasible alternative, granted, at least without a lot of code restructuring.

My end result has been use of try...catch...end blocks with some timing and checking that the "~$" temporary file doesn't exist any longer before the try of opening the file for access again.

[Joel Marchand]

Setting the 'UseExcel' option for writematrix/writecell to true does in fact seem to alleviate this issue. However, writing what is (in my mind) a fairly modest amount of data is painfully slow. I ran the code in the profiler. Total time was around 54 seconds. It seems that MATLAB is spending most of its time creating a workbook.

The profiler points to an inordinate amount of time in a constructWorkbook function, specifically in creating the workbook instance. The comments in this function imply that if I move my "Data" sheet to be the first tab and allow writematrix/writecell to write to the first sheet in the spreadsheet by default, the line would not be called. I made this change, and the execution time is in fact quicker ~45 seconds. It does still take an inordinate amount of time creating the workbook.

Is there any way to speed this up further? The software is intended to run "man-in-the-loop" measurements and processing of data, so the tech will have to wait while the data is saved.

[Joel Marchand]

With regards to timing, by considering the data as blocks of unrelated, but adjacent (in the spreadsheet) data, then writing as such, I have been able to reduce the save time to the order of 15 seconds. While not ideal, this is a much more reasonable execution time.

[dpb]

My recent apps have a long-enough execution time the write overhead doesn't seem inordinate to the user, but it's not in the usage pattern you're tyring to create but simply on demand; internally there are a bunch of calls, but nothing else for the user to be doing, anyway.

You might try without the actual use of an Excel instance, but instead insert a short delay before the next write. The alternative to stay with the recent writexxx family (all of which internally conver the cell or array to a table and then call writetable, btw) is to do the upfront check that the file with the preceding "~$" in front of the filename has been closed and deleted by Excel/the OS before trying again.

For the cases that really do have the need for a really tight loop that haven't gotten around otherwise, I have reverted to the ploy @Walter Roberson mentions of using the File Exchange <xlswriteEx> which does create the ActiveX COM server and leaves it open until one callls the function again with an empty argument list that is the trigger to close the Excel file.. This is by far the fastest way because it avoids the buildup/teardown every time.

I made a slight structural change in the function as on the FEX in that I explicitly create the ActiveX instance first and pass it to the function rather than having it internal as the other. This allows one to then also do things such as formatting at the same time by making direct ActiveX calls.

While perhaps now considered archaic, I've found it quite stable to do this although if one does get into the decoration stuff it can take a while to delve through the Excel VBA doc's to figure out the needed syntax.

I have not tried to keep up with what would be considered the modern way to do the same thing...

[dpb]

The following is in a GUI front end to make sure the user has closed the file before dispatching a function that needs to update it -- it shows the basic idea; for essentially unintended use omit the user interaction unless it does eventually fail entirely which should never happen unless your app crashes somehow with the file still open...

      % If UpdateSplits requested make sure user doesn't have bill file open, too
      % Excel creates hidden file with "~$" prefix while open; search for this file
      if app.billUpdate
        chkOpen=true;
        nTries=0;
        while chkOpen
          [~,fn]=system(['dir /a:h /b "' fullfile(app.billPath,['~$' app.billFile]) '"']);
          chkOpen=matches(strtrim(fn(3:end)),app.billFile,'IgnoreCase',true);
          if chkOpen
            h=errordlg([app.billFile "Locked for Update.  Must Close Billing File First."], ...
                      'File Locked',"non-modal");
            waitfor(h)
            %figure(app.RestrictedAwardsUIFigure)
            pause(1)
          end
          nTries=nTries+1;
          if nTries>2
            h=errordlg([app.billFile "File Still Locked After "+nTries+" Attempts.  Returning to Main Menu."], ...
                      'File Locked',"modal");
            waitfor(h)
            %figure(app.RestrictedAwardsUIFigure)
            app.UpdateButton.Text="Update"; app.UpdateButton.FontColor='k'; app.UpdateButton.Enable='on';
            app.QuitButton.Enable='on';
            app.FileMenu.Enable='on'; app.OptionsMenu.Enable='on';
            drawnow
            return
          end
        end
      end

Answer 2

[Walter Roberson]

It is known that for efficiency, MATLAB might leave excel documents open between read* and write* calls. It is not at all clear when MATLAB releases the files.

Answer 3

[dpb]

"...solutions with native MATLAB code, or using the Excel .NET interface."

OK, I got curious enough to go looking -- I had previously mistakenly thought .NET was another layer but still using the Excel instance, but actually the .NET API interacts directly with the native file format. This makes it much more attractive, indeed, at the expense of learning another API documented in C# code rather than VBA.

But, I got the following to work; I didn't pursue the API in more depth; with the help of the MATLAB doc on using Excel with .NET and asking specific functionality of AI that generated example code for things not shown in the limited examples:

dotnetenv('framework');                                 % requires R2022b+
NET.addAssembly('microsoft.office.interop.excel');
app = Microsoft.Office.Interop.Excel.ApplicationClass;
fqn=string(fullfile(pwd,'Book1.xlsx'));                 % create fully-qualified filename
wbk=app.Workbooks.Open(fqn);                            % open the file, returns a WorkbookClass object
wantedsheet='Sheet1';                                   % set the wanted sheet to address
wkshts=wbk.Worksheets;                                  % returns a sheets collect COM object
wksht=Item(wkshts,wantedsheet);                         % select the wanted sheet 
wksht=Microsoft.Office.Interop.Excel.Worksheet(wksht);  % this is key -- the COM object is not populated directly
% first let's read what is in the given named range...
rnge=Range(wksht,'RangeA')                              % create a named range object
data=rnge.Value2;                                       % again, a COM object, not the actual data
data=cell(data,'ConvertTypes',{'all'});                 % MUST convert to MATLAB type
data=cell2mat(data);                                    % if all numeric
% if all data in the range are known to be numeric, above can be shortened as
%data=cell2mat(cell(rnge.Value2));

% now write some other data in its place...
data=rand(size(data));                                  % arbitrary block of values
rnge.Value2=data;                                       % store it
wbk.Save                                                % and save the workbook
wbk.Close(0)                                            % and close
System.Runtime.InteropServices.Marshal.ReleaseComObject(rnge);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wksht);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wkshts);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wbk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);

After the .Open() I checked about whether it was really so via

>> !dir /b /a:h *Book1.xlsx
~$Book1.xlsx 
>> wbk.Close
>> !dir /b /a:h *Book1.xlsx
File Not Found 
>> 

and the hidden backup file was there when open and not once close.

it's not as straightforward as is translating ActiveX VBA syntax to MATLAB-callable equivalent; the need to cast returned COM objects to specific types is different and it is not clear precisely when this is or is not needed nor the syntax to do so. I found the above only via the example/AI-generated code, not in the use documentation. You'll probably find asking an AI bot for "How do I ... with .NET in MATLAB?" a very frequent tool if doing anything but the most basic.

I also leanred that the Value2 property is recommended over using the Value property for speed and avoiding the Excel propensity to convert dates internally; it is a parallel property that contains the underlying data value and doesn't support the Time or Currency formatting types that Value does. That is, in fact, documented at the MS Learn site.

The <Ineract with Excel by .NET> doc page will provide some vital klews, but certainly won't be all you'll have to figure out.