파이썬을 사용하여 Excel 데이터를 추출하는 6가지 코드

Python

파이썬을 사용하여 Excel 데이터를 추출하는 6가지 코드

권현욱(엑셀러) 2024. 7. 7. 19:04

들어가기 전에

프로그래밍 방식으로 Excel에서 데이터를 추출하는 것은 데이터 검색의 자동화와 효율성을 가능하게 하는 귀중한 접근 방식입니다. 파이썬에는 Excel의 데이터에 접근하고 추출할 수 있는 다양한 라이브러리가 있습니다. 엑셀에서 데이터를 추출하는 6가지 방법을 소개합니다.

권현욱(엑셀러) | 아이엑셀러 닷컴 대표 · Microsoft Excel MVP · Excel 솔루션 프로바이더 · 작가

※ 이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.

원문: Extracting Data from Excel with Python: 6 Code Examples
URL: https://python.plainenglish.io/extracting-data-from-excel-with-python-6-code-examples-e2e5b323a869

Excel에서 데이터를 추출하는 파이썬 라이브러리

이 솔루션은 Python 라이브러리용 Spire.XLS를 사용하여 Excel 문서를 읽고 조작(manipulate)합니다. 광범위한 API 세트를 통해 프로그래머는 Excel 파일 작업을 위한 광범위한 기능에 액세스할 수 있습니다. 다음은 Excel에서 데이터를 추출하는 데 특히 유용한 클래스 및 속성 목록입니다.

Workbook: Workbook 모델을 나타냅니다.
워크시트: 워크북의 워크시트를 나타냅니다.
CellRange: 특정 셀 또는 셀의 범위를 나타냅니다.
Worksheet.AllocationRange: 데이터가 포함된 셀의 범위를 나타냅니다.
CellRange.Value: 셀의 텍스트 값이나 숫자 값을 반환합니다.
CellRage.HasFormula: 셀에 수식이 포함되어 있는지 여부를 결정합니다.
CellRange.FormulaValue: 셀 내부 수식의 결과를 반환합니다.

다음 pip 명령을 사용하여 PyPI 에서 라이브러리를 설치할 수 있습니다

pip install Spire.XLS

파이썬에서 특정 셀에서 데이터 추출하기

특정 셀의 텍스트 값이나 숫자 값을 검색하려면 CellRange 객체의 Value 속성을 사용할 수 있습니다. 다음 코드 예제는 Python을 사용하여 Excel 문서를 로드하고 특정 셀에서 데이터를 추출하는 방법을 보여줍니다.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
wb = Workbook()

# Load an Excel file
wb.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx");

# Get a specific worksheet
sheet = wb.Worksheets[0]

# Get a specific cell
specificCell = sheet.Range["A1"]

# Get the value of the cell
print("A1 has the value: " + specificCell.Value)

# Dispose resources
wb.Dispose()

파이썬에서 셀 범위에서 데이터 추출하기

셀 범위는 Worksheet.Range[startRow: int, stratColumn: int, endRow: int, endColumn: int] 속성을 통해 얻을 수 있습니다. 그런 다음 범위 내의 각 셀을 반복하여 셀 데이터를 순차적으로 검색할 수 있습니다.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
wb = Workbook()

# Load an existing Excel file
wb.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx")

# Get a specific worksheet
sheet = wb.Worksheets[0]

# Get a cell range
startingRow = 2
startingColumn = 1
endingRow = 4
endColumn = 5
cellRange = sheet.Range[startingRow, startingColumn, endingRow, endColumn]

# Iterate through the rows
for i in range(len(cellRange.Rows)):

    # Iterate through the columns
    for j in range(len(cellRange.Rows[i].Columns)):

        # Get data of a specific cell
        print(cellRange[startingRow + i, startingColumn  + j].Value + "  ", end ='')
        
    print("")

# Dispose resources
wb.Dispose()

파이썬에서 전체 워크시트에서 데이터 추출하기

전체 워크시트에서 데이터를 가져온다고 해서 워크시트의 모든 셀을 순회해야 하는 것은 아닙니다. 대신 Worksheet.AllocatedRange를 통해 데이터가 포함된 범위를 가져온 다음 해당 범위 내의 셀을 반복하여 데이터를 개별적으로 추출할 수 있습니다.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
wb = Workbook()

# Load an existing Excel file
wb.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx");

# Get the first worksheet
sheet = wb.Worksheets[0]

# Get the cell range containing data
locatedRange = sheet.AllocatedRange

# Iterate through the rows
for i in range(len(locatedRange.Rows)):

    # Iterate through the columns
    for j in range(len(locatedRange.Rows[i].Columns)):

        # Get data of a specific cell
        print(locatedRange[i + 1, j + 1].Value + "  ", end ='')
        
    print("")

# Dispose resources
wb.Dispose()

파이썬에서 워크시트에서 수식이 아닌 수식 결과 추출하기

Excel 표의 수식은 흔히 볼 수 있습니다. 데이터를 추출할 때 Value 속성이 직접 반환하는 수식 자체보다는 수식의 결과를 얻는 것이 더 중요한 경우가 많습니다. 이 문제를 해결하기 위해 셀에 수식이 포함되어 있는지 확인하기 위해 CellRange.HasFormula 속성을 활용할 수 있습니다. 셀에 수식이 포함되어 있으면 CellRange.FormulaValue 속성을 사용하여 수식의 결과를 검색할 수 있습니다.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
wb = Workbook()

# Load an existing Excel file
wb.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx");

# Get the first worksheet
sheet = wb.Worksheets[0]

# Get the cell range containing data
locatedRange = sheet.AllocatedRange

# Iterate through the rows
for i in range(len(locatedRange.Rows)):

    # Iterate through the columns
    for j in range(len(locatedRange.Rows[i].Columns)):

        # Get a specific cell
        certainCell = locatedRange[i + 1, j + 1]

        # Determine if the cell has formula
        if(certainCell.HasFormula):

            # Get the formula result of the cell
            print(str(certainCell.FormulaValue) + "  ", end ='')
        else:

            # Get data of a specific cell
            print(certainCell.Value + "  ", end ='')
        
    print("")

# Dispose resources
wb.Dispose()

파이썬에서 워크시트를 CSV 파일로 내보내기

Excel 파일을 CSV 형식으로 변환하는 것은 단순성, 호환성 및 데이터 이식성 때문에 데이터를 추출하는 데 널리 사용됩니다. CSV 파일은 간단한 구조의 일반 텍스트 파일로, 다양한 애플리케이션 및 플랫폼과 호환됩니다. Worksheet.SaveToFile() 메서드를 사용하면 워크시트를 CSV 파일로 쉽게 변환할 수 있습니다.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
wb = Workbook()

# Load an Excel document
wb.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx")

# Get a specific sheet
sheet = wb.Worksheets[0]

# Convert it to CSV file
sheet.SaveToFile("ToCSV.csv", ",", Encoding.get_UTF8())

# Dispose resources
wb.Dispose()

파이썬에서 Excel에서 MySQL 서버 데이터베이스로 데이터 가져오기

Excel 데이터를 데이터베이스로 가져오면 SQL 또는 기타 분석 도구를 사용하여 복잡한 쿼리를 수행하고, 보고서를 생성하고, 데이터에서 인사이트를 추출할 수 있습니다. 또한 비즈니스 인텔리전스 시스템, API 및 기타 데이터 기반 애플리케이션과 쉽게 통합할 수 있습니다. 다음 코드는 워크시트에서 데이터를 읽고 MySQL 서버 테이블에 삽입하는 방법을 보여줍니다.

from spire.xls import *
from spire.xls.common import *
import mysql.connector

# Create a Workbook object
wb = Workbook()

# Load an Excel document
wb.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx")

# Get a specific sheet
sheet = wb.Worksheets[0]

# Get the cell range containing data
locatedRange = sheet.AllocatedRange

# Establish a connection to the MySQL database
connection = mysql.connector.connect(
    host = "localhost",
    user = "root",
    password = "admin",
    database = "exceldata"
)
cursor = connection.cursor()

# Retrieve the data from the Excel worksheet
for i in range(len(locatedRange.Rows)):
    values = []
    if( i > 0):
        for j in range(len(locatedRange.Columns)): 
            values.append(locatedRange[i + 1 , j + 1].Value)
    
        # Construct the SQL INSERT statement
        sql = "INSERT INTO excel_table(office_cost, jan, feb, mar, apr, may, jun, half_year) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)" 

        # Specify values to insert
        insert_values = (values[0], values[1], values[2], values[3], values[4], values[5], values[6], values[7])

        # Execute the INSERT statement for the list of values
        cursor.execute(sql, insert_values)

# Commit the changes
connection.commit()

# Close the database connection
cursor.close()
connection.close()

마치며

Excel에서 데이터를 추출하는 다양한 방법을 살펴보았습니다. 수식이 포함된 워크시트를 다룰 때 수식 자체 대신 수식 결과를 얻는 기법도 살펴보았습니다. 또한 Excel 파일을 CSV 형식으로 변환하고 Excel에서 데이터베이스로 데이터를 가져오는 프로세스에 대해서도 자세히 살펴봤습니다. 이를 통해 다른 애플리케이션, 시스템, 도구와 원활하게 통합할 수 있어 데이터 활용의 가능성을 넓힐 수 있습니다.

Excel과 VBA의 모든 것 아이엑셀러 닷컴 · 강사들이 숨겨 놓고 보는 엑셀러TV

'Python' 카테고리의 다른 글

파이썬으로 PDF 파일을 자르는 방법 (0)	2024.07.14
파이썬 코드 수준을 높이는 7가지 간단한 방법 (0)	2024.07.13
파이썬으로 Excel 파일을 읽으시나요? 여기 빠른 방법이 있습니다 (0)	2024.07.06
Taipy로 Excel 시트를 대화형 대시보드 웹 앱으로 변환하는 방법 (0)	2024.06.29
파이썬을 사용하여 PDF를 HTML로 변환하기 (0)	2024.06.22

현재글파이썬을 사용하여 Excel 데이터를 추출하는 6가지 코드

권현욱의 엑셀 & IT정보