'Class:ExcelData: 

Option Public
Option Declare
Option Compare Nocase

%REM
******************************************ExcelData Class************************************
Purpose
The purpose of this class was to give me an excuse to learn about writing Classes in LotusScript.
But other than that, you can use this class to get data from or put data into excel and do some
basic formatting on the cells.

Requirements
* Windows Lotus Notes Workstation
* Microsoft Excel installed

Credit where Credit is due...
* Christian Gorni - I modified his "exportNotesView" sub to more match my style of coding. I did
	use his "doColumnExport" function exactly as he wrote it
* Ben Langhinrichs Ben Langrich - Helped me with the convAlphToNum function, it's his code for the final
	value of convAlphtoNum
******************************************ExcelData Class************************************
%END REM
Public Class ExcelData
	Private excel As Variant
	Private wrkBk As Variant
	Private wrkSht As Variant
	Private w As NotesUIWorkspace	
	Private strFileName As String
'********************************* C O N S T R U C T O R S *******************************	
	Public Sub new (strFileName As String, askPath As Boolean)
		Dim filenames As Variant
		
		Set w = New NotesUIWorkspace
		Set excel = CreateObject("Excel.Application")
		If askPath = True Then
			Set w = New NotesUIWorkspace
			filenames = w.OpenFileDialog(False,"Open Spreadsheet","Excel|*.xls","c:\")
			If Isempty(filenames) Then
				Msgbox "Cancelling",0,"Cancelled"
				End
			End If
			Me.filename = filenames(0)
		End If
		If strFileName = "" And askPath = False Then
			Set wrkBk = excel.Workbooks.Add()
		Elseif strFilename <> "" And askPath = True Then
			excel.Workbooks.Open Me.fileName
			Set wrkBk = excel.ActiveWorkbook
		End If
		Set wrkSht = wrkBk.WorkSheets(1)
	End Sub
'*****************************************	
	Sub Delete
		Set excel = Nothing
	End Sub
'****************************** E N D  C O N S T R U C T O R S ***************************
	
'********************************* P R O P E R T I E S *******************************		
	Property Get getData(cell As String) As String
		getData = excel.ActiveSheet.Range(cell).Value
	End Property
'*****************************************	
	Property Get rows As Integer
		rows = wrkBk.ActiveSheet.UsedRange.Rows.Count
	End Property
'*****************************************	
	Property Set isVisible As Boolean
		excel.Application.Visible = isVisible
	End Property
'*****************************************
	Property Set fileName As String
		strFileName = fileName
	End Property
	
	Property Get fileName As String
		Dim fileNameStr As Variant
		If strFileName = "" Then
			fileNameStr = w.SaveFileDialog(False,"Save As")
			fileName = fileNameStr(0)
		Else
			fileName = strFileName			
		End If
	End Property
'***************************** E N D   P R O P E R T I E S ***************************	
	
'************************************** M E T H O D S ********************************	
	Public Sub saveExcel
		With wrkBk
			.SaveAs(Me.fileName)
		End With
	End Sub
'*****************************************	
	Public Function formatCell(cell As String, color As String, fontBold As Boolean, fontColor As String)
		With excel.ActiveSheet.Range(cell)
			If color <> "" Then
				.Interior.ColorIndex = getColor(color)
			End If
			.font.Bold = fontBold
			If fontColor <> "" Then
				.font.ColorIndex = getColor(fontColor)
			End If
		End With
		
	End Function
'*****************************************
	Public Function setData(cell As String, cellValue As String)
		With excel
			.ActiveSheet.Range(cell).FormulaR1C1 = cellValue
		End With
	End Function
'*****************************************	
	Private Function getColor(color As String)
		Dim colorList List As Integer
		colorList("black") = 1
		colorList("white") = 2
		colorList("red") = 3
		colorList("green") = 4
		colorList("blue") = 5
		colorList("yellow") = 6
		colorList("light blue") = 8
		colorList("gray") = 15
		
		If Iselement(colorList(color)) = False Then
			Msgbox "You defined a color that isn't allowed!" + Chr(13) + "Exiting",0,"Error"
			End
		Else
			getColor = colorList(color)
		End If
	End Function
'*****************************************	
	Public Sub sizeColumns(colRange As String)
		With excel
			.ActiveSheet.Range(colRange).EntireColumn.Autofit
		End With
	End Sub
'*****************************************	
	Public Sub sizeAllColumns
		With excel
			.ActiveSheet.Columns.Autofit
		End With
	End Sub
'*****************************************	
	Public Sub sizeRows
		With excel
			.ActiveSheet.Rows.Autofit
		End With
	End Sub
'*****************************************	
	Public Sub wordWrap(cellRange As String, wwOn As Boolean)
		With excel
			.ActiveSheet.Range(cellRange).WrapText = wwOn
		End With
	End Sub
'*****************************************	
	Public Sub exportNotesView(view As NotesView, isWithheader As Boolean, includeIcons As Boolean,_
	includeColors As Boolean, includeHidden As Boolean)
		Dim entry As NotesViewEntry
		Dim entryCol As NotesViewEntryCollection
		Dim viewcolumns As Variant
		Dim column As NotesViewColumn
		Dim i As Integer, j As Integer, k As Integer
		Dim cell As String
		Dim colValue As Variant
		Dim colVals As Variant
		
		Set entryCol 	= view.AllEntries		
		Set entry		= entryCol.GetFirstEntry
		viewcolumns	= view.Columns
		
		If isWithHeader Then
			For i = 0 To view.ColumnCount -1
				Set column = viewcolumns(i)
				cell = convAlphtoNum(Cstr(i)+1) + "1"					
				Call Me.setData(cell,column.Title)
			Next
		End If
		
		For i = 1 To view.EntryCount
			Set entry = entryCol.GetNthEntry(i)
			For j = 0 To Ubound(entry.ColumnValues)
				If doColumnExport(column, includeHidden, IncludeIcons, includeColors) Then
					If Isarray(entry.ColumnValues(j)) = True Then
						k = 0
						colVals = entry.ColumnValues(j)
						Forall colValArray In colVals
							If k = 0 Then
								colValue = colValArray + "  "
							Else
								colValue = colValue + Chr(10) + colValArray + "  "
							End If
							k = k + 1
						End Forall
					Else
						colValue = entry.ColumnValues(j)
					End If
					Call Me.setData(convAlphToNum(Cstr(j)+1) + Cstr(i+1),Cstr(colValue))
				End If
			Next
		Next
	End Sub
'*****************************************	
	Private Function doColumnExport (viewcol As NotesViewColumn, includeHidden As Boolean,_
	IncludeIcons As Boolean, includeColors As Boolean) As Boolean
		Dim isHiddenOK 	As Boolean
		Dim isIconOK 		As Boolean
		Dim isColorOK 		As Boolean
		
		isHiddenOK 			= (viewcol.isHidden And IncludeHidden) Or Not viewcol.isHidden
		isIconOK				= (viewcol.isIcon And IncludeIcons) Or Not (viewcol.isIcon)
		isColorOK				= True
		doColumnExport 	= isHiddenOK And isIconOK And isColorOK
	End Function
'*****************************************	
	Private Function convAlphtoNum(num As Integer) As String
		If num <= 26 Then
			convAlphtoNum = Cstr(Chr(64+(num Mod 26)))
		Elseif num > 26 Then
			convAlphtoNum = Cstr(Chr(64+(num/26)) + Chr(64+(num Mod 26)))
		End If
	End Function
'*****************************************
	Public Sub setCommentText(cell As String, commentText As String)
		With excel
			.ActiveSheet.Range(cell).AddComment(commentText)
		End With
	End Sub
'*****************************************
	Public Sub addBorder(cellRange As String, bordColor As String, LineStyle As String, LineType As String)
		bordColor = Me.getColor(bordColor)
		With excel
			.ActiveSheet.Range(cellRange).Borders.LineStyle = getBorderConstNum(LineStyle)
			.ActiveSheet.Range(cellRange).Borders(getBorderConstNum(LineType)).Color = bordColor
		End With		
	End Sub
'*****************************************
	Private Function getBorderConstNum(xlConst As String) As Integer
		Dim xlConstList List As Integer
		xlConstList("xlContinuous") = 1
		xlConstList("xlDash") = -4115
		xlConstList("xlDashDot") = 5
		xlConstList("xlDot") = -4118
		xlConstList("xlDouble") = -4119
		xlConstList("xlLineStyleNone") = -4142
		xlConstList("xlSlantDashDot") = 13
		xlConstList("xlHairline") = 1
		xlConstList("xlMedium") = -4138
		xlConstList("xlThick") = 4
		xlConstList("xlThin") = 2
		xlConstList("xlDiagonalDown") = 5
		xlConstList("xlDiagonalUp") = 6
		xlConstList("xlEdgeBottom") = 9
		xlConstList("xlEdgeLeft") = 7
		xlConstList("xlEdgeRight") = 10
		xlConstList("xlEdgeTop") = 8
		xlConstList("xlInsideHorizontal") = 12
		xlConstList("xlInsideVertical") = 11
		
		If Iselement(xlConstList(xlConst)) = False Then
			Msgbox "You defined an Excel constant that isn't allowed!" + Chr(13) + "Exiting",0,"Error"
			End
		Else
			getBorderConstNum = xlConstList(xlConst)
		End If
	End Function
'*****************************************
	Public Sub setColumnWidth(columnLetter As String, size As Integer)
		With excel
			.ActiveSheet.Columns(columnLetter).ColumnWidth = size
		End With
	End Sub
'*********************************** E N D   M E T H O D S ***************************
End Class