Ускорение генерации excel файла на c#

#c_sharp #excel

Ситуация следующая:

Необходимо из набора данных (порядка 7000 значений) сгенерировать отчет. Структура
отчета задается шаблоном (набор стандартных блоков и порядок значений в строках блока,
количество строк в блоке и количество заполняемых ячеек варьируется), ячейки в которые
необходимо вставить данные ищутся поиском по ключевому тексту для значения. После вставки
данных незаполненные ячейки очищаются (тот же поиск, но вставка пустого текста, а не
значений). Вставка занимает в среднем 87с., удаление 33с. Есть варианты ускорить работу
приложения?

Код примерно следующий:

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 = "";
   }
}


Дополнение:

Ускорит ли работу вариант со сбором значений в массив по шаблону, а потом уже вставкой
массива в excel без поиска, просто по тем же индексам что в массиве?

Дополнение2:
Добавление в код следующих строк ускорило работу на 13%:

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


Заметки: Calculation можно изменить только при открытом документе и ручной расчет
влияет на доступность буфера обмена (при дефолтном значении во время генерации документа
нельзя было что-то скопировать в windows).

Для ускорения работы с данными можно работать с массивом вместо Range (Object[,]
vals = (System.Object[,])wsReport.Cells.get_Range(from, to)), но так не получается
сохранять форматирование из шаблона, как обойтись без Range, но сохранить форматирование?

Похожее обсуждение на stackoverflow.com.
Статья на microsoft.com по работе с массивом вместо range.

Дополнение 3:
Удалось сравнить Interop и EPPlus (3.0.0.2) на генерации одинаковых документов: Interop
медленнее в ~20 раз.
    

Ответы

Ответ 1

Делай как я:
Object[,] vals = (System.Object[,])wsReport.get_Range(from, to).get_Value(Type.Missing);

В этом массиве набираются и изменяются итоговые данные.
Excel.Range format = wsReport.Cells.get_Range(from, to);

Этот range используется для вставки форматов из шаблонов.
При получении блока из шаблона (rng) делаю так:
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++;//курсор на последнюю строчку
}

Вместо Find приходится делать так:
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: ;
}

В итоге время формирования документа со 150с. (вставка строк 0.6с, вставка значений
86с., удаление 32с.) уменьшилось до 45с (вставка строк 13с. вставка данных в массив
0.13с., удаление 0.32с, итоговая вставка 0.16).