Deleting formulas from cells in Excel document using C# and. NET
In today's world of automation and data processing, Excel is becoming an indispensable tool for business, analytics, and solution development. One of the most common scenarios is the need to clean up the sheets — for example, deleting formulas from cells, turning them into static values. In this article, we will look at how to implement the removal of formulas from Excel files using C# and .NET using the SautinSoft Excel .Net library.
Excel is a powerful tool for storing and analyzing data. Formulas automate calculations, increasing work efficiency, but there are situations when they need to be removed:
- Creating the final version of the document: to transfer a report or archive data without formulas.
- Change Prevention: disable automatic calculations to avoid unwanted data changes in the future.
- Ensuring data security and protection: deleting formulas prevents them from being viewed or modified.
- Data processing volume: with large automatic report generation, sometimes it is necessary to leave only the totals.
Complete code
GitHubusing SautinSoft.Excel;
using System.IO;
namespace Example
{
class Program
{
static void Main(string[] args)
{
// Get your free key here:
// https://sautinsoft.com/start-for-free/
Sample();
}
/// <summary>
/// Delete formulasin the cell range.
/// </summary>
/// <remarks>
/// Details:
/// </remarks>
static void Sample()
{
string inpFile = @"..\..\..\Example.xlsx";
string outFile = @"..\..\..\Result.xlsx";
ExcelDocument excelDocument = ExcelDocument.Load(inpFile);
CellRange range = excelDocument.Worksheets[0].Cells.GetSubrange("A7", "B12");
foreach (ExcelCell cell in range)
{
cell.Formula = null;
}
excelDocument.Save(outFile);
// Important for Linux: Install MS Fonts
// sudo apt install ttf-mscorefonts-installer -y
// Open the result for demonstration purposes.
System.Diagnostics.Process.Start(new System.Diagnostics.ProcessStartInfo(outFile) { UseShellExecute = true });
}
}
}Imports SautinSoft.Excel
Imports System.IO
Namespace Example
Friend Class Program
Shared Sub Main(ByVal args() As String)
' Get your free key here:
' https://sautinsoft.com/start-for-free/
Sample()
End Sub
''' <summary>
''' Delete formulasin the cell range.
''' </summary>
''' <remarks>
''' Details:
''' </remarks>
Private Shared Sub Sample()
Dim inpFile As String = "..\..\..\Example.xlsx"
Dim outFile As String = "..\..\..\Result.xlsx"
Dim excelDocument As ExcelDocument = ExcelDocument.Load(inpFile)
Dim range As CellRange = excelDocument.Worksheets(0).Cells.GetSubrange("A7", "B12")
For Each cell As ExcelCell In range
cell.Formula = Nothing
Next cell
excelDocument.Save(outFile)
' Important for Linux: Install MS Fonts
' sudo apt install ttf-mscorefonts-installer -y
' Open the result for demonstration purposes.
System.Diagnostics.Process.Start(New System.Diagnostics.ProcessStartInfo(outFile) With {.UseShellExecute = True})
End Sub
End Class
End Namespace
If you need a new code example or have a question: email us at support@sautinsoft.com or ask at Online Chat (right-bottom corner of this page) or use the Form below: