How to Add GcExcel, an Excel XLSX API Library, to Your Java App
Tags: webdev, devops, javascript, tutorial
Published: 2023-06-21 13:28:14

---
canonical_url: https://developer.mescius.com/blogs/how-to-add-gcexcel-an-excel-xlsx-api-library-to-your-java-app
description: Learn how to add GcExcel, an Excel XLSX API Library, to your Java application. See more from GrapeCity Documents today.
---
This tutorial will create a real-life scenario with [GrapeCity Documents for Excel - Java (**GcExcel Java**)](https://developer.mescius.com/document-solutions/java-excel-api "documents-api-excel-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 **GcExcel Java**, with no dependency on Microsoft Excel:

1.  [Prepare the Project](#prepare)
2.  [Add Namespaces and Create the Workbook](#add-name)
3.  [Initialize Data and Create Tables](#initialize)
4.  [Create Tables, Apply Formulas, and Set Row Heights & Column Widths](#create)
5.  [Apply Styles and Borders](#apply)
6.  [Add Conditional Formatting](#add-format)
7.  [Add a PivotTable](#add-table)
8.  [Add a Chart](#add-chart)
9.  [Save Result to .XLSX File](#save)

[![How to Add GrapeCity Documents for Excel, an Excel XLSX API Library, to Your Java App](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4lkv5t7q9szrpkhwmvpj.png)](https://youtu.be/e4u7eJeI0Dk)

## <a id="prepare"></a>Prepare the Project

*   Ensure that you have installed JDK 8 or higher version.
*   Create a Java console application with any Java IDE you prefer, such as [**IntelliJ IDEA**](https://www.jetbrains.com/idea/) or [**Eclipse**](https://www.eclipse.org/downloads/packages/release/kepler/sr1/eclipse-ide-java-developers).
*   **IntelliJ or Eclipse Console Application:**

*   Download the **GcExcel** jar package from [Maven](https://search.maven.org/artifact/com.grapecity.documents/gcexcel/) or [Github](https://github.com/GrapeCity/GcExcel-Java).
*   Copy gcexcel-x.x.x.jar into the project library folder and add it as a dependency library.

*   **Gradle Project:**

*   Open the build.gradle and append the script below in the dependencies block, where “x.x.x” is replaced with the actual version of the **GcExcel Java** jar package:

```
    compile("com.grapecity.documents:gcexcel:x.x.x")
```

*   **Maven Project:**

*   Open the pom.xml and add the XML element below in the dependencies node, where “x.x.x” is replaced with the actual version of the **GcExcel Java** jar package:

```
    <dependency>
       <groupId>com.grapecity.documents</groupId>
       <artifactId>gcexcel</artifactId>
       <version>x.x.x</version>
    </dependency>
```

This sample does not require any external packages as dependencies, as it does not use any of the features which require using such external packages. For a full listing of the specific features and associated external package dependencies required when using those features, please see this help topic: [**GcExcel Dependencies**](https://developer.mescius.com/document-solutions/java-excel-api/docs/online/gcexceldependencies.html).

## <a id="add-name"></a>Add Namespaces and Create the Workbook

*   Open **main.java** and add the following namespaces at the top:

**Java**

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

**Kotlin**

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

*   Add the following code in the **Main** function to create the **Workbook** object:

**Java**

```
    Workbook workbook = new Workbook();
```

**Kotlin**

```
    var workbook = new Workbook()
```

## <a id="initialize"></a>Initialize Data and Create Tables

*   To initialize data in **GcExcel**, prepare a two-dimensional array and assign it to the [**Value** of a worksheet **Range**](https://developer.mescius.com/document-solutions/java-excel-api/docs/online/Documents.for.Excel.Java.Edition.Packages~com.grapecity.documents.excel.IRange~setValue.html):

**Java**

```
    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.getRange("B3:C7").setValue(sourceData);
    worksheet.getRange("B10:C23").setValue(sourceData1);
    worksheet.setName("Tables");

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

**Kotlin**

```
    var sourceData = arrayOf(
            arrayOf<Any>("ITEM", "AMOUNT"),
            arrayOf<Any>("Income 1", 2500),
            arrayOf<Any>("Income 2", 1000),
            arrayOf<Any>("Income 3", 250),
            arrayOf<Any>("Other", 250)
    )

    var sourceData1 = arrayOf(
            arrayOf<Any>("ITEM", "AMOUNT"),
            arrayOf<Any>("ITEM", "AMOUNT"),
            arrayOf<Any>("ITEM", "AMOUNT"),
            arrayOf<Any>("ITEM", "AMOUNT"),
            arrayOf<Any>("ITEM", "AMOUNT"),
            arrayOf<Any>("ITEM", "AMOUNT"),
            arrayOf<Any>("ITEM", "AMOUNT"),
            arrayOf<Any>("ITEM", "AMOUNT"),
            arrayOf<Any>("ITEM", "AMOUNT"),
            arrayOf<Any>("ITEM", "AMOUNT"),
            arrayOf<Any>("ITEM", "AMOUNT"),
            arrayOf<Any>("ITEM", "AMOUNT"),
            arrayOf<Any>("ITEM", "AMOUNT"),
            arrayOf<Any>("ITEM", "AMOUNT")
    )

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

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

## <a id="create"></a>Create Tables, Apply Formulas, and Set Row Heights and Column Widths

*   Add the following code to [create two tables](https://developer.mescius.com/document-solutions/java-excel-api/docs/online/CreateDeleteTables.html), **Income** and **Expenses**, apply [built-in table styles](https://developer.mescius.com/document-solutions/java-excel-api/docs/online/ApplyTableStyle.html) to each, then set custom names **_TotalMonthlyIncome_** and **_TotalMonthlyExpenses_**, cell formulas, row heights, and column widths:

**Java**

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

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

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

**Kotlin**

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

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

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

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

## <a id="apply"></a>Apply Styles and Borders

*   **GcExcel Java** API can apply changes to range styles directly on each element, use a built-in named style, or use a custom named style, which can copy one or more built-in styles to initialize then set individual style properties to customize the style and re-use the style in multiple ranges to optimize memory. [Built-in named styles can also be customized](https://developer.mescius.com/document-solutions/java-excel-api/docs/online/ModifyTableWithCustomStyle.html) – this example shows how to modify the built-in **_Currency,_** **_Heading 1,_** and **_Percent_** styles. The changes will affect all cells in the workbook using those built-in styles, and the changes will be saved with the workbook in the **.XLSX**:

**Java**

```
    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.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)); 
    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); 
    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(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); 
    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(255,32,61,64));
```

**Kotlin**

```
    val currencyStyle: IStyle = workbook.styles.get("Currency") 
    currencyStyle.includeAlignment = true 
    currencyStyle.horizontalAlignment = HorizontalAlignment.Left 
    currencyStyle.verticalAlignment = VerticalAlignment.Bottom 
    currencyStyle.numberFormat = "$#,##0.00" 
    val heading1Style: IStyle = workbook.styles.get("Heading 1") 
    heading1Style.includeAlignment = true 
    heading1Style.horizontalAlignment = HorizontalAlignment.Center 
    heading1Style.verticalAlignment = VerticalAlignment.Center 
    heading1Style.includeFont = true 
    heading1Style.font.name = "Century Gothic" 
    heading1Style.font.bold = true heading1Style.font.size = 11.0 
    heading1Style.font.color = Color.GetWhite() 
    heading1Style.includeBorder = false 
    heading1Style.includePatterns = true 
    heading1Style.interior.color = Color.FromArgb(255, 32, 61, 64) 
    val percentStyle: IStyle = workbook.styles.get("Percent") 
    percentStyle.includeAlignment = true 
    percentStyle.horizontalAlignment = HorizontalAlignment.Center 
    percentStyle.includeFont = true 
    percentStyle.font.color = Color.FromArgb(255, 32, 61, 64) 
    percentStyle.font.name = "Century Gothic" 
    percentStyle.font.bold = true percentStyle.font.size = 14.0 
    worksheet.getSheetView().setDisplayGridlines(false) 
    worksheet.getRange("C4:C7, C11:C23, G6:G7, G9").style = currencyStyle 
    worksheet.getRange("B2, B9, E2, E5").style = heading1Style 
    worksheet.getRange("G3").style = percentStyle 
    worksheet.getRange("E6:G6").borders.get(BordersIndex.EdgeBottom).lineStyle = BorderLineStyle.Medium 
    worksheet.getRange("E6:G6").borders.get(BordersIndex.EdgeBottom).color = Color.FromArgb(255, 32, 61, 64) 
    worksheet.getRange("E7:G7").borders.get(BordersIndex.EdgeBottom).lineStyle = BorderLineStyle.Medium 
    worksheet.getRange("E7:G7").borders.get(BordersIndex.EdgeBottom).color = Color.FromArgb(255, 32, 61, 64) 
    worksheet.getRange("E9:G9").interior.color = Color.FromArgb(255, 32, 61, 64) 
    worksheet.getRange("E9:G9").horizontalAlignment = HorizontalAlignment.Left 
    worksheet.getRange("E9:G9").verticalAlignment = VerticalAlignment.Center 
    worksheet.getRange("E9:G9").font.name = "Century Gothic" 
    worksheet.getRange("E9:G9").font.bold = true 
    worksheet.getRange("E9:G9").font.size = 11.0 
    worksheet.getRange("E9:G9").font.color = Color.GetWhite() 
    worksheet.getRange("E3:F3").borders.color = Color.FromArgb(255, 32, 61, 64)
```

## <a id="add-format"></a>Add Conditional Formatting

*   **GcExcel Java** API supports all types of [conditional formatting rules](https://developer.mescius.com/document-solutions/java-excel-api/demos/conditionalformatting). This example creates a data bar rule to show the percentage of income spent without showing a value:

**Java**

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

**Kotlin**

```
    val dataBar: IDataBar = 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)
```

## <a id="add-table"></a>Add a PivotTable

*   **GcExcel Java** API supports [pivot tables](https://developer.mescius.com/document-solutions/java-excel-api/demos/pivottable), which enable quick aggregation and subtotals for the analysis of complex data. This example creates a new worksheet and creates a new pivot table referencing data in a range on the worksheet:

**Java**

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

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

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

**Kotlin**

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

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

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

*   To configure the pivot table layout, specify the row field, column field, data field, and page field:

**Java**

```
    // configure pivot table's fields
    IPivotField fieldCategory = pivotTable.getPivotFields().get("Category");
    fieldCategory.setOrientation(PivotFieldOrientation.RowField);

    IPivotField fieldProduct = pivotTable.getPivotFields().get("Product");
    fieldProduct.setOrientation(PivotFieldOrientation.ColumnField);

    IPivotField fieldAmount = pivotTable.getPivotFields().get("Amount");
    fieldAmount.setOrientation(PivotFieldOrientation.DataField);
    fieldAmount.setNumberFormat("$#,##0");

    IPivotField fieldCountry = pivotTable.getPivotFields().get("Country");
    fieldCountry.setOrientation(PivotFieldOrientation.PageField);
```

**Kotlin**

```
    // configure pivot table's fields
    val fieldCategory: IPivotField = pivotTable.getPivotFields().get("Category")
    fieldCategory.setOrientation(PivotFieldOrientation.RowField)

    val fieldProduct: IPivotField = pivotTable.getPivotFields().get("Product")
    fieldProduct.setOrientation(PivotFieldOrientation.ColumnField)

    val fieldAmount: IPivotField = pivotTable.getPivotFields().get("Amount")
    fieldAmount.setOrientation(PivotFieldOrientation.DataField)
    fieldAmount.setNumberFormat("$#,##0")

    val fieldCountry: IPivotField = pivotTable.getPivotFields().get("Country")
    fieldCountry.setOrientation(PivotFieldOrientation.PageField)
```

## <a id="add-chart"></a>Add a Chart

*   GcExcel Java API supports many [types of charts](https://developer.mescius.com/document-solutions/java-excel-api/docs/online/work_with_chart_types.html) for visualizing various kinds of data. This example creates a new worksheet, then creates a new chart referencing data in a range on the worksheet:

**Java**

```
    // add a new sheet and create a chart in it
    IWorksheet worksheet3 = workbook.getWorksheets().add();
    worksheet3.setName("Chart");

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

    shape.getChart().getChartTitle().setText("Sales Increases Over Previous Quarter");

    worksheet3.getRange("A1:D6").setValue(new Object[][]{
            {null, "Q1", "Q2", "Q3"},
            {"Belgium", 10, 25, 25},
            {"France", -51, -36, 27},
            {"Greece", 52, -85, -30},
            {"Italy", 22, 65, 65},
            {"UK", 23, 69, 69},
    });

    shape.getChart().getSeriesCollection().add(worksheet3.getRange("A1:D6"), RowCol.Columns, true, true);

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

    IAxis valueAxis = shape.getChart().getAxes().item(AxisType.Value);
    valueAxis.getTickLabels().setNumberFormat("€#,##0");
```

**Kotlin**

```
    // add a new sheet and create a chart in it
    val worksheet3: IWorksheet = workbook.getWorksheets().add()
    worksheet3.setName("Chart")

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

    shape.getChart().getChartTitle().setText("Sales Increases Over Previous Quarter")

    worksheet3.getRange("A1:D6").setValue(arrayOf<Array<Any>>(
            arrayOf<Any>(null, "Q1", "Q2", "Q3"),
            arrayOf<Any>("Belgium", 10, 25, 25),
            arrayOf<Any>("France", -51, -36, 27),
            arrayOf<Any>("Greece", 52, -85, -30),
            arrayOf<Any>("Italy", 22, 65, 65),
            arrayOf<Any>("UK", 23, 69, 69)
    )

    shape.getChart().getSeriesCollection().add(worksheet3.getRange("A1:D6"), RowCol.Columns, true, true)

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

    val valueAxis: IAxis = shape.getChart().getAxes().item(AxisType.Value)
    valueAxis.getTickLabels().setNumberFormat("€#,##0")
```

## <a id="save"></a>Save the Result to a .XLSX File

*   After all other changes are complete, [save the resulting workbook](https://developer.mescius.com/document-solutions/java-excel-api/docs/online/OpenSaveProtectWorkbook.html#i-heading-save-a-workbook) to a new **.XLSX** file using the **IWorkbook.save** method:

**Java**

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

**Kotlin**

```
    workbook.save("GcExcelFeatures.xlsx")
```

*   When you run the project, the file **GcExcelFeatures.XLSX** will be created in the project folder:

[**GcExcelFeatures.xlsx**](https://gccontent.blob.core.windows.net/gccontent/blogs/gcdocuments/20230531-how-to-add-gcexcel-an-excel-xlsx-api-library-to-your-java-app/GcExcelFeatures-2.xlsx)

To download the sample for this blog, please click [here](https://developer.mescius.com/document-solutions/java-excel-api/demos/tutorial).