xlswrite randomly overwrites entire spreadsheet!

I am working with some code I inherited from my predecessor, and I am experiencing an unusual problem. Here is the basic algorithm (please don't ask me WHY):

1. MATLAB code starts with some initial values in the MATLAB workspace
2. These values get written to specific cells in the "inputs" tab in spreadsheet "inputs.xlsx"
3. The MATLAB code then reopens "inputs.xlsx" and reads a larger set of values from the "input_master" tab in "inputs.xlsx" - by closing and reopening, the spreadsheet calculates values on the latter tab based on the new values on the former tab.
4. The MATLAB code does a bunch of processing
5. The MATLAB code writes some outputs to "outputs.xlsx"

Sometimes, in an as yet unpredictable manner, xlswrite simply overwrites the entire "inputs.xlsx" spreadsheet during step 2. It has come up with a prompt asking for permission to overwrite, but even when I deny it, it seems to happen anyway. It has only happened a few times so I am trying to run down what is making it do this. It does not appear to be directly repeatable - I can run the code twice with the same inputs and one time it will overwrite, the other time it won't.

This is problematic because the spreadsheet is quite elaborate, and more importantly, when it overwrites during step 2 it destroys what should be read during step 3.

I know that this is vague, and unfortunately the code is spread across a couple of dozen different m files so posting the whole code is right out. But does anyone have any ideas what would do this?

[Paolo]

Can you share the code?

[Steve Hall]

The function where it appears to occur is (edited because the details are confidential):

function [] = Input_Management_xls_update( control_file_in, variables)
  inputsheet = 'sim inputs';
  xlswrite(control_file_in, variable 1, inputsheet, 'B1');
  xlswrite(control_file_in, variable 2, inputsheet, 'B2');
  xlswrite(control_file_in, variable 3, inputsheet, 'B3');
  xlswrite(control_file_in, variable 4, inputsheet, 'B4');
  % Etc
end

All of the variables are individually assigned in the function call and individually addressed as shown.

[Jeremy Hughes]

What version of MATLAB?

[Steve Hall]

R2015a

[Adam Danz]

This is where using debug mode really comes in handy. It's especially useful to step through code, line by line, when you're working with other people's code. In fact, I bet you could solve this with 3 simple ingredients.

1. working in debug mode
2. the use of help and doc functions to understand inputs/output/expected behavior of functions
3. time

If you need help getting started with working in debug mode, I'd be glad to help.

[dpb]

Possibly more significant than the ML version would be which release of Excel? I've found many more "issues" with 2016 than 2010 and earlier.

I'd agree w/ Adam altho if it isn't clear how to reproduce the symptoms that can be difficult.

I'd guess that what's happening is data related and there's something in the code that on occasion your variables in the function doing the writing aren't what are expected and since the cell range references are just single cells, whatever is in the variable will be written from that starting UL position down and to the right of whatever is the size of the variable.

If you would recode to fit a fixed region and check the data don't exceed that, then you'd at least have a way to trap one possible type of error to help in the debugging process.

[Steve Hall]

I am using EXCEL 2010.

There is definitely the possibility that something is generating a zero-length vector for one of the variables, but I am not sure how that would trigger a complete overwrite.

The code as it was given to me used a single cell reference for the xlswrite commands, I will look into bounding the other corner and see if that helps.

[Steve Hall]

Adam,

I am well familiar with debug mode. This is a very (and unnecessarily!) complex set of files that take a long time to run, often while I am not in attendance. I am asking on here in the hopes that I can tailor my search rather than babysitting it while I step through every instance where it interfaces with EXCEL over multiple loops*. If no one has a better idea then I will indeed be spending a day or two ticking through debug mode waiting for a random failure to occur.

*: I did not mention before, but this code is run repeatedly to check against different permutations of the inputs. There does not seem to be any correlation between input values and this overwriting.

[Adam Danz]

One way to find your target might be to seed the random number generator, rng(), prior to running the code so that if/when it fails, you can replicate the exact permutations. Then the problem might be reproducible.

[dpb]

" is definitely the possibility that something is generating a zero-length vector for one of the variables, but I am not sure how that would trigger a complete overwrite."

I'd say all bets are off if such were to happen that is invalid input. If that's at all possible I'd strongly suggest trapping the condition before making the call and do the appropriate fixup there.

Answer 1

[Image Analyst]

A couple of inconsistencies we need to clear up. You say "xlswrite simply overwrites the entire "inputs.xlsx" spreadsheet during step 2." However inputs.xlsx is not a spreadsheet, it's a workbook, a workbook with two sheets "inputs" and "inputs_master". So what gets overwritten? The whole workbook, both sheets? If so, what do you have after that -- both sheets or just one sheet?

Also you say " the "inputs" tab in spreadsheet" while the code refers to a sheet named "sim inputs". So which is it?

I suggest you set a breakpoint at the xlswrite() call and see what's being written. My guess is that either the size and shape of variable 1, 2, 3, or 4 is not what you think it is (for example a matrix rather than a row or column vector), or that the sheet name, "inputsheet", somehow got changed.

One way to prevent that is to compute the full rectangular range of the size you expect to write, for example 'A1:A99' or 'B4:H379' or whatever and make that the cell reference. Then if the variable size is not a perfect match for the cell range it will throw and error and you can investigate why. Currently when you only give one cell, like 'B1' it will put B1 as the upper left corner of whatever shape it is which will blast over other cells possibly. For example if variable 1 was a 5 row by 6 column matrix instead of a 6 column row vector, it will overwrite the entire block 'B1:G5' instead of just row 1 'B1:G1', thus obliterating anything you had in rows 2 through 4.

[Steve Hall]

To clarify: wherever I said "spreadsheet" read that as "workbook" - a bad habit shared by many in my workplace.

The sheet is titled "sim inputs", I abbreviated in my earlier description on the assumption that the actual sheet name was not relevant - I have used xlswrite outside this particular code without issue using the full name.

When it overwrites, it overwrites all sheets in the workbook. Before running the code, there are more than a dozen named sheets. After running the code, when this overwrite randomly* occurs, the workbook has only the inputs sheet plus the default "sheet1", "sheet2", and "sheet3". The inputs sheet has only the values written to it in the previously shown code.

I can bound the individual xlswrite cell ranges to "B1:B1", but the sheets they are reading and writing from are heavily populated beyond what MATLAB is accessing. I cannot, for example, let them write a vector instead of a single value as that will overwrite cells that are referenced elsewhere by other code.

*: This code is running Monte Carlo simulations, so there are some truly random elements to the calculations!

[Image Analyst]

Because the new sheet has the default 3 sheets (which you can put down to one in the preferences of Excel if you want), it seems like the workbook is totally gone, like something deleted it. Can you find it in the recycle bin? If it's MATLAB that's deleting it, it may just delete it outright unless you've issued the command

recycle on

before you start. dpb and I both suggested you figure out the expected cell range and write that explicitly rather than just giving the upper left cell. You can do this but you might want ot look up functions such as excel2col in the File Exchange to figure out the column letter from the number. For example it will give you AA for column 27

[lastRow, lastColumn] = size(yourData);
lastColumnLetter = char(ExcelCol(lastColumn));
cellReference = sprintf('A1:%s%d', lastColumnLetter, lastRow);
xlswrite(FileName, yourData, sheetName, cellReference);

Alternatively try writetable(0 like Jeremy suggested.

Answer 2

[Jeremy Hughes]

I suggest trying in a newer version of MATLAB. If it works there, contact technical support to report the bug.

I'd also suggest using readtable/writetable as I doubt they will have the same issue.