前言

　　当我们在写设计文档，或者是其他涉及到数据架构、表结构时，可以用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

出处：

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