Apply Apache POI, docx4j and springframework.jdbc
Hello!
I present an example of a simple Java console application that reads data from the database and from the * .xslx file, and then creates a * .docx document, filling in the merge fields. It uses the Apache POI, docx4j, and springframework.jdbc libraries. In the example, the implementation of several tasks frequently encountered in the process of automation is put together. It is possible that he will be useful to someone.
O app
What it can do:
The need for this kind of application arose when it was necessary to manually generate a certain number of similar documents for their further printing and sending by mail. Having brought together various pieces from his other minor developments, this application appeared.
The choice of the Apache POI library was not worth it, since I had already implemented tasks with its use. But docx4j applied due to the fact that it had the ability to fill in the merge fields in MS Word documents. That was what I needed.
At the entrance we have a certain MS Excel file in which there is information identifying clients. Customer information is not complete. To retrieve additional data, we will be forced to access the Oracle database via jdbc. Then the application will generate an MS Word file for each client.
Implementation
Conclusion
That's the whole application. Of course, the multitude in it is sewn into the code (connection settings with the database, file paths) and, moreover, it is console. You can add various checks and create gui. I did not do this, since the task was one-time. I still hope that the post will be useful to someone!
Thanks for attention!
I present an example of a simple Java console application that reads data from the database and from the * .xslx file, and then creates a * .docx document, filling in the merge fields. It uses the Apache POI, docx4j, and springframework.jdbc libraries. In the example, the implementation of several tasks frequently encountered in the process of automation is put together. It is possible that he will be useful to someone.
O app
What it can do:
- Read data from xslx file
- Extract information from the database (in this case, Oracle is used)
- Generate docx files based on the existing file, add values to the merge fields
The need for this kind of application arose when it was necessary to manually generate a certain number of similar documents for their further printing and sending by mail. Having brought together various pieces from his other minor developments, this application appeared.
The choice of the Apache POI library was not worth it, since I had already implemented tasks with its use. But docx4j applied due to the fact that it had the ability to fill in the merge fields in MS Word documents. That was what I needed.
At the entrance we have a certain MS Excel file in which there is information identifying clients. Customer information is not complete. To retrieve additional data, we will be forced to access the Oracle database via jdbc. Then the application will generate an MS Word file for each client.
Implementation
- Application created using maven. First, let's deal with the dependencies we need. Here is what you need to add to the pom.xml file.Depencies
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.9</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.9</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>2.5.6</version> <type>jar</type> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-dao</artifactId> <version>2.0.6</version> <type>jar</type> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-core</artifactId> <version>2.5.6</version> <type>jar</type> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>2.5.6</version> <type>jar</type> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-beans</artifactId> <version>2.5.6</version> <type>jar</type> </dependency> <dependency> <groupId>ojdbc</groupId> <artifactId>ojdbc</artifactId> <version>14</version> </dependency> <dependency> <groupId>org.docx4j</groupId> <artifactId>docx4j</artifactId> <version>2.8.1</version> </dependency> - Consider the App class — the main class of the application. In the main method of this class, the 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(); } } - In the HelperWord class, we obtain customer data, process it, and create an 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<HashMap> clientsRows; // private List<HashMap> additionalData; // MS Word public void createDocs() { // MyDataManager dmg = new MyDataManager(); try { // MS Excel clientsRows = dmg.getDataBlock(); // <editor-fold defaultstate="collapsed" desc="Catch clauses"> } 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); //</editor-fold> } // 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<Map<DataFieldName, String>> data = new ArrayList<Map<DataFieldName, String>>(); // additionalData = dmg.getAddress(row.get("NAME").toString(), row.get("DOCDATE").toString()); // Map<DataFieldName, String> map = new HashMap<DataFieldName, String>(); 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")); // <editor-fold defaultstate="collapsed" desc="Catch clauses"> } 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); } // </editor-fold> } } }
This is where we fill in the merge fields in the document using the docx4j library. Perhaps attention should be paid only to the classes DataFieldName and MailMerger. It seems that both of them should be present in the docx4j library, but they were not in my build. Therefore, they were added to the project separately. Few words about these classes- In the DataFieldName class, there is a name field and the equals method is overridden. This is done so that we compare the names of the uppercase merge fields.
- The MailMerger class just inserts values into the document merge fields. The class code is fully borrowed from the official docx4j website. Here is the link
- MyDataManager is a class for working with data. It uses Apache POI libraries for reading MS Excel files and springframework.jdbc classes for working with databases.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.xx"); ods.setPortNumber(); ods.setDatabaseName("SID"); ods.setUser("user"); ods.setPassword("password"); return ods; } // MS Excel public List<HashMap> getDataBlock() throws FileNotFoundException, IOException { ArrayList<HashMap> res = new ArrayList<HashMap>(); FileInputStream file = new FileInputStream(new File("clients.xlsx")); XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> 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<HashMap> 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<HashMap> res = (List<HashMap>) namedPar.query(query, namedParameters, new DataMapper()); try { return res; } finally { ds.close(); } } }
Conclusion
That's the whole application. Of course, the multitude in it is sewn into the code (connection settings with the database, file paths) and, moreover, it is console. You can add various checks and create gui. I did not do this, since the task was one-time. I still hope that the post will be useful to someone!
Thanks for attention!
')
Source: https://habr.com/ru/post/214435/
All Articles