How to Apply Floating Point Format in Apache POI Based on Database Values

Описание: Learn how to conditionally apply `floating point formatting` using Apache POI if numbers are decimal. Ensure that integer values are displayed correctly in Excel without unnecessary decimal places.
---
This video is based on the question https://stackoverflow.com/q/66171321/ asked by the user 'Meow' ( https://stackoverflow.com/u/3097279/ ) and on the answer https://stackoverflow.com/a/66172063/ provided by the user 'Axel Richter' ( https://stackoverflow.com/u/3915431/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Apache POI apply foating point format only if Number is decimal

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Working with Apache POI: Conditional Floating Point Formatting

When working with numbers in Excel, particularly when exporting data from a database, it's important to display these numbers in a way that conveys valuable information without cluttering the view. For example, think about the difference between displaying a whole number like 12 and a decimal number like 12.1. If you're using Apache POI for Excel operations, applying the appropriate number format can sometimes be a challenge, especially when you want to show decimals only if they exist in the original data.

The Problem Statement

You are retrieving number values from an Oracle database and saving them into an Excel file using Apache POI. The requirement is to format these numbers correctly based on their original values from the database:

Desired Outcome:

12 should be displayed as 12 (no decimal places)

12.1 should be displayed as 12.10 (showing two decimal places)

12.123 should be displayed as 12.12 (rounding to two decimal places)

The Solution: Conditional Formatting Based on Number Type

Understanding Apache POI and Number Formatting

In Apache POI, if you don’t specify a special number format for a cell, it defaults to the General number format. This means:

Whole numbers will display without decimals.

Decimal numbers will display as intended up to however many decimals they have.

To achieve the desired formatting, you can use two different styles:

A format for integer values (e.g., 0 or # ,# # 0 for thousands separators).

A format for decimal values (e.g., # ,# # 0.00 for two decimal places).

Steps to Implement the Formatting Logic

To determine if a number is an integer or a decimal, you can leverage the methods available in the Number class. The fundamental check is whether the integer value of the number equals its decimal value. If they are equal, the number is an integer; if not, it is a decimal.

Here’s how to implement this logic using Java and Apache POI:

1. Set Up the Workbook and Cell Styles

You will first need to create an instance of your workbook (either HSSFWorkbook or XSSFWorkbook) and set up the necessary cell styles for integers and decimals:

[[See Video to Reveal this Text or Code Snippet]]

2. Create a Sheet and Define Your Data

This step involves creating a sheet within the workbook and defining the numerical data you wish to handle:

[[See Video to Reveal this Text or Code Snippet]]

3. Populate the Sheet with Formatted Values

Now you'll loop through the list of numbers, evaluating each one to determine if it should be formatted as an integer or a decimal:

[[See Video to Reveal this Text or Code Snippet]]

4. Write to File and Close the Workbook

Finally, you’ll want to write the workbook to a file and ensure all resources are properly closed:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

By following these steps, you can effectively control the appearance of numerical data in your Excel files created with Apache POI. This flexibility not only makes your Excel files clearer but also adds a professional touch to your data presentations. Using conditional formatting based on number types helps maintain the integrity of the data while ensuring that values are easily readable.

Now you can confidently format numeric values from your database as required, making your Excel exports both functional and informative!