You can use Microsoft Excel to do any .xlsx conversions to PDF, I am currently developing an application that uses Jacob, (Java Com Bridge) a thin wrapper for connecting to the Object Models of Microsoft Office Programs, afaik it doesn't support open-office, but it does a good job at converting your .xlsx file to a PDF file. It requires a bit of setting up. Link to Jacob
When looking into the problem I found in Excel -> Page Setup, if you change Fit to 1 pages wide and 1 pages tall, it squeezes each work sheet to fit on each page in PDF. Another problem I ran into was the wrap text property, be careful when you use it, as it can cause layout issues.
I made a small implementation of this, tested on Excel 2010 and Jacob 1.18
import com.jacob.activeX.ActiveXComponent;
import com.jacob.com.ComFailException;
import com.jacob.com.Dispatch;
import com.jacob.com.Variant;
public class ExcelApplication {
private final String APP_NAME = "Excel.Application";
private final ActiveXComponent excelApplication;
private Dispatch workbooks;//all active workbooks opened
private Dispatch activeWorkbook;//active workbook
private Dispatch activeWorksheets;//all worksheets in active workbook
public ExcelApplication() {
excelApplication = new ActiveXComponent(APP_NAME);
}
public void openExcelFileInvisible(String fileName) {
//Opens Excel in the background
String fileUrl;
if (excelApplication != null) {
excelApplication.setProperty("Visible", new Variant(false));//sets excel invisible
//file url relative to this class
//or you can just give an absolute path
fileUrl = getClass().getResource(fileName).toExternalForm();
//get workbooks
workbooks = Dispatch.call(excelApplication, "Workbooks").getDispatch();
if (activeWorkbook == null) {
try {
activeWorkbook = Dispatch.call(workbooks, "Open", fileUrl).getDispatch();
} catch (ComFailException comFailEx) {
//error opening the Excel Document
}
}
}
}
public void closeActiveWorkbookAndSave() {
try {
//close and save change's to active workbook
//this only closes the workbook, not Excel
Dispatch.call(activeWorkbook, "Close", new Variant(true));
//if you want to exit the Excel App.
//excelApplication.invoke("Quit", new Variant[0]);
} catch (ComFailException cfe) {
//problem closing the workbook
}
}
public void convert_XLSX_TO_PDF(String pdfFileName) {
if (activeWorkbook != null) {
String workbookName = Dispatch.call(activeWorkbook, "Name").getString();
activeWorksheets = Dispatch.call(activeWorkbook, "Worksheets").getDispatch();
int workSheetCount = Dispatch.call(activeWorksheets, "Count").getInt();
System.out.println("Workbook Name =" + workbookName);
System.out.println("Total Worksheets In Active Document = " + workSheetCount);
System.out.println("Converting to PDF....");
try {
Dispatch currentWorksheet;
String currentWorksheetName;
//worksheets not zero based, starts at one
for (int i = 1; i < workSheetCount+1; i++) {
//get each active work sheet and set up the page setup settings
currentWorksheet = Dispatch.call(activeWorksheets, "Item", new Variant(i)).getDispatch();
currentWorksheetName = Dispatch.call(currentWorksheet, "Name").getString();
System.out.println("Setting up page setup for Workbook Sheet ("+ i + ".) - " + currentWorksheetName);
//Get page setup for each worksheet
Dispatch pageSetup = Dispatch.get(currentWorksheet, "PageSetup").getDispatch();
/**** Zoom must be set to false for FitToPagesWide and FitToPagesTall
to take control of scaling
*/
Dispatch.put(pageSetup, "Zoom", new Variant(false));
//Fit content on each worksheet to fit in a single page
Dispatch.put(pageSetup, "FitToPagesWide", new Variant(1));
Dispatch.put(pageSetup, "FitToPagesTall", new Variant(1));
//set print area to not chop off content
Dispatch.put(pageSetup, "PrintArea", new Variant(false));
//set left margin small
Dispatch.put(pageSetup, "LeftMargin", new Variant(0));
}
//[3rd param] = 0 specifies PDF document, 1 is XPS format
//[4th param] = 0 specifies high quality, 1 is low quality
//[5th param] = true to keep document properties, false to ommit
//[6th param] = true to keep print areas set, false does not keep print areas set
Dispatch.call(activeWorkbook, "ExportAsFixedFormat", new Variant(0), new Variant(pdfFileName), new Variant(0), new Variant(false), new Variant(true));
System.out.println("Export to PDF has been successful.");
//close and save
closeActiveWorkbookAndSave();
} catch (ComFailException comFailEx) {
//Export Failed
System.out.println("Export to PDF has failed");
}
}
}
}
public class TestExcel {
public static void main(String[] args) {
// TODO code application logic here
ExcelApplication e = new ExcelApplication();
e.openExcelFileInvisible("ss1.xlsx");
//full path accepted here or if not it will be exported to current directory
e.convert_XLSX_TO_PDF("covertedXLSXFile.pdf");
}
}
Here is the PDF File generated from the above code. Notice the 3rd page, the content is a bit chopped off, when you remove the wrap text property and merged cells, it generates fine. Converted XLSX
