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

GrapeCity Documents for Excel Java, Edition (GcExcel Java) is an Excel API for Java applications, that can create, load, modify, and save spreadsheets to Excel or PDF. With GcExcel 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 GcExcel Java.

Use cases for Documents 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 GcExcel, Java to solve these requests.

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

Lets see how to accomplish this using GcExcel, Java.

Step 1: Load existing Excel spreadsheet

Write the following code to load an existing spreadsheet into your GcExcel 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:

Browse our spreadsheet demos.