如何从excel电子表格中选择数据并绑定到datagrid

本文介绍了如何从excel电子表格中选择数据并绑定到datagrid的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在开发一种工具，基本上允许用户从他们的计算机中选择一个excel文件，在提交到数据库之前在数据网格中预览它。目前这是打开文件的代码看起来像

private void btn_browse_file_Click（ object sender，EventArgs e） { OpenFileDialog openFileDialog1 = new OpenFileDialog（）; openFileDialog1.CheckFileExists = true ; openFileDialog1.AddExtension = true ; openFileDialog1.Multiselect = false ; openFileDialog1.Filter = Excel文件（* .xls）| * .xlsx; openFileDialog1.Title = 选择电子表格文件; if （openFileDialog1.ShowDialog（）== DialogResult.OK） { openFileDialog1。打开文件（）; // MessageBox.Show（openFileDialog1.FileName.ToString（））; label_display_file_name.Text = openFileDialog1.FileName.ToString（）; } }

这是用于显示excel文件的代码在datagridview中

private void btn_preview_Click（object sender，EventArgs e） { OleDbConnection ConnectToExcel = new OleDbConnection（）; DataSet excelds = new DataSet（）; OleDbDataAdapter excelda = new OleDbDataAdapter（）; OleDbCommand excelcmd = new OleDbCommand（）; ConnectToExcel = new OleDbConnection（String.Format（@Provider = Microsoft.ACE.OLEDB.12.0; Data Source = {0}; Extended Properties =Excel 12.0; HDR = YES; IMEX = 1; ，openFileDialog1.FileName.ToString（）））; excelcmd = new OleDbCommand（String.Format（SELECT * FROM [{0} $]，ConnectToExcel，openFileDialog1.FileName））; excelda = new OleDbDataAdapter（excelcmd）; excelda.Fill（excelds）; dataGridView1.DataSource = excelds.Tables [0]; }

由于某些原因我还不知道，在datagrid上显示excel文件的代码给出了以下错误Fill：SelectCommand.Connection属性尚未初始化。 非常感谢任何有关如何完成这项工作的帮助或想法。

解决方案

， ConnectToExcel，openFileDialog1.FileName））; excelda = new OleDbDataAdapter（excelcmd）; excelda.Fill（excelds）; dataGridView1.DataSource = excelds.Tables [0]; }

由于某些原因我还不知道，显示的代码datagrid上的excel文件给出了以下错误Fill：SelectCommand.Connection属性尚未初始化。 有关如何使用此工作的任何帮助或想法非常感谢。

此代码失败，因为您错误地使用了String.Format语法。这反过来导致Command没有分配连接。将代码更改为如下所示：

excelcmd = new OleDbCommand（String.Format（SELECT * FROM [{0}]

， openFileDialog1.FileName），ConnectToExcel）;

I am working on a tool that basically allow users select an excel file from their computer, preview it in a datagrid before submitting to the database. Currently this is what the code that opens the file looks like

private void btn_browse_file_Click(object sender, EventArgs e) { OpenFileDialog openFileDialog1 = new OpenFileDialog(); openFileDialog1.CheckFileExists = true; openFileDialog1.AddExtension = true; openFileDialog1.Multiselect = false; openFileDialog1.Filter = "Excel files (*.xls) | *.xlsx"; openFileDialog1.Title = "Select a spreadsheet file"; if (openFileDialog1.ShowDialog() == DialogResult.OK) { openFileDialog1.OpenFile(); // MessageBox.Show(openFileDialog1.FileName.ToString()); label_display_file_name.Text = openFileDialog1.FileName.ToString(); } }

and this is the code that is meant to display the excel file in the datagridview

private void btn_preview_Click(object sender, EventArgs e) { OleDbConnection ConnectToExcel = new OleDbConnection(); DataSet excelds = new DataSet(); OleDbDataAdapter excelda = new OleDbDataAdapter(); OleDbCommand excelcmd = new OleDbCommand(); ConnectToExcel = new OleDbConnection (String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;HDR=YES;IMEX=1;""", openFileDialog1.FileName.ToString())); excelcmd = new OleDbCommand (String.Format("SELECT * FROM [{0}$]", ConnectToExcel, openFileDialog1.FileName)); excelda = new OleDbDataAdapter(excelcmd); excelda.Fill(excelds); dataGridView1.DataSource = excelds.Tables[0]; }

For some reasons I don't yet know, the code to display the excel file on the datagrid gives me the following error "Fill:SelectCommand.Connection property has not been initialized". Any help or ideas on how to get this work will be much appreciated.

解决方案

", ConnectToExcel, openFileDialog1.FileName)); excelda = new OleDbDataAdapter(excelcmd); excelda.Fill(excelds); dataGridView1.DataSource = excelds.Tables[0]; }

For some reasons I don't yet know, the code to display the excel file on the datagrid gives me the following error "Fill:SelectCommand.Connection property has not been initialized". Any help or ideas on how to get this work will be much appreciated.

This code fails because you have used the String.Format syntax incorrectly. Which in return is causing the Command to not have a connection assigned to it. Change your code to this below:

excelcmd = new OleDbCommand(String.Format("SELECT * FROM [{0}]

", openFileDialog1.FileName), ConnectToExcel);