Create, Modify, and Save Excel Spreadsheets in Java Apps

In many cases, you may want to load existing templates of Excel documents, modify the data or modify the formatting of the spreadsheet and save it back.

For example:

• You are given an Excel spreadsheet with raw data of an event budget. You need to modify the formatting of the document and save it back.
• You have sales data for a year and want to show charted progress of sales. You need to add a chart over the data in the existing Excel spreadsheet, so it is easy to understand the progression of revenue.
• You have a template of financial KPI document with raw data on another sheet. You need to fill this data in the first sheet and then update the spreadsheet.

When working with Excel spreadsheets in Java, developers would need an Excel API that not only can create a spreadsheet, but also loads existing Spreadsheets, adds data to it or modifies existing data, adds formatting and saves it back. This article describes one of such use cases and demonstrates how to create, modify, and save Excel spreadsheets in Java applications.

An Excel API for Java Applications

Document Solutions for Excel, Java Edition (DsExcel, previously GcExcel) is an Excel API for Java applications, that can create, load, modify, and save spreadsheets to Excel, PDF, HTML, or Images. With DsExcel Java, you can load existing Excel spreadsheets, access the range of data, tables, fonts, formats, formulas, and shapes. You can modify these objects and save them back.

Read more about the key features of DsExcel Java.

Use Cases for Document Solutions for Excel

You receive several Excel templates for numerous budgets.

You are asked to:

1. Format and brand each individual spreadsheet
2. Modify the number format to currency for cell ranges containing numbers.

The budget reports can be any type (financial, event, monthly budget, travel budget), and you may receive them sporadically. To solve such problems, you may want to code the formatting of the spreadsheet and just change the cell ranges in them.

Lets get started with DsExcel, Java to solve these requests.

Download a Trial of Document Solutions for Excel, Java Edition, Today!

You are given a spreadsheet like this:

First, well want to format the sheet:

1. Change font properties of the title of the sheet and Total Income cell.
2. Change table, table column and cell styles

Steps to Programmatically Edit and Existing Excel files Using Java

1. Load existing Excel spreadsheet
2. Get the worksheet that you want to modify
3. Modify font properties of titles
4. Modify table style
5. Modify cell range's style
6. Modify table column's styles
7. Save the Excel file

Step 1: Load Existing Excel Spreadsheet

Write the following code to load an existing spreadsheet into your DsExcel Workbook:

Workbook workbook = new Workbook();
workbook.open("Event budget.xlsx");

Step 2: Get the Worksheet that You Want to Modify

IWorksheet worksheet = workbook.getActiveSheet();

Step 3: Modify Font Properties of Titles

Change font properties of B2 (font size) and E4 (Set to bold) cells:

//change range B2s font size.
worksheet.getRange("B2").getFont().setSize(22);

//change range E4s font style to bold.
worksheet.getRange("E4").getFont().setBold(true);

Step 4: Modify Table Style

Set styles of the four tables to an in-built style.

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

Step 5: Modify Cell Range's Style

Modify the cell style of Total Income cells. getInterior() method of a range can help you set the ThemeColor and TintAndShade properties. You can also change the font of entire range using getFont() method. In addition, this code can set the borders of a range of cells.

Since the spreadsheet shows the budget data, set the number format of a range of cells to currency.

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

Step 6: Modify Table Column's Styles

Since table columns' range is 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");

Step 7: Save the Excel File

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

Congratulations! You have entirely changed the formatting of the document. Run the application, and your Excel spreadsheet looks like this:

Java Spreadsheet 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!