C#迭代Excel工作表的有效方法 [英] C# Efficient way to iterate over excel worksheet

问题描述

我有以下代码:

string result = "";
for(int i=2; i<=excelRange.Rows.Count; i++)
{
    result += excelRange.Cells[i, 2].Value2;
}

对于具有数百个条目的excel文件，此过程需要5秒钟.也许有一种更有效的方法吗?我只想要B2到Bn的值.

For an excel file with a couple hundred entries this takes 5 seconds. Is there a more efficient way, perhaps? I only want the values from B2 to Bn.

推荐答案

是的，有一种更有效的方法.

Yes, there is a more efficient way.

1. 创建一个与您真正需要的单元格完全匹配的范围.

1. Create a range that exactly matches the cells that you really need.

获取此范围的 Value2 属性.结果将是数组类型.

Get the Value2 property of this range. The result will be an array type.

遍历数组

您的方法存在的问题是应用程序和Excel之间存在大量的进程间请求.您的方法每个单元需要两个或三个请求.提议的方法要快得多，因为它需要预先几个请求，但每个单元不需要额外的请求.

The problem with your approach is the large number of inter-process requests between your application and Excel. Your approach requires two or three requests per cell. The proposed approach is much faster because it requires a few requests up-front but not additional requests per cell.

请注意，这最多可以处理4000个单元格.如果需要处理更多的单元格，则需要将其分成几个范围，每个范围包含少于4000个单元格.

Note that this works up to about 4000 cells. If you need to process more cells, you will need to split it into several ranges, each one containing less than 4000 cells.

更新

假设Excel已经在运行，则看起来像这样(在B列中自动选择了正确的行数):

Assuming Excel is already running, it would look something like this (the correct number of rows in the B column is automatically selected):

var excelApp =  (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
var range = (Excel.Range)workSheet.Range[workSheet.Range["B2"],
        workSheet.Range["B2"].End[Excel.XlDirection.xlDown]];
var cellData = (Object[,])range.Value2;

string result = "";
foreach (var cell in cellData) {
    result += cell.ToString();
}

这篇关于C#迭代Excel工作表的有效方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！