Excel.Workbook

官方说明:

从Excel工作簿返回工作表的记录。
Excel.Workbook( workbook as binary, optional useHeaders as nullable logical, optional delayTypes as nullable logical) as table

解读:

语法:

若导入的文件为*.csv或*.txt,请参照另一篇《Csv.Document》
第一参数为binary,通常是先使用File.Contents根据指定路径获取Excel文件,返回类型为binary,然后再用Excel.Workbook将binary解析出来,比如= Excel.Workbook(File.Contents("C:\Users\rages\Desktop\excel\test1.xlsx"), null, true)
第二参数为是否使用标题,类型为布尔值。true表示使用第一行作为标题,不填或null或false都表示不使用。
第三参数字面意思为延迟类型,同样为布尔值。据说在合并文件夹的时候填true效率更高,填false在每合并一次后会有延迟。但是经过本人测试填不填并没有什么区别,所以一般省略不填,如果大家测试后有发现区别欢迎反馈。

导入单文件:

如果是导入单个文件,我们一般点击数据-新建查询-从文件-从工作簿即可,能界面操作的也没必要去手写公式。但是系统自动生成的公式第二参数默认为null,第三参数为ture,然后会再加一个提升标题的步骤,看起来很不科学。第三参数又没什么用,我们完全可以把 ,null 删掉,让true变成第二参数使用标题,然后把"提升的标题"删掉。"导航"这个步骤是从表记录中深化出[Data]列中数据所在区域,如果只需要其中的一个sheet那可以直接使用[Data]{0}的形式深化出来,写成= Excel.Workbook(File.Contents("C:\Users\rages\Desktop\excel\test1.xlsx"),true){0}[Data]
如果要的是所有sheet的合并数据那么就使用Table.Combine进行合并,然后"导航"就可以删掉了,写成= Table.Combine(Excel.Workbook(File.Contents("C:\Users\rages\Desktop\excel\test1.xlsx"),true)[Data])
最终只是改了几个字符,就让原来三个步骤做的事现在一个步骤就能完成,看上去清爽很多。

合并文件夹:

我们使用这个函数最多的场景是合并文件夹下的所有excel文件,如果我们导入文件夹后直接点击合并-合并并编辑,会看到如下的效果:

一下多出这么多的查询和步骤,不知道你感觉如何,反正身为重度强迫症患者的我表示接受不了。那怎么办?
可能其他大多数教程都会教你添加一列=Excel.Workbook([Content]),然后展开再删除列。相比自动生成的公式已经简洁很多了,但是既然有强迫症,就必须把强迫症发挥到极致。
首先导入文件夹,不要点合并直接点编辑,看到的应该是如下界面:

思路同解析单个文件一样,只不过现在是要对[Content]字段下的多个binary同时转换,于是要用到List.Transform,最后再将所有表格合并起来。

= Table.Combine(List.Transform(Folder.Files("C:\Users\rages\Desktop\excel")[Content],each Table.Combine(Excel.Workbook(_,true)[Data])))
看到这你会发现,如果懂一点M语言,原本系统自动生成N个查询N个步骤实现的效果,现在也许一行代码就能搞定。

带文件名的合并文件夹:

有些时候可能需要保留文件名,比如说文件以日期命名而表中没有日期。这时候就不能用上面极简的写法了,只能老老实实的先用File.Contents获取路径下所有文件,然后把其他列删掉只保留[Name]和[Content],再添加列使用= Table.Combine(Excel.Workbook([Content],true)[Data]),最后再展开。

28 Replies to “Excel.Workbook”

  1. 若果从文件夹获取,里面有不同的工作簿1、2、3,同时每个工作簿中又有不同的工作表:甲 乙 丙,怎么将每个工作表合并,如1、2、3的甲 ;1、2、3的乙 ;1、2、3的丙

    1. = List.Transform({0..2},(x)=>Table.Combine(List.Transform(Folder.Files("C:\XXXXXX")[Content],each Excel.Workbook(_,true)[Data]{x})))

      1. 这个很赞,但返回的列表是table,如何分sheet导出呢? 我现在办法是手动一个个table的点右键,深化新的查询,但 感觉应该有其它办法

      2. 施阳老师,如果我只要合并一个工作薄其中的几个表呢,比如:A工作薄有,1-10个表,我只要合并其中3,4,5,8,9 这几个表,如何写表达示?谢谢老师。

        1. Table.Combine合并表之前再加一步筛选。
          从合理性的角度来说,要合并所有工作表就要保证所有表格式一样,不一样的就不应该放到同一个文件里,所以最佳的解决方案就是从数据源上避免这种伪需求的产生。
          如果无法避免,那么就要考虑,每张表的位置是固定的吗?会不会哪天2和3换个位置?工作表总数是固定的吗?如果再多一张表要不要合并进来?
          并且除了位置,需要合并的表是否有什么特征?比如sheet名?如果没有是否可以加上等等。
          所以不同的情况不同的思路都会有不同的解决方案,这并不是一个表达式的问题。

  2. 对查询的某一步骤单击右键可以引用 但是会生成新表,有没有办法跨表直接引用其他查询的某个步骤 而不生成新表?谢谢

  3. 施老师,您的这句代码会生成一个List,我想把每个table都加载到一个工作簿的工作表。该怎么办呢?
    我试了一下,只能加载一个table,而不能所有的table都一起上传并加载。

  4. 你好,请问某些xls文件,怎么通过pq导入,提示“不是预期的格式”。网上搜索的方法,好像都不太行。能帮忙看看嘛?
    链接:https://pan.baidu.com/s/1piuqilBjxF0JCBzakVR_-Q
    提取码:np2t

      1. 这个代码是预先使用js自动打开保存一遍下载的xls文件,再进行查询么?
        我现在的办法就是用宏批量打开、保存一遍,再按正常的来查询。不知道是不是一样?

  5. 施老师, 如果Folder路径或者需要建立connection的文件名是变化的,但有规律,比如这个月的叫xxxxxxx_Apr'19,下个月叫xxxxxx_May'19了。这种情况要怎么办呢?

    比如Folder.Files("C:\Users\rages\Desktop\excel")这种,如果这个路径被人改过名字,是不是这个Query就不好用了?

  6. 兄弟,你的博客被注入了。从这个页面前端源码里搜lobbydesires。
    参考https://guides.magefix.com/2020/07/drop-dontstopthismusics-com/
    应该是用了有问题的wordpress插件。

  7. 老师,使用File.Contents解析路径的时候一直出现错误提示 DataFormat.Error: 提供的文件路径必须为有效的绝对路径。
    详细信息: ‪C:\Users\u130785\Desktop\abc.XLSX
    这是什么原因呢,我想用类似这样的方式来在工作表中填入路径就可以随时更改数据来源 Excel.Workbook(File.Contents(Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="表4"]}[Content],{{"Inv addr", type text}})[Inv addr]{0}), null, true)
    但就是这个问题导致不能做到

  8. 你好,最近从xlsx导入query的源文件中,部分单元格数据长度超过了255,导致原数据被截取了前255位,请问有方法可以解决吗?

  9. = Excel.Workbook(File.Contents("C:\Users\rages\Desktop\excel\test1.xlsx"),true){0}[Data]

    这个公式中可以指定提取哪张工作表么?{0}有时会变动,是否可以指定表名?

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注