MATLAB Answers

0

xlswrite randomly overwrites entire spreadsheet!

Asked by Steve Hall on 20 Aug 2018
Latest activity Commented on by dpb
on 20 Aug 2018
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?

  10 Comments

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.
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
on 20 Aug 2018
" 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.

Sign in to comment.

Products


Release

R2015a

2 Answers

Answer by Image Analyst
on 20 Aug 2018

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.

  2 Comments

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

Sign in to comment.


Answer by Jeremy Hughes on 20 Aug 2018

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.

  0 Comments

Sign in to comment.