教程：筛选 Excel 的数据

在此快速教程中，我们将创建一个自动化项目来实现以下流程：

1. 将包含供应商信息的电子表格中的数据复制到新的工作表中。
2. 筛选数据以仅显示与过去十年中添加的服务和 IT 行业供应商的行。
3. 将筛选的数据复制到 CSV 文件。
4. 通过电子邮件发送 CSV 文件。

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

• “使用 Excel 文件”活动，用来指定带有供应商信息的 Excel 文件。
• “复制/粘贴范围”活动，用于将数据复制到另一张工作表。
• 两个筛选器活动可根据所需条件筛选数据：一个筛选器用于行业列，另一个筛选器用于供应商起始时间列。
• “写入 CSV”活动，用于将筛选后的数据复制到 CSV 文件。
• “使用 Outlook 桌面 App”活动，用于指明发出电子邮件的 Outlook 帐户。
• “发送邮件”活动，用于发送电子邮件。

1. 设置项目并获取必要的文件。
   1. 使用默认设置创建一个新的空白项目。
   2. 使用此页面底部的按钮，通过本教程中的自动化项目下载并提取存档。将“Suppliers.xlsx”文件复制到您的项目文件夹。
2. 将 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 Suppliers.xlsx

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

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

3. 筛选数据并将其复制到 CSV 文件。

   1. Click Add Activity inside the Use Excel File and then, in the search box at the top of the screen, locate and select Copy/Paste Range. A Copy/Paste Range activity is added to the Designer panel.

   2. 在“复制/粘贴范围”活动中：

      • Click Plus on the right side of the Source field, and then select Suppliers > Data [Sheet].

      • Click Plus on the right side of the Destination field, and then select Suppliers > Processed [Sheet].

        您已指明要复制“Suppliers”文件的“Data”工作表中的数据，并将其粘贴到同一文件中的“Processed”工作表中。

   3. 在 StudioX 功能区中单击“保存”，以保存自动化，然后单击“运行”来执行此自动化。

      数据从“Data”工作表复制到“Suppliers”工作簿中的“Processed”工作表。

   4. Click Add Activity inside the Use Excel File just below the Copy/Paste Range activity, and then, in the search box at the top of the screen, locate and select Filter. A Filter activity is added to the Designer panel.

   5. 在“筛选”活动中：

      • Click Plus on the right side of the Source field, and then select Suppliers > Processed [Sheet].
      • Click Plus on the right side of the Column name field, and then select Range > Industry.
      • 单击“配置筛选器”按钮。在“筛选器”窗口中，确保选中“基本筛选器”，然后：

        • Click Plus on the right side of the Value field, and then select Text. In the Text Builder, enter Services, and then click Save.

        • 单击“添加”以添加第二个值。

        • Click Plus on the right side of the second Value field, and then select Text. In the Text Builder, enter IT, then click Save, and then click OK to close the Filter window.

          您已指明要筛选“Processed”工作表中的数据，以便在“Industry ”列中仅显示值为“Services ”或“IT”的行。

   6. Click Add Activity inside the Use Excel File just below the Filter activity, and then, in the search box at the top of the screen, locate and select Filter. A second Filter activity is added to the Designer panel.

   7. 在第二个筛选活动中：

      • Click Plus on the right side of the Source field, and then select Suppliers > Processed [Sheet].
      • Click Plus on the right side of the Column name field, and then select Range > Supplier Since.
      • 单击“筛选”按钮。在“筛选”窗口中：

        • Select Advanced filter.

        • 从“运算符”下拉菜单中，选择“>”（大于）。

        • Click Plus on the right side of the Value field, and then select Text. In the Text Builder, enter a date from 10 years ago, for example 5/5/2009, then click Save, and then click OK to close the Filter window.

          您已指明要筛选“Processed”工作表中的数据，以便在“Supplier Since”列中仅显示日期为“5/5/2009”之后的行。

   8. 要使“筛选”活动更容易识别，请在每个活动的上部栏中编辑名称。例如第一个活动使用 Filter Industry，第二个活动使用 Filter Supplier Since。

   9. Click Add Activity just below the Use Excel File activity, and then, in the search box at the top of the screen, locate and select Write CSV. A Write CSV activity is added to the Designer panel. Alternatively, you can also add this activity inside the Use Excel File activity, just below the last Filter activity.

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

       • Click Plus on the right side of the Write to what file field, and then select Text. In the Text Builder, enter result-, 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 result-[Note] Date!YYYYMMDD. Enter the text .csv at the end and click Save. The final text should be result-[Note] Date!YYYYMMDD.csv.

       • Click Plus on the right side of the Write from field, and then select Suppliers > Processed [Sheet].

         您已经表明要在项目文件夹中创建一个 CSV 文件，其名称包含文本“result-”和今天的日期，并且您要将“Processed”工作表中的数据复制到其中。

         

4. 通过电子邮件发送 CSV 文件。

   1. Click Add Activity below the Use Excel File activity, and then find the Use Desktop Outlook App activity in the search box at the top of the screen and select it. A Use Desktop Outlook App activity is added to the Designer panel.

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

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

   3. Click Add Activity inside the Use Desktop Outlook App activity, and then, in the search box at the top of the screen, locate and select Send Email. A Send Email activity is added to the Designer panel.

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

      • 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 List of filtered suppliers for , and then, from the Plus menu on the right side of the Text Builder, select Notebook > Date [Sheet] > Today [Cell]. The final text should look like this: List of filtered suppliers for [Notes]Date!Today. Click Save to close the Text Builder.

      • 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 Please see attachment.

      • For Attachments, select Files, then click Plus on the right side of the field, and then select Text. In the Text Builder, enter the file name in the same way you entered it in the Write CSV activity: result-[Notes] Date!YYYYMMDD.csv. One way you could do this is to select all the text in the Text Builder of the Write to what file field in the Write CSV activity, copy the text, and then paste it to the Text Builder of the Attachments field.

        

   5. 在 StudioX 功能区单击“保存”以保存自动化，然后单击“运行”来执行自动化。筛选“已处理”工作表中的数据，然后将其复制到名称中包含今天日期的 CSV 文件中，然后通过电子邮件发送该 CSV 文件。下载示例