create excel files in java

Microsoft Excel is a powerful and widely-used spreadsheet application, and many businesses rely on it for data storage, analysis, and reporting. When working with Java applications, you might find the need to create, modify, or manipulate Excel files programmatically. Aspose.Cells for Java is a robust Java library that provides a comprehensive set of APIs for working with Excel files. In this blog post, we will explore how to use Aspose.Cells for Java to create Excel files in Java and populate them with data.

Java Excel Library

Aspose.Cells for Java is a powerful spreadsheet manipulation library that lets you create or modify Excel files without MS Office. The library supports adding charts, graphs, formulas, and perform other spreadsheet manipulation operations programmatically. You can download the library 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>23.4</version>
</dependency>

Create an Excel XLS in Java

Now that you’ve set up your development environment, let’s move on to creating Excel files. 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 in Java.

// Create a new workbook
Workbook workbook = new Workbook();
// Add value in the cell
workbook.getWorksheets().get(0).getCells().get("A1").putValue("Hello World!");
// Save as Excel XLSX file
workbook.save("Excel.xlsx");
view raw create-excel-file.java hosted with ❤ by GitHub
create excel files in java

Edit an Excel XLS 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 Excel file in Java.

// Create a new workbook
Workbook workbook = new Workbook("workbook.xls");
// Get the reference of "A1" cell from the cells of a worksheet
Cell cell = workbook.getWorksheets().get(0).getCells().get("A1");
// Set the "Hello World!" value into the "A1" cell
cell.setValue("updated cell value.");
// Write the Excel file
workbook.save("Excel.xls", FileFormatType.EXCEL_97_TO_2003);
view raw update-excel-file.java hosted with ❤ by GitHub

Add Charts or Graphs to Excel Files

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 with a chart in Java.

// Create a new workbook
Workbook workbook = new Workbook("workbook.xlsx");
// Obtaining the reference of the first worksheet
WorksheetCollection worksheets = workbook.getWorksheets();
Worksheet sheet = worksheets.get(0);
// Adding some sample value to cells
Cells cells = sheet.getCells();
Cell cell = cells.get("A1");
cell.setValue(50);
cell = cells.get("A2");
cell.setValue(100);
cell = cells.get("A3");
cell.setValue(150);
cell = cells.get("B1");
cell.setValue(4);
cell = cells.get("B2");
cell.setValue(20);
cell = cells.get("B3");
cell.setValue(50);
// get charts in worksheet
ChartCollection charts = sheet.getCharts();
// Adding a chart to the worksheet
int chartIndex = charts.add(ChartType.PYRAMID, 5, 0, 15, 5);
Chart chart = charts.get(chartIndex);
// Adding NSeries (chart data source) to the chart ranging from "A1"
// cell to "B3"
SeriesCollection serieses = chart.getNSeries();
serieses.add("A1:B3", true);
// Write the Excel file
workbook.save("Excel_with_Chart.xlsx");
view raw insert-chart-in-excel.java hosted with ❤ by GitHub
create chart in excel using java

Create an Excel Pivot Table in Java

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 XLS 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 XLS in Java.

// Create a new workbook
Workbook workbook = new Workbook("workbook.xlsx");
// Get the first worksheet.
Worksheet sheet = workbook.getWorksheets().get(0);
// Obtaining Worksheet's cells collection
Cells cells = sheet.getCells();
// Setting the value to the cells
Cell cell = cells.get("A1");
cell.setValue("Sport");
cell = cells.get("B1");
cell.setValue("Quarter");
cell = cells.get("C1");
cell.setValue("Sales");
cell = cells.get("A2");
cell.setValue("Golf");
cell = cells.get("A3");
cell.setValue("Golf");
cell = cells.get("A4");
cell.setValue("Tennis");
cell = cells.get("A5");
cell.setValue("Tennis");
cell = cells.get("A6");
cell.setValue("Tennis");
cell = cells.get("A7");
cell.setValue("Tennis");
cell = cells.get("A8");
cell.setValue("Golf");
cell = cells.get("B2");
cell.setValue("Qtr3");
cell = cells.get("B3");
cell.setValue("Qtr4");
cell = cells.get("B4");
cell.setValue("Qtr3");
cell = cells.get("B5");
cell.setValue("Qtr4");
cell = cells.get("B6");
cell.setValue("Qtr3");
cell = cells.get("B7");
cell.setValue("Qtr4");
cell = cells.get("B8");
cell.setValue("Qtr3");
cell = cells.get("C2");
cell.setValue(1500);
cell = cells.get("C3");
cell.setValue(2000);
cell = cells.get("C4");
cell.setValue(600);
cell = cells.get("C5");
cell.setValue(1500);
cell = cells.get("C6");
cell.setValue(4070);
cell = cells.get("C7");
cell.setValue(5000);
cell = cells.get("C8");
cell.setValue(6430);
PivotTableCollection pivotTables = sheet.getPivotTables();
// Adding a PivotTable to the worksheet
int index = pivotTables.add("=A1:C8", "E3", "PivotTable2");
// Accessing the instance of the newly added PivotTable
PivotTable pivotTable = pivotTables.get(index);
// Unshowing grand totals for rows.
pivotTable.setRowGrand(false);
// Dragging the first field to the row area.
pivotTable.addFieldToArea(PivotFieldType.ROW, 0);
// Dragging the second field to the column area.
pivotTable.addFieldToArea(PivotFieldType.COLUMN, 1);
// Dragging the third field to the data area.
pivotTable.addFieldToArea(PivotFieldType.DATA, 2);
// Write the Excel file
workbook.save("Excel_with_Chart.xlsx");
view raw insert-pivottable-in-excel.java hosted with ❤ by GitHub
create pivot table in excel using java

Add Formulas for Cells in Excel Sheets

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.

// Create a new workbook
Workbook workbook = new Workbook();
// Add value in the cell
workbook.getWorksheets().get(0).getCells().get(0).setFormula("=H7*(1+IF(P7 =$L$3,$M$3, (IF(P7=$L$4,$M$4,0))))");
// Save as Excel XLSX file
workbook.save("Excel.xlsx");
view raw add-builtin-formula.java hosted with ❤ by GitHub

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.

// create a new workbook
Workbook workbook = new Workbook();
// Register macro enabled add-in along with the function name
int id = workbook.getWorksheets().registerAddInFunction("TESTUDF.xlam", "TEST_UDF", false);
// Register more functions in the file (if any)
workbook.getWorksheets().registerAddInFunction(id, "TEST_UDF1"); //in this way you can add more functions that are in the same file
// Access first worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);
// Access first cell
Cell cell = worksheet.getCells().get("A1");
// Set formula name present in the add-in
cell.setFormula("=TEST_UDF()");
// Save as Excel XLSX file
workbook.save("Excel.xlsx");
view raw add-addin-formula.java hosted with ❤ by GitHub

Free Online Excel Editor

We also provide an online Excel editor for you to create and edit Excel files. You can use this powerful spreadsheet editor without creating an account.

Get Free Java Excel Library

You can use the Java Excel library without evaluation limitations by getting a free temporary license.

Conclusion

Aspose.Cells for Java is a powerful and versatile library that simplifies the process of working with Excel files in Java applications. Whether you need to create basic spreadsheets or perform advanced data analysis and reporting, Aspose.Cells provides the tools and features to make your tasks easier. By following the steps outlined in this blog post, you can get started with creating Excel files in Java using Aspose.Cells for Java and unlock the full potential of Excel manipulation within your Java applications.

You can learn more about Aspose’s Java Excel library using documentation.

See Also

Excel Files to PDF using JavaJSON to Excel in JavaWord to JSON in C#
Word to JSON in PythonDOC 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 JavaDOCX to JSON in JavaDOC to JSON in Java
Word to JSON in JavaTXT to JSON in JavaJSON to PDF in Python
Excel to JSON in JavaTXT to JSON in PythonConvert Excel Files to Google Sheets in C# .NET
Convert Excel to JSON in JavaConvert Excel XLS/XLSX to CSV in JavaConvert Excel XLS Files to XLSX in Python
Send Excel Data in Email Body using C# .NETExport Excel Data to Google Sheets in PythonConvert Excel Files to Google Sheets in Java