These days, spreadsheets are widely used to keep and share a large amount of tabular data. Also, you can perform various operations such as applying formulas, generating charts and graphs, sorting and filtering data, and so on. The spreadsheets automation has brough a revolution in various industries. The dynamic creation and manipulation of the spreadsheets have made the data handling quite easier. In order to perform spreadsheet manipulation programmatically, this article covers how to create Excel XLSX or XLS files from scratch on Linux using Java.
- Java API to Create Excel Files on Linux
- Create Excel XLSX or XLS Files on Linux
- Edit Existing Excel Files
- Create Charts in Excel Files
- Create a Pivot Table in an XLSX
- Add Formulas for Cells in XLSX
API to Create Excel Files on Linux – Free Download
For creating and manipulating the Excel files, we will use Aspose.Cells for Java. It is a powerful spreadsheet manipulation API to create, update or convert Excel files. You can either download the API’s JAR or install it within your Maven-based applications using the following configurations.
<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>21.9</version>
</dependency>Create an Excel File on Linux
MS Excel files keep data in the worksheets that are composed of multiple cells. Whereas, these worksheets are contained by a workbook. The following are the steps to create an Excel XLSX file from scratch on Linux.
- Create an instance of Workbook class.
- Get reference of the desired worksheet using Workbook.getWorksheets.get() method.
- Insert value in the desired cell of the worksheet using the cell’s identifier, such as A1, B3, etc.
- Save the workbook using the Workbook.save() method.
The following code sample shows how to create an Excel XLSX file on Linux using Java.
The following is the output of the above code sample.
Edit an Excel XLSX File on Linux
You can also modify the existing Excel files seamlessly. For this, you can simply load the file, access the desired worksheet, and update its content. The following are the steps to edit an Excel XLSX file.
- Load the Excel file using Workbook class.
- Get reference of the worksheets and cells using the Worksheet and Cell classes, respectively.
- Update the content of the cells.
- Save the updated workbook using Workbook.save() method.
The following code sample shows how to edit an Excel file on Linux.
Create Charts in Excel Files on Linux
Charts in spreadsheets are used to visually represent the data stored in the worksheets. Aspose.Cells for Java provides a wide range of charts that you can generate within the Excel files. The following are the steps to create a chart in an Excel XLSX file on Linux platform.
- 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 to the collection using Worksheet.getCharts().add() method.
- Get reference of the newly created Chart from the collection.
- Specify the cells’ range to set NSeries for the chart.
- Finally, save the Excel file.
The following code sample shows how to create a chart in an Excel XLSX file.
The following screenshot shows the output of the above code sample.
Create a Pivot Table in Excel File on Linux
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. You can create a pivot table in an Excel file following the steps below.
- Create a new Excel file or load an existing one using Workbook class.
- 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 using Workbook.save() method.
The following code sample shows how to create a pivot table in Excel.
The following is the screenshot of the pivot table generated by the above code sample.
Add Formulas for Cells in Excel Files
Aspose.Cells for Java also supports working with formulas in Excel worksheets. The following sections describe how to work with built-in and add-in functions.
Apply Built-in Functions in Excel
For built-in functions, you can simply get reference of the desired cell in the worksheet and add a formula using the Cell.setFormula(String) method. The following code sample shows how to set a built-in formula in Excel.
Add Add-in Functions in Excel
Aspose.Cells for Java also supports user-defined function for the Excel worksheets. For this, you will have to register the add-in function using an xlam (Excel macro-enabled add-in) file. To register the add-in functions, the API provides registerAddInFunction(int, String) and registerAddInFunction(String, String, boolean) methods. The following code sample shows how to register and use an add-in function in Excel.
Get a Free License
You can use Aspose.Cells for Java without evaluation limitations using a temporary license.
Conclusion
In this article, you have learned how to create MS Excel files from scratch on Linux. Also, you have seen how to update the Excel files, generate charts, create pivot tables, and add formulas in the worksheets. You can learn more about Aspose.Cells for Java using documentation. In case you would have any questions, you can ask via our forum.
