使用 CapacitorJS 存储工作表

CapacitorJS 是一个用于构建 iOS 和 Android 应用的移动应用运行时。

¥CapacitorJS is a mobile app runtime for building iOS and Android apps.

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

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

此演示使用 CapacitorJS 和 SheetJS 处理数据和导出电子表格。我们将探讨如何在 CapacitorJS 应用中加载 SheetJS 并使用 API 和插件从设备上的电子表格文件中提取数据并将数据写入其中。

¥This demo uses CapacitorJS and SheetJS to process data and export spreadsheets. We'll explore how to load SheetJS in an CapacitorJS app and use APIs and plugins to extract data from, and write data to, spreadsheet files on the device.

"演示" 创建一个应用，如下图所示：

¥The "Demo" creates an app that looks like the screenshots below:

iOS	Android

测试部署

本 demo 在以下环境下进行了测试：

¥This demo was tested in the following environments:

真实设备

¥Real Devices

OS	设备	CapacitorJS + FS	日期
安卓 30	英伟达盾	6.0.0/6.0.0	2024-06-02
iOS 15.1	iPad Pro	6.0.0/6.0.0	2024-06-02

模拟器

¥Simulators

OS	设备	CapacitorJS + FS	开发平台	日期
安卓 34	像素 3a	6.0.0/6.0.0	darwin-x64	2024-06-02
iOS 17.5	iPhone 15 Pro 最大	6.0.0/6.0.0	darwin-x64	2024-06-02
安卓 34	像素 3a	6.0.0/6.0.0	darwin-arm	2024-06-02
iOS 17.5	iPhone 15 Pro 最大	6.0.0/6.0.0	darwin-arm	2024-06-02
安卓 34	像素 3a	6.0.0/6.0.0	win10-x64	2024-05-28

遥测

在开始此演示之前，请手动禁用遥测。在 Linux 和 MacOS 上：

¥Before starting this demo, manually disable telemetry. On Linux and MacOS:

npx @capacitor/cli telemetry off

要验证遥测是否已禁用：

¥To verify telemetry was disabled:

npx @capacitor/cli telemetry

集成详情

¥Integration Details

SheetJS NodeJS 模块 可以从应用中的任何组件或脚本导入。

¥The SheetJS NodeJS Module can be imported from any component or script in the app.

此演示使用 SvelteJS，但相同的原则适用于其他框架。

¥This demo uses SvelteJS, but the same principles apply to other frameworks.

读取数据

¥Reading data

标准 HTML5 文件输入 API 在 CapacitorJS 中按预期工作。

¥The standard HTML5 File Input API works as expected in CapacitorJS.

应用通常会包含一个 input type="file" 元素。激活元素后，CapacitorJS 将显示文件选择器。用户选择文件后，元素将收到 change 事件。

¥Apps will typically include an input type="file" element. When the element is activated, CapacitorJS will show a file picker. After the user selects a file, the element will receive a change event.

以下示例使用 SheetJS read[^1] 方法解析所选文件，使用 sheet_to_html[^2] 从第一个工作表生成 HTML 表格，并通过设置 div 元素的 innerHTML 属性显示表格：

¥The following example parses the selected file using the SheetJS read[^1] method, generates a HTML table from the first sheet using sheet_to_html[^2], and displays the table by setting the innerHTML attribute of a div element:

Sample component for data import

<script>
import { read, utils } from 'xlsx';

let html = "";

/* show file picker, read file, load table */
async function importFile(evt) {
  const f = evt.target.files[0];
  const wb = read(await f.arrayBuffer());
  const ws = wb.Sheets[wb.SheetNames[0]]; // get the first worksheet
  html = utils.sheet_to_html(ws); // generate HTML and update state
}
</script>

<main>
  <input type="file" on:change={importFile}/>
  <div bind:this={tbl}>{@html html}</div>
</main>

写入数据

¥Writing data

从 SheetJS 工作簿对象 [^3] 开始，带有选项 type: "base64"[^4] 的 write 方法将生成 Base64 编码的文件。

¥Starting from a SheetJS workbook object[^3], the write method with the option type: "base64"[^4] will generate Base64-encoded files.

@capacitor/filesystem 插件可以将 Base64 字符串写入设备。

¥The @capacitor/filesystem plugin can write Base64 strings to the device.

以下示例使用 SheetJS table_to_book 方法 [^5] 从 HTML 表格创建工作簿对象。工作簿对象导出为 XLSX 格式并写入设备。

¥The following example uses the SheetJS table_to_book method[^5] to create a workbook object from a HTML table. The workbook object is exported to the XLSX format and written to the device.

Sample component for data export

<script>
import { Filesystem, Directory } from '@capacitor/filesystem';
import { utils, write } from 'xlsx';

let html = "";
let tbl;

/* get state data and export to XLSX */
async function exportFile() {
  /* generate workbook object from HTML table */
  const elt = tbl.getElementsByTagName("TABLE")[0];
  const wb = utils.table_to_book(elt);

  /* export to XLSX encoded in a Base64 string  */
  const data = write(wb, { bookType: "xlsx", type: "base64" });

  /* attempt to write to the device */
  await Filesystem.writeFile({
    data,
    path: "SheetJSCap.xlsx",
    directory: Directory.Documents
  });
}

</script>

<main>
  <button on:click={exportFile}>Export XLSX</button>
  <div bind:this={tbl}>{@html html}</div>
</main>

Filesystem.writeFile 无法覆盖现有文件。生产应用应尝试在写入之前删除文件：

¥Filesystem.writeFile cannot overwrite existing files. Production apps should attempt to delete the file before writing:

  /* attempt to delete file first */
  try {
    await Filesystem.deleteFile({
      path: "SheetJSCap.xlsx",
      directory: Directory.Documents
    });
  } catch(e) {}
  /* attempt to write to the device */
  await Filesystem.writeFile({
    data,
    path: "SheetJSCap.xlsx",
    directory: Directory.Documents
  });

演示

¥Demo

此演示中的应用将在表格中显示数据。

¥The app in this demo will display data in a table.

启动应用时，将获取并处理 测试文件。

¥When the app is launched, a test file will be fetched and processed.

当使用文件选择器选择文档时，它将被处理并且表格将刷新以显示内容。

¥When a document is selected with the file picker, it will be processed and the table will refresh to show the contents.

"导出 XLSX" 将尝试将表数据导出到应用 Documents 文件夹中的 SheetJSCap.xlsx。警报将显示文件的位置。

¥"Export XLSX" will attempt to export the table data to SheetJSCap.xlsx in the app Documents folder. An alert will display the location of the file.

基础项目

¥Base Project

0. 按照官方 "环境设置"[^6] 说明设置 Android 和 iOS 目标

   ¥Follow the official "Environment Setup"[^6] instructions to set up Android and iOS targets

iOS 开发仅支持 macOS。

¥iOS development is only supported on macOS.

Installation Notes (click to show)

CapacitorJS requires Java 17.

1. 禁用遥测。

   ¥Disable telemetry.

npx @capacitor/cli telemetry off

通过运行验证遥测是否已禁用

¥Verify that telemetry is disabled by running

npx @capacitor/cli telemetry

（它应该打印 Telemetry is off）

¥(it should print Telemetry is off)

2. 创建一个新的 Svelte 项目：

   ¥Create a new Svelte project:

npm create vite@latest sheetjs-cap -- --template svelte
cd sheetjs-cap

3. 安装依赖：

   ¥Install dependencies:

npm i --save https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz
npm i --save @capacitor/core @capacitor/cli @capacitor/filesystem

4. 创建 CapacitorJS 结构：

   ¥Create CapacitorJS structure:

npx cap init sheetjs-cap com.sheetjs.cap --web-dir=dist
npm run build

如果提示创建 Ionic 账户，请输入 N 并按 Enter。

¥If prompted to create an Ionic account, type N and press Enter.

5. 下载 src/App.svelte 并替换：

   ¥Download src/App.svelte and replace:

curl -o src/App.svelte -L https://xlsx.nodejs.cn/cap/App.svelte

安卓

¥Android

6. 创建 Android 应用

   ¥Create Android app

npm i --save @capacitor/android
npx cap add android

7. 在 Android 应用中启用文件读写。

   ¥Enable file reading and writing in the Android app.

将高亮的行添加到 Permissions 注释后的 android/app/src/main/AndroidManifest.xml：

¥Add the highlighted lines to android/app/src/main/AndroidManifest.xml after the Permissions comment:

android/app/src/main/AndroidManifest.xml (add to file)

    <!-- Permissions -->

    <uses-permission android:name="android.permission.READ_EXTERNAL_STORAGE"/>
    <uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE"/>

    <uses-permission android:name="android.permission.INTERNET" />

8. 在模拟器中运行应用：

   ¥Run the app in the simulator:

npm run build
npx cap sync
npx cap run android

该应用应类似于页面顶部的屏幕截图。

¥The app should look like the screenshot at the top of the page.

9. 测试导出功能。

   ¥Test the export functionality.

触摸 "导出 XLSX"，模拟器将请求权限。点击 "允许"，将显示一个带有路径的弹出窗口。

¥Touch "Export XLSX" and the emulator will ask for permission. Tap "Allow" and a popup will be displayed with a path.

在模拟器中打开 "文件" 应用，点击 ≡ 图标，然后点击 "文件"。点击 "文件" 文件夹以找到 SheetJSCap.xlsx。

¥Open the "Files" app in the simulator, tap the ≡ icon and tap "Documents". Tap the "Documents" folder to find SheetJSCap.xlsx.

Downloading the generated file (click to hide)

adb 必须从 root 用户运行：

¥adb must be run from the root user:

adb root

可以通过搜索 SheetJSCap.xlsx 找到文件位置：

¥The file location can be found by searching for SheetJSCap.xlsx:

adb exec-out find / -name SheetJSCap.xlsx

以 / 开头的输出的第一行是所需路径：

¥The first line of the output starting with / is the desired path:

find: /proc/8533/task/8533/exe: No such file or directory
find: /proc/8533/exe: No such file or directory
/data/media/0/Documents/SheetJSCap.xlsx
/storage/emulated/0/Documents/SheetJSCap.xlsx

adb pull 可以下载文件：

¥adb pull can download the file:

adb pull "/data/media/0/Documents/SheetJSCap.xlsx" SheetJSCap.xlsx

可以使用 Excel 等电子表格编辑器打开 SheetJSCap.xlsx。

¥SheetJSCap.xlsx can be opened with a spreadsheet editor such as Excel.

10. 测试导入功能。

    ¥Test the import functionality.

创建一个电子表格或查找现有文件。单击并将文件拖入 Android 模拟器窗口。文件将上传到模拟器中的下载文件夹。

¥Create a spreadsheet or find an existing file. Click and drag the file into the Android emulator window. The file will be uploaded to a Downloads folder in the emulator.

点击应用中的 "选择文件"。在选择器中，点击 ≡ 并选择 "下载" 以查找上传的文件。选择文件后，表格将刷新。

¥Tap on "Choose File" in the app. In the selector, tap ≡ and select "Downloads" to find the uploaded file. After selecting the file, the table will refresh.

iOS

11. 创建 iOS 应用

    ¥Create iOS app

npm i --save @capacitor/ios
npx cap add ios

12. 启用文件共享并使文档文件夹在 iOS 应用中可见。必须将以下行添加到 ios/App/App/Info.plist：

    ¥Enable file sharing and make the documents folder visible in the iOS app. The following lines must be added to ios/App/App/Info.plist:

ios/App/App/Info.plist (add to file)

<plist version="1.0">
<dict>
  <key>UIFileSharingEnabled</key>
  <true/>
  <key>LSSupportsOpeningDocumentsInPlace</key>
  <true/>
  <key>CFBundleDevelopmentRegion</key>

（文档的根元素是 plist，它包含一个 dict 子元素）

¥(The root element of the document is plist and it contains one dict child)

13. 在模拟器中运行应用

    ¥Run the app in the simulator

npm run build
npx cap sync
npx cap run ios

如果提示选择目标设备，请选择 "iPhone 15 Pro Max（模拟器）"。

¥If prompted to select a target device, select "iPhone 15 Pro Max (simulator)".

该应用应类似于页面顶部的屏幕截图。

¥The app should look like the screenshot at the top of the page.

14. 测试导出功能。

    ¥Test the export functionality.

触摸 "导出 XLSX" 将显示一个弹出窗口。

¥Touch "Export XLSX" and a popup will be displayed.

要查看生成的文件，请切换到模拟器中的 "文件" 应用，然后在 "在我的 iPhone 上" >“sheetjs-cap”中查找 SheetJSCap.xlsx

¥To see the generated file, switch to the "Files" app in the simulator and look for SheetJSCap.xlsx in "On My iPhone" > "sheetjs-cap"

Downloading the generated file (click to hide)

应用文件在 ~/Library/Developer 中的文件系统中可用。打开终端并运行以下命令查找文件：

¥The app files are available in the filesystem in ~/Library/Developer. Open a terminal and run the following command to find the file:

find ~/Library/Developer -name SheetJSCap.xlsx

15. 测试导入功能。

    ¥Test the import functionality.

创建一个电子表格或查找现有文件。单击并将文件拖入 iOS 模拟器窗口。模拟器将显示一个用于保存文件的选择器。选择 sheetjs-cap 文件夹，然后点击 "保存"。

¥Create a spreadsheet or find an existing file. Click and drag the file into the iOS simulator window. The simulator will show a picker for saving the file. Select the sheetjs-cap folder and tap "Save".

点击应用中的 "选择文件" 并在弹出窗口中点击 "选择文件"。在选择器中，点击 "最近" 并选择新文件。选择文件后，表格将刷新。

¥Tap on "Choose File" in the app and "Choose File" in the popup. In the picker, tap "Recents" and select the new file. After selecting the file, the table will refresh.

安卓设备

¥Android Device

16. 使用 USB 电缆连接 Android 设备。

    ¥Connect an Android device using a USB cable.

如果设备要求允许 USB 调试，请点击 "允许"。

¥If the device asks to allow USB debugging, tap "Allow".

17. 关闭所有 Android/iOS 模拟器。

    ¥Close any Android / iOS emulators.

18. 构建 APK 并在设备上运行：

    ¥Build APK and run on device:

npm run build
npx cap sync
npx cap run android

如果 Android 模拟器已关闭并且 Android 设备已连接，则最后一个命令将构建 APK 并安装在设备上。

¥If the Android emulators are closed and an Android device is connected, the last command will build an APK and install on the device.

在一些测试中，最后一个命令要求目标设备。在列表中选择 Android 设备，然后按 Enter

¥In some tests, the last command asked for a target device. Select the Android device in the list and press Enter

对于运行 API 级别 29 或更底层的真实设备，必须将以下行添加到 android/app/src/main/AndroidManifest.xml 中的 application 打开标记中：

¥For real devices running API level 29 or below, the following line must be added to the application open tag in android/app/src/main/AndroidManifest.xml:

android/app/src/main/AndroidManifest.xml (add highlighted attribute)

    <application
        android:allowBackup="true"
        android:icon="@mipmap/ic_launcher"
        android:label="@string/app_name"
        android:roundIcon="@mipmap/ic_launcher_round"
        android:supportsRtl="true"
        android:requestLegacyExternalStorage="true"
        android:theme="@style/AppTheme">

iOS 设备

¥iOS Device

19. 使用 USB 线连接 iOS 设备

    ¥Connect an iOS device using a USB cable

20. 关闭所有 Android/iOS 模拟器。

    ¥Close any Android / iOS emulators.

21. 启用开发者代码签名证书。

    ¥Enable developer code signing certificates.

在 Xcode 中打开 ios/App/App.xcworkspace。选择 "项目导航器"，然后选择 "应用" 项目。在主视图中，选择 "签名和能力"。在 "签名" 下，在下拉菜单中选择一个团队。

¥Open ios/App/App.xcworkspace in Xcode. Select the "Project Navigator" and select the "App" project. In the main view, select "Signing & Capabilities". Under "Signing", select a team in the dropdown menu.

22. 在设备上运行：

    ¥Run on device:

npm run build
npx cap sync
npx cap run ios

当提示选择目标设备时，请在列表中选择实际设备。

¥When prompted to select a target device, select the real device in the list.

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

¥See read in "Reading Files"

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

¥See sheet_to_html in "Utilities"

[^3]: 见 "工作簿对象"

¥See "Workbook Object"

[^4]: 见 "写入文件" 中的 "base64" 类型

¥See the "base64" type in "Writing Files"

[^5]: 见 "HTML" 实用程序中的 table_to_book

¥See table_to_book in "HTML" Utilities

[^6]: 请参阅 CapacitorJS 文档中的 "环境设置"。

¥See "Environment Setup" in the CapacitorJS documentation.