Excel Interop - insert & add data by row

Question

Hey guys I need to insert empty rows below a certain row in an excel and then add data into those empty rows I inserted...

So far I am able to create empty rows but I am having a hell of a time trying to figure out how to set Range.Value to an array of type String

Method for inserting Rows:

private void shiftRows(int from, int numberof)
    {
        from++;
        Range r = oXL.get_Range("A" + from.ToString(), "A" + from.ToString()).EntireRow;

        for (int i = 0; i < numberof; i++)
            r.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown);
    }
 // so this would shift the below rows by numberof times. 

This method is currently what I am stuck on... which is inserting an array into the new rows one row at a time

    public void inputRowData(string[] data, int rds)
    {
        int bestRow = getRowByRDS(rds);
        string val = getValueOfCell(bestRow, 6);
        if (val == null || val.Equals(""))
        {
            shiftRows(bestRow, data.Length);
            string[] formatedData = formatOutput(bestRow, data);
            for (int i = 0; i < formatedData.Length; i++)
            {
                Range r = oSheet.get_Range((bestRow + i).ToString() + ":" + (bestRow + i).ToString());
                r.set_Value(formatedData[i].Split('\t'));

// have tried r.Value = formatedData[i].Split('\t')
// formatedData is an array of string which contains data for each cell seperated by a tab

            }

        }
        else
        {
            Console.WriteLine("Line has some information already, skipping 1 more");
            shiftRows(bestRow, data.Length + 1);
        }

    }

Answer 1

I strongly advise you:

• NOT to insert rows but just write empty row instead (safety and performance)
• to set a big array object and do only ONE write in excel (performance)

example (i kept the shiftrows but you should really get rid of it):

public void inputRowData(string[] data, int rds)
{
    int bestRow = getRowByRDS(rds);
    string val = getValueOfCell(bestRow, 6);
    if (val == null || val.Equals(""))
    {
        shiftRows(bestRow, data.Length);
        string[] formatedData = formatOutput(bestRow, data);
        // transform formated data into string[,]
        var string[][] splitedData = formatedData.Select(s => s.Split('\t')).ToArray();
        var colCount = splitedData.Max(r => r.Lenght);
        var excelData = new string[splitedData.Length, colCount]
        for (int i = 0; i < splitedData.Length; i++)
        {
            for (int j = 0; j < splitedData[i].Length; j++)
            {
                 excelData[i,j] = splitedData[i][j];
            }
        }
        oSheet.get_Range("A" + bestRow.ToString()).Resize(splitedData.Length, colCount).Value = excelData;
    }
    else
    {
        Console.WriteLine("Line has some information already, skipping 1 more");
        shiftRows(bestRow, data.Length + 1);
    }

}