How to Create, Modify, and Save Excel XLSX Files in Java Apps

Download a finished sample application to follow along with this blog.

Create a New Java Excel API Application

For this demo, we will use Document Solutions for Excel, Java Edition (DsExcel Java) to create a Java Excel workbook in a Java application. Please see the DsExcel getting started blog, How to Add an Excel XLSX API Library to Your Java App, for steps on how to begin creating a new Java application utilizing the Java Excel API library.

After following the steps to setup your application environment, your Java application will include the Java Excel API library and its required dependencies. Additionally, your Main.java file will have the necessary library namespaces imported.

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

Programmatically Generate a New Excel File with the Java Excel API

To begin, we'll first want to programmatically generate a new Excel workbook by initializing a new workbook through the Java Excel API library’s Workbook class:

Workbook workbook = new Workbook();

Adding Data to the Excel Workbook

Next, we'll want to add some data to the Excel workbook by adding some two-dimensional arrays:

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

With data added to our workbook, we can begin setting variable names to our data ranges and to our worksheet, like so:

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

Now we have a basic Excel sheet that looks like so:

Basic Cell Formatting in Java for Excel

With the data in place, we can programmatically add Excel cell formatting in Java by merging cells, changing the cell values, and setting the height and width of each cell within our worksheet. We programmatically set these cell values through the IRange class’s getRange setValue method, and merge them through the merge 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");

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

Now we have a basically formatted Excel sheet to continue manipulating further to better showcase our data:

Adding Tables in Java for Excel

To begin applying some advanced data transformations, we'll first want to create some tables to hold the data we've added to our worksheet through the ITable interface. We can also utilize the setTableStyle property to alter the table appearance, like so:

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

Next, we'll want to apply some formulaic logic on the cells related to these tables, as well as some basic formatting for the columns associated with these tables as well. This will be accomplished through the worksheet’s getRange class’s methods setFormula, setRowHeight, and setColumnWidth:

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

Now we can see the tables added to the Excel sheet that includes the data we added:

Programmatically Setting the Styling for Currency, Headings, and Percentages

With the DsExcel Java API library, developers can programmatically set the style formatting for currencies, headings, and percentage formats contained within their worksheet dataset through the IStyle interface. This can be done at the cell level or applied to the entire workbook if desired. Multiple styles can be generated for each format, and the developer can decide which format to apply where within the workbook. The getFont method has different properties to set, such as bolding, font name, font size, font color, and more. In this example, we'll generate one style for each format, set the relevant properties for each style, and then apply those styles throughout specific relevant ranges within our worksheet:

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

Now we can see the different styling applied to the headings, currencies, and percent's included within the data set:

Programmatically Setting Cell Borders and Cell Colors in Excel Java

Developers also have the ability to programmatically set cell borders and cell color properties through the Java Excel API library. The setColor and setLineStyle methods allows developers to choose specific color values by setting RGB values, or you can choose from preset values for both colors and line border styles. In the example below, we set the color and border style of some cell ranges to make the borders clearly visible.

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

Now we can see the borders within the Excel sheet with the applied colors and line widths:

Programmatically Generate a Data Bar in Excel Worksheet with Java

With Document Solutions for Excel, Java Edition (DsExcel Java), creating advanced visualizations in spreadsheets is effortless and fully customizable. For example, you can programmatically generate a data bar through the IDataBar interface in an Excel worksheet using just a few lines of Java code. By setting custom minimum and maximum values, defining gradient fills, and choosing bar colors, developers can deliver clear, dynamic insights right inside Excel files, without relying on manual formatting. By controlling the minimum and maximum values through the getMinPoint and getMaxPoint methods, we can control the scope of the data bar. We can also control the color and fill type of the data bar through the getBarColor and setBarFillType methods, respectively. With the code below, we can create a data bar that highlights the percent of expenses taken out of our total income each month:

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

Here we can see our generated data bar within the worksheet:

Programmatically Add a Pivot Table to Excel Worksheet in Java

Document Solutions for Excel also allows you to seamlessly add and connect a pivot table to your data within your application. In the code below, we create a new worksheet within the workbook, and create a new data table with values that we’ll be using to initialize a pivot table on the new worksheet. This dataset is inserted into the worksheet’s cell range A1:F16, with columns auto-sized for readability. Next, a pivot cache through the IPivotCache interface is created from the data range, which is then used to insert a new pivot table from the IPivotTable interface starting at cell H7. The pivot table’s IPivotFields are configured so that Category appears as row labels, Product appears as column labels, and Amount is placed in the data field, formatted as currency for clarity. Additionally, Country is added as a page (filter) field, allowing the user to filter pivot table results by country. The result is a dynamic, interactive pivot table that summarizes sales amounts by product and category, with optional country filtering.

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

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

With this, we’ve created a new worksheet with a pivot table that can be accessed and modified within Excel:

Programmatically Add a Chart to Excel Worksheet in Java

DsExcel offers many other visualization options beyond data bars, including charts and graphs. In the code below, we create another new worksheet within the workbook where we’ll be adding a clustered column chart through the IShape interface that tracks quarterly sales growth across multiple countries. The data is populated directly into a worksheet, formatted with bold headers and Euro currency for clarity, and then instantly visualized in a professional, interactive chart. Developers can fully customize chart titles, layouts, and axis formatting via the IAxis interface, all programmatically, without manual intervention in Excel. Whether you’re building business dashboards, generating client-ready reports, or automating financial analysis, DsExcel Java empowers you to deliver polished, data-rich visualizations with just a few lines of code.

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

Now we have a chart on a new worksheet that looks like this:

Save Your Excel Workbook in Java

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

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

Just like that, you've programmatically transformed the formatting of the Excel XLSX document, and generated a new, fully polished Excel document from scratch. Run the Java application, and your Excel workbook with multiple spreadsheets will now appear as shown below:

Java Excel API

This article highlights just a fraction of what Document Solutions for Excel, Java Edition can do. Explore our documentation to discover the wide range of available features, or try our interactive online demo explorer to see them in action and experiment with sample code. By integrating a Java spreadsheet API into your application, developers gain the ability to import and export data, generate reports and templates, and seamlessly deploy spreadsheets at scale across Java environments. Visit our release page to learn more about Document Solutions for Excel and the latest features included in the newest version.

Download a Trial of Documents for Excel, Java Edition, Today!