无法将大量数据插入工作表 [英] Can't insert big amount of data to worksheet

问题描述

我无法在工作表中插入大量数据.错误是内存不足，无法继续执行程序".我正在使用Excel2007.我的数组有393210行和29列.首先，我尝试使用一个操作(Range =(对象)数组)将其插入.之后，我将数组分为几个部分.我的代码:

I can't insert big amount of data to worksheet. Error is "Insufficient memory to continue the execution of the program". I'm using Excel 2007. My array has 393210 rows and 29 columns. Firstly I tried to insert it using one operation (Range = (object) array). After that I divided array to several parts. My code:

    // Fill content
const int row_limit = 283500;
int row_start = 0;
int row_end;

int number_of_iterations = (int)Math.Ceiling((double)dtable.Rows.Count / (double)row_limit);

for (int idx = 0; idx < number_of_iterations; idx++)
{
    if (idx == number_of_iterations - 1)
        row_end = dtable.Rows.Count - 1;
    else
        row_end = row_start + row_limit - 1;

    object[,] arr = new object[row_end - row_start + 1, dtable.Columns.Count];
    int arr_row = 0;

    for (int r = row_start; r <= row_end; r++)
    {
        for (int c = 0; c < dtable.Columns.Count; c++)
        {
            arr[arr_row, c] = dtable.Rows[r][c];
        }
        arr_row++;
    }

    ((Excel.Range)ex_sheet.get_Range((Excel.Range)ex_sheet.Cells[row_start + 2, 1], (Excel.Range)ex_sheet.Cells[row_end + 2, dtable.Columns.Count])).Value = arr;

    row_start = row_end + 1;
}

恒定的row_limit设置一次操作期间插入的最大行数.如果该数字小于283000，则一切正常.但是如果超过283500，我会收到错误消息.

Constant row_limit sets the maximum amount of rows inserted during one operation. If the number is less than 283000 everything is ok. But if it is more than 283500 I get the error.

我在此处工作表大小为1,048,576行x 16,384列".此错误的原因是什么?

As I found out here "Worksheet size 1,048,576 rows by 16,384 columns". What is the reason of this error?

推荐答案

Excel 2007的内存限制为大约2GB(它是一个32位程序)，但实际上，它经常在达到该限制之前就耗尽内存由于内存碎片问题.
Excel在内部使用稀疏数组方案来跟踪工作表上正在使用的单元格，因此，尽管已发布的1,048,576行和16,384列限制是正确的，但在不超过2GB限制的情况下不可能使用所有这些单元格(您将需要超过1000 GB).
64位版本的Excel 2010和2013具有更大的内存限制.
有关以下内容的某些详细信息，请参见 http://www.decisionmodels.com/memlimitsc.htm 内存限制(按Excel版本).

Excel 2007 is limited to about 2GB of memory (it is a 32-bit program), but in practice it often runs out of memory well before reaching that limit due to memory fragmentation problems.
Internally Excel uses a sparse array scheme to track the cells in use on a worksheet so although the published limits of 1,048,576 rows and 16,384 columns are correct it is not possible to use all of these cells without exceeding the 2GB limit (you would need over 1000 GB).
The 64-bit versions of Excel 2010 and 2013 have much greater memory limits.
See http://www.decisionmodels.com/memlimitsc.htm for some details of memory limits by Excel version.

这篇关于无法将大量数据插入工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！