Writematrix command writing wrong data in Excel sheet

2024 3 5
1 个回答
回答已采纳
更新时间：2024 3 19
4 次查看（30 天）

0 个投票

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?

7 个评论
显示 5更早的评论 隐藏 5更早的评论

Johannes
Johannes 2024-3-5
Note: the values are saved in a different order than in the app
Dyuman Joshi
Dyuman Joshi 2024-3-5
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
Johannes 2024-3-5
编辑：Johannes 2024-3-5
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
Stephen23 2024-3-5
@Johannes: please upload a sample .XLSX file by clicking the paperclip button.
Johannes
Johannes 2024-3-5
I just updated my MWE and attached the file
Stephen23
Stephen23 2024-3-5
编辑：Stephen23 2024-3-6
"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
Johannes 2024-3-19
@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.

请先登录，再进行评论。

请先登录，再回答此问题。

 采纳的回答

Harald
Harald 2024-3-5

0 个投票

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

3 个评论
显示 1更早的评论 隐藏 1更早的评论

Johannes
Johannes 2024-3-5
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
Harald 2024-3-6
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
Johannes 2024-3-19
I just made it work. Dropping the "UseExcel" parameter did the trick.

请先登录，再进行评论。

更多回答（0 个）

类别

在 帮助中心 和 File Exchange 中查找有关 Data Import from MATLAB 的更多信息

产品

版本

R2023b

标签

Community Treasure Hunt

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

Start Hunting!

Translated by