导入EXCEL导入的DataGridView

问题描述：

我正在做，其中的两个数据库合并到一起的程序....我可以导入Excel电子表格与此代码一个DataGridView：导入EXCEL导入的DataGridView

 string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test.xls;Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"""; 

       DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb"); 

       DbDataAdapter adapter = factory.CreateDataAdapter(); 

       DbCommand selectCommand = factory.CreateCommand(); 
       selectCommand.CommandText = "SELECT * FROM [All Carpets to Excel$]"; 

       DbConnection connection = factory.CreateConnection(); 
       connection.ConnectionString = connectionString; 

       selectCommand.Connection = connection; 

       adapter.SelectCommand = selectCommand; 

       data = new DataSet(); 

       adapter.Fill(data); 

       dataGridView1.DataSource = data.Tables[0].DefaultView;

我遇到的问题是我试图找到一种方法将源文件更改为由对话框返回的路径。我有一个包含文件路径的字符串文件。我如何将它并入连接字符串？

或者也许有更好的方法来做到这一点？

谢谢！

卢克

只需更换数据源= C：\ TEST.XLS”与对话框中的值 – 2012-07-05 17:26:41

@TimLentine该文件位于距对话框值可视改变，我需要这种改变发生 – 2012-07-05 17:41:10

用“连接字符串”中的对话框路径将路径更改为字符串名称会引发错误 – 2012-07-05 17:48:59

答

使用OleDbConnectionStringBuilder类来修改你的连接字符串。

string fileName = "your path to the excel.xls"; // From the dialog box. 

OleDbConnectionStringBuilder connStringBuilder = 
    new OleDbConnectionStringBuilder(); 

connStringBuilder.DataSource = fileName; // Set path to excel file 
connStringBuilder.Provider = "Microsoft.Jet.OLEDB.4.0"; 
connStringBuilder.Add("Extended Properties", "Excel 8.0;HDR=YES;IMEX1");   

... 

// Get the connection string from the builder. 
connection.ConnectionString = connStringBuilder.ConnectionString;

完美！谢谢你！工作起来就像一个魅力一样。 – 2012-07-05 18:13:00

答

您只需要Microsoft.Office.Interop.Excel并将其与数据表和数据集结合使用。

Excel.Workbook ExWorkbook; 
    Excel.Worksheet ExWorksheet; 
    Excel.Range ExRange; 
    Excel.Application ExObj = new Excel.Application(); 

    DataTable dt = new DataTable("dataTable"); 
    DataSet dsSource = new DataSet("dataSet"); 
    dt.Reset(); 

    openFileDialog1.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm"; 
    DialogResult result = openFileDialog1.ShowDialog(); 

    if (result == DialogResult.OK) // Test result. 
    { 
     ExWorkbook = ExObj.Workbooks.Open(openFileDialog1.FileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); 
     ExWorksheet = (Excel.Worksheet)ExWorkbook.Sheets.get_Item(1); 
     ExRange = ExWorksheet.UsedRange; 

     for (int Cnum = 1; Cnum <= ExRange.Columns.Count; Cnum++) 
     { 
      dt.Columns.Add(new DataColumn((ExRange.Cells[1, Cnum] as Excel.Range).Value2.ToString())); 
     } 
     dt.AcceptChanges(); 

     string[] columnNames = new String[dt.Columns.Count]; 
     for (int i = 0; i < dt.Columns.Count; i++) 
     { 
      columnNames[0] = dt.Columns[i].ColumnName; 
     } 

     for (int Rnum = 2; Rnum <= ExRange.Rows.Count; Rnum++) 
     { 
      DataRow dr = dt.NewRow(); 
      for (int Cnum = 1; Cnum <= ExRange.Columns.Count; Cnum++) 
      { 
       if ((ExRange.Cells[Rnum, Cnum] as Excel.Range).Value2 != null) 
       { 
        dr[Cnum - 1] = (ExRange.Cells[Rnum, Cnum] as Excel.Range).Value2.ToString(); 
       } 
      } 
      dt.Rows.Add(dr); 
      dt.AcceptChanges(); 
     } 
     ExWorkbook.Close(true, Missing.Value, Missing.Value); 
     ExObj.Quit(); 

     dataGridView1.DataSource = dt;

导入EXCEL导入的DataGridView

相关推荐