0

I've used one module to read excel files and fill the data from them into a DataTable. This DataTable gets then displayed into a DataGridView form.

With that I have no problems, but the thing is that I want to catch any changes made to that DataTable while it was displayed in the GridView and to save them.

In other words, I want to call a function each time something has been changed on the updated new DataTable (I have no problems, if this would require an additional button (e.g. "Save Changes") as well).

I've read about binding source and adapters, but I am quite confused and cannot get them to work properly. So any help would be appreciated, here's the code.

public partial class MainForm : Form
{

    DataTable dTable = new DataTable();
    BindingSource bSource = new BindingSource();

    public MainForm()
    {
        //
        // The InitializeComponent() call is required for Windows Forms designer support.
        //
        InitializeComponent();

        //
        // TODO: Add constructor code after the InitializeComponent() call.
        //
    }
    void Button1Click(object sender, EventArgs e)
    {
        OpenFileDialog openFileDialog1 = new OpenFileDialog();

        openFileDialog1.InitialDirectory = "d:\\" ;
        openFileDialog1.Filter = /*txt files (*.txt)|*.txt|xcel files (*.xcel*)|*.xcel*|*/ "All files (*.*)|*.*" ;
        openFileDialog1.FilterIndex = 1;
        openFileDialog1.RestoreDirectory = true ;
        openFileDialog1.Title = "Choose a file to read";

        if(openFileDialog1.ShowDialog() == DialogResult.OK)
        {
            string szChosenFileNameDirectory = openFileDialog1.FileName;
            string szChosenFileExtension = szChosenFileNameDirectory.Substring(szChosenFileNameDirectory.LastIndexOf("."), szChosenFileNameDirectory.Length - szChosenFileNameDirectory.LastIndexOf("."));

            SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

            ExcelFile ef = ExcelFile.Load(szChosenFileNameDirectory);

            foreach (ExcelWorksheet sheet in ef.Worksheets)
            {   
                foreach(ExcelColumn column in sheet.Columns)
                {
                    if(column.Index == sheet.CalculateMaxUsedColumns())
                        break;

                    DataColumn col = new DataColumn();
                    col.ColumnName = column.Name;
                    dTable.Columns.Add(col);

                }

                foreach (ExcelRow row in sheet.Rows)
                {
                    DataRow r = dTable.NewRow();
                    r.BeginEdit();
                    foreach (ExcelCell cell in row.AllocatedCells)
                    {
                        r[cell.Column.Name] = cell.Value;

                    }
                    r.EndEdit();
                    dTable.Rows.Add(r);
                }
            }

            //setting the datasource, ok
            dataGridView1.DataSource = dTable;

            //Handlig a changed row or?
            dTable.RowChanged += new DataRowChangeEventHandler(Row_Changed);
        }
    }

    public static void Row_Changed(object sender, DataRowChangeEventArgs e)
    {

    }
}

P.S.: You should have this :/

using GemBox.Spreadsheet;
Hugh Nolan
  • 2,508
  • 13
  • 15
D. Petrov
  • 1,147
  • 15
  • 27
  • 1
    I'm not sure what you mean by "I want to become the updated new DataTable" though, just to get this straight... You want to save changes that you make in the source that is the **DataTable** apply in the **dataGridView** while it's already displaying Rows? – Ahmed Aboumalek Apr 07 '16 at 10:42
  • This is a very common question. The row numbers of the DataGridView is the same as the DataTable. So in the Row_Changed event get the row number from the sender object. I don't have Visual Studio running at the moment so I can't give you exact code. You can look it up. – jdweng Apr 07 '16 at 10:54
  • @Angelo That's exactly what I mean. Because I create one variable from the class DataTable at the beginning and I want the changes to be kept in the same one. – D. Petrov Apr 07 '16 at 10:54
  • @jdweng I couldn't understand how exactly that would help me. Consider me a total newbie, but after I get the number (or the index, e.g. 'id'?) of the row, am I able to apply the changes made to it to the same data table? – D. Petrov Apr 07 '16 at 10:57
  • Yes to same DataTable. The dataGridView will not automatically update. So you have to set the DataSource to null like Angelo posted. Setting to null causes the form paint event to recognize the data changed and then will refresh the forms window. – jdweng Apr 07 '16 at 11:23

1 Answers1

1

There are several ways to do this and several topics that regards this issue.

A quick fix is rebinding your dataSource to the dataGridView via a method attached to a 'refresh' button.

Use the following method, you can assign it to a on_click button event that you must create beforehand:

function bool refresh()
{
    dataGridView1.DataSource = null;
    dataGridView1.DataSource = dTable;

    return true;
}

Alternatively, try looking around the forum, there are several similar topics that show different methods:

Community
  • 1
  • 1
Ahmed Aboumalek
  • 613
  • 4
  • 21
  • Oh gawd... Never knew that the DataTable is getting changed troughout tha period while it's displayed... So a simple refresh like that does the trick. Thanks a lot, I guess I've spent too much time on useless information then >. – D. Petrov Apr 07 '16 at 11:12
  • 1
    Pleasure. I should mention that this might not be the best way to do it. Simply because if you have a lot of records (and I mean a LOT), it will probably take time and consume more RAM to refresh the entire dataGridView. – Ahmed Aboumalek Apr 07 '16 at 11:22
  • It is only about a single file at the time, so there should be no problems. ^_^ Thanks a lot. – D. Petrov Apr 07 '16 at 12:17