DEV Community

Leon Davis
Leon Davis

Posted on

Convert Number-to-Text and Text-to-Number in Excel Using Java

#java #excel #texttonumber #numbertotext

In data processing, it's common to encounter situations where you need to convert text to numbers or numbers to text in Excel cells. These conversions are often necessary in tasks like data cleaning, analysis, and report generation. In this article, we will explore how to perform number-to-text and text-to-number conversions in Excel using Java, leveraging the Free Spire.XLS library for the operations.

1. Introduction to Free Spire.XLS

Free Spire.XLS for Java is a free Java library designed to read and write Microsoft Excel files, including XLS and XLSX formats. It also supports CSV file reading and writing, and is compatible with OpenOffice's ODS format and WPS’s ET format. This library enables us to perform various Excel file operations, such as reading data, writing data, and converting between different data types with ease.

2. Installing Free Spire.XLS

Before diving into the code, ensure that your Java project has the Free Spire.XLS dependency added. If you're using Maven, you can include the following dependency in your pom.xml file:

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <name>e-iceblue</name>
        <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
    </repository>
</repositories>
<dependency>
    <groupId>e-iceblue</groupId>
    <artifactId>spire.xls.free</artifactId>
    <version>5.3.0</version>
</dependency>
Enter fullscreen mode Exit fullscreen mode

If you're not using Maven, you can download the JAR file directly from the website and add it to your project manually.

3. Converting Numbers to Text and Text to Numbers in Excel

3.1 Converting Numbers to Text

In Excel, numbers are typically recognized as numerical data types. However, if you need to convert a number into text, you can do so by setting the number format of the cell to "@", which designates it as a text format.

Here’s an example of how to convert numbers to text:

import com.spire.xls.*;

public class ConvertNumberToText {
    public static void main(String[] args) {
        // Create a Workbook object
        Workbook workbook = new Workbook();

        // Load the Excel file
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\Employee.xlsx");

        // Get the specified worksheet
        Worksheet worksheet = workbook.getWorksheets().get(0);

        // Get the range of cells to be converted
        CellRange cellRange = worksheet.getRange().get("F2:F9");

        // Set the cell format to text
        cellRange.setNumberFormat("@");

        // Save the modified workbook
        workbook.saveToFile("output/NumbersToText.xlsx");

        // Dispose of resources
        workbook.dispose();
    }
}
Enter fullscreen mode Exit fullscreen mode

In this example:

  • The Excel file Employee.xlsx is loaded.
  • The NumberFormat is set to "@", converting the numbers in the range F2:F9 to text.
  • The modified file is saved as NumbersToText.xlsx.

3.2 Converting Text to Numbers

When text in Excel represents numeric data, it may need to be converted back into numbers for calculations or further processing. The CellRange.convertToNumber() method provided by Free Spire.XLS handles this conversion.

Here’s an example of how to convert text to numbers:

import com.spire.xls.*;

public class ConvertTextToNumber {
    public static void main(String[] args) {
        // Create a Workbook object
        Workbook workbook = new Workbook();

        // Load the Excel file
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx");

        // Get the specified worksheet
        Worksheet worksheet = workbook.getWorksheets().get(0);

        // Get the range of cells to be converted
        CellRange range = worksheet.getRange().get("D2:G13");

        // Convert text to numbers
        range.convertToNumber();

        // Save the modified workbook
        workbook.saveToFile("output/TextToNumbers.xlsx");

        // Dispose of resources
        workbook.dispose();
    }
}
Enter fullscreen mode Exit fullscreen mode

In this example:

  • The Excel file Input.xlsx is loaded.
  • The range D2:G13 is selected, and the convertToNumber() method is used to convert text values in this range to numbers.
  • The modified file is saved as TextToNumbers.xlsx.

4. Key Considerations

  1. Distinguishing Text and Numbers : Make sure that the text cells contain only numeric characters. If there are any non-numeric characters, the conversion may not work correctly.
  2. Data Formatting : If you need to preserve the formatting of numbers (e.g., currency, percentages), you may need to set the cell format while converting. You can use the setNumberFormat() method for this.

5. Conclusion

With Free Spire.XLS , you can easily perform number-to-text and text-to-number conversions in Excel using Java. Whether you need to convert numbers to text or convert text back to numbers, these operations simplify your tasks during data processing and report generation. Mastering these fundamental operations will help developers efficiently manage and process Excel data.

By following the code examples provided, you can automate these conversions and ensure that your Excel files are correctly formatted for further analysis, presentation, or reporting.

Top comments (0)