# 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