<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>15.11.3</version>
    </dependency>
</dependencies>

import com.spire.xls.*;

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

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

        // Add a title
        sheet.getCellRange("B1").setText("NUMBER FORMATTING");
        sheet.getCellRange("B1").getCellStyle().getExcelFont().isBold(true);
        sheet.getCellRange("B1:C1").merge(); // Merge cells B1 and C1
        sheet.getCellRange("B1:C1").setHorizontalAlignment(HorizontalAlignType.Center); // Center align the text

        // Add number format examples and corresponding values
        // Add positive number formats
        addNumberFormatExample(sheet, "B3", "C3", "0", "1234.5678");
        addNumberFormatExample(sheet, "B4", "C4", "0.00", "1234.5678");
        addNumberFormatExample(sheet, "B5", "C5", "#,##0.00", "1234.5678");
        addNumberFormatExample(sheet, "B6", "C6", "$#,##0.00", "1234.5678");

        // Add negative number formats
        addNumberFormatExample(sheet, "B7", "C7", "0;[Red]-0", "-1234.5678");
        addNumberFormatExample(sheet, "B8", "C8", "0.00;[Red]-0.00", "-1234.5678");

        // Add scientific notation and percentage formats
        addNumberFormatExample(sheet, "B9", "C9", "0.00E+00", "1234.5678");
        addNumberFormatExample(sheet, "B10", "C10", "0.00%", "0.5678");

        // Add date and time formats
        addNumberFormatExample(sheet, "B11", "C11", "yyyy-MM-dd", "44930.0"); // Excel date value for 2023-01-01
        addNumberFormatExample(sheet, "B12", "C12", "HH:mm:ss", "0.75"); // Excel time value for 18:00:00

        // Add text format
        addNumberFormatExample(sheet, "B13", "C13", "@", "Text Example");

        // Set the formatting
        sheet.getCellRange("B3:B13").getCellStyle().setKnownColor(ExcelColors.Gray25Percent);
        sheet.getCellRange("C3:C13").getCellStyle().setKnownColor(ExcelColors.Gray50Percent);
        sheet.setColumnWidth(2, 24); // Column B
        sheet.setColumnWidth(3, 24); // Column C

        // Save the workbook to a file
        workbook.saveToFile("output/SetExcelNumberFormat.xlsx", FileFormat.Version2016);
        workbook.dispose();
    }

        /**
        * Adds a number format example to the specified cells in the worksheet.
        *
        * @param sheet        The worksheet to modify.
        * @param textCell     The cell for displaying the number format string.
        * @param valueCell    The cell for displaying the formatted value.
        * @param format       The number format code.
        * @param value        The numeric value to format.
        */
        private static void addNumberFormatExample(Worksheet sheet, String textCell, String valueCell, String format, String value) {
            sheet.getCellRange(textCell).setText(format); // Display the number format code
            sheet.getCellRange(valueCell).setValue(value); // Add the value
            // sheet.getCellRange(valueCell).setNumberValue(Double); //  Or set numeric value with setNumberValue() method
            sheet.getCellRange(valueCell).setNumberFormat(format); // Apply the number format
    }
}

import com.spire.xls.*;

import java.util.Calendar;
import java.util.Date;

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

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

        // Add a text value
        sheet.getCellRange("C3").setText("Example Text");
        // Add number value
        sheet.getCellRange("C4").setNumberValue(1234.5678);
        // Add boolean value
        sheet.getCellRange("C5").setBooleanValue(true);
        // Add date time value
        sheet.getCellRange("C6").setDateTimeValue(new Date(2024, Calendar.DECEMBER, 12));
        // Add HTML string
        sheet.getCellRange("C7").setHtmlString("Bold Text");

        // Format the cells
        sheet.getCellRange("C3:C7").setHorizontalAlignment(HorizontalAlignType.Center);
        sheet.getCellRange("C3:C7").setVerticalAlignment(VerticalAlignType.Center);
        sheet.getCellRange("C3:C7").getCellStyle().getExcelFont().setSize(14);
        for (int i = 3; i <= 7; i++) {
            sheet.autoFitColumn(i);
        }

        // Save the workbook
        workbook.saveToFile("output/AddFormattedDataExcel.xlsx", FileFormat.Version2016);
        workbook.dispose();
    }
}

<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>15.11.3</version>
    </dependency>
</dependencies>

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);
    }
}

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);
    }
}

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);
    }
}

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);
    }
}

<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>15.11.3</version>
    </dependency>
</dependencies>

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();
    }
}

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();
    }
}

<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>15.11.3</version>
    </dependency>
</dependencies>

import com.spire.xls.CellRange;
import com.spire.xls.TableBuiltInStyles;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
import com.spire.xls.core.IListObject;

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

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

        // Get the cell range of the table
        CellRange range = sheet.getCellRange("A1:I11");

        // Create a table
        IListObject table1 = sheet.getListObjects().create("Table1", range);

        // Apply a built-in style to the table
        table1.setBuiltInTableStyle(TableBuiltInStyles.TableStyleLight16);

        // Save the workbook
        workbook.saveToFile("output/CreateTableExcel.xlsx");
        workbook.dispose();
    }
}

import com.spire.xls.TableBuiltInStyles;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
import com.spire.xls.core.IListObject;

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

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

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

        // Get the table
        IListObject table = sheet.getListObjects().get(0);

        // Modify the table
        table.setName("NewTable"); // Change the name
        table.setLocation(sheet.getRange().get("A1:D11")); // Change the location
        table.setBuiltInTableStyle(TableBuiltInStyles.TableStyleDark5); // Change the style
        table.setDisplayHeaderRow(false); // Hide the header row

        // Save the workbook
        workbook.saveToFile("output/ModifyTableExcel.xlsx");
        workbook.dispose();
    }
}

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

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

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

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

        // Remove the table
        sheet.getListObjects().removeAt(0);

        // Save the workbook
        workbook.saveToFile("output/RemoveTableExcel.xlsx");
        workbook.dispose();
    }
}