Generating Excel documents on Java on Windows, macOS and Linux

GrapeCity Documents for Excel, Java Edition (GcExcel Java) is a high-speed, feature-rich Excel document API based on VSTO. GcExcel Java includes all of the features that are supported in our Documents for Excel .NET version, allowing developers to fulfil complex business use cases and work with Excel spreadsheets in Java applications.

In another article, we discussed GcExcel 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, pivot table, styles, conditional formatting, and charts.

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

Step 1. Installation

Prerequisites

1. Install Java 6 SDK or higher versions for Windows/Linux/Mac.
2. Install Java IDE of your choice. This article will focus on using GcExcel with IntelliJ.

Installing GcExcel Package on IntelliJ

1. Download GcExcel package - gcexcel-2.0.0.jar from website.
2. Open IntelliJ IDE.
3. Create a new Java project.
4. Under 'src' folder, add a folder 'libs'.
5. Copy gcexcel-2.0.0.jar to 'libs' folder.
6. Right-click 'libs' folder and choose 'Add as library' option.
7. Press OK.

For Gradle project:

Open the build.gradle and append the below script in the dependencies block compile("com.grapecity.documents:gcexcel:2.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>2.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 GcExcel in the jar file.

Step 2. Set up your project

Add namespace

In Main.java, import 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 GcExcel 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 worksheet. Also, add labels and calculations for each range of data.

 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},
        };

IWorksheet worksheet = workbook.getWorksheets().get(0);
worksheet.setName("Table");
worksheet.getRange("B3:C7").setValue(sourceData);
worksheet.getRange("B10:C23").setValue(sourceData1);

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 similar to the 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. Create Tables

Add a table on the range of data you added above and set its style.

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

Step 4. Set formulas for the tables

Each table will calculate the summary income and expenses. Set a name for each table's summary for the month:

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

Now, we’ll use these names and set formulas to calculate the total monthly income, total monthly expenses, percentage of income spent, and balance.

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

If you save the workbook at this point (see step 10 for the code) and run your project, the following Excel file will be created.

Step 5. Set row heights and column widths

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

Save the workbook and run the project. You'll notice that all of the rows and columns have a definite height and width, respectively.

The next step helps you set styles to format your Excel spreadsheet into a professional-looking spreadsheet.

Step 6. Set styles

You can set named styles over a range of cells. These styles can be modified using desired properties. You can even create a custom style of your own.

IStyle currencyStyle = workbook.getStyles().get("Currency");
currencyStyle.setIncludeAlignment(true);
currencyStyle.setHorizontalAlignment(HorizontalAlignment.Left);
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.FromRGB(32, 61, 64));

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

Apply your customized styles to specific ranges. Also, turn off the grid lines for the worksheet.

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

Your spreadsheet now looks like this:

With GcExcel, it is also possible to set individual style elements on specific ranges.

worksheet.getRange("E6:G6").getBorders().get(BordersIndex.EdgeBottom).setLineStyle(BorderLineStyle.Medium);
worksheet.getRange("E6:G6").getBorders().get(BordersIndex.EdgeBottom).setColor(Color.FromRGB(32, 61, 64));
worksheet.getRange("E7:G7").getBorders().get(BordersIndex.EdgeBottom).setLineStyle(BorderLineStyle.Medium);
worksheet.getRange("E7:G7").getBorders().get(BordersIndex.EdgeBottom).setColor(Color.FromRGB(32, 61, 64));

worksheet.getRange("E9:G9").getInterior().setColor(Color.FromRGB(32, 61, 64));
worksheet.getRange("E9:G9").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.FromRGB(32, 61, 64));

Step 7. Add conditional formatting

You can apply conditional formatting on a range depending on the condition to be applied. In this example, you 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:

Step 8. Create a pivot table

Start with adding a new worksheet and setting it’s name.

//add a new worksheet and create a pivot table in it
IWorksheet worksheet2 = workbook.getWorksheets().add();
worksheet2.setName("Pivot Table");

Prepare the data for the Pivot Table.

sourceData = new Object[][]{
                {"Order ID", "Product", "Category", "Amount", "Date", "Country"},
                {1, "Carrots", "Vegetables", 4270, new GregorianCalendar(2018, 0, 6), "United States"},
                {2, "Broccoli", "Vegetables", 8239, new GregorianCalendar(2018, 0, 7), "United Kingdom"},
                {3, "Banana", "Fruit", 617, new GregorianCalendar(2018, 0, 8), "United States"},
                {4, "Banana", "Fruit", 8384, new GregorianCalendar(2018, 0, 10), "Canada"},
                {5, "Beans", "Vegetables", 2626, new GregorianCalendar(2018, 0, 10), "Germany"},
                {6, "Orange", "Fruit", 3610, new GregorianCalendar(2018, 0, 11), "United States"},
                {7, "Broccoli", "Vegetables", 9062, new GregorianCalendar(2018, 0, 11), "Australia"},
                {8, "Banana", "Fruit", 6906, new GregorianCalendar(2018, 0, 16), "New Zealand"},
                {9, "Apple", "Fruit", 2417, new GregorianCalendar(2018, 0, 16), "France"},
                {10, "Apple", "Fruit", 7431, new GregorianCalendar(2018, 0, 16), "Canada"},
                {11, "Banana", "Fruit", 8250, new GregorianCalendar(2018, 0, 16), "Germany"},
                {12, "Broccoli", "Vegetables", 7012, new GregorianCalendar(2018, 0, 18), "United States"},
                {13, "Carrots", "Vegetables", 1903, new GregorianCalendar(2018, 0, 20), "Germany"},
                {14, "Broccoli", "Vegetables", 2824, new GregorianCalendar(2018, 0, 22), "Canada"},
                {15, "Apple", "Fruit", 6946, new GregorianCalendar(2018, 0, 24), "France"},
        };

Note: To use Gregorian Calendar in Java, following namespace should be included in the project:

import java.util.*;

Set the data to the respective range and setup the pivot cache and pivot table.

worksheet2.getRange("A1:F16").setValue(sourceData);
worksheet2.getRange("A:F").setColumnWidth(15);
IPivotCache pivotcache = workbook.getPivotCaches().create(worksheet2.getRange("A1:F16"));
IPivotTable pivottable = worksheet2.getPivotTables().add(pivotcache, worksheet2.getRange("H7"), "pivottable1"); 

If you stop right here, you can see that you get the data, and a DIY pivot table.

We can do better than that! Add this code to add pivot table fields and set the orientation for each:

//config pivot table's fields
IPivotField field_Category = pivottable.getPivotFields().get("Category");
field_Category.setOrientation(PivotFieldOrientation.RowField);

IPivotField field_Product = pivottable.getPivotFields().get("Product");
field_Product.setOrientation(PivotFieldOrientation.ColumnField);

IPivotField field_Amount = pivottable.getPivotFields().get("Amount");
field_Amount.setOrientation(PivotFieldOrientation.DataField);

IPivotField field_Country = pivottable.getPivotFields().get("Country");
field_Country.setOrientation(PivotFieldOrientation.PageField);

Now we've finished a complete pivot table.

To learn more about pivot tables, see our Pivot Table in the documentation, and the Pivot Table section of the online demo.

Step 9. Add a chart

Before adding the chart, add a new sheet for the chart.

IWorksheet worksheet3 = workbook.getWorksheets().add();
worksheet3.setName("Chart");

A chart in Documents for Excel is essentially a shape object. To set up the type of chart you want to create, first create a shape, then add a chart to it. The AddChart parameters let you specify the ChartType, and the location and size. Specify the location in points for the left and top positions, and the size with width, and height.

IShape shape = worksheet3.getShapes().addChart(ChartType.ColumnClustered, 300, 10, 300, 300);

Now you can set the chart title, add data, and create the chart.

//Set chart title
shape.getChart().getChartTitle().setText("Sales Increases Over Previous Quarter");
worksheet3.getRange("A1:D6").setValue(new Object[][]{
                {null, "S1", "S2", "S3"},
                {"Item1", 10, 25, 25},
                {"Item2", -51, -36, 27},
                {"Item3", 52, -85, -30},
                {"Item4", 22, 65, 65},
                {"Item5", 23, 69, 69}
    });
//create chart.
shape.getChart().getSeriesCollection().add(worksheet3.getRange("A1:D6"), RowCol.Columns, true, true);

Here's what we have at this point:

To make the data table look nicer and format the numbers, you can apply formatting to specific ranges. Let's format the numbers on the chart's value axis.

//Format data table
worksheet3.getRange("B1:D1").setHorizontalAlignment(HorizontalAlignment.Right);
worksheet3.getRange("B1:D1").getFont().setBold(true);
worksheet3.getRange("B2:D6").setNumberFormat("€#,##0");

//Format axis numbers
IAxis value_axis = shape.getChart().getAxes().item(AxisType.Value);
value_axis.getTickLabels().setNumberFormat("€#,##0");

And here it is, a nicely formatted chart in our workbook with three sheets:

To learn more about charts, see our documentation, and the Charts section of the online demo.

Step 10. 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.

workbook.save("./GcExcelFeatures.xlsx");

That's how you can create a simple spreadsheet in Java with Documents for Excel.

Download the code

• The code for adding tables and charts
• The code for adding Pivot tables

If you have a question about this tutorial, leave it for us in the comment thread below!

IStyle currencyStyle = workbook.getStyles().get("Currency");
currencyStyle.setIncludeAlignment(true);
currencyStyle.setHorizontalAlignment(HorizontalAlignment.Left);
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.FromRGB(32, 61, 64));

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