使用OleDbAdaptor和查询参数打开Excel表

时间:2021-09-26 20:56:42

This was my previous method for opening an excel sheet:

这是我之前打开excel表格的方法:

public static System.Data.DataTable GetEntireSheet(string fileName,
    string sheetName)
{
    string connectionString = GetConnectionString(fileName);
    System.Data.DataTable excelTable = new System.Data.DataTable();
    excelTable.Locale = CultureInfo.InvariantCulture;

    using (OleDbConnection connection =
        new OleDbConnection(connectionString))
    {
        using (OleDbDataAdapter adaptor = new OleDbDataAdapter(
            string.Format(CultureInfo.InvariantCulture,
                "Select * from [{0}$]", sheetName),
            connection))
        {
            adaptor.Fill(excelTable);
        }
    }

    return excelTable;
}

This works fine but it also generates code analysis warning:

这样做很好,但是它也会生成代码分析警告:

CA2100 The query string passed to 'OleDbDataAdapter.OleDbDataAdapter(string, OleDbConnection)' in 'ExcelWrapper.GetEntireSheet(string, string)' could contain the following variables 'string.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}$]", sheetName)'. If any of these variables could come from user input, consider using a stored procedure or a parameterized SQL query instead of building the query with string concatenations.

CA2100传递给“OleDbDataAdapter”的查询字符串。OleDbDataAdapter(字符串,OleDbConnection)”“ExcelWrapper。GetEntireSheet(string, string)'可以包含以下变量的string. format (CultureInfo)。“不变文化”,“选择*从[{0}$]”,sheetName)。如果这些变量中的任何一个可能来自用户输入,请考虑使用存储过程或参数化SQL查询,而不是用字符串连接构建查询。

The sheetname is not coming from user input, so I could suppress the warning, but suppressing warnings is a practice I try to avoid as much as possible.

sheetname不是来自用户输入,所以我可以抑制警告,但是我尽量避免压制警告。

I've tried to update the method with the following:

我尝试更新方法如下:

            string query = "SELECT * FROM [?]";
            string parameter = string.Format(
                CultureInfo.InvariantCulture, "{0}$", sheetName);
            using (OleDbCommand command =
                new OleDbCommand(query, connection))
            {
                command.Parameters.Add("?", OleDbType.BSTR).Value =
                    parameter;

                using (OleDbDataAdapter adaptor =
                    new OleDbDataAdapter(command))
                {
                    adaptor.Fill(excelTable);
                }
            }

It looks suspicious, though and it also generates an OleDbException of:

尽管它看起来可疑,但它也产生了一个被称为:

The Microsoft Access database engine could not find the object '?'. Make sure the object exists and that you spell its name and the path name correctly. If '?' is not a local object, check your network connection or contact the server administrator.

Microsoft Access数据库引擎找不到对象'?'。确保对象存在,并正确拼写它的名称和路径名。如果”?不是本地对象,请检查您的网络连接或联系服务器管理员。

What is the right way to call this with query parameters?

用查询参数调用该方法的正确方法是什么?

2 个解决方案

#1


1  

You cannot use a parameter to represent a table name (sheet name). You use parameters only when you want to pass VALUES for INSERT, UPDATE and in WHERE clause.

不能使用参数来表示表名(表名)。只有当您希望为INSERT、UPDATE和WHERE子句传递值时才使用参数。

However, as pointed out, by your code analysis tool you can use a white list of table names where your user can choose from without typing anything.

但是,正如所指出的,通过代码分析工具,您可以使用一个白色的表名列表,用户可以在其中进行选择,而无需输入任何内容。

You use GetOleDbSchemaTable from the OleDbConnection and fill a combo with a DropDownStyle of DropDownList

您可以使用来自OleDbConnection的GetOleDbSchemaTable,并使用DropDownList的DropDownStyle填充组合

using(OleDbConnection excel_con = new OleDbConnection(connectionString))
using(OleDbCommand cmd = new OleDbCommand())
{
    excel_con.Open();
    DataTable result = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    var names = result.AsEnumerable().Select(x => x.Field<string>("TABLE_NAME").TrimEnd('$')).ToList();
    comboBoxTables.DataSource = names;
}

Now your code could use the SelectedItem from the comboBoxTables and use the string concatenation approach without problems with Sql Injection.

现在,您的代码可以使用comboBoxTables的SelectedItem,并使用字符串连接方法,而不会遇到Sql注入的问题。

#2


0  

Use code given below, user explicit provider declaration.

使用下面给出的代码,用户显式提供程序声明。

public System.Data.DataTable ReadExcel(string fileName, string fileExt, strig SheetName)
    {
        string conn = string.Empty;
        System.Data.DataTable dtexcel = new System.Data.DataTable();
        if (fileExt.CompareTo(".xls") == 0)
            conn = @"provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HRD=Yes;IMEX=1';"; //for below excel 2007  
        else
            conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=YES';"; //for above excel 2007  
        using (OleDbConnection con = new OleDbConnection(conn))
        {
            try
            {
     String Query = String.Format("select * from [{0}]",SheetName);         
       // [sheet1] = [YouerSheetNameinExcelFile]
                //OleDbDataAdapter oleAdpt = new OleDbDataAdapter("select * from [sheet1]", con); //here we read data from sheet1  
                OleDbDataAdapter oleAdpt = new OleDbDataAdapter(Query, con); //here we read data from sheet1 and from specific cell range.   
                oleAdpt.Fill(dtexcel); //fill excel data into dataTable  
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); }
        }
        return dtexcel;
    }

#1


1  

You cannot use a parameter to represent a table name (sheet name). You use parameters only when you want to pass VALUES for INSERT, UPDATE and in WHERE clause.

不能使用参数来表示表名(表名)。只有当您希望为INSERT、UPDATE和WHERE子句传递值时才使用参数。

However, as pointed out, by your code analysis tool you can use a white list of table names where your user can choose from without typing anything.

但是,正如所指出的,通过代码分析工具,您可以使用一个白色的表名列表,用户可以在其中进行选择,而无需输入任何内容。

You use GetOleDbSchemaTable from the OleDbConnection and fill a combo with a DropDownStyle of DropDownList

您可以使用来自OleDbConnection的GetOleDbSchemaTable,并使用DropDownList的DropDownStyle填充组合

using(OleDbConnection excel_con = new OleDbConnection(connectionString))
using(OleDbCommand cmd = new OleDbCommand())
{
    excel_con.Open();
    DataTable result = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    var names = result.AsEnumerable().Select(x => x.Field<string>("TABLE_NAME").TrimEnd('$')).ToList();
    comboBoxTables.DataSource = names;
}

Now your code could use the SelectedItem from the comboBoxTables and use the string concatenation approach without problems with Sql Injection.

现在,您的代码可以使用comboBoxTables的SelectedItem,并使用字符串连接方法,而不会遇到Sql注入的问题。

#2


0  

Use code given below, user explicit provider declaration.

使用下面给出的代码,用户显式提供程序声明。

public System.Data.DataTable ReadExcel(string fileName, string fileExt, strig SheetName)
    {
        string conn = string.Empty;
        System.Data.DataTable dtexcel = new System.Data.DataTable();
        if (fileExt.CompareTo(".xls") == 0)
            conn = @"provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HRD=Yes;IMEX=1';"; //for below excel 2007  
        else
            conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=YES';"; //for above excel 2007  
        using (OleDbConnection con = new OleDbConnection(conn))
        {
            try
            {
     String Query = String.Format("select * from [{0}]",SheetName);         
       // [sheet1] = [YouerSheetNameinExcelFile]
                //OleDbDataAdapter oleAdpt = new OleDbDataAdapter("select * from [sheet1]", con); //here we read data from sheet1  
                OleDbDataAdapter oleAdpt = new OleDbDataAdapter(Query, con); //here we read data from sheet1 and from specific cell range.   
                oleAdpt.Fill(dtexcel); //fill excel data into dataTable  
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); }
        }
        return dtexcel;
    }