読者です 読者をやめる 読者になる 読者になる

日付変換Excelマクロ for DBUnit with Oracle

DBUnit

DBUnitOracleのデータをExcelファイルに出力すると、Date型・Timestamp型の情報は1970年1月1日からの経過ミリ秒で返ってきます。

そして、DBUnitExcelファイルのデータをDBに投入するには、Date型・Timestamp型のカラムに相当する部分はJDBC形式の日付文字列(yyyy-MM-dd HH:mm:ss形式)になっていないと取り込めません。

つまり、DBUnitで出力したExcelファイルは、そのままDBUnitでのデータ投入には使えないのです。えー、この矛盾は一体なぜ??

文句を言っても仕方ないので、Excelマクロで無理矢理解決。

使い方:
以下のようなマクロを作って呼び出してください。

Sub 全ての日付を文字列化()
    Call date2string("テーブル名1", "Date型やTimestamp型の列名1")
    Call date2string("テーブル名2", "Date型やTimestamp型の列名2")End Sub

Excelマクロのソースコード:

Const MAX_ROW = 65000	'最大行数を65000に定義
Const MAX_YEAR = 2100	'最大年を2100年に定義
Const MAX_COL_NUM = 255	'最大列数を255に定義

'指定テーブル名のシートの指定カラム列について全て日付数値を文字列に変換する
Sub date2string(tableName, colName)
    On Error Resume Next
    Set sh = Worksheets(tableName)
    If Err <> 0 Then
        Exit Sub
    End If
    colNum = findColNum(sh, colName)
    If colNum = 0 Then
        Exit Sub
    End If
        
    For i = 2 To MAX_ROW
        orgNum = sh.Cells(i, colNum).Value
        If orgNum = "" Then
            Exit Sub
        End If
        If IsNumeric(orgNum) Then
            dataStr = "'" & exDateStr(orgNum)
            sh.Cells(i, colNum).Value = dataStr
        End If
    Next i
End Sub

'数値をJDBC日付文字列に変換する
Function exDateStr(num)
'1970年1月1日からの差分を計算
    Dim tt
    Dim prevYearStart, nextYearStart
    Dim prevMonthStart, nextMonthStart
    prevYearStart = 0
    Dim months
    Dim yy, mm, dd, ddMod, hh, minutes, ss, mmsec
    num = Int(argnum / 1000)
    mmsec = argnum - num * 1000
    ss = num Mod 60
    num = Int(num / 60)
    minutes = num Mod 60
    num = Int(num / 60) + 9
    hh = num Mod 24
    num = Int(num / 24) - 1
    months = Array(31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31)
    prevYearStart = 0
    For yy = 1970 To MAX_YEAR
        If yy Mod 4 <> 0 Or (yy Mod 4 = 0 And yy Mod 100 = 0) Then
            '365日
            tt = 365
            months(1) = 28
        Else    '366日
            tt = 366
            months(1) = 29
        End If
        nextYearStart = prevYearStart + tt
        If prevYearStart <= num And num < nextYearStart Then
            'その年
            prevMonthStart = prevYearStart
            Exit For
        End If
        prevYearStart = nextYearStart
    Next yy
    If yy >= MAX_YEAR Then  '最大年を超えた
        exDateStr = ""
        Exit Function
    End If
    For mm = 0 To 11
        tt = months(mm)
        nextMonthStart = prevMonthStart + tt
        If prevMonthStart <= num And num < nextMonthStart Then
            'その月
            Exit For
        End If
        prevMonthStart = nextMonthStart
    Next mm
    dd = num - prevMonthStart
    'JDBC タイムスタンプエスケープ形式にする
    exDateStr = Format(yy, "0000") & "-" & Format(mm + 1, "00") & "-" & Format(dd + 1, "00") & " " & _
        Format(hh, "00") & ":" & Format(minutes, "00") & ":" & Format(ss, "00") & "." & Format(mmsec, "000")
End Function

'シートから指定名の列の番号(1〜)を探す。見つからなければ0を返す
Function findColNum(sh, colName)
    For i = 1 To MAX_COL_NUM
        If sh.Cells(1, i) = colName Then
            findColNum = i
            Exit Function
        End If
    Next i
    findColNum = 0
End Function