search

ropensci / readODS

Read ODS (OpenDocument Spreadsheet) into R as data frame. Also support writing data frame into ODS file.
https://docs.ropensci.org/readODS/
Other
55 stars 22 forks source link

Referencing an empty cell by formula gives zero #175

Closed no-buddy closed 1 year ago

no-buddy commented 1 year ago

I have a problem when reading "redirected" cells. If I have these at Sheet1: A1="Value" A2=1 A3=2 A4= A5=4

And than open a new sheet and collect data from Sheet1 as: A1="Collected values" A2=Sheet1!A2 A3=Sheet1!A3 A4=Sheet1!A4 A5=Sheet1!A5 etc...

then when I import data Sheet2, readODS converts A4 into 0 and not NA or something that is not number... Is there a way to correct that? If I remove the formula from Sheet2!A4 for blank data, than it is ok.

chainsawriot commented 1 year ago

@no-buddy Thank you for reporting this. I will give you an update later. But it looks like a remaining issue of #104.

chainsawriot commented 1 year ago

I tried both LibreOffice and Google Sheets. In both, referencing a empty cell gives zero. Therefore, it is those software encoding the data that way. readODS reads the data correctly. Below is the generated XML. I will check whether this behavior is expected as per the OASIS standard.

<table:table table:name="Sheet2" table:style-name="ta2"><table:table-column table:style-name="co1" table:number-columns-repeated="1024" table:default-cell-style-name="Default"/><table:table-row table:style-name="ro1"><table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p>b</text:p></table:table-cell><table:table-cell table:number-columns-repeated="1023"/></table:table-row><table:table-row table:style-name="ro1"><table:table-cell table:style-name="ce2" table:formula="of:=[$Sheet1.A2]" office:value-type="float" office:value="0" calcext:value-type="float"><text:p>0</text:p></table:table-cell><table:table-cell table:number-columns-repeated="1023"/></table:table-row><table:table-row table:style-name="ro1"><table:table-cell table:style-name="ce2" table:formula="of:=[$Sheet1.A3]" office:value-type="float" office:value="1" calcext:value-type="float"><text:p>1</text:p></table:table-cell><table:table-cell table:number-columns-repeated="1023"/></table:table-row><table:table-row table:style-name="ro1"><table:table-cell table:style-name="ce2" table:formula="of:=[$Sheet1.A4]" office:value-type="float" office:value="2" calcext:value-type="float"><text:p>2</text:p></table:table-cell><table:table-cell table:number-columns-repeated="1023"/></table:table-row><table:table-row table:style-name="ro1"><table:table-cell table:style-name="ce2" table:formula="of:=[$Sheet1.A5]" office:value-type="float" office:value="3" calcext:value-type="float"><text:p>3</text:p></table:table-cell><table:table-cell table:number-columns-repeated="1023"/></table:table-row><table:table-row table:style-name="ro1"><table:table-cell table:style-name="ce2" table:formula="of:=[$Sheet1.A6]" office:value-type="float" office:value="0" calcext:value-type="float"><text:p>0</text:p></table:table-cell><table:table-cell table:number-columns-repeated="1023"/></table:table-row><table:table-row table:style-name="ro1"><table:table-cell table:style-name="ce2" table:formula="of:=[$Sheet1.A7]" office:value-type="float" office:value="5" calcext:value-type="float"><text:p>5</text:p></table:table-cell><table:table-cell table:number-columns-repeated="1023"/></table:table-row><table:table-row table:style-name="ro2" table:number-rows-repeated="1048568"><table:table-cell table:number-columns-repeated="1024"/></table:table-row><table:table-row table:style-name="ro2"><table:table-cell table:number-columns-repeated="1024"/></table:table-row></table:table><table:named-expressions/></office:spreadsheet>
chainsawriot commented 1 year ago

OASIS doesn't specify this. It seems to be the design of Libreoffice / Google Sheets.