Activity

1. dimastbk commented on Nov 2, 2024

   

   dimastbk

   on Nov 2, 2024

   Owner

   More actions

   Hi.

   Sometimes an integer in a table is incorrectly converted to a floating point number.

   All numbers in Excel are stored as floats, so it's hard to detect the type of number with 100% success. Could you give an example file with this problem?

   Also, can I skip the empty lines in the middle of the sheet?

   No, and I don't have a plan to add this feature.
2. B-D-T commented on Aug 30, 2026

   

   B-D-T

   on Aug 30, 2026

   More actions

   From @dimastbk:

   All numbers in Excel are stored as floats

   I don't think this is exactly accurate. As I understand it, all Excel values are stored as plain text in XML files, then the calamine read engine parses the text and interprets it accordingly (see datatype.rs). That is, although strings are handled directly¹, Excel's numeric values don't have meaning (int or float) until the engine interprets them during reading.

   Excel Example

   Click to show initial Excel file set-up

   Input data

   Create a new spreadsheet and put the following in A1:B5 (or just copy-paste the table below).

   Data value	Notes
   Dog	a string
   Cat	a string
   123	an integer
   123	an integer
   3.14	a float

   Content summary:

   • 7 strings: 'Data value', 'Notes', 'a string', 'a string', 'an integer', 'an integer', 'a float'
   • 5 unique strings: 'Data value', 'Notes', 'a string', 'an integer', 'a float'
   • 2 numeric values: 123, 3.14

   Explore the file contents

   Click for instructions on seeing an Excel file as XML

   Change the Excel file extension to .zip (on a Windows device). Now you can navigate through the file contents more easily. If you look in the /xl/worksheets/sheet1.xml file, you see the cells' content.

   Show XML

   <sheetData>
       <row r="1" spans="1:2" x14ac:dyDescent="0.35">
           <c r="A1" t="s">
               <v>1</v>
           </c>
           <c r="B1" t="s">
               <v>2</v>
           </c>
       </row>
       <row r="2" spans="1:2" x14ac:dyDescent="0.35">
           <c r="A2" t="s">
               <v>0</v>
           </c>
           <c r="B2" t="s">
               <v>3</v>
           </c>
       </row>
       <row r="3" spans="1:2" x14ac:dyDescent="0.35">
           <c r="A3" t="s">
               <v>4</v>
           </c>
           <c r="B3" t="s">
               <v>3</v>
           </c>
       </row>
       <row r="4" spans="1:2" x14ac:dyDescent="0.35">
           <c r="A4">
               <v>123</v>
           </c>
           <c r="B4" t="s">
               <v>5</v>
           </c>
       </row>
       <row r="5" spans="1:2" x14ac:dyDescent="0.35">
           <c r="A5" s="1">
               <v>123</v>
           </c>
           <c r="B5" t="s">
               <v>5</v>
           </c>
       </row>
       <row r="6" spans="1:2" x14ac:dyDescent="0.35">
           <c r="A6">
               <v>3.14</v>
           </c>
           <c r="B6" t="s">
               <v>6</v>
           </c>
       </row>
   </sheetData>

   The numeric values are in row r="5" and row r="6", which are stored as shown below²:

   <c r="A5" s="1">
       <v>123</v>
   </c>
   ...
   <c r="A6">
       <v>3.14</v>
   </c>

   We can see that both the integer and float values are stored as plain text within the <v> tags; Excel doesn't differentiate between the two.

   Footnotes

   1. String values are stored in a dedicated file: /xl/sharedStrings.xml. Then, in worksheet files like sheet1.xml, the string values are referenced by an index number. Excel flags these cells as strings by using the t="s" attribute in the <c ...> tag. ↩

   2. The s="1" attribute is just a reference to the cell's style, which is defined in /xl/styles.xml. It doesn't affect the value of the cell. ↩
3. B-D-T commented on Aug 30, 2026

   

   B-D-T

   on Aug 30, 2026

   Last edited by B-D-T

   More actions

   @Rex859619

   Also, can I skip the empty lines in the middle of the sheet?

   If you're using polars read_excel in python, would using drop_empty_rows = True be a solution for dropping your empty middle rows?

   (EDIT: actually, this might only work with the xlsx2csv engine. I'm not sure.)