Memperbarui nilai sel Excel secara terprogram adalah salah satu tugas paling umum dalam otomatisasi Excel. Baik Anda membuat laporan, memperbarui berkas data, atau memproses informasi massal, kemampuan untuk memperbarui sel Excel dalam C# secara efisien sangat penting untuk aplikasi Anda.

Cara Memperbarui Nilai Sel Excel di C# Secara Terprogram

Mengapa Memperbarui Sel Excel Secara Terprogram?

  • 📊 Hasilkan laporan dinamis
  • Pembaruan data massal
  • Pemrosesan data otomatis
  • Otomatisasi alur kerja bisnis
  • 🕒 Pembaruan data terjadwal

Memulai dengan Openize.OpenXML SDK

using Openize.Cells;
using System;

Pembaruan Sel Dasar

Perbarui Sel Individual

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!");
        }
    }
}

Memperbarui Beberapa Sel dalam Satu 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;
}

Operasi Pembaruan Lanjutan

Pembaruan Berdasarkan Kondisi

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!");
    }
}

Pembaruan Massal dengan Operasi Jangkauan

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!");
    }
}

Contoh Dunia Nyata: Pembaruan Data Karyawan

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!");
        }
    }
}

Praktik Terbaik Penanganan Kesalahan

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}");
    }
}

Tips Performa

  1. Pembaruan Batch: Perbarui beberapa sel sebelum menyimpan
  2. Gunakan Rentang: Untuk operasi massal, gunakan metode rentang
  3. Minimalkan File I/O: Buka, perbarui, simpan, tutup dalam satu operasi
  4. Kesadaran Tipe Data: Gunakan kelebihan PutValue yang tepat

Kesimpulan

Memperbarui nilai sel Excel secara terprogram dengan Openize.OpenXML SDK mudah dan efisien. Pustaka ini menyediakan:

  • ✅ Dukungan berbagai tipe data
  • ✅ Operasi jangkauan untuk pembaruan massal
  • ✅ Kinerja tinggi
  • ✅ Operasi aman untuk thread
  • ✅ Tidak perlu instalasi Excel Mulailah mengotomatiskan pembaruan Excel Anda hari ini!