Manipulate excel files from python with xlrd (personal notes)

Reference URL

Reference code

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

Manipulate excel files from python with xlrd (personal notes)
Handle Excel CSV files with Python
WEB scraping with Python (for personal notes)
Decrypt files encrypted with openssl from python with openssl
[Easy Python] Reading Excel files with openpyxl
[Easy Python] Reading Excel files with pandas
python personal notes
Excel with Python
missingintegers python personal notes
Run python from excel
Handle Excel with python
[Personal notes] Python, Django
Manipulate riak from python
Operate Excel with Python (1)
Operate Excel with Python (2)
Remove headings from multiple format CSV files with python
Operate Excel with Python openpyxl
Let's run Excel with Python
Sorting image files with Python (2)
Sort huge files with python
Sorting image files with Python (3)
Sorting image files with Python
Manipulate various databases with Python
Integrate PDF files with Python
Reading .txt files with Python
With skype, notify with skype from python!
Improve your productivity by processing huge Excel files with Python
Notes on importing data from MySQL or CSV with Python
Introduction to Python for VBA users-Calling Python from Excel with xlwings-
solver> Link> Solve Excel Solver with python
Call C from Python with DragonFFI
Recursively unzip zip files with python
Using Rstan from Python with PypeR
Manipulating EAGLE .brd files with Python
Create folders from '01' to '12' with python
Notes on using MeCab from Python
Manipulate files and folders in Python
[Python] POST wav files with requests [POST]
Laplacian eigenmaps with Scikit-learn (personal notes)
Read and use Python files from Python
Create an Excel file with Python3
Decrypt files encrypted with OpenSSL with Python 3
Personal notes for python image processing
Let's play with Excel with Python [Beginner]
Python Pandas Data Preprocessing Personal Notes
Run Aprili from Python with Orange
Read files in parallel with Python
Notes on using rstrip with python.
Notes on accessing dashDB from python
Call python from nim with Nimpy
(Personal notes) Python metaclasses and metaprogramming
Read fbx from python with cinema4d
Excel aggregation with Python pandas Part 1
Extract strings from files in Python
Collecting information from Twitter with Python (Twitter API)
[AWS] Using ini files with Lambda [Python]
Receive textual data from mysql with python
Get html from element with Python selenium
[Note] Get data from PostgreSQL with Python
Create wordcloud from your tweet with python3
[Automation] Manipulate mouse and keyboard with Python