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
| import com.aspose.cells.Cell; | |
| import com.aspose.cells.License; | |
| import com.aspose.cells.Workbook; | |
| public class HowToRemoveFormulaButKeepDataInExcelInJava { | |
| public static void main(String[] args) throws Exception { //main function for HowToRemoveFormulaButKeepDataInExcelInJava | |
| // Initialize a license to avoid trial version watermark in the output file after removing formulas | |
| License license = new License(); | |
| license.setLicense("Aspose.Cells.lic"); | |
| // Load the Excel file from which formula is to be removed | |
| Workbook excelWorkbookWithFormula = new Workbook("SampleExcelWithFormula.xlsx"); | |
| // Get a reference of the cell where formula is to be removed | |
| Cell cellWithFormula = excelWorkbookWithFormula.getWorksheets().get(0).getCells().get("C1"); | |
| // Store the value in a temporary variable for later use | |
| Object tempData = cellWithFormula.getValue(); | |
| // Remove the formula by setting its value empty | |
| cellWithFormula.setFormula(""); | |
| // Save the value back from the temporary variable | |
| cellWithFormula.setValue(tempData); | |
| // Save the workbook with data without the formula | |
| excelWorkbookWithFormula.save("WorkbookWithDataOnly.xlsx"); | |
| } | |
| } |
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
| import com.aspose.cells.License; | |
| import com.aspose.cells.Workbook; | |
| import com.aspose.cells.Worksheet; | |
| public class HowToRemoveAllFormulasButKeepDataInExcelInJava { | |
| public static void main(String[] args) throws Exception { //main function for HowToRemoveAllFormulasButKeepDataInExcelInJava | |
| // Initialize a license to avoid trial version watermark in the output file after removing all formulas | |
| License license = new License(); | |
| license.setLicense("Aspose.Cells.lic"); | |
| // Load the target workbook from which all formulas are to be removed | |
| Workbook workbookWithFormula = new Workbook("WorkbookWithFormulas.xlsx"); | |
| // Iterate through all the worksheets in the workbook to remove formulas | |
| for(Object object : workbookWithFormula.getWorksheets()) | |
| { | |
| Worksheet worksheet = (Worksheet)object; | |
| worksheet.getCells().removeFormulas(); | |
| } | |
| // Save the resultant workbook having data only | |
| workbookWithFormula.save("WorkbookWithoutFormulas.xlsx"); | |
| } | |
| } |
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.
