从Excel工作簿在C＃中读取数据时的低性能的ArrayList [英] Low performance when reading data from Excel workbook to ArrayList in C#

问题描述

下面是一个问题的描述

条件：
总的想法是读取MS Excel文件了很多实数，并把它们企口的ArrayList进一步处理。
Excel工作簿中只有一个工作表。所有的数字是真实的，它们都存储在一列。
我读到行这些数字行，把它们放到ArrayList中

问题：这个过程需要太多的时间。计划花费约2分钟，以填补一个ArrayList 10000元。
这里是我的代码。我需要您的意见，使其更快。但该文件的结构不能被修改。这是唯一可以修改代码。
帮我请，使其更快。

  //方法GetExcelData 1打开excel文件，按行读取数据行并增加了
 //入源数据值（在我们的例子sourceDataValues​​）的阵列。 
私人无效GetExcelData（字符串完整路径，ArrayList的arrForValues​​）
 {
 Excel.Application excelapp =新Excel.Application（）; 
 excelapp.Visible = FALSE; 
 //以避免在屏幕上
 Excel.Workbook excelappworkbook = excelapp.Workbooks.Open出现Excel窗口中（
 FULLPATH，
 Type.Missing，Type.Missing，真实， Type.Missing，
 Type.Missing，Type.Missing，Type.Missing，Type.Missing，
 Type.Missing，Type.Missing，Type.Missing，Type.Missing，
型。缺少，Type.Missing）; 
 Excel.Worksheet excelworksheet =（Excel.Worksheet）excelappworkbook.Worksheets.get_Item（1）; 
 Excel.Range excelcells = excelworksheet.UsedRange; 
 UINT rowsNum = 0; 
为（rowsNum = 1;！rowsNum = excelcells.Rows.Count; rowsNum ++）
 {
 arrForValues​​.Add（（excelcells.Cells [rowsNum，1] Excel.Range）.Value2 ）; 
} 
 excelappworkbook.Close（假，Type.Missing，Type.Missing）; 
 excelapp.Quit（）; 
} 
  

解决方案

该问题得到解决。
一切都是qute简单。首先，我们看当前工作表为简单的二维数组中的所有范围 - worksheetValues​​Array。之后，我们把从阵列中的所有值到我们的容器中，元素转换为double类型。下面是纠正解决方案的一部分：

 私人无效GetExcelData（字符串完整路径，列表与LT;双> arrForValues​​）
 {
 Excel.Application excelapp =新Excel.Application（）; 
 excelapp.Visible = FALSE; 
 //以避免在屏幕上
 Excel.Workbook excelappworkbook = excelapp.Workbooks.Open出现Excel窗口中（
 FULLPATH，
 Type.Missing，Type.Missing，真实， Type.Missing，
 Type.Missing，Type.Missing，Type.Missing，Type.Missing，
 Type.Missing，Type.Missing，Type.Missing，Type.Missing，
型。缺少，Type.Missing）; 
 Excel.Worksheet excelworksheet =（Excel.Worksheet）excelappworkbook.Worksheets.get_Item（1）; 
 Excel.Range excelcells = excelworksheet.UsedRange; 
对象[，] worksheetValues​​Array = excelcells.get_Value（Type.Missing）; 
 
为（INT COL = 1;西≤（worksheetValues​​Array.GetLength（1）+1）;西++）
 {
为（INT行= 1;行< （worksheetValues​​Array.GetLength（0）+1）;排++）
 {
 arrForValues​​.Add（（双）worksheetValues​​Array [行，列]）; 
} 
} 
 excelappworkbook.Close（假，Type.Missing，Type.Missing）; 
 excelapp.Quit（）; 
} 
  

Here's a problem description.

CONDITIONS: General idea is to read a lot of real numbers from MS Excel file and put them inro ArrayList for further processing. An excel workbook has only one worksheet. All the numbers are real and they are stored in one column. I read these numbers row by row and put them into ArrayList.

PROBLEM: the process takes too much time. Program spends about 2 minutes to fill an ArrayList with 10000 elements. Here's my code. I need your advise to make it faster. But the structure of the file cannot be modified. It's only possible to modify code. Help me please to make it faster.

// Method GetExcelData opens 1 excel file, reads data row by row and adds
// it into the array of source Data Values (sourceDataValues in our case).
private void GetExcelData(string fullPath, ArrayList arrForValues)
{
    Excel.Application excelapp = new Excel.Application();
    excelapp.Visible = false;
    // to avoid appearing of Excel window on the screen
    Excel.Workbook excelappworkbook = excelapp.Workbooks.Open(
        fullPath,
        Type.Missing, Type.Missing, true, Type.Missing,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing);
    Excel.Worksheet excelworksheet = (Excel.Worksheet)excelappworkbook.Worksheets.get_Item(1);
    Excel.Range excelcells = excelworksheet.UsedRange;
    uint rowsNum = 0;
    for (rowsNum = 1; rowsNum != excelcells.Rows.Count; rowsNum++)
    {
        arrForValues.Add((excelcells.Cells[rowsNum, 1] as Excel.Range).Value2);
    }
    excelappworkbook.Close(false, Type.Missing, Type.Missing);
    excelapp.Quit();
}

解决方案

The problem is resolved. Everything is qute simple. First, we read all the range of current worksheet into simple two-dimension array - worksheetValuesArray. After that we put all the values from that array into our container, converting the type of elements to double. Here's the part of corrected solution:

private void GetExcelData(string fullPath, List<double> arrForValues)
        {
            Excel.Application excelapp = new Excel.Application();
            excelapp.Visible = false;
            // to avoid appearing of Excel window on the screen
            Excel.Workbook excelappworkbook = excelapp.Workbooks.Open(
                fullPath,
                Type.Missing, Type.Missing, true, Type.Missing,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing);
            Excel.Worksheet excelworksheet = (Excel.Worksheet)excelappworkbook.Worksheets.get_Item(1);
            Excel.Range excelcells = excelworksheet.UsedRange;
            object[,] worksheetValuesArray = excelcells.get_Value(Type.Missing);

            for (int col = 1; col < (worksheetValuesArray.GetLength(1)+1); col++)
            {
                for (int row = 1; row < (worksheetValuesArray.GetLength(0)+1); row++)
                {
                    arrForValues.Add((double) worksheetValuesArray[row, col]);
                }
            }
            excelappworkbook.Close(false, Type.Missing, Type.Missing);
            excelapp.Quit();
        }

这篇关于从Excel工作簿在C＃中读取数据时的低性能的ArrayList的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！