为了实现我的目标,SQL查询
SELECT lastname, firstname, phonenumber WHERE phonenumber IS NOT NULL ORDER BY lastname
会做到这一点。这似乎太简单了,以至于Excel不能本地支持它。我怎么能在Excel中运行这样的SQL查询?SELECT lastname, firstname, phonenumber WHERE phonenumber IS NOT NULL ORDER BY lastname
会做到这一点。这似乎太简单了,以至于Excel不能本地支持它。我怎么能在Excel中运行这样的SQL查询?有很多种方法可以完成这个任务,其他人已经提出了一些建议。沿着“通过SQL获取Excel数据”的轨迹,以下是一些要点。
Excel具有"数据连接向导",允许您从另一个数据源或甚至在同一个Excel文件中导入或链接。
作为Microsoft Office(和操作系统)的一部分,存在两个感兴趣的提供程序:旧的"Microsoft.Jet.OLEDB"和最新的"Microsoft.ACE.OLEDB"。在设置连接时(例如使用数据连接向导),请查找它们。
连接到Excel工作簿后,工作表或范围相当于表或视图。工作表的表名是将工作表名称附加了美元符号("$")并用方括号("["和"]")括起来;对于范围,则只是范围的名称。要将未命名的单元格范围指定为记录源,请将标准Excel行/列表示法附加到方括号中的工作表名称末尾。
本机SQL将是Microsoft Access的SQL。(过去称为JET SQL;但是Access SQL已经发展,我认为JET是不推荐使用的旧技术。)
例如,读取工作表:SELECT * FROM [Sheet1$]
例如,读取范围:SELECT * FROM MyRange
例如,读取未命名的单元格范围:SELECT * FROM [Sheet1$A1:B10]
有很多很多很多书籍和网站可帮助您解决具体问题。
HDR = setting
来完成的。默认值为HDR = Yes
,不需要指定。如果没有列标题,则需要指定HDR = No
;提供程序将命名您的字段F1、F2等。Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C:\MyFolder\MyWorkbook.xls; Extended Properties = Excel 8.0;
。对于Microsoft Excel 5.0和7.0(95)工作簿,请使用Excel 5.0源数据库类型,对于Microsoft Excel 8.0(97),9.0(2000)和10.0(2002)工作簿,请使用Excel 8.0源数据库类型。连接到“最新”的 Excel 文件(扩展名为 xlsx):Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Excel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;"
将数据视为文本:IMEX 设置将所有数据视为文本。 Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Excel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";
(更多详细信息请参见http://www.connectionstrings.com/excel)
更多信息,请参见http://msdn.microsoft.com/en-US/library/ms141683(v=sql.90).aspx和http://support.microsoft.com/kb/316934
通过 VBA 详细说明了使用 ADODB 连接到 Excel,详情请查看http://support.microsoft.com/kb/257819
Microsoft JET 4 的详细信息请参见http://support.microsoft.com/kb/275561
我建议你试试QueryStorm - 它是Excel的一个插件,使在Excel中使用SQL非常方便。
在SQL脚本中,Excel表格可以像普通的数据库表格一样显示。tl;dr; Excel原生支持这些功能 - 使用筛选器和/或表格
(http://office.microsoft.com/en-gb/excel-help/filter-data-in-an-excel-table-HA102840028.aspx)
您可以通过OleDb连接以编程方式打开Excel,并在工作表中的表上执行SQL。但是,您可以通过过滤器而不是公式完成您要求的所有操作。除非您要经常执行此操作或者您想自动导入数据到其他地方,否则请使用筛选器..但为了完整起见:
C# 选项:
OleDbConnection ExcelFile = new OleDbConnection( String.Format( "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=YES\"", filename));
ExcelFile.Open();
List<String> excelSheets = new List<string>();
// Add the sheet name to the string array.
foreach (DataRow row in dt.Rows) {
string temp = row["TABLE_NAME"].ToString();
if (temp[temp.Length - 1] == '$') {
excelSheets.Add(row["TABLE_NAME"].ToString());
}
}
然后当您想要查询表格时:
OleDbDataAdapter da = new OleDbDataAdapter("select * from [" + sheet + "]", ExcelFile);
dt = new DataTable();
da.Fill(dt);
注意 - 在Excel中使用表格!:
Excel具有“表格”功能,使数据更像一个表格.. 这给您带来了一些巨大的好处,但不会让您执行每种类型的查询。
http://office.microsoft.com/en-gb/excel-help/overview-of-excel-tables-HA010048546.aspx
对于Excel中的表格数据,这是我的默认方式。我首先点击数据,然后从功能区的主页部分选择“表格格式”。这会为您提供默认的筛选和排序,并允许您通过名称访问表格和字段(例如,table[fieldname])。这还允许对列进行聚合函数,例如最大值和平均值。使用 System.Data.OleDb;
使用 System.Data;
- user359135=C6=""
,C6是电话号码列的第一个数据单元格。如果您想以编程方式执行此操作,建议使用宏记录器记录上述步骤并查看代码。
这里被接受的答案是旧技术,不应再尝试使用。
当这个问题被写出时,Power Query还不是一个众所周知的选项,除非你使用最新版本的Office并将其作为单独的Add-in安装,否则无法使用。
现在,Power Query已经包含在Excel中,并默认用于获取数据。这是正确的方法。它简单、快速且有效。
以下是使用Power Query回答此问题的方法。如果需要帮助复制此操作,请搜索“Power Query入门”。一旦您开始使用Power Query,您会发现这是非常基本和易于操作的高级编辑器:
let
Source = Excel.CurrentWorkbook(){[Name="Names"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"lastname", type text}, {"firstname", type text}, {"phonenumber", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([phonenumber] <> null)),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"lastname", "firstname", "phonenumber"}),
#"Sorted Rows" = Table.Sort(#"Removed Other Columns",{{"lastname", Order.Ascending}})
in
#"Sorted Rows"
你可以在Excel中使用SQL语言,只是这个功能很难找到。
可以参考以下教程:http://smallbusiness.chron.com/use-sql-statements-ms-excel-41193.html
如果您只需要执行一次,请按照Charles的说明进行操作,但是如果您想要使筛选器具有动态性,则也可以使用Excel公式和辅助列来完成此操作。
假设您的数据位于名为DataSheet的工作表中,并从以下列的第2行开始:
在该工作表上,您需要两个辅助列。
=IF(A2="",1,0)
,这是筛选器列,对应您的where条件=IF(D2<>1,"",SUMIFS(D$2:D$1048576,A$2:A$1048576,"<"&A2)+SUMIFS(D$2:D2,A$2:A2,A2))
,这对应于order by将这些公式复制到数据所达到的范围。
在应显示结果的工作表上,创建以下列。
=MATCH(A2,DataSheet!$E$2:$E$1048576,0)
,这是相应数据的行=IFERROR(INDEX(DataSheet!A$2:A$1048576,$B2),"")
,这是实际数据或为空(如果没有数据存在)将B2和C2中的公式复制下来,并将列C复制到列D和E。
ogrinfo -dialect sqlite -sql "SELECT name, count(*) FROM sheet1 GROUP BY name" Book1.xlsx
将在sheet1
上运行一个SQLite查询,并以不寻常的形式输出查询结果:
INFO: Open of `Book1.xlsx'
using driver `XLSX' successful.
Layer name: SELECT
Geometry: None
Feature Count: 36
Layer SRS WKT:
(unknown)
name: String (0.0)
count(*): Integer (0.0)
OGRFeature(SELECT):0
name (String) = Red
count(*) (Integer) = 849
OGRFeature(SELECT):1
name (String) = Green
count(*) (Integer) = 265
...
或者使用ogr2ogr运行相同查询,生成一个简单的CSV文件:
$ ogr2ogr -f CSV out.csv -dialect sqlite \
-sql "SELECT name, count(*) FROM sheet1 GROUP BY name" Book1.xlsx
$ cat out.csv
name,count(*)
Red,849
Green,265
...
这可以通过使用SQLite和"XLite"扩展来实现:
sqlite3
.load libxlite
(在Linux和macOS上),或者.load xlite
(在Windows上)然后你可以创建一个虚拟表,并对其进行查询:
CREATE VIRTUAL TABLE test_data USING xlite (
FILENAME './tests/abcdef_colnames.xlsx', -- path to to your excel file
WORKSHEET 'Sheet1', -- sheet name
RANGE 'A2:F', -- optional range
COLNAMES '1' -- optional param for column names
);
SELECT A, B, C, D, E, F FROM test_data;
SELECT
COUNT(*),
D
FROM test_data
GROUP BY D
ORDER BY COUNT(*);