Surya's Sandbox

"""
Export or Copy ADODB Recordset to Excel Spreadsheet
@Author Surya Nyayapati
@Date Jun-25-2009
"""
 
import clr
 
from System.Reflection import Assembly
from System.Reflection import Missing
 
#From C:\WINDOWS\assembly Copied the Display name for ADODB
clr.AddReferenceByName('ADODB, Version=7.0.3300.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a')
from ADODB import ConnectionClass, RecordsetClass, CursorLocationEnum
 
connString = "DSN=myDsn;Uid=myUsername;Pwd=myPassword;"
 
conn = ConnectionClass()
rs = RecordsetClass()
conn.Open(connString)
 
conn.CursorLocation = CursorLocationEnum.adUseClient
query = "SELECT * FROM user_group" # Any SELECT SQL
rs = conn.Execute(query)
"""
#To iterate over Recordset
while rs.EOF <> True:	
	print "%s - %s" %(rs[0].Item[0].Name.ToString(),rs[0].Item[0].Value.ToString())
	print "%s - %s" %(rs[0].Item[1].Name.ToString(),rs[0].Item[1].Value.ToString())	
        # ...
	rs.MoveNext()
"""	
 
#Start a New Excel Instance
VSTOpath = "C:\\Program Files\\Microsoft Visual Studio 9.0\\Visual Studio Tools for Office\\PIA\\Office11\\"
 
#Load and add reference to Excel dll
excelAssemblyPath = "%sMicrosoft.Office.Interop.Excel.dll" % VSTOpath
excelAssembly = Assembly.LoadFile(excelAssemblyPath)
clr.AddReference(excelAssembly)
from Microsoft.Office.Interop import Excel
 
import System.IO.Directory
excelApp = Excel.ApplicationClass()   
excelApp.DefaultFilePath = System.IO.Directory.GetCurrentDirectory()
excelApp.Visible = True
excelApp.ScreenUpdating = False;
excelApp.DisplayAlerts = False   
excelApp.UserControl = False;
try:
	book1 = excelApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet)
	sheet1 = book1.ActiveSheet
 
        headRange = sheet1.Range("A1", "D1")	
 
	#Python does not provide built-in support for multi-dimensional arrays
	from System import Array
	array2D = Array.CreateInstance(object, 2, 4)
	array2D[0,0] = "HEADER 1"
	array2D[0,1] = "HEADER 2"
	array2D[0,2] = "HEADER 3"
	array2D[0,3] = "HEADER 4"
 
	headRange.Value2 = array2D
	headRange.Font.Bold = True
	sheet1.Range("A2").CopyFromRecordset(rs, Missing.Value, Missing.Value)
 
except StandardError, (ErrorMessage):
	print "something wrong (%s)" % ErrorMessage
 
finally:
	excelApp.ScreenUpdating = True;
        excelApp.DisplayAlerts = True;
        excelApp.UserControl = True;
	#excelApp.Quit()