salesforce - 如何在静态资源中使用 Excel 文件并在 Salesforce Apex 中访问其字段

Question

我想从静态资源文件中获取代码中的值。我在静态资源中使用 excel 文件，我想知道如何将 excel 文件中的字段映射到代码中的 salesforce 字段。例如 Account.Name 等

铅 l = 新铅（）；l.Company = $Resource.EMailServicesLead.EmailServiceStaticResourceData.csv; \ Not Working EMailServicesLead - 静态资源的名称。EmailServiceStaticResourceData - 包含字段 {FirstName、LastName、Email、Company、Static、LeadSource} 的 csv 文件

/** * @File Name : myHandler.cls * @Description : * @Author : [e-mail] * @Group : * @Last Modified By : [e-mail] * @Last Modified On : 7/10/2019 , 3:54:01 PM * @Modification Log : * Ver Date Author Modification * 1.0 7/10/2019 [e-mail] Initial Version **/ global with sharing virtual class myHandler implements Messaging.InboundEmailHandler { global Messaging.InboundEmailResult handleInboundEmail （Messaging.InboundEmail 电子邮件，Messaging.InboundEnvelope 信封）{ Messaging.InboundEmailResult result = new Messaging.InboundEmailresult();

  //Lead l= (Lead)ListofLeadsStaticRes[0];
  // List<SObject> ListofLeadsStaticRes = {!URLFOR($Resource.EMailServicesLead, 'EmailServiceStaticResourceData.csv')};
  Integer j=0;
  String myPlainText= '';

   staticResource sr = new staticResource();
   List<sObject> ls = Test.loadData(Lead.sObjectType, 'myResource');
  // {!URLFOR($Resource.EMailServicesLead,EMailServicesLead/EmailServiceStaticResourceData.csv)}

  Lead l = new Lead();
  l.FirstName=email.fromName.substring(0,email.fromName.indexOf(''));
  l.LastName=email.fromName.substring(email.fromName.indexOf(''));
  l.Email = envelope.fromAddress;
  myPlainText = email.plainTextBody;
  l.Company = $Resource.EMailServicesLead.EmailServiceStaticResourceData.csv;
  l.Status = $Resource.EMailServicesLead.EmailServiceStaticResourceData.csv;
  l.LeadSource = $Resource.EMailServicesLead.EmailServiceStaticResourceData.csv;
  l.Description = 'Mr.' + l.FirstName + l.LastName + ' enquired about the product Fin' + ' via' + ' Ëmail' + 'with body '+myPlainText;

  Task[] newTask = new Task[0];
  try { // Add a new Task to the Lead record we just found above.
       List<Lead> vLed = [SELECT Id, Name, Email FROM Lead WHERE Email = :email.fromAddress LIMIT 1];
       if(vLed.size()>0)
        { newTask.add(new Task(Description =  myPlainText,
                                  Priority = 'Normal',
                                  Status = 'Inbound Email',
                                  Subject = email.subject,
                                  IsReminderSet = true,
                                  ReminderDateTime = System.now()+1,
                                  WhoId =  vLed[0].Id));
          insert newTask;
          System.debug('New Task Object: ' + newTask ); 
        }
       else 
        {
          insert l;
          System.debug('Lead Created'+ l.Id);
        }
       }
    catch (QueryException e) {  System.debug('Query Issue: ' + e); }     
    return  result;
 }

}

标签： salesforceapex

Answer 1

您将需要解析 CSV 以从中提取数据。为了使这更容易，您可以使用Nicolas Galler 编写的这个简单的 CSV 解析器类。查看代码，了解它的工作原理，但您将使用的只是构造函数和将readLine()CSV 每一行的值列表作为字符串返回的方法。您必须将该链接中的代码复制并粘贴到名为“SSSCsvReader”的顶点类中才能使用它，然后在您的代码中...

1. 将您的静态资源 CSV 作为字符串

StaticResource sr = [SELECT ID, body FROM StaticResource WHERE Name = 'EMailServicesLead' LIMIT 1];
String csvData = sr.body.toString();

2.SSSCsvReader通过调用将 CSV 字符串作为参数传入的构造函数来创建

SSSCsvReader csvR = new SSSCsvReader(csvData);

3. 使用readLine()SSSCsvReader对象上的 获取字符串列表中的 CSV 字段值。（如果您的文件上有标题行，请不要忘记readLine()在获取数据列表之前先调用一次来删除它）。

csvR.readLine() //Removing header (optional)
String[] line = csvR.readLine();
if(line == null){return null} //No line in the file or there was no header so return

4. 将返回列表中的每个字段分配readLine()给您的 salesforce 对象中的相应字段。

Lead l = new Lead();
l.firstName = line[0]; //If your file is laid out like you said FirstName will be index [0]
l.lastName = line[1]; //LastName will be index [1]
.... //And so on
....
....

那里有一个 Lead 对象，其中包含 CSV 静态资源文件中的字段。如果您的 CSV 上有更多行，并且想要创建更多潜在客户，而不仅仅是设置一个 while 循环，使用 CSV 从 CSV 获取每一行，请readLine()从该行创建一个新的潜在客户并将其添加到列表中。