python 폴더내 txt파일 합쳐서 xlsx로 저장하기

import os

import glob

import pandas as pd

import numpy as np

 

# location = os.getcwd()

# path_hometax = r'b:\Python\hometax\data'

 

# def removeAllFile(filePath):

#     if os.path.exists(filePath):

#         for file in os.scandir(filePath):

#             os.remove(file.path)

#         return 'Remove All File'

#     else:

#         return 'Directory Not Found'

# print(removeAllFile('c:/temptest'))

# print(removeFile('b:/python/hometax/data/hometax_pandas_xlsxWriter.xlsx'))

if os.path.exists('b:/python/hometax/data/hometax_pandas_xlsxWriter.xlsx'):

    os.remove('b:/python/hometax/data/hometax_pandas_xlsxWriter.xlsx')

 

# path = os.path.dirname(r'b:\\Python\\hometax\data\\')

path = os.path.dirname('b:/Python/hometax/data/')

os.chdir(path)

file_list=os.listdir(path)

cov_file_list=[]

for file in file_list:

    cov_file_list.append(pd.read_csv(file, sep = "\t",skiprows=4 , engine='python', encoding = "UTF-8"))

    # cov_file_list.append(pd.read_csv(file, sep = "\t",skiprows=4 , engine='python', encoding = "utf-8"))

df_before=pd.concat(cov_file_list)

 

items=["합계금액","공급가액","세액"]

 

for item in items:

 

    df_before[item] = df_before[item].replace('[\$,]', '', regex=True)

 

    df_before[item] = pd.to_numeric(df_before[item])

 

# http://hleecaster.com/pandas-data-cleaning-2/

 

df=df_before.iloc[:,:17]

 

# http://blog.naver.com/PostView.nhn?blogId=rising_n_falling&logNo=221622971970&parentCategoryNo=12&categoryNo=15&viewDate=&isShowPopularPosts=false&from=postView

 

 

 

## XlsxWriter 엔진으로 Pandas writer 객체 만들기

 

# writer = pd.ExcelWriter('./data/hometax_pandas_xlsxWriter.xlsx', engine='xlsxwriter')

writer = pd.ExcelWriter('b:/python/hometax/data/hometax_pandas_xlsxWriter.xlsx', engine='xlsxwriter')

 

 

## DataFrame을 xlsx에 쓰기

 

df.to_excel(writer, sheet_name='Sheet1',index=False)

 

 

 

## Pandas writer 객체에서 xlsxwriter 객체 가져오기

 

workbook = writer.book

 

worksheet = writer.sheets['Sheet1']

 

 

 

## 포맷 만들기

 

# format1 = workbook.add_format({'num_format': '0%'})

 

formater = workbook.add_format({'border':1})

 

# worksheet.set_column('A:Q',None,formater)

 

format2 = workbook.add_format({'num_format': '#,##0','border':1})

 

 

 

## 컬럼에 포맷 적용

 

# worksheet.set_column('A:N',None, formater)

 

worksheet.set_column('A:N',None, formater)

 

# worksheet.set_column('O:Q', 20, format2)

 

worksheet.set_column('O:Q',None, format2)

 

 

 

# 출처: https://korbillgates.tistory.com/152 [생물정보학자의 블로그]

 

 

 

## Pandas writer 객체 닫기

 

writer.close()

 

 

 

# vs code 실행 단축키 Ctrl + F5

import win32api

import win32com.client

import pythoncom

 

result = win32api.MessageBox(0,"통합파일 생성 완료!", "파일 생성")

 

# if result == 1:

#     print('Ok')

# elif result == 2:

#     print('cancel' )