写一个大的二维数组到Excel [英] Writing a large 2d array to Excel

问题描述

我期待写一个大的二维数组使用C＃的Excel工作表。如果数组为500×500，我会用它来写这是如下的代码：

I'm looking to write a large 2d array to an Excel worksheet using C#. If the array is 500 x 500, the code that I would use to write this is as follows:

 var startCell = Worksheet.Cells[1, 1];
 var endCell = Worksheet.Cells[500, 500];
 var writeRange = (Excel.Range)Worksheet.Cells[startCell, endCell;
 writeRange.Value = myArray;

我得到在这条线的异常：

I get an exception on this line:

var endCell = Worksheet.Cells[500, 500];

由于谁使用C＃和Excel通过COM任何人都可以作证，收到错误消息是几乎无用。我认为问题是，用于工作表中的基础数据结构具有足够的大小不是索引单元500,500，当我第一次创建的工作表。

As anybody who has used C# and Excel via COM can testify, the error message received is pretty much useless. I think that the issue is that the underlying data structure used for the worksheet is not of sufficient size to index cell 500,500 when I first create the sheet.

是否有人知道如何以实现所期望的结果？我希望有一个简单的方法创建范围之前重新大小底层的数据结构。

Does anybody know how to achieve the desired result? I'm hoping that there is a simple way to re-size the underlying data structure before creating the Range.

感谢。

编辑：错误是：

{"Exception from HRESULT: 0x800A03EC"}

通过和Excel -2146827284错误代码

With and excel error code of -2146827284.

更新：在下面的意见提供链接提到的一个问题，在兼容模式下打开Excel工作表。这似乎是这个问题。如果我保存的.xlsx或.XLSM格式文档运行我的代码之前，这似乎工作。我的问题是，我不能指望我的用户这每一次做的。是否有实现这一目标的方式programmitcal？难道仅仅是打开文件，检查扩展程序，然后将其保存在如果需要新格式的情况？

Update: The link supplied in the comments below alluded to an issue with opening the Excel sheet in compatibility mode. This does seem to be the problem. If I save the document in .xlsx or .xlsm format before running my code, this seems to work. My issue is that I cannot expect my users to do this each time. Is there a programmitcal way of achieving this? Would it simply be a case of opening the file, checking the extension and then saving it in the new format if needed?

推荐答案

找到了解决办法。

而不是使用Worksheet.Cells的[X，X]，使用Worksheet.get_range（X，X）来代替。

Instead of using Worksheet.Cells[x, x], use Worksheet.get_range(x, x) instead.

这篇关于写一个大的二维数组到Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！