이번에는 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
- Dictionary 란걸 사용했는데 이걸 쓰려면 scrrun.dll 을 사용할 수 있도록 엑셀의 ‘도구 > 참조’에서 Microsoft Scripting Runtime’ 을 선택해주어야만 합니다.
- 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 코드값을 설정하는 방법이 있는지 좀 찾아봐야겠습니다.
힘껏 차라 