前言

　　当我们在写设计文档，或者是其他涉及到数据架构、表结构时，可以用POI来批量生成表格，例如下面的表格

　　代码编写

　　引入POI依赖

        <!-- 引入apache poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.0.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.0.1</version>
        </dependency>

　　封装两个工具类
　　ExcelUtil，POI操作Excel工具类

        <!-- 引入apache poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.0.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.0.1</version>
        </dependency>

　　WordUtil，POI操作Word工具类

import org.apache.poi.xwpf.usermodel.*;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.*;

import java.math.BigInteger;
import java.util.List;
import java.util.Map;

/**
 * POI操作Word工具类
 */
public class WordUtil {

    /**
     * 简单表格生成
     * @param xdoc XWPFDocument对象
     * @param titles 表头表头
     * @param values 表内容
     */
    public static void createSimpleTable(XWPFDocument xdoc,String[] titles,List<Map<String, String>> values){
        //行高
        int rowHeight = 300;

        //开始创建表格（默认有一行一列）
        XWPFTable xTable = xdoc.createTable();
        CTTbl ctTbl = xTable.getCTTbl();
        CTTblPr tblPr = ctTbl.getTblPr() == null ? ctTbl.addNewTblPr() : ctTbl.getTblPr();
        CTTblWidth tblWidth = tblPr.isSetTblW() ? tblPr.getTblW() : tblPr.addNewTblW();
        tblWidth.setType(STTblWidth.DXA);
        tblWidth.setW(new BigInteger("8600"));//表格宽度

        // 创建表头数据
        XWPFTableRow titleRow = xTable.getRow(0);
        titleRow.setHeight(rowHeight);
        for (int i = 0; i < titles.length; i++) {
            setCellText(i == 0 ? titleRow.getCell(0) :titleRow.createCell(), titles[i]);
        }

        // 创建表格内容
        for (int i = 0; i < values.size(); i++) {
            Map<String, String> stringStringMap = values.get(i);

            //设置列内容
            XWPFTableRow row = xTable.insertNewTableRow(i + 1);
            row.setHeight(rowHeight);
            for (String title : titles) {
                setCellText(row.createCell(), stringStringMap.get(title));
            }
        }
    }

    /**
     * 设置列内容
     */
    private static void setCellText(XWPFTableCell cell,String text) {
        CTTc cttc = cell.getCTTc();
        CTTcPr cellPr = cttc.addNewTcPr();
        cellPr.addNewTcW().setW(new BigInteger("2100"));
        cell.setColor("FFFFFF");
        cell.setVerticalAlignment(XWPFTableCell.XWPFVertAlign.CENTER);
        CTTcPr ctPr = cttc.addNewTcPr();
        ctPr.addNewVAlign().setVal(STVerticalJc.CENTER);
        cttc.getPList().get(0).addNewPPr().addNewJc().setVal(STJc.CENTER);
        cell.setText(text);
    }
}

　　首先写sql脚本，查出所有表结构信息（表名称、表注释、表字段数据等）

    -- mysql查询表名、表注释、表字段数据
    SELECT
        t.table_name AS '表名称',
        t.table_comment AS '表注释',
        c.column_name AS '字段名称',
        c.column_type AS '数据类型',
        c.column_comment AS '字段注释',
        c.column_key AS '是否主键',
        c.is_nullable AS '是否允许NULL'
    FROM
        information_schema.COLUMNS c
        JOIN information_schema.TABLES t ON c.table_name = t.table_name
    WHERE
        c.table_schema = (
    SELECT DATABASE
        ());

　　把结果集拷贝到Excel中

　　前期工作准备完毕，接下来开始干正事

import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.apache.poi.xwpf.usermodel.XWPFParagraph;
import org.apache.poi.xwpf.usermodel.XWPFRun;

import java.io.File;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class Test {

    private static void tables(){
        try {
            XWPFDocument xdoc = new XWPFDocument();
            HashMap<String, List<Map<String, String>>> hashMap = new HashMap<>();

            //获取数据
            /*
                -- mysql查询表名、表注释、表字段数据
                SELECT
                    t.table_name AS '表名称',
                    t.table_comment AS '表注释',
                    c.column_name AS '字段名称',
                    c.column_type AS '数据类型',
                    c.column_comment AS '字段注释',
                    c.column_key AS '是否主键',
                    c.is_nullable AS '是否允许NULL'
                FROM
                    information_schema.COLUMNS c
                    JOIN information_schema.TABLES t ON c.table_name = t.table_name
                WHERE
                    c.table_schema = (
                SELECT DATABASE
                    ());
             */
            File file = new File("E:\\TestExcel01.xlsx");
            List<Map<String, String>> list = ExcelUtil.readExcel3(file, 0);

            //处理数据，调整成下面的格式
            /*
                [
                    {"表名称":[
                        {},//一条条字段信息
                        {},//一条条字段信息
                        {},//一条条字段信息
                    ]}
                ]
             */
            ArrayList<Map<String, String>> arrayList = new ArrayList<>();
            String tableName = "";
            for (int i = 0; i < list.size(); i++) {
                Map<String, String> map = list.get(i);
                String tName = String.valueOf(map.get("表名称"));
                if(tableName.equals(tName)){
                    arrayList.add(map);
                }else{
                    hashMap.put(tableName,arrayList);
                    tableName = tName;
                    arrayList = new ArrayList<>();
                    arrayList.add(map);
                }

                if(list.size() - i == 1){
                    hashMap.put(tableName,arrayList);
                }
            }


            //生成内容
            for (String tName : hashMap.keySet()) {
                if("".equals(tName)){
                    continue;
                }
                List<Map<String, String>> maps = hashMap.get(tName);
                String tZs = String.valueOf(maps.get(0).get("表注释"));

                //设置文字，对表格进行描述
                XWPFParagraph xp = xdoc.createParagraph();
                xp.setSpacingBefore(0);
                XWPFRun r1 = xp.createRun();
                r1.setFontFamily("宋体");
                r1.setFontSize(12);
                r1.setTextPosition(0);

                r1.addBreak(); // 换行
                r1.setText("表名称："+tName);
                r1.addBreak(); // 换行
                r1.setText("表注释："+tZs);


                //表格标题
                String[] titles = {
                        "字段名称",
                        "字段类型",
                        "字段注释",
                        "允许空值",
                };

                //表格内容
                List<Map<String, String>> values = new ArrayList<>();
                for (Map<String, String> stringStringMap : maps) {
                    String cName = stringStringMap.get("字段名称");
                    String cType = stringStringMap.get("数据类型");
                    String cZs = stringStringMap.get("字段注释");
                    String isPri = stringStringMap.get("是否主键");
                    String isNull = stringStringMap.get("是否允许NULL");

                    //按照表格标题格式进行封装
                    HashMap<String, String> stringStringHashMap = new HashMap<>();
                    stringStringHashMap.put("字段名称",cName);
                    stringStringHashMap.put("字段类型",cType);
                    stringStringHashMap.put("字段注释",cZs);
                    stringStringHashMap.put("允许空值",isNull);

                    values.add(stringStringHashMap);
                }


                WordUtil.createSimpleTable(xdoc, titles, values);
            }

            //保存word文件
            FileOutputStream fos = new FileOutputStream("E:\\Test1.doc");
            xdoc.write(fos);
            fos.close();

            System.out.println("操作完成！");

        }catch (Exception e){
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        tables();
    }
}

　　运行main进行测试

    public static void main(String[] args) {
        tables();
    }

　　效果

　　后记

　　通过使用POI，批量生成表格，方便快捷、省心高效，项目经理用了都说好！

版权声明

作者：huanzi-qch

出处：
https://www.cnblogs.com/huanzi-qch

若标题中有“转载”字样，则本文版权归原作者所有。若无转载字样，本文版权归作者所有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文链接，否则保留追究法律责任的权利.