⌘+k ctrl+k
1.3 (稳定版)
搜索快捷键 cmd + k | ctrl + k
Excel 扩展

`excel` 扩展通过封装 i18npool 库，提供了根据 Excel 格式规则设置数字格式的功能，但自 DuckDB 1.2 起，它也提供了读取和写入 Excel（ `.xlsx`）文件的功能。然而，不支持 `.xls` 文件。

以前，Excel 文件的读写是通过 `spatial` 扩展处理的，该扩展恰好通过其一个依赖项包含了对 XLSX 文件的支持，但此功能未来可能会从 spatial 扩展中移除。此外，`excel` 扩展效率更高，并提供了对导入/导出过程的更多控制。有关说明，请参阅 Excel 导入和 Excel 导出页面。

安装和加载

`excel` 扩展将在首次使用时从官方扩展仓库中透明地自动加载。如果您想手动安装和加载它，请运行

INSTALL excel;
LOAD excel;

Excel 标量函数

函数 描述
excel_text(number, format_string) 根据 `format_string` 中给定的规则格式化指定的 `number`。
text(number, format_string) `excel_text` 的别名

示例 

SELECT excel_text(1_234_567.897, 'h:mm AM/PM') AS timestamp;
时间戳
晚上 9:31 
SELECT excel_text(1_234_567.897, 'h AM/PM') AS timestamp;
时间戳
晚上 9 点

读取 XLSX 文件

读取 `.xlsx` 文件就像直接从它 `SELECT` 一样简单，例如：

SELECT *
FROM 'test.xlsx';
a b
1.0 2.0
3.0 4.0

但是，如果您想设置额外选项来控制导入过程，可以使用 `read_xlsx` 函数代替。支持以下命名参数：

选项 类型 默认值 描述
header 布尔值 自动推断 是否将第一行视为包含结果列的名称。
sheet VARCHAR 自动推断 要读取的 xlsx 文件中的工作表名称。默认是第一张工作表。
all_varchar 布尔值 false 是否将所有单元格读取为 `VARCHAR`。
ignore_errors 布尔值 false 是否忽略错误，并静默地将无法转换为相应推断列类型的单元格替换为 `NULL` 值。
range VARCHAR 自动推断 要读取的单元格范围，使用电子表格表示法。例如，`A1:B2` 读取从 A1 到 B2 的单元格。如果未指定，结果范围将被推断为连续非空单元格的第一行与跨越相同列的第一个空行之间的矩形单元格区域。
stop_at_empty 布尔值 自动推断 遇到空行时是否停止读取文件。如果提供了显式的 `range` 选项，则默认值为 `false`，否则为 `true`。
empty_as_varchar 布尔值 false 在尝试自动推断列类型时，是否将空单元格视为 `VARCHAR` 而不是 `DOUBLE`。 
SELECT *
FROM read_xlsx('test.xlsx', header = true);
a b
1.0 2.0
3.0 4.0

或者，可以使用带有 `XLSX` 格式选项的 `COPY` 语句将 Excel 文件导入现有表，在这种情况下，目标表中列的类型将用于强制转换 Excel 文件中单元格的类型。

CREATE TABLE test (a DOUBLE, b DOUBLE);
COPY test FROM 'test.xlsx' WITH (FORMAT xlsx, HEADER);
SELECT * FROM test;

类型和范围推断

因为 Excel 本身只在单元格中存储数字或字符串，并且不强制要求一列中的所有单元格都具有相同的类型，所以 `excel` 扩展在导入 Excel 工作表时必须进行一些猜测来“推断”和决定列的类型。虽然几乎所有列都被推断为 `DOUBLE` 或 `VARCHAR`，但也有一些注意事项：

  • `TIMESTAMP`、`TIME`、`DATE` 和 `BOOLEAN` 类型在可能的情况下会根据应用于单元格的格式进行推断。
  • 包含 `TRUE` 和 `FALSE` 的文本单元格被推断为 `BOOLEAN`。
  • 默认情况下，空单元格被视为 `DOUBLE`，除非 `empty_as_varchar` 选项设置为 `true`，在这种情况下，它们被类型化为 `VARCHAR`。

如果 `all_varchar` 选项设置为 `true`，则上述所有规则都不适用，所有单元格都将读取为 `VARCHAR`。

当未显式指定类型时（例如，使用 `read_xlsx` 函数而不是 `COPY TO ... FROM 'file.xlsx' 时），结果列的类型将根据工作表中的第一个“数据”行进行推断，即：

  • 如果未给出显式范围
    • 如果找到标题或通过 `header` 选项强制使用标题，则为标题后的第一行
    • 如果未找到或未强制使用标题，则为工作表中的第一个非空行
  • 如果给出了显式范围
    • 如果第一行中找到标题或通过 `header` 选项强制使用标题，则为该范围的第二行
    • 如果未找到或未强制使用标题，则为该范围的第一行

如果第一个“数据行”不能代表工作表的其余部分（例如，它包含空单元格），这有时会导致问题，在这种情况下，可以使用 `ignore_errors` 或 `empty_as_varchar` 选项来解决此问题。

然而，当使用 `COPY TO ... FROM '⟨file⟩.xlsx'` 语法时，不进行类型推断，结果列的类型由要复制到的表中的列类型决定。所有单元格将通过从 `DOUBLE` 或 `VARCHAR` 转换为目标列类型来简单地进行转换。

写入 XLSX 文件

使用 `COPY` 语句并指定 `XLSX` 作为格式来写入 `.xlsx` 文件。支持以下附加参数：

选项 类型 默认值 描述
header 布尔值 false 是否将列名作为工作表的第一行写入
sheet VARCHAR Sheet1 要写入的 xlsx 文件中的工作表名称。
sheet_row_limit 整数 1048576 工作表中的最大行数。如果超出此限制，将抛出错误。

警告：许多工具仅支持工作表中最多 1,048,576 行，因此增加 `sheet_row_limit` 可能会导致生成的文件无法被其他软件读取。

这些参数作为选项在 `FORMAT` 之后传递给 `COPY` 语句，例如：

CREATE TABLE test AS
    SELECT *
    FROM (VALUES (1, 2), (3, 4)) AS t(a, b);
COPY test TO 'test.xlsx' WITH (FORMAT xlsx, HEADER true);

类型转换

由于 XLSX 文件实际只支持存储数字或字符串（等同于 `VARCHAR` 和 `DOUBLE`），因此在写入 XLSX 文件时会应用以下类型转换：

  • 数字类型在写入 XLSX 文件时会被强制转换为 `DOUBLE`。
  • 时间类型（`TIMESTAMP`、`DATE`、`TIME` 等）被转换为 Excel“序列”号，即日期为自 1900-01-01 以来的天数，时间为一天的分数。然后使用“数字格式”对其进行样式化，以便在 Excel 中打开时它们显示为日期或时间。
  • `TIMESTAMP_TZ` 和 `TIME_TZ` 分别被强制转换为 UTC `TIMESTAMP` 和 `TIME`，时区信息会丢失。
  • `BOOLEAN` 值被转换为 `1` 和 `0`，并应用“数字格式”使其在 Excel 中显示为 `TRUE` 和 `FALSE`。
  • 所有其他类型都被强制转换为 `VARCHAR`，然后作为文本单元格写入。
关于此页面