python
# coding: utf-8
from xlrd import open_workbook, XL_CELL_TEXT , cellname
import re
from datetime import datetime
wtdlist = ('Month', 'fire', 'water' , 'wood', 'Money', 'soil', 'Day' )
wb = open_workbook('test.xls')
outfile = open('bscs_rate', 'w', 1000 )
for s in wb.sheets():
print 'Sheet:',s .name
channel = s .cell(0 ,0 ).value
# date -> 2014-02-01
ymd = s .cell(0 ,26 ).value
s_date, others = ymd.strip().split( '(')
tdatetime = datetime.strptime(s_date, '%Y year%m month%d day')
# get week day
wtd = wtdlist[tdatetime.weekday()]
# get tuned date
ymd = tdatetime.strftime( '%Y-%m-%d')
for row in xrange(4,s .nrows):
if s.cell(row,1).value==u'': continue
start_time = ''
minute = ''
region = ''
title = ''
rate = ''
values = []
start_time = unicode( int( s.cell(row,1).value) )
start_time = start_time if len(start_time)== 4 else '0'+start_time
start_time = start_time[: 2]+ ':'+start_time[2 :]
minute = unicode( int( s.cell(row,2).value) )
title = s.cell(row,3).value
for col in xrange( 6, 9):
region = s.cell(2,col).value
region = re.sub(ur '\n','' ,region).strip()
region = u 'Kansai' if region==u'Kinki' else region
region = u 'Nagoya' if region==u'Chubu' else region
rate = unicode( s.cell(row,col).value)
values = [region, ymd, wtd, title, channel, start_time, minute, rate]
str = u','.join(values)
print str
outfile.write(str+ '\n')
Recommended Posts