Note

const.py

#Excel file path
excelFilePath = "./"
#Excel file name
excelFileName = "Table definition.xlsx"
#Migration file output destination
laravelMigrationFilePath = excelFilePath
#Migration file name
laravelMigrationTimeStamp = "0000_00_00_000000"
#File character code
fileEncode = "UTF-8"
#Line feed code
fileIndention = "\n"
#Table definition exclusion sheet list
exceptionSheetName = ['Table list','table definition']
#Table information
indexTableData = 4
#table name
indexTableNmae = 6
#Table ID
indexTableId = 36
#Column information start row
indexColumnRow = 7

# ***************************************
#Table definition information
# ***************************************
#Logical name
indexLogicalName = 2
#Physical name
indexPhysicalName = 14
#Mold
indexShape = 26
#Signed
indexUnsigned = 29
#Fill with 0
indexZeroFill = 31
#Column length
indexLength = 33
#Column length (decimal)
indexFew = 35
# PK
indexPrimaryKey = 40
# FK
indexForeignKey = 42
# UK
indexUniqueKey = 44
# AI
indexAutoIncrement = 46
# NN
indexNotNull = 48
# DF
indexDefault = 50
# IDX
indexIdx = 52

common.py

import re
import const

#Snake case to camel case conversion
def snakeCaseToCamelCase(strValue):
    return re.sub("_(.)",lambda x:x.group(1).upper(),strValue)

def spaceInsert(spaceCount):
    strScript = ""
    for num in range(spaceCount):
        strScript = strScript + " "
    return strScript

def bracketsStart():
    return "{" + const.fileIndention

def bracketsEnd():
    return "}" + const.fileIndention

def strSpaceInsert(spaceCount, strValue):
    return spaceInsert(spaceCount) + strValue

def strNone(strValue):
    if strValue is None:
        return ""
    return strValue

docTableDefinition.py

import common
import const

def columnDefinition(rowData):
    addrs = []
    addrs.append(common.strNone(rowData[const.indexLogicalName].value))
    addrs.append(common.strNone(rowData[const.indexPhysicalName].value))
    addrs.append(common.strNone(rowData[const.indexShape].value))

    indexUnsignedFlg = str(False)
    if rowData[const.indexUnsigned].value is not None:
        indexUnsignedFlg = str(True)
    addrs.append(indexUnsignedFlg)

    indexZeroFillFlg = str(False)
    if rowData[const.indexZeroFill].value is not None:
        indexZeroFillFlg = str(True)
    addrs.append(indexZeroFillFlg)

    addrs.append(str(common.strNone(rowData[const.indexLength].value)))
    addrs.append(str(common.strNone(rowData[const.indexFew].value)))

    indexPrimaryKeyFlg = str(False)
    if rowData[const.indexPrimaryKey].value is not None:
        indexPrimaryKeyFlg = str(True)
    addrs.append(indexPrimaryKeyFlg)

    indexForeignKeyFlg = str(False)
    if rowData[const.indexForeignKey].value is not None:
        indexForeignKeyFlg = str(True)
    addrs.append(indexForeignKeyFlg)

    indexUniqueKeyFlg = str(False)
    if rowData[const.indexUniqueKey].value is not None:
        indexUniqueKeyFlg = str(True)
    addrs.append(indexUniqueKeyFlg)

    indexAutoIncrementFlg = str(False)
    if rowData[const.indexAutoIncrement].value is not None:
        indexAutoIncrementFlg = str(True)
    addrs.append(indexAutoIncrementFlg)

    indexNotNullFlg = str(False)
    if rowData[const.indexNotNull].value is not None:
        indexNotNullFlg = str(True)
    addrs.append(indexNotNullFlg)

    indexDefaultFlg = str(False)
    if rowData[const.indexDefault].value is not None:
        indexDefaultFlg = str(True)
    addrs.append(indexDefaultFlg)

    return addrs

laravelMigrations.py

import const
import common

#Laravel Migration Class name conversion
def snakeCaseToCamelCaseForLaravelMigrationClassName(strValue):
    laravelMigrationClassName = common.snakeCaseToCamelCase(strValue)
    return laravelMigrationClassName[0].upper() + laravelMigrationClassName[1:]

#Laravel Migration header part
def laravelMigrationHeader(strValue):
    strScript = "<?php" + const.fileIndention
    strScript = strScript + const.fileIndention
    strScript = strScript + "use Illuminate\Support\Facades\Schema;" + const.fileIndention
    strScript = strScript + "use Illuminate\Database\Schema\Blueprint;" + const.fileIndention
    strScript = strScript + "use Illuminate\Database\Migrations\Migration;" + const.fileIndention
    strScript = strScript + const.fileIndention
    strScript = strScript + "class " + snakeCaseToCamelCaseForLaravelMigrationClassName(strValue) + " extends Migration" + const.fileIndention
    strScript = strScript + common.bracketsStart()
    return strScript

#Laravel Migration footer
def laravelMigrationFooter(strValue):
    return common.bracketsEnd()

#Laravel Migration Up Method Header
def laravelMigrationUpHeader(strValue):
    strScript = common.strSpaceInsert(4,"/**" + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(5,"*Migration execution" + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(5,"* @return void" + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(5,"*/" + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(4,"public function up()" + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(4,common.bracketsStart())
    return strScript

def laravelMigrationUpSchema(strValue, aryColumns):
    strScript = common.strSpaceInsert(8,"Schema::create('"+ strValue +"', function (Blueprint $table) {" + const.fileIndention)
    for column in aryColumns:
        strScript = strScript + common.strSpaceInsert(12, column + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(12, laravelMigrationTableEngine() + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(12, laravelMigrationTableCharset() + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(12, laravelMigrationTableCollation() + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(8,"});" + const.fileIndention)
    return strScript
        
#Laravel Migration Up Method Footer
def laravelMigrationUpFooter(strValue):
    return common.strSpaceInsert(4,common.bracketsEnd())

# Laravel Migration Down Method
def laravelMigrationDown(strValue):
    strScript = common.strSpaceInsert(4,"/**" + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(5,"*Undo migration" + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(5,"* @return void" + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(5,"*/" + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(4,"public function down()" + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(4,common.bracketsStart())
    strScript = strScript + common.strSpaceInsert(8,"Schema::drop('" + strValue + "');" + const.fileIndention)
    strScript = strScript + common.strSpaceInsert(4,common.bracketsEnd())
    return strScript

# Laravel Migration Script
def laravelMigrationScript(strValue, aryColumns):
    strScript = laravelMigrationHeader(strValue)
    strScript = strScript + laravelMigrationUpHeader(strValue)
    strScript = strScript + laravelMigrationUpSchema(strValue, aryColumns)
    strScript = strScript + laravelMigrationUpFooter(strValue)
    strScript = strScript + laravelMigrationDown(strValue)
    strScript = strScript + laravelMigrationFooter(strValue)
    return strScript

def laravelMigrationTableEngine(strValue = "InnoDB"):
    return "$table->engine = '" + strValue + "';"

def laravelMigrationTableCharset(strValue = "utf8mb4"):
    return "$table->charset = '" + strValue + "';"

def laravelMigrationTableCollation(strValue = "utf8mb4_unicode_ci"):
    return "$table->collation = '" + strValue + "';"

def laravelMigrationTableIncrements(strValue, strShape):
    strScript = ""

    if strShape == 'bigint':
        strScript = "$table->bigIncrements('" + strValue + "')"
    elif strShape == 'integer':
        strScript = "$table->increments('" + strValue + "')"
    elif strShape == 'mediumint':
        strScript = "$table->mediumIncrements('" + strValue + "')"
    elif strShape == 'smallint':
        strScript = "$table->smallIncrements('" + strValue + "')"
    elif strShape == 'tinyint':
        strScript = "$table->tinyIncrements('" + strValue + "')"
    else:
        strScript = "$table->increments('" + strValue + "')"

    return strScript

def laravelMigrationTableString(strValue, strShape, strLength):
    strScript = ""

    if strShape == 'char':
        strScript = "$table->char('" + strValue + "', " + strLength + ")"
    elif strShape == 'varchar':
        strScript = "$table->string('" + strValue + "', " + strLength + ")"
    elif strShape == 'text':
        strScript = "$table->text('" + strValue + "')"
    elif strShape == 'mediumText':
        strScript = "$table->mediumText('" + strValue + "')"
    elif strShape == 'longText':
        strScript = "$table->longText('" + strValue + "')"

    return strScript

def laravelMigrationTableNumerical(strValue, strShape):
    strScript = ""
    if strShape == 'bigint':
        strScript = "$table->bigInteger('" + strValue +  "')"
    elif strShape == 'integer':
        strScript = "$table->integer('" + strValue +  "')"
    elif strShape == 'mediumint':
        strScript = "$table->mediumInteger('" + strValue +  "')"
    elif strShape == 'smallint':
        strScript = "$table->smallInteger('" + strValue +  "')"
    elif strShape == 'tinyint':
        strScript = "$table->tinyInteger('" + strValue +  "')"
    elif strShape == 'unsignedbigint':
        strScript = "$table->unsignedBigInteger('" + strValue +  "')"
    elif strShape == 'unsignedinteger':
        strScript = "$table->unsignedInteger('" + strValue +  "')"
    elif strShape == 'unsignedmediumint':
        strScript = "$table->unsignedMediumInteger('" + strValue +  "')"
    elif strShape == 'unsignedsmallint':
        strScript = "$table->unsignedSmallInteger('" + strValue +  "')"
    elif strShape == 'unsignedtinyint':
        strScript = "$table->unsignedTinyInteger('" + strValue +  "')"

    return strScript

def laravelMigrationTableComment(strValue, strLogicalName):
    return strValue + "->comment('" + strLogicalName + "');"

laravelMigrationCreateScript.py

import openpyxl
import pprint

import common
import const
import laravelMigrations
import docTableDefinition

wb = openpyxl.load_workbook(const.excelFilePath + const.excelFileName)

for sheets in wb.sheetnames:
    rows = []
    strTableId = wb[sheets].cell(row=const.indexTableData, column=const.indexTableId).value
    for row in wb[sheets].iter_rows(min_row=const.indexColumnRow):
        if row[const.indexLogicalName].value is None:
            break

        # [0] :Logical name
        # [1] :Physical name
        # [2] :Mold
        # [3] :Signed
        # [4] :Fill with 0
        # [5] :Column length
        # [6] :Column length (decimal)
        # [7] :PK
        # [8] :FK
        # [9] :UK
        # [10]:AI
        # [11]:NN
        # [12]:DF
        # [13]:IDX

        strScriptData = docTableDefinition.columnDefinition(row)
        strScript = ""

        # Auto Increment
        if strScriptData[10] == "True":
            strScript = laravelMigrations.laravelMigrationTableIncrements(strScriptData[1], strScriptData[2])
        
        #String column
        elif strScriptData[2] == "char" \
            or strScriptData[2] == "varchar" \
            or strScriptData[2] == "text" \
            or strScriptData[2] == "mediumText" \
            or strScriptData[2] == "longText" :
            strScript = laravelMigrations.laravelMigrationTableString(strScriptData[1], strScriptData[2], strScriptData[5])

        #Numeric column
        elif strScriptData[2] == "bigint" \
            or strScriptData[2] == "integer" \
            or strScriptData[2] == "mediumint" \
            or strScriptData[2] == "smallint" \
            or strScriptData[2] == "tinyint" :

            if strScriptData[3] == "True":
                strScriptData[2] = "unsigned" + strScriptData[2]

            strScript = laravelMigrations.laravelMigrationTableNumerical(strScriptData[1], strScriptData[2])


        # Comment
        strScript = laravelMigrations.laravelMigrationTableComment(strScript, strScriptData[0])
        
        #print(strScript)

        rows.append(strScript)
    
    print(laravelMigrations.laravelMigrationScript(strTableId, rows))

#        print(docTableDefinition.columnDefinition(row))
#        rows.append(docTableDefinition.columnDefinition(row))



#for row in rows:
#    print(row)
#    print(",".join(row))

test.py

import openpyxl
import pprint

import const
#import common
import laravelMigrations

#wb = openpyxl.load_workbook("Table definition.xlsx")
wb = openpyxl.load_workbook(const.excelFilePath + const.excelFileName)
print(wb.sheetnames)
#print(len(wb.sheetnames))
#print(wb.worksheets[0].title)

#print("")

for sheets in wb.sheetnames:
    for row in wb[sheets].iter_rows(min_row=const.indexColumnRow):
        addrs = []
        addrs.append(row[const.indexLogicalName].value)
        print(",".join(addrs))
        break


#for row in wb.worksheets[0].iter_rows(min_row=const.indexColumnRow):
#    addrs = []
#    for cell in row:
#        addrs.append(cell.coordinate)
#    print(",".join(addrs))
#    print(row[const.indexLogicalName].value)
#    print(row[const.indexPhysicalName].value)
#    print(row[const.indexShape].value)

#    addrs.append(row[const.indexLogicalName].value)
#   addrs.append(row[const.indexPhysicalName].value)
#    addrs.append(row[const.indexShape].value)
#    indexUnsignedFlg = str(False)
#    if row[const.indexUnsigned].value is not None:
#        indexUnsignedFlg = str(True)
#    addrs.append(indexUnsignedFlg)
#    print(",".join(addrs))
#    break

#    print(wb.worksheets[0].max_row)

#print(common.snakeCaseToCamelCase("aaa_bbb_ccc"))

#print(laravelMigrations.snakeCaseToCamelCaseForLaravelMigrationClassName("aaa_bbb_ccc"))

#print(laravelMigrations.laravelMigrationScript("aaa_bbb_ccc"))