파이썬을 사용하여 다양한 소스의 데이터를 Excel로 가져오기

들어가기 전에

데이터를 Excel로 효율적으로 전송하는 것은 많은 전문가에게 필요한 기술입니다. 분석 결과를 저장하거나 정보를 정리해야 하는 경우 Excel 스프레드시트에 데이터를 기록하면 저장, 조작 및 공유가 쉬워집니다. Python Excel 라이브러리를 사용하여 다양한 데이터 유형을 Excel에 작성하고 CSV, XML 및 데이터베이스의 데이터를 Excel로 가져오는 방법을 소개합니다.

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

이미지: 아이엑셀러 닷컴

 

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

---

• 원문: Write Excel Files in Python: Import Data from Various Data Sources to Excel
• URL: https://python.plainenglish.io/write-data-to-excel-with-python-5-examples-019a5970f851

---

Excel 문서 작성을 위한 Python 라이브러리

Python용 Spire.XLS는 개발자가 시스템에 Microsoft Office나 Excel을 설치하지 않고도 Microsoft Excel 파일을 읽고, 쓰고, 수정할 수 있는 고성능 스프레드시트 SDK입니다. Spire.XLS를 사용하면 새 스프레드시트를 만들고, 데이터를 가져오고 내보내고, 셀 서식을 지정하고, 차트를 삽입하는 등의 작업을 수행할 수 있습니다. 이 라이브러리는 다음 명령을 사용하여 PyPI에서 설치할 수 있습니다.

pip install Spire.XLS

 

파이썬에서 Excel에 다양한 데이터 유형 쓰기

Spire.XLS는 각각 통합 문서와 워크시트를 나타내는 Workbook 클래스와 워크시트 클래스를 제공합니다. 사용자는 CellRange 객체를 반환하는 Worksheet.Range[int row, int column] 속성을 활용하여 특정 셀에 액세스할 수 있습니다.

셀에 텍스트, 숫자, 날짜 또는 부울 값을 입력하려면 셀범위 개체 내의 Value, NumberValue, DataTimeValue 및 BooleanValue 속성을 활용할 수 있습니다. 다음 코드는 Python에서 Spire.XLS를 사용하여 Excel에 다양한 데이터 유형을 작성하는 방법을 보여줍니다.

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

# Create a Workbook object
workbook = Workbook()

# Remove default worksheets
workbook.Worksheets.Clear()

# Add a worksheet and name it
worksheet = workbook.Worksheets.Add("WriteToCells")

# Write string to cell
worksheet.Range[1, 1].Value = "Hello, World"

# Write number to cell and format the number
worksheet.Range[2, 1].NumberValue = 12345
worksheet.Range[2, 1].NumberFormat = "#,##0.00"

# Write date to cell and format the date
worksheet.Range[3, 1].DateTimeValue = DateTime.get_Now()
worksheet.Range[3, 1].NumberFormat = "yyyy-mm-dd"

# Write a bool value to cell
worksheet.Range[4, 1].BooleanValue = True

# Align content to center
worksheet.Columns[0].HorizontalAlignment = HorizontalAlignType.Center

# Set column width
worksheet.Columns[0].ColumnWidth = 20

# Save to an Excel file
workbook.SaveToFile("output/WriteDataToCells.xlsx", ExcelVersion.Version2016)

# Dispose resources
workbook.Dispose()

 

이미지: medium

 

파이썬에서 Excel에 목록 쓰기

워크시트에 1차원 목록을 작성하려면 Worksheet.InsertArray() 메서드를 사용할 수 있습니다. 기본적으로 목록의 목록인 2차원 목록의 경우, 각 하위 목록(행이라고도 함)을 반복하고 Worksheet.InsertArray()를 사용하여 각 행을 워크시트에 기록해야 합니다. 다음 코드 스니펫은 Python을 사용하여 Excel에 목록을 작성하는 방법을 보여줍니다.

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

# Create a Workbook object
workbook = Workbook()

# Remove default worksheets
workbook.Worksheets.Clear()

# Add a worksheet and name it
worksheet = workbook.Worksheets.Add("InsertLists")

#Create a one-dimensional list
oneDimensionalList = ["January", "February", "March", "April", "May", "June", "July", "August"]

# Write the list to the first row of the worksheet
worksheet.InsertArray(oneDimensionalList, 1, 1, True)

# Create a two-dimensional list
twoDimensionalList= [["Name", "Age", "Sex", "Dept.", "Tel."],
                     ["John", "25", "Male", "Development","654214"],
                     ["Albert", "24", "Male", "Support","624847"],
                     ["Amy", "26", "Female", "Sales","624758"]]

# Write the list to the worksheet starting from the cell C3
i = 3
for list in twoDimensionalList:
    worksheet.InsertArray(list, i, 3, False)
    i = i + 1

# Set column width
worksheet.AllocatedRange.ColumnWidth = 12

# Set font style
worksheet.Range["A1:A8"].Style.Font.IsBold = True
worksheet.Range["C3:G3"].Style.Font.IsBold = True

# Save to an Excel file
workbook.SaveToFile("output/InsertLists.xlsx", ExcelVersion.Version2016)

# Dispose resources
workbook.Dispose()

 

이미지: medium

 

파이썬에서 CSV에서 Excel로 데이터 가져오기

CSV 데이터를 Excel 테이블에 쓰려면 Workbook.LoadFromFile()을 사용하여 CSV 파일을 로드하고 Workbook.SaveToFile()메서드를 사용하여 XLSX 형식으로 저장할 수 있습니다. CSV 파일을 로드할 때 CSV 테이블에 사용되는 구분 기호를 지정할 수 있는 옵션이 있습니다. 구분 기호는 쉼표, 탭, 세미콜론 등이 될 수 있습니다. 다음 코드는 Python을 사용하여 CSV 파일에서 Excel 문서로 데이터를 가져오는 방법을 보여줍니다.

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

# Create a Workbook object
workbook = Workbook()

# Load a CSV file
workbook.LoadFromFile("C:\\Users\\Administrator\\Desktop\\username.csv", ";", 1, 1)

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

# Ignore all error checking options, such as "number stored as text" 
sheet.AllocatedRange.IgnoreErrorOptions = IgnoreErrorType.All

# Autofit column width
sheet.AllocatedRange.AutoFitColumns()

# Save to an Excel file
workbook.SaveToFile("output/CsvToExcel.xlsx", ExcelVersion.Version2016)

# Dispose resources
workbook.Dispose()

 

이미지: medium

파이썬에서 XML에서 Excel로 데이터 가져오기

Excel 워크시트에 XML 데이터를 쓰려면 먼저 XML 데이터를 읽은 다음 표 헤더를 워크시트에 쓴 다음 해당 데이터를 셀에 써야 합니다. XML 테이블 데이터의 구조에 따라 쓰기 코드를 조정해야 합니다. 다음 코드는 Python의 Spire.XLS 라이브러리를 사용하여 XML 파일에서 데이터를 가져와서 Excel 파일에 쓰는 방법을 보여줍니다.

from spire.xls import *
from spire.xls.common import *
import xml.etree.ElementTree as ET

# Create a Workbook object
workbook = Workbook()

# Remove default worksheets
workbook.Worksheets.Clear()

# Add a worksheet and name it
worksheet = workbook.Worksheets.Add("Students")

# Load an XML file
xml_tree = ET.parse("C:\\Users\\Administrator\\Desktop\\Students.xml")

# Get the root element of the XML tree
xml_root = xml_tree.getroot()

# Get the first the "student" element
first_student = xml_root.find("student")

# Extract header information and convert it into a list
header = list(first_student.iter())[1:]  

# Write header to Excel
for col_index, header_node in enumerate(header, start=1):
    header_text = header_node.tag
    worksheet.SetValue(1, col_index, header_text)

# Write other data to Excel by iterating over each student element and each data node within it
row_index = 2
for student in xml_root.iter("student"):
    for col_index, data_node in enumerate(list(student.iter())[1:], start=1):  
        value = data_node.text
        header_text = list(header[col_index - 1].iter())[0].tag
        worksheet.SetValue(row_index, col_index, value)
    row_index += 1

# Set column width
worksheet.AllocatedRange.ColumnWidth = 10

# Set alignment
worksheet.AllocatedRange.HorizontalAlignment = HorizontalAlignType.Left

# Set font style
worksheet.Range["A1:H1"].Style.Font.IsBold = True

# Save the document
workbook.SaveToFile("output/WriteXmlToExcel.xlsx")

# Dispose resources
workbook.Dispose()

 

XML data:

<?xml version="1.0" encoding="UTF-8"?>
<students>
  <student>
    <id>1</id>
    <name>John Doe</name>
    <major>Data Science</major>
    <gpa>3.8</gpa>
    <age>20</age>
    <gender>Male</gender>
    <email>john.doe@example.com</email>
    <phone>542-495-0254</phone>
  </student>
  <student>
    <id>2</id>
    <name>Jane Smith</name>
    <major>Computer Science</major>
    <gpa>3.9</gpa>
    <age>21</age>
    <gender>Female</gender>
    <email>jane.smith@example.com</email>
    <phone>248-420-7140</phone>
  </student>
  <student>
    <id>3</id>
    <name>Michael Lee</name>
    <major>Statistics</major>
    <gpa>3.6</gpa>
    <age>22</age>
    <gender>Female</gender>
    <email>michael.lee@example.com</email>
    <phone>124-840-0025</phone>
  </student>
  <student>
    <id>4</id>
    <name>Patrick James</name>
    <major>Computer Engineer</major>
    <gpa>3.9</gpa>
    <age>23</age>
    <gender>Male</gender>
    <email>patrick.james@example.com</email>
    <phone>885-021-0066</phone>
  </student>
</students>

 

Excel 출력 파일:

 

이미지: medium

 

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

데이터베이스에서 Excel 파일로 데이터를 가져오려면 일반적인 프로세스는 다음과 같습니다.

• 데이터베이스에 대한 연결을 설정합니다.
• 열 이름과 데이터 행을 검색합니다.
• 열 이름과 데이터 행을 Excel 워크시트에 씁니다.
• Excel 파일을 포맷하고 저장합니다.

 

다음 코드는 Python의 Spire.XLS를 사용하여 MySQL 테이블에서 Excel 문서로 데이터를 가져오는 방법을 보여줍니다.

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

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

# Create a cursor object
cursor = connection.cursor()

# Retrieve column names
cursor.execute("SHOW COLUMNS FROM excel_table")
column_names = [column[0] for column in cursor.fetchall()]
print(column_names)

# Retrieve data
cursor.execute("SELECT * FROM excel_table")
data = cursor.fetchall()

# Create a Workbook object
workbook = Workbook()

# Remove default worksheets
workbook.Worksheets.Clear()

# Add a worksheet and name it
worksheet = workbook.Worksheets.Add("FromDatabase")

# Write column names to Excel worksheet
for col_index, column_name in enumerate(column_names, start=1):
    worksheet.SetValue(1, col_index, column_name)

# Write data to Excel worksheet
for row_index, row in enumerate(data, start=2):
    for col_index, value in enumerate(row, start=1):
        worksheet.SetValue(row_index, col_index, str(value))

# Set column width
worksheet.AllocatedRange.ColumnWidth = 10

# Set alignment
worksheet.AllocatedRange.HorizontalAlignment = HorizontalAlignType.Left

# Set font style
worksheet.Rows[0].Style.Font.IsBold = True

# Save the workbook to Excel file
workbook.SaveToFile("output/DatabaseToExcel.xlsx")

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

 

이미지: medium

 

마치며

파이썬을 사용하여 Excel로 데이터를 가져오는 다양한 방법을 살펴보았습니다. 다양한 데이터 유형, 목록 작성, CSV, XML 및 데이터베이스에서 데이터 가져오기 등을 다루었습니다. 이러한 기술을 통해 보고 작업을 자동화하고, 데이터를 통합하고, 데이터 접근성을 개선할 수 있습니다.

멤버십 안내 "최고" 가성비로 "최신" Office 활용 정보를 보내드립니다.

추천 도서 심혈을 기울여 집필한 책들입니다. 자신있게 추천합니다.

강의 & 개발 최고 전문가의 실무 노하우를 아낌 없이 전수해 드립니다.