Java Apache POI Excel save as PDF

Question

How can I convert/save excel file to pdf? I'm using java play framework to generate some excel files and now the requirement changes to pdf. I don't want to recode everything.

Is there a way to convert to pdf?

The excel files I'm generating are from a template; I read the excel template file, write changes, and save as new excel file. That way, the template is unchanged. It contains border, image, and other formatting.

Answer 1

You would need the following Java libraries and associated JAR files for the program to work. POI v3.8 iText v5.3.4

Try this Example to convert XLS to PDF

The complete Java code that accepts Excel spreadsheet data as an input and transforms that to a PDF table data is provided below:

 import java.io.FileInputStream;     import java.io.*;     import org.apache.poi.hssf.usermodel.HSSFWorkbook;     import org.apache.poi.hssf.usermodel.HSSFSheet;     import org.apache.poi.ss.usermodel.*;     import java.util.Iterator;    import com.itextpdf.text.*;     import com.itextpdf.text.pdf.*;      public class excel2pdf {               public static void main(String[] args) throws Exception{                      FileInputStream input_document = new FileInputStream(new File("C:\\excel_to_pdf.xls"));                     // Read workbook into HSSFWorkbook                     HSSFWorkbook my_xls_workbook = new HSSFWorkbook(input_document);                      // Read worksheet into HSSFSheet                     HSSFSheet my_worksheet = my_xls_workbook.getSheetAt(0);                      // To iterate over the rows                     Iterator<Row> rowIterator = my_worksheet.iterator();                     //We will create output PDF document objects at this point                     Document iText_xls_2_pdf = new Document();                     PdfWriter.getInstance(iText_xls_2_pdf, new FileOutputStream("Excel2PDF_Output.pdf"));                     iText_xls_2_pdf.open();                     //we have two columns in the Excel sheet, so we create a PDF table with two columns                     //Note: There are ways to make this dynamic in nature, if you want to.                     PdfPTable my_table = new PdfPTable(2);                     //We will use the object below to dynamically add new data to the table                     PdfPCell table_cell;                     //Loop through rows.                     while(rowIterator.hasNext()) {                             Row row = rowIterator.next();                              Iterator<Cell> cellIterator = row.cellIterator();                                     while(cellIterator.hasNext()) {                                             Cell cell = cellIterator.next(); //Fetch CELL                                             switch(cell.getCellType()) { //Identify CELL type                                                     //you need to add more code here based on                                                     //your requirement / transformations                                             case Cell.CELL_TYPE_STRING:                                                     //Push the data from Excel to PDF Cell                                                      table_cell=new PdfPCell(new Phrase(cell.getStringCellValue()));                                                      //feel free to move the code below to suit to your needs                                                      my_table.addCell(table_cell);                                                     break;                                             }                                             //next line                                     }                      }                     //Finally add the table to PDF document                     iText_xls_2_pdf.add(my_table);                                            iText_xls_2_pdf.close();                                     //we created our pdf file..                     input_document.close(); //close xls             }     } 

i hope this will help you

Answer 2

Add on to assylias's answer

The code from assylias above was very helpful to me in solving this problem. The answer from santhosh could be great if you don't care about the resulting PDF looking exactly like your excel pdf export would look. However, if you are, say, filling out an excel template using Apache POI an then trying to export that while preserving its look and not writing a ton of code in iText just to try to get close to that look, then the VBS option is quite nice.

I'll share a Java version of the kotlin assylias has above in case that helps anyone. All credit to assylias for the general form of the solution.

In Java:

try {     //create a temporary file and grab the path for it     Path tempScript = Files.createTempFile("script", ".vbs");      //read all the lines of the .vbs script into memory as a list     //here we pull from the resources of a Gradle build, where the vbs script is stored     System.out.println("Path for vbs script is: '" + Main.class.getResource("xl2pdf.vbs").toString().substring(6) + "'");     List<String> script = Files.readAllLines(Paths.get(Main.class.getResource("xl2pdf.vbs").toString().substring(6)));      // append test.xlsm for file name. savePath was passed to this function     String templateFile = savePath + "\\test.xlsm";     templateFile = templateFile.replace("\\", "\\\\");     String pdfFile = savePath + "\\test.pdf";     pdfFile = pdfFile.replace("\\", "\\\\");     System.out.println("templateFile is: " + templateFile);     System.out.println("pdfFile is: " + pdfFile);      //replace the placeholders in the vbs script with the chosen file paths     for (int i = 0; i < script.size(); i++) {         script.set(i, script.get(i).replaceAll("XL_FILE", templateFile));         script.set(i, script.get(i).replaceAll("PDF_FILE", pdfFile));         System.out.println("Line " + i + " is: " + script.get(i));     }      //write the modified code to the temporary script     Files.write(tempScript, script);      //create a processBuilder for starting an operating system process     ProcessBuilder pb = new ProcessBuilder("wscript", tempScript.toString());      //start the process on the operating system     Process process = pb.start();      //tell the process how long to wait for timeout     Boolean success = process.waitFor(timeout, minutes);     if(!success) {         System.out.println("Error: Could not print PDF within " + timeout + minutes);     } else {         System.out.println("Process to run visual basic script for pdf conversion succeeded.");     }      } catch (Exception e) {     e.printStackTrace();     Alert saveAsPdfAlert = new Alert(AlertType.ERROR);     saveAsPdfAlert.setTitle("ERROR: Error converting to pdf.");     saveAsPdfAlert.setHeaderText("Exception message is:");     saveAsPdfAlert.setContentText(e.getMessage());     saveAsPdfAlert.showAndWait();   } 

VBS:

Option Explicit Dim objExcel, strExcelPath, objSheet  strExcelPath = "XL_FILE"   Set objExcel = CreateObject("Excel.Application") objExcel.WorkBooks.Open strExcelPath Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)  objSheet.ExportAsFixedFormat 0, "PDF_FILE",0, 1, 0, , , 0  objExcel.ActiveWorkbook.Close objExcel.Application.Quit