Putting array values into DataGridView

Question

I've been trying to figure out for awhile now how to add array values into a DataGridView. As it stands now, all of the values get added to a single Column, when I am really looking for something along the lines of this:

Col1  Col2
Name  Number
Adam  3
Ryan  4

Right now they show up as

Col1  Col2
Name
Number
Adam
3
Ryan
4

The array is being populated by reading in values of an excel spreadsheet. I've searched and have tried different approaches to no success. Here is the code that I currently am working with.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;

namespace WindowsFormsApplication12
{
public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }

    private void button1_Click(object sender, EventArgs e)
    {
        Excel.Application xlApp ;
        Excel.Workbook xlWorkBook ;
        Excel.Worksheet xlWorkSheet ;
        Excel.Range range ;

        //string str;
        int rCnt = 0;
        int cCnt = 0;

        xlApp = new Excel.ApplicationClass();
        xlWorkBook = xlApp.Workbooks.Open("C:\\Text Files\\test1.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

        range = xlWorkSheet.UsedRange;

        string[,] excelArray = new string[range.Rows.Count, range.Columns.Count];
        for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
        {
            for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
            {
                excelArray[rCnt - 1, cCnt - 1] = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2.ToString();
                //excelArray[rCnt - 1,cCnt - 1] = str;
                //listBox1.Items.Add("location " + rCnt + "," + cCnt + " value " + excelArray[rCnt - 1, cCnt - 1] + excelArray[rCnt, cCnt - 1]);

                this.dataGridView1.Rows.Add(excelArray[rCnt -1, cCnt - 1]);
                //dataGridView1.DataSource = excelArray[rCnt - 1, cCnt - 1];
            }
        }

        xlWorkBook.Close(true, null, null);
        xlApp.Quit();

        releaseObject(xlWorkSheet);
        releaseObject(xlWorkBook);
        releaseObject(xlApp);
    }

    private void releaseObject(object obj)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            obj = null;
        }
        catch (Exception ex)
        {
            obj = null;
            MessageBox.Show("Unable to release the Object " + ex.ToString());
        }
        finally
        {
            GC.Collect();
        }
    }

}

}

Any push in the right direction would be greatly appreciated.

Answer 1

In order to avoid coordination problems (and keep your current loops), I think that the best solution is separating the row-creation and cell-population parts. That is, firstly you add all the rows (I understand that you have already added all the required columns to DataGridView1):

for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
{
    dataGridView1.Rows.Add();
}

Such that you can continue using the two nested loops and populate the corresponding cells by relying on the column/row indices:

for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
{
    for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
    {
        excelArray[rCnt - 1, cCnt - 1] = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2.ToString();

        this.dataGridView1[cCnt - 1, rCnt - 1].Value = excelArray[rCnt - 1, cCnt - 1]; //Note that column goes first in the DGV
    }
}

You can perform both actions (row adding and cell population) in one go by using this.dataGridView1.Rows.Add("col1 val", "col2 val"); but, in order to do that, you would have to redo your two loops.