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");
    }
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: