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 patter 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.