Search This Blog

Friday, December 16, 2011

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.

Gene

From Wikipedia, the free encyclopedia https://en.wikipedia.org/wiki/Gene Chromosome ...