POI批量生成Word文档表格

　　前言

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

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * POI操作Excel工具类
 */
public class ExcelUtil {

    /**
     * 读取指定Sheet页的数据
     */
    public static List<Map<String,String>> readExcel3(File file, int sheetIndex) throws Exception {
        try (FileInputStream fs = new FileInputStream(file)) {
            XSSFWorkbook hw = new XSSFWorkbook(fs);
            XSSFSheet sheet = hw.getSheetAt(sheetIndex);

            ArrayList<Map<String,String>> list = new ArrayList<>();

            //读取表头
            List<String> headerList = new ArrayList<String>();
            XSSFRow headerRow = sheet.getRow(0);
            for (int j = 0; j < headerRow.getLastCellNum(); j++) {
                String val = getCellValue(headerRow,headerRow.getCell(j));

                //数据为空
                if (StringUtils.isEmpty(val)) {
                    continue;
                }

                headerList.add(val);
            }

            //读取数据
            for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
                XSSFRow dataRow = sheet.getRow(i);

                if (dataRow == null) {
                    continue;
                }

                HashMap<String, String> map = new HashMap<>();
                for (int j = 0; j < headerList.size(); j++) {
                    String header = headerList.get(j);
                    String val = getCellValue(dataRow,dataRow.getCell(j));
                    map.put(header, val);
                }
                list.add(map);
            }
            return list;
        }
    }

    /**
     * 获取单元格内容
     */
    private static String getCellValue(XSSFRow dataRow, Cell cell){
        String cellvalue = "";
        if (cell!=null) {
            switch (cell.getCellType()) {
                case BOOLEAN:
                    cellvalue = String.valueOf(cell.getBooleanCellValue());
                    break;
                case NUMERIC:
                    cellvalue = String.valueOf(cell.getNumericCellValue()).split("\\.")[0];
                    if(cellvalue.toLowerCase().contains("e")){
                        cellvalue = new DecimalFormat("#").format(cell.getNumericCellValue());
                        if(cellvalue.toLowerCase().contains("e")){
                            throw new RuntimeException(dataRow.getCell(4) + "/数值带E！！！");
                        }
                    }
                    break;
                case STRING:
                    cellvalue = cell.getStringCellValue();
                    break;
                case BLANK:
                    break;
                case ERROR:
                    cellvalue = String.valueOf(cell.getErrorCellValue());
                    break;
                case FORMULA:
                    try {
                        cellvalue = String.valueOf(cell.getNumericCellValue());
                    } catch (IllegalStateException e) {
                        if (e.getMessage().contains("from a STRING cell")) {
                            try {
                                cellvalue = String.valueOf(cell.getStringCellValue());
                            } catch (IllegalStateException e2) {
                                throw new RuntimeException("公式计算出错");
                            }
                        }
                    }
                    break;
                default:
                    cellvalue = String.valueOf(cell.getBooleanCellValue());
                    break;
            }
        }
        return cellvalue;
    }


    /**
     * 只支持一级表头
     *
     * @param file   文件
     * @param titleName   表标题
     * @param columnNames 列名集合，key是用来设置填充数据时对应单元格的值，label就是对应的列名，生成Excel表时，
     *                    第一维数组下标0对应值为Excel表最左边的列的列名 例：{ { key,label },{ key,label } }
     * @param dataLists   数据集合，key对应的是列名集合的key，value是要填充到单元格的值 例：ArrayList<HashMap<String key, String vaule>>
     */
    public static String createExcelFile(File file,String titleName, String[][] columnNames, ArrayList<HashMap<String, String>> dataLists) {

        //创建HSSFWorkbook对象(excel的文档对象)
        HSSFWorkbook wb = new HSSFWorkbook();
        //建立新的sheet对象（excel的表单）
        HSSFSheet sheet = wb.createSheet(titleName);//设置表单名

        //1、标题名
        //创建标题行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
        HSSFRow row1 = sheet.createRow(0);

        createCell(row1, 0, titleName);
        //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, columnNames.length - 1));

        //2、列名
        //创建列名行
        HSSFRow row2 = sheet.createRow(1);
        for (int i = 0; i < columnNames.length; i++) {
            //单元格宽度
            sheet.setColumnWidth(i, 20 * 256);
            createCell(row2, i, columnNames[i][1]);//例：[[key,label],[key,label]] 取label
        }

        //3、填充数据
        int index = 2;//标题行、列名行，所以数据行默认从第三行开始
        for (HashMap<String, String> map : dataLists) {
            //创建内容行
            HSSFRow row3 = sheet.createRow(index);
            for (int i = 0; i < columnNames.length; i++) {
                String val = map.get(columnNames[i][0]);
                createCell(row3, i, val == null ? "" : val);//例：[[key,label],[key,label]] 取key
            }
            index++;
        }

        try(FileOutputStream outputStream = new FileOutputStream(file)) {
            wb.write(outputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }

        return file.getName()+" 创建成功";
    }

    /**
     * 创建一个单元格
     */
    private static void createCell(Row row, int column, String text) {
        Cell cell = row.createCell(column);  // 创建单元格
        cell.setCellValue(text);  // 设置值
    }
}

　　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，批量生成表格，方便快捷、省心高效，项目经理用了都说好！