The implications of this picture are either highly disturbing or highly exciting, or both. But they can't be neither.

Some Belated Tricks with Java and Excel VBA

Several years ago, when I was working for Dr. Jean-Claude Bradley at Drexel University, I posted on a number of programming projects on the UsefulChem blog site (http://usefulchem.blogspot.com/).  I recall a flurry of interest in how I got Excel VBA programs to call Java programs and how Java could make Excel spreadsheets.  It’s probably a lot easier now, but here were my tricks below.  First part is how to get VBA to run any software and wait for its completion:

________________________________________

‘Declarations

Private Type PROCESS_INFORMATION

    hProcess As Long

    hThread As Long

    dwProcessId As Long

    dwThread As Long

End Type

Private Type STARTUPINFO

    cb As Long

    lpReserved As String

    lpDesktop As String

    dwX As Long

    dwY As Long

    dwXSize As Long

    dwYSize As Long

    dwXCountChars As Long

    dwYCountChars As Long

    dwFillAttribute As Long

    dwFlags As Long

    wShowWindow As Integer

    cbReserved2 As Long

    lpReserved2 As Long

    hStdInput As Long

    hStdOutput As Long

    hStdError As Long

End Type

Private Declare Function CreateProcess Lib "Kernel32" Alias "CreateProcessA" _

                                 (ByVal lpApplicationName As String, ByVal lpCommandLine As String, _

                                  ByVal lpProcessAttributes As Long, ByVal lpThreadAttributes As Long, _

                                  ByVal bInheritHandles As Long, ByVal dwCreationFlags As Long, _

                                  lpEnvironment As Any, ByVal lpCurrentDirectory As String, _

                                  lpStartupInfo As STARTUPINFO, lpProcessInformation As PROCESS_INFORMATION) As Long

Private Declare Function CloseHandle Lib "Kernel32" (ByVal hObject As Long) As Long

Private Declare Function WaitForSingleObject Lib "Kernel32" _

                                  (ByVal hHandle As Long, ByVal dwMilliseconds As Long) As Long

 ‘The actual function.

Private Function RunWaitApp(lpCommandLine As String, wShowWindow As Integer, bWait As Boolean, _

                            Optional lWaitTime As Long) As Boolean

  Dim sinfo As STARTUPINFO

  Dim pinfo As PROCESS_INFORMATION

  Dim res As Long

  Dim lWait As Long

 

  If bWait Then

    lWait = lWaitTime

  Else

    Shell lpCommandLine, wShowWindow

    RunWaitApp = True

    Exit Function

  End If

 

  sinfo.cb = Len(sinfo)

  sinfo.wShowWindow = wShowWindow

  res = CreateProcess(vbNullString, lpCommandLine, 0, 0, True, &H20, ByVal 0&, vbNullString, sinfo, pinfo)

                 

  If res <> 0 Then

    Do

      res = WaitForSingleObject(pinfo.hProcess, lWait)

    

      If res <> &H102& Then

        Exit Do

      End If

 

      DoEvents

    Loop While True

   

    CloseHandle pinfo.hProcess

    RunWaitApp = True

  Else

    RunWaitApp = False

  End If

End Function

_______________________________________

A bit windy, I know, but you need write the module only once, then copy, paste, and call it wherever needed.

The code to create Excel spreadsheets from within Java then required a special library import, of which I could only find one at the time but am sure now there are many others.  The full code is:

________________________________________

import java.io.*;

import java.util.*;
import org.apache.poi.hssf.usermodel.*; // The special import
 
public class CreateXLS
{
  public static void main(String[] args)
  {

    if (args.length < 1)
    {
      System.out.println("Usage:  java CreateXLS ");
      System.exit(0);
    }

    try

    {
      String saveDirectory = args[0];
      String feedFile = args[1];
      System.out.print("Creating XLS files for items from " + feedFile + " ... ");
      File dd = new File(saveDirectory);
      if (!dd.isDirectory()) dd.mkdir();
      String separator = System.getProperty("file.separator");
      String newLine = System.getProperty("line.separator");
      String tab = "\t";
      FileWriter fw;
      FileOutputStream os;
      HSSFWorkbook wb;
      HSSFSheet sheet;
      HSSFRow row;
      Feed feed = Feed.loadFeed(feedFile);
      ArrayList itemList = feed.getItemList();
      Item item;
      Molecule molecule;
      Field suppliers;
      ArrayList suppliersList;

      for (int i = 0;i < itemList.size();i++)
      {
        item = (Item) itemList.get(i);
        molecule = item.getMolecule();
        fw = new FileWriter(saveDirectory + separator + item.toString() + ".txt");
        os = new FileOutputStream(saveDirectory + separator + item.toString() + ".xls");
        wb = new HSSFWorkbook();
        sheet = wb.createSheet();
        row = sheet.createRow((short) 0);
        row.createCell((short) 0).setCellValue("UC Number:");
        row.createCell((short) 1).setCellValue(item.toString());
        fw.write("UC Number:" + tab + item + newLine);
        row = sheet.createRow((short) 1);
        row.createCell((short) 0).setCellValue("SMILES");
        row.createCell((short) 1).setCellValue(molecule.getSMILES().getFieldContents());
        fw.write("SMILES:" + tab + molecule.getSMILES().getFieldContents() + newLine);
        row = sheet.createRow((short) 2);
        row.createCell((short) 0).setCellValue("InChI:");
        row.createCell((short) 1).setCellValue(molecule.getInChI().getFieldContents());
        fw.write("InChI:" + tab + molecule.getInChI().getFieldContents() + newLine);
        row = sheet.createRow((short) 3);
        row.createCell((short) 0).setCellValue("Image URL:");
        row.createCell((short) 1).setCellValue(molecule.getImageURL().getFieldContents());
        fw.write("Image URL:" + tab + molecule.getImageURL().getFieldContents() + newLine);
        row = sheet.createRow((short) 4);
        row.createCell((short) 0).setCellValue("Substructure Search:");
        row.createCell((short) 1).setCellValue(molecule.getSubStructureSearch().getFieldContents());
        fw.write("Substructure Search:" + tab + molecule.getSubStructureSearch().getFieldContents() + newLine);
        row = sheet.createRow((short) 5);
        row.createCell((short) 0).setCellValue("Item Page:");
        row.createCell((short) 1).setCellValue(molecule.getItemPage().getFieldContents());
        fw.write("Item Page:" + tab + molecule.getItemPage().getFieldContents() + newLine);
        row = sheet.createRow((short) 6);
        row.createCell((short) 0).setCellValue("Canonical MW:");
        row.createCell((short) 1).setCellValue(molecule.getCanonicalMW().getFieldContents());
        fw.write("Canonical MW:" + tab + molecule.getCanonicalMW().getFieldContents() + newLine);
        row = sheet.createRow((short) 7);
        row.createCell((short) 0).setCellValue("Natural MW:");
        row.createCell((short) 1).setCellValue(molecule.getNaturalMW().getFieldContents());
        fw.write("Natural MW:" + tab + molecule.getNaturalMW().getFieldContents() + newLine);
        row = sheet.createRow((short) 8);
        row.createCell((short) 0).setCellValue("Suppliers:");
        fw.write("Suppliers:");

        suppliers = molecule.getSuppliers();

        suppliersList = suppliers.getFieldContentsList();

        if (suppliersList != null)

        {

          for (int j = 0;j < suppliersList.size();j++)
          {
            row.createCell((short) 1).setCellValue((String) suppliersList.get(j));
            row = sheet.createRow((short) (j + 9));
            fw.write(tab + (String) suppliersList.get(j) + newLine);
            row.createCell((short) 0).setCellValue("");
          }

        }

        wb.write(os);

        os.close();
        fw.flush();
        fw.close();
      }

      System.out.println("[ok]");

    }
    catch (Exception e)
    {
      System.out.println("[failed]:  " + e.getMessage());
    }

  }

}

________________________________________

This is all five years after the fact, and much has changed since then, but hopefully someone will find it useful.