search

mjul / docjure

Read and write Office documents from Clojure
MIT License
622 stars 129 forks source link

Numeric text string being read as float #87

Open keithmantell opened 4 years ago

keithmantell commented 4 years ago

Hi,

Probably a newbie question:

I have a cell set as Text, say 9A5655897 then when I read with select-columns I get "9A5655897"

But if I have 91371672 then I get 9.13712672E8

Is there a way of forcing a read as a string?

I am on Mac OS if that makes a difference.

Thanks

mjul commented 4 years ago

In general, Excel prefers numbers. If you are up to it, you can have a look at the Docjure code to see how it reads the cell value. Maybe there is a clue there. You can also try applying the clojure str function to turn the data into strings.

keithmantell commented 4 years ago

Hi, tried the str function bit that just gives the floating point version in quotes! I did look at the code. I'm not an expert but will think about submitting a change. My work around is to save the spreadsheet as a CSV and read that! Thanks

kimim commented 3 years ago

I've tested in Excel. By default, number string is stored as number. When the field with small green triangle, indicates it is a number stored as string:

https://support.microsoft.com/en-us/office/fix-text-formatted-numbers-by-applying-a-number-format-6599c03a-954d-4d83-b78a-23af2c8845d0#__toc260840018

kimim commented 3 years ago

"2014" in this file is treated as string. 1900-based-dates.xlsx number-stored-as-string

andres-moreno commented 3 years ago

I noticed that when reading a 10-digit integer I would also get a floating-point number. I decided on a lark to format it using cl-format (the formatting string used was "~14,'0d". It turns out that this will give me a 14-digit, zero left-padded string!

viesti commented 11 months ago

I ran into this also, and found an SO post, which points to org.apache.poi.ss.usermodel.DataFormatter.formatCellValue, which seems useful, based on the javadoc, if you want a string that looks the same as in Excel:

DataFormatter contains methods for formatting the value stored in a Cell. This can be useful for reports and GUI presentations when you need to display data exactly as it appears in Excel.

mjul commented 11 months ago

Please submit a PR if it would be useful to provide a helper function in the Clojure library that wraps a call to this formatter.