Number formatting is a way to control how a number is displayed using numeric format string. For example, you can use format string '0.00' to format 1234.5678 as 1234.57. Numeric format strings often consist of one or more custom numeric specifiers listed as below:

• "#" - Digit placeholder
• "0" - Zero placeholder
• "," - Decimal point
• "." - Decimal separator
• "[Red]" - Color specifier
• "%" - Percentage placeholder

The following example demonstrates how to set number format in Excel using Spire.XLS for Java.

import com.spire.xls.*;

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

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

        //Add a string to cell “B1”
        sheet.getCellRange("B1").setText("NUMBER FORMATTING");
        sheet.getCellRange("B1").getCellStyle().getExcelFont().isBold(true);
        sheet.getCellRange("B1:C1").merge();
        sheet.getCellRange("B1:C1").setHorizontalAlignment(HorizontalAlignType.Center);

        
        //Add a string to cell “B3”
        sheet.getCellRange("B3").setText("0");
       //Add a number to cell “C3” and set the number format
        sheet.getCellRange("C3").setNumberValue(1234.5678);
        sheet.getCellRange("C3").setNumberFormat("0");

        //Repeat the above step to add string and number to other cells and set the number format
        sheet.getCellRange("B4").setText("0.00");
        sheet.getCellRange("C4").setNumberValue(1234.5678);
        sheet.getCellRange("C4").setNumberFormat("0.00");

        sheet.getCellRange("B5").setText("#,##0.00");
        sheet.getCellRange("C5").setNumberValue(1234.5678);
        sheet.getCellRange("C5").setNumberFormat("#,##0.00");

        sheet.getCellRange("B6").setText("$#,##0.00");
        sheet.getCellRange("C6").setNumberValue(1234.5678);
        sheet.getCellRange("C6").setNumberFormat("$#,##0.00");

        sheet.getCellRange("B7").setText("0;[Red]-0");
        sheet.getCellRange("C7").setNumberValue(-1234.5678);
        sheet.getCellRange("C7").setNumberFormat("0;[Red]-0");

        sheet.getCellRange("B8").setText("0.00;[Red]-0.00");
        sheet.getCellRange("C8").setNumberValue(-1234.5678);
        sheet.getCellRange("C8").setNumberFormat("0.00;[Red]-0.00");

        sheet.getCellRange("B9").setText("#,##0;[Red]-#,##0");
        sheet.getCellRange("C9").setNumberValue(-1234.5678);
        sheet.getCellRange("C9").setNumberFormat("#,##0;[Red]-#,##0");

        sheet.getCellRange("B10").setText("#,##0.00;[Red]-#,##0.000");
        sheet.getCellRange("C10").setNumberValue(-1234.5678);
        sheet.getCellRange("C10").setNumberFormat("#,##0.00;[Red]-#,##0.00");

        sheet.getCellRange("B11").setText("0.00E+00");
        sheet.getCellRange("C11").setNumberValue(1234.5678);
        sheet.getCellRange("C11").setNumberFormat("0.00E+00");

        sheet.getCellRange("B12").setText("0.00%");
        sheet.getCellRange("C12").setNumberValue(1234.5678);
        sheet.getCellRange("C12").setNumberFormat("0.00%");

        //Set background color for specified cells 
        sheet.getCellRange("B3:B12").getCellStyle().setKnownColor(ExcelColors.Gray25Percent);
        sheet.getCellRange("C3:C12").getCellStyle().setKnownColor(ExcelColors.Gray50Percent);

        //Set column width for specified columns
        sheet.setColumnWidth(2, 24);
        sheet.setColumnWidth(3, 24);

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

The following screenshot shows the output Excel files after setting number format:

When working with large datasets, finding information that matches certain criteria in seconds can be quite challenging. Fortunately, MS Excel provides the AutoFilter tool to help you narrow down the search by displaying only the relevant information and hiding all other data from view. In this article, you will learn how to add or remove AutoFilter in Excel with Python using Spire.XLS for Java.

• Add AutoFilter to Excel Cells in Java
• Apply Date AutoFilter in Excel in Java
• Apply Custom AutoFilter in Excel in Java
• Remove AutoFilter in Excel in Java

Install Spire.XLS for Java

First of all, you're required to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in 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</artifactId>
        <version>14.4.1</version>
    </dependency>
</dependencies>
    

Add AutoFilter to Excel Cells in Java

Spire.XLS for Java allows you to apply AutoFilter on a specific cell range through the Worksheet.getAutoFilters().setRange() method. The following are the detailed steps:

• Create a Workbook instance.
• Load a sample Excel file using Workbook.loadFromFile() method.
• Get a specified worksheet using Workbook.getWorksheets().get() method.
• Add an AutoFilter to a specified cell range using Worksheet.getAutoFilters().setRange() method.
• Save the result file using Workbook.saveToFile() method.

import com.spire.xls.*;

public class createFilter {
    public static void main(String[] args) {

        //Create a Workbook instance
        Workbook workbook = new Workbook();

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

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

        //Create an AutoFilter in the sheet and specify the range to be filtered
        sheet.getAutoFilters().setRange(sheet.getCellRange("A1:C1"));

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

Apply Date AutoFilter in Excel in Java

If you need to explore information related to specific dates or time, you can apply a date filter to the selected range using the Workbook.getAutoFilters().addDateFilter(IAutoFilter column, DateTimeGroupingType dateTimeGroupingType, int year, int month, int day, int hour, int minute, int second) method. The following are detailed steps.

• Create a Workbook instance.
• Load a sample Excel file using Workbook.loadFromFile() method.
• Get a specified worksheet using Workbook.getWorksheets().get() method.
• Add an AutoFilter to a specified range using Workbook.getAutoFilters().setRange() method.
• Get the column to be filtered.
• Call the Workbook.getAutoFilters().addDateFilter() method to add a date filter to the column to filter data related to a specified year/month/date, etc.
• Apply the filter using Workbook.getAutoFilters().filter() method.
• Save the result file using Workbook.saveToFile() method.

import com.spire.xls.*;
import com.spire.xls.core.IAutoFilter;
import com.spire.xls.core.spreadsheet.autofilter.DateTimeGroupingType;

public class ApplyDateFilter {
    public static void main(String[] args) {

        //Create a Workbook instance
        Workbook workbook = new Workbook();

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

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

        //Create an auto filter in the sheet and specify the range to be filtered
        sheet.getAutoFilters().setRange(sheet.getCellRange("A1:A12"));

        //Get the column to be filtered
        IAutoFilter filterColumn = sheet.getAutoFilters().get(0);

        //Add a date filter to filter data related to February 2022
        sheet.getAutoFilters().addDateFilter(filterColumn, DateTimeGroupingType.Month, 2022, 2, 0, 0, 0, 0);

        //Apply the filter
        sheet.getAutoFilters().filter();

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

Apply Custom AutoFilter in Excel in Java

The Workbook.getAutoFilters().customFilter(FilterColumn column, FilterOperatorType operatorType, java.lang.Object criteria) method allows you to create custom filters based on certain criteria. For example, you can filter data that contains specific text. The following are detailed steps.

• Create a Workbook instance.
• Load a sample Excel file using Workbook.LoadFromFile() method.
• Get a specified worksheet using Workbook.getWorksheets().get() method.
• Add an AutoFilter to a specified range using Workbook.getAutoFilters().setRange() method.
• Get the column to be filtered.
• Add a custom filter to the column to filter data containing the specified string using Workbook.getAutoFilters().customFilter() method.
• Apply the filter using Workbook.getAutoFilters().filter() method.
• Save the result file using Workbook.saveToFile() method.

import com.spire.xls.*;
import com.spire.xls.core.spreadsheet.autofilter.FilterColumn;
import com.spire.xls.core.spreadsheet.autofilter.FilterOperatorType;

public class CustomFilter {
    public static void main(String[] args) {

        //Create a Workbook instance
        Workbook workbook = new Workbook();

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

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

        //Create an auto filter in the sheet and specify the range to be filtered
        sheet.getAutoFilters().setRange(sheet.getCellRange("G1:G12"));

        //Get the column to be filtered
        FilterColumn filterColumn = sheet.getAutoFilters().get(0);

        //Add a custom filter to filter data containing the string "Grocery"
        String strCrt = "Grocery";
        sheet.getAutoFilters().customFilter(filterColumn, FilterOperatorType.Equal, strCrt);

        //Apply the filter
        sheet.getAutoFilters().filter();

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

Remove AutoFilter in Excel in Java

In addition to adding AutoFilters in Excel files, Spire.XLS for Java also support removing or deleting the AutoFilters from Excel through the Worksheet.getAutoFilters().clear() method. The following are detailed steps.

• Create a Workbook instance.
• Load a sample Excel file using Workbook.loadFromFile() method.
• Get a specified worksheet using Workbook.getWorksheets().get() method.
• Remove AutoFilter from the worksheet using Worksheet.getAutoFilters().clear() method.
• Save the result file using Workbook.saveToFile() method.

import com.spire.xls.*;

public class removeAutoFilters {
    public static void main(String[] args) {

        //Create a Workbook instance
        Workbook workbook = new Workbook();

        //Load an Excel file
        workbook.loadFromFile("CustomAutoFilter.xlsx");

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

        //Remove the auto filters
        sheet.getAutoFilters().clear();

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

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Excel is a versatile tool extensively utilized for data management and analysis. There are occasions when you may require locating specific data within an Excel file and replacing it with updated values. In this article, we will explore how to find and replace data in Excel in Java using Spire.XLS for Java.

• Find and Replace Data in a Worksheet in Excel
• Find and Replace Data in a Specific Cell Range in Excel

Install Spire.XLS for Java

First of all, you're required to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in 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</artifactId>
        <version>14.4.1</version>
    </dependency>
</dependencies>
    

Find and Replace Data in a Worksheet in Excel

The Worksheet.findAllString() method provided by Spire.XLS for Java can help you find the cells containing specific text in Excel documents. Once found, you can conveniently replace these values with new ones using the CellRange.setText() method. The steps are as follows:

• Create an instance of Workbook class.
• Load an Excel file using Workbook.loadFromFile() method.
• Get the desired worksheet using Workbook.getWorksheets().get() method.
• Find the specific value in the worksheet using Worksheet.findAllString() method and replace the value of the cell with another value using CellRange.setText() method.
• Set a background for the cell so you can easily find the updated cells using CellRange. getStyle().setColor() method.
• 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;

import java.awt.Color;

public class ReplaceData {
    public static void main(String[] args) {
        // Initialize an instance of the Workbook class
        Workbook workbook = new Workbook();
        // Load an Excel file
        workbook.loadFromFile("Test.xlsx");

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

        // Find the cells with the specific string value "Total" in the worksheet
        CellRange[] cells = worksheet.findAllString("Total", true, true);

        // Iterate through the found cells
        for (CellRange cell : cells) {
            // Replace the value of the cell with another value
            cell.setText("Sum");
            // Set a background color for the cell
            cell.getStyle().setColor(Color.YELLOW);
        }

        // Save the result file to a specific location
        workbook.saveToFile("ReplaceDataInWorksheet.xlsx", ExcelVersion.Version2016);
        workbook.dispose();
    }
}

Find and Replace Data in a Specific Cell Range in Excel

To replace data within a specific range of cells, you can utilize the CellRange.findAllString() method to locate cells within the range that contain the desired values. Then, use the CellRange.setText() method to replace the cell value with a new value. The detailed steps are as follows:

• Create an instance of Workbook class.
• Load an Excel file using Workbook.loadFromFile() method.
• Get the desired worksheet using Workbook.getWorksheets().get() method.
• Get a specific cell range using Worksheet.getCellRange() method.
• Find the cells with the specific value in the cell range using CellRange.findAllString() method.
• Iterate through the found cells
• Replace the value of the cell with another value using CellRange setText() method.
• Set a background for the cell so you can easily find the updated cells using the CellRange. getStyle().setColor() method.
• 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;

import java.awt.Color;

public class ReplaceDataInCellRange {
    public static void main(String[] args) {
        // Initialize an instance of the Workbook class
        Workbook workbook = new Workbook();
        // Load an Excel file
        workbook.loadFromFile("Test.xlsx");

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

        // Get a specific cell range
        CellRange range = worksheet.getCellRange("A1:C12");

        // Find the cells with the specific value "Total" in the cell range
        CellRange[] cells = range.findAllString("Total", true, true);

        // Iterate through the found cells
        for (CellRange cell : cells) {
            // Replace the value of the cell with another value
            cell.setText("Sum");
            // Set a background color for the cell
            cell.getStyle().setColor(Color.YELLOW);
        }

        // Save the result file to a specific location
        workbook.saveToFile("ReplaceDataInCellRange.xlsx", ExcelVersion.Version2016);
        workbook.dispose();
    }
}

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Spire.XLS for Java supports to create and format the table on Excel file. This article will show you how to create a simple table by the range of data from an existing worksheet.

Firstly, view the sample Excel worksheet:

mport com.spire.xls.*;

public class CreateTable {
    public static void main(String[] args) throws Exception {

        String inputFile = "Sample1.xlsx";
        String outputFile = "output/CreateTable.xlsx";

        //Create a workbook and load a file
        Workbook workbook = new Workbook();
        workbook.loadFromFile(inputFile);

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

        //Create table with the data in given range
        sheet.getListObjects().create("table", sheet.getCellRange(1, 1, 13, 4));

        //Add Default Style to the table.        sheet.getListObjects().get(0).setBuiltInTableStyle(TableBuiltInStyles.TableStyleLight9);

        //Save the Excel file
        workbook.saveToFile(outputFile, ExcelVersion.Version2010);
    }
}

Effective screenshot after creating the Excel table: