Merging Excel with PDF

Introduction

This article demonstrates how to handle Excel files in a process where multiple uploaded documents must be merged into a single PDF. Since Joget does not provide a built-in feature to convert Excel files to PDF during the merge step, this article explains how to implement a feature to convert Excel documents into PDF before the merge takes place.

How does it work?

Joget is able to merge PDF files, but Excel files must first be converted into PDF. You can achieve this by installing a PDF library, such as iText PDF, into the Apache Tomcat library directory. A BeanShell post-processing script is then added to the desired form. The script detects uploaded Excel files, converts them into PDF, updates the file reference in the form data, and prepares the files for merging.

1. Install the required libraries. Place the itextpdf-5.5.4.jar file into the folder /<apache-tomcat-directory>/lib. Apache POI is already available, but you may add additional libraries if needed.
2. Restart Joget.
3. Insert the following BeanShell script into the Configure Form > Advanced > Post Form Submission Processing section of your form, and run the tool on "Both data creation and update". Modify the primaryKey, tableName, and fileName at the indicated lines.
   

import java.io.File;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Map;
import org.joget.apps.app.model.AppDefinition;
import org.joget.apps.app.service.AppService;
import org.joget.apps.app.service.AppUtil;
import org.joget.apps.form.dao.FormDataDao;
import org.joget.apps.form.model.Element;
import org.joget.apps.form.model.Form;
import org.joget.apps.form.model.FormBinder;
import org.joget.apps.form.model.FormData;
import org.joget.apps.form.model.FormDataDeletableBinder;
import org.joget.apps.form.model.FormLoadBinder;
import org.joget.apps.form.model.FormLoadMultiRowElementBinder;
import org.joget.apps.form.model.FormRow;
import org.joget.apps.form.model.FormRowSet;
import org.joget.apps.form.model.FormStoreBinder;
import org.joget.apps.form.model.FormStoreMultiRowElementBinder;
import org.joget.apps.form.service.FormUtil;
import org.joget.commons.util.LogUtil;
import org.joget.commons.util.FileManager;
import org.joget.apps.form.service.FileUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import com.itextpdf.text.*;
import com.itextpdf.text.pdf.*;

public void excelToPdf(String excelPath, String pdfPath) throws Exception {
    FileInputStream fis = new FileInputStream(excelPath);
    Workbook workbook = new XSSFWorkbook(fis);
    Sheet sheet = workbook.getSheetAt(0);
    
    Document document = new Document();
    PdfWriter.getInstance(document, new FileOutputStream(pdfPath));
    document.open();
    
    // Process each row
    for (Row row : sheet) {
        int columnCount = row.getLastCellNum();
        PdfPTable table = new PdfPTable(columnCount);
        for (int i = 0; i < columnCount; i++) {
            Cell cell = row.getCell(i);
            PdfPCell pdfCell = createFormattedCell(cell, workbook);
            table.addCell(pdfCell);
        }
        document.add(table);
    }
    
    document.close();
    workbook.close();
    fis.close();
}

private PdfPCell createFormattedCell(Cell cell, Workbook workbook) throws Exception {
    String cellValue = getCellValueAsString(cell);
    PdfPCell pdfCell = new PdfPCell(new Phrase(cellValue, new Font(Font.FontFamily.HELVETICA, 8)));
    
    if (cell != null) {
        CellStyle style = cell.getCellStyle();
    
        handleMergedCells(pdfCell, cell, workbook);
    }
    
    return pdfCell;
}

private void handleMergedCells(PdfPCell pdfCell, Cell cell, Workbook workbook) {
    if (cell == null) return;
    
    Sheet sheet = cell.getSheet();
    int currentRow = cell.getRowIndex();
    int currentCol = cell.getColumnIndex();
    
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
        CellRangeAddress region = sheet.getMergedRegion(i);
        
        if (region.isInRange(currentRow, currentCol)) {
            
            if (region.getFirstRow() == currentRow && region.getFirstColumn() == currentCol) {
                
                int rowSpan = region.getLastRow() - region.getFirstRow() + 1;
                int colSpan = region.getLastColumn() - region.getFirstColumn() + 1;
                
                if (rowSpan > 1) {
                    pdfCell.setRowspan(rowSpan);
                    LogUtil.info("Post process", "Set rowspan: " + rowSpan + " for cell at [" + currentRow + "," + currentCol + "]");
                }
                if (colSpan > 1) {
                    pdfCell.setColspan(colSpan);
                    LogUtil.info("Post process", "Set colspan: " + colSpan + " for cell at [" + currentRow + "," + currentCol + "]");
                }
                
                addMergedRegionContent(pdfCell, region, sheet);
                
            } else {
                pdfCell.setRowspan(0);  // 0 means this cell will be covered
                pdfCell.setColspan(0);
                LogUtil.info("Post process", "Set dummy cell at [" + currentRow + "," + currentCol + "] for merged region");
            }
            
            break;
        }
    }
}

private void addMergedRegionContent(PdfPCell pdfCell, CellRangeAddress region, Sheet sheet) {
    Row firstRow = sheet.getRow(region.getFirstRow());
    if (firstRow != null) {
        Cell firstCell = firstRow.getCell(region.getFirstColumn());
        if (firstCell != null) {
            String content = getCellValueAsString(firstCell);
            if (!content.equals(pdfCell.getPhrase().getContent())) {
                pdfCell.setPhrase(new Phrase(content));
            }
        }
    }
}

public String getCellValueAsString(org.apache.poi.ss.usermodel.Cell cell) {
    if (cell == null) return "";
    
    try {
        return cell.getStringCellValue();
    } catch (Exception e1) {
        try {
            return String.valueOf(cell.getNumericCellValue());
        } catch (Exception e2) {
            try {
                return String.valueOf(cell.getBooleanCellValue());
            } catch (Exception e3) {
                try {
                    return cell.getCellFormula();
                } catch (Exception e4) {
                    return "";
                }
            }
        }
    }
}


String primaryKey = "#form.fileup.id#"; // replace the form id with your actual form id
String fileName = "#form.fileup.file_upload1#"; // replace the file upload field with your actual file upload field
String tableName = "fileup"; // replace the table name with your actual table name
System.out.println("primaryKey: " + primaryKey);
System.out.println("File Name: " + fileName);

if(fileName == null ){
    LogUtil.info("Post process", "File does not exist");
    return;
}

if(fileName != null){
    if(fileName.contains("xlsx")){
        String[] fileNamesArray = fileName.split(";");
        for(String fileName1 : fileNamesArray){
            File uploadedFile = FileUtil.getFile(fileName1, tableName, primaryKey);
            if(uploadedFile != null){
                if(uploadedFile.exists()){

                    // check uploadedFile is excel or not   
                    if(fileName1.endsWith(".xlsx")){

                        String pdfFileName = fileName1.replaceAll("\\.(xlsx?|pdf)", ".pdf");
                        String pdfPath = uploadedFile.getParent() + File.separator + pdfFileName;
                        
                        excelToPdf(uploadedFile.getAbsolutePath(), pdfPath);
                    } else {
                        LogUtil.info("Post process", "File is not excel: " + fileName1);
                    }
                    
                }
            }
        }
        fileName = fileName.replaceAll("\\.(xlsx?|pdf)", ".pdf");
        LogUtil.info("Post process", "File name: " + fileName);
        FormDataDao formDataDao = (FormDataDao) AppUtil.getApplicationContext().getBean("formDataDao");

        FormRow formRow = formDataDao.load("fileUp", "fileup", primaryKey); // replace the formId, tableName with your actual form id and table name
        formRow.setProperty("file_upload1", fileName); // replace the file upload field with your actual file upload field

        FormRowSet formRowSet = new FormRowSet();
        formRowSet.add(formRow);
        formDataDao.saveOrUpdate("fileUp", "fileup", formRowSet); // replace the formId, tableName with your actual form id and table name

        LogUtil.info("Post process", "File uploaded: " + fileName);
    }
}


// To verify the libraries installed correctly or not
try {
    Class.forName("com.itextpdf.text.Document");
    LogUtil.info("Post process", "iText is available");
} catch (ClassNotFoundException e) {
    LogUtil.info("Post process", "iText NOT available");
}

try {
    Class.forName("org.apache.poi.ss.usermodel.Workbook");
    LogUtil.info("Post process", "Apache POI is available");
} catch (ClassNotFoundException e) {
    LogUtil.info("Post process", "Apache POI NOT available");
}

This script checks whether the uploaded file is an Excel file. If it is, it creates a corresponding PDF file using iText PDF, preserves merged cell structures, populates content, and stores the resulting PDF in the same directory. The script then updates the form row to reference the new PDF file. It also verifies whether iText is available in the environment. You may also further adjust the formatting methods excelToPdf, createFormattedCell, and handleMergedCells to refine the appearance of the converted PDF output.

Expected Outcome

After this configuration, any Excel files uploaded through the form will be automatically converted into PDF upon submission. Since the form now contains only PDF files, the Merge PDF plugin can merge all uploaded documents successfully. Here is the video demonstration of the implementation.

Download sample app