March 3, 2014 at 15:44

Apply Apache POI, docx4j and springframework.jdbc

    Hello!
    I present an example of a simple Java console application that reads data from a database and from a * .xslx file , and then creates a * .docx document , filling out mergefield fields . It uses the Apache POI, docx4j, and springframework.jdbc libraries. The example brings together the implementation of several tasks that often arise in the process of automation. It is possible that he will be useful to someone.

    About the application
    What it can do:
    • Read data from xslx file
    • Retrieve information from the database (in this case, using Oracle)
    • Generate docx files based on the existing file, along the way add values ​​to the merge fields

    The need for this kind of application arose when it was required to manually generate a certain number of documents of the same type for their further printing and sending by mail. Having gathered together various pieces from his other small developments, this application appeared.
    The choice of the Apache POI library was not worth it, as it was already implementing tasks using it. But docx4j applied due to the fact that it was possible to fill in the merge fields in MS Word documents. This was what I needed.
    At the entrance, we have a certain MS Excel file, in which there is information that identifies customers. Customer information is not complete. To extract additional data, we will be forced to access the Oracle database through jdbc. Then the application will generate an MS Word file for each client.

    Implementation
    1. Application created using maven. First, let's figure out the dependencies we need. Here is what you need to add to the pom.xml file
      Dependencies
      org.apache.poipoi3.9org.apache.poipoi-ooxml3.9org.springframeworkspring-jdbc2.5.6jarorg.springframeworkspring-dao2.0.6jarorg.springframeworkspring-core2.5.6jarorg.springframeworkspring-context2.5.6jarorg.springframeworkspring-beans2.5.6jarojdbcojdbc14org.docx4jdocx4j2.8.1

    2. Consider the class App - the main class of the application. In the main method of this class, an object of the HelperWord class is simply created and its createWord () method is called
      App class
      public class App 
{
    public static void main( String[] args )
    {
        HelperWord helper = new HelperWord();
        helper.createWord();
    }
}
    3. In the HelperWord class, we obtain client data, process it, and create the MS Word file.
      HelperWord Class
      import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.docx4j.openpackaging.exceptions.Docx4JException;
import org.docx4j.openpackaging.exceptions.InvalidFormatException;
import org.docx4j.openpackaging.packages.WordprocessingMLPackage;
public class HelperWord {
    // Объект класса MyDataManager для работы с данными
    private MyDataManager dmg;
    // Данные из фала *.xslx
    private List clientsRows;
    // Дополнительные данные из БД
    private List additionalData;
    // Инициирует создание файлов MS Word 
    public void createDocs() {
        // Создаем объект класса MyDataManager для работы с данными
        dmg = new MyDataManager();
        try {
            // Извлекаем данные из файла MS Excel
            clientsRows = dmg.getDataBlock();
            //             
        } catch (FileNotFoundException ex) {
            Logger.getLogger(HelperWord.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IOException ex) {
            Logger.getLogger(HelperWord.class.getName()).log(Level.SEVERE, null, ex);
            //   
        }
        // Создаем файл MS Word и заполняем его
        addDataBlock();    
    }
    // Создает файл MS Word и заполняет его
    private void addDataBlock() {
        int num = 0;
        // Считываем информацию о каждом клменте
        for (HashMap row : clientsRows) {            
            try {
                num++;
                // Извлекаем данные о существующем объекте MS Word
                WordprocessingMLPackage wordMLPackage =
                        WordprocessingMLPackage
                        .load(new File("template.docx"));
                // Создаем объект для вставки значений в поля слияния
                List> data = 
                        new ArrayList>();
                // Получаем дополнительные данные о клиенте из базы
                additionalData = dmg.getAddress(row.get("NAME").toString(), 
                        row.get("DOCDATE").toString());
                // Заполняем значения для полей слияния
                Map map = 
                        new HashMap();
                map.put(new DataFieldName("NAME"), row.get("NAME").toString());
                map.put(new DataFieldName("ADDRESS"), 
                        additionalData.get(0).get("ADDRESS").toString());                
                data.add(map);
                // Создаем новый объект MS Word на основе существующего и 
                // значений полей слияния
                WordprocessingMLPackage output = 
                        MailMerger.getConsolidatedResultCrude(
                        wordMLPackage, data);
                // Сохраняем объект в файл
                output.save(new File("T:\\VIPISKI_KK\\Письма\\" 
                        + num + ". " + row.get("NAME") + ".docx"));
                //                  
            } catch (InvalidFormatException ex) {
                Logger.getLogger(HelperWord.class.getName()).log(Level.SEVERE, null, ex);
            } catch (Docx4JException ex) {
                Logger.getLogger(HelperWord.class.getName()).log(Level.SEVERE, null, ex);
            } catch (SQLException ex) {
                Logger.getLogger(HelperWord.class.getName()).log(Level.SEVERE, null, ex);
            } catch (FileNotFoundException ex) {
                Logger.getLogger(HelperWord.class.getName()).log(Level.SEVERE, null, ex);
            } catch (IOException ex) {
                Logger.getLogger(HelperWord.class.getName()).log(Level.SEVERE, null, ex);
            }
            //     
        }
    }
}


      This is where we fill in the merge fields in the document using the docx4j library. Perhaps you should pay attention only to the DataFieldName and MailMerger classes. It seems that they both should be present in the docx4j library, but they were not in my assembly. Therefore, they were added to the project separately. A few words about these classes
      • The DataFieldName class has a name field and the equals method is overridden. This is done so that we compare the names of the merge fields in upper case
      • The MailMerger class just inserts the values ​​into the document merge fields. The class code is fully borrowed from the official docx4j website. Here is the link
    4. MyDataManager - a class for working with data. It uses the Apache POI libraries to read the MS Excel file and the springframework.jdbc classes to work with the database.
      Class MyDataManager
      import java.io.*;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import oracle.jdbc.pool.OracleDataSource;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
public class MyDataManager {
    public NamedParameterJdbcTemplate namedPar;
    private OracleDataSource getDataSource() throws SQLException {
        // Создаем объект источника данных и заполняем значения параметров
        OracleDataSource ods = new OracleDataSource();
        ods.setDriverType("thin");
        ods.setServerName("192.168.x.x");
        ods.setPortNumber();
        ods.setDatabaseName("SID");
        ods.setUser("user");
        ods.setPassword("password");
        return ods;
    }
    //Получаем данные из MS Excel
    public List getDataBlock()
            throws FileNotFoundException, IOException {
        ArrayList res = new ArrayList();
        FileInputStream file = new FileInputStream(new File("clients.xlsx"));
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        XSSFSheet sheet = workbook.getSheetAt(0);
        Iterator rowIterator = sheet.iterator();
        // Пропускаю первую строку. В моем случае в ней только заколовки
        if(rowIterator.hasNext()) rowIterator.next();
        //Пробегаемся по всем строкам
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            HashMap line = new HashMap();
            // В моей структуре файла мне интересны только 1-ая и 4-ая строки
            Cell cell = row.getCell(0);
            line.put("NAME", cell.getStringCellValue());
            cell = row.getCell(3);
            line.put("DOCDATE", cell.getStringCellValue());
            res.add(line);
        }
        file.close();
        return res;
    }
    //Получаем данные из БД
    public List getAddress(String name, String date) 
            throws SQLException, FileNotFoundException, IOException {
        // Получаем источник данных
        OracleDataSource ds = getDataSource();
        // Считываем запрос
        FileInputStream fins = new FileInputStream("query.txt");
        BufferedReader br = new BufferedReader(
                new InputStreamReader(fins, "UTF8"));
        String query = "";
        String line = "";
        while ((line = br.readLine()) != null) {
            query += "\n";
            query += line;
        }
        // Вставляем значения параметров
        namedPar = new NamedParameterJdbcTemplate(ds);
        MapSqlParameterSource namedParameters = new MapSqlParameterSource();
        namedParameters.addValue("NAME", name);
        namedParameters.addValue("DOCDATE", date);
        // Исполняем запрос и получае результат
        List res = (List) namedPar.query(query,
                namedParameters, new DataMapper());
        try {
            return res;
        } finally {
            ds.close();
        }
    }
}



    Conclusion
    Here is the whole application. Of course, a lot of it is wired into the code (settings for connecting to the database, file paths) and also it is console. You can add various checks and create gui. I did not do this, since the task was a one-time task. Anyway, I hope that the post will be useful to someone!
    Thanks for attention!
    Tags:

    Also popular now: