在Excel表上编写SQL查询 [英] Write SQL query on excel tables

问题描述

最近给我分配了编码任务，其中涉及我编写对以.xlsx格式(excel电子表格)表示的数据库的SQL查询.分配基本上涉及到我从前三个表中选择某些值，对这些值进行一些计算，然后在第四个表上打印答案.所有四个表均已创建，并且前三个表具有供我选择的输入值，而最后一个表具有以下标题，应在下面输入我的答案.

I was recently given a coding assignment which involves me writing a SQL query for a database presented in the .xlsx form (excel spreadsheet). The assignment basically involves me selecting certain values from the first three tables, doing some calculations on those values and then printing the answer on the fourth table. All four tables have been created, and the first three tables have input values for me to choose from, while the last table has headings below which I should put my answers in.

我可以知道该怎么做吗? (实际上，当目标是excel表时，在哪里可以写SELECT * FROM Table 1 WHERE Number = 1之类的SQL命令)

May I know how I can do that? (essentially, where can I write SQL commands like SELECT * FROM Table 1 WHERE Number = 1 when the target is an excel table)

推荐答案

我一直在对Excel文件运行查询-通常是xls，但是xlsx的代码基本相同:

I run queries against Excel files all the time - usually xls, but the code is basically the same for xlsx:

Sub ImportThisFile(FilePath As String, SourceSheet As String, Destination As Range)
    Set Conn = New ADODB.Connection
    Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
        FilePath & ";Extended Properties=Excel 8.0;"

    Sql = "SELECT * FROM [" & SourceSheet & "$]"

    Set RcdSet = New ADODB.Recordset
    RcdSet.Open Sql, Conn, adOpenForwardOnly

    Destination.CopyFromRecordset RcdSet

    RcdSet.Close
    Set RcdSet = Nothing

    Conn.Close
    Set Conn = Nothing
End Sub

这使用了ADODB对象的早期绑定，因此需要将"Microsoft ActiveX数据对象2.x库"添加到您的引用中(工具->引用...)

This uses early binding for the ADODB objects so "Microsoft ActiveX Data Objects 2.x Library" will need to be added to your references (Tools->References...)

根据connectionstrings.com，对于XLSX，提供程序应为Microsoft.Jet.OLEDB.12.0，扩展属性应为Excel 12.0 Xml

According to connectionstrings.com, for XLSX the Provider should be Microsoft.Jet.OLEDB.12.0 and the Extended Properties should be Excel 12.0 Xml

https://www.connectionstrings.com/excel/

如何从另一个子例程/函数调用此示例:

Example of how this would be called from another subroutine/function:

Sub StartDoingStuff()
    ImportThisFile "c:\Path\To\File.xls", "Sheet1", range("Sheet2!A2")
End Sub

这篇关于在Excel表上编写SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！