#!/usr/bin/env python

import os
import getopt
import sys
import xlrd
import csv

def xls_to_csv(fin, fout):
    print "convert xls to csv"
    if not os.path.exists(fin):
        print "WARN: %s does not exist", fin

    x =  xlrd.open_workbook(fin)
    x1 = x.sheet_by_index(1)

    try:
        csvfile = open(fout, "wb")
    except IOError:
        print "WARN: Unable to open %s", fout
        sys.exit(2)

    writecsv = csv.writer(csvfile, delimiter=' ')

    for rownum in xrange(x1.nrows):
        writecsv.writerow(x1.row_values(rownum))

    csvfile.close()

def float_to_int(fin, fout):
    print "convert float to Int"

    if not os.path.exists(fin):
        print "WARN: %s does not exist", fin

    try:
        csvfile = open(fin, "r")
    except IOError:
        print "WARN: Unable to open %s", fin
        sys.exit(2)

    try:
        txtfile = open(fout, "w+")
    except IOError:
        print "WARN: Unable to open %s", fout
        sys.exit(2)


    readcsv = csv.reader(csvfile, delimiter=' ', quotechar='|')
    for row in readcsv:
        try:
            myline = str(int(float(row[0]) * 1000000)) + ' ' + str(int(float(row[1]) * 1000000)) + ' ' + str(int(float(row[2]) * 1000000)) + ' ' + str(int(float(row[3]) * 1000000)) + '\n'
        except ValueError:
            myline = row[0] + ' ' +  row[1] + ' ' + row[2] + ' ' + row[3] + '\n'

        txtfile.write(myline)

    csvfile.close()
    txtfile.close()


def remove_spurious_line(fin):
    print "Remove spurious heading lines"

    if not os.path.exists(fin):
        print "WARN: %s does not exist", fin

    try:
        txtfile = open(fin, "r")
    except IOError:
        print "WARN: Unable to open %s", fin
        sys.exit(2)

    lines = txtfile.readlines()

    txtfile.close()

    try:
        txtfile = open(fin, "w+")
    except IOError:
        print "WARN: Unable to open %s", fin
        sys.exit(2)
    
    filt = True
    prevline = ""

    for myline in lines:
        if ((filt == True) and (myline[0].isdigit())) :
            filt = False
            txtfile.write(prevline)
            continue
            
        if (filt == True):
            prevline = myline
        else:
            txtfile.write(myline)

    txtfile.close()


if __name__ == '__main__':
    try:
        opts, args = getopt.getopt(sys.argv[1:], "o:avdD:s:")
    except getopt.GetoptError as err:
        print str(err) # will print something like "option -a not recognized"
        sys.exit(2)

    for o, a in opts:
        if o == "-o":
            outfile = a
        if o == "-v":
            verbose = True
        if o == "-d":
            dry_run = True

    for f in args:
        ftmp = 'unikid.csv'
        fout = f+".txt"
        xls_to_csv(f, ftmp)
        float_to_int(ftmp, fout)
        remove_spurious_line(fout)