按数据字典生成生成Excel

    近日实施工程师提了个需求，要把MySQL中的表按注释导出Excel。试了一下SQLYog, Navigate等工具，都不能导成想要的表格。如果要手工操作的话，字段少还好说，100+的字段，也是很蛋疼。所以写了个工具类。

    创建个普通Maven工程，pom.xml如下：

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>comzf</groupId>
    <artifactId>zf-tools-sql2excel</artifactId>
    <packaging>jar</packaging>
    <version>0.0.1</version>
    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>
    <name>zf-tools-sql2excel</name>
    <url>http://maven.apache.org</url>
    <dependencies>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.11</version>
            <scope>test</scope>
        </dependency>
        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.49</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.12</version>
        </dependency>
        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>29.0-jre</version>
            <type>bundle</type>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.11</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
    </dependencies>
</project>

获取注释使用的SQL语句是：

SHOW FULL COLUMNS FROM TALBE_NAME

从ResultSet中读取Comment即可。为了处理方便，使用LinkedHashMap存储返回结果。

连接MySQL和获取注释的类如下：

package com.xx.database;

import com.google.common.base.Strings;
import com.google.common.collect.Maps;
import lombok.Builder;
import org.apache.commons.lang3.BooleanUtils;

import java.sql.*;
import java.util.Map;

@Builder
public class MysqlClient {

    private String database;
    private String tableName;
    private String user;
    private String password;

    public Connection connect() throws ClassNotFoundException, SQLException {

        if (Strings.isNullOrEmpty(database)) {
            throw new IllegalArgumentException("database can not be empty");
        }
        String driver = "com.mysql.jdbc.Driver";
        String url = String.format("jdbc:mysql://localhost:3306/%s?characterEncoding=utf8&useSSL=false", database);
        Class.forName(driver);
        Connection connection = DriverManager.getConnection(url, user, password);
        return connection;
    }

    /***
     * 查询表
     */
    public Map<String, String> showCreateTable() throws SQLException, ClassNotFoundException {

        if (Strings.isNullOrEmpty(tableName)) {
            throw new IllegalArgumentException("tableName can not be empty");
        }

        String sql = String.format("SHOW FULL COLUMNS FROM %s", tableName);

        System.out.printf("executed sql is: %s\n", sql);
        Connection connection = connect();

        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);


        ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
        int columnCount = resultSetMetaData.getColumnCount();
        for (int i = 1; i < columnCount; i++) {

            String columnName = resultSetMetaData.getColumnName(i);
            System.out.println(columnName);
        }

        Map<String, String> resultMap = Maps.newLinkedHashMap();
        while(resultSet.next()) {

            String field = resultSet.getString("Field");
            String comment = resultSet.getString("Comment");
            resultMap.put(field, comment);
        }

        close(resultSet);
        close(statement);
        close(connection);

        return resultMap;
    }

    private void close(Connection connection) throws SQLException {
        if (connection != null && BooleanUtils.isFalse(connection.isClosed())) {
            connection.close();
        }
    }

    private void close(Statement statement) throws SQLException {
        if (statement != null && BooleanUtils.isFalse(statement.isClosed())) {
            statement.close();
        }
    }

    private void close(ResultSet resultSet) throws SQLException {
        if (resultSet != null && BooleanUtils.isFalse(resultSet.isClosed())) {
            resultSet.close();
        }
    }
}

注释拿到后，下一步就要生成Excel，这里使用POI。写了个工具类，代码如下：

package com.xx.excel;

import com.google.common.collect.Lists;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Collections;
import java.util.List;

public class ExcelUtils {

    public static HSSFWorkbook createWorkbook() {

        HSSFWorkbook workbook = new HSSFWorkbook();
        return workbook;
    }

    public static HSSFSheet createSheet(HSSFWorkbook workbook) {
        return workbook.createSheet();
    }

    public static HSSFSheet createSheet(HSSFWorkbook workbook, String sheetName) {
        return workbook.createSheet(sheetName);
    }

    public static HSSFRow createRow(HSSFSheet sheet, int rownum) {
        return sheet.createRow(rownum);
    }

    public static HSSFCell createCell(HSSFRow row, int column) {
        return row.createCell(column);
    }

    public static HSSFCell createCell(HSSFRow row, int column, CellType cellType) {
        return row.createCell(column, cellType);
    }

    public static void setExplicitList(HSSFCell cell, String[] list, HSSFSheet sheet) {

        List<String> arrayList = Lists.newArrayList();
        Collections.addAll(arrayList, list);
        System.out.println("column index: " + cell.getColumnIndex() + ", list: " + arrayList);
        CellRangeAddressList regions = new CellRangeAddressList(1, 255, cell.getColumnIndex(), cell.getColumnIndex());
        //生成下拉框内容
        DVConstraint constraint = DVConstraint.createExplicitListConstraint(list);
        //绑定下拉框和作用区域
        HSSFDataValidation data_validation = new HSSFDataValidation(regions, constraint);
        //对sheet页生效
        sheet.addValidationData(data_validation);
    }

    public static void save(String filename, HSSFWorkbook workbook) throws IOException {

        File file = new File(filename);
        if (file.exists()) {
            file.delete();
        }

        FileOutputStream fos = new FileOutputStream(file);
        workbook.write(fos);
        fos.close();
    }
}

生成Excel的需求：

1. 标题生成。

2. 下拉框，如字段有状态位的时候，形成下拉框。如：字段含义:状态-描述，多个状态用逗号（英文）分隔。

示例： 持有者类型:natural-自然人,enterprises-法人,organization-团体组织,other-其他

代码如下：

package com.xx.excel;

import com.google.common.collect.Lists;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.IOException;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

public class ExcelGenerator {

    private Map<String, String> data;

    public ExcelGenerator(Map<String, String> data) {
        if (data == null || data.isEmpty()) {
            throw new IllegalArgumentException("data can not be empty");
        }
        this.data = data;
    }

    public void generate(String filename) throws IOException {

        HSSFWorkbook workbook = ExcelUtils.createWorkbook();
        HSSFSheet sheet = ExcelUtils.createSheet(workbook);

        HSSFRow firstrow = ExcelUtils.createRow(sheet, 0);
        //
        int count = 0;
        Iterator<String> iterator = data.keySet().iterator();
        while (iterator.hasNext()) {
            String key = iterator.next();
            String value = data.get(key);

            HSSFCell cell = ExcelUtils.createCell(firstrow, count);
            cell.setCellValue(value);

            // 状态:1-有效,2-无效
            // 状态:insert-新增,update-变更,cancel-注销,corr-纠错,upload-仅上传附件
//            ExcelUtils.setExplicitList();
            if (value.indexOf(':') > 0 && value.indexOf('-') > 0 && value.indexOf(',') > 0) {
                String extractValue = value.substring(value.indexOf(':') + 1);
                System.out.println("extractValue: " + extractValue);
                cell.setCellValue(value.substring(0, value.indexOf(':')));

                List<String> list = Lists.newArrayList();

                String[] splitExtractValues = extractValue.split(",");
                for (String splitExtractValue : splitExtractValues) {
                    System.out.println("value split with '-' :" + splitExtractValue);
                    String[] nameValuePair = splitExtractValue.split("-");
                    list.add(nameValuePair[1]);
                }

                String[] array = new String[list.size()];
                ExcelUtils.setExplicitList(cell, list.toArray(array), sheet);
            }
            count++;
        }
        ExcelUtils.save(filename, workbook);

    }
}

最后是启动主类

public class App {

    public static void main(String[] args) {

        String database = "test";
        String user = "root";
        String password = "123456";
        String tableName = "ex_yljgzyxkz_1";

        MysqlClient client = MysqlClient.builder()
                .database(database)
                .user(user)
                .password(password)
                .tableName(tableName)
                .build();

        try {
            Map<String, String> resultMap = client.showCreateTable();
            System.out.println(resultMap);

            ExcelGenerator generator = new ExcelGenerator(resultMap);
            generator.generate(tableName + ".xlsx");

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