c# – Speeding up the generation of excel file in c #

Question:

The situation is as follows:

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

The code is similar to the following:

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 the option of collecting values ​​into an array by a template, and then inserting the array into excel without searching, just by the same indices as in the array, speed up the work?

Addendum2: Adding the following lines to the code made the work faster by 13%:

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

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

To speed up working 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?

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

Appendix 3: It was possible to compare Interop and EPPlus (3.0.0.2) on generating 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 totals are typed 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 is from 150s. (inserting rows 0.6s, inserting values ​​86s., deleting 32s.) decreased to 45s (inserting rows 13s. inserting data into an array 0.13s., deleting 0.32s, final insertion 0.16).

Scroll to Top