Working with Excel File

xls_functions.py

import os
import sys
import pyExcelerator
from pyExcelerator import *
import xlrd
from database import PGDatabase

mydb = PGDatabase()
#mydb = mydbC.connect()


import datetime

default_date_format = "%Y/%m/%d"
default_time_format = '%H:%M:%S'
def touple_to_list(listoftuples):
    values_seen = {}
    result = []
    for atuple in listoftuples:
        result.append(atuple)
    return result

def get_header_row(xls_file,number_of_columns=0,sheet_no=0):
    workbook = pyExcelerator.parse_xls(xls_file)
    try:
        workbook = pyExcelerator.parse_xls(xls_file)
    except:
        sys.exit('cant load %s '%xls_file)

    worksheet = workbook[sheet_no]
    row_value= worksheet[1]
    total_row = max([element[0] for element in row_value.keys()])+1
    i = 0
    data = []
    for row_idx, col_idx in sorted(row_value.keys()):
        if i < 1:
            row = []
            for col in range(number_of_columns):
               try:
                  value=row_value[(i,col)]
                  row.append(value)
               except:
                  row.append('')
            i+=1
            data.append(row)
    return data

def get_all_data_row(xls_file,number_of_columns,sheet_no=0,):
    workbook = pyExcelerator.parse_xls(xls_file)
    try:
        workbook = pyExcelerator.parse_xls(xls_file)
    except:
        sys.exit('cant load %s '%xls_file)

    worksheet = workbook[sheet_no]
    row_value= worksheet[1]
    total_row = max([element[0] for element in row_value.keys()])+1
    i = 1
    data = []
    for row_idx, col_idx in sorted(row_value.keys()):
        if i < total_row:
            row = []
            for col in range(number_of_columns):
               try:
                  value=row_value[(i,col)]
                  row.append(value)
               except:
                  row.append('')
            i+=1
            data.append(row)
    return data

def get_all_object_names(alldata):
    allobjects = {}
    for data in alldata:
        if data[0]!="":
            if data[0] not in allobjects.keys():
                allobjects[data[0]]=data[0]
    return allobjects

type_of_field = {
                 1:' varchar(255) ',
                 2: ' varchar(100) ',
                 3: ' datetime ',
                 4: ' time '
                 }
def get_all_data_xlrd(xls_file,number_of_columns=0,sheet_no=0):

    table_structure = []
    book = xlrd.open_workbook(xls_file,formatting_info=True)

    s = book.sheet_by_index(sheet_no)
    result = []
    row_val = []

    for i in range(s.nrows):
        row_val = []
        if i>0:
          for j in range(s.ncols):
            #if j < number_of_columns:
              #try:
                  if s.cell_type(i,j) == 3:
                      val = s.cell_value(i,j)

                      val = xlrd.xldate_as_tuple(val,0)
                      val=touple_to_list(val)
                      if val[0]==0 and val[1]==0 and val[2]==0:
                          val = datetime.time(val[3],val[4],val[5]).strftime(default_time_format)
                      else:
                          val = datetime.datetime(val[0],val[1],val[2]).strftime(default_date_format)
                      #print val
                  else:
                      val = s.cell_value(i,j)

              #except:
                  #val = ""
                  row_val.append(val)
          result.append(row_val)

    return result

def get_table_structure_from_xls(xls_file,number_of_columns,sheet_no=0):

    table_structure = []
    book = xlrd.open_workbook(xls_file,formatting_info=True)

    s = book.sheet_by_index(sheet_no)
    row_val = []
    for j in range(s.ncols):
        if j < number_of_columns:
              table_structure_row = []
              table_structure_row.append(str(s.cell_value(0,j)))
              cell_type_no = s.cell_type(1,j)
              if cell_type_no == 3:
                  if s.cell_value(1,j)<1:
                      cell_type_no = 4
              if str(s.cell_value(0,j)) in ['SPECIFACTIONOFSAE']:
                  cell_type_no = 100
              table_structure_row.append(type_of_field.get(cell_type_no,' TEXT '))
              table_structure.append(table_structure_row)
              #print s.cell_value(1,j)

    return table_structure
    xldate_as_tuple(xldate, datemode)

def get_total_column_number(xls_file,sheet_no=0):

    book = xlrd.open_workbook(xls_file,formatting_info=True)
    s = book.sheet_by_index(sheet_no)
    return s.ncols

import transaction
EACH_TRANSACTION_ROWS = 15
def export_data_from_xls_to_mysql(xls_file,number_of_columns,sheet_no=0):
    print 'getting the table structure from xls'
    if not number_of_columns:
        number_of_columns = get_total_column_number(xls_file)
    TABLE_STRUCTURE = get_table_structure_from_xls(xls_file,number_of_columns,sheet_no)
    table_name = os.path.basename(xls_file)
    table_name = table_name[:-4]+"_table"
    #print table_name
    print 'creating table %s '%table_name
    mydb.drop_table_if_exists(table_name)

    mydb.create_table(table_name,TABLE_STRUCTURE)

    print 'getting all the data...'
    Data = get_all_data_xlrd(xls_file,number_of_columns,sheet_no=0)
    print 'total rows:%s'%str(len(Data))
    print 'inserting into table..'

    msg = '<font color="red">'
    counter = 0
    for row in Data:
        counter +=1
        if counter%EACH_TRANSACTION_ROWS==0:
            transaction.commit()
        mydb.insert_into_table(table_name,row)

    transaction.commit()
        #print row
    print 'inserted rows %s'%str(counter)
    msg +='</font>'
    return msg