Dropbox 中的电子表格

Dropbox 是一种文件托管服务，提供用于编程文件访问的 API。

¥Dropbox is a file hosting service that offers APIs for programmatic file access.

SheetJS 是一个用于从电子表格读取和写入数据的 JavaScript 库。

¥SheetJS is a JavaScript library for reading and writing data from spreadsheets.

该演示使用 SheetJS 读取和写入存储在 Dropbox 上的电子表格。我们将探讨两个 Dropbox API 工作流程：

¥This demo uses SheetJS to read and write spreadsheets stored on Dropbox. We'll explore two Dropbox API workflows:

• "选择器"[^1] 应用允许用户从其 Dropbox 账户中选择文件。该演示将获取并解析所选文件。

  ¥A "Chooser"[^1] application allows users to select files from their Dropbox accounts. This demo will fetch and parse the selected file.

• "保存者"[^2] 应用允许用户将生成的电子表格保存到他们的 Dropbox 账户。此演示将使用 SheetJS 生成 XLS 工作簿。

  ¥A "Saver"[^2] application allows users to save a generated spreadsheet to their Dropbox account. This demo will generate a XLS workbook using SheetJS.

集成详情

¥Integration Details

"Dropbox 应用" 是与服务交互的标准方式。"Dropbox 应用" 部分描述了如何配置此演示。

¥"Dropbox Apps" are the standard way to interact with the service. The "Dropbox App" section describes how this demo was configured.

Dropbox API 脚本在此页面中加载

¥The Dropbox API script is loaded in this page with

<script type="text/javascript" src="https://www.dropbox.com/static/api/2/dropins.js" id="dropboxjs" data-app-key="v85yuk360zx6nyx"></script>

此演示中使用的 data-app-key 是与 localhost 和 docs.sheetjs.com 域关联的 "开发" 密钥。Dropbox API 不需要选择器或保护器的 "生产" 批准。

¥The data-app-key used in this demo is a "Development" key associated with the localhost and docs.sheetjs.com domains. Dropbox API does not require "Production" approval for the Chooser or Saver.

在线演示需要 Dropbox 账户。

¥The live demos require a Dropbox account.

读取文件

¥Reading Files

"选择器" 是一个小型库，允许用户从其账户中选择文件。Dropbox.createChooseButton 是一个接受选项参数并返回应添加到页面的 DOM 元素的函数：

¥"Chooser" is a small library that lets users select a file from their account. Dropbox.createChooseButton is a function that accepts an options argument and returns a DOM element that should be added to the page:

var button = Dropbox.createChooseButton({
  /* ... options described below ... */
});
document.appendChild(button);

必须设置以下选项：

¥The following options must be set:

• multiselect: false 确保只能选择一个文件

  ¥multiselect: false ensures only one file can be selected

• folderselect: false 限制选择真实文件

  ¥folderselect: false limits selection to real files

• linkType: "direct" 确保链接指向原始文件

  ¥linkType: "direct" ensures the link points to a raw file

• 当用户选择文件时调用 success 方法

  ¥success method is called when the user selects a file

以下选项是可选的：

¥The following options are optional:

• extensions: ['.xlsx', '.xls'] 限制选择的文件类型

  ¥extensions: ['.xlsx', '.xls'] limits the file types for selection

选择器回调

¥Chooser Callback

即使只选择了一个文件，success 回调方法也会接收一组 File 对象。该文件对象具有以下属性：

¥The success callback method receives an array of File objects even if only one file is selected. This file object has the following properties:

• name 是所选文件的名称

  ¥name is the name of the selected file

• link 是一个可以获取的临时 URL

  ¥link is a temporary URL that can be fetched

该演示使用 fetch API 获取链接，使用 SheetJS read 函数 [^3] 解析原始数据，并使用 sheet_to_html[^4] 生成 HTML 表格

¥This demo fetches the link using the fetch API, parses the raw data using the SheetJS read function[^3] and generates a HTML table using sheet_to_html[^4]

async(files) => {
  /* get file entry -- note that dropbox API always passes an array */
  var file = files[0];
  console.log(`Selected ${file.name} ID=${file.id}`);

  /* fetch file and parse */
  var wb = XLSX.read(await (await fetch(file.link)).arrayBuffer());

  /* convert first sheet to HTML table and add to page */
  var html = XLSX.utils.sheet_to_html(wb.Sheets[wb.SheetNames[0]]);
  console.log(html);
}

选择器在线演示

¥Chooser Live Demo

如果在线演示显示一条消息

¥If the live demo shows a message

ReferenceError: Dropbox is not defined

请刷新页面。这是文档生成器中的一个已知错误。

¥please refresh the page. This is a known bug in the documentation generator.

Result

Loading...

Live Editor

function SheetJSChoisissez() {
  const [msg, setMsg] = React.useState("Press the button to show a Chooser");
  const btn = useRef(), tbl = useRef();
  React.useEffect(() => {
    if(typeof Dropbox == "undefined") return setMsg("Dropbox is not defined");
    /* create button */
    var button = Dropbox.createChooseButton({
      /* required settings */
      multiselect: false,
      folderselect: false,
      linkType: "direct",
      /* optional settings */
      extensions: ['.xlsx', '.xls', '.numbers'], // list of extensions

      /* event handlers */
      cancel: () => setMsg("User Canceled Selection!"),
      success: async(files) => {
        /* get file entry -- note that dropbox API always passes an array */
        var file = files[0];
        setMsg(`Selected ${file.name} ID=${file.id}`);

        /* fetch file and parse */
        var wb = XLSX.read(await (await fetch(file.link)).arrayBuffer());

        /* convert first sheet to HTML table and add to page */
        tbl.current.innerHTML = XLSX.utils.sheet_to_html(wb.Sheets[wb.SheetNames[0]]);
      }
    });
    /* add button to page */
    btn.current.appendChild(button);
  }, []);
  return ( <><b>{msg}</b><br/><div ref={btn}/><div ref={tbl}/></> );
}

写入文件

¥Writing Files

"保存者" 是一个小型库，允许用户将文件保存到他们的账户中。Dropbox.createSaveButton 是一个接受三个参数并返回应添加到页面的 DOM 元素的函数：

¥"Saver" is a small library that lets users save files to their account. Dropbox.createSaveButton is a function that accepts three arguments and returns a DOM element that should be added to the page:

var button = Dropbox.createSaveButton(url, filename, options);
/* add button to page */
btn.current.appendChild(button);

filename 将是“保存”窗口中推荐的文件名。

¥filename will be the recommended filename in the Save window.

options 对象支持两种回调：success（如果保存成功）和 cancel（如果用户取消而不保存）。

¥The options object supports two callbacks: success (if the save succeeded) and cancel (if the user cancels without saving).

URL

Dropbox API 并不是为写入在网络浏览器中创建的文件而设计的。数据 URI 方法是一种巧妙的解决方法，但不应该在较大文件的生产中使用。最好使用 NodeJS 在服务器中创建文件并生成正确的 URL 供 Dropbox 获取。

¥The Dropbox API was not designed for writing files that are created in the web browser. The Data URI approach is a neat workaround but should not be used in production for larger files. It is better to create the files in the server using NodeJS and generate a proper URL for Dropbox to fetch.

Dropbox API 旨在从用户指定的 URL 获取数据。文件不包含在请求中！

¥The Dropbox API is designed to fetch data from a user-specified URL. Files are not included in the request!

SheetJS 解决方法涉及数据 URI 方案 [^5]。主要步骤是：

¥The SheetJS workaround involves the Data URI scheme[^5]. The main steps are:

1. 使用 SheetJS write[^6] 方法编写工作簿。type: "base64" 选项指示该方法返回 Base64 编码的字符串。

   ¥Write a workbook using the SheetJS write[^6] method. The type: "base64" option instructs the method to return a Base64-encoded string.

/* write XLS workbook (Base64 string) */
const b64 = XLSX.write(workbook, { type: "base64", bookType: "xls" });

2. 通过添加 data 标头来构建数据 URL：

   ¥Construct a Data URL by prepending the data header:

/* create data URI */
const url = "data:application/vnd.ms-excel;base64," + b64;

3. 创建一个按钮并将其添加到页面：

   ¥Create a button and add it to the page:

/* create save button using the concise function call */
var button = Dropbox.createSaveButton( url, "SheetJSDropbox.xls", {
  success: () => setMsg("File saved successfully!"),
  cancel: () => setMsg("User Canceled Selection!"),
});
document.appendChild(button);

必须在创建“保存”按钮之前写入该文件。

¥The file must be written before the Save button is created.

Saver 在线演示

¥Saver Live Demo

此演示以数据数组数组开始。页面加载时，数据将导出到 XLSX 并生成数据 URI。单击按钮时，数据 URI 将发送到 Dropbox，服务将尝试将数据保存到 Dropbox 账户中的 SheetJSDropbox.xls。

¥This demo starts with an array of arrays of data. When the page loads, the data is exported to XLSX and a data URI is generated. When the button is clicked, the data URI is sent to Dropbox and the service will attempt to save the data to SheetJSDropbox.xls in your Dropbox account.

如果在线演示显示一条消息

¥If the live demo shows a message

ReferenceError: Dropbox is not defined

请刷新页面。这是文档生成器中的一个已知错误。

¥please refresh the page. This is a known bug in the documentation generator.

Result

Loading...

Live Editor

function SheetJSEnregistrez() {
  const [msg, setMsg] = React.useState("Press the button to write XLS file");
  const btn = useRef(), tbl = useRef();
  React.useEffect(() => { (async() => {
    if(typeof Dropbox == "undefined") return setMsg("Dropbox is not defined");
    /* fetch data and write table (sample data) */
    const f = await(await fetch("https://xlsx.nodejs.cn/pres.xlsx")).arrayBuffer();
    const wb = XLSX.read(f);
    tbl.current.innerHTML = XLSX.utils.sheet_to_html(wb.Sheets[wb.SheetNames[0]]);

    /* create workbook from table */
    const table = tbl.current.getElementsByTagName("TABLE")[0];
    const new_wb = XLSX.utils.table_to_book(table);

    /* write XLS workbook (Base64 string) */
    const b64 = XLSX.write(new_wb, { type: "base64", bookType: "xls" });

    /* create data URI */
    const url = "data:application/vnd.ms-excel;base64," + b64;

    /* create save button using the concise function call */
    var button = Dropbox.createSaveButton( url, "SheetJSDropbox.xls", {
      success: () => setMsg("File saved successfully!"),
      cancel: () => setMsg("User Canceled Selection!"),
    });
    /* add button to page */
    btn.current.appendChild(button);
  })(); }, []);
  return ( <><b>{msg}</b><br/><div ref={btn}/><div ref={tbl}/></> );
}

Dropbox 应用

¥Dropbox App

测试部署

此演示最后一次测试是在 2024 年 5 月 27 日。

¥This demo was last tested on 2024 May 27.

该演示需要 "Dropbox 应用"：

¥This demo requires a "Dropbox app":

0. 创建 Dropbox 账户并验证关联的电子邮件地址。此演示已使用免费的 Dropbox Basic 计划账户进行了测试。可以安全地跳过应用安装步骤。

   ¥Create a Dropbox account and verify the associated email address. This demo has been tested with a free Dropbox Basic plan account. The app installation step can be safely skipped.

1. 在开发者面板中创建 Dropbox 应用。

   ¥Create a Dropbox app in the Developer panel.

单击 ᎒᎒᎒ 图标 > 应用中心。在下一页中，单击左侧边栏中的 "构建一个应用"。在下一页中，单击 "创建应用"。

¥Click the ᎒᎒᎒ icon > App Center. In the next page, click "Build an App" in the left sidebar. In the next page, click "Create apps".

在应用创建向导中，选择以下选项：

¥In the App creation wizard, select the following options:

• "选择一个 API"："范围访问"

  ¥"Choose an API": "Scoped access"

• "选择你需要的访问类型"："完整的 Dropbox"

  ¥"Choose the type of access you need": "Full Dropbox"

• "名称"：（输入任意名称）

  ¥"Name": (enter any name)

接受之前应先审查 Dropbox API 条款和条件。

¥The Dropbox API Terms and Conditions should be reviewed before acceptance.

单击 "创建应用" 创建应用。

¥Click "Create App" to create the app.

2. 在开发者工具中配置 Dropbox 应用。

   ¥Configure the Dropbox app in the Developer tools.

应在 "权限" 选项卡中选择以下权限

¥The following permissions should be selected in the "Permissions" tab

• files.metadata.write（查看和编辑有关 Dropbox 文件和文件夹的信息）

  ¥files.metadata.write (View and edit information about your Dropbox files and folders)

• files.metadata.read（查看有关你的 Dropbox 文件和文件夹的信息）

  ¥files.metadata.read (View information about your Dropbox files and folders)

• files.content.write（编辑 Dropbox 文件和文件夹的内容）

  ¥files.content.write (Edit content of your Dropbox files and folders)

• files.content.read（查看 Dropbox 文件和文件夹的内容）

  ¥files.content.read (View content of your Dropbox files and folders)

选择权限后，单击 "提交"。

¥After selecting the permissions, click "Submit".

在“设置”选项卡中的 "选择器/保存器/嵌入器域" 下，应添加所需的公共域。还必须添加 localhost 以供开发使用（它不会自动启用）。

¥In the Settings tab, under "Chooser / Saver / Embedder domains", the desired public domains should be added. localhost must also be added for development use (it is not automatically enabled).

对于公共使用，请选择 "启用其他用户"。

¥For public use, select "Enable Additional Users".

3. 复制 "应用密钥" 并将其添加到 Dropbox 集成脚本引用的 data-app-key 属性中。

   ¥Copy the "App key" and add it to the data-app-key attribute of the Dropbox integration script reference.

[^1]: 请参阅 Dropbox 开发者文档中的 "选择器"

¥See "Chooser" in the Dropbox Developers Documentation

[^2]: 请参阅 Dropbox 开发者文档中的 "保存者"

¥See "Saver" in the Dropbox Developers Documentation

[^3]: 见 read 于 "读取文件"

¥See read in "Reading Files"

[^4]: 见 sheet_to_html 于 "实用工具"

¥See sheet_to_html in "Utilities"

[^5]: 参见 "MDN 网络文档" 中的 "数据 URL"

¥See "Data URLs" in the "MDN web docs"

[^6]: 见 writeFile 于 "写入文件"

¥See writeFile in "Writing Files"