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

What You Will Need

• Java 8.0+ application
• Document Solutions for Excel
• Maven: DsExcel

Controls Referenced

• Document Solutions for Excel (DsExcel Java) - Java Excel API Library
• Documentation | Online Demo Explorer

Tutorial Concept

Learn how to create, modify, and save Excel .xlsx files programmatically within Java applications using a Java Excel API.

Creating, modifying, and saving Excel files is a common requirement in many Java applications. Whether you're handling data analysis, reporting, or any task that involves spreadsheet manipulation, using a reliable Java Excel API can streamline your workflow. In this guide, we'll explore how to programmatically work with Excel (XLSX) files in Java using a Java Excel API library.

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

1.Create a Java Excel API Application
2.Import Excel Files with the Java Excel API
3.Accessing the Java Excel Worksheet for Modification

• Modify Font Properties of Table Titles
• Modify Table Style
• Modify Table Column’s Style
• Modify Cell Range Style

4.Save Your Excel Workbook in Java

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

Create a 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, to create a Java Excel API application.

After completing these steps, your Java application will include the Java Excel API library and its dependencies. Additionally, your Main.java file will have the necessary library namespaces imported, and the Java Excel API workbook will be initialized.

    import com.grapecity.documents.excel.*;
    // Initialize a Java Excel Workbook
    Workbook workbook = new Workbook(); 

Import Excel Files with the Java Excel API

The Excel API’s Workbook class includes an open method to read existing Excel files into the Java workbook.

    workbook.open("EventBudget.xlsx");

Accessing the Java Excel Worksheet for Modification

Developers can then programmatically modify the imported Excel file using the API library. These modifications include updating font properties, table style, and range style, modifying the table column styles, and many other options.

Modify Font Properties of Table Titles

We can modify the font properties of a specific range using the IRange’s getFont method. In this example, we will set the font size to 22 using the setSize method and the text to bold using the setBold method.

    worksheet.getRange("B2").getFont().setSize(22);
    worksheet.getRange("E4").getFont().setBold(true);

Modify Cell Range Style

Easily modify the style of a cell range in the Java worksheet using the getRange method along with other helper methods. In this example, we use the getInterior method to adjust the ThemeColor and TintAndShade properties. We also customize the font of the range with the getFont method and apply borders using the getBorders method. Since the spreadsheet contains budget data, we will format the number as currency using the setNumberFormat method for the selected range.

    // Modify range F4:G5's cell style
    worksheet.getRange("F4:G5").getInterior().setThemeColor(ThemeColor.Light1);
    worksheet.getRange("F4:G5").getInterior().setTintAndShade(-0.15);
    worksheet.getRange("F4:G5").getFont().setThemeFont(ThemeFont.Major);
    worksheet.getRange("F4:G5").getFont().setSize(12);  worksheet.getRange("F4:G5").getBorders().get(BordersIndex.InsideHorizontal).setLineStyle(BorderLineStyle.None);
    worksheet.getRange("F5:G5").setNumberFormat("$#,##0.00");   

Below, we see how that range of cells now looks with the applied code:

Modify Table Style

Easily modify the appearance of tables within the Java worksheet using the setTableStyle() method. Below, we apply the predefined TableStyleLight10 to four different tables: "tblAdmissions," "tblAds," "tblVendors," and "tblItems."

worksheet.getTables().get("tblAdmissions").setTableStyle(workbook.getTableStyles().get("TableStyleLight10"));
    worksheet.getTables().get("tblAds").setTableStyle(workbook.getTableStyles().get("TableStyleLight10"));
    worksheet.getTables().get("tblVendors").setTableStyle(workbook.getTableStyles().get("TableStyleLight10"));
    worksheet.getTables().get("tblItems").setTableStyle(workbook.getTableStyles().get("TableStyleLight10"));

This allows you to create a consistent look across multiple tables in your worksheet, as seen here:

Modify Table Column’s Style

Since the table columns' ranges are different, set the ThemeColor, TintAndShade properties, and NumberFormat on a different column range of the table.

    worksheet.getRange("F8:G11, F15:G18, F22:G25, F29:G33").getInterior().setThemeColor(ThemeColor.Light1);
    worksheet.getRange("F8:G11, F15:G18, F22:G25, F29:G33").getInterior().setTintAndShade(-0.15);
    worksheet.getRange("E8:G11, E15:G18, E22:G25, E29:G33").setNumberFormat("$#,##0.00");

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("EventBudget_Modified.xlsx");

Just like that, you've programmatically transformed the formatting of the Excel XLSX document. Run the Java application, and your Excel spreadsheet will now appear as shown below:

Note: An Evaluation Warning worksheet is added to the exported Excel file because we are running an unlicensed version of DsExcel. Feel free to contact our Sales Team to request a trial license to remove this.

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.