How to Add an Excel XLSX API Library to Your Java App

This tutorial will create a real-life scenario with Document Solutions for Excel, Java Edition (DsExcel Java) to provide a fundamental understanding of the library's capabilities. The steps will show how to create a simple budget in a Microsoft Excel XLSX file using DsExcel Java, with no dependency on Microsoft Excel.

Steps to Add an Excel API to a Java Application

1. Create a Java Application Including the Java Excel API
2. Create Excel Tables Using the ITable Interface
   a. Initialize Table Data
   b. Add the Excel Tables
   c. Apply Table Formulas
   d. Set the Table’s Row Height & Column Width
3. Save the Java Excel Workbook

Download the latest release of our Java Excel API, Document Solutions for Excel, Java Edition.

Create a Java Application Including the Java Excel API

Create a new Java Project. For this example, we will use the Eclipse Java IDE.

After clicking the Next button, navigate to the Libraries tab. Add the DsExcel Java package and the dependencies for the DsExcel Java library. This includes:

• dsexcel-x.x.x.jar - download trial package here or from Maven
• javax.json-1.0.4.jar
• fontbox-2.0.24.jar 
• pdfbox-2.0.24.jar
• commons-logging-1.2.jar
• gson-2.8.9.jar

Click Finish to create the Java project.

Next, we need to add the namespaces in the Main.java file:

import java.util.*;
import com.grapecity.documents.excel.drawing.*;
import com.grapecity.documents.excel.*;

Lastly, create the Java Excel Workbook instance by initializing the Workbook class.

// Initialize a Java Excel Workbook
Workbook workbook = new Workbook(); 

The Java Excel API library allows developers to easily add data and visualize it using tables, charts, and conditional formatting. The next few steps will show how easy it is to programmatically modify the Java Excel API workbook.

Create Excel Tables Using the ITable Interface

Initialize Table Data

To initialize data in a Java Excel API worksheet, create a two-dimensional array and assign it to a cell range using the setValue method of the IRange interface.

Object[][] sourceData = new Object[][]{
        {"ITEM", "AMOUNT"},
        {"Income 1", 2500},
        {"Income 2", 1000},
        {"Income 3", 250},
        {"Other", 250},
};
Object[][] sourceData1 = new Object[][]{
        {"ITEM", "AMOUNT"},
        {"Rent/mortgage", 800},
        {"Electricity", 120},
        {"Gas", 50},
        {"Cell phone", 45},
        {"Groceries", 500},
        {"Car payment", 273},
        {"Auto expenses", 120},
        {"Student loans", 50},
        {"Credit cards", 100},
        {"Auto insurance", 78},
        {"Personal care", 50},
        {"Entertainment", 100},
        {"Miscellaneous", 50},
};
// Get the first worksheet
IWorksheet worksheet = workbook.getWorksheets().get(0);
// Set the data sources to the Java worksheet
worksheet.getRange("B3:C7").setValue(sourceData);
worksheet.getRange("B10:C23").setValue(sourceData1);
worksheet.setName("Tables");

Apply titles to the tables using the setValue method.

worksheet.getRange("B2:C2").merge();
worksheet.getRange("B2").setValue("MONTHLY INCOME");
worksheet.getRange("B9:C9").merge();
worksheet.getRange("B9").setValue("MONTHLY EXPENSES");
worksheet.getRange("E2:G2").merge();
worksheet.getRange("E2").setValue("PERCENTAGE OF INCOME SPENT");
worksheet.getRange("E5:G5").merge();
worksheet.getRange("E5").setValue("SUMMARY");
worksheet.getRange("E3:F3").merge();
worksheet.getRange("E9").setValue("BALANCE");
worksheet.getRange("E6").setValue("Total Monthly Income");
worksheet.getRange("E7").setValue("Total Monthly Expenses");

Add the Excel Tables

Use the add method of the ITable interface to create two tables labeled Income and Expenses. Next, apply a built-in table style to each table using the setTableStyle method, and finally, set custom names for the tables as TotalMonthlyIncome and TotalMonthlyExpenses using the setName method.

// Create the first table to show Income
ITable incomeTable = worksheet.getTables().add(worksheet.getRange("B3:C7"), true);
incomeTable.setName("tblIncome");
incomeTable.setTableStyle(workbook.getTableStyles().get("TableStyleMedium4"));

// Create the second table to show Expenses
ITable expensesTable = worksheet.getTables().add(worksheet.getRange("B10:C23"), true);
expensesTable.setName("tblExpenses");
expensesTable.setTableStyle(workbook.getTableStyles().get("TableStyleMedium4"));

Apply Table Formulas

Use the worksheet.getNames().add method in the DsExcel Java API to create two named ranges for table formulas. For this example, define TotalMonthlyIncome and TotalMonthlyExpenses as the sum of the "AMOUNT" columns in your tables. These named ranges make it easy to apply formulas across your worksheet, like calculating total expenses or the percentage of income spent.

// Create named ranges
worksheet.getNames().add("TotalMonthlyIncome", "=SUM(tblIncome[AMOUNT])");
worksheet.getNames().add("TotalMonthlyExpenses", "=SUM(tblExpenses[AMOUNT])");
// Apply formulas
worksheet.getRange("E3").setFormula("=TotalMonthlyExpenses");
worksheet.getRange("G3").setFormula("=TotalMonthlyExpenses/TotalMonthlyIncome");
worksheet.getRange("G6").setFormula("=TotalMonthlyIncome");
worksheet.getRange("G7").setFormula("=TotalMonthlyExpenses");
worksheet.getRange("G9").setFormula("=TotalMonthlyIncome-TotalMonthlyExpenses");

Set the Table’s Row Height & Column Width

Set the standard height for all rows and the standard width for all columns in a worksheet using the setStandardHeight and setStandardWidth methods.

worksheet.setStandardHeight(26.25);
worksheet.setStandardWidth(8.43);

To customize individual row heights and column widths, use the setRowHeight and setColumnWidth methods from the IRange interface.

worksheet.getRange("2:24").setRowHeight(27);
worksheet.getRange("A:A").setColumnWidth(2.855);
worksheet.getRange("B:B").setColumnWidth(33.285);
worksheet.getRange("C:C").setColumnWidth(25.57);
worksheet.getRange("D:D").setColumnWidth(1);
worksheet.getRange("E:F").setColumnWidth(25.57);
worksheet.getRange("G:G").setColumnWidth(14.285);

With these adjustments in place, your Excel tables are now ready for viewing. Run the app to see the customized tables with the applied styles and formatting directly in your generated worksheet.

Apply Table Styles & Borders

The DsExcel Java API supports customizing table styles and borders to enhance the appearance of your Excel worksheet.

Retrieve the built-in "Currency" style, align horizontally to the left and vertically to the bottom, and set the number format to $#,##0.00.

IStyle currencyStyle = workbook.getStyles().get("Currency"); 
currencyStyle.setIncludeAlignment(true); 
currencyStyle.setHorizontalAlignment(HorizontalAlignment.Left); 
currencyStyle.setVerticalAlignment(VerticalAlignment.Bottom); 
currencyStyle.setNumberFormat("$#,##0.00");

Similarly, center the "Heading 1" style. Additionally, make the header style bold, set the font to Century Gothic and font size to 11, set the text color to white, apply a custom background with the setIncludePatterns method, and optionally exclude borders.

IStyle heading1Style = workbook.getStyles().get("Heading 1"); 
heading1Style.setIncludeAlignment(true);
heading1Style.setHorizontalAlignment(HorizontalAlignment.Center); 
heading1Style.setVerticalAlignment(VerticalAlignment.Center); 
heading1Style.setIncludeFont(true); 
heading1Style.getFont().setName("Century Gothic"); 
heading1Style.getFont().setBold(true); 
heading1Style.getFont().setSize(11); 
heading1Style.getFont().setColor(Color.GetWhite()); 
heading1Style.setIncludeBorder(false); 
heading1Style.setIncludePatterns(true); 
heading1Style.getInterior().setColor(Color.FromArgb(255, 32, 61, 64));

Update the "Percent" style to center the text both horizontally and vertically using Century Gothic bold font in a custom color.

IStyle percentStyle = workbook.getStyles().get("Percent"); 
percentStyle.setIncludeAlignment(true); 
percentStyle.setHorizontalAlignment(HorizontalAlignment.Center); 
percentStyle.setIncludeFont(true); 
percentStyle.getFont().setColor(Color.FromArgb(255, 32, 61, 64)); 
percentStyle.getFont().setName("Century Gothic"); 
percentStyle.getFont().setBold(true); 
percentStyle.getFont().setSize(14);

After customizing the styles, apply them to specific cell ranges using the setStyle method.

worksheet.getRange("C4:C7, C11:C23, G6:G7, G9").setStyle(currencyStyle);
worksheet.getRange("B2, B9, E2, E5").setStyle(heading1Style);
worksheet.getRange("G3").setStyle(percentStyle);

You can also customize borders for specific ranges. For example, set medium borders with a custom color for specific cells.

worksheet.getRange("E6:G6").getBorders().get(BordersIndex.EdgeBottom).setLineStyle(BorderLineStyle.Medium); 
worksheet.getRange("E6:G6").getBorders().get(BordersIndex.EdgeBottom).setColor(Color.FromArgb(255,32, 61, 64)); 
worksheet.getRange("E7:G7").getBorders().get(BordersIndex.EdgeBottom).setLineStyle(BorderLineStyle.Medium); 
worksheet.getRange("E7:G7").getBorders().get(BordersIndex.EdgeBottom).setColor(Color.FromArgb(255,32,61,64)); 
worksheet.getRange("E9:G9").getInterior().setColor(Color.FromArgb(255,32,61,64)); 
worksheet.getRange("E9:G9").setHorizontalAlignment(HorizontalAlignment.Left); 
worksheet.getRange("E9:G9").setVerticalAlignment(VerticalAlignment.Center); 

By applying these customized styles and borders, you can make your Excel tables visually appealing and consistent, enhancing the overall presentation of your data.

Add Conditional Formatting

The DsExcel Java API supports all types of conditional formatting rules. This example creates a data bar rule to show the percentage of income spent without showing a value.

IDataBar dataBar = worksheet.getRange("E3").getFormatConditions().addDatabar();
dataBar.getMinPoint().setType(ConditionValueTypes.Number);
dataBar.getMinPoint().setValue(1);
dataBar.getMaxPoint().setType(ConditionValueTypes.Number);
dataBar.getMaxPoint().setValue("=TotalMonthlyIncome");
dataBar.setBarFillType(DataBarFillType.Gradient);
dataBar.getBarColor().setColor(Color.GetRed());
dataBar.setShowValue(false);

Save the Java Excel File

Save the Java workbook instance using the save method included with the Excel API’s Workbook class.

//save to an excel file
workbook.save("EventBudget.xlsx");

Download a Trial of Document Solutions for Excel, Java Edition, Today!

Java Excel API

This article only scratches the surface of the full capabilities of Document Solutions for Excel, Java Edition. Review our documentation to see some of the many available features, or try our online demo explorer to see the features in action and interact with the sample code. Integrating a Java spreadsheet API in your application allows developers to import/export, create reports and templates, and deploy spreadsheets at scale across Java applications. Check out our release page to learn more about Document Solutions for Excel and the new features added in the latest release.