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) $WorkBook.SaveAs($args, $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.