Thys Michels Blog
{ turning coffee into code }
Archives
Apache Camel Salesforce Integration
1. Setup SalesforceLoginConfig
2. Setup SalesforceCamelEndpointConfig
3. Setup SalesforceCamelComponent
4. Setup SalesforceCamelRouteConfig
5. Run SalesforceCamelIntegrationTest
1. Setup SalesforceLoginConfig
package com.sforce.core.camel;
import org.apache.camel.CamelContext;
import org.apache.camel.component.salesforce.SalesforceComponent;
import org.apache.camel.component.salesforce.SalesforceEndpointConfig;
import org.apache.camel.component.salesforce.SalesforceLoginConfig;
import org.apache.camel.impl.DefaultCamelContext;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
/**
* Created by tmichels on 4/8/15.
*/
@Configuration
public class SalesforceCamelConfig {
@Value("${SF_USERNAME}")
private String username;
@Value("${SF_PASSWORD}")
private String password;
@Value("${SF_TOKEN}")
private String token;
@Value("${SF_BASE_URL}")
private String url;
@Value("${SF_VERSION}")
private String version;
@Value("${SF_CLIENT_ID}")
private String clientId;
@Value("${SF_CLIENT_SECRET}")
private String clientSecret;
@Bean
public SalesforceLoginConfig salesforceLoginConfig(){
SalesforceLoginConfig salesforceLoginConfig = new SalesforceLoginConfig();
salesforceLoginConfig.setUserName(username);
salesforceLoginConfig.setPassword(password+token);
salesforceLoginConfig.setLoginUrl(url);
salesforceLoginConfig.setClientId(clientId);
salesforceLoginConfig.setClientSecret(clientSecret);
salesforceLoginConfig.setLazyLogin(false);
return salesforceLoginConfig;
}
}
2. Setup SalesforceCamelEndpointConfig
package com.sforce.core.camel;
import org.apache.camel.Endpoint;
import org.apache.camel.component.salesforce.SalesforceComponent;
import org.apache.camel.component.salesforce.SalesforceEndpoint;
import org.apache.camel.component.salesforce.SalesforceEndpointConfig;
import org.apache.camel.component.salesforce.internal.OperationName;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
/**
* Created by tmichels on 4/13/15.
*/
@Configuration
public class SalesforceCamelEndpointConfig {
@Bean
public SalesforceEndpointConfig salesforceEndpointConfig(){
SalesforceEndpointConfig salesforceEndpointConfig = new SalesforceEndpointConfig();
salesforceEndpointConfig.setApiVersion("33.0");
return salesforceEndpointConfig;
}
}
3. Setup SalesforceCamelComponent
package com.sforce.core.camel;
import org.apache.camel.component.salesforce.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Import;
/**
* Created by tmichels on 4/13/15.
*/
@Configuration
@Import({SalesforceCamelEndpointConfig.class, SalesforceCamelConfig.class})
public class SalesforceCamelComponent {
@Autowired
SalesforceEndpointConfig salesforceEndpointConfig;
@Autowired
SalesforceLoginConfig salesforceLoginConfig;
@Bean
public SalesforceComponent salesforceComponent(){
SalesforceComponent salesforceComponent = new SalesforceComponent();
salesforceComponent.setConfig(salesforceEndpointConfig);
salesforceComponent.setLoginConfig(salesforceLoginConfig);
salesforceComponent.setPackages("com.sfore.core.camel");
return salesforceComponent;
}
}
4. Setup SalesforceCamelRouteConfig
package com.sforce.core.camel;
import com.sforce.core.config.LcGroovyPropertiesConfig;
import org.apache.camel.CamelContext;
import org.apache.camel.builder.RouteBuilder;
import org.apache.camel.component.salesforce.SalesforceComponent;
import org.apache.camel.spring.SpringCamelContext;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.*;
import org.apache.camel.spring.javaconfig.SingleRouteCamelConfiguration;
import org.apache.camel.spring.javaconfig.Main;
/**
* Created by tmichels on 4/10/15.
*/
@Configuration
@ComponentScan({"com.sforce.core.camel"})
public class SalesforceCamelRouteConfig extends SingleRouteCamelConfiguration implements InitializingBean {
@Autowired
SalesforceComponent salesforceCamelComponent;
public static void main(String[] args) throws Exception {
Main main = new Main();
main.run(args);
}
@Override
protected CamelContext createCamelContext() throws Exception {
SpringCamelContext camelContext = new SpringCamelContext();
camelContext.setApplicationContext(getApplicationContext());
camelContext.addComponent("salesforce", salesforceCamelComponent);
camelContext.addRoutes(route());
camelContext.start();
return camelContext;
}
@Override
public void afterPropertiesSet() throws Exception {}
@Bean
@Override
public RouteBuilder route() {
return new RouteBuilder() {
public void configure() {
from("direct:getBasicInfo").to("salesforce:getBasicInfo").bean(BasicInfoBean.class);
}
};
}
public static class BasicInfoBean {
public void someMethod(String body) {
System.out.println(">>>>>>> Salesforce Basics Info: " + body);
}
}
}
5. Run SalesforceCamelIntegrationTest
package com.sforce.core.camel;
import org.apache.camel.CamelContext;
import org.apache.camel.EndpointInject;
import org.apache.camel.Produce;
import org.apache.camel.ProducerTemplate;
import org.apache.camel.component.mock.MockEndpoint;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.annotation.DirtiesContext;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
/**
* Created by tmichels on 7/27/15.
*/
@ContextConfiguration(classes = {SalesforceCamelRouteConfig.class})
@RunWith(SpringJUnit4ClassRunner.class)
public class SalesforceCamelIntegrationTest {
@Autowired
CamelContext camelContext;
@EndpointInject(uri = "direct:getBasicInfo")
ProducerTemplate producer;
@Test
public void testCamelContext() throws Exception {
String body = "Account";
producer.sendBody(body);
}
}
OUTPUT:
>>>>>>> Salesforce Basics Info: {"objectDescribe":{"name":"Account","label":"Account","updateable":true,"keyPrefix":"001","custom":false,"urls":{"sobject":"/services/data/v33.0/sobjects/Account","describe":"/services/data/v33.0/sobjects/Account/describe","rowTemplate":"/services/data/v33.0/sobjects/Account/{ID}","approvalLayouts":"/services/data/v33.0/sobjects/Account/describe/approvalLayouts","quickActions":"/services/data/v33.0/sobjects/Account/quickActions","listviews":"/services/data/v33.0/sobjects/Account/listviews","layouts":"/services/data/v33.0/sobjects/Account/describe/layouts","compactLayouts":"/services/data/v33.0/sobjects/Account/describe/compactLayouts"},"searchable":true,"labelPlural":"Accounts","layoutable":true,"activateable":false,"createable":true,"deprecatedAndHidden":false,"deletable":true,"customSetting":false,"feedEnabled":true,"mergeable":true,"queryable":true,"replicateable":true,"retrieveable":true,"undeletable":true,"triggerable":true},"recentItems":[{"attributes":{"type":"Account","url":"/services/data/v33.0/sobjects/Account/001W000000JhUzlIAF"},"Name":"Pope John XXIII Regional High School","Id":"001W000000JhUzlIAF"},{"attributes":{"type":"Account","url":"/services/data/v33.0/sobjects/Account/001W000000JhZFQIA3"},"Name":"A PLAZA DRIVING SCHOOL","Id":"001W000000JhZFQIA3"},{"attributes":{"type":"Account","url":"/services/data/v33.0/sobjects/Account/001W000000JgxxXIAR"},"Name":"Advanced Reproductive Care, Inc. (ARC)","Id":"001W000000JgxxXIAR"},{"attributes":{"type":"Account","url":"/services/data/v33.0/sobjects/Account/001W000000JrwqvIAB"},"Name":"name","Id":"001W000000JrwqvIAB"},{"attributes":{"type":"Account","url":"/services/data/v33.0/sobjects/Account/001W000000JhXSnIAN"},"Name":"Stella Niagara Education Park","Id":"001W000000JhXSnIAN"},{"attributes":{"type":"Account","url":"/services/data/v33.0/sobjects/Account/001W000000Jr1WOIAZ"},"Name":"A J DIANA SONS INC","Id":"001W000000Jr1WOIAZ"},{"attributes":{"type":"Account","url":"/services/data/v33.0/sobjects/Account/001W000000Jr1TIIAZ"},"Name":"A J DIANA SONS COMPANY","Id":"001W000000Jr1TIIAZ"},{"attributes":{"type":"Account","url":"/services/data/v33.0/sobjects/Account/001W000000Jr1WNIAZ"},"Name":"A J DIANA SONS INC","Id":"001W000000Jr1WNIAZ"},{"attributes":{"type":"Account","url":"/services/data/v33.0/sobjects/Account/001W000000JqzmSIAR"},"Name":"A J DIANA SONS INC","Id":"001W000000JqzmSIAR"},{"attributes":{"type":"Account","url":"/services/data/v33.0/sobjects/Account/001W000000Jq8TeIAJ"},"Name":"A J DIANA SONS COMPANY","Id":"001W000000Jq8TeIAJ"},{"attributes":{"type":"Account","url":"/services/data/v33.0/sobjects/Account/001W000000JhZLTIA3"},"Name":"name","Id":"001W000000JhZLTIA3"},{"attributes":{"type":"Account","url":"/services/data/v33.0/sobjects/Account/001W000000Jq8LVIAZ"},"Name":"A PLAZA DRIVING SCHOOL","Id":"001W000000Jq8LVIAZ"},{"attributes":{"type":"Account","url":"/services/data/v33.0/sobjects/Account/001W000000JhZBTIA3"},"Name":"A J DIANA SONS INC","Id":"001W000000JhZBTIA3"},{"attributes":{"type":"Account","url":"/services/data/v33.0/sobjects/Account/001W000000JgxwjIAB"},"Name":"[First]Name [Last]Name","Id":"001W000000JgxwjIAB"},{"attributes":{"type":"Account","url":"/services/data/v33.0/sobjects/Account/001W000000JhUlVIAV"},"Name":"Central Texas Christian School","Id":"001W000000JhUlVIAV"},{"attributes":{"type":"Account","url":"/services/data/v33.0/sobjects/Account/001W000000JhKbtIAF"},"Name":"Sylvan Learning Center - 112","Id":"001W000000JhKbtIAF"}]}
Salesforce Export Database Records to CSV for Batch Upload
Exporting of Database records to csv can be done using:
1. OpenCSV CSVWriter
2. FileWriter
1. OpenCSV CSVWriter
@Inject
private Environment environment;
@Inject @Named("proddataSource") DataSource dataSource;
private FileWriter writer;
@Override
public ResultSet getDWRecords(String queryName) {
try {
java.sql.PreparedStatement ps = dataSource.getConnection().prepareStatement(environment.getProperty(queryName));
ps.setMaxRows(10000);
ResultSet rs = ps.executeQuery();
return rs;
} catch (SQLException e) {
logger.error("CreateCSVDWDumpFileImpl.getDWRecords(): " + e.getMessage());
}
return null;
}
@Override
public void createCSVDumpFromQuery(ResultSet resultSet, String[] salesforceColumnNames, String fileName) {
if (resultSet!=null){
try {
CSVWriter writer = new CSVWriter(new FileWriter(fileName), ',', CSVWriter.NO_QUOTE_CHARACTER);
writer.writeNext(salesforceColumnNames);
writer.writeAll(resultSet, false);
writer.close();
} catch (IOException e) {
logger.error("CreateCSVDWDumpFileImpl.createCSVDumpFromQuery(): " + e.getMessage());
} catch (SQLException e) {
logger.error("CreateCSVDWDumpFileImpl.createCSVDumpFromQuery(): " + e.getMessage());
}
}
}
2. FileWriter
private FileWriter writer;
public void writeToCSVFileAccount(Collection<SObject> sObjects, String[] salesforceColumnNames, String fileName) {
try {
writer = new FileWriter(fileName);
for (int firstCol = 0; firstCol < salesforceColumnNames.length; firstCol++){
writer.append(salesforceColumnNames[firstCol]);
if (firstCol < salesforceColumnNames.length-1)
writer.append(',');
}
writer.append('\n');
for (SObject sAccount : sObjects){
for (int k = 0; k < salesforceColumnNames.length; k++){
writer.append(sAccount.getField(salesforceColumnNames[k])!=null ? salesforceColumnNames[k].contains("Date") || salesforceColumnNames[k].contains("date") ? String.format("%1$tF", sAccount.getField(salesforceColumnNames[k])) : sAccount.getField(salesforceColumnNames[k]).toString() : "");
if (k < salesforceColumnNames.length-1)
writer.append(',');
}
writer.append('\n');
}
writer.flush();
writer.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
Salesforce WSC Partner Connection Session Renew when Session Timeout
Implement SessionRenewer
package com.sforce.authentication;
import javax.inject.Inject;
import javax.xml.namespace.QName;
import org.slf4j.Logger;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.env.Environment;
import com.sforce.async.AsyncApiException;
import com.sforce.async.BulkConnection;
import com.sforce.soap.partner.Connector;
import com.sforce.soap.partner.PartnerConnection;
import com.sforce.ws.ConnectionException;
import com.sforce.ws.ConnectorConfig;
import com.sforce.ws.SessionRenewer;
/**
* @author tmichels
*/
@PropertySource("classpath:salesforcesync.properties")
@Configuration
public class SalesforceAuthenticationConfigImpl implements SalesforceAuthenticationConfig {
private static final Logger logger = org.slf4j.LoggerFactory.getLogger(SalesforceAuthenticationConfigImpl.class);
@Inject private Environment environment;
private PartnerConnection partnerConnection = null;
private BulkConnection bulkConnection = null;
public ConnectorConfig productionConnectionConfig(){
ConnectorConfig config = new ConnectorConfig();
config.setUsername("username");
config.setPassword("passwordtoken");
config.setCompression(true);
return config;
}
@Bean(name="loginToProductionSalesforce")
public PartnerConnection loginToProductionSalesforce() {
try {
ConnectorConfig config = productionConnectionConfig();
config.setSessionRenewer(new productionSessionRenewer());
partnerConnection = Connector.newConnection(config);
} catch (ConnectionException e) {
logger.error("SalesforceAuthenticationConfigImpl.loginToProductionSalesforce(): " + e.getMessage());
e.printStackTrace();
}
return partnerConnection;
}
public class productionSessionRenewer implements SessionRenewer {
@Override
public SessionRenewalHeader renewSession(ConnectorConfig config) throws ConnectionException {
ConnectorConfig conConfig = productionConnectionConfig();
partnerConnection = Connector.newConnection(conConfig);
SessionRenewalHeader header = new SessionRenewalHeader();
header.name = new QName("urn:partner.soap.sforce.com", "SessionHeader");
header.headerElement = partnerConnection.getSessionHeader();
return header;
}
}
}
Test SessionRenewer
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(loader=AnnotationConfigContextLoader.class, classes={SalesforceAuthenticationConfigImpl.class})
public class SalesforceAuthenticationServiceTest {
@Inject @Named("loginToProductionSalesforce") PartnerConnection loginToProductionPartner;
@Test
public void testLoginToProductionSalesforce() {
assertNotNull(loginToProductionPartner.getConfig().getSessionId());
try {
loginToProductionPartner.logout();
assertNotNull(loginToProductionPartner.getConfig().getSessionId());
} catch (ConnectionException e) {
e.printStackTrace();
}
}
}
Salesforce List View converted to Tab
Converting a List View to a Salesforce Tab is as easy as to create a Visualforce page. Just specify the list view Id and add a custom Visualforce tab:
<apex:page> <apex:enhancedList listId="xxxxxxxxxxxxxxx" height="600" customizable="false" rowsPerPage="25"/> </apex:page>
Salesforce Integration JUnit Testing Using Spring
Spring Configuration to test integration to Salesforce using PartnerConnection, EnterpriseConnection and BulkConnection. Create a properties file called salesforcesync.properties with all the login details
salesforcesync.properties
salesforce.sandbox.username=***your username *** salesforce.sandbox.password=***your password + security token *** salesforce.sandbox.url=https://test.salesforce.com/services/Soap/u/29.0 salesforce.sandbox.enterprise.url=https://test.salesforce.com/services/Soap/c/29.0/ salesforce.sandbox.version=29.0 salesforce.production.username=***your username *** salesforce.production.password=***your password + security token *** salesforce.production.url=https://login.salesforce.com/services/Soap/u/29.0 salesforce.production.enterprise.url=https://login.salesforce.com/services/Soap/c/29.0 salesforce.production.version=29.0
Salesforce Authentication Spring Configuration
package com.sforce.authentication;
import javax.inject.Inject;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.env.Environment;
import com.sforce.async.AsyncApiException;
import com.sforce.async.BulkConnection;
import com.sforce.soap.partner.Connector;
import com.sforce.soap.partner.PartnerConnection;
import com.sforce.ws.ConnectionException;
import com.sforce.ws.ConnectorConfig;
/**
* @author tmichels
*/
@PropertySource("classpath:/salesforcesync.properties")
@Configuration
public class SalesforceAuthenticationConfigImpl implements SalesforceAuthenticationConfig {
@Inject
private Environment environment;
private PartnerConnection partnerConnection = null;
private BulkConnection bulkConnection = null;
@Bean(name="loginToProductionSalesforce")
public PartnerConnection loginToProductionSalesforce() {
try {
ConnectorConfig config = new ConnectorConfig();
config.setUsername(environment.getProperty("salesforce.production.username"));
config.setPassword(environment.getProperty("salesforce.production.password"));
partnerConnection = Connector.newConnection(config);
} catch (ConnectionException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return partnerConnection;
}
@Bean(name="loginToSandboxSalesforce")
public PartnerConnection loginToSandboxSalesforce() {
try {
ConnectorConfig config = new ConnectorConfig();
config.setUsername(environment.getProperty("salesforce.sandbox.username"));
config.setPassword(environment.getProperty("salesforce.sandbox.password"));
config.setAuthEndpoint(environment.getProperty("salesforce.sandbox.url"));
partnerConnection = Connector.newConnection(config);
} catch (ConnectionException ce) {
ce.printStackTrace();
}
return partnerConnection;
}
@Bean(name="bulkLoginToProductionSalesforce")
public BulkConnection bulkLoginToProductionSalesforce() {
try {
ConnectorConfig partnerConfig = new ConnectorConfig();
partnerConfig.setUsername(environment.getProperty("salesforce.production.username"));
partnerConfig.setPassword(environment.getProperty("salesforce.production.password"));
partnerConfig.setAuthEndpoint(environment.getProperty("salesforce.production.url"));
partnerConnection = Connector.newConnection(partnerConfig);
ConnectorConfig config = new ConnectorConfig();
config.setSessionId(partnerConfig.getSessionId());
String soapEndpoint = partnerConfig.getServiceEndpoint();
String restEndpoint = soapEndpoint.substring(0, soapEndpoint.indexOf("Soap/"))
+ "async/" + environment.getProperty("salesforce.sandbox.version");
config.setRestEndpoint(restEndpoint);
// This should only be false when doing debugging.
config.setCompression(true);
// Set this to true to see HTTP requests and responses on stdout
config.setTraceMessage(false);
bulkConnection = new BulkConnection(config);
} catch (AsyncApiException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ConnectionException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return bulkConnection;
}
@Bean(name="bulkLoginToSandboxSalesforce")
public BulkConnection bulkLoginToSandboxSalesforce() {
try {
ConnectorConfig partnerConfig = new ConnectorConfig();
partnerConfig.setUsername(environment.getProperty("salesforce.sandbox.username"));
partnerConfig.setPassword(environment.getProperty("salesforce.sandbox.password"));
partnerConfig.setAuthEndpoint(environment.getProperty("salesforce.sandbox.url"));
partnerConnection = Connector.newConnection(partnerConfig);
ConnectorConfig bulkconfig = new ConnectorConfig();
bulkconfig.setSessionId(partnerConfig.getSessionId());
String soapEndpoint = partnerConfig.getServiceEndpoint();
String restEndpoint = soapEndpoint.substring(0, soapEndpoint.indexOf("Soap/"))
+ "async/" + environment.getProperty("salesforce.sandbox.version");
bulkconfig.setRestEndpoint(restEndpoint);
// This should only be false when doing debugging.
bulkconfig.setCompression(true);
// Set this to true to see HTTP requests and responses on stdout
bulkconfig.setTraceMessage(false);
bulkConnection = new BulkConnection(bulkconfig);
} catch (AsyncApiException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ConnectionException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return bulkConnection;
}
@Bean(name="loginToSalesforceSandboxEnterprise")
public EnterpriseConnection loginToSandboxSalesforceEnterprise() {
try {
ConnectorConfig config = new ConnectorConfig();
config.setUsername(environment.getProperty("salesforce.sandbox.username"));
config.setPassword(environment.getProperty("salesforce.sandbox.password"));
config.setAuthEndpoint(environment.getProperty("salesforce.sandbox.enterprise.url"));
config.setValidateSchema(true);
config.setCompression(false);
config.setConnectionTimeout(360000);
config.setTraceMessage(true);
config.setPrettyPrintXml(true);
enterpriseConnection = new EnterpriseConnection(config);
} catch (ConnectionException ce) {
ce.printStackTrace();
}
return enterpriseConnection;
}
@Bean(name="loginToSalesforceProductionEnterprise")
public EnterpriseConnection loginToProductionSalesforceEnterprise() {
try {
ConnectorConfig config = new ConnectorConfig();
config.setUsername(environment.getProperty("salesforce.production.username"));
config.setPassword(environment.getProperty("salesforce.production.password"));
config.setAuthEndpoint(environment.getProperty("salesforce.production.url"));
config.setValidateSchema(true);
config.setCompression(false);
config.setConnectionTimeout(360000);
config.setTraceMessage(true);
config.setPrettyPrintXml(true);
enterpriseConnection = new EnterpriseConnection(config);
} catch (ConnectionException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return enterpriseConnection;
}
}
Junit test class to assert login was successful
package com.sforce.authentication;
import static org.junit.Assert.*;
import javax.inject.Inject;
import javax.inject.Named;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.test.context.support.AnnotationConfigContextLoader;
import com.sforce.async.BulkConnection;
import com.sforce.authentication.SalesforceAuthenticationConfig;
import com.sforce.authentication.SalesforceAuthenticationConfigImpl;
import com.sforce.soap.partner.PartnerConnection;
import com.sforce.ws.ConnectionException;
/**
* @author tmichels
*/
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(loader=AnnotationConfigContextLoader.class, classes={SalesforceAuthenticationConfigImpl.class})
public class SalesforceAuthenticationServiceTest {
@Inject @Named("loginToProductionSalesforce") PartnerConnection loginToProductionPartner;
@Inject @Named("loginToSandboxSalesforce") PartnerConnection loginToSandboxPartner;
@Inject @Named("bulkLoginToProductionSalesforce") BulkConnection loginToProductionBulkConnection;
@Inject @Named("bulkLoginToSandboxSalesforce") BulkConnection loginToSandboxBulkConnection;
@Inject @Named("loginToSalesforceSandboxEnterprise") EnterpriseConnection loginToSalesforceEnterpriseConnection;
@Test
public void testLoginToProductionSalesforce() {
assertNotNull(loginToProductionPartner.getConfig().getSessionId());
try {
loginToProductionPartner.logout();
} catch (ConnectionException e) {
e.printStackTrace();
}
}
@Test
public void testLoginToSandboxSalesforce(){
assertNotNull(loginToSandboxPartner.getConfig().getSessionId());
try {
loginToSandboxPartner.logout();
} catch (ConnectionException e) {
e.printStackTrace();
}
}
@Test
public void testBulkLoginToProductionSalesforce(){
assertNotNull(loginToProductionBulkConnection.getConfig().getSessionId());
}
@Test
public void testBulkLoginToSandboxSalesforce(){
assertNotNull(loginToSandboxBulkConnection.getConfig().getSessionId());
}
@Test
public void testLoginToSalesforceEnterprise(){
assertNotNull(loginToSalesforceEnterpriseConnection.getConfig().getSessionId());
}
}
Salesforce Streaming Api using Spring MVC
Check out my Salesforce Streaming Spring MVC project
http://cloudsole-streaming.herokuapp.com/
Find the code here:
https://github.com/thysmichels/cloudsole-force.com-streaming-web
Using Salesforce Streaming in Java project:
Push Topic Factory
package com.example.service;
import com.force.sdk.streaming.client.ForceBayeuxClient;
import com.force.sdk.streaming.client.ForceStreamingClientModule;
import com.force.sdk.streaming.client.PushTopicManager;
import com.force.sdk.streaming.exception.ForceStreamingException;
import com.force.sdk.streaming.model.PushTopic;
import com.google.inject.Guice;
import com.google.inject.Injector;
public class PushTopicFactory {
public Injector createInjector(){
Injector injector = Guice.createInjector(new ForceStreamingClientModule());
return injector;
}
public ForceBayeuxClient createClient(Injector injector){
ForceBayeuxClient client = injector.getInstance(ForceBayeuxClient.class);
return client;
}
public PushTopicManager createPushTopManager(Injector injector){
PushTopicManager pushTopicManager = injector.getInstance(PushTopicManager.class);
return pushTopicManager;
}
public PushTopic getTopicByName(PushTopicManager pushTopicManager, String topicName) throws ForceStreamingException{
PushTopic topic = pushTopicManager.getTopicByName(topicName);
return topic;
}
public PushTopic createPushTopic(PushTopicManager pushTopicManager, String name, Double apiVersion, String query, String description){
PushTopic createTopic = pushTopicManager.createPushTopic(new PushTopic(name, apiVersion, query, description));
return createTopic;
}
public PushTopic pushTopicFactory(String topicName) throws ForceStreamingException{
PushTopicFactory pushTopicFactory = new PushTopicFactory();
Injector injector = pushTopicFactory.createInjector();
pushTopicFactory.createClient(injector);
PushTopicManager publicTopicManager = pushTopicFactory.createPushTopManager(injector);
return pushTopicFactory.getTopicByName(publicTopicManager, topicName);
}
public ForceBayeuxClient createPushTopicClientFactory(){
PushTopicFactory pushTopicClientFactory = new PushTopicFactory();
ForceBayeuxClient fbClient = pushTopicClientFactory.createClient(pushTopicClientFactory.createInjector());
return fbClient;
}
}
Streaming Service
package com.example.service;
import java.io.IOException;
import org.cometd.bayeux.Message;
import org.cometd.bayeux.client.ClientSessionChannel;
import org.eclipse.jetty.util.log.Log;
import com.example.service.PushTopicFactory;
import com.force.sdk.streaming.client.ForceBayeuxClient;
import com.force.sdk.streaming.client.PushTopicManager;
import com.force.sdk.streaming.exception.ForceStreamingException;
import com.force.sdk.streaming.model.PushTopic;
import com.google.inject.Injector;
public class StreamingService {
public void pushTopicSubScriber(){
PushTopicFactory pushTopicFactory = new PushTopicFactory();
Injector injector = pushTopicFactory.createInjector();
ForceBayeuxClient client = pushTopicFactory.createClient(injector);
PushTopicManager publicTopicManager = pushTopicFactory.createPushTopManager(injector);
PushTopic createTopic = pushTopicFactory.createPushTopic(publicTopicManager, "NewAccountPushTopic", 27.0, "select Id, Name from Account", "New Push Topic");
PushTopic topic = null;
try {
topic = pushTopicFactory.getTopicByName(publicTopicManager, createTopic.getName());
} catch (ForceStreamingException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
try {
client.subscribeTo(topic, new ClientSessionChannel.MessageListener()
{
public void onMessage(ClientSessionChannel channel, Message message)
{
Log.info(message.getJSON());
}
});
} catch (InterruptedException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
CloudSole Salesforce REST Feed
I was looking at building a easy way to POST information from your salesforce org to my Public Feed. This feed can be used to publish and data from you salesforce org to the public.
Below is some code sameples on ways to POST to the Public Feed:
curl:
curl -H "Accept: application/json" -H "Content-type: application/json" -X POST -d '{"name": "I love Cloudsole Salesforce Feed", "message": "This is awesome I can post my message on the public feed"}' http://cloudsole-feed.herokuapp.com/sfdc/public/feed
Apex code:
Note:
Make sure to add http://cloudsole-feed.herokuapp.com to you Remote Site Details.
Code:
public with sharing class HttpCalloutToFeed
{
public static void sentMessageToFeed()
{
Http http = new Http();
HttpRequest httpreq = new HttpRequest();
httpreq.setEndpoint('http://cloudsole-feed.herokuapp.com/sfdc/public/feed');
String message = '{"name": "Message from ' + UserInfo.getFirstName() + '", "message": "My Organization ' + UserInfo.getOrganizationName() + ' located in ' + UserInfo.getLocale() + ' loves CloudSole public feed"}';
httpreq.setHeader('Accept', 'application/json');
httpreq.setHeader('Content-type', 'application/json');
httpreq.setBody(message);
httpreq.setMethod('POST');
HttpResponse response = http.send(httpreq);
System.debug(response.getBody());
}
}
Java code:
package com.thysmichels.main;
import java.io.IOException;
import org.apache.http.HttpResponse;
import org.apache.http.client.HttpClient;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.entity.StringEntity;
import org.apache.http.impl.client.DefaultHttpClient;
public class CloudSoleFeedMain
{
public static void main(String[] args)
{
HttpClient client = new DefaultHttpClient();
HttpPost post = new HttpPost("http://cloudsole-feed.herokuapp.com/sfdc/public/feed");
try
{
post.setEntity(new StringEntity("{\"name\": \"Message from Java Class\", \"message\": \"This is awesome I can post my message on the public feed\"}"));
post.setHeader("Accept", "application/json");
post.setHeader("Content-Type", "application/json");
HttpResponse response = client.execute(post);
} catch (IOException e) {
e.printStackTrace();
}
}
}
https://github.com/thysmichels/cloudsole_hibernate_rest_salesforce_feed
Java Code: Apex Bulk Loading MySQL
Creating a bulk sync between MySQL and Salesforce using the Salesforce Bulk API. I created a Bulk Loader that connects to MySQL exports data to CSV and imports it into Salesforce. In this example I am syncing the Lead object.
package com.thysmichels;
import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.*;
import au.com.bytecode.opencsv.CSVWriter;
import com.sforce.async.*;
import com.sforce.soap.partner.PartnerConnection;
import com.sforce.ws.ConnectionException;
import com.sforce.ws.ConnectorConfig;
public class BulkLoader {
// Salesforce.com credentials
private String userName = "yourusername";
private String password = "yourpasswordandsecuritytoken";
// the sObject being uploaded
private String sObject = "Lead";
// the CSV file being uploaded - either manually or from MySQL resultset
// ie /Users/Me/Desktop/BulkAPI/myfile.csv
private String csvFileName = "/Users/tmichels/Documents/lead.csv";
// MySQL connection URL
String mySqlUrl = "jdbc:mysql://localhost/RLSFDCArchive?user=admin&password=admin";
// the query that returns results from MySQL
String mySqlQuery = "SELECT LASTNAME, COMPANY, STATUS, EMAIL FROM RLSFDCArchive.LEAD limit 10";
private BufferedReader console = null;
public static void main(String[] args) throws AsyncApiException, ConnectionException, IOException
{
BulkLoader example = new BulkLoader();
example.run();
}
public void run() throws AsyncApiException, ConnectionException, IOException
{
if (createCSVFromMySQL())
{
System.out.println("Submitting CSV file to Salesforce.com");
runJob(sObject, userName, password, csvFileName);
}
}
private boolean createCSVFromMySQL() {
System.out.println("Fetching records from MySQL");
Connection conn = null;
ResultSet rs = null;
boolean success = false;
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn = DriverManager.getConnection(mySqlUrl);
Statement s = conn.createStatement ();
s.executeQuery(mySqlQuery);
rs = s.getResultSet();
// dump the contents to the console
//System.out.println(rs.getMetaData().getColumnName(1));
/* while (rs.next ()){
String LnVal = rs.getString ("LASTNAME");
String CompanyVal = rs.getString ("COMPANY");
System.out.println ("LastName = "+LnVal+", Company = "+CompanyVal);
} */
// write the result set to the CSV file
if (rs != null) {
CSVWriter writer = new CSVWriter(new FileWriter(csvFileName), ',');
writer.writeAll(rs, true);
writer.close();
System.out.println("Successfully fetched records from MySQL");
success = true;
}
} catch (Exception e) {
System.err.println("Cannot connect to database server");
success = false;
} finally {
if (rs != null) {
try {
rs.close();
System.out.println("Resultset terminated");
} catch (Exception e1) { /* ignore close errors */
}
}
if (conn != null) {
try {
conn.close();
System.out.println("Database connection terminated");
} catch (Exception e2) { /* ignore close errors */
}
}
}
return success;
}
public void runJob(String sobjectType, String userName, String password,
String sampleFileName) throws AsyncApiException, ConnectionException,
IOException
{
BulkConnection connection = getBulkConnection(userName, password);
JobInfo job = createJob(sobjectType, connection);
List<BatchInfo> batchInfoList = createBatchesFromCSVFile(connection, job, sampleFileName);
closeJob(connection, job.getId());
awaitCompletion(connection, job, batchInfoList);
checkResults(connection, job, batchInfoList);
}
private void awaitCompletion(BulkConnection connection, JobInfo job,
List<BatchInfo> batchInfoList) throws AsyncApiException {
long sleepTime = 0L;
Set<String> incomplete = new HashSet<String>();
for (BatchInfo bi : batchInfoList) {
incomplete.add(bi.getId());
}
while (!incomplete.isEmpty()) {
try {
Thread.sleep(sleepTime);
} catch (InterruptedException e) {
}
System.out.println("Awaiting results..." + incomplete.size());
sleepTime = 10000L;
BatchInfo[] statusList = connection.getBatchInfoList(job.getId())
.getBatchInfo();
for (BatchInfo b : statusList) {
if (b.getState() == BatchStateEnum.Completed
|| b.getState() == BatchStateEnum.Failed) {
if (incomplete.remove(b.getId())) {
System.out.println("BATCH STATUS:\n" + b);
}
}
}
}
}
private void checkResults(BulkConnection connection, JobInfo job,
List<BatchInfo> batchInfoList) throws AsyncApiException, IOException {
// batchInfoList was populated when batches were created and submitted
for (BatchInfo b : batchInfoList) {
CSVReader rdr = new CSVReader(connection.getBatchResultStream(
job.getId(), b.getId()));
List<String> resultHeader = rdr.nextRecord();
int resultCols = resultHeader.size();
List<String> row;
while ((row = rdr.nextRecord()) != null) {
Map<String, String> resultInfo = new HashMap<String, String>();
for (int i = 0; i < resultCols; i++) {
resultInfo.put(resultHeader.get(i), row.get(i));
}
boolean success = Boolean.valueOf(resultInfo.get("Success"));
boolean created = Boolean.valueOf(resultInfo.get("Created"));
String id = resultInfo.get("Id");
String error = resultInfo.get("Error");
if (success && created) {
System.out.println("Created row with id " + id);
} else if (!success) {
System.out.println("Failed with error: " + error);
}
}
}
}
private void closeJob(BulkConnection connection, String jobId)
throws AsyncApiException {
JobInfo job = new JobInfo();
job.setId(jobId);
job.setState(JobStateEnum.Closed);
connection.updateJob(job);
}
private List<BatchInfo> createBatchesFromCSVFile(BulkConnection connection,
JobInfo jobInfo, String csvFileName) throws IOException,
AsyncApiException {
List<BatchInfo> batchInfos = new ArrayList<BatchInfo>();
BufferedReader rdr = new BufferedReader(new InputStreamReader(
new FileInputStream(csvFileName)));
// read the CSV header row
byte[] headerBytes = (rdr.readLine() + "\n").getBytes("UTF-8");
int headerBytesLength = headerBytes.length;
File tmpFile = File.createTempFile("bulkAPIInsert", ".csv");
// Split the CSV file into multiple batches
try {
FileOutputStream tmpOut = new FileOutputStream(tmpFile);
int maxBytesPerBatch = 10000000; // 10 million bytes per batch
int maxRowsPerBatch = 10000; // 10 thousand rows per batch
int currentBytes = 0;
int currentLines = 0;
String nextLine;
while ((nextLine = rdr.readLine()) != null) {
byte[] bytes = (nextLine + "\n").getBytes("UTF-8");
// Create a new batch when our batch size limit is reached
if (currentBytes + bytes.length > maxBytesPerBatch
|| currentLines > maxRowsPerBatch) {
createBatch(tmpOut, tmpFile, batchInfos, connection, jobInfo);
currentBytes = 0;
currentLines = 0;
}
if (currentBytes == 0) {
tmpOut = new FileOutputStream(tmpFile);
tmpOut.write(headerBytes);
currentBytes = headerBytesLength;
currentLines = 1;
}
tmpOut.write(bytes);
currentBytes += bytes.length;
currentLines++;
}
// Finished processing all rows
// Create a final batch for any remaining data
if (currentLines > 1) {
createBatch(tmpOut, tmpFile, batchInfos, connection, jobInfo);
}
} finally {
tmpFile.delete();
}
return batchInfos;
}
private void createBatch(FileOutputStream tmpOut, File tmpFile,
List<BatchInfo> batchInfos, BulkConnection connection, JobInfo jobInfo)
throws IOException, AsyncApiException {
tmpOut.flush();
tmpOut.close();
FileInputStream tmpInputStream = new FileInputStream(tmpFile);
try {
BatchInfo batchInfo = connection.createBatchFromStream(jobInfo,
tmpInputStream);
System.out.println(batchInfo);
batchInfos.add(batchInfo);
} finally {
tmpInputStream.close();
}
}
private BulkConnection getBulkConnection(String userName, String password)
throws ConnectionException, AsyncApiException {
ConnectorConfig partnerConfig = new ConnectorConfig();
partnerConfig.setUsername(userName);
partnerConfig.setPassword(password);
partnerConfig.setAuthEndpoint("https://www.salesforce.com/services/Soap/u/25.0");
// Creating the connection automatically handles login and stores
// the session in partnerConfig
new PartnerConnection(partnerConfig);
// When PartnerConnection is instantiated, a login is implicitly
// executed and, if successful,
// a valid session is stored in the ConnectorConfig instance.
// Use this key to initialize a RestConnection:
ConnectorConfig config = new ConnectorConfig();
config.setSessionId(partnerConfig.getSessionId());
// The endpoint for the Bulk API service is the same as for the normal
// SOAP uri until the /Soap/ part. From here it's '/async/versionNumber'
String soapEndpoint = partnerConfig.getServiceEndpoint();
String apiVersion = "25.0";
String restEndpoint = soapEndpoint.substring(0, soapEndpoint
.indexOf("Soap/"))
+ "async/" + apiVersion;
config.setRestEndpoint(restEndpoint);
// This should only be false when doing debugging.
config.setCompression(true);
// Set this to true to see HTTP requests and responses on stdout
config.setTraceMessage(false);
BulkConnection connection = new BulkConnection(config);
return connection;
}
private JobInfo createJob(String sobjectType, BulkConnection connection)
throws AsyncApiException
{
JobInfo job = new JobInfo();
job.setObject(sobjectType);
job.setOperation(OperationEnum.insert);
job.setContentType(ContentType.CSV);
job = connection.createJob(job);
System.out.println(job);
return job;
}
}
Below is the output expected:
Fetching records from MySQL Successfully fetched records from MySQL Resultset terminated Database connection terminated Submitting CSV file to Salesforce.com [JobInfo id='75060000000LtROAA0' operation='insert' object='Lead' createdById='00560000001mciHAAQ' createdDate='java.util.GregorianCalendar[time=1357172058000,areFieldsSet=true,areAllFieldsSet=true,lenient=true,zone=sun.util.calendar.ZoneInfo[id="GMT",offset=0,dstSavings=0,useDaylight=false,transitions=0,lastRule=null],firstDayOfWeek=1,minimalDaysInFirstWeek=1,ERA=1,YEAR=2013,MONTH=0,WEEK_OF_YEAR=1,WEEK_OF_MONTH=1,DAY_OF_MONTH=3,DAY_OF_YEAR=3,DAY_OF_WEEK=5,DAY_OF_WEEK_IN_MONTH=1,AM_PM=0,HOUR=0,HOUR_OF_DAY=0,MINUTE=14,SECOND=18,MILLISECOND=0,ZONE_OFFSET=0,DST_OFFSET=0]' systemModstamp='java.util.GregorianCalendar[time=1357172058000,areFieldsSet=true,areAllFieldsSet=true,lenient=true,zone=sun.util.calendar.ZoneInfo[id="GMT",offset=0,dstSavings=0,useDaylight=false,transitions=0,lastRule=null],firstDayOfWeek=1,minimalDaysInFirstWeek=1,ERA=1,YEAR=2013,MONTH=0,WEEK_OF_YEAR=1,WEEK_OF_MONTH=1,DAY_OF_MONTH=3,DAY_OF_YEAR=3,DAY_OF_WEEK=5,DAY_OF_WEEK_IN_MONTH=1,AM_PM=0,HOUR=0,HOUR_OF_DAY=0,MINUTE=14,SECOND=18,MILLISECOND=0,ZONE_OFFSET=0,DST_OFFSET=0]' state='Open' externalIdFieldName='null' concurrencyMode='Parallel' contentType='CSV' numberBatchesQueued='0' numberBatchesInProgress='0' numberBatchesCompleted='0' numberBatchesFailed='0' numberBatchesTotal='0' numberRecordsProcessed='0' numberRetries='0' apiVersion='25.0' assignmentRuleId='null' numberRecordsFailed='0' totalProcessingTime='0' apiActiveProcessingTime='0' apexProcessingTime='0' ] [BatchInfo id='75160000000sEGGAA2' jobId='75060000000LtROAA0' state='Queued' stateMessage='null' createdDate='java.util.GregorianCalendar[time=1357172059000,areFieldsSet=true,areAllFieldsSet=true,lenient=true,zone=sun.util.calendar.ZoneInfo[id="GMT",offset=0,dstSavings=0,useDaylight=false,transitions=0,lastRule=null],firstDayOfWeek=1,minimalDaysInFirstWeek=1,ERA=1,YEAR=2013,MONTH=0,WEEK_OF_YEAR=1,WEEK_OF_MONTH=1,DAY_OF_MONTH=3,DAY_OF_YEAR=3,DAY_OF_WEEK=5,DAY_OF_WEEK_IN_MONTH=1,AM_PM=0,HOUR=0,HOUR_OF_DAY=0,MINUTE=14,SECOND=19,MILLISECOND=0,ZONE_OFFSET=0,DST_OFFSET=0]' systemModstamp='java.util.GregorianCalendar[time=1357172059000,areFieldsSet=true,areAllFieldsSet=true,lenient=true,zone=sun.util.calendar.ZoneInfo[id="GMT",offset=0,dstSavings=0,useDaylight=false,transitions=0,lastRule=null],firstDayOfWeek=1,minimalDaysInFirstWeek=1,ERA=1,YEAR=2013,MONTH=0,WEEK_OF_YEAR=1,WEEK_OF_MONTH=1,DAY_OF_MONTH=3,DAY_OF_YEAR=3,DAY_OF_WEEK=5,DAY_OF_WEEK_IN_MONTH=1,AM_PM=0,HOUR=0,HOUR_OF_DAY=0,MINUTE=14,SECOND=19,MILLISECOND=0,ZONE_OFFSET=0,DST_OFFSET=0]' numberRecordsProcessed='0' numberRecordsFailed='0' totalProcessingTime='0' apiActiveProcessingTime='0' apexProcessingTime='0' ] Awaiting results...1 Awaiting results...1 BATCH STATUS: [BatchInfo id='75160000000sEGGAA2' jobId='75060000000LtROAA0' state='Completed' stateMessage='null' createdDate='java.util.GregorianCalendar[time=1357172059000,areFieldsSet=true,areAllFieldsSet=true,lenient=true,zone=sun.util.calendar.ZoneInfo[id="GMT",offset=0,dstSavings=0,useDaylight=false,transitions=0,lastRule=null],firstDayOfWeek=1,minimalDaysInFirstWeek=1,ERA=1,YEAR=2013,MONTH=0,WEEK_OF_YEAR=1,WEEK_OF_MONTH=1,DAY_OF_MONTH=3,DAY_OF_YEAR=3,DAY_OF_WEEK=5,DAY_OF_WEEK_IN_MONTH=1,AM_PM=0,HOUR=0,HOUR_OF_DAY=0,MINUTE=14,SECOND=19,MILLISECOND=0,ZONE_OFFSET=0,DST_OFFSET=0]' systemModstamp='java.util.GregorianCalendar[time=1357172060000,areFieldsSet=true,areAllFieldsSet=true,lenient=true,zone=sun.util.calendar.ZoneInfo[id="GMT",offset=0,dstSavings=0,useDaylight=false,transitions=0,lastRule=null],firstDayOfWeek=1,minimalDaysInFirstWeek=1,ERA=1,YEAR=2013,MONTH=0,WEEK_OF_YEAR=1,WEEK_OF_MONTH=1,DAY_OF_MONTH=3,DAY_OF_YEAR=3,DAY_OF_WEEK=5,DAY_OF_WEEK_IN_MONTH=1,AM_PM=0,HOUR=0,HOUR_OF_DAY=0,MINUTE=14,SECOND=20,MILLISECOND=0,ZONE_OFFSET=0,DST_OFFSET=0]' numberRecordsProcessed='10' numberRecordsFailed='1' totalProcessingTime='753' apiActiveProcessingTime='648' apexProcessingTime='517' ]
Java Code: Salesforce Export into Excel
Export Salesforce records into Excel. In my example I read records from Excel and also from Salesforce. Reuse my code by providing your own username and password to your Salesforce org to create more advanced SFDC to Excel integrations. You will need to download or add jxl to your maven project:
http://mvnrepository.com/artifact/net.sourceforge.jexcelapi/jxl
package com.thys.michels;
import java.io.File;
import java.io.IOException;
import java.util.Locale;
import com.sforce.soap.partner.Connector;
import com.sforce.soap.partner.DescribeSObjectResult;
import com.sforce.soap.partner.PartnerConnection;
import com.sforce.soap.partner.QueryResult;
import com.sforce.soap.partner.sobject.SObject;
import com.sforce.ws.ConnectionException;
import com.sforce.ws.ConnectorConfig;
import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.CellView;
import jxl.WorkbookSettings;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
public class ForcedotcomExcelToolkit {
private WritableCellFormat times;
private String inputFile;
private WritableCellFormat timesBoldUnderline;
public void setInputFile(String inputFile) {
this.inputFile = inputFile;
}
public void setOutputFile(String inputFile) {
this.inputFile = inputFile;
}
public String[] read() throws IOException {
File inputWorkbook = new File(inputFile);
Workbook w;
try {
w = Workbook.getWorkbook(inputWorkbook);
// Get the first sheet
Sheet sheet = w.getSheet(0);
// Loop over first 10 column and lines
String arremail[] = new String[246];
for (int j = 0; j < sheet.getColumns(); j++)
{
for (int i = 0; i < sheet.getRows(); i++)
{
Cell cell = sheet.getCell(j, i);
CellType type = cell.getType();
if (cell.getType() == CellType.LABEL) {
arremail[i] = cell.getContents();
}
}
}
return arremail;
}
catch (BiffException e)
{
e.printStackTrace();
return null;
}
}
public void write(String[][] arremail) throws IOException, WriteException {
File file = new File(inputFile);
WorkbookSettings wbSettings = new WorkbookSettings();
wbSettings.setLocale(new Locale("en", "EN"));
WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings);
workbook.createSheet("Report", 0);
WritableSheet excelSheet = workbook.getSheet(0);
createLabel(excelSheet);
System.out.println(arremail.length);
for (int i = 1; i < arremail.length; i++)
{
// First column
if (arremail[i-1][0]!=null)
addLabel(excelSheet, 0, i, arremail[i-1][0].toString());
// Second column
if (arremail[i-1][1]!=null)
addLabel(excelSheet, 1, i, arremail[i-1][1].toString());
}
//createContent(excelSheet, arremail);
System.out.println("Done 8");
workbook.write();
workbook.close();
}
//private void createContent(WritableSheet sheet, String[][] arremail) throws WriteException,RowsExceededException {
// Write a few number
//}
private void createLabel(WritableSheet sheet)
throws WriteException {
// Lets create a times font
WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10);
// Define the cell format
times = new WritableCellFormat(times10pt);
// Lets automatically wrap the cells
times.setWrap(true);
// Create create a bold font with unterlines
WritableFont times10ptBoldUnderline = new WritableFont(WritableFont.TIMES, 10, WritableFont.BOLD, false,
UnderlineStyle.SINGLE);
timesBoldUnderline = new WritableCellFormat(times10ptBoldUnderline);
// Lets automatically wrap the cells
timesBoldUnderline.setWrap(true);
CellView cv = new CellView();
cv.setFormat(times);
cv.setFormat(timesBoldUnderline);
cv.setAutosize(true);
// Write a few headers
addCaption(sheet, 0, 0, "Email");
addCaption(sheet, 1, 0, "Phone");
}
private void addCaption(WritableSheet sheet, int column, int row, String s)
throws RowsExceededException, WriteException {
Label label;
label = new Label(column, row, s, timesBoldUnderline);
sheet.addCell(label);
}
private void addLabel(WritableSheet sheet, int column, int row, String s)
throws WriteException, RowsExceededException {
Label label;
label = new Label(column, row, s, times);
sheet.addCell(label);
}
public String[][] SFDCEmailResult(String [] stremail)
{
//Create a new connectionconfig to your Salesforce Org
ConnectorConfig sfconfig = new ConnectorConfig();
//Use your salesforce username = email
sfconfig.setUsername("yourusername");
//Use your saleforce password with your security token look like: passwordjeIzBAQKkR6FBW8bw5HbVkkkk
sfconfig.setPassword("yourpassword");
sfconfig.setConnectionTimeout(99999);
PartnerConnection partnercon;
try
{
partnercon = Connector.newConnection(sfconfig);
DescribeSObjectResult describeGlobalResult = partnercon.describeSObject("Contact");
//DescribeGlobalSObjectResult[] sobjectResults = describeGlobalResult.getSobjects();
String [][] emailresult = new String[246][246];
for (int k = 0; k < stremail.length; k++)
{
String QueryStr = "SELECT Email, Phone FROM Contact where Email='" + stremail[k]+"'";
QueryResult queryResults = partnercon.query(QueryStr);
if (queryResults.getSize() > 0)
{
for (SObject s: queryResults.getRecords())
{
emailresult[k][0] = s.getField("Email").toString();
if (s.getField("Phone") != null)
emailresult[k][1] = s.getField("Phone").toString();
else
emailresult[k][1]="";
}
}
}
return emailresult;
}
catch(ConnectionException e)
{
e.printStackTrace();
return null;
}
}
public static void main(String[] args) throws IOException, WriteException
{
ForcedotcomExcelToolkit outemailexcelinput = new ForcedotcomExcelToolkit();
outemailexcelinput.setInputFile("/Users/tmichels/Documents/SFDCEmail.xls");
System.out.println("Complete Reading Emails from Excel");
ForcedotcomExcelToolkit outemailexceloutput = new ForcedotcomExcelToolkit();
outemailexceloutput.setOutputFile("/Users/tmichels/Documents/SFDCEmailResults.xls");
outemailexceloutput.write(outemailexceloutput.SFDCEmailResult(outemailexcelinput.read()));
System.out.println("Complete Writing Emails");
}
Apex Code: Visualforce Google Maps Address mapping
Below is the code to display Google map of the Billing Address of an Account. Add the Visualforce page to your page layout.
<apex:page standardController="Account">
<head>
<script type="text/javascript" src="https://maps.google.com/maps/api/js?sensor=false"></script>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></script>
<script type="text/javascript
$(document).ready(function()
{
var myOptions =
{
zoom: 15,
mapTypeId: google.maps.MapTypeId.ROADMAP,
mapTypeControl: false
}
var map;
var marker;
var geocoder = new google.maps.Geocoder();
var address = "{!Account.BillingStreet}, " + "{!Account.BillingCity}, " + "{!Account.BillingPostalCode}, " + "{!Account.BillingCountry}";
var infowindow = new google.maps.InfoWindow({
content: "<b>{!Account.Name}</b><br>{!Account.BillingStreet}<br>{!Account.BillingCity}, {!Account.BillingPostalCode}<br>{!Account.BillingCountry}"
});
geocoder.geocode( { address: address}, function(results, status) {
if (status == google.maps.GeocoderStatus.OK && results.length) {
if (status != google.maps.GeocoderStatus.ZERO_RESULTS) {
//create map
map = new google.maps.Map(document.getElementById("map"), myOptions);
//center map
map.setCenter(results[0].geometry.location);
//create marker
marker = new google.maps.Marker({
position: results[0].geometry.location,
map: map,
title: "{!Account.Name}"
});
//add listeners
google.maps.event.addListener(marker, 'click', function() {
infowindow.open(map,marker);
});
google.maps.event.addListener(infowindow, 'closeclick', function() {
map.setCenter(marker.getPosition());
});
}
} else {
$('#map').css({'height' : '15px'});
$('#map').html("{!Account.Name}'s billing address could not be found, confirm the address is correct.");
resizeIframe();
}
});
function resizeIframe() {
var me = window.name;
if (me) {
var iframes = parent.document.getElementsByName(me);
if (iframes && iframes.length == 1) {
height = document.body.offsetHeight;
iframes[0].style.height = height + "px";
}
}
}
});
</script>
<style>
#map {
font-family: Arial;
font-size:12px;
line-height:normal !important;
height:250px;
background:transparent;
}
</style>
</head>
<body>
<div id="map"></div>
</body>
</apex:page>
