.NET下如何向Excel中非相邻单元格的命名区域写入数据

问题原因 #

向非相邻单元格组成的命名区域直接赋值二维数组时，Excel会将数组的第一个元素重复填充到每个独立区域块，而非按顺序分配元素到对应单元格。多次调用Range方法会产生大量COM交互开销，导致20个工作表的批量写入操作速度缓慢。

优化解决方案 #

通过禁用Excel后台耗时操作+遍历非相邻区域单元格的方式，减少交互开销，实现高效批量赋值：

1. 关闭Excel性能消耗项

' 关闭屏幕更新、事件触发和自动计算，大幅提升处理速度
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

2. 批量遍历工作表与目标区域
   假设要写入的7个值已存入一维数组dataArr，遍历每个工作表的非相邻命名区域，按顺序给单元格赋值：

Dim ws As Worksheet
Dim targetRange As Range
Dim cell As Range
Dim dataArr As Variant
Dim idx As Integer

' 示例：初始化要写入的7个值
dataArr = Array("数值1", "数值2", "数值3", "数值4", "数值5", "数值6", "数值7")

' 遍历所有目标工作表
For Each ws In ThisWorkbook.Worksheets
    Set targetRange = ws.Range("你的非相邻命名区域名称")
    idx = 0
    ' 遍历区域内每个单元格，按顺序赋值数组元素
    For Each cell In targetRange
        cell.Value = dataArr(idx)
        idx = idx + 1
        ' 避免数组越界（需确保命名区域单元格数与数组元素数一致）
        If idx > UBound(dataArr) Then Exit For
    Next cell
Next ws

3. 恢复Excel默认设置

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

效果说明 #

这种方式将多次Range调用合并为一次区域获取+单元格遍历，配合Excel性能优化设置，能显著降低20个工作表批量写入的耗时，同时解决非相邻区域赋值重复第一个元素的问题。

内容的提问来源于stack exchange，提问作者trainingBro