This article contains information to create Excel table using Java. It has the details to set the development environment, a list of steps to create a table for the range of data, and a sample code demonstrating how to make a table in Excel using Java. It will assist you to customize the table by setting various table styles.
Steps to Create MS Excel Table using Java
- Set the development environment to use Aspose.Cells for Java to create a table
- Load or create an Excel file with the Workbook object and access a sheet
- Add a new list object as a table encompassing the data and flag to mark the existence of the headers
- Set the table style using the TableStyleType enumerator
- Set the flag to show the total of numbers in the respective columns
- set the count of records for a particular column
- Save the resultant workbook with a formatted table
These steps define how to create a data table in Excel using Java. Commence the process by loading a Workbook with some data and creating a list object as a table around the data. Set the table style using the TableStyleType enumerator, set the flag to show totals at the end of all columns having number data, and set the calculation mode to COUNT using the TotalsCalculation enumerator before saving the workbook.
Code to Add Table to Excel using Java
| import com.aspose.cells.*; | |
| import java.util.Random; | |
| public class Main | |
| { | |
| public static void main(String[] args) throws Exception // Create table in Excel using Java | |
| { | |
| // Set the licenses | |
| new License().setLicense("License.lic"); | |
| // Create a workbook. | |
| Workbook wb = new Workbook(); | |
| // If the workbook is empty, call this function (Optional) | |
| CreateSampleData(wb); | |
| // Access the first sheet | |
| Worksheet sheet = wb.getWorksheets().get(0); | |
| // Add a new list object | |
| int listObjectIndex = sheet.getListObjects().add("A1", "E20", true); | |
| ListObject listObject = sheet.getListObjects().get(listObjectIndex); | |
| // Set table style | |
| listObject.setTableStyleType(TableStyleType.TABLE_STYLE_MEDIUM_10); | |
| // Set the show total flag | |
| listObject.setShowTotals(true); | |
| // Set the calculation type | |
| listObject.getListColumns().get(1).setTotalsCalculation(TotalsCalculation.COUNT); | |
| // Saving the Excel file | |
| wb.save("output.xlsx"); | |
| System.out.println("Done"); | |
| } | |
| static void CreateSampleData(Workbook wb) | |
| { | |
| // Fill workbook with some dummy data | |
| String[] titles = new String[] {"Employee", "Quarter", "Product", "Country","Sale"}; | |
| String[] employees = new String[] {"David", "James","Miya" }; | |
| String[] products = new String[] { "Chai", "Chang", "Geitost", "Maxilaku" }; | |
| String[] countries = new String[] { "Brazil", "China", "France", "Germany", "India", "Italy" }; | |
| for (int idx = 0; idx < titles.length; idx++) { | |
| wb.getWorksheets().get(0).getCells().get(0, idx).setValue(titles[idx]); | |
| } | |
| Random random = new Random(); | |
| for (int i = 1; i < 20; i++) { | |
| wb.getWorksheets().get(0).getCells().get(i, 0).setValue(employees[random.nextInt(employees.length)]); | |
| wb.getWorksheets().get(0).getCells().get(i, 1).setValue((random.nextInt(4)) + 1); | |
| wb.getWorksheets().get(0).getCells().get(i, 2).setValue(products[random.nextInt(products.length)]); | |
| wb.getWorksheets().get(0).getCells().get(i, 3).setValue(countries[random.nextInt(countries.length)]); | |
| wb.getWorksheets().get(0).getCells().get(i, 4).setValue(random.nextInt(2000)); | |
| } | |
| } | |
| } |
This sample code shows how to create a table in Excel using Java. We have used TotalsCalculation.COUNT, however, you may use other options such as AVERAGE, MIN, MAX, SUM, and STD_DEV as per the requirements. The ListObject has various methods like setShowTableStyleRowStripes to display stripes, setShowHeaderRow to display headers, and setComment to set table comments to list a few.
This article has introduced us to creating advanced Excel tables using Java. If you want to create a pivot table, refer to the article on Create pivot table in Excel using Java.
