如何基于Excel搜索结果填充DataGridView并从DataGridView中删除空白标题？ [英] How to populate a DataGridView based on Excel search results and remove blank headers from the DataGridView?

问题描述

我有1个带有1个TextBox的表单，用户将在其中键入 ProductId（FCID），按Enter键，然后在此基础上填充DataGridView

I have 1 Form with 1 TextBox where an user will type ProductId(FCID), press the enter key and, based on that, I want to populate my DataGridView with the results of a search.

这里有2个问题：

• 如何根据用户输入的 FCID 从Excel过滤记录？

• How do I filter records from Excel based on user entered FCID?

绑定数据源时从Excel中，如何删除第二张图片中所示的空白记录？

While binding the DataSource from Excel, how to remove blank records as shown in the second image?

代码：

private void txtProductId_KeyUp(object sender, KeyEventArgs e)
{
    if (e.KeyCode == Keys.Enter)
    {
        string pathName = txtFilePath.Text;
        string fileName = System.IO.Path.GetFileNameWithoutExtension(txtFilePath.Text);
        DataTable tbContainer = new DataTable();
        string strConn = string.Empty;
        string sheetName = fileName;

        FileInfo file = new FileInfo(pathName);
        if (!file.Exists) { throw new Exception("Error, file doesn't exists!"); }
        string extension = file.Extension;

        switch (extension)
        {
            case ".xls":
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
                break;
            case ".xlsx":
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
                break;
            default:
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
                break;
        }
        OleDbConnection cnnxls = new OleDbConnection(strConn);
        OleDbDataAdapter oda = new OleDbDataAdapter(string.Format("select * from [{0}$]", sheetName), cnnxls);
        oda.Fill(tbContainer);
        grdProductList.DataSource = tbContainer;
        e.Handled = true;
    }
}

我以前没有在Windows Forms上工作。

I have not worked previously on Windows Forms.

推荐答案

您可以指定包含要检索的数据的单元格范围。格式：

You can specify a Range of cells that contain the Data you want to retrieve. In the form:

[sheet1$[Start Cell]:[End Cell]]

根据您的情况，您可以更改查询，仅包括单元格从 B6 到 ZZ （这似乎是包含您数据的范围。请根据需要对其进行调整）：

请记住处理创建或声明的一次性对象

In your case, you can change the query, including only the Cells from B6 to ZZ (which seems to be the range that contains your Data. Adapt it as needed):
Remember to Dispose of the disposable objects you created or declare them in using blocks)

string sheetName = "Sheet1";
string query = $"SELECT * FROM [{sheetName}$B6:ZZ]"
OleDbDataAdapter oda = new OleDbDataAdapter(query, cnnxls); 
//(...)
oda.Dispose();

您当然可以指定过滤器：

You can of course specify a Filter:

int fieldID = 204;
//
string query = $"SELECT * FROM [{sheetName}$B6:ZZ] WHERE FCID = {fieldID}"

using (OleDbConnection cnnxls = new OleDbConnection(strConn))
using (OleDbDataAdapter oda = new OleDbDataAdapter(query, cnnxls))
{
    oda.Fill(tbContainer);
    grdProductList.DataSource = tbContainer;
}

或范围过滤器：

string query = $"SELECT * FROM [{sheetName}$B6:ZZ] WHERE FCID BETWEEN 204 AND 300"

或使用变量值：

int minValue = 204;
int maxValue = 300;
string query = $"SELECT * FROM [{sheetName}$B6:ZZ] WHERE FCID BETWEEN {minValue} AND {maxValue}"

.xls 格式不支持通用范围：

The .xls format doesn't support a generic Range:

[sheet1 $ B6：ZZ] 。

[ sheet1 $ B6：O65535] 被接受：数据在 B6：O * 列范围内。

更新 ：（特定于此问题）：

UPDATE: (specific to this question):

鉴于Excel工作表的组成，字段名称需要明确指定，否则生成的 DataTable 将包含空列：

Given the composition of the Excel Sheet, the Field Names need to be specified explicitly, otherwise the resulting DataTable will contain empty columns:

int fieldID = 204;    
string fieldSelector = "[ FCID], [Product Name], [Category], [Sub Category], " +
                       "[Brand], [MRP], [Disc %], [Stock], [Discount Type]";

query = $"SELECT {fieldSelector} FROM [{sheetName}$B6:O65535] WHERE [ FCID] = {fieldID}";

这篇关于如何基于Excel搜索结果填充DataGridView并从DataGridView中删除空白标题？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！