Writematrix command writing wrong data in Excel sheet
8 ビュー (過去 30 日間)
古いコメントを表示
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.
![](https://www.mathworks.com/matlabcentral/answers/uploaded_files/1635376/image.png)
![](https://www.mathworks.com/matlabcentral/answers/uploaded_files/1635381/image.png)
Has anyone experienced a similar problem? How can I fix this?
7 件のコメント
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:
"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.
採用された回答
Harald
2024 年 3 月 5 日
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 件のコメント
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
その他の回答 (0 件)
参考
カテゴリ
Help Center および File Exchange で Spreadsheets についてさらに検索
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!