Automating Excel for Headless, Server-side HTML Conversion

If you’re ever tasked with producing an Excel formatted report from your Java application you might want to hold fire before heading over to the usual down the route of using Apache POI or Aspose. If you’ve got the time to work with these libraries then both provide a great deal of functionality, however, if you’ve already got code to produce HTML (or you’re simply looking to convert existing documents) then you might want to think about another method of getting it into native Excel; use Excel itself!

Excel is really rather good at converting HTML to its native .xls format and through Powershell it’s very easy to automate the Open and Save As operations into something that requires no UI interaction. For example:

$Excel = New-Object -comobject Excel.Application
$format = 56

$Excel.Visible = $False
$Excel.displayalerts=$False

$WorkBook = $Excel.Workbooks.Open($args[0])
$WorkBook.SaveAs($args[1], $format)

$Excel.Quit()

…executed in powershell opens the file that is the first argument and saves it as the second. Excel is told to not be visible and not display any alerts. The format 56 is the id for xlExcel8 (97-2003 format in Excel 2007-2013, xls).

Having saved this powershell script (.ps) we can break out of our JVM (running in Tomcat in my case) to call it on the command line.


String cmd = "cmd /c powershell -ExecutionPolicy RemoteSigned -noprofile -noninteractive C:\\ExcelConversion\\excelconvert.ps1 " + toConvert.getAbsolutePath() + " " + toConvert.getAbsolutePath();
Runtime runtime = Runtime.getRuntime();
Process proc = runtime.exec(cmd);

The above overwrites the existing HTML document with its native xls equivalent.

For obvious reason MS do not recommend using their desktop software for headless server automation but this works well for me even under load and dealing with large HTML documents.

 

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

%d bloggers like this: