如何基于更多参数从Excel工作表中选择数据 [英] How to select data from excel sheet based on more parameters

问题描述

我有Excel工作表2007 xlsx有UserID，UserName，国家/地区

I have excel sheet 2007 xlsx have UserID,UserName,Country

我需要根据用户名值文本框1从excel工作表中选择数据

I need to select data from excel sheet Based on Username  value textbox1

国家(地区)值是combobox1

and country  value is combobox1

表示我需要从excel工作表中写选择*，其中用户名如textbox1，国家/地区为combobox1

meaning i need to write select * from excel sheet where username like textbox1 and country as combobox1

如果他仅选择国家/地区，则可以获得结果

if he select country only can get result

如果他仅使用"@"这样写用户名就可以得到结果

if he write user name only using like  can get result

如果我同时输入用户名和国家/地区都可以得到结果

if i make both username and country can get result

以便如何进行查询

我要做什么

 var fileName = string.Format("{0}\\Book502", Directory.GetCurrentDirectory());
            var connection = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source={0};Mode=ReadWrite;Extended Properties=Excel 12.0 Xml;", fileName);
            System.Data.OleDb.OleDbConnection MyConnection;
            System.Data.DataSet DtSet;
            System.Data.OleDb.OleDbDataAdapter MyCommand;
            MyConnection = new System.Data.OleDb.OleDbConnection(connection);
            MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [MemberEN$]", MyConnection);
            MyCommand.TableMappings.Add("Table", "Net-informations.com");
            DtSet = new System.Data.DataSet();
            MyCommand.Fill(DtSet);
            dataGridView1.DataSource = DtSet.Tables[0];
            if (DtSet.Tables[0].Rows.Count == 0)
            {
                dataGridView1.Visible = false;
            }
            else
            {
                dataGridView1.Visible = true;
            }
            MyConnection.Close();

如何修改上面的功能以接受按国家​​或用户名进行的动态搜索

How to modify function above to accept dynamic search by country or UserName

推荐答案

您好，

这与在数据库中创建表的方式相同，只是表的第一行具有字段名称，而第一行中没有字段名称.

It's the same way one does a table in a database except id the sheet's first row has field names vs no field names in the first row.

在此示例中，cboCountries是ComBoBox，而tbUserName是TextBox.

For this example, cboCountries is the ComBoBox and tbUserName is the TextBox.

此处WHERE条件中的每个字段都假设第一行具有字段名称.

Here each field in the WHERE condition assumes the first row has field names.

var selectStatement =

"从[MemberEN

"select * from [MemberEN

WHERE UserName ='{tbUserName.Text}'中选择* AND Country ='{cbCountries.Text}'';;

WHERE UserName='{tbUserName.Text}' AND Country= '{cbCountries.Text}'";

此版本的第一行中没有字段名称. F1代表名字的第一列，而F4代表国家的第四列

This version there are no field names in the first row. F1 represents the first column for FirstName while F4 is the fourth column representing country

var selectStatement =

这篇关于如何基于更多参数从Excel工作表中选择数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！