Question

I have just started to learn C# and I m a little bit confused about DataSet/DataTable/DataGridView. For now I m having a simple form where there is DataGridView, listBox, Insert & Delete buttons. When my form loads in listBox I m getting excel sheet s list. Then I choose one of the sheets and then it appears on DataGridView (all information I m writing to DataSet and DataTable). I would like to add or delete columns at runtime. Here is my code for Delete button:

    excelConn = new OleDbConnection();
    excelConn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + excelFilePath + ";Extended Properties="Excel 8.0;HDR=YES;IMEX=1"";
    DataTable dt = new DataTable();
    OleDbDataAdapter ExcelAdapt = new OleDbDataAdapter("Select * From " + "[" + tableName + "]", excelConn);
    ExcelAdapt.Fill(dt);
    try
    {
      dt.Columns.Remove(colName);
      dt.AcceptChanges();
      var bds = new BindingSource();
      bds.DataSource = dt;
      grid.DataSource = bds;

      MessageBox.Show("Column(s) deleted ");
      excelConn.Close();
      }

      catch (Exception ex)
      {
         MessageBox.Show(ex.Message);
      }

我面临的问题是,我无法保存运行时甚至运行时所做的更改。这些更改是临时性的。如果运行时我添加了新的列, 然后从 ListBox 中选择另一个工作表, 这些更改就会消失。

我找到了这个教程 ,但如果我理解它会改变数据库;在我的情况下,它会改变极好的文件(但我希望有原始文件,只对 DataSet 和DataTable 作出修改)。

我只能更改 DataSet/DataTable 。如果有,如何更改?

Answer 1

这是解决你问题的精确方法, 它将数据GridView的数据更新到Excel, 我刚刚在我的机器上测试过, 它确实起作用了:

http://blogs.msdn.com/b/spike/archive/2008/10/29/how-to- update-an-excel-workshall-used-datastems-d-dataset-and-the-oledbddatadapter.aspx

<强>[编辑

例如,以下代码将从 DataGridView1 中删除行3, 以及您的 ds 的本地副本, 然而您的 EXCEL 将不会受到影响。

dataGridView1.Rows.RemoveAt(2);
ds.Tables["[Sheet1$]"].Rows[2].Delete();

稍后当您想要将此数据保存到其它 EXCEL 时, 您只需导出到新的 EXCEL 或相同的 EXCEL 不同 [SheetX] 。

友情链接