One, foreword

Recently, I saw my friend write a business of exporting database to generate Word documents. I felt very interesting. I studied it and here I share a wave with you

Take a look at the generated Word document effect

Now let’s do a simple implementation

Java export database table information to generate Word document

Tips: The following is just a simple demonstration of some of the main code, details can refer to the end of the case demo source

Basic environment

  1. Spring – the boot 2.1.8
  2. Mybatis – plus 2.2.0
  3. The mysql database

1. New dependencies

<! - = = = = = = = = = = = = = = = = = = the database table information generated word document information needed for = = = = = = = = = = = = = = = = = = = = = = -- -- > <! -- https://mvnrepository.com/artifact/com.lowagie/itext --> <dependency> <groupId>com.lowagie</groupId> The < artifactId > itext < / artifactId > < version > 2.1.7 < / version > < / dependency > <! -- https://mvnrepository.com/artifact/com.itextpdf/itext-asian --> <dependency> <groupId>com.itextpdf</groupId> < artifactId > itext - Asian < / artifactId > < version > 5.2.0 < / version > < / dependency > <! -- https://mvnrepository.com/artifact/com.lowagie/itext-rtf --> <dependency> <groupId>com.lowagie</groupId> < artifactId > itext - RTF < / artifactId > < version > 2.1.7 < / version > < / dependency >Copy the code

2, query table data information

@mapper public interface TableMapper {/** * get all table names and comments from specified database ** @param dbName: database name * @return: java.util.List<com.zhengqing.demo.modules.system.entity.Tables> */ @Select("select table_name as name,table_comment as comment from information_schema.tables where table_schema =#{dbName} order by table_name") List<Tables> getAllTables(@Param("dbName") String dbName); ** @param tableName: table * @return: java.util.List<com.zhengqing.demo.modules.system.entity.TableFileds> */ @Select("SHOW FULL FIELDS FROM ${tableName}") List<TableFileds> getTable(@Param("tableName") String tableName); }Copy the code

3. Generate word document implementation classes

@Service public class TableService implements ITableService { @Autowired private TableMapper tableMapper; @Autowired private TableToWordUtil tableToWordUtil; @override public String getTableInfo() {List<Tables> Tables = tableMapper.getAllTables(Constants.DATABASE); // 2. File name information - year month day hour minute second String date = null; try { date = DateTimeUtils.dateFormat(new Date(), DateTimeUtils.PARSE_PATTERNS[12]); } catch (ParseException e) { e.printStackTrace(); } String docFileName = Constants.FILE_PATH + "\\" + Constants.FILE_NAME + "-" + date + ".doc"; // Call tableTowordutil. toWord(tables, docFileName, Constants.FILE_NAME); // Call tableTowordutil. toWord(tables, docFileName, Constants. String filePath = docfilename. replaceAll("\\\\", "/"); return filePath; }}Copy the code

4. Generate word document tool class

@Service public class TableToWordUtil { @Autowired TableMapper tableMapper; @param fileName: address of the generated file * @param title: title of the file content * @return: void */ public void toWord(List<Tables> tables, String fileName, String title) { Document document = new Document(PageSize.A4); Try {// Create folder File dir = new File(Constants.FILE_PATH); dir.mkdirs(); File File = new File(fileName); if (file.exists() && file.isFile()) { file.delete(); } file.createNewFile(); Rtfwriter2. getInstance(document, new FileOutputStream(fileName)); document.open(); Paragraph ph = new Paragraph(); Font f = new Font(); Paragraph p = new Paragraph(title, new Font(Font.NORMAL, 24, Font.BOLDITALIC, new Color(0, 0, 0))); p.setAlignment(1); document.add(p); ph.setFont(f); for (int i = 0; i < tables.size(); i++) { String table_name = tables.get(i).getName(); String table_comment = tables.get(i).getComment(); List<TableFileds> fileds = tableMapper.getTable(tables.get(i).getName()); String all = "" + (I + 1) + "(" + table_comment +") "; Table table = new Table(6); document.add(new Paragraph("")); table.setBorderWidth(1); table.setPadding(0); table.setSpacing(0); Chade = new Color(176, 196, 222); chade = new Color(176, 196, 222); Cell Cell = new Cell(" number "); addCell(table, cell, chade); Cell = new cell (" field name "); addCell(table, cell, chade); Cell = new cell (" type "); addCell(table, cell, chade); Cell = new cell (" non-empty "); addCell(table, cell, chade); Cell = new cell (" primary key "); addCell(table, cell, chade); Cell = new cell (" comment "); addCell(table, cell, chade); table.endHeaders(); For (int k = 0; k < fileds.size(); k++) { addContent(table, cell, (k + 1) + ""); addContent(table, cell, fileds.get(k).getField()); addContent(table, cell, fileds.get(k).getType()); addContent(table, cell, fileds.get(k).getNull().equals("YES") ? "No" : "yes "); addContent(table, cell, fileds.get(k).getKey() ! = ""? "Yes" : "No "); addContent(table, cell, fileds.get(k).getComment()); } Paragraph pheae = new Paragraph(all); Document.add (pheae); // Create table document.add(table); } document.close(); } catch (Exception e) { e.printStackTrace(); }} /** * add table header to table ** @param table * @param cell * @param chade */ private void addCell(table table, cell cell, Color chade) { cell.setHorizontalAlignment(Element.ALIGN_CENTER); cell.setBackgroundColor(chade); table.addCell(cell); } /** * addContent to table ** @param table * @param content */ private void addContent(table table, Cell, String content) { cell = new Cell(content); cell.setHorizontalAlignment(Element.ALIGN_CENTER); table.addCell(cell); }}Copy the code

5. Some constant parameters

Public Class Constants {/** * Static final String DATABASE = "demo"; public Class Constants {/** * Static final String DATABASE = "demo"; /** * public static final String FILE_NAME = "testdb "; Public static String FILE_PATH = "D:\\ WWW "; }Copy the code

Three, test the generated effect

Small make up in the demo interface provides a get request in http://localhost:8080/api/tableToWord

We can then go to the returned address to view the build file

Example demo source

Gitee.com/zhengqingya…