Create Excel Tables in Java

Following example shows how to create a Table from a range of cells in an Excel file with Java.

Table elements are loaded from and saved to XLSX file format only.

Excel Table created with GemBox.Spreadsheet
Screenshot of Excel Table created with GemBox.Spreadsheet
import com.gembox.spreadsheet.*;
import com.gembox.spreadsheet.tables.*;

class Program {

    public static void main(String[] args) throws java.io.IOException {
        // If using the Professional version, put your serial key below.
        SpreadsheetInfo.setLicense("FREE-LIMITED-KEY");

        ExcelFile workbook = new ExcelFile();
        ExcelWorksheet worksheet = workbook.addWorksheet("Tables");

        // Add some data.
        Object[][] data = {
            { "Worker", "Hours", "Price" },
            { "John Doe", 25, 35.0 },
            { "Jane Doe", 27, 35.0 },
            { "Jack White", 18, 32.0 },
            { "George Black", 31, 35.0 }
        };

        for (int i = 0; i < 5; i++)
            for (int j = 0; j < 3; j++)
                worksheet.getCell(i, j).setValue(data[i][j]);

        // Set column widths.
        worksheet.getColumn(0).setWidth(100, LengthUnit.PIXEL);
        worksheet.getColumn(1).setWidth(70, LengthUnit.PIXEL);
        worksheet.getColumn(2).setWidth(70, LengthUnit.PIXEL);
        worksheet.getColumn(3).setWidth(70, LengthUnit.PIXEL);
        worksheet.getColumn(2).getStyle().setNumberFormat("\"$\"#,##0.00");
        worksheet.getColumn(3).getStyle().setNumberFormat("\"$\"#,##0.00");

        // Create table and enable totals row.
        Table table = worksheet.addTable("Table1", "A1:C5", true);
        table.setHasTotalsRow(true);

        // Add new column.
        TableColumn column = table.addColumn();
        column.setName("Total");

        // Populate column.
        for (ExcelCell cell : column.getDataRange())
            cell.setFormula("=Table1[Hours] * Table1[Price]");

        // Set totals row function for newly added column and calculate it.
        column.setTotalsRowFunction(TotalsRowFunction.SUM);
        column.getRange().calculate();

        // Set table style.
        table.setBuiltInStyle(BuiltInTableStyleName.TABLE_STYLE_MEDIUM_2);

        workbook.save("Tables.xlsx");
    }
}

See also


Next steps

GemBox.Spreadsheet for Java is a component that enables you to read, write, edit and convert spreadsheet files from your applications using one simple API.

Download Buy