[파이썬/업무자동화] 여러 엑셀 파일의 모든 시트 하이퍼링크 걸기

  실제 업무 내용 중 불편한 부분이 있어 자동화해보려고 작성하는 포스팅. 다른 회사들은 어떻게 엑셀파일을 활용하고 있는지는 모르겠으나 우리 회사에는 시트수가 굉장히 많은 엑셀 파일이 존재한다.(굉장히 비효율적이라고 생각..)

예를 들어서 아래와 같이 한 파일에 Sheet 수가 엄청 많다. 심지어 새로운 시트가 지속적으로 업데이트 된다.

 

< 어디가 처음인지 마지막인지 모르는 Sheet들>

 

 실제 다른 회사에서 이런 경우가 많은지는 잘모르겠지만 예시로 저 많은 시트 중 Sheet100을 찾아가야한다고 해보자. 보통은 아래처럼 찾지 않을까??

 

< 작품명 : 인내와 기다림 >

 

  최악의 상황을 가정해보기 위해 위와 같이 시트가 무더기인 파일이 여러개 있다고 해보자.(실제 우리 회사가 그렇습니다..) 필요한 시트의 정보만을 찾아서 보고 싶을 때 굉장히 번거로울 것이다.

 

하... 머리야

 

  이번 포스팅은 각 파일의 시트를 쉽고 빠르게 찾을 수 있는 방법이 뭐가 있을까 고민하다 작성한 내용이다. 엑셀의 내부 하이퍼링크기능이 있지만 각 엑셀파일마다 개별 시트를 연결해주기 위해 일일히 설정을 해줘야 한다. 그럼 일이 더 많아지지 않을까?? 그럼 파이썬을 통해 엑셀시트가 추가되어도 하이퍼링크를 주기적으로 업데이트할 수 있는 프로그램을 만들어보자는 컨셉으로 코드를 작성해보았다.

 

  이번 코드 컨셉은 아래와 같다.

 

Concept 1. 특정 폴더의 모든 엑셀 파일, 모든 시트를 대상으로 한다.

Concept 2. 사용자는 엑셀파일 시트 정보가 담긴 "시트목록.xlsx" 엑셀파일을 들고있으면 모든 엑셀파일의 시트에 접근할 수 있다.

Concept 3. 엑셀 파일의 시트는 계속 추가 업데이트되므로 재실행시 "시트목록.xlsx"에 자동으로 업데이트 된다.

 

 

 

1. 엑셀의 하이퍼링크 함수 사용법 (HYPERLINK)

 

  먼저 엑셀의 HYPERLINK 함수 기능을 활용하고자 했다. 사용하기 전에 HYPERLINK 함수에 대한 내용을 정리해보았다. 기본 사용 문법은 아래와 같다.

 

* 함수 사용법 =HYPERLINK(link_ location, [friendly name])

 

-. link_location : 링크정보. 하이퍼링크로 연결시킬 주소이다.(웹페이지 주소, 파일 절대경로, 시트 정보 등)

-. friendly name : 표시이름. 하이퍼링크로 표시 될 명칭이다. 

 

 

  아래 예시를 통해 이해할 수 있다. 네이버 웹페이지를 연결하는 하이퍼링크이다. 웹페이지를 입력시 http:// 를 앞에 붙여야 제대로 연결된다.

 

< 네이버 웹페이지 연결 하이퍼링크 >

 

  위 예시는 웹페이지를 예시로 들었지만 HYPERLINK 함수는 다른 파일(엑셀 뿐만 아니라 다른 확장자 파일도 가능), 특정 시트 접근 등 다양한 방식으로 활용가능하다. 대표적인 예시 3가지만 추가로 정리해보았다.

 

 

예시	설명
=HYPERLINK("#Sheet2!A1","표시")	-. 같은 엑셀 파일의 다른 시트 접근하기 -. 시트명 앞에 #을 붙여줘야 한다.
=HYPERLINK("test.txt",)	-. 같은 경로의 text.txt 파일에 접근하기 -. 다른 경로일경우 ""안에 절대 경로를 써준다.
=HYPERLINK("[t.xlsx]Sheet1!A1")	-. [ ]안의 t.xlsx는 다른 엑셀파일이다.(같은 경로) -. 첫번째 예시와 마찬가지로 다른 경로일경우 절대경로로 표시한다.

 

 

2. 파이썬을 통한 자동화 구현

 

 

1) 모듈 import

 

 

  이번에 사용하는 모듈을 총 2가지이다. (엑셀을 다루는 openpyxl과 디렉토리를 다루는 os 모듈)

 

<코드>

import openpyxl as op
import os

 

 

 

2) sheetInfo 함수 구현

 

 

  엑셀 파일명 및 시트 정보를 튜플로 받아 리스트로 추가하는 함수이다. sheetInfo의 최종 리턴값은 튜플로 구성 된 리스트이다. 이전 pdf 변환 프로그램을 짤 때도 같은 코드 구성을 사용하였다. 설명은 주석으로 대신한다.

(참고링크 : 2021.06.21 - [코딩/업무자동화] - [파이썬/업무자동화] 엑셀 파일 시트별 pdf 저장/변환하기)

 

 

<코드>

def sheetInfo(path : str) -> list:

    excellist = os.listdir(path) #path에 있는 엑셀파일을 리스트로 받아옴
    shtlist = [] #빈리스트 생성
    
    for file in excellist: #excellist를 for문을 통해 반복
        temp_wb = op.load_workbook(path+"/"+file) #엑셀파일 workbook 생성
        temp_shtlist = temp_wb.sheetnames #해당 엑셀파일의 시트를 리스트로 읽어옴
        
        for sht in temp_shtlist: #위 시트 리스트를 for문 통해 반복
            temp_tuple = (file, sht) #파일명과 시트명을 튜플로 생성
            shtlist.append(temp_tuple) #해당 튜플을 리스트에 추가
    
    return shtlist #튜플로 이루어진 리스트를 리턴

 

 

<결과>

 

  위 함수에서 리턴하는 shtlist를 출력해보면 아래와 같은 결과를 확인할 수 있다. 테스트 엑셀 파일은 포스팅 초반에 설명했던 주차별 엑셀파일이다.

 

<튜플로 이루어진 리스트 - (엑셀 파일명, 시트명)을 담고있다.>

 

 

 

3) hyperLink 함수 구현

 

  hyperLink 함수는 2) 함수의 리스트를 입력받아 엑셀에 직접 하이퍼링크를 입력하는 부분이다.

자세한 내용은 주석을 통해 정리했다.

 

 

<코드>

#path는 엑셀 파일이 있는 경로, shtlist는 sheetInfo 함수의 리턴값임
def hyperLink(path, shtlist : list):
    
    wb = op.Workbook() #엑셀 워크북 신규 생성
    ws = wb.active #엑셀 활성화시트 설정(신규 워크북이므로 Sheet1)
    
    i=1 #엑셀파일에 데이터를 쓰기 위한 인덱싱

    for data in shtlist:
        ws.cell(row=i+1, column=1).value = data[0] #튜플 0번째 요소 : 파일명
        ws.cell(row=i+1, column=2).value = data[1] #튜플 1번째 요소 : 시트명

        #엑셀 Hyperlink 함수 구현
        ws.cell(row=i+1, column=3).value = '=HYPERLINK("{}", "{}")'.format("["+path+"/"+data[0]+"]"+data[1]+"!A1","링크")
        i=i+1
    
    ws["A1"].value = "파일명"
    ws["B1"].value = "시트명"
    ws["C1"].value = "하이퍼링크"
    
    #시트 목록 하이퍼링크 파일을 아래 파일명으로 저장
    wb.save("시트목록.xlsx")

 

 

 

4) 실행부 

 

 

<코드>

#실행
path = r"C:\UsersDesktop\VS CODE\Project\hyperlink\Data" #엑셀파일이 있는 경로

#SheetInfo 함수 호출 후 리턴값을 sht_info에 저장
sht_info = sheetInfo(path)

#path와 sht_info를 매개변수로 입력받아 hyperlink 함수 실행
(path,sht_info)

 

 

<결과 엑셀 파일 확인>

5월 1주차.xlsx 파일의 sheet29 열기

 

 

3. 전체 코드

 

 

<코드>

import openpyxl as op
import os

#엑셀파일, 시트 정보를 읽어서 리스트로 리턴
def sheetInfo(path : str) -> list:

    excellist = os.listdir(path)
    shtlist = []
    for file in excellist:
        temp_wb = op.load_workbook(path+"/"+file)
        temp_shtlist = temp_wb.sheetnames
        for sht in temp_shtlist:
            temp_tuple = (file, sht)
            shtlist.append(temp_tuple)
    
    return shtlist

#path는 엑셀 파일이 있는 경로, shtlist는 sheetInfo 함수의 리턴값임
def hyperLink(path, shtlist : list):
    
    wb = op.Workbook() #엑셀 워크북 신규 생성
    ws = wb.active #엑셀 활성화시트 설정(신규 워크북이므로 Sheet1)
    
    i=1 #엑셀파일에 데이터를 쓰기 위한 인덱싱

    for data in shtlist:
        ws.cell(row=i+1, column=1).value = data[0] #튜플 0번째 요소 : 파일명
        ws.cell(row=i+1, column=2).value = data[1] #튜플 1번째 요소 : 시트명

        #엑셀 Hyperlink 함수 구현
        ws.cell(row=i+1, column=3).value = '=HYPERLINK("{}", "{}")'.format("["+path+"/"+data[0]+"]"+data[1]+"!A1","링크")
        i=i+1
    
    ws["A1"].value = "파일명"
    ws["B1"].value = "시트명"
    ws["C1"].value = "하이퍼링크"
    wb.save("시트목록.xlsx")


#실행
path = r"엑셀 파일 경로"
sht_info = sheetInfo(path)
hyperLink(path,sht_info)

 

.py파일을 아래에 첨부한다.

 

hyperLink.zip

0.00MB