Today I Learned - Day 35 [엑셀 연동 ~ Json - 2]

어제는 C#에서의 Json 파일 연동 방법을 알아봤다면, 오늘은 엑셀에서 연동하는 방법을 알아보자.

 

다만, 언급하기에 앞서, 엑셀에서 Json으로 변환하는 경우에는 보통 외부 프로그램으로 변환시키는 경우가 많다.

엑셀 내부에서 처리하려면 Excel Macro로 만들어야 해서인지, 아니면 괜찮은 변환 프로그램이 많아서인지는 잘 모르지만, 인터넷에 검색 해 보면 외부 프로그램으로 변환시킨다는 내용이 매우 많다.

 

다만, 그렇게 구성하면 본 게시글을 작성하는 이유가 없을 터다.

연동을 할 때 마다 외부 프로그램을 실행해야 한다는 점도 있다. [사실 이건 큰 문제는 아니긴 하다.]

무엇보다 본인이 궁금한 내용이다.

그러니 Excel Macro를 이용해서 변환하는 방법을 알아보자.

---

Excel Macro란?

엑셀은 셀 간의 함수 연산을 이용한 통계 기록에 특화된 문서 처리용 어플리케이션이다.

그런 만큼 다종다양한 방면에서 사용되는데, 문제는 '엑셀의 기능에는 없는 것이 필요한 경우가 있다'는 점이다.

[예를 들면, 특정 동작을 수행하는 버튼이나, 일괄적으로 데이터를 추가하는 기능 같은 것들.]

엑셀 매크로는 이러한 난점을 해결하기 위해서 사용되는 기능이다.

다만, 실제로는 VBA(Visual Basic for Application)이라는 통합 개발 환경으로 작동하는 '코딩'에 해당하는 요소이기도 하다.

 

여기서 VBA에 대한 문법을 설명하기는 어려우므로, Json으로 저장하는 코드만 정리해 보자.

[본 코드는 VBA-JSON 라이브러리를 사용하여 구현되어 있다. 주소는 하단 참고.]

'Add Reference to Microsoft Scripting Runtime Library

Sub Convert()
    '// 변수 선언
    Dim sht As Worksheet
    Dim rng As Range
    Dim items As New Collection
    Dim item As New Dictionary
    Dim r As Long, c As Integer
    Dim fname As String
    Dim handle As Integer
    Dim Json As String
    
    '특정 시트 접근 함수. [시트 이름]에 해당하는 시트에 접근한다.
    Set sht = Thisworkbook.Worksheets([시트 이름])
    
    '//첫 행을 카테고리로 지정
    cat = sht.UsedRange.Rows(1).Value
    
    '// 첫 행의 가로열의 수 만큼을 1행 당 아이템 수로 지정하고 매 줄 마다 반복
    For Each rng In sht.UsedRange.Columns(1).Cells
        '// 2행 부터 테이블 내부 변수로 지정
        If rng.Row > 1 Then
            r = r + 1
            For c = 1 To UBound(cat, 2)
                '// 행의 각 요소들을 item[Dictionary]에 추가
                item(cat(1, c)) = rng.Offset(, c - 1).Value
                Next c
        
            '// 행의 전체 정리본(item)을 items[Collection]에 추가.
            items.Add item
            '//item 초기화
            Set item = Nothing
        End If
    Next rng
    
    '// 상술한 반복문의 결과물[Items]를 Json 문자열로 변환하여 Json 변수에 저장.
    Json = JsonConverter.ConvertToJson(items, Whitespace:=4)
    
    '// Json 변수의 내용을 텍스트 파일에 저장
    fname = [디렉토리 주소. (확장자명이 .json 이어야 함에 주의할 것.)]
    handle = FreeFile
    Open fname For Output As #handle
    Print #handle, Json
    Close #handle
    
End Sub

---

참고 사이트 : https://codingislove.com/excel-json/

Import Json to excel and export excel to Json (Updated 2022) - Coding is Love

관련 라이브러리 Git: https://github.com/VBA-tools/VBA-JSON

GitHub - VBA-tools/VBA-JSON: JSON conversion and parsing for VBA

코드 참고 사이트: https://konahn.tistory.com/entry/excel2JSON

엑셀 데이터를 JSON형식으로 변환