blah blah

java properties 추출 위한 엑셀 매크로

이번에는 excel macro. 급조한거라 함수명이나 로직이 조악하기는 하지만 …

**********

자바 웹 개발 시 웹상의 다국어 페이지 관리 등을 위해 ResourceBundle 방식을 이용해서 메시지나 레이블 등을 properties 파일에 넣어 활용하곤 합니다.

그런데 보통 실제 웹 디플로이 전에는 관리를 위해 이 컨텐츠들을 엑셀로 취합 관리하는 경우가 일반적이고 변경 시에는 여기 내용을 복사해서

  • 텍스트 파일에 붙여 넣은 후 한글 등의 경우에는 native2ascii 를 실행해서 변환해주거나
  • 이클립스 사용자들은 이 추가 작업이 불편해서 PropetiesEditor 등을 사용하는 듯 합니다.

(옛날에는 보통 이런 식으로 했었지만 아마 요즘은 진일보한 방법 쓰고 있겠죠? 방법 공유 좀 해주세요 )

오늘은 엑셀에서 관리하는 내용을 properties 파일로 자동 추출하기 위해 짜본 간단한 매크로를 공유 차원에서 적어봅니다.

( 물론 POI 나 JExcelApi 등 이용해서 자바로도 충분히 가능한 작업입니다만 이 작업을 수행할 담당자가 개발자가 아니란 점을 고려하면 … )

  A B C D
1        
2   메시지 관리    
3 일련번호 영문 국문 중문
4 EST00001 Detail Schedule 상세 스케줄 详细船期
5 EST00002 Route Information 라우트 정보 路径信息

가령 엑셀의 문서 형식이 위와 같다고 할 때 매크로 소스는 아래와 같습니다.

소스의 VBA 문법에 관해서 설명은 건너뛰겠습니다.

데이타 추출해서 텍스트 파일로 저장 시 유니코드 값으로 전환하는 기능은 get_codepoints(…)에 구현해 놓았습니다. 여기서 핵심은 Hex(AscW(Mid$(msg, i, 1))) 입니다.

이 매크로 그런대로 잘 잘동했습니다만 중문의 간체의 유니코드를 처리 못하는 문제가 있더군요.

위 엑셀 샘플에서 详细船期 란 데이타 중 ‘详细’가 간자체입니다. 해당 글자의 번자는 ‘詳細’라고 하던데 아무튼 위에 언급한 Hex(…) 구문만으로는 ‘详细’의 정확한 값을 못가져옵니다.

그래서 간체 문제는 중문의 LocaleID (LCID) 값을 지정해주는 것으로 일단 해결했습니다 .

txt = txt & StrConv(Hex(Asc(Mid$(msg, i, 1))), vbFromUnicode, 1028) 이런 식으로 하면 되네요.

대충 기본적인 문제들 해결한 VAB 매크로 소스는 다음과 같습니다.

Sub ExportText()
    Dim fso As Object
    Dim dict As Dictionary, dict1 As Dictionary
    Dim v As Variant
    Dim sPath As String
    Dim key_col As String

    title_str = GetTitle()
  
    Set dict = New Dictionary
    With dict
       .Add “en”, title_str
       .Add “ko”, title_str
       .Add “zh”, title_str
    End With
   
    Set dict1 = New Dictionary
    With dict1
       .Add “ko”, Array(4, 4)
       .Add “en”, Array(5, 5)
       .Add “zh”, Array(6, 6)
    End With

    es_pos = pFindRowPos(“ESW”)
    es_title = vbCrLf & “#ES(e-Service)” & vbCrLf
   
    last_row = GetLastRow(“Sheet1”, “A”)
    For i = 3 To last_row
        key_col = Cells(i, 1)
        If i = es_pos Then
            For Each v In dict.keys
                dict(v) = dict(v) & es_title
            Next
        End If
       
        If key_col <> “” Then
            For Each v In dict.keys
                dict(v) = GetPropertieString(dict(v), key_col, Cells(i, dict1(v)(0)), v)
            Next
        End If
    Next i
   
    sPath = ActiveWorkbook.Path

    Workbooks.Open sPath & “\contents_v1.0.xlsx”
        find_pos = pFindRowPos(“WS”)
        last_row = GetLastRow(“Sheet1”, “A”)
       
        For i = find_pos To last_row
            key_col = Cells(i, 1)
            If key_col <> “” Then
                For Each v In dict.keys
                    dict(v) = GetPropertieString(dict(v), key_col, Cells(i, dict1(v)(1)), v)
                Next
            End If
        Next i
    ActiveWorkbook.Close

    Set fso = CreateObject(“scripting.filesystemobject”)
    For Each v In dict.keys
        r = CreateFile(fso, sPath, v, dict.Item(v))
    Next
    Set fso = Nothing
   
    MsgBox “done”
End Sub

Function GetTitle()
    GetTitle = “##############################” & vbCrLf _
             & “# Message Properties” & vbCrLf
             & “# 1. AM(Admin)” & vbCrLf _
             & “#################################” & vbCrLf _
             & “#CM(Common)” & vbCrLf
End Function

Private Function pFindRowPos(stext As Variant, _
  Optional searchdirection As XlSearchDirection = xlNext, _
  Optional searchorder As XlSearchOrder = xlByRows) As Long

    Dim lresult As Long, org As Range

    Set org = Cells.Find(what:=stext, LookIn:=xlValues, _
    lookat:=xlPart, searchorder:=searchorder, _
                 searchdirection:=searchdirection, _
    MatchCase:=False, searchformat:=False)

    If Not org Is Nothing Then lresult = org.Row

    pFindRowPos = lresult

    Set org = Nothing

End Function

Function GetLastRow(data_sheet As String, key_column As String)
    Worksheets(data_sheet).Activate
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, key_column).End(xlUp).Row
    End With
   
    GetLastRow = LastRow
End Function

Function GetPropertieString(curr_txt As String, key_col As String, cel_val As String, lang As Variant)
    Dim result As String
    result = curr_txt & key_col & ” = ” & GetCodePoints(cel_val, lang) & vbCrLf
   
    GetPropertieString = result
End Function

Private Function CreateFile(fso As Object, sPath As String, prop_file As Variant, msg_str As String)
    Set lang_prop = fso.CreateTextFile(sPath & “\message_” & prop_file & “.properties”, True)
    With lang_prop
        .Write msg_str
        .Close
    End With
End Function

Function GetCodePoints(msg As String, lang As Variant)
    Dim txt As String
    Dim iChar As Integer
   
    msg = Replace(msg, Chr(10), ” “)
    For i = 1 To Len(msg)

        iChar = AscW(Mid$(msg, i, 1))

        If iChar = 63 Then
            If lang = “zh” Then
                If Mid$(msg, i, 1) = “?” Then
                    txt = txt & “?”
                Else
                    txt = txt & StrConv(Hex(Asc(Mid$(msg, i, 1))), vbFromUnicode, 1028)  ‘간체
                End If
            Else
                txt = txt & “?”
            End If
        ElseIf iChar = 160 Then              ‘공백 특수문자 처리
            txt = txt & ” ”
        ElseIf (iChar < 128 And iChar > 0) Then ‘Or (iChar > 8210 And iChar < 8304) Then   ‘“ ” ‘ ’ 처리
            txt = txt & Mid$(msg, i, 1)
        Else
            uni = StrConv(Hex(AscW(Mid$(msg, i, 1))), vbLowerCase)
            If Len(uni) = 2 Then
                uni = “00” & uni             ‘ub7, ub0, ue1
            End If
            txt = txt & “\u” & uni
        End If
    Next i

    GetCodePoints = txt
End Function

  1. Dictionary 란걸 사용했는데 이걸 쓰려면 scrrun.dll 을 사용할 수 있도록 엑셀의 ‘도구 > 참조’에서 Microsoft Scripting Runtime’ 을 선택해주어야만 합니다. 
  2. pFindRowPos 라고 특정 문자열이 들어있는 셀의 row 위치 구하는 함수는 http://excelvbamacro.com/how-to-find-row-position-of-a-particular-text.html 에서 구했습니다.

 덤으로 이 기능을 ruby 로 간단하게 구현한 테스트 코드도 적어 봅니다.

require ‘win32ole’
require ‘active_support’
 
#excel = WIN32OLE::new(‘excel.Application’)
#sheet = excel.Workbooks.Open(‘E:\test1.xlsx’).Worksheets(‘Sheet3’)
 
excel = WIN32OLE::connect(‘excel.Application’)
sheet = excel.activesheet
sheet.Range(‘B4:E200’).columns.each { |col| col.cells.each { |cell| puts cell.Value + ” : ” + ActiveSupport::JSON.encode(cell.Value) } }
 
루비 소스는 제대로 쓰려면 한참 다듬어야 하지만 그래도 기본 기능은 들어가 있으니 … ( ActiveSupport::JSON.encode(cell.Value)가 unicode 의 code print 값 구하는 부분입니다.)
아직 간자 문제 등은 처리하지 않은 상태인데 엑셀에서 해결한 방식처럼 encode 함수 사용 시에 Locale 코드값을 설정하는 방법이 있는지 좀 찾아봐야겠습니다.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s