Interop - Speed of inserting into open excel instance

Question

I am finding a running instance of excel and inserting data into one of the worksheets. Everything works, however inserting data is very slow. ~0.25 seconds for each cell.

Is there any way I can make this faster?

I have been looking for an approach that doesn't go cell by cell, but haven't found anything.

My code:

using System;
using System.Data;
using Microsoft.Office.Interop.Excel;
using Application = Microsoft.Office.Interop.Excel.Application;
using DataTable = System.Data.DataTable;


[STAThread]
static void Main()
{
    Application xlApp = null;

    try
    {
        xlApp = (Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
    }
    catch (Exception)//Excel not open
    {
        return;
    }

    xlApp.Visible = true;

    var wb = xlApp.ActiveWorkbook;

    Worksheet ws = null;

    try
    {
        ws = (Worksheet)wb.Worksheets["SomeSheet"];
    }
    catch (Exception e)
    {
        return;
    }

    if (ws == null)
    {
        return;
    }

    var dt = new DataTable();

    dt = new DataTable();
    dt.Clear();
    dt.Columns.Add("Col1");
    dt.Columns.Add("Col2");
    for (int ii = 0; ii < 20; ii++)
    {
        DataRow row = dt.NewRow();
        row["Col1"] = "xxxx";
        row["Col2"] = "yyyy";
        dt.Rows.Add(row);
    }

    //Header
    for (int i = 0; i < dt.Columns.Count; i++)
    {
        ws.Cells[1, i + 1] = dt.Columns[i].ColumnName;

    }

    //Data
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        for (int j = 0; j < dt.Columns.Count; j++)
        {
            ws.Cells[i + 2, j + 1] = dt.Rows[i][j];
        }
    }

}

Answer 1

Have you tried using a Range? Assigning a Array to the range ensuring the Range and Array are the same length should work.

https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.worksheet.range.aspx?cs-save-lang=1&cs-lang=csharp#code-snippet-1