How to Import and Export Excel XLSX using Java

Working with Excel files is a common need in Java applications whether for data analysis, reporting, or general spreadsheet manipulation. A reliable Java Excel API, like Document Solutions for Excel, can simplify these tasks and improve efficiency. In this guide, we’ll show you how to programmatically import, modify, and export Excel (XLSX) files in Java using a dedicated server-side Excel API library.

Download the Latest Release of Our Java Excel API Library Today!

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

1. Create a Java Excel API Application
2. Import an XLSX File with a 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. Export the Java Workbook to XLSX

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 an XLSX File with a 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");

Export Java Workbook to XLSX

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.

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