尝试将大Excel文件读入DataTable时发生OutOfMemoryException

问题描述

我正在使用SSIS包来清理数据并将.Xlsx文件中的数据加载到SQL Server表中.我还必须突出显示.Xlsx文件中包含错误数据的单元格，为此，我必须根据列名和行ID(我在数据电子表格中拥有的女巫)取回列索引和行索引.为此，我将第一个电子表格(Error_Sheet)中的每个列名称与第二个电子表格中添加的列的行进行比较，并对行进行相同的操作，如果我具有相同的单元格值，则返回该列并我的数据电子表格的行索引，并根据该列和行索引突出显示单元格.该脚本运行良好，但是尝试从服务器运行该脚本后，出现了内存异常，并且在以前运行良好的工作站上也出现了异常.

I'm using SSIS package to clean and load data from .Xlsx file to SQL Server table.I have also to highlight cells containing wrong data in .Xlsx file, for this I have to get back column and row indexes based on column name and row id(witch I have in my data spreadsheet). For that I compare each column name from my first spreadsheet (Error_Sheet) with rows of a column that I added in a second spreadsheet and do the same for rows, and if I have the same value of cells I get back the column and row indexes of my data spreadsheet and highlight the cell based on that column and row index. The script worked fine, but after trying to run it from a server I got an Memory exception and also on my workstation where it was working fine before.

我试图将数据采集的范围从AC1:AC10000减小到AC1:AC100，它仅在第一次编译后才起作用，但仍会再次引发异常.

I've tried to reduce the range that I'm taking data from : AC1:AC10000 to AC1:AC100, it worked only after the first time compilation, but it keeps throwing exception again.

string strSQLErrorColumns = "Select * From [" + Error_Sheet + "AC1:AC100]";
OleDbConnection cn = new OleDbConnection(strCn);

OleDbDataAdapter objAdapterErrorColumns = new OleDbDataAdapter(strSQLErrorColumns, cn);
System.Data.DataSet dsErrorColumns = new DataSet();
objAdapterErrorColumns.Fill(dsErrorColumns, Error_Sheet);
System.Data.DataTable dtErrorColumns = dsErrorColumns.Tables[Error_Sheet];
dsErrorColumns.Dispose();
objAdapterErrorColumns.Dispose();

foreach (DataColumn ColumnData in dtDataColumns.Columns){
    ColumnDataCellsValue = dtDataColumns.Columns[iCntD].ColumnName.ToString();
    iCntE = 0;

    foreach (DataRow ColumnError in dtErrorColumns.Rows){
        ColumnErrorCellsValue = dtErrorColumns.Rows[iCntE].ItemArray[0].ToString();

        if (ColumnDataCellsValue.Equals(ColumnErrorCellsValue)){

            ColumnIndex = ColumnData.Table.Columns[ColumnDataCellsValue].Ordinal;
            iCntE = iCntE + 1;
            break;
            }
        }

        iCntD = iCntD + 1;
    }

ColumnIndexHCell = ColumnIndex + 1;
RowIndexHCell = RowIndex + 2;

Range rng = xlSheets.Cells[RowIndexHCell, ColumnIndexHCell] as Excel.Range;
rng.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);

还有其他方法可以在不使用大量内存的情况下在DataTable中加载数据以获取列和行索引，或者通过使用Excel.Range.Cell代替数据集和DataTable从xlsx文件中获取单元格值，列和行索引吗?

There is any other way to load data in DataTable to get column and row index without using a lot of memory or by using Excel.Range.Cell instead of dataset and DataTable to get Cell value, column and row index from xlsx file please ?

我没有显示完整的代码，因为它很长.如果需要更多信息，请通知我.

I didn't show the whole code because it's long. Please keep me informed if more information needed.

推荐答案

当尝试从具有大量行的Excel中读取数据时，最好按块读取数据(在OleDbDataAdapter中，您可以使用分页选项来实现这一目标).

When trying to read data from an Excel with huge number of Rows, it is better to read data by chunk (in OleDbDataAdapter you can use paging option to achieve that).

int result = 1;
int intPagingIndex = 0;
int intPagingInterval = 1000;

while (result > 0){

    result = daGetDataFromSheet.Fill(dsErrorColumns,intPagingIndex, intPagingInterval , Error_Sheet);
    System.Data.DataTable dtErrorColumns = dsErrorColumns.Tables[Error_Sheet];

    //Implement your logic here

    intPagingIndex += intPagingInterval ;

}

这将防止OutOfMemory异常.无需再指定AC1:AC10000

This will prevent an OutOfMemory Exception. And no more need to specify a range like AC1:AC10000

参考

• 通过查询结果
• 填充(数据集，Int32，Int32，字符串)

• Paging Through a Query Result
• Fill(DataSet, Int32, Int32, String)

这篇关于尝试将大Excel文件读入DataTable时发生OutOfMemoryException的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！