Java: Create, Modify, and Remove Tables in Excel Worksheets

In Excel worksheets, a table is a specially designated area within a worksheet that comes with headers, optional total rows, and built-in features such as filtering, sorting, data inserting and deleting, and calculated columns, which greatly facilitate data handling and analysis. For developers looking to automate or integrate Excel data operations within their Java applications, the ability to create, modify, or remove tables within these worksheets becomes an essential skill. This article explores how to create, modify, and remove tables in Excel worksheets with Java using Spire.XLS for Java to manage data in Excel files effectively.

• Create Tables in Excel Worksheets with Java
• Modify Tables in Excel Worksheets with Java
• Remove Tables from Excel Worksheets with 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>15.4.0</version>
    </dependency>
</dependencies>
    

Create Tables in Excel Worksheets with Java

To create a table in an Excel worksheet using Java, you can use the Worksheet.getListObjects().create(String tableName, IXLSRange cellRange) method. Follow these steps to create and customize a table:

• Create an instance of the Workbook class.
• Use the Workbook.loadFromFile() method to load an existing Excel file.
• Retrieve the desired worksheet using the Workbook.getWorksheets().get() method.
• Obtain the table’s cell range using the Worksheet.getRange().get() method.
• Use the Worksheet.getListObjects().create(String tableName, IXLSRange cellRange) method to create the table with a name and range.
• Format the table as needed.
• Save the changes using the Workbook.saveToFile() method.

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

Modify Tables in Excel Worksheets with Java

Spire.XLS for Java provides methods in the IListObject class to modify worksheet table properties, such as the table name, cell range, style, and header visibility. Follow these steps to modify tables in Excel worksheets:

• Create an instance of the Workbook class.
• Use the Workbook.loadFromFile() method to open an existing Excel file.
• Retrieve the worksheet containing the table using the Workbook.getWorksheets().get() method.
• Access the table using the Worksheet.getListObjects().get() method.
• Use the methods in the IListObject class to update the table's properties, such as its name, style, or headers.
• Save the updated workbook using the Workbook.saveToFile() method.

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

Remove Tables from Excel Worksheets with Java

You can remove tables from Excel worksheets using the Worksheet.getListObjects().removeAt(int index) method. This action converts the table area back to a normal cell range and removes any associated formatting. Follow these steps to remove tables:

• Create an instance of the Workbook class.
• Open an existing Excel file using the Workbook.loadFromFile() method.
• Retrieve the worksheet containing the table using the Workbook.getWorksheets().get() method.
• Use the Worksheet.getListObjects().removeAt() method to delete the table by its index.
• Save the changes using the Workbook.saveToFile() method.

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

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.