DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

How does AI transform chaos engineering from an experiment into a critical capability? Learn how to effectively operationalize the chaos.

Data quality isn't just a technical issue: It impacts an organization's compliance, operational efficiency, and customer satisfaction.

Are you a front-end or full-stack developer frustrated by front-end distractions? Learn to move forward with tooling and clear boundaries.

Developer Experience: Demand to support engineering teams has risen, and there is a shift from traditional DevOps to workflow improvements.

Related

  • Practical Generators in Go 1.23 for Database Pagination
  • How To Get Cell Data From an Excel Spreadsheet Using APIs in Java
  • Exploring Exciting New Features in Java 17 With Examples
  • Unraveling Lombok's Code Design Pitfalls: Exploring Encapsulation Issues

Trending

  • TIOBE Programming Index News June 2025: SQL Falls to Record Low Popularity
  • What is Microsoft Fabric for Azure Cloud (Beyond the Buzz) and How It Competes with Snowflake and Databricks
  • What They Don’t Teach You About Starting Your First IT Job
  • Jakarta WebSocket Essentials: A Guide to Full-Duplex Communication in Java
  1. DZone
  2. Data Engineering
  3. Data
  4. When Reading Excel with POI, Beware of Floating Points

When Reading Excel with POI, Beware of Floating Points

Our problem began when we tried to read a certain cell that contained the value 929 as a numeric field and store it into an integer.

By 
Lieven Doclo user avatar
Lieven Doclo
·
Aug. 30, 13 · Tutorial
Likes (1)
Comment
Save
Tweet
Share
47.4K Views

Join the DZone community and get the full member experience.

Join For Free

Recently, at my current project, we’ve been hunting down a bug in our software. See, our source data comes from Excel files and need to be imported into our system. Since it’s a Java project, we’re using POI.

Our problem began when we tried to read a certain cell that contained the value 929 as a numeric field and store it into an integer. So we did something like this.

int value = new Double(cell.getNumericCellValue()).intValue();

POI only has one way to read numeric cell values, and that’s by using  double. Guess what we got after reading the cell? 928. The reason? The numeric cell value, that shows in Excel as 929, is actually read as 928.99999999… You have to love floating points. You’re probably wondering why we just didn’t read the value as a String value and used Integer.parseInt(...). Well, POI doesn’t allow reading a cell as a String value if it’s actually a numeric cell type.

The solution?

int value = new BigDecimal(d).setScale(0, RoundingMode.HALF_UP).intValue()

The sad part is that this only happens for certain numeric values, if you’re unlucky, the first code example will work (until it suddenly doesn’t anymore). Again, that’s floating point logic. This, by the way, is why I hate floating point and why I think they shouldn’t be allowed in modern development. It’s the principle of least surprise. Groovy, for examples, understands this and considers all decimal values in code as BigDecimal (and allows for easy math operations on those fields, unlike Java). You need to explicitly state your want to use a double.

So remember, when reading Excel numeric values, what you see isn’t always what you get. That, and always use BigDecimal, even if the API forces you to use floating point fields. There’s really no excuse for using double or float.

IT Data (computing) Data Types Groovy (programming language) Java (programming language)

Published at DZone with permission of Lieven Doclo, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • Practical Generators in Go 1.23 for Database Pagination
  • How To Get Cell Data From an Excel Spreadsheet Using APIs in Java
  • Exploring Exciting New Features in Java 17 With Examples
  • Unraveling Lombok's Code Design Pitfalls: Exploring Encapsulation Issues

Partner Resources

×

Comments

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • [email protected]

Let's be friends: