Acceleration of excel file generation in c#

Question:

The situation is as follows:

It is necessary to generate a report from a data set (about 7000 values). The structure of the report is set by a template (a set of standard blocks and the order of values ​​in the rows of the block, the number of rows in the block and the number of cells to be filled in varies), the cells in which data must be inserted are searched for by searching for the key text for the value. After inserting data, empty cells are cleared (same search, but inserting empty text, not values). Insertion takes an average of 87s, removal 33s. Are there options to speed up the application?

The code is something like this:

foreach(значения)
{
   rngFind = rngReport.Find(Ключевой текст, Type.Missing, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlWhole, 
Excel.XlSearchOrder.xlByColumns, Excel.XlSearchDirection.xlNext, false, Type.Missing, false);
   if (rngFind != null)
   {
      rngFind.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
      rngFind.Value2 = Значение;
   }
}


while (flag)
{
   rngFind = rngReport.Find(Элемент ключа, rngReport.Cells[1, 1],  Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart,
                              Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false, Type.Missing, false);
   if (rngFind != null)
   {
      rngFind.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
      rngFind.Value2 = "";
   }
}

Addition:

Will it speed up the work of collecting values ​​into an array according to a template, and then inserting the array into excel without searching, just by the same indexes as in the array?

Addition2: Adding the following lines to the code speeded up the work by 13%:

xlApp.Calculation = Excel.XlCalculation.xlCalculationManual;
xlApp.ScreenUpdating = false;

Notes: Calculation can only be changed when a document is open, and manual calculation affects the availability of the clipboard (with the default value, nothing could be copied to windows during document generation).

To speed up work with data, you can work with an array instead of Range ( Object[,] vals = (System.Object[,])wsReport.Cells.get_Range(from, to) ), but you can’t keep the formatting from the template, how to do without Range but keep formatting?

Similar discussion on stackoverflow.com . An article on microsoft.com on working with an array instead of a range.

Addendum 3: It was possible to compare Interop and EPPlus (3.0.0.2) on the generation of identical documents: Interop is ~20 times slower.

Answer:

Do like me:

Object[,] vals = (System.Object[,])wsReport.get_Range(from, to).get_Value(Type.Missing);

In this array, the total data is collected and changed.

Excel.Range format = wsReport.Cells.get_Range(from, to);

This range is used to insert formats from templates.

When receiving a block from a template (rng), I do this:

rng.Copy(Type.Missing);//скопировали в буфер
((Excel.Range)format[fromRow, 1]).PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteFormats, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, Type.Missing, Type.Missing);//скопировали форматирование из шаблона на итоговый лист

Object[,] tmpvals = (Object[,])rng.get_Value(Type.Missing);//взяли значения из шаблона, теперь надо вставить в итоговый массив данных
for (int i = 1; i <= rng.Rows.Count; i++)
{
    if (fromRow > vals.GetUpperBound(0)) break;
    for (int j = 1; j<= rng.Columns.Count; j++)
    {
        vals[fromRow, j] = tmpvals[i,j];
    }
    fromRow++;//курсор на последнюю строчку
}

Instead of Find, you have to do this:

foreach (значения)
{
    for (int i = nRows - dx; i <= nRows; i++)
    {
        for (int j = 2; j < vals.GetUpperBound(1); j++)
        {
             if (vals[i,j]!=null && vals[i, j].ToString() == ключевой текст)
             {
                 vals[i, j] = значение;
                 goto Found;
             }
        }
    }
Found: ;
}

As a result, the time of document formation from 150s. (insert rows 0.6s, insert values ​​86s, delete 32s) decreased to 45s (insert rows 13s, insert data into array 0.13s, delete 0.32s, total insert 0.16).

Post Views: 0
Scroll to Top