在Excel互操作中处理较大的选择范围

问题描述

我正在尝试将电子表格数据捕获到2D数组中.我正在使用VSTO.

I am trying to capture a spreadsheet data in to a 2D array. I am using VSTO.

int rc = 1048576;
int cc = 1638;

string[,] arr = new string[rc, cc];

最后一行抛出内存不足"异常.我想显示一条消息，告诉用户只能捕获'X'元素.

The last line throws Out of Memory exception. I would like to show message telling the user only 'X' elements can be captured.

检查了MSDN，数据表的行数限制为16,777,216，因此数据表可以容纳工作表大小的数据.无法找到二维数组的限制.

Checked MSDN and there is a row count limit mentioned of 16,777,216 for the data-table.So a data table can hold data of size of a worksheet. Cant find limit either for 2D array.

我的问题不是为什么例外.我要寻找的是如果您正在进行VSTO开发，并且必须捕获DataTable中的工作表以执行内存中连接等，则需要执行以下操作:

My issue is not with WHY the exception. What I am looking for is if you are doing VSTO development, and had to capture a worksheet in a DataTable to perform In-Memory joins etc, you will need to do this:

string[,] arr = new string[rc, cc];
Microsoft.Office.Interop.Excel.Range selection
arr = selection.Value as string[,];

，然后将数据从该数组复制到数据表.现在，用户应该选择的元素数量的理想限制是多少.因此，我可以设置行计数/列计数限制，并在选择超出此条件时显示消息.或考虑到第一行始终是列标题，还有其他方法可以根据工作表中的选定范围来创建数据表.

and then copy the data from that array to data table. Now what will be the ideal limit for number of elements a user should select. So I can set that rowcount/columncount lmits and display message when selection exceeds this criteria.OR is there any other way to create a data table based on selected range in a worksheet, considering first row is always Column headers.

推荐答案

您可以创建自己的类:

    public class MyClass {
 public static string col1 {get;set;}
 public static string col2 {get;set;}
  public static string col3 {get;set;}
 //etc for every column

}

将第一行读入MyClass的属性

read the first row into the properties of MyClass

然后将工作表的每一行读入如下列表:

then read every row of your worksheet into a list like this:

List<string> myList = new IList<string>();

myList.append(anInstanceofMyClass);

对每行数据重复

这篇关于在Excel互操作中处理较大的选择范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！