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.

user2480047 · Accepted Answer · 2013-10-23 20:14:53Z

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.

answered Oct 23, 2013 at 20:14

user2480047

Sign up to request clarification or add additional context in comments.

3 Comments

user2405778 Over a year ago

If I am understanding you correctly, the new for loops I would add would be the same as the current ones? But instead of populating the array and then adding it to the DGV in the same loop, they would be split up? If thats the case, wouldnt I get the same outcome?

user2480047 Over a year ago

@user2405778 I have let your loops as in your code and adapt the DataGridView population to your requirements. Replace your two nested loops with my two bits of code (first loop to create rows + two nested loops populating cells). What this code will do is replicating in the DGV the same structure than in Excel (same values per column/row), this is what you want, isn't it?

user2405778 Over a year ago

Ah, okay I didnt notice you changed the code in the second nested for loop. This works exactly how I wanted it, thank you very much for your help!

Collectives™ on Stack Overflow

Putting array values into DataGridView

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related