업무의 잔머리

파이썬으로 엑셀을 다루는 3가지 방법

https://www.youtube.com/watch?v=fJmN8qPAFAU&t=312s 

pandas_worksmart.py--------------------

#pip install pandas
#pip install xlrd
#pip install pywin32
#pip install openpyxl

FName = "test.xlsx"

# Pandas
import pandas as pd

df = pd.read_excel(FName, sheet_name=['4대급여'])
# print(df)
# print(type(df))
print(df['4대급여'])

====================

openpyxl_worksmart.py--------------------

#pip install pandas
#pip install xlrd
#pip install pywin32
#pip install openpyxl

FName = "test.xlsx"

# openpyxl
import openpyxl
import pandas as pd

wb = openpyxl.load_workbook(FName)
# ws_name = wb.sheetnames
# print(ws_name)
# print(type(ws_name))
# ws = wb.active
ws = wb['4대급여']
Company = []
Name = []
Salary = []
for cell in ws.rows:
    Company.append(cell[0].value)
    Name.append(cell[1].value)
    Salary.append(cell[2].value)
print(Company,Name,Salary)

# data = ws.values
# # 맨 처음의 행을 헤더로써 취득한다. 
# columns = next(data)[0:]
# # 출처: https://engineer-mole.tistory.com/211 [매일 꾸준히, 더 깊이:티스토리]
# # 이후의 데이터부터 DataFrame을 작성
# df = pd.DataFrame(data, columns=columns)
# # print(type(df))
# print(df)

====================

win32com_worksmart.py--------------------

#pip install pandas
#pip install xlrd
#pip install pywin32
#pip install openpyxl

# pywin32
import win32com.client
import os

# FName = r"D:\python\vscode\excel\test.xlsx"
current_path = os.getcwd().replace('\\','/') + '/'
filename = "test.xlsx"
FName = current_path + filename
# print(FName)
#Excel 프로그램 객체 생성
excel=win32com.client.Dispatch("Excel.Application")
#엑셀 실행과정이 보이게 설정
# excel.Visible = True
wb = excel.Workbooks.Open(FName)
ws = wb.Worksheets('4대급여')

Company = []
Name = []
Salary = []

# print(ws.UsedRange.Rows.Count+1)
for i in range(2,ws.UsedRange.Rows.Count+1):
    Company.append(ws.Range("A"+str(i)).value)
    Name.append(ws.Range("B"+str(i)).value)
    Salary.append(ws.Range("C"+str(i)).value)

print(Company, Name, Salary)

wb.Close(False)
excel.Quit()

====================

참고자료

인생은 짧아요 엑셀 대신 파이썬 - 이승준

https://www.youtube.com/watch?v=w7Q_eKN5r-I 

============================================================================================