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 it 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 as it is enough different that will take some time to be able to actually accomplish anything.

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
wbk.ActiveSheet.isvalid                                 % see if is ok object -- thinks it is, anyway
wbk.Close

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.

I didn't have much luck in just poking at the interface object as used to doing with ActiveX/VBA syntax, so you'll have to dive into the MS doc's to figure out just how to actually work with the document, but with this you should be able to do everything you want entirely without relying on the readxxx/writexxx high level MATLAB functions or use the old xlswriteEx trick. You should be able to just open the file for a session and let the tech close it when he's good and ready although you may also find that without the Excel overhead the timing conflicts won't be nearly as bad although if you do open/close the file there's still the issue of just how long it will take for the OS to flush buffers, physically write and close/release file handles so if in a really tight loop may still have to wait some msec.

[dpb]

ADDENDUM:

BTW, it appears the .NET inteface requires using strings, not char() string arrays...the call

fqn=(fullfile(pwd,'Book1.xlsx'));       % returns char() array
wbk=app.Workbooks.Open(fqn);

failed silently(*) which is why it is wrapped in string() above.

(*) The call didn't return any error, but the isvalid method returned false and the .ActiveSheet property returned an empty object when using the character string.