教程：比较 Excel 文件和电子邮件对帐错误

在本教程中，我们将创建一个自动化项目，将包含发票的电子表格中的金额与包含每个供应商摘要的 CSV 文件中的金额进行比较。如果文件之间存在任何对帐错误，我们将创建一个包含详细信息的 CSV 文件，然后通过电子邮件发送该文件。

我们将创建一个包含以下活动的项目：

1. Two Use Excel File activities to indicate the Excel file with the invoices and the file where we want to reconcile the data.
2. A series of Write Cell activities to add two column headers to the reconciliation Excel file and to calculate the sum of invoices for one supplier and the difference between that sum and the amount in the initial summary. We will add a Copy Range activity to copy the formulas we added for one supplier to all the rows in the sheet so that we can perform the same calculations for all the suppliers, and then add one more Write Cell activity to add the total difference to a cell.
3. An If activity where we will add a condition that if there are reconciliation errors (the sum of differences is greater than 0), a CSV file with details should be created and emailed to a specified address. For the email tasks, inside the If activity we will add a Use Desktop Outlook App activity to indicate the Outlook account we want to send the mail from and a Send Email activity inside Use Desktop Outlook App.
4. 设置项目并获取必要的文件。
   1. Create a new blank project using the default settings.
   2. Download and extract the archive with the automation project in this tutorial using the button at the bottom of this page. Copy the folder data to your project folder.
5. 将 Excel 文件添加到项目中。
   1. Click Add Activity in the Designer panel, and then find the Use Excel File activity in the search box at the top of the screen and select it. A Use Excel File activity is added to the Designer panel.
   2. 在活动中：

      • Click Browse next to the Excel file field, and then browse to and select the file invoices.xlsx

      • 在“引用为”字段中，输入 Invoices。

        您已指明将使用自动化中称为“Invoices”的文件 invoices.xlsx。

   3. Click Add Activity inside the Use Excel File activity, and then add a another Use Excel File activity.
   4. 在第二个活动中：

      • Click Browse next to the Excel file field, and then browse to and select the file reconcile.xlsx

      • 在“引用为”字段中，输入 Rec。

        您已指明将使用自动化中称为 Rec 的文件 reconcile.xlsx。

        

6. 将数据复制到对帐文件中。
   1. Click Add Activity inside the second Use Excel File, and then find the Read CSV activity in the search box at the top of the screen and select it. The activity is added inside the Use Excel File activity.
   2. In the Read CSV activity:

      • Click Browse next to the Read from file field, and then browse to and select the file summary.csv.

      • Click Plus on the right side of the Output to field, and then select Rec > Summary [Sheet]. The field is updated with your selection [Rec] Summary.

        您已指明要将数据从 CSV 文件 summary.csv 复制到摘要工作表中的记录文件。

   3. Click Add Activity inside the second Use Excel File below the Read CSV activity, and then find the Copy Range activity in the search box at the top of the screen and select it. A Copy Range activity is added inside the Use Excel File activity.
   4. 在“复制范围”活动中：

      • Click Plus on the right side of the Source field, and then select Invoices > Invoices [Sheet]. Your selection is displayed in the field as [Invoices] Invoices.

        您已指明要从文件“Invoices”中复制“Invoices”工作表。

      • Click Plus on the right side of the Destination field, and then select Rec > Invoices [Sheet]. Your selection is displayed in the field as [Rec] Invoices.

        您已指明要将复制的范围粘贴到“Rec”文件中的“Invoices”工作表中。

        

7. 进行必要的计算以比较数据。
   1. 在“活动”面板中，选择“Excel”选项卡，然后将“写入单元格”活动拖放到第二个“使用 Excel 文件”中“复制范围”下方。“写入单元格”活动随即添加到“设计器”面板中。
   2. 在“写入单元格”活动中：

      • Click Plus on the right side of the What to write field, and then select Text. In the Text Builder, enter the text Calculated Amount.

      • Click Plus on the right side of the Where to write field, and then select Rec > Indicate in Excel. The spreadsheet is opened in Excel. Select the cell C1, and then click Confirm. The field is updated with your selection [Rec] Summary!C1.

        您已指明要让“Rec”文件的“Summary”工作表中 C 列的第一个单元格包含文本 Calculated Amount。

   3. 在前一个“写入单元格”活动下方添加第二个“写入单元格”活动，并在活动中执行以下操作::

      • Click Plus on the right side of the What to write field, and then select Text. In the Text Builder, enter the text =IF(A2 <> "", SUMIF(Invoices!B:B, A2, Invoices!C:C), "").

      • Click Plus on the right side of the Where to write field, and then select Rec > Indicate in Excel. The spreadsheet is opened in Excel. Select the cell C2, and then click Confirm. The field is updated with your selection [Rec] Summary!C2.

        您已指明要让“Rec”文件的“Summary”工作表中的 C2 包含公式 =IF(A2 <> "", SUMIF(Invoices!B:B, A2, Invoices!C:C), "")。此公式会检查供应商单元格 (A2) 是否不为空，并会对“Summary”工作表的单元格 A2 中供应商的“Invoices”工作表 C 列中的值求和。

   4. 在前一个“写入单元格”活动下方添加第三个“写入单元格”活动，并在活动中执行以下操作::

      • Click Plus on the right side of the What to write field, and then select Text. In the Text Builder, enter the text Difference.

      • Click Plus on the right side of the Where to write field, and then select Rec > Indicate in Excel. The spreadsheet is opened in Excel. Select the cell D1, and then click Confirm. The field is updated with your selection [Rec] Summary!D1.

        您已指明要让“Rec”文件的“Summary”工作表中 D 列的第一个单元格包含文本 Difference。

   5. 在前一个“写入单元格”活动下方添加第四个“写入单元格”活动，并在活动中执行以下操作::

      • Click Plus on the right side of the What to write field, and then select Text. In the Text Builder, enter the text =IF(A2 <> "", C2-B2, "").

      • Click Plus on the right side of the Where to write field, and then select Rec > Indicate in Excel. The spreadsheet is opened in Excel. Select the cell D2, and then click Confirm. The field is updated with your selection [Rec] Summary!D2.

        您已指明要让“Rec”文件的“Summary”工作表中的 D2 单元格包含公式 =IF(A2 <> "", C2-B2, "")。此公式会检查供应商单元格 (A2) 是否不为空，并计算“Summary”工作表 C2 和 B2 单元格中值的差额。

   6. Click Add Activity below the last Write Cell activity, and then find the Copy Range activity in the search box at the top of the screen and select it. A Copy Range activity is added to the Designer panel.
   7. 在“复制范围”活动中：

      • Click Plus on the right side of the Source field, and then select Rec > Indicate in Excel. The spreadsheet is opened in Excel. Select the range C2:D2, and then click Confirm. The field is updated with your selection [Rec] Summary!C2:D2.

      • Click Plus on the right side of the Destination field, and then select Rec > Indicate in Excel. The spreadsheet is opened in Excel. Select the range C3-D20, and then click Confirm. The field is updated with your selection [Rec] Summary!C3-D20.

      • 从“复制内容”下拉菜单中，选择“公式”

        您已指明要将复制范围 C2:D2 中的公式粘贴到工作表的同一列中。

   8. 在“活动”面板中，选择“Excel”选项卡，然后将“写入单元格”活动拖放到最后一个“复制范围”活动下面。“写入单元格”活动随即添加到“设计器”面板中。
   9. 在“写入单元格”活动中：

      • Click Plus on the right side of the What to write field, and then select Text. In the Text Builder, enter the formula =SUM(Summary!D:D).

      • Click Plus on the right side of the Where to write field, and then select Rec > Indicate in Excel. The spreadsheet is opened in Excel. Select the sheet Invoices, then the cell F1, and then click Confirm. The field is updated with your selection [Rec] Invoices!F1.

        您已指明要让“Rec”文件的“Invoices”工作表中的 F 列中第一个单元格包含公式 =SUM(Summary!D:D)。该公式会将“Summary”工作表的 D 列中的差异总和添加到“Invoices”工作表中的 F1 单元格中。

        

8. 如果差异总和大于 0，则发送一封电子邮件，其中包含有关对帐的详细信息。

   1. Click Add Activity below the last Write Cell activity, and then, in the search box at the top of the screen, locate and select If. An If activity is added to the Designer panel.

   2. In the If activity, select Open in Advanced Editor next to the Condition field to open the Condition Builder.

   3. 在“条件生成器”中，添加条件“如果差的总和大于 0”：

      • Click Plus on the right side of the field on the left, and then select Rec > Indicate in Excel. The spreadsheet is opened in Excel. Select the sheet Invoices, then the cell F1, and then click Confirm. The field is updated with your selection [Rec] Invoices!F1.

      • From the drop-down menu in the middle, select greater than.

      • Click Plus on the right side of the field on the right, select Number, enter 0 and click Save twice.

        如果满足此条件，将执行您在 If 活动的 Then 分支中添加的活动。

   4. In the Activities panel, select the CSV tab, and then drag the Write CSV activity and drop it in the Then branch of the If activity. A Write CSV activity is added to the Designer panel.

   5. 在“写入 CSV”活动中执行以下操作：

      • Click Plus on the right side of the Write to what file field, and then select Text. In the Text Builder, enter reconcile-, and then from the Plus menu on the right side of the Text Builder select Notebook > Date [Sheet] > YYYYMMDD [Cell]. The text in the Text Builder is updated to reconcile-[Notes] Date!YYYYMMDD. Enter the text .csv at the end and click Save. The final text should be reconcile-[Notes] Date!YYYYMMDD.csv.

      • Click Plus on the right side of the Write from field, and then select Rec > Summary [Sheet]. The field is updated with your selection [Rec] Summary.

        您已指明要在项目文件夹中创建一个 CSV 文件，其名称中要包含单词“reconcile”和今天的日期，然后将“Rec”文件中的“Summary”工作表复制到该文件中。

   6. 在“活动”面板中，选择“邮件”选项卡，然后将“使用桌面 Outlook App”活动拖放到“写入 CSV”活动下方 If 活动的 Then 分支中。

   7. 在活动中，“帐户”字段中已经选择默认的电子邮件帐户。如果您要使用其他帐户，请从下拉菜单中选择。

      在“引用为”字段中，保留默认值 Outlook 作为在自动化中引用该帐户时使用的名称。

   8. 在“活动”面板中，将“发送电子邮件”活动拖放到“使用桌面 Outlook App”活动中。

   9. 在"发送电子邮件"活动中：

      • Click Plus on the right side of the Account field, and then select Outlook.

      • Click Plus on the right side of the To field, and then select Text. In the Text Builder window, enter an email address where to send the email. For example, you can enter your own email address to send the email to yourself. If you leave the Save as draft option selected, the automation does not send the email, it instead saves the email to the Outlook Drafts folder.

      • Click Plus on the right side of the Subject field, and then select Text. In the Text Builder window, enter Reconciliation errors for, and then from the Plus menu on the right side of the Text Builder select Notebook > Date [Sheet] > Today [Cell]. The text in the Text Builder is updated to Reconciliation errors for [Notes] Date!Today.

      • Click Plus on the right side of the Body field, and then select Text. In the Text Builder window, enter text for the body of the email, for example One or more suppliers have reconciliation errors. See attached CSV file for details..

      • 对于附件，选择“文件”，然后输入与第 5 步中“写入 CSV”活动中所用文件名相同的文件名reconcile-[Notes] Date!YYYYMMDD.csv。例如，您可以打开“写入 CSV”活动的“写入哪个文件”字段的文本生成器，复制所有文本，然后粘贴在文件附件字段的文本生成器中。

        

   10. Click Save in the StudioX ribbon to save the automation, then click Run to execute the automation.The invoices and summary data are copied to the reconcile.xlsx file, the calculations are performed and, because the total amount in the invoices does not match the one in the summary for one of the suppliers, the Summary sheet of the reconcile.xsls file is copied to a CSV file whose name includes today's date, the file is attached to an email and sent to the specified address. In the file, you can identify the supplier with reconciliation errors by the value in cell D3 that is greater than 0. Download example