How can I avoid java.io.Fi​leNotFound​Exception for temp files after multiple xlsread/xlswrite operations?

I'm running simulations that require multiple write and read interactions with Excel in each iteration. I use xlsread and xlswrite to perform these read/write operations.

After a number of iterations, I start getting a java.io.FileNotFoundException for .tmp files (example included below). The program execution is not stopped, but sometimes there are corrupt values in the output that seem to be caused by this problem.

Since the problem only occurs after running the simulations for a while and becomes more frequent over time, I think it might be due to a memory leak of some sort. So I'd like to perform a cleanup after each iteration, and start the Excel interaction in the next iteration with a clean slate. Any ideas on how I could achieve this?

I've already tried using fclose('all') after each iteration, but this didn't help.

Matlab version: R2012b

Excel version: MS Office Excel 2007

FileSystemView.getShellFolder: f=H:\MATLAB\EC3BF813.tmp
java.io.FileNotFoundException: File H:\MATLAB\EC3BF813.tmp not found
  at sun.awt.shell.Win32ShellFolderManager2.createShellFolder(Unknown Source)
  at sun.awt.shell.Win32ShellFolderManager2.createShellFolder(Unknown Source)
  at sun.awt.shell.ShellFolder.getShellFolder(Unknown Source)
  at javax.swing.filechooser.FileSystemView.getShellFolder(Unknown Source)
  at javax.swing.filechooser.FileSystemView.getSystemIcon(Unknown Source)
  at com.mathworks.jmi.MLFileIconUtils.getNativeFileIcon(MLFileIconUtils.java:242)
  at com.mathworks.jmi.MLFileIconUtils.getFileIcon(MLFileIconUtils.java:102)
  at com.mathworks.mlwidgets.explorer.extensions.basic.DefaultFileInfoProvider$1.run(DefaultFileInfoProvider.java:64)
  at com.mathworks.mlwidgets.explorer.model.FileDecorationModel$DeferHandler.run(FileDecorationModel.java:390)
  at com.mathworks.mlwidgets.explorer.model.FileDecorationModel$2.get(FileDecorationModel.java:275)
  at com.mathworks.mlwidgets.explorer.util.UiFileSystemUtils.getDecorationSynchronously(UiFileSystemUtils.java:125)
  at com.mathworks.mlwidgets.explorer.util.UiFileSystemUtils.getDecorationSynchronously(UiFileSystemUtils.java:98)
  at com.mathworks.mlwidgets.explorer.model.table.FileSystemExpansionProvider.getDecoration(FileSystemExpansionProvider.java:124)
  at com.mathworks.mlwidgets.explorer.model.table.FileSystemExpansionProvider.getDecoration(FileSystemExpansionProvider.java:129)
  at com.mathworks.mlwidgets.explorer.model.table.IconColumn$1.convert(IconColumn.java:38)
  at com.mathworks.mlwidgets.explorer.model.table.IconColumn$1.convert(IconColumn.java:35)
  at com.mathworks.widgets.grouptable.GroupingTableColumn.getValue(GroupingTableColumn.java:141)
  at com.mathworks.widgets.grouptable.GroupingTableRow.getValueAt(GroupingTableRow.java:474)
  at com.jidesoft.grid.TreeTableModel.getValueAt(Unknown Source)
  at javax.swing.JTable.getValueAt(Unknown Source)
  at com.jidesoft.grid.CellSpanTable.getValueAt(Unknown Source)
  at com.jidesoft.grid.JideTable.prepareRenderer(Unknown Source)
  at com.jidesoft.grid.CellStyleTable.prepareRenderer(Unknown Source)
  at com.mathworks.widgets.grouptable.GroupingTableUIDelegate.paintCell(GroupingTableUIDelegate.java:66)
  at com.mathworks.widgets.grouptable.GroupingTableUIDelegate.paintSpanCells(GroupingTableUIDelegate.java:373)
  at com.mathworks.widgets.grouptable.GroupingTableUIDelegate.paint(GroupingTableUIDelegate.java:141)
  at com.jidesoft.plaf.basic.BasicCellSpanTableUI.paint(Unknown Source)
  at javax.swing.plaf.ComponentUI.update(Unknown Source)
  at javax.swing.JComponent.paintComponent(Unknown Source)
  at com.jidesoft.grid.CellStyleTable.paintComponent(Unknown Source)
  at com.mathworks.widgets.grouptable.GroupingTable.paintComponent(GroupingTable.java:267)
  at javax.swing.JComponent.paint(Unknown Source)
  at javax.swing.JComponent.paintChildren(Unknown Source)
  at javax.swing.JComponent.paint(Unknown Source)
  at javax.swing.JViewport.paint(Unknown Source)
  at javax.swing.JComponent.paintChildren(Unknown Source)
  at javax.swing.JComponent.paint(Unknown Source)
  at javax.swing.JComponent.paintToOffscreen(Unknown Source)
  at javax.swing.RepaintManager$PaintManager.paintDoubleBuffered(Unknown Source)
  at javax.swing.RepaintManager$PaintManager.paint(Unknown Source)
  at javax.swing.BufferStrategyPaintManager.paint(Unknown Source)
  at javax.swing.RepaintManager.paint(Unknown Source)
  at javax.swing.JComponent._paintImmediately(Unknown Source)
  at javax.swing.JComponent.paintImmediately(Unknown Source)
  at javax.swing.RepaintManager.paintDirtyRegions(Unknown Source)
  at javax.swing.RepaintManager.paintDirtyRegions(Unknown Source)
  at javax.swing.RepaintManager.seqPaintDirtyRegions(Unknown Source)
  at javax.swing.SystemEventQueueUtilities$ComponentWorkRequest.run(Unknown Source)
  at java.awt.event.InvocationEvent.dispatch(Unknown Source)
  at java.awt.EventQueue.dispatchEvent(Unknown Source)
  at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
  at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
  at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
  at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
  at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
  at java.awt.EventDispatchThread.run(Unknown Source)

Answer 1

[Sofie]

I was able to fix the problem by replacing the xlsread/xlswrite functions with equivalent ActiveX functions. I didn't want to use ActivX at first because it seemed more complicated, but once you know which commands to use, it's relatively easy. I haven't seen any of those java errors since I've been using ActiveX, and my code is running faster since Excel doesn't need to close between subsequent read/write operations to the same file.

The ActiveX commands I used to replace xlsread/xlswrite can be found below. The basic replacement version is given first. If you need to create sheets and files or open multiple files at once, some advanced examples are in my 'testing function' at the end.

So here's the basic version:

%define file path
    filepath = [pwd '\testExcel_TE.xlsx'];
%open Excel
    Excelhandle = actxserver('excel.application');
    Excelhandle.Visible = 1; %0 if you want to run Excel in the background
    set(Excelhandle, 'DisplayAlerts', 0); % Stop dialog (do you wish to overwrite...)
%open your file
    TE_filehandle = Excelhandle.Workbooks.Open(filepath);
%create a handle to the sheets in your file
    Sheets = TE_filehandle.Sheets;
%your data should have the format of a cell array
dataToWrite = num2cell([10,5,3,6,7,9]);
%basic XLSWRITE
%write cell array 'dataToWrite' to Excel file in sheet 'Sheet1', range B3:G3
    ActivesheetRange = get(Sheets.Item('Sheet1'), 'Range', 'B3:G3');
    set(ActivesheetRange, 'Value', dataToWrite);
%Notes: 
% - make sure the dimensions of your variable and the Excel range match; 
% - if the Sheet doesn't exist yet, you'll need to create it first (see testing function)
%basic XLSREAD
%read cell array from Excel file and store it in 'readDataCell'
    ActivesheetRange = get(Sheets.Item('Sheet1'), 'Range', 'B3:G3');
    readDataCell = ActivesheetRange.value;
%save workbook, close workbook, close Excel
    TE_filehandle.Save();
    Excelhandle.Workbooks.Close;
    Excelhandle.Quit;

To use the testing function below, manually create two Excel files in your MATLAB working directory, one named testExcel.xlsx, the other secondtestExcel.xlsx and place some strings, numbers, formulas... in the cells you're going to read.

Example:

testExcel.xlsx

Sheet2, row1A-G: this; is; a; test; 1; 2; 3

Sheet2, row3A-G: 1; 2; 3; 4; 5; 6; 7

Sheet3, C2: =A2+B2

secondtestExcel.xlsx

(empty)

function testingExcel

%store Excel file location in filepath variable
filepath = [pwd '\testExcel.xlsx']; %pwd returns the current Matlab folder
filepath2 = [pwd '\secondtestExcel.xlsx'];

%open Excel
Excelhandle = actxserver('excel.application');
%you can make it visible (then you can actually see Excel changing the values in the cells, which is useful for debugging). Set this to 0 to hide Excel and run it in the background.
Excelhandle.Visible = 1;
%The following line is very important when you want to have your code run without interruptions: it stops Excel from asking confirmation when saving or overwriting files. If you don't include this line, every time you want to save a file you'll have to confirm it manually by clicking 'Yes' in the Excel dialog.
set(Excelhandle, 'DisplayAlerts', 0);

%Open your file in Excel and create a handle to your file
exlFile = Excelhandle.Workbooks.Open(filepath);

%Alternatively, you can create a new blank file
% Workbooks = Excelhandle.Workbooks;
% exlFile = invoke(Workbooks, 'Add');

% Get the list of sheets in the workbook
Sheets = exlFile.Sheets;

%Put a cell array into Excel (specify sheet + position)
A = {'apple', 'pear' ; 6, 4};
ActivesheetRange = get(Sheets.Item('Sheet3'), 'Range', 'A1:B2');
set(ActivesheetRange, 'Value', A);

%Get a range from the Excel file as a cell array.
ActivesheetRange = get(Sheets.Item('Sheet3'), 'Range', 'C2');
sixplusfour = ActivesheetRange.value; %if you inserted the formula =A2+B2 manually in C2 when you created testExcel.xlsx, this should return the sum of the values you wrote in A2 and B2 
display(sixplusfour);

%Add a sheet
% Newsheet = invoke(Sheets, 'Add');
% Newsheet.Name = 'New sheet X';

%Rename the first sheet
Sheets.Item(1).Name = 'This is sheet 1';
%Create a handle to the sheet called 'Sheet2'
exlSheet2 = exlFile.Sheets.Item('Sheet2'); 
%Read data in A1:G3 in Sheet2, it's returned as a cell array
dat_range = ['A1:G3']; 
rngObj = exlSheet2.Range(dat_range);

exlData = rngObj.Value; %this returns a cell array

display(exlData);

%Save the file
% Excel.ActiveWorkbook.Save();

%Save as
invoke(Excelhandle.ActiveWorkbook, 'SaveAs', [pwd '\mynewfile.xlsx']);

%You can create a new handle (start another instance of Excel) to work on another file at the same time
Excelhandle2 = actxserver('excel.application');
Excelhandle2.Visible = 1;
set(Excelhandle2, 'DisplayAlerts', 0);

%Open the second file
Excelhandle2.Workbooks.Open(filepath2);

%Close all workbooks opened by the first Excel handle (in this case, this will close testExcel.xlsx)
Excelhandle.Workbooks.Close;
%Close all workbooks opened by the second Excel handle (in this case, this will close secondtestExcel.xlsx)
Excelhandle2.Workbooks.Close;

%Quit Excel, delete the related objects if needed
Excelhandle.Quit;
Excelhandle2.Quit;
clear Excel;
clear Sheets;
%...