Java - Convert Excel Cell Value from Text to Number or Number to Text

When processing Excel files, you may find that some cell values look like numbers, but they are actually stored as text. If you use these cells in Excel functions like SUM and Average, you will get incorrect calculation results because these functions ignore cells that contain text values. In this case, you need to convert the numbers stored as text back to general numbers. This article will explain how to convert Excel cell value from text to number or number to text in Java using Free Spire.XLS for Java API.

Add Dependencies

Method 1: If you are using maven, you can easily import the JAR file of Free Spire.XLS for Java API into your application by adding the following code to your project's 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>
<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls.free</artifactId>
        <version>5.1.0</version>
    </dependency>
</dependencies>

Method 2: If you are not using maven, you can download Free Spire.XLS for Java from the official website, extract the zip file, then import the Spire.Xls.jar file under the lib folder into your project as a dependency.

Convert Excel Cell Value from Text to Number in Java

Free Spire.XLS for Java provides the convertToNumber() method of the CellRange class to convert number values formatted as text back to numbers. The following are the detailed steps to do so:

• Initialize an instance of the Workbook class.
• Load an Excel file using Workbook.loadFromFile() method.
• Get the desired worksheet using Workbook.getWorksheets().get(int) method.
• Access the specific cell range that contains numbers stored as text using Worksheet.getCellRange(int, int, int, int) method.
• Call the CellRange.convertToNumber() method to convert the numbers stored as text back to numbers.
• Save the result file using Workbook.saveToFile(String, ExcelVersion) method.

import com.spire.xls.CellRange;
import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

public class ConvertTextToNumber {
    public static void main(String []args){
        //Create a Workbook instance
        Workbook workbook = new Workbook();
        //Load an Excel file
        workbook.loadFromFile("Sample1.xlsx");

        //Get the first worksheet 
        Worksheet sheet = workbook.getWorksheets().get(0);
        //Get the specific cell range that contains numbers stored as text
        CellRange range = sheet.getCellRange(1, 1, sheet.getLastRow(), sheet.getLastColumn());
        //Convert numbers stored as text back to number
        range.convertToNumber();

        //Save the result file
        workbook.saveToFile("ConvertTextToNumber.xlsx", ExcelVersion.Version2016);
    }
}

Convert Excel Cell Value from Number to Text in Java

You can format the numbers in Excel cells as text, decimals, dates etc. using the setNumberFormat() method of the CellRange class. The following steps show you how to format numbers in Excel cells as text:

• Initialize an instance of the Workbook class.
• Load an Excel file using Workbook.loadFromFile() method.
• Get the desired worksheet using Workbook.getWorksheets().get(int) method.
• Access the specific cell range that contains numbers using Worksheet.getCellRange(int, int, int, int) method.
• Call the CellRange.setNumberFormat(String) method to convert the numbers to text.
• Save the result file using Workbook.saveToFile() method.

import com.spire.xls.CellRange;
import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

public class ConvertNumberToText {
    public static void main(String []args){
        //Create a Workbook instance
        Workbook workbook = new Workbook();
        //Load an Excel file
        workbook.loadFromFile("Sample2.xlsx");

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);
        //Get the specific cell range that contains numbers
        CellRange range = sheet.getCellRange(1, 1, sheet.getLastRow(), sheet.getLastColumn());
        //Convert numbers to text
        range.setNumberFormat("@");

        //Save the result file
        workbook.saveToFile("ConvertNumberToText.xlsx", ExcelVersion.Version2016);
    }
}