xlsread problem regarding reading values
显示 更早的评论
While i read a excel sheet using xlsread, it doesn't show whole value in the variable file but when i print the value i shows more decimal places than the actual value.
For eg. the actual number in excel sheet is 3.53719759
the value shown in variable when i open it 3.5372
the value shown when i print it is 3.53719758987427
How can i get the exact same value as it is in the excel sheet?
7 个评论
"the value shown in variable when i open it 3.5372"
The value is shown as such i.e. 4 digits after decimal, as it is the default output display format, 'short', for numeric datatype in MATLAB.
"the value shown when i print it is 3.53719758987427"
How exactly do you print it? This is not the 'long' format as it has 14 digits after decimal. Or did you missed a digit in copy pasting?
"but when i print the value i shows more decimal places than the actual value."
You have it the other way around. The floating point number is stored with (approximately) 16 digit precision but the default format (as mentioned above) displays only 4 digits after the decimal.
"How can i get the exact same value as it is in the excel sheet?"
If you want to obtain that in numeric format, it is not possible. You can, however, obtain that in text format -
format long
x = pi
sprintf('%0.8f', x)
sprintf("%0.8f", x)
You can round a number upto 8 digits after decimal, but the output will not exactly be same as what you want.
round(x,8)
Stephen23
2023-6-5
"How exactly do you print it?": try FORMAT LONG G
Atharva Bhomle
2023-6-5
"How can i get the exact same value as it is in the excel sheet?"
MS Excel uses double binary floating point numbers:
MATLAB uses double binary floating point numbers (by default):
MATLAB imports the spreadsheet values so that it retrieves exactly the save double value that MS Excel saves to file (which is no different to how MS Excel also imports saved data). So you will get exactly the same value in memory.
Most likely you are confusing how values are displayed with the actual values stored in memory. Not the same things at all. But because you did not explain what you are trying to achieve, we cannot guess how to help you.
Walter Roberson
2023-6-5
Note though that .xlsx files are zip'd directories of mostly XML that stores numbers as text . It is hypothetically possible that when MATLAB converts the text number back to binary, that it might not be bit-for-bit identical to the original number.
If I recall correctly, there are some numbers that need a 17th decimal digit to resolve to the last bit, and it would not astonish me if routinely only 16 decimal digits were stored into the .xlsx file. Maybe even onl 15 decimal digits (by default MATLAB displays only 15 decimal digits, so it is common for MATLAB's display of numbers to not display enough decimal digits to resolve the last bit.)
Stephen23
2023-6-5
"and it would not astonish me if routinely only 16 decimal digits were stored into the .xlsx file"
Apparently XLSX uses up to 17 digits for storing double values:
Walter Roberson
2023-6-5
By the way, https://stackoverflow.com/questions/68784030/how-do-we-need-17-significant-decimal-digits-to-identify-an-arbitrary-double-pre gives an example of a number that requires 17 digits, 50388143.0682372152805328369140625
I just tested with writematrix() and confirmed that MATLAB for one writes out enough digits to be able to exactly replicate the number: writetable() writes out 50388143.068237215
Unfortunately at the moment I do not have genuine Excel handy to test what Excel does.
采纳的回答
Divyanshu
2023-6-13
0 个投票
The values are getting rounded off because by default MATLAB uses ‘short’ format and hence displays numbers only up to 4 decimal places.
In order to get the correct values, follow these steps:
- Firstly, set the format to ‘long’, you can do these by executing command ‘format long’.
- And then read the value from excel file using ‘readtable’ function.
Please refer the following documentation for further details:
更多回答（0 个）
类别
在 帮助中心 和 File Exchange 中查找有关 Spreadsheets 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
Translated by 
