C#, have DataGridView behave like excel?

[elkinm]

I have a basic array of integers, 10x5 (10 rows) and I have a DataGridView displaying those values.

I go through the array and output the values to the appropriate DataGridView cell, I don't know how to link the DataGrid to the array, but that is not important.

I want to be able to change values in the first two columns of the DataGridView and therefore the array, but not the last 3.
The last 3 should do real-time calculations on the values in the previous columns and be effectively locked so I cannot change them.

Lest say column 0 has integers from 1-10 and column 1 is all 1s. Column 2 should be the sum of the previous 2 values in column 0 times the value in column 1. (obviously for row 0 there is not previous row so it only takes the values from row 0.

Then column 4 does calculations based on values in columns 0, 1 and 2, and so on.

This is easy to do in excel with simple formulas. Then I lock the formula cells and change the first 2 columns at will.

I my calculation I go through the array to do my calculation which is fine, but I don't know how to take data from the gridview that I enter into the array, calculate and display the results in the gridview.

This should all be done in real time, and if at all possible I would like the application to still keep running while I am editing (unlike Excel).

Also, I think excel knows which cells were changed and only recalculated what it needs, I co though the entire array even if I don't need to, is there a way to optimize my calculation. The data I am using is small, 10x10 max but I still would like to optimize it.

Also, as another link to excel, I need know when the values of the last 3 columns change as other parts of the code use that value. I can use events, however, I am not sure how to send an event for each cell. Would I need each cell have it's own event change generator which means I need to have at least 30 checks on every update? Anybody know how excel does it, I can't believe it was efficient for someone to write 2^16*256 events, and that is only for pre 2008 versions.

I know this is quite a few questions, please answer or help anywhere you can.
I an nutshell, I am trying to clone a small excel sheet efficiently and use the result from code.

Thanks, elkinm.

 

[beginner99]

put your data in a datatable. Then it will be much easier to work with the grid IMHO, meaning changes are automatically refelcetd from grid to datatable and vice versa.

google for expression columns. syntax is something like:

myDatatable.Columns["myColumns"].Expression = "ColumnA * ColumnB"

As far as i remember you can put in some rather complicated stuff in your expression, eg also conditions (=iif).

 

[KIAman]

Yeah, definitely put your data into datatable first, that way the binding becomes extremely simple.

Then you will become best friends with gridview events especially rowdatabound, rowupdate, rowedit.

That will take care of the "real time" effects. As to the actual calculations, I would do that on the back end and referencing the gridview contents is slightly different depending on if this is a windows app or aspx.

 

[elkinm]

Thanks, I am very new to GUI and external data programming so I have never used data tables like this or GridViews.

DataTables seem very similar to generic arrays, but from examples I see them creating named rows and/or columns. Can I use regular index based references. After some searching I found how to do it in a DataGridview: DataGridView.Rows.Cells[j].Value = ? where j is the column index. All datagridview examples I found either used binding or called rows or columns by name, this way I just use the index values.
Will the same work for DataTables?

I will try adding a DataTable to my code and see how it goes, maybe even post some of my code.

And you are suggesting to use datagrid events to run when grid data changes.
About the background calculation, in Excel, when editing a formula, excel is frozen. What I would prefer is if it continues to run using the old formula until a new and valid formula is entered, will datagridevents work in a similar way?

Thanks again, I will see where I can get.

 

[Crusty]

WPF or WinForms?

 

[elkinm]

Sorry, I am using Visual Studio 2008 so I think WinForms.

 

[elkinm]

Here is some of my original code:

public Form1()
        {
            InitializeComponent();
            Data = new int[5, 5];
            dataGridView1.RowCount = 5;
            dataGridView1.ColumnCount = 5;
            ResetGrid();
        }
private void ResetGrid()
        {
            for (int i = 0; i < 5; i++)
            {
                for (int j = 0; j < 5; j++)
                {
                    Data[i, j] = 0;
                }
            }
            UpdateGrid();
        }
private void UpdateGrid()
        {
            for (int i = 0; i < 5; i++)
            {
                for (int j = 0; j < 5; j++)
                {
                    dataGridView1.Rows[i].Cells[j].Value = Data[i, j];
                }
            }
        }
private void button1_Click(object sender, EventArgs e)
{
for (int i = 0; i < 5; i++)
{
if (i == 0)
                {
                    Data[i, 0] = 1 + Data[i, 0];
                    Data[i, 1] = 1 + Data[i, 1];
                    Data[i, 2] = Data[i, 0] + Data[i, 1];
                    Data[i, 3] = Data[i, 2] * (Data[i, 0] + Data[i, 1]);
                    Data[i, 4] = Data[i, 3] * (Data[i, 0] + Data[i, 1] + Data[i, 2]);
                    sum2 = Data[i, 2];
                    sum3 = Data[i, 3];
                    sum4 = Data[i, 4];
                }
                else
                {
                    Data[i,0] = Data[i - 1,0] + 1;
                    Data[i, 1] = Data[i,0] + Data[i - 1,1];
                    Data[i, 2] = Data[i,0] + Data[i,1];
                    Data[i, 3] = Data[i, 2] * (Data[i,0] + Data[i,1]);
                    Data[i, 4] = Data[i, 3] * (Data[i, 0] + Data[i,1] + Data[i,2]);
                    sum2 += Data[i, 2];
                    sum3 += Data[i, 3];
                    sum4 += Data[i, 4];
                }
}
UpdateGrid()
}

This made an array put values in the array and then placed those values into the DataGrid.

For the data table I started with:

static DataTable GetTable()
        {
            DataTable table = new DataTable();
            table.Columns.Add("0", typeof(int));
            table.Columns.Add("1", typeof(int));
            table.Columns.Add("2", typeof(int));
            table.Columns.Add("3", typeof(int));
            table.Columns.Add("4", typeof(int));

            table.Rows.Add(0, 0, 0, 0, 0);
            table.Rows.Add(0, 0, 0, 0, 0);
            table.Rows.Add(0, 0, 0, 0, 0);
            table.Rows.Add(0, 0, 0, 0, 0);
            table.Rows.Add(0, 0, 0, 0, 0);

            return table;
        }

if took me forever to find how to change one cell in the table until I found:
table.Rows[0][(0).ToString()] = 1; for table.Rows[0]["0"] = 1;

Then my button code became:

for (int i = 0; i < 5; i++)
            {
                if (i == 0)
                {
                    table.Rows[i][(0).ToString()] = 1 + (int)table.Rows[i][(0).ToString()];
                    table.Rows[i][(1).ToString()] = 1 + (int)table.Rows[i][(1).ToString()];
                    table.Rows[i][(2).ToString()] = (int)table.Rows[i][(0).ToString()] + (int)table.Rows[i][(1).ToString()];
                    table.Rows[i][(3).ToString()] = (int)table.Rows[i][(2).ToString()] * ((int)table.Rows[i][(0).ToString()] + (int)table.Rows[i][(1).ToString()]);
                    table.Rows[i][(4).ToString()] = (int)table.Rows[i][(3).ToString()] * ((int)table.Rows[i][(0).ToString()] + (int)table.Rows[i][(1).ToString()] + (int)table.Rows[i][(2).ToString()]);
                    sum2 = (int)table.Rows[i][(2).ToString()];
                    sum3 = (int)table.Rows[i][(3).ToString()];
                    sum4 = (int)table.Rows[i][(4).ToString()];
                }
                else
                {
                    table.Rows[i][(0).ToString()] = (int)table.Rows[i - 1][(0).ToString()] + 1;
                    table.Rows[i][(1).ToString()] = (int)table.Rows[i][(0).ToString()] + (int)table.Rows[i - 1][(1).ToString()];
                    table.Rows[i][(2).ToString()] = (int)table.Rows[i][(0).ToString()] + (int)table.Rows[i][(1).ToString()];
                    table.Rows[i][(3).ToString()] = (int)table.Rows[i][(2).ToString()] * ((int)table.Rows[i][(0).ToString()] + (int)table.Rows[i][(1).ToString()]);
                    table.Rows[i][(4).ToString()] = (int)table.Rows[i][(3).ToString()] * ((int)table.Rows[i][(0).ToString()] + (int)table.Rows[i][(1).ToString()] + (int)table.Rows[i][(2).ToString()]);
                    sum2 += (int)table.Rows[i][(2).ToString()];
                    sum3 += (int)table.Rows[i][(3).ToString()];
                    sum4 += (int)table.Rows[i][(4).ToString()];
                }
            }

my initialization is:

public Form1()
        {
            InitializeComponent();
            table = GetTable();
            dataGridView1.DataSource = table;
        }

So with the table, I don't need to update the grid so that is good, however, I think that referencing the columns with a string has to be slower than an index.

I am trying to find expressions that I can use for this code, but so far no luck.

Can you create an expression for the other rows so all I need to do is change the first and/or second column values and the rest will self recalculate.

Also, the data and calculations are arbitrary. I just wanted something for an example. In actuality, the first 2 columns are the input values, either user updated or program updated and then I do the calculations and when a result value changes the new value is sent elsewhere in the code to handle the change, like excel cell update events.

Thanks again.

 

[Cogman]

Here is some of my original code:

public Form1()
        {
            InitializeComponent();
            Data = new int[5, 5];
            dataGridView1.RowCount = 5;
            dataGridView1.ColumnCount = 5;
            ResetGrid();
        }
private void ResetGrid()
        {
            for (int i = 0; i < 5; i++)
            {
                for (int j = 0; j < 5; j++)
                {
                    Data[i, j] = 0;
                }
            }
            UpdateGrid();
        }
private void UpdateGrid()
        {
            for (int i = 0; i < 5; i++)
            {
                for (int j = 0; j < 5; j++)
                {
                    dataGridView1.Rows[i].Cells[j].Value = Data[i, j];
                }
            }
        }
private void button1_Click(object sender, EventArgs e)
{
for (int i = 0; i < 5; i++)
{
if (i == 0)
                {
                    Data[i, 0] = 1 + Data[i, 0];
                    Data[i, 1] = 1 + Data[i, 1];
                    Data[i, 2] = Data[i, 0] + Data[i, 1];
                    Data[i, 3] = Data[i, 2] * (Data[i, 0] + Data[i, 1]);
                    Data[i, 4] = Data[i, 3] * (Data[i, 0] + Data[i, 1] + Data[i, 2]);
                    sum2 = Data[i, 2];
                    sum3 = Data[i, 3];
                    sum4 = Data[i, 4];
                }
                else
                {
                    Data[i,0] = Data[i - 1,0] + 1;
                    Data[i, 1] = Data[i,0] + Data[i - 1,1];
                    Data[i, 2] = Data[i,0] + Data[i,1];
                    Data[i, 3] = Data[i, 2] * (Data[i,0] + Data[i,1]);
                    Data[i, 4] = Data[i, 3] * (Data[i, 0] + Data[i,1] + Data[i,2]);
                    sum2 += Data[i, 2];
                    sum3 += Data[i, 3];
                    sum4 += Data[i, 4];
                }
}
UpdateGrid()
}

This made an array put values in the array and then placed those values into the DataGrid.

For the data table I started with:

static DataTable GetTable()
        {
            DataTable table = new DataTable();
            table.Columns.Add("0", typeof(int));
            table.Columns.Add("1", typeof(int));
            table.Columns.Add("2", typeof(int));
            table.Columns.Add("3", typeof(int));
            table.Columns.Add("4", typeof(int));

            table.Rows.Add(0, 0, 0, 0, 0);
            table.Rows.Add(0, 0, 0, 0, 0);
            table.Rows.Add(0, 0, 0, 0, 0);
            table.Rows.Add(0, 0, 0, 0, 0);
            table.Rows.Add(0, 0, 0, 0, 0);

            return table;
        }

if took me forever to find how to change one cell in the table until I found:
table.Rows[0][(0).ToString()] = 1; for table.Rows[0]["0"] = 1;

Then my button code became:

for (int i = 0; i < 5; i++)
            {
                if (i == 0)
                {
                    table.Rows[i][(0).ToString()] = 1 + (int)table.Rows[i][(0).ToString()];
                    table.Rows[i][(1).ToString()] = 1 + (int)table.Rows[i][(1).ToString()];
                    table.Rows[i][(2).ToString()] = (int)table.Rows[i][(0).ToString()] + (int)table.Rows[i][(1).ToString()];
                    table.Rows[i][(3).ToString()] = (int)table.Rows[i][(2).ToString()] * ((int)table.Rows[i][(0).ToString()] + (int)table.Rows[i][(1).ToString()]);
                    table.Rows[i][(4).ToString()] = (int)table.Rows[i][(3).ToString()] * ((int)table.Rows[i][(0).ToString()] + (int)table.Rows[i][(1).ToString()] + (int)table.Rows[i][(2).ToString()]);
                    sum2 = (int)table.Rows[i][(2).ToString()];
                    sum3 = (int)table.Rows[i][(3).ToString()];
                    sum4 = (int)table.Rows[i][(4).ToString()];
                }
                else
                {
                    table.Rows[i][(0).ToString()] = (int)table.Rows[i - 1][(0).ToString()] + 1;
                    table.Rows[i][(1).ToString()] = (int)table.Rows[i][(0).ToString()] + (int)table.Rows[i - 1][(1).ToString()];
                    table.Rows[i][(2).ToString()] = (int)table.Rows[i][(0).ToString()] + (int)table.Rows[i][(1).ToString()];
                    table.Rows[i][(3).ToString()] = (int)table.Rows[i][(2).ToString()] * ((int)table.Rows[i][(0).ToString()] + (int)table.Rows[i][(1).ToString()]);
                    table.Rows[i][(4).ToString()] = (int)table.Rows[i][(3).ToString()] * ((int)table.Rows[i][(0).ToString()] + (int)table.Rows[i][(1).ToString()] + (int)table.Rows[i][(2).ToString()]);
                    sum2 += (int)table.Rows[i][(2).ToString()];
                    sum3 += (int)table.Rows[i][(3).ToString()];
                    sum4 += (int)table.Rows[i][(4).ToString()];
                }
            }

my initialization is:

public Form1()
        {
            InitializeComponent();
            table = GetTable();
            dataGridView1.DataSource = table;
        }

So with the table, I don't need to update the grid so that is good, however, I think that referencing the columns with a string has to be slower than an index.

I am trying to find expressions that I can use for this code, but so far no luck.

Can you create an expression for the other rows so all I need to do is change the first and/or second column values and the rest will self recalculate.

Also, the data and calculations are arbitrary. I just wanted something for an example. In actuality, the first 2 columns are the input values, either user updated or program updated and then I do the calculations and when a result value changes the new value is sent elsewhere in the code to handle the change, like excel cell update events.

Thanks again.

Please, for the love of all that is holy, use code tags! http://forums.anandtech.com/showthread.php?t=2069847

Just a sec. I'm going to look at the code taged version... Your call is important.

*edit*

Ok, so your basic question is "Can I make this do things by itself." The answer is a solid "It depends, but is not likely" Generally, the method to do something like this is to create a sort of callback function that, when the box changes, all other boxes "linked" to it will change.

As for the updating of the table, Make sure you do all changes to the data first before committing it to the table. It is almost always faster to load a grid with an array of data than it is to change one cell at a time.

 

[elkinm]

Sorry, added code tags, I knew it could be done I just did not know how.

As far as the code, I just need to replicate Excel. I have an Excel spreadsheet doing this basic table. And I just need to know when the results change.

From what I understand, expressions in DataTables allow cells to calculate internally without me having to the do the calculation loop I posted earlier.

Some of the entry inputs can be entered as array programmatically as they would be part of the program, but some, like the first column, needs to be user entered. These values can be copied from excel or somewhere else, but they need to be updated individually.

Also, the way the input data comes in, changes would come in one or 2 at a time so an entire array may be wasting updates. What if some values in the array stay the same?

When manually entering values, I need to see the calculations, but I don't want them sent to the rest of the program yet, so I think I would need to duplicate the data-table, in possibly a new control, look at it while I am editing it and when all is complete, apply it and then the new input data is placed in the primary DataTable.

And change events would be primarily on the data table, not the grid view to send to the rest of the code.

If I would do it the slow and crude way, I would use my regular array. At a push of a button I output another window with a DataGridView (I also don't know how to make separate windows yet, anyway) and a button and a table. I update values in the grid, hopefully I see the calculation. Then I click the button, and the button event handler looks through the DataGrid or table and copies the input data to the main array, noting if a value changed and calculating values, if a value changed, it triggers an array for that cell, which again means I need an event trigger for each cell.

I am hoping that with Tables and Grids and build in events most of this can be done for me quickly and efficiently, just like in Excel.

Thanks again.

 

[beginner99]

omg, just lost a long post because of some forum issue. not motivated to repeat...

table.Rows[i][(0).ToString()]

is IMHO not very nice because it means nothing else than:

table.Rows[0]["0"] = 1;

In your case you could also use:

table.Rows[0][0] = 1;

For the column you can use either the index [0] or column name. Since you named your first column "0", 0 and "0" should work.

For "calculated columns" see

http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression(v=VS.80).aspx


Are the expressions constant? I did not quite understand. or must the user be able to enter an expression (excel like formula) himself?

 

[elkinm]

I used the string because I though that I needed to reference the by column name. I don't like converting to string and having the string compared as it is slow. I will try with just integers.

The expressions are constant, like locked excel formulas and don't need to be changed at run-time. The expressions are also repetitive or have a pattern so I can fill the formulas up and down in Excel if that helps.

I will look through the document and see if it helps.

As far as the events, I have an array pointing to all the object that need the result data from the data table.
The organization is that Array[0,0] gets the value from Table[0][2], Array[0,1] gets Table[0][3] and Array[1,0] gets Table[1][2] so they are matched just transposed. It seems simple, but I need an efficient way to know if a value changed and is valid and send it on.

Thanks again.