xlswrite is truncating my values

Hi All,
I have a double matrix that I would like to export into Excel. When I create the matrix, i specify 'format long' so that I will see all the decimal places of my values (for example, my time variable in the matrix has 13 decimal places). The problem I am having is that when I write this matrix to Excel using 'xlswrite', it truncates these values (i.e. my time variable now has only 2 decimal places). I checked the formatting in Excel to see whether this was just an issue in the way the numbers were being displayed or whether the values were indeed truncated and the latter is true (adding more decimal places in Excel only results in zeros being added to the end of the number).
Can anyone please tell me how to "force" Matlab to keep the 'format long' option when writing to excel?
Thank you!

Answers (4)

I've never ever experienced that. format long has nothing to do with it though. Are you saying that if you do this:
format long;
xlswrite('test.xlsx', rand(10));
Only 2 decimal places show up?

1 Comment

But Melissa, if you do
>> spreadsheet(1,3)
You're doing that in MATLAB and you will see only a few decimal places, like THE FAQ says. What I would like to see for you to prove your case is a screenshot of Excel with you on a cell and your cursor in the edit field where the entire number should show up but that you're claiming only two decimal places actually made it there. Can you post that screenshot? Do alt-PrintScreen, then go to http://snag.gy and type control-v and give us the link.

Sign in to comment.

format has no bearing on anything except the command window display format.
I see no such problem here --
>> xlswrite('test.xls',pi)
>> p=xlsread('test.xls')
p =
3.1416
>> p==pi
ans =
1
>>
shows the full precision was written and recovered.
Need to see your actual case but one must conclude the data itself is being munged upon for some reason (or perhaps the Excel file you're looking at isn't the one actually written; are you sure you're looking in the working Matlab directory and not a default directory in Excel?).
Using your test code, no, the numbers are 4 decimal places long. However, in my code, the problem is still occurring.
In my code, I have a matrix called 'spreadsheet' that is a 915x13 double. Column 3 of this matrix has a long number in it.
>> spreadsheet(1,3)
ans =
2.440585398032407e+02
However, when I write 'spreadsheet' to Excel using xlswrite, column 3 now looks like this = 244.06
MY CODE FOR WRITING MATRIX TO EXCEL:
format long xlswrite('hos_davis_2_S0.xlsx',spreadsheet);
Thank you for any help you can give me! I have a work around using fprintf, but I'd love to know if there is a more straightforward way to do this.

1 Comment

dpb
dpb on 4 Mar 2014
Edited: dpb on 4 Mar 2014
"...Using your test code, no, the numbers are 4 decimal places long."
NO!!! The internal value is ~15 decimal digits as would be shown if you place the cursor in the cell and look at the edit line. You're seeing a fig-newton of the display format, not the actual value.
And, continuing the above example to illustrate--
>> format long,p
p =
3.141592653589793
>>
shows that while I use default format short, the actual value of p is full precision as the logic test proved that the internal representation is the same as that returned by the builtin pi. And, thereby, proving that the data written to Excel was also full precision as the value of the "extra" digits beyond those shown in the short format could only have come by having been obtained from the xlsread which has only the data in the spreadsheet from which to obtain them. IOW, xlswrite wrote doubles; xlsread read doubles and anything you're seeing inside Excel to the contrary is owing to the display formatting, not the actual values, just as you only see four decimal digits in Matlab with the short format.
_format long xlswrite('hos_davis_2_S0.xlsx',spreadsheet);_
And again, the
format long
has ABSOLUTELY NO BEARING on the subsequent xlswrite -- it only controls the output format in the display window; it doesn't make a bit of difference on internal storage or precision of values nor affect anything in any Matlab function as far as any internal precision therein unless there is display to the command window involved. See
help format % for further info on that point
The code line as you write above won't even work...
>> format long xlswrite('text.xls',pi)
Error using format
Unknown command option.
>>
If you really mean
format long
xlswrite('hos_davis_2_S0.xlsx',spreadsheet);
Then again, I'd be pretty convinced the actual data in the array spreadsheet are being written unless there's some failure.
Try the form
[st,msg]=xlswrite('hos_davis_2_S0.xlsx',spreadsheet)
And see what is returned.
Also, I'd try the experiment I did above -- after the write operation, try to read the file --
s=xlsread('hos_davis_2_S0.xlsx');
s(1,3)==spreadsheet(1,3)
I'll bet the above will also return 1 showing the value stored in the Excel sheet is indeed the same as that written and stored in Matlab (again, presuming the status returned from the write is TRUE).

Sign in to comment.

Melissa
Melissa on 4 Mar 2014
Correction:
MY CODE FOR WRITING MATRIX TO EXCEL:
format long
xlswrite('hos_davis_2_S0.xlsx',spreadsheet);

1 Comment

OK, the one comment about bad syntax is indeed just a typo but again the FORMAT command has no relevance to the problem. You could use
format rat
and the result of the output to the spreadsheet would be unchanged.
>> help format
format Set output format.
format with no inputs sets the output format to the default appropriate
for the class of the variable. For float variables, the default is
format SHORT.
format does not affect how MATLAB computations are done. Computations
on float variables, namely single or double, are done in appropriate
floating point precision, no matter how those variables are displayed.
...

Sign in to comment.

Asked:

on 4 Mar 2014

Commented:

on 4 Mar 2014

Community Treasure Hunt

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

Start Hunting!