This tutorial explains how to remove formula but keep data in Excel in Java using simple code. It will provide details about loading an Excel file and then processing it to remove the formula by accessing each cell individually. In the end, you can save this file as it is or in a different format like XLSX file on the disc.
Steps to Remove Formula but Keep Data in Excel in Java
- From the Maven repository, add a reference to Aspose.Cells library to your project
- Add a reference to Workbook and Cell classes by using Import in the program
- Load the workbook from which formula is to be removed
- Get a reference to the cell in the target worksheet where the formula is to be removed
- Using the cell reference save the existing value in a temporary variable
- Set the formula empty in the target cell
- Set cell value from the temporary variable
- Save the output file without formula having data only
Using these steps, you can access any worksheet of the target Excel file and then get the reference to the desired cell whose formula is to be removed. During this operation first, you save the value in a temporary variable and then remove the formula. Finally, the original value is set again in the same cell.
Code to Delete Formula in Excel without Deleting Data in Java
The above sample code demonstrates how to remove formula but keep the data in Excel in Java cell by cell. However if you are having a large file and want to remove all the formulas from the entire workbook at once, just call the removeFormulas() in the Cells collection of the worksheet as demonstrated in the following sample code.
Code to Remove All Formulas in Excel without Deleting Data in Java
It is to be noted that no other third-party tool or software like Interop or MS Excel is required to run this code. Also if you want to save the output file in some other format like PDF, you may refer to the article on how to generate PDF from Excel in Java.