MS Excel provides an easy way to keep and share a large amount of tabular data. In addition, you can perform various operations such as applying formulas, generating charts and graphs, sorting and filtering data, and so on. In this article, you will learn how to implement Excel automation features from within your Java applications. We will demonstrate how to create Excel XLSX or XLS files from scratch in Java. In addition, we will cover how to update an existing Excel file, generate charts, apply formulas, and add pivot tables in Excel worksheets.

Java API to Create Excel Files - Free Download

Aspose.Cells for Java is a powerful spreadsheet manipulation API that lets you create or modify Excel files without MS Office. The API supports adding charts, graphs, formulas, and perform other spreadsheet manipulation operations programmatically. You can download the API for free or install it within your Maven-based applications.

<repository>
    <id>AsposeJavaAPI</id>
    <name>Aspose Java API</name>
    <url>https://repository.aspose.com/repo/</url>
</repository>
<dependency>
    <groupId>com.aspose</groupId>
    <artifactId>aspose-cells</artifactId>
    <version>20.9</version>
</dependency>

Create Excel XLSX or XLS in Java

The MS Excel files are referred to as workbooks and each workbook is composed of one or more worksheets. The worksheets further contain the rows and columns to keep the data in the form of cells. So let’s start by creating a simple workbook. The following are the steps to create an Excel XLSX file from scratch.

  • Create an instance of Workbook class.
  • Access the desired worksheet using Workbook.getWorksheets.get() method.
  • Put the value in the desired cell in the worksheet using the cell’s identifier, such as A1, B3, etc.
  • Save the workbook as an Excel file using the Workbook.save() method.

The following code sample shows how to create an Excel XLSX file using Java.

Edit an Excel XLSX File in Java

Lets now have a look at how to modify or insert data into an existing MS Excel file. For this, you can simply load the file, access the desired worksheet and save the updated file. The following are the steps to modify an existing Excel file.

  • Open Excel file using Workbook class.
  • Access the worksheets and cells using the Worksheet and Cell classes respectively.
  • Save the updated workbook as an Excel .xlsx file.

The following code sample shows how to edit an existing MS Excel file using Java.

Java Create Excel Files with Charts or Graphs

Charts in spreadsheets are used to visually represent the data stored in the worksheets. They make it easier to analyze a large amount of data quite easily. Aspose.Cells for Java provides a wide range of charts that you can create within the Excel files programmatically. The following are the steps to create an Excel file having chart in Java.

  • Create a new Excel file or load an existing one using Workbook class.
  • Add data to the worksheet (optional).
  • Get the chart collection of the worksheet using the Worksheet.getCharts() method.
  • Add a new chart using Worksheet.getCharts().add() method.
  • Get the newly created chart from the collection.
  • Specify the cells’ range to set NSeries for the chart.
  • Save the workbook as an Excel .xlsx file.

The following code sample shows how to create Excel XLSX file with a chart in Java.

create chart in excel using java

Java Create an XLSX File having a Pivot Table

Pivot tables in Excel worksheets have various purposes such as adding filters to the data, computing totals, summarizing data, and etc. Pivot tables can be created using the range of the cells in the worksheet. The following are the steps to create a pivot table in an Excel file in Java.

  • Create a new Workbook or load an existing file.
  • Insert data into the worksheet (optional).
  • Access the pivot table collection using Worksheet.getPivotTables() method.
  • Add a new pivot table in the worksheet using Worksheet.getPivotTables().add() method.
  • Provide data to the pivot table.
  • Save the workbook.

The following code sample shows how to create pivot table in Excel in Java.

create pivot table in excel using java

Add Formulas for Cells in Excel File in Java

Aspose.Cells for Java also allows you to work with formulas in the Excel worksheets. You can apply the built-in as well as add-in functions to the cells.

Apply Built-in Functions in Excel

For using the built-in functions, you can simply access the desired cell in the worksheet and add formula using the Cell.setFormula(String) method. The following code sample shows how to set a built-in formula using Java.

Add Add-in Functions in Excel

There might be a case when you have to use a user-defined function. For this, you will have to register the add-in function using a .xlam (Excel macro-enabled add-in) file and then use it for the desired cells. For registering the add-in functions, Aspose.Cells for Java provides registerAddInFunction(int, String) and registerAddInFunction(String, String, boolean) methods. The following code sample shows how to register and use an add-in function using Java.

Java API to Create Excel XLS XLSX Files - Get a Free License

You can use the API without evaluation limitations by getting a free temporary license.

Conclusion

In this article, you have seen how to create MS Excel files from scratch using Java without MS Office. You have also learned how to update workbooks, create charts, add tables, and apply formulas to cell values within MS Excel worksheets. You can learn more about Aspose’s Java Excel API using documentation.

See Also

Excel Files to PDF using Java JSON to Excel in Java Word to JSON in C#
Word to JSON in Python DOC to JSON in C# Excel File to JSON in Python
XLSX to JSON in C# XLS to JSON in C# XLSX to JSON in Java
Excel to JSON in Java DOCX to JSON in Java DOC to JSON in Java
Word to JSON in Java TXT to JSON in Java JSON to PDF in Python
Excel to JSON in Java TXT to JSON in Python Convert Excel Files to Google Sheets in C# .NET
Convert Excel to JSON in Java Convert Excel XLS/XLSX to CSV in Java Convert Excel XLS Files to XLSX in Python
Send Excel Data in Email Body using C# .NET Export Excel Data to Google Sheets in Python Convert Excel Files to Google Sheets in Java