writecell( [.... -inf inf ...] gives 65535 and 65535 in Excel both for -inf and +inf

table2cell(ToperLow)
ans =
1×5 cell array
{[650]} {[-Inf]} {[-Inf]} {[-Inf]} {[500]}
>> xx = table2cell(ToperLow)
xx =
1×5 cell array
{[650]} {[-Inf]} {[-Inf]} {[-Inf]} {[500]}
writecell( xx, 'test.xls', 'Sheet', 'sheet1', 'UseExcel', false )
Resulting cells in Excel is +65535 instead of - 65535???
650 65535 65535 65535 500

3 Kommentare

dpb
dpb am 17 Aug. 2020
Bearbeitet: dpb am 18 Aug. 2020
Another of the mysteries of Excel.
It doesn't know about IEEE Inf or NaN
There are some kludges you can do like writing a VBA routine something like
Function Inf(Optional Recalc) As Double
On Error Resume Next
Inf = 1/0
End Function
but you have to then use conditional formatting to display the "inf" indicator for the value that is, iirc, returned as (2^1024).
All in all, use 1E99 or some other "magic number" for the purpose or just forget about it...
I think you can use string to instead of inf
For some purposes, maybe...a string in the column instead of numeric may break import and return as cellstr() though...not convenient.

Melden Sie sich an, um zu kommentieren.

Antworten (1)

Maadhav Akula
Maadhav Akula am 20 Aug. 2020

0 Stimmen

Hi,
I believe this is how Excel treats Inf and -Inf in a cell, as @Binbin Qi mentioned one workaround would be to write them as strings as mentioned in the following post:
Also the documentation of xlswrite and writetable mention regarding this.
Hope this Helps!

Gefragt:

am 17 Aug. 2020

Beantwortet:

am 20 Aug. 2020

Community Treasure Hunt

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

Start Hunting!

Translated by