2017/05/25
此需求是以python和openpyxl,ftplib為主，從unix主機下載指定的csv檔，並且自動的將csv轉成xlsx檔案
而其中第一row為中文標題,而第三column為日期yy/mm/dd形式,我們要將日期格式轉成yy/mm/dd的文字格式
#-------------------------------------------
# -*- coding: big5 -*-
from ftplib import FTP, all_errors
from openpyxl import Workbook
from openpyxl.styles import Font, Fill
import datetime
import wx
import os
import re
import csv
import sys
def getFile(filename):
try:
ftp.retrbinary("RETR " + filename ,open(filename, 'wb').write)
except:
print('檔案下載失敗!')
print "Error"
try:
ftp = FTP("XXX.XXX.XX.XX")
ftp.login("XX","XXX")
print('使用者連線成功')
except AttributeError:
print('連線失敗(主機或帳號或密碼錯誤)')
ftp = None
except all_errors, err:
print(str(err))
ftp = None
# download the file
local_Dir = "c:\\tmp"
host_Dir = r"/BACK2/f/"
print "Changing local directory to " + local_Dir
os.chdir(local_Dir)
print 'Changing remote directory to ' + host_Dir
ftp.cwd(host_Dir)
ls = ftp.nlst('*-*.csv')
count = len(ls)
curr = 0
print "found {} files".format(count)
for filename in ls:
curr += 1
print "get file {} to {}".format(filename,local_Dir)
getFile(filename) #從unix 的指定目錄下下載指定的檔案
ftp.quit()
ftp = None
print('ftp離線') # ftp 完作成且離線
#-------- open csv and transfer to excel automatically--------
for fnList in ls: #依次開啓所有的csv檔案
fileName, file_extension = os.path.splitext(fnList)
saveFn = fileName + '.xlsx' #分開檔名及檔案 extension
print saveFn #印出每個檔案l值,視情況可以省略
wb = Workbook()
ws = wb.active
with open(fnList, 'r') as f:
reader = csv.reader(f)
for r, row in enumerate(reader): #設定row 迴圈
for c, col in enumerate(row): #設定column 迴圈
for idx, val in enumerate(col.split(',')):
cell = ws.cell(row=r+1, column=c+1)
cell.font = Font(size=12) #設定字體大小
if (c == 3 and r > 0): #特殊需求處理,要將日期格式轉成文字格式
val = "{:10}".format(val) #特殊需求處,只有第3column及row2以後要處理格式
cell.value = val #將值填入cell內
cell.number_format = 'yy/mm/dd' #文字格式
else:
cell.value = val.decode('big5')
print cell.value #印出每個cell值,視情況可以省略
wb.save(saveFn)
留言列表
