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

问题描述

我有 1 个带有 1 个文本框的表单，用户将在其中键入 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.

这里有两个问题:

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

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

从Excel绑定DataSource时，如何去除第二张图所示的空白记录?

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 窗体上工作过.

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 的单元格(这似乎是包含您的数据的范围.将其调整为需要):
请记住处置您创建的一次性对象或在 using 块中声明它们)

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* 列范围内.

更新:(针对这个问题):

鉴于 Excel Sheet 的组成，需要明确指定 Field Names，否则生成的 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屋！