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");
}
Thys Michels Blog 