使用 PHPWord 產生 MySQL 資料庫結構文件

開發一個含資料庫的網站，不論是個人興趣或是工作需求，事後的文件製作都是「神煩」的。

至少我就不喜歡另外寫資料結構說明文件，尤其是使用 phpMyAdmin 這種 GUI 界面操作資料庫後，就覺得如果還要花很多時間來做多餘的事情，所以我就要花更多的時間來寫一個自動產生資料庫結構文件的程式 ...

大致上的想法就是把所有的說明都直接寫在資料庫內，MySQL 的資料表、欄位都有 Comment 欄位可以填寫，phpMyAdmin 的預設快速匯出也會把 Comment 匯出，因此備份以及說明可以達成一致性同步。

列出該資料庫所有資料表

SHOW TABLES

1	SHOW TABLES

資料表說明

參考： changing mysql table comment

SHOW TABLE STATUS WHERE name='TableName'

1	SHOW TABLE STATUS WHERE name='TableName'

修改資料表說明

ALTER TABLE TableName COMMENT='資料表說明\n進一步說明'

1	ALTER TABLE TableName COMMENT='資料表說明\n進一步說明'

顯示完整的資料表欄位資訊

SHOW FULL FIELDS FROM TableName

1	SHOW FULL FIELDS FROM TableName

使用 PHP 產出 docx, odt, rtf, HTML 文件

使用套件 PHPWord ，最好的學習方式就是看 samples code

觀看範例結果 samples results

下載 PHPWord 並解壓縮到網站
將 PHPWord-develop\samples\results 目錄修改為可讀寫權限
瀏覽 PHPWord-develop\samples 下拉選擇項目，點選文件類別下載

單位轉換查詢

PHPWord 預設使用的數值單位是 Twip ，想要更進一步了解可以參考 https://en.wikipedia.org/wiki/Twip

這裡有單位轉換，可以在文書編輯軟體設計後取得大致距離後再進行換算 → Topography Conversion

設定預設字型

參考至：Default font

$phpWord = new \PhpOffice\PhpWord\PhpWord();
$phpWord->setDefaultFontSize(11);

1 2	$phpWord = new \PhpOffice\PhpWord\PhpWord(); $phpWord->setDefaultFontSize(11);

<?php
/**
 * Created by PhpStorm.
 * User: hoyo
 * Date: 2015/9/21
 * Time: 下午 02:30
 */

namespace Hoyo\Front;

require OS_PATH ."/Component_Back/PHPWord-develop/src/PhpWord/Autoloader.php";
use PhpOffice\PhpWord\Autoloader;
use PhpOffice\PhpWord\Settings;

Autoloader::register();
Settings::loadConfig();

class MySQLStruct
{
    var $TN = 'MJAPP_SoundU'; // Material DEVELOP TimeIsMoney
    function __construct()
    {
        $init = new \Hoyo\Init();
        $this->init = $init;
        //$this->db = $init->PDO();

        $PDO = new \PDO("mysql:dbname=". $this->TN .";host=192.168.0.101;charset=utf8", "root", "sly-ha-123");
        $PDO->exec("set names utf8"); // php 5.3.6 之前的舊版本
        $this->db = $PDO;

        $this->smarty = $init->smarty();
        $this->LNG = $GLOBALS['L']['L'];

        $this->DefaultPage();
    }

    function DefaultPage()
    {

        $phpWord = new \PhpOffice\PhpWord\PhpWord();
        $phpWord->setDefaultFontSize(11);
        $section = $phpWord->addSection(
            array('paperSize'=>'A4', 'marginLeft'=>600, 'marginRight'=>600, 'marginTop'=>1100, 'marginBottom'=>1100)
        );

        $header = array('size' => 16, 'bold' => true);

        $sql = "SHOW TABLES";
        $pre = $this->db->prepare($sql);
        $pre->execute();
        while( $row = $pre->fetch() ) {
            $TableName = $row['Tables_in_'. $this->TN];

            // 資料表說明
            $sql = " SHOW TABLE STATUS WHERE name='{$TableName}' " ;
            $preTC = $this->db->prepare($sql);
            $preTC->execute();
            $rowTC = $preTC->fetch();
            $Comment = explode( "^", $rowTC['Comment'] );
            $TableNameComment = array_shift($Comment);
            //$TableComment = $TableNameComment;
            //$section->addText(htmlspecialchars( $TableComment, ENT_COMPAT, 'UTF-8'));

            // 資料表標題
            $TableTitle = $TableName;
            if ( strlen($TableNameComment)>0 ) $TableTitle .= ' - '. $TableNameComment;
            $section->addText(htmlspecialchars( $TableTitle, ENT_COMPAT, 'UTF-8'), $header );
            // 資料表說明
            if ( strlen($Comment[0]) >0 ) {
                $section->addText(htmlspecialchars('(' . $Comment[0] . ')', ENT_COMPAT, 'UTF-8'));
            }

            // 表格格現
            $styleTable = array('borderSize' => 6, 'borderColor' => '006699', 'cellMargin' => 80);
            // 第一行 標題格式
            $styleFirstRow = array('borderBottomSize' => 18, 'borderBottomColor' => '0000FF', 'bgColor' => '66BBFF');
            // 將表格標題樣式加入
            $phpWord->addTableStyle('Fancy Table', $styleTable, $styleFirstRow);

            // 標題垂直對齊
            $styleCell = array('valign' => 'center');
            // 標題文字粗體 水平對齊
            $fontStyle = array('bold' => true, 'align' => 'center');

            $table = $section->addTable('Fancy Table');
            $table->addRow(400);
            $table->addCell(500, $styleCell)->addText(htmlspecialchars('#', ENT_COMPAT, 'UTF-8'), $fontStyle);
            $table->addCell(3200, $styleCell)->addText(htmlspecialchars('欄位名稱', ENT_COMPAT, 'UTF-8'));
            $table->addCell(1400, $styleCell)->addText(htmlspecialchars('型態', ENT_COMPAT, 'UTF-8'));
            $table->addCell(1600, $styleCell)->addText(htmlspecialchars('預設值', ENT_COMPAT, 'UTF-8'));
            $table->addCell(1800, $styleCell)->addText(htmlspecialchars('鍵值、附加', ENT_COMPAT, 'UTF-8'));
            $table->addCell(2100, $styleCell)->addText(htmlspecialchars('說明', ENT_COMPAT, 'UTF-8'));

            $i = 1;
            $sql = " SHOW FULL FIELDS FROM `{$TableName}` ";
            $preTable = $this->db->prepare($sql);
            $preTable->execute();
            while( $rowTable = $preTable->fetch() ) {
                $table->addRow();
                $table->addCell()->addText(htmlspecialchars($i, ENT_COMPAT, 'UTF-8'));
                $table->addCell()->addText(htmlspecialchars($rowTable['Field'], ENT_COMPAT, 'UTF-8'));
                $table->addCell()->addText(htmlspecialchars($rowTable['Type'], ENT_COMPAT, 'UTF-8'));
                $table->addCell()->addText(htmlspecialchars($rowTable['Default'], ENT_COMPAT, 'UTF-8'));

                // Key & Extra 需要特別處理
                $KeyExtra = $table->addCell();
                $Key = htmlspecialchars($rowTable['Key'], ENT_COMPAT, 'UTF-8');
                $Extra = htmlspecialchars($rowTable['Extra'], ENT_COMPAT, 'UTF-8');
                $KeyExtra->addText($Key);
                $KeyExtra->addText($Extra);

                $table->addCell()->addText(htmlspecialchars($rowTable['Comment'], ENT_COMPAT, 'UTF-8'));

                $i++; // # 序號 方便和 phpMyAdmin 核對
            }


            // 下一頁 (一個資料表一頁)
            $section->addPageBreak();
        }

        // 儲存 Word
        // Word2007, ODText, HTML
        $phpWord->save('/tmp/'. $this->TN .'.docx', 'Word2007');

        $this->smarty->assign('LNG', $this->LNG);
        //echo __CLASS__;
        $this->smarty->assign('Content', 'Page/'.__CLASS__.'.tpl');
        $this->smarty->display('Layout_index.tpl');
    }

}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

<?php

/**

* Created by PhpStorm.

* User: hoyo

* Date: 2015/9/21

* Time: 下午 02:30

namespace Hoyo\Front;

require OS_PATH ."/Component_Back/PHPWord-develop/src/PhpWord/Autoloader.php";

use PhpOffice\PhpWord\Autoloader;

use PhpOffice\PhpWord\Settings;

Autoloader::register();

Settings::loadConfig();

class MySQLStruct

{

var $TN = 'MJAPP_SoundU'; // Material DEVELOP TimeIsMoney

function __construct()

{

$init = new \Hoyo\Init();

$this->init = $init;

//$this->db = $init->PDO();

$PDO = new \PDO("mysql:dbname=". $this->TN .";host=192.168.0.101;charset=utf8", "root", "sly-ha-123");

$PDO->exec("set names utf8"); // php 5.3.6 之前的舊版本

$this->db = $PDO;

$this->smarty = $init->smarty();

$this->LNG = $GLOBALS['L']['L'];

$this->DefaultPage();

}

function DefaultPage()

{

$phpWord = new \PhpOffice\PhpWord\PhpWord();

$phpWord->setDefaultFontSize(11);

$section = $phpWord->addSection(

array('paperSize'=>'A4', 'marginLeft'=>600, 'marginRight'=>600, 'marginTop'=>1100, 'marginBottom'=>1100)

);

$header = array('size' => 16, 'bold' => true);

$sql = "SHOW TABLES";

$pre = $this->db->prepare($sql);

$pre->execute();

while( $row = $pre->fetch() ) {

$TableName = $row['Tables_in_'. $this->TN];

// 資料表說明

$sql = " SHOW TABLE STATUS WHERE name='{$TableName}' " ;

$preTC = $this->db->prepare($sql);

$preTC->execute();

$rowTC = $preTC->fetch();

$Comment = explode( "^", $rowTC['Comment'] );

$TableNameComment = array_shift($Comment);

//$TableComment = $TableNameComment;

//$section->addText(htmlspecialchars( $TableComment, ENT_COMPAT, 'UTF-8'));

// 資料表標題

$TableTitle = $TableName;

if ( strlen($TableNameComment)>0 ) $TableTitle .= ' - '. $TableNameComment;

$section->addText(htmlspecialchars( $TableTitle, ENT_COMPAT, 'UTF-8'), $header );

// 資料表說明

if ( strlen($Comment[0]) >0 ) {

$section->addText(htmlspecialchars('(' . $Comment[0] . ')', ENT_COMPAT, 'UTF-8'));

}

// 表格格現

$styleTable = array('borderSize' => 6, 'borderColor' => '006699', 'cellMargin' => 80);

// 第一行標題格式

$styleFirstRow = array('borderBottomSize' => 18, 'borderBottomColor' => '0000FF', 'bgColor' => '66BBFF');

// 將表格標題樣式加入

$phpWord->addTableStyle('Fancy Table', $styleTable, $styleFirstRow);

// 標題垂直對齊

$styleCell = array('valign' => 'center');

// 標題文字粗體水平對齊

$fontStyle = array('bold' => true, 'align' => 'center');

$table = $section->addTable('Fancy Table');

$table->addRow(400);

$table->addCell(500, $styleCell)->addText(htmlspecialchars('#', ENT_COMPAT, 'UTF-8'), $fontStyle);

$table->addCell(3200, $styleCell)->addText(htmlspecialchars('欄位名稱', ENT_COMPAT, 'UTF-8'));

$table->addCell(1400, $styleCell)->addText(htmlspecialchars('型態', ENT_COMPAT, 'UTF-8'));

$table->addCell(1600, $styleCell)->addText(htmlspecialchars('預設值', ENT_COMPAT, 'UTF-8'));

$table->addCell(1800, $styleCell)->addText(htmlspecialchars('鍵值、附加', ENT_COMPAT, 'UTF-8'));

$table->addCell(2100, $styleCell)->addText(htmlspecialchars('說明', ENT_COMPAT, 'UTF-8'));

$i = 1;

$sql = " SHOW FULL FIELDS FROM `{$TableName}` ";

$preTable = $this->db->prepare($sql);

$preTable->execute();

while( $rowTable = $preTable->fetch() ) {

$table->addRow();

$table->addCell()->addText(htmlspecialchars($i, ENT_COMPAT, 'UTF-8'));

$table->addCell()->addText(htmlspecialchars($rowTable['Field'], ENT_COMPAT, 'UTF-8'));

$table->addCell()->addText(htmlspecialchars($rowTable['Type'], ENT_COMPAT, 'UTF-8'));

$table->addCell()->addText(htmlspecialchars($rowTable['Default'], ENT_COMPAT, 'UTF-8'));

// Key & Extra 需要特別處理

$KeyExtra = $table->addCell();

$Key = htmlspecialchars($rowTable['Key'], ENT_COMPAT, 'UTF-8');

$Extra = htmlspecialchars($rowTable['Extra'], ENT_COMPAT, 'UTF-8');

$KeyExtra->addText($Key);

$KeyExtra->addText($Extra);

$table->addCell()->addText(htmlspecialchars($rowTable['Comment'], ENT_COMPAT, 'UTF-8'));

$i++; // # 序號方便和 phpMyAdmin 核對

}

// 下一頁 (一個資料表一頁)

$section->addPageBreak();

}

// 儲存 Word

// Word2007, ODText, HTML

$phpWord->save('/tmp/'. $this->TN .'.docx', 'Word2007');

$this->smarty->assign('LNG', $this->LNG);

//echo __CLASS__;

$this->smarty->assign('Content', 'Page/'.__CLASS__.'.tpl');

$this->smarty->display('Layout_index.tpl');

}

完成品

5,205 total views, 1 views today

使用 PHPWord 產生 MySQL 資料庫結構文件

列出該資料庫所有資料表

資料表說明

使用 PHP 產出 docx, odt, rtf, HTML 文件

Related Post

發佈留言

使用 PHPWord 產生 MySQL 資料庫結構文件

列出該資料庫所有資料表

資料表說明

使用 PHP 產出 docx, odt, rtf, HTML 文件

Related Post

CodeIgniter 3 - PHPMailer

CentOS 7 - 選擇安裝 PHP 版本

PHP - 並行處理

發佈留言