# File:: excel_tools.rb
# Description:: XLS MS Excel spreadsheet tools
#
# Author:: Derek Ward <derek.ward@rockstarnorth.com>
# Date:: 07 October 2010
#

#-----------------------------------------------------------------------------
# Uses
#-----------------------------------------------------------------------------
require 'win32ole'
require 'pipeline/os/path'
require 'time'
require 'pipeline/os/getopt'
require 'pipeline/os/path'
require 'fileutils'
include FileUtils
require 'dl'
include Pipeline
require 'pipeline/log/log'

#-----------------------------------------------------------------------------
# Constants
#-----------------------------------------------------------------------------

#-----------------------------------------------------------------------------
# Implementation
#-----------------------------------------------------------------------------
module ExcelTools
	
	#---------------------------------------------------------------------	
	# class repository for Excel constants involved in Win32OLE calls.
	class ExcelConst
		def ExcelConst::filled=(val)
			@@filled = val
		end
		def ExcelConst::filled()
			@@filled
		end
		private
		@@filled = false
	end		
	
	#---------------------------------------------------------------------	
	# class repository for MSO constants involved in Win32OLE calls.
	class MSOConst
		def MSOConst::filled=(val)
			@@filled = val
		end
		def MSOConst::filled()
			@@filled
		end
		private
		@@filled = false
	end					
	
	def ExcelTools::Create()
					
		puts "Staring Excel"
		begin
			excel = WIN32OLE.connect("excel.application") 							
		rescue
			#puts " * A new instance of Excel has been started, if you see this message more than once it may be a leaking process! * "
			excel = WIN32OLE.new("excel.application") if excel==nil 
		end
					
		excel.DisplayAlerts = false
					
		WIN32OLE.const_load(excel, ExcelConst) if not ExcelConst::filled
		ExcelConst::filled = true
		#WIN32OLE.const_load('Microsoft Office 9.0 Object Library', MSOConst) if not MSOConst::filled						
		#MSOConst::filled = true
		#excel.visible = true     # in case you want to see what happens 

		excel
	end

	#
	# == Description
	# yield a block to work with the worksheet
	def ExcelTools::Run(xls_filename)		
		begin
			puts "File not found #{xls_filename}" and return '' if not File.exists?(xls_filename)
			
			if File::exists?( xls_filename ) then 
				begin		
					puts "Opening #{xls_filename}"
					excel 		= ExcelTools::Create()
					workbook	= excel.Workbooks.Open(xls_filename)
					worksheet	= workbook.Worksheets(1)								
					worksheet.Activate														
					
					data		= worksheet.UsedRange.Value
					num_rows	= data.size
					num_columns 	= data[0].size
				
					if (block_given?)
						yield(worksheet, data, num_rows, num_columns)
					else 
						puts "no block given!?"
					end

					workbook.saveas(xls_filename)
					workbook.close	
					
				rescue Exception => ex
					puts "WIN32OLE Excel stuff has quit unexpectedly:"
					puts "\t#{ex.message}"
					ex.backtrace.each do |m| puts "\t#{m}"; end
					excel.Quit()
				ensure
					excel.Quit() unless excel.nil?
				end	# end begin 1
				
				excel.Quit() unless excel.nil?				
			end # end if file exists
			
		rescue Exception => ex
			puts "ExcelTools::Run has quit unexpectedly."
			puts "\t#{ex.message}"
			ex.backtrace.each { |m| puts "\t#{m}" }
		end			
	end # Run

	#
	# == Description
	# append a row to the worksheet
	def ExcelTools::AppendRow(xls_filename, trailing)		
		begin
			ExcelTools::Run(xls_filename) do |worksheet, data, num_rows, num_columns|
				trailing.each_with_index do |cell, idx|
					worksheet.cells(num_rows+1, idx+1)['Value'] = cell
				end	
			end			
		rescue Exception => ex
			puts "ExcelTools::AppendRow has quit unexpectedly."
			puts "\t#{ex.message}"
			ex.backtrace.each { |m| puts "\t#{m}" }
		end	
		
		puts "AppendRow completed"
	end # analyse_file	
	
	#
	# == Description
	# append a row to the worksheet
	def ExcelTools::SetCell(xls_filename, row, col, val)		
		begin
			ExcelTools::Run(xls_filename) do |worksheet, data, num_rows, num_columns|
				worksheet.cells(row, col)['Value'] = val	
			end			
		rescue Exception => ex
			puts "ExcelTools::SetCell has quit unexpectedly."
			puts "\t#{ex.message}"
			ex.backtrace.each { |m| puts "\t#{m}" }
		end	
		
		puts "SetCell completed"
	end # SetCell	
	
	#
	# == Description
	# Delete last row 
	def ExcelTools::DeleteRow(xls_filename, row_num)		
		begin
			ExcelTools::Run(xls_filename) do |worksheet, data, num_rows, num_columns|				
				row_num = num_rows + row_num if (row_num < 0)
				row_num += 1 # cos excel starts at index 1
				worksheet.rows(row_num).delete	
			end			
		rescue Exception => ex
			puts "ExcelTools::AppendRow has quit unexpectedly."
			puts "\t#{ex.message}"
			ex.backtrace.each { |m| puts "\t#{m}" }
		end	
		
		puts "AppendRow completed"
	end # ExcelTools::DeleteLastRow	

	#
	# == Description
	# Delete last row 
	def ExcelTools::Row(xls_filename, row_num)		

		row = []

		begin
			ExcelTools::Run(xls_filename) do |worksheet, data, num_rows, num_columns|
				row_num = num_rows + row_num if (row_num < 0)	
				row_num += 1 # cos excel starts at index 1

				if (row_num > 1)					
					num_columns.times do |col|
						row << worksheet.cells(row_num, col+1)['Value']
					end	
				end
			end			
		rescue Exception => ex
			puts "ExcelTools::ReadRow has quit unexpectedly."
			puts "\t#{ex.message}"
			ex.backtrace.each { |m| puts "\t#{m}" }
		end	
		
		puts "ReadRow completed #{row.to_s}"
		row
	end # ExcelTools::ReadRow

end # Exceltools module

# Exceltools.rb