Updating Excel cell values programmatically is one of the most common tasks in Excel automation. Whether you’re generating reports, updating data files, or processing bulk information, being able to update Excel cells in C# efficiently is crucial for your applications.

How to Update Excel Cell Values in C# Programmatically

Why Update Excel Cells Programmatically?

  • 📊 Generate dynamic reports
  • 🔄 Bulk data updates
  • 📈 Automated data processing
  • 💼 Business workflow automation
  • 🕒 Scheduled data updates

Getting Started with Openize.OpenXML SDK

using Openize.Cells;
using System;

Basic Cell Updates

Update Individual Cells

using Openize.Cells;

class CellUpdater
{
    public static void UpdateSingleCells()
    {
        using (var workbook = new Workbook("data.xlsx"))
        {
            var worksheet = workbook.Worksheets[0];
            
            // Update different data types
            worksheet.Cells["A1"].PutValue("Updated Text");
            worksheet.Cells["B1"].PutValue(12345);
            worksheet.Cells["C1"].PutValue(99.99);
            worksheet.Cells["D1"].PutValue(DateTime.Now);
            worksheet.Cells["E1"].PutValue(true);
            
            // Save changes
            workbook.Save();
            Console.WriteLine("Cells updated successfully!");
        }
    }
}

Update Multiple Cells in a Loop

public static void UpdateMultipleCells()
{
    using (var workbook = new Workbook("report.xlsx"))
    {
        var worksheet = workbook.Worksheets[0];
        
        // Update headers
        string[] headers = {"Product", "Price", "Quantity", "Total"};
        for (int i = 0; i < headers.Length; i++)
        {
            worksheet.Cells[$"{GetColumnLetter(i + 1)}1"].PutValue(headers[i]);
        }
        
        // Update data rows
        string[,] data = {
            {"Laptop", "999.99", "5", "4999.95"},
            {"Mouse", "25.50", "10", "255.00"},
            {"Keyboard", "75.00", "3", "225.00"}
        };
        
        for (int row = 0; row < data.GetLength(0); row++)
        {
            for (int col = 0; col < data.GetLength(1); col++)
            {
                string cellRef = GetCellReference(row + 2, col + 1);
                worksheet.Cells[cellRef].PutValue(data[row, col]);
            }
        }
        
        workbook.Save();
        Console.WriteLine("Multiple cells updated!");
    }
}

static string GetCellReference(int row, int col)
{
    return $"{GetColumnLetter(col)}{row}";
}

static string GetColumnLetter(int columnNumber)
{
    string columnLetter = string.Empty;
    while (columnNumber > 0)
    {
        columnNumber--;
        columnLetter = (char)('A' + columnNumber % 26) + columnLetter;
        columnNumber /= 26;
    }
    return columnLetter;
}

Advanced Update Operations

Update Based on Conditions

public static void ConditionalUpdate()
{
    using (var workbook = new Workbook("sales.xlsx"))
    {
        var worksheet = workbook.Worksheets[0];
        
        // Read and update based on conditions
        for (int row = 2; row <= worksheet.GetRowCount(); row++)
        {
            string salesValue = worksheet.Cells[$"C{row}"].GetValue();
            
            if (double.TryParse(salesValue, out double sales))
            {
                // Update status based on sales amount
                if (sales > 10000)
                {
                    worksheet.Cells[$"D{row}"].PutValue("High Performer");
                }
                else if (sales > 5000)
                {
                    worksheet.Cells[$"D{row}"].PutValue("Good");
                }
                else
                {
                    worksheet.Cells[$"D{row}"].PutValue("Needs Improvement");
                }
            }
        }
        
        workbook.Save();
        Console.WriteLine("Conditional updates completed!");
    }
}

Bulk Update with Range Operations

public static void BulkRangeUpdate()
{
    using (var workbook = new Workbook("template.xlsx"))
    {
        var worksheet = workbook.Worksheets[0];
        
        // Update a range of cells with the same value
        var range = worksheet.GetRange(1, 1, 1, 5); // A1:E1
        range.SetValue("HEADER");
        
        // Update a column with sequential values
        for (int row = 2; row <= 10; row++)
        {
            worksheet.Cells[$"A{row}"].PutValue($"Item {row - 1}");
            worksheet.Cells[$"B{row}"].PutValue(row * 100);
        }
        
        workbook.Save();
        Console.WriteLine("Bulk update completed!");
    }
}

Real-World Example: Employee Data Update

using Openize.Cells;
using System.Collections.Generic;

public class Employee
{
    public string Name { get; set; }
    public string Department { get; set; }
    public double Salary { get; set; }
    public DateTime HireDate { get; set; }
}

public class EmployeeDataUpdater
{
    public static void UpdateEmployeeData()
    {
        var employees = new List<Employee>
        {
            new Employee { Name = "John Doe", Department = "IT", Salary = 75000, HireDate = new DateTime(2020, 1, 15) },
            new Employee { Name = "Jane Smith", Department = "HR", Salary = 65000, HireDate = new DateTime(2019, 3, 20) },
            new Employee { Name = "Mike Johnson", Department = "Finance", Salary = 80000, HireDate = new DateTime(2021, 6, 10) }
        };
        
        using (var workbook = new Workbook())
        {
            var worksheet = workbook.Worksheets[0];
            worksheet.Name = "Employee Data";
            
            // Set headers
            worksheet.Cells["A1"].PutValue("Name");
            worksheet.Cells["B1"].PutValue("Department");
            worksheet.Cells["C1"].PutValue("Salary");
            worksheet.Cells["D1"].PutValue("Hire Date");
            worksheet.Cells["E1"].PutValue("Years of Service");
            
            // Update employee data
            for (int i = 0; i < employees.Count; i++)
            {
                int row = i + 2;
                var emp = employees[i];
                
                worksheet.Cells[$"A{row}"].PutValue(emp.Name);
                worksheet.Cells[$"B{row}"].PutValue(emp.Department);
                worksheet.Cells[$"C{row}"].PutValue(emp.Salary);
                worksheet.Cells[$"D{row}"].PutValue(emp.HireDate);
                
                // Calculate years of service
                int yearsOfService = DateTime.Now.Year - emp.HireDate.Year;
                worksheet.Cells[$"E{row}"].PutValue(yearsOfService);
            }
            
            workbook.Save("UpdatedEmployeeData.xlsx");
            Console.WriteLine("Employee data updated successfully!");
        }
    }
}

Error Handling Best Practices

public static void SafeUpdateCells(string filePath)
{
    try
    {
        using (var workbook = new Workbook(filePath))
        {
            var worksheet = workbook.Worksheets[0];
            
            // Validate before updating
            if (worksheet.GetRowCount() < 1 || worksheet.GetColumnCount() < 1)
            {
                Console.WriteLine("Worksheet is empty!");
                return;
            }
            
            // Safe update with validation
            string existingValue = worksheet.Cells["A1"].GetValue();
            if (!string.IsNullOrEmpty(existingValue))
            {
                worksheet.Cells["A1"].PutValue($"Updated: {existingValue}");
            }
            else
            {
                worksheet.Cells["A1"].PutValue("New Value");
            }
            
            workbook.Save();
            Console.WriteLine("Update completed successfully!");
        }
    }
    catch (FileNotFoundException)
    {
        Console.WriteLine("Excel file not found!");
    }
    catch (UnauthorizedAccessException)
    {
        Console.WriteLine("File is locked or you don't have permission!");
    }
    catch (Exception ex)
    {
        Console.WriteLine($"Error updating cells: {ex.Message}");
    }
}

Performance Tips

  1. Batch Updates: Update multiple cells before saving
  2. Use Ranges: For bulk operations, use range methods
  3. Minimize File I/O: Open, update, save, close in one operation
  4. Data Type Awareness: Use appropriate PutValue overloads

Conclusion

Updating Excel cell values programmatically with Openize.OpenXML SDK is straightforward and efficient. The library provides:

  • ✅ Multiple data type support
  • ✅ Range operations for bulk updates
  • ✅ High performance
  • ✅ Thread-safe operations
  • ✅ No Excel installation required

Start automating your Excel updates today!