Programmatically Create Excel XLSX in Java on Windows, Mac, Linux

March 15 2022

Document Solutions for Excel, Java Edition (DsExcel, previously GcExcel) is a high-speed, feature-rich Excel document API based on VSTO. DsExcel Java includes all the features supported in our Document Solutions for Excel .NET edition, allowing developers to fulfill complex business use cases and work with Excel spreadsheets in Java applications.

In another article, we discussed DsExcel Java and its features. In this tutorial, you'll learn how to generate an Excel Java spreadsheet. We'll show you how to add features to the spreadsheet-like tables, styles, conditional formatting, and charts.

In this tutorial, we'll use Eclipse as the Java IDE to work with DsExcel Java. The steps of adding the package and working with DsExcel Java are the same on all three operating systems - Windows, MAC, and Linux.

Step 1. Installation

Prerequisites

Install Java 6 SDK or higher versions for Windows/Linux/Mac
Install Java IDE of your choice

Installing DsExcel Package on Eclipse IDE

Download the DsExcel java package (gcexcel-*.0.0.jar) from Maven Central repository or download it locally on your machine from the MESCIUS website.

For Eclipse IDE (or IntelliJ)

Open the Eclipse IDE
Create a new Java project
In the Project name field, enter the name of your project and click Next
In Java settings, under Libraries tab, click Add External JARs.

install

Select the gcexcel-*.0.0.jar to add it to your project
Click Finish
The jar file will be added under the Referenced Libraries in your project

The following dependencies are also required, apart from gcexcel.jar:
- javax.json-1.0.4.jar
- javax.json-api-1.1.4.jar
- fontbox-2.0.19.jar
- pdfbox-2.0.19.jar
- commons-logging-1.1.2.jar
- barcode4j.jar

To know more about the above dependencies, refer DsExcel Dependencies.

For Gradle project:

Open the build.gradle and append the below script in the dependencies block compile ("com.grapecity.documents:gcexcel:*.0.0.jar")

For Maven project:

Open the pom.xml and add below xml element in the dependencies node.

<dependency>  
   <groupId>com.grapecity.documents</groupId>  
   <artifactId>gcexcel</artifactId>  
   <version>*.0.0-snapshot</version>  
</dependency>

The jar file will be added as a library in the project and your project can now reference all classes of DsExcel in the jar file.

Step 2. Set Up Your Project

Add Namespace

In Main.java, import the following namespaces:

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

Create a New Workbook

In the main function, add the following code to create a new DsExcel workbook:

Workbook workbook = new Workbook();

Initialize Data

Prepare data for the worksheet and arrange it in a 2-D array. Then set this value to a given range of the worksheet using the setValue method of the worksheet. Also, add labels and calculations for each range of data.

worksheet.getRange("B3:C7").setValue(new Object[][]{  
    {"ITEM", "AMOUNT"},  
    {"Income 1", 2500},  
    {"Income 2", 1000},  
    {"Income 3", 250},  
    {"Other", 250},  
});  
worksheet.getRange("B10:C23").setValue(new Object[][]{  
    {"ITEM", "AMOUNT"},  
    {"Rent/mortgage", 800},  
    {"Electric", 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},  
});

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

NOTE: You can get the data from any external data source, like an SQL Server or any back-end database, CRM system, barcode scanner, lab equipment, or whatever you use.

If you want to use data from a data source, you can connect to any data source, add a DataSet, and then use code like the one above to make a 2-D array of type object. You can set the size of the range to fit the data and fill it in with the values from the dataset.

Step 3. Set Row Height and Column Widths

To set the uniform height of rows and widths of columns, set a default row height and column width for the whole worksheet using IWorksheet.setStandardHeight and IWorksheet.setStandardWidth for all the row and column ranges.

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

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

Step 4: Create Table

Add two tables: "Income" and "Expenses," and apply a built-in table style to each.

ITable incomeTable = worksheet.getTables().add(worksheet.getRange("B3:C7"), true);  
incomeTable.setName("tblIncome");  
incomeTable.setTableStyle(workbook.getTableStyles().get("TableStyleMedium4"));

ITable expensesTable = worksheet.getTables().add(worksheet.getRange("B10:C23"), true);  
expensesTable.setName("tblExpenses");  
expensesTable.setTableStyle(workbook.getTableStyles().get("TableStyleMedium4"));

Step 5. Set Formulas for the Tables

Create two custom names to summarize the income and expenses for the month, then add formulas that calculate the total monthly income, total monthly expenses, percentage of income spent, and balance.

worksheet.getNames().add("TotalMonthlyIncome", "=SUM(tblIncome[AMOUNT])");  
worksheet.getNames().add("TotalMonthlyExpenses", "=SUM(tblExpenses[AMOUNT])");

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

Step 6. Set Styles

Modify the "Currency," "Heading 1," and "Percent" built-in styles, and apply them to ranges of cells. Modify individual style elements for other ranges.

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

IStyle heading1Style = workbook.getStyles().get("Heading 1");  
heading1Style.setIncludeAlignment(true);  
heading1Style.setHorizontalAlignment(HorizontalAlignment.Center);  
heading1Style.setVerticalAlignment(VerticalAlignment.Center);  
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(32, 61, 64));

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

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

worksheet.getRange("E6:G6").getBorders().get(BordersIndex.EdgeBottom).setLineStyle(BorderLineStyle.Medium);  
worksheet.getRange("E6:G6").getBorders().get(BordersIndex.EdgeBottom).setColor(Color.FromArgb(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(32, 61, 64));

worksheet.getRange("E9:G9").getInterior().setColor(Color.FromArgb(32, 61, 64));  
worksheet.getRange("E9:F9").setHorizontalAlignment(HorizontalAlignment.Left);  
worksheet.getRange("E9:G9").setVerticalAlignment(VerticalAlignment.Center);  
worksheet.getRange("E9:G9").getFont().setName("Century Gothic");  
worksheet.getRange("E9:G9").getFont().setBold(true);  
worksheet.getRange("E9:G9").getFont().setSize(11);  
worksheet.getRange("E9:G9").getFont().setColor(Color.GetWhite());  
worksheet.getRange("E3:F3").getBorders().setColor(Color.FromArgb(32, 61, 64));

Step 7. Add Conditional Formatting

You can apply conditional formatting on a range depending on the condition to be applied. This example will show the percentage of income spent through a data bar.

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

With all of the individual style elements and conditional formatting applied, your spreadsheet now looks like this:

style

Step 8. Add Charts

Create a column chart to illustrate the gap between income and expenses. To polish the layout, change the series overlap and gap width, then customize the formatting of some of the chart elements: chart area, axis line, tick labels, and data points.

IShape shape = worksheet.getShapes().addChart(ChartType.ColumnClustered, 339, 247, 316.5, 346);  
shape.getChart().getChartArea().getFormat().getLine().setTransparency(1);  
shape.getChart().getColumnGroups().get(0).setOverlap(0);  
shape.getChart().getColumnGroups().get(0).setGapWidth(37);

IAxis category_axis = shape.getChart().getAxes().item(AxisType.Category);  
category_axis.getFormat().getLine().getColor().setRGB(Color.GetBlack());  
category_axis.getTickLabels().getFont().setSize(11);  
category_axis.getTickLabels().getFont().getColor().setRGB(Color.GetBlack());

IAxis series_axis = shape.getChart().getAxes().item(AxisType.Value);  
series_axis.getFormat().getLine().setWeight(1);  
series_axis.getFormat().getLine().getColor().setRGB(Color.GetBlack());  
series_axis.getTickLabels().setNumberFormat("$###0");  
series_axis.getTickLabels().getFont().setSize(11);  
series_axis.getTickLabels().getFont().getColor().setRGB(Color.GetBlack());

ISeries chartSeries = shape.getChart().getSeriesCollection().newSeries();  
chartSeries.setFormula("=SERIES(\"Simple Budget\",{\"Income\",\"Expenses\"},'Sheet1'!$G$6:$G$7,1)");  
chartSeries.getPoints().get(0).getFormat().getFill().getColor().setRGB(Color.FromArgb(176, 21, 19));  
chartSeries.getPoints().get(1).getFormat().getFill().getColor().setRGB(Color.FromArgb(234, 99, 18));  
chartSeries.getDataLabels().getFont().setSize(11);  
chartSeries.getDataLabels().getFont().getColor().setRGB(Color.GetBlack());  
chartSeries.getDataLabels().setShowValue(true);  
chartSeries.getDataLabels().setPosition(DataLabelPosition.OutsideEnd);

Step 9. Save it to XLSX

And finally: the payoff. Export it all to an Excel file so you can see what you've built. Be sure that this line of code comes AFTER all of your other code, so it processes before it's saved.