MATLAB Answers

xlswrite randomly overwrites entire spreadsheet!

2 views (last 30 days)
Steve Hall
Steve Hall on 20 Aug 2018
Commented: 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?
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.

Answers (2)

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

Jeremy Hughes
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.




Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!