电子邮件

电子邮件（"email" 或 "e-mail"）是现代业务工作流程的重要组成部分。电子表格通常会被传递和处理。

¥Electronic mail ("email" or "e-mail") is an essential part of modern business workflows. Spreadsheets are commonly passed around and processed.

有 NodeJS 和其他服务器端解决方案用于发送带有附加电子表格的电子邮件以及处理收件箱中的电子表格。

¥There are NodeJS and other server-side solutions for sending email with attached spreadsheets as well as processing spreadsheets in inboxes.

该演示涵盖三个工作流程：

¥This demo covers three workflows:

发送邮件涵盖用于发送消息的库

¥Sending mail covers libraries for sending messages
阅读邮件涵盖用于读取消息的库

¥Reading mail covers libraries for reading messages
数据文件涵盖邮箱文件格式

¥Data files covers mailbox file formats

处理实时邮件服务器时有许多注意事项。建议在与重要收件箱或账户集成之前仔细遵循连接器模块文档并使用新账户进行测试。

¥There are a number of caveats when dealing with live mail servers. It is advised to follow connector module documentation carefully and test with new accounts before integrating with important inboxes or accounts.

实时服务器

¥Live Servers

强烈建议在使用重要地址之前先使用测试电子邮件地址。一个小错误可能会删除数十年的消息或导致谷歌服务被阻止或禁止。

¥It is strongly advised to use a test email address before using an important address. One small mistake could erase decades of messages or result in a block or ban from Google services.

应用密码

¥App Passwords

许多电子邮件提供商（包括 Fastmail、GMail 和 Yahoo Mail）需要 "应用密码" 或 "不太安全的应用" 的密码。尝试使用账户密码连接和发送将引发错误。

¥Many email providers (including Fastmail, GMail, and Yahoo Mail) require "app passwords" or passwords for "less secure apps". Attempting to connect and send using the account password will throw errors.

测试账户

¥Test Account

强烈建议首先与独立服务提供商进行测试。

¥It is strongly recommended to first test with an independent service provider.

Fastmail

该演示将从 Fastmail 的 30 天免费试用开始。上次测试演示时，不需要付款详细信息。

¥This demo will start with a free 30-day trial of Fastmail. At the time the demo was last tested, no payment details were required.

需要有效的调用号码（用于短信验证）。

¥A valid phone number (for SMS verification) was required.

创建一个新的 Fastmail 电子邮件账户并使用手机号码进行验证。

¥Create a new Fastmail email account and verify with a mobile number.

创建应用密码

¥Create App Password

打开设置屏幕（单击屏幕左上角的图标并选择 "设置"）。

¥Open the settings screen (click on the icon in the top-left corner of the screen and select "Settings").
在左侧窗格中选择 "隐私与安全"，然后单击主页顶部附近的 "集成"。单击 "新的应用密码"。

¥Select "Privacy & Security" in the left pane, then click "Integrations" near the top of the main page. Click "New app password".
在顶部下拉列表中选择任意名称（可以使用默认的 "iPhone"）。在第二个下拉列表中，选择 "邮件（IMAP/POP/SMTP）"。单击 "生成密码"。

¥Select any name in the top drop-down (the default "iPhone" can be used). In the second drop-down, select "Mail (IMAP/POP/SMTP)". Click "Generate password".

将显示新密码。这是将在演示脚本中使用的应用密码。复制或写下显示的密码。

¥A new password will be displayed. This is the app password that will be used in the demo script. Copy the displayed password or write it down.

Gmail

此演示将从免费的 Gmail 账户开始。上次测试演示时，不需要付款详细信息。

¥This demo will start with a free Gmail account. At the time the demo was last tested, no payment details were required.

需要有效的调用号码（用于短信验证和 2FA）。

¥A valid phone number (for SMS verification and 2FA) was required.

创建一个新的 Gmail 电子邮件账户并使用手机号码进行验证。

¥Create a new Gmail email account and verify with a mobile number.

创建应用密码

¥Create App Password

点击右上角的图标，然后点击 "管理你的 Google 账户"

¥Click the icon in the top-right corner and click "Manage your Google Account"
单击左栏中的 "安全"

¥Click "Security" in the left column
启用两步验证（如果当前未启用）

¥Enable 2-Step Verification (if it is not currently enabled)
点击 "两步验证"

¥Click "2-Step Verification"
单击 "应用密码" 旁边的向右箭头 (>)。

¥Click the right arrow (>) next to "App passwords".
输入名称 ("SheetJS 测试") 并单击 "创造"。

¥Type a name ("SheetJS Test") and click "Create".

将显示新密码。这是将在演示脚本中使用的应用密码。复制或写下显示的密码。

¥A new password will be displayed. This is the app password that will be used in the demo script. Copy the displayed password or write it down.

操作

¥Operations

发送邮件

¥Sending Mail

许多 SheetJS 用户在生产中部署 nodemailer 模块。

¥Many SheetJS users deploy the nodemailer module in production.

nodemailer 支持从 XLSX.write 生成的 NodeJS Buffer 附件：

¥nodemailer supports NodeJS Buffer attachments generated from XLSX.write:

/* write workbook to buffer */
const buf = XLSX.write(workbook, {
  bookType: "xlsb", // <-- write XLSB file
  type: "buffer"    // <-- generate a buffer
});

/* create a message */
const msg = { from: "*", to: "*", subject: "*", text: "*",
  attachments: [
    {
      filename: "SheetJSMailExport.xlsb", // <-- filename
      content: buf                        // <-- data
    }
  ]
}

文件名必须具有预期的扩展名 bookType!

¥The file name must have the expected extension for the bookType!

"支持的输出格式" 包含一个表格，显示每种支持的类型所需的文件扩展名。

¥"Supported Output Formats" includes a table showing the file extension required for each supported type.

发送演示

¥Send Demo

测试部署

该演示在以下部署中进行了测试：

¥This demo was tested in the following deployments:

电子邮件提供商	日期	库	版本
`gmail.com`	2024-03-11	`nodemailer`	`6.9.12`
`fastmail.com`	2024-03-11	`nodemailer`	`6.9.12`

创建一个新账户

¥Create a new account
创建一个新项目并安装依赖：

¥Create a new project and install dependencies:

mkdir sheetjs-send
cd sheetjs-send
npm i --save https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz nodemailer@6.9.12

将以下脚本保存到 SheetJSend.js：

¥Save the following script to SheetJSend.js:

SheetJSend.js
const XLSX = require('xlsx');
const nodemailer = require('nodemailer');

const transporter = nodemailer.createTransport({
  service: 'fastmail',
  auth: {
    user: '**',
    pass: '**'
  }
});

const wb = XLSX.utils.book_new();
const ws = XLSX.utils.aoa_to_sheet([["Sheet","JS"], ["Node","Mailer"]]);
XLSX.utils.book_append_sheet(wb, ws, "Sheet1");

const buf = XLSX.write(wb, { bookType: "xlsb", type: "buffer" });

const mailOptions = {
  from: "**",
  to: "**",
  subject: "Attachment test",
  text: "if this succeeded, there will be an attachment",
  attachments: [{
    filename: "SheetJSMailExport.xlsb", // <-- filename
    content: buf                        // <-- data
  }]
}

transporter.sendMail(mailOptions, function (err, info) {
  if(err) console.log(err); else console.log(info);
});

编辑 SheetJSend.js 并替换高亮的行：

¥Edit SheetJSend.js and replace the highlighted lines:

service: 'fastmail', 该值应该是受支持的提供商之一 [^1]

¥service: 'fastmail', the value should be one of the supported providers[^1]
user: "**", 该值应该是发送者电子邮件地址

¥user: "**", the value should be the sender email address
pass: "**" 该值应该是之前的应用密码

¥pass: "**" the value should be the app password from earlier
from: "**", 该值应该是发送者电子邮件地址

¥from: "**", the value should be the sender email address
to: "**", 该值应该是你的工作或个人电子邮件地址

¥to: "**", the value should be your work or personal email address

运行脚本：

¥Run the script:

node SheetJSend.js

如果处理成功，终端将打印一个 JS 对象，其中字段包括 accepted 和 response。收件人收件箱很快就会收到一封电子邮件。该电子邮件将包含一个可以在 Excel 中打开的附件 SheetJSMailExport.xlsb。

¥If the process succeeded, the terminal will print a JS object with fields including accepted and response. The recipient inbox should receive an email shortly. The email will include an attachment SheetJSMailExport.xlsb which can be opened in Excel.

必须在步骤 3 中输入应用密码。如果使用了账户密码，邮件程序将失败并显示一条消息，其中包括：

¥The app password must be entered in step 3. If the account password was used, the mailer will fail with a message that includes:

Sorry, you need to create an app password to use this service

阅读邮件

¥Reading Mail

imapflow 是一个现代 IMAP 客户端库。

¥imapflow is a modern IMAP client library.

解析附件是一个多步舞蹈：

¥Parsing attachments is a multi-step dance:

获取消息并解析消息体结构：

¥Fetch a message and parse the body structure:

let m = await client.fetchOne(client.mailbox.exists, { bodyStructure: true });
let children = message.bodyStructure.childNodes;

查找具有相关文件扩展名的所有附件：

¥Find all attachments with relevant file extensions:

for(let bs of message.bodyStructure.childNodes) {
  if(bs.disposition?.toLowerCase() != "attachment") continue;
  // look for attachments with certain extensions
  if(!/\.(numbers|xls[xbm]?)$/i.test(bs?.parameters?.name)) continue;
  await process_attachment(bs);
}

下载数据并收集到 NodeJS 缓冲区中：

¥Download data and collect into a NodeJS Buffer:

/* helper function to concatenate data from a stream */
const concat_RS = (stream) => new Promise((res, rej) => {
  var buffers = [];
  stream.on("data", function(data) { buffers.push(data); });
  stream.on("end", function() { res(Buffer.concat(buffers)); });
});
async function process_attachment(bs) {
  const { content } = await client.download('*', bs.part);
  /* content is a stream */
  const buf = await concat_RS(content);
  return process_buf(buf, bs.parameters.name);
}

使用 XLSX.read 解析缓冲区：

¥Parse Buffer with XLSX.read:

function process_buf(buf, name) {
  const wb = XLSX.read(buf);
  /* DO SOMETHING WITH wb HERE */
  // print file name and CSV of first sheet
  const wsname = wb.SheetNames[0];
  console.log(name);
  console.log(XLSX.utils.sheet_to_csv(wb.Sheets[wsname]));
}

接收演示

¥Receive Demo

测试部署

该演示在以下部署中进行了测试：

¥This demo was tested in the following deployments:

电子邮件提供商	日期	库	版本
`gmail.com`	2024-03-11	`imapflow`	`1.0.156`
`fastmail.com`	2024-03-11	`imapflow`	`1.0.156`

创建一个新账户

¥Create a new account
创建一个新项目并安装依赖：

¥Create a new project and install dependencies:

mkdir sheetjs-recv
cd sheetjs-recv
npm i --save https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz imapflow@1.0.156

将以下脚本保存到 SheetJSIMAP.js：

¥Save the following script to SheetJSIMAP.js:

SheetJSIMAP.js
const XLSX = require('xlsx');
const { ImapFlow } = require('imapflow');

const client = new ImapFlow({
  host: 'imap.fastmail.com',
  port: 993, secure: true, logger: false,
  auth: {
    user: '**',
    pass: '**'
  }
});

const concat_RS = (stream)  => new Promise((res, rej) => {
  var buffers = [];
  stream.on("data", function(data) { buffers.push(data); });
  stream.on("end", function() { res(Buffer.concat(buffers)); });
});

(async() => {
  await client.connect();
  let lock = await client.getMailboxLock('INBOX'); // INBOX
  try {
    // fetch latest message source with body structure
    let message = await client.fetchOne(client.mailbox.exists, { bodyStructure: true });
    for(let bs of message.bodyStructure.childNodes) {
      if(bs.disposition?.toLowerCase() != "attachment") continue;
      // look for attachments with certain extensions
      if(!/\.(numbers|xls[xbm]?)$/i.test(bs?.parameters?.name)) continue;

      // download data
      const { content } = await client.download('*', bs.part);
      const buf = await concat_RS(content);

      // parse
      const wb = XLSX.read(buf);

      // print file name and CSV of first sheet
      const wsname = wb.SheetNames[0];
      console.log(bs.parameters.name);
      console.log(XLSX.utils.sheet_to_csv(wb.Sheets[wsname]));
    }
  } finally { lock.release(); }
  await client.logout();
})();

编辑 SheetJSIMAP.js 并替换高亮的行：

¥Edit SheetJSIMAP.js and replace the highlighted lines:

user: "**", 值应该是账户地址

¥user: "**", the value should be the account address
pass: "**" 该值应该是之前的应用密码

¥pass: "**" the value should be the app password from earlier
host: 'imap.fastmail.com', 值应该是主机名：

¥host: 'imap.fastmail.com', the value should be the host name:

服务	`host` 值
`gmail.com`	`imap.gmail.com`
`fastmail.com`	`imap.fastmail.com`

下载 https://xlsx.nodejs.cn/pres.numbers .使用其他账户，向测试账户发送电子邮件并附加文件。在此步骤结束时，测试账户的收件箱中应该有一封带有附件的电子邮件。

¥Download https://xlsx.nodejs.cn/pres.numbers . Using a different account, send an email to the test account and attach the file. At the end of this step, the test account should have an email in the inbox that has an attachment.
运行脚本：

¥Run the script:

node SheetJSIMAP.js

输出应包括文件名 (pres.numbers) 和 CSV：

¥The output should include the file name (pres.numbers) and the CSV:

pres.numbers
Name,Index
Bill Clinton,42
GeorgeW Bush,43
Barack Obama,44
Donald Trump,45
Joseph Biden,46

数据文件

¥Data Files

电子发现通常涉及电子邮件探索。有许多专有的邮件和电子邮件账户文件格式。

¥Electronic discovery commonly involves email spelunking. There are a number of proprietary mail and email account file formats.

PST

该展览已移至单独的页面。

¥The exposition has been moved to a separate page.

[^1]: 服务列表可以在 NodeMailer 项目的 lib/well-known/services.json 中找到。

¥The list of services can be found in lib/well-known/services.json in the NodeMailer project.

实时服务器​

应用密码​

测试账户​

Fastmail​

Gmail​

操作​

发送邮件​

发送演示​

阅读邮件​

接收演示​

数据文件​

PST​