Extra small doubles written into xlsx result in corrupted file

[ivanbutenko]

Hello!

I've encountered an issue with extra small numbers: when I write 10 ^ -238 and most of smaller numbers into xlsx file, I get a corrupted xlsx file, which can be recovered by Excel. In the recovered file these problem values are replaced by corresponding text values, which can't be converted back into numerics - Excels's =N function gives exact zero for them.

I believe, that the problem is close to the way R converts these numbers into text and the way these text values are written into xlsx file, because Excel, actually, can handle numbers as low as 10 ^ -308 correctly as numeric values, while values below 2.22507 * 10 ^ -308, which is the smallest possible normalized double, are treated as exact numeric zero by it. Also, converting them into text with scientific notation is possible, saving and recalculating files with them gives no issues in Excel. R, at the same time, allows the use of smaller numbers down to 4.94066 * 10 ^ -324, which is the smallest possible denormalized double, but with the loss of accuracy for these.

So, while both R and Excel allow the use of accurate numbers at least down to 10 ^ -308, and the problem is in putting R's text representation of these numbers into xlsx file, it might be worth a look.

Minimal double for my machine:

> .Machine$double.xmin
[1] 2.225074e-308

I use 64-bit R 3.2.2 for x86_64-w64-mingw32/x64 with openxlsx version 3.0.0 from CRAN, 32-bit Excel 2010 version 14.0.7153.5000 all under 64-bit Wintows 7 SP1 Professional

Code to produce xlsx:

library(openxlsx)

data_row <- 10 ^ -(330:210)

wb <- openxlsx::createWorkbook()
openxlsx::addWorksheet(wb, 'Small_numbers')
openxlsx::writeData(wb, 'Small_numbers', data_row)

openxlsx::saveWorkbook(wb, 'corrupted.xlsx')

Code to check conversion with the first problem number fin my example (10 ^ -238):

x <- 10 ^ -238

xc <- as.character(x)
xc
## [1] "9.99999999999999e-239"

xcn <- as.numeric(xc)
xcn
## [1] 1e-238

xcn/x
## [1] 1

xcn/x - 1
## [1] -5.551115e-16

I also looked at: http://www.cplusplus.com/forum/general/53760/ https://en.wikipedia.org/wiki/Denormal_number

Anyway, thank you for your great work there!

PS Unfortunately, I don't see the way to avoid these small values as numbers in Excel - for purpose of reproducible research, last step of my calculation has to be done with Excel template provided with the mass-spec instruments we use, and most of researchers do it manually, while I need to make the process automatic and preferably with R only - diving into VBA or VBA through R is going to make the whole process too complicated.

[awalker89]

Hi when I run

library(openxlsx)
data_row <- 10 ^ -(330:210)
wb <- openxlsx::createWorkbook()
addWorksheet(wb, 'Small_numbers')
writeData(wb, 'Small_numbers', data_row)
openXL(wb)

everything works fine for me. Please update to the dev version on github with

devtools::install_github("awalker89/openxlsx")

Let me know if it's still not working for you. Alex

[splaisan]

I had the same issue and tried replace the stable package with the dev version but when doing so I get an error loading the package

library("openxlsx", lib.loc="/opt/R_LIBS")
Error in get(method, envir = home) : 
  lazy-load database '/opt/R_LIBS/openxlsx/R/openxlsx.rdb' is corrupt
In addition: Warning messages:
1: In .registerS3method(fin[i, 1], fin[i, 2], fin[i, 3], fin[i, 4],  :
  restarting interrupted promise evaluation
2: In get(method, envir = home) :
  restarting interrupted promise evaluation
3: In get(method, envir = home) : internal error -3 in R_decompress1
Error: package or namespace load failed for ‘openxlsx’

Any idea how I can fix the tiny number issue or better install the dev package? I work on OSX 10.11.3 with

R version 3.2.3 (2015-12-10) -- "Wooden Christmas-Tree"
Copyright (C) 2015 The R Foundation for Statistical Computing
Platform: x86_64-apple-darwin13.4.0 (64-bit)

[splaisan]

worked both for install and fixing the demo core after following the full instruction to install the dev

BUT my own data still is buggy and values smaller than 10E-245 ..10E-280 are still replaced by 0.0000000000000 instead of 0 after opening in excel and fixing.

This is not the Excel limit documented in the MS pages

Why does the demo code work but not my numbers like 7.849918e-290 or 1.214082e-288.

I checked the used version is indeed 3.1.6

In order to reproduce my misery, please use this table extract saved as text or try to open the open xlsx exported version with excel link to my public dropbox

[awalker89]

Seems to work for me,

If I run

x <- read.table("edgeTable.txt", sep ="\t", header = TRUE)
wb <- openxlsx::createWorkbook()
addWorksheet(wb, 'Small_numbers')
writeData(wb, 'Small_numbers', x)
openXL(wb)

Everything looks fine to me - can you send me a screen shot of what you are getting and what's not right.