from xlrd import open_workbook, colname, xldate_as_tuple
from xlwt import Workbook
# from xlutils.copy import copy
from collections import namedtuple
from datetime import date


# column names so I can refer to them with lowercase letters instead of numbers
Cols = namedtuple( "Cols", " ".join( colname(c).lower() for c in range(44) ) )
col = Cols( *range(44) )

class Excel(object):
	"""docstring for Excel"""
	def __init__(self, source_fn, outfn):
		# where the data we want to process is currently located
		self.source_fn = source_fn

		# we save output Workbook to this file
		self.outfile = open( outfn, "wb" )
		
		self.bk = open_workbook( self.source_fn, on_demand=True )
		# the data we want to parse is on first sheet, zero-indexed
		self.sh = self.bk.sheet_by_index(0)

		# convenient instance ppties for cell values and types objects
		# cell value
		self.cv = self.sh.cell_value
		# cell type
		self.ct = self.sh.cell_type

		# make a new Workbook
		self.wb = Workbook()

		# make a worksheet. this is the output worksheet that we'll write to
		ws = self.wb.add_sheet( "Sheet1", cell_overwrite_ok=True )

		# begin row in output worksheet
		wrow = 0

		# we bundle the worksheet and its row object together. this is needed where we'll be writing to 2 
		# or more worksheets it enables us to pass both sheet and row objects to the writer method
		self.sheet_objs = [ ws, wrow ]

		# we begin processing source Workbook from this row
		self.srow = 0

		################################################################
		# copy the workbook so we can overwrite some cells
		#self.wb = copy( self.bk )
		#
		# the data to be overwritten will be in the first worksheet, so we get it
		#self.ws = self.wb.get_sheet( 0 )
		#################################################################		

		# the last row in source Workbook to be processed
		self.endrow = self.sh.nrows

		# write the headers if any
		self.headers()


	def parse_date( self,val ):
		"fix date. xlrd claims that ms excel stores date internally as float, so we need this function to fix that"
		# @param val:the float obtained by calling sheet.cell_value on a column containing excel date
		date_tuple = xldate_as_tuple( val, self.bk.datemode )

		# we return a date object so callers can format it in any way they want
		return date( *date_tuple[:3] )


	def headers(self):
		"we write the headers"
		headers = [ '' ]
		self.writerow( self.sheet_objs, headers )


	def writerow( self, sheetinfo, args ):
		'function for writing a row of values to the output worksheet'
		# do any set up here
		sheet, row = sheetinfo
		# write row of values to worksheet
		[ sheet.row( row ).write( c, val )  for c,val in enumerate( args ) ]

		# increment row number for next write
		sheetinfo[1] = row + 1


	def writecell( self, sheet, row, col_index, val ):
		'function for writing a cell value to the output worksheet'
		
		# overwrite  the cell
		sheet.row( row ).write( col_index, val )		


	def process_rows(self):
		"""process the rows in source and call self.writerow to rewrite the 
		formatted values to destination sheet"""	

		for row in range( self.srow, self.endrow ):
			# print something so we seeing what row in source we are processing
			print "processing row ", row + 1

			#get the entire row
			row_obj = self.sh.row(row)

			#get one cell value
			self.cv(row, col.x)
			

	def main(self):
		"program entry point. calls process_rows and saves Workbook"

		# process row in source
		self.process_rows()

		# we are done so save output Workbook
		self.wb.save(  self.outfile )

		self.outfile.close()



if __name__ == '__main__': 
	source_fn = ''
	outfn = ''
	xl = Excel( source_fn, outfn )
	xl.main()from xlrd import open_workbook, colname, xldate_as_tuple
from xlwt import Workbook
# from xlutils.copy import copy
from collections import namedtuple
from datetime import date


# column names so I can refer to them with lowercase letters instead of numbers
Cols = namedtuple( "Cols", " ".join( colname(c).lower() for c in range(44) ) )
Python: xlrdd definition