Writematrix command writing wrong data in Excel sheet

I have an app with different numeric fields displaying the results of certain calculations. The numbers are calculated correctly and stored as doubles (I assume). When I try to store them in a matrix and write this matrix in my Excel file, some of the numbers are incorrect (wrong decimals). This could have something to do with the format Matlab uses to save those numbers. The correct ones are formatted in "standard", the incorrect ones are numbers.

Has anyone experienced a similar problem? How can I fix this?

[Johannes]

Note: the values are saved in a different order than in the app

[Dyuman Joshi]

How are the values provided as input and how are they stored? And how do you export the data to excel?

Please share your app, or a minimum working example, which we can run and reproduce the same results as yours and provide suggestions/solutions accordingly.

[Johannes]

The values are provided as a result of a numeric calculation and they are stored in numeric edit fields. I am trying to call those values to save it in a matrix and then export this to Excel.

A minimum working example of this could look like this:

app.var1.Value = 1.151;
app.var2.Value = 2.151;
app.var3.Value = 0.2378;
app.var4.Value = 0.1684;
app.var5.Value = 2.047;
app.var6.Value = 0.4107;
app.var7.Value = 0.6076;
app.var8.Value = 0;
app.var9.Value = 3.472;

Matrix = [app.var1.Value;app.var2.Value;app.var9.Value;app.var3.Value; app.var4.Value; app.var5.Value;
          app.var6.Value;app.var7.Value;app.var8.Value];

%Export to Excel
writematrix(Matrix, "Matlab_Testfile.xlsx","UseExcel",true, "Sheet", 'Sheet1', 'Range', 'B9:B17');

[Stephen23]

@Johannes: please upload a sample .XLSX file by clicking the paperclip button.

[Johannes]

I just updated my MWE and attached the file

[Stephen23]

"This could have something to do with the format Matlab uses to save those numbers"

No, by default MATLAB does not change the cell format. These are the cell formats of the file you uploaded:

• B9: number, 0dp
• B10: number, 0dp
• B11: number, 0dp
• B12: number, general
• B13: number, general
• B14: number, 0dp
• B15: number, general
• B16: number, general
• B17: number, general

However what is more interesting is that some cells contain leading single quotes: this indicates that Excel will force the cell content to be stored as text (this is totally irrespective of the cell format). In your uploaded file, cells B9, B10, B11, B14, B17 store numeric values (or strictly speaking: dynamically typed values), whereas cells B12, B13, B15, B16 store text (with leading single quote indicating content will be stored as text).

Basically that spreadsheet is a bit of a mess. You might find Excel's CELL() function useful to investigate that worksheet:

https://support.microsoft.com/en-au/office/cell-function-51bd39a5-f338-4dbe-a33f-955d67c2b2cf

"Has anyone experienced a similar problem?

Yes, quite often when working with people who do not understand how to use MS Excel.

How can I fix this?"

Do you expect MATLAB to fix your badly formatted file for you?

Open excel. Replace those cells with some fresh, unformatted ones. Save, close, try again.

[Johannes]

@Stephen23 The problem occured only after overwriting the cells with Matlab. The formatting was not the problem, the error still existed when using an empty sheet with unformatted cells as suggested.

Answer 1

[Harald]

Hi,

the problem seems to be due to cell formatting in Excel. Right-click a seemingly wrong cell in Excel, select "Format Cells" and you will notice "Number" with 0 Decimal places. Select the entire range in Excel, right-click, select "Format Cell" and choose "General" to fix this.

If you'd like MATLAB to take care of this, you can use the option "PreserveFormat", false. Downside is that it may then also change formatting you'd wish to be kept. Thus if you are working with one nicely formatted template, you may prefer adjusting it in Excel.

Best wishes,

Harald

[Johannes]

Unfortunately both ways do not work. The error remains the same. The values are correctly displayed in the App environment. What makes it interesting is, that this error concerns only some of the values. Most of them are saved correctly.

[Harald]

This is surprising because I can reproduce the problem and both of the fixes do work for me in R2023b.

Please double-check to verify that neither of the fixes for your MWE work on your machine. Please also consider dropping the "UseExcel", true parameter-value pair.

In case the fixes work for the MWE but not for your real work, please share a new MWE (code + spreadsheet) that still exhibits the issue.

If none of the suggestions help, please contact Technical Support. While I would not expect it, behaviors may be different because of aspects such as the MS Office version, language settings, and Windows OS version.

Best wishes,

Harald

[Johannes]

I just made it work. Dropping the "UseExcel" parameter did the trick.