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.
