以编程方式更新 Excel 单元格值是 Excel 自动化中最常见的任务之一。无论您是生成报告、更新数据文件还是处理批量信息，能够高效地使用 C# 更新 Excel 单元格对您的应用程序至关重要。
为什么要以编程方式更新 Excel 单元格？
- 📊 生成动态报告
- 🔄 批量数据更新
- 📈 自动化数据处理
- 💼 业务工作流程自动化
- 🕒 定期数据更新
开始使用 Openize.OpenXML SDK
using Openize.Cells;
using System;
基本单元更新
更新单个单元格
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!");
}
}
}
循环更新多个单元格
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;
}
高级更新操作
根据条件更新
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!");
}
}
使用范围操作进行批量更新
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!");
}
}
真实示例：员工数据更新
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!");
}
}
}
错误处理最佳实践
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}");
}
}
性能提示
- 批量更新：保存前更新多个单元格
- 使用范围：对于批量操作，使用范围方法
- 最小化文件 I/O：一次操作即可完成打开、更新、保存、关闭
- 数据类型感知：使用适当的 PutValue 重载
结论
使用 Openize.OpenXML SDK 以编程方式更新 Excel 单元格值简单高效。该库提供： -✅ 支持多种数据类型 -✅ 批量更新的范围操作 -✅高性能 -✅ 线程安全操作
- ✅ 无需安装 Excel 立即开始自动化您的 Excel 更新！