' Class file for writing Microsoft Excel BIFF 2.1 files.

' This class is intended for users who do not want to use the huge
' Jet or ADO providers if they only want to export their data to
' an Excel compatible file.

' Newer versions of Excel use the OLE Structure Storage methods
' which are quite complicated.

' Changed for use with Lotus Notes / Lotus Script ; September 2005, Heinz Ulrich Krause
' Original Code Paul Squires, November 10, 2001
' rambo2000@canada.com


' the memory copy API is used in the MKI$ function which converts an integer
' value to a 2-byte string value to write to the file. (used by the Horizontal
' Page Break function).
Declare Sub CopyMemory Lib "KERNEL32" Alias "RtlMoveMemory" (lpvDest As Any, lpvSource As Any, Byval cbCopy As Long)

'TYPE
Const    TYPE_INTEGER = 0
Const    TYPE_NUMBER = 1
Const    TYPE_LABEL = 2

' Alignment
Const    CELL_ALIGN_GENERAL = 0
Const    CELL_ALIGN_LEFT = 1
Const    CELL_ALIGN_CENTER = 2
Const    CELL_ALIGN_RIGHT = 3
Const    CELL_FILL = 4
Const    CELL_BORDER_LEFT = 8
Const    CELL_BORDER_RIGHT = 16
Const    CELL_BORDER_TOP = 32
Const    CELL_BORDER_BOTTOM = 64
Const    CELL_SHADED = 128

' Font
'used by rgbAttr2
'bits 0-5 handle the *picture* formatting, not bold/underline etc...
'bits 6-7 handle the font number
Const    FONT_0 = 0
Const    FONT_1 = 64
Const    FONT_2 = 128
Const    FONT_3 = 192

' CellHiddenLocked
'used by rgbAttr1
'bits 0-5 must be zero
'bit 6 locked/unlocked
'bit 7 hidden/not hidden
Const   CELL_NORMAL = 0
Const   CELL_LOCKED = 64
Const   CELL_HIDDEN = 128
'set up variables to hold the spreadsheet's layout
'MarginTypes
Const   MARGIN_LEFT = 38
Const   MARGIN_RIGHT = 39
Const   MARGIN_TOP = 40
Const   MARGIN_BOTTOM = 41

'FontFormatting
'add these enums together. For example: xlsBold + xlsUnderline
Const   FORMAT_NONE = 0
Const   FORMAT_BOLD = 1
Const   FORMAT_ITALIC = 2
Const   FORMAT_UNDERLINE = 4
Const   FORMAT_STRIKEOUT = 8


Private Type FONT_RECORD
	opcode As Integer  '49
	length As Integer  '5+len(fontname)
	FontHeight As Integer
	   'bit0 bold, bit1 italic, bit2 underline, bit3 strikeout, bit4-7 reserved
	FontAttributes1 As Byte
	FontAttributes2 As Byte  'reserved - always 0
	FontNameLength As Byte
End Type

Private Type PASSWORD_RECORD
	opcode As Integer  '47
	length As Integer  'len(password)
End Type


Private Type HEADER_FOOTER_RECORD
	opcode As Integer  '20 Header, 21 Footer
	length As Integer  '1+len(text)
	TextLength As Byte
End Type


Private Type PROTECT_SPREADSHEET_RECORD
	opcode As Integer  '18
	length As Integer  '2
	Protect As Integer
End Type

Private Type FORMAT_COUNT_RECORD
	opcode As Integer  '1f
	length As Integer '2
	Count As Integer
End Type

Private Type FORMAT_RECORD
	opcode As Integer  '1e
	length As Integer  '1+len(format)
	FormatLenght As Byte 'len(format)
End Type '+ followed by the Format-Picture

Private Type COLWIDTH_RECORD
	opcode As Integer  '36
	length As Integer  '4
	col1 As Byte       'first column
	col2 As Byte       'last column
	ColumnWidth As Integer   'at 1/256th of a character
End Type

'Beginning Of File record
Private Type BEG_FILE_RECORD
	opcode As Integer
	length As Integer
	version As Integer
	ftype As Integer
End Type

'End Of File record
Private Type END_FILE_RECORD
	opcode As Integer
	length As Integer
End Type

'true/false to print gridlines
Private Type PRINT_GRIDLINES_RECORD
	opcode As Integer
	length As Integer
	PrintFlag As Integer
End Type

'Integer record
Private Type tInteger
	opcode As Integer
	length As Integer
	Row As Integer     'unsigned integer
	col As Integer
	
  'rgbAttr1 handles whether cell is hidden and/or locked
	rgbAttr1 As Byte
	
  'rgbAttr2 handles the Font# and Formatting assigned to this cell
	rgbAttr2 As Byte
	
  'rgbAttr3 handles the Cell Alignment/borders/shading
	rgbAttr3 As Byte
	
	intValue As Integer  'the actual integer value
End Type

'Number record
Private Type tNumber
	opcode As Integer
	length As Integer
	Row As Integer
	col As Integer
	rgbAttr1 As Byte
	rgbAttr2 As Byte
	rgbAttr3 As Byte
	NumberValue As Double  '8 Bytes
End Type

'Label (Text) record
Private Type tText
	opcode As Integer
	length As Integer
	Row As Integer
	col As Integer
	rgbAttr1 As Byte
	rgbAttr2 As Byte
	rgbAttr3 As Byte
	TextLength As Byte
End Type

Private Type MARGIN_RECORD_LAYOUT
	opcode As Integer
	length As Integer
	MarginValue As Double  '8 bytes
End Type

Private Type HPAGE_BREAK_RECORD
	opcode As Integer
	length As Integer
	NumPageBreaks As Integer
End Type

Private Type DEF_ROWHEIGHT_RECORD
	opcode As Integer
	length As Integer
	RowHeight As Integer
End Type

Private Type ROW_HEIGHT_RECORD
	opcode As Integer  '08
	length As Integer  'should always be 16 bytes
	RowNumber As Integer
	FirstColumn As Integer
	LastColumn As Integer
	RowHeight As Integer  'written to file as 1/20ths of a point
	internal As Integer
	DefaultAttributes As Byte  'set to zero for no default attributes
	FileOffset As Integer
	rgbAttr1 As Byte
	rgbAttr2 As Byte
	rgbAttr3 As Byte
End Type

Private FileNumber As Integer

Private BEG_FILE_MARKER As BEG_FILE_RECORD
Private END_FILE_MARKER As END_FILE_RECORD
Private HORIZ_PAGE_BREAK As HPAGE_BREAK_RECORD
Private INTEGER_RECORD As tInteger
Private NUMBER_RECORD As tNumber
Private b As Byte
Private TEXT_RECORD As tText

'create an array that will hold the rows where a horizontal page
'break will be inserted just before.
Private HorizPageBreakRows() As Integer
Private NumHorizPageBreaks As Integer

Class ExcelFile
	
Declare Public Sub ProtectSpreadsheet( newvalue As Integer )
Declare Public Sub PrintGridLines ( newvalue As Integer )
Declare Public Function SetFont(FontName As String, FontHeight As Integer, FontFormat As Integer) As Integer
Declare Public Function SetRowHeight(lrow As Long, HeightValue As Integer)
Declare Public Function SetFooter(FooterText As String) As Integer
Declare Public Function SetDefaultRowHeight(HeightValue As Integer)
Declare Public Function SetFilePassword(PasswordText As String) As Integer
Declare Public Function SetHeader(HeaderText As String) As Integer
Declare Public Function MKI(x As Integer)
Declare Public Function WriteDefaultFormats() As Integer
Declare Public Function InsertHorizPageBreak(lrow As Long) As Integer
Declare Public Function CreateFile(FileName As String) As Integer
Declare Public Function CloseFile() As Integer
Declare Public Function WriteValue(ValueType As Integer, CellFontUsed As Integer, Alignment As Integer, HiddenLocked As Integer, lrow As Long, lcol As Long, value As Variant,  CellFormat As Long ) As Integer
Declare Public Function SetMargin(Margin As Integer, MarginValue As Double) As Integer	
	
	Public Function SetMargin(Margin As Integer, MarginValue As Double) As Integer
		
		On Error Goto Write_Error
		
    'write the spreadsheet's layout information (in inches)
		Dim MarginRecord As MARGIN_RECORD_LAYOUT
		
		MarginRecord.opcode = Margin
		MarginRecord.length = 8
		MarginRecord.MarginValue = MarginValue 'in inches
		
		Put #FileNumber, , MarginRecord
		
		SetMargin = 0
		
		Exit Function
		
Write_Error:
		
		Exit Function
		
	End Function
	
	Public Sub PrintGridLines (newValue As Boolean)
		
		On Error Goto Write_Error
		
		Dim GRIDLINES_RECORD As PRINT_GRIDLINES_RECORD
		
		GRIDLINES_RECORD.opcode = 43
		GRIDLINES_RECORD.length = 2
		If newValue = True Then
			GRIDLINES_RECORD.PrintFlag = 1
		Else
			GRIDLINES_RECORD.PrintFlag = 0
		End If
		
		Put #FileNumber, , GRIDLINES_RECORD
		
		Exit Sub
		
Write_Error:
		Exit Sub
	End Sub
	
	
	Public Sub ProtectSpreadsheet( newvalue As Integer )
		
		On Error Goto Write_Error
		
		Dim PROTECT_RECORD As PROTECT_SPREADSHEET_RECORD
		
		PROTECT_RECORD.opcode = 18
		PROTECT_RECORD.length = 2
		If newvalue = True Then
			PROTECT_RECORD.Protect = 1
		Else
			PROTECT_RECORD.Protect = 0
		End If
		
		
		Put #FileNumber, , PROTECT_RECORD
		
		Exit Sub
		
Write_Error:
		Exit Sub
		
	End Sub
	
	Public Function SetFont(FontName As String, FontHeight As Integer, FontFormat As Integer) As Integer
		
		On Error Goto Write_Error
		
    'you can set up to 4 fonts in the spreadsheet file. When writing a value such
    'as a Text or Number you can specify one of the 4 fonts (numbered 0 to 3)
		
		Dim FONTNAME_RECORD As FONT_RECORD
		
		l% = Len(FontName)
		
		FONTNAME_RECORD.opcode = 49
		FONTNAME_RECORD.length = 5 + l%
		FONTNAME_RECORD.FontHeight = FontHeight * 20
		FONTNAME_RECORD.FontAttributes1 = Cbyte(FontFormat)  'bold/underline etc...
		FONTNAME_RECORD.FontAttributes2 = Cbyte(0) 'reserved-always zero!!
		FONTNAME_RECORD.FontNameLength = Cbyte(Len(FontName))
		
		Put #FileNumber, , FONTNAME_RECORD
		
    'Then the actual font name data
		Dim b As Byte
		For a = 1 To l%
			b = Asc(Mid$(FontName, a, 1))
			Put #FileNumber, , b
		Next
		
		SetFont = 0
		
		Exit Function
		
Write_Error:
		Exit Function
		
		
	End Function
	
	Public Function MKI(x As Integer)
   'used for writing integer array values to the disk file
		temp$ = Space$(2)
		CopyMemory Byval temp$, x, 2
		MKI = temp$
	End Function
	
	Public Function SetRowHeight(lrow As Long, HeightValue As Integer)
		
		On Error Goto Write_Error
		
'the row and column values are written to the excel file as
'unsigned integers. Therefore, must convert the longs to integer.
		
		If lrow > 32767 Then
			Row% = Cint(lrow - 65536)
		Else
			Row% = Cint(lrow) - 1    'rows/cols in Excel binary file are zero based
		End If
		
		
'Height is defined in units of 1/20th of a point. Therefore, a 10-point font
'would be 200 (i.e. 200/20 = 10). This function takes a HeightValue such as
'14 point and converts it the correct size before writing it to the file.
		
		Dim ROWHEIGHTREC As ROW_HEIGHT_RECORD
		
		ROWHEIGHTREC.opcode = 8
		ROWHEIGHTREC.length = 16
		ROWHEIGHTREC.RowNumber = Row%
		ROWHEIGHTREC.FirstColumn = 0
		ROWHEIGHTREC.LastColumn = 256
		ROWHEIGHTREC.RowHeight = HeightValue * 20 'convert points to 1/20ths of point
		ROWHEIGHTREC.internal = 0
		ROWHEIGHTREC.DefaultAttributes = 0
		ROWHEIGHTREC.FileOffset = 0
		ROWHEIGHTREC.rgbAttr1 = 0
		ROWHEIGHTREC.rgbAttr2 = 0
		ROWHEIGHTREC.rgbAttr3 = 0
		
		Put #FileNumber, , ROWHEIGHTREC
		
		SetRowHeight = 0
		
		Exit Function
		
Write_Error:
		
		Exit Function
		
	End Function
	
	Public Function SetFooter(FooterText As String) As Integer
		
		On Error Goto Write_Error
		
		Dim FOOTER_RECORD As HEADER_FOOTER_RECORD
		
		l% = Len(FooterText)
		
		FOOTER_RECORD.opcode = 21
		FOOTER_RECORD.length = 1 + l%
		FOOTER_RECORD.TextLength = Cbyte(Len(FooterText))
		
		Put #FileNumber, , FOOTER_RECORD
		
    'Then the actual Header text
		Dim b As Byte
		For a = 1 To l%
			b = Asc(Mid$(FooterText, a, 1))
			Put #FileNumber, , b
		Next
		
		SetFooter = 0
		
		Exit Function
		
Write_Error:
		
		Exit Function
		
	End Function
	
	Public Function SetDefaultRowHeight(HeightValue As Integer)
		
		On Error Goto Write_Error
		
'Height is defined in units of 1/20th of a point. Therefore, a 10-point font
'would be 200 (i.e. 200/20 = 10). This function takes a HeightValue such as
'14 point and converts it the correct size before writing it to the file.
		
		Dim DEFHEIGHT As DEF_ROWHEIGHT_RECORD
		
		DEFHEIGHT.opcode = 37
		DEFHEIGHT.length = 2
		DEFHEIGHT.RowHeight = HeightValue * 20  'convert points to 1/20ths of point
		
		Put #FileNumber, , DEFHEIGHT
		
		SetDefaultRowHeight = 0
		
		Exit Function
		
Write_Error:
		
		Exit Function
		
	End Function
	
	Function SetFilePassword(PasswordText As String) As Integer
		
		On Error Goto Write_Error
		
		Dim FILE_PASSWORD_RECORD As PASSWORD_RECORD
		
		l% = Len(PasswordText)
		
		FILE_PASSWORD_RECORD.opcode = 47
		FILE_PASSWORD_RECORD.length = l%
		Put #FileNumber, , FILE_PASSWORD_RECORD
		
    'Then the actual Password text
		Dim b As Byte
		For a = 1 To l%
			b = Asc(Mid$(PasswordText, a, 1))
			Put #FileNumber, , b
		Next
		
		SetFilePassword = 0
		
		Exit Function
		
Write_Error:
		
		Exit Function
		
	End Function
	
	Public Function SetHeader(HeaderText As String) As Integer
		
		On Error Goto Write_Error
		
		Dim HEADER_RECORD As HEADER_FOOTER_RECORD
		
		l% = Len(HeaderText)
		
		HEADER_RECORD.opcode = 20
		HEADER_RECORD.length = 1 + l%
		HEADER_RECORD.TextLength = Cbyte(Len(HeaderText))
		
		Put #FileNumber, , HEADER_RECORD
		
    'Then the actual Header text
		Dim b As Byte
		For a = 1 To l%
			b = Asc(Mid$(HeaderText, a, 1))
			Put #FileNumber, , b
		Next
		
		SetHeader = 0
		
		Exit Function
		
Write_Error:
		Exit Function
		
	End Function
	
	Public Function WriteDefaultFormats() As Integer
		
		Dim cFORMAT_COUNT_RECORD As FORMAT_COUNT_RECORD
		Dim cFORMAT_RECORD As FORMAT_RECORD
		Dim lIndex As Long
		Dim aFormat(0 To 23) As String
		Dim l As Long
		Dim q As String
		q = Chr$(34)
		
		aFormat(0) = "General"
		aFormat(1) = "0"
		aFormat(2) = "0.00"
		aFormat(3) = "#,##0"
		aFormat(4) = "#,##0.00"
		aFormat(5) = "#,##0\ " & q & "$" & q & ";\-#,##0\ " & q & "$" & q
		aFormat(6) = "#,##0\ " & q & "$" & q & ";[Red]\-#,##0\ " & q & "$" & q
		aFormat(7) = "#,##0.00\ " & q & "$" & q & ";\-#,##0.00\ " & q & "$" & q
		aFormat(8) = "#,##0.00\ " & q & "$" & q & ";[Red]\-#,##0.00\ " & q & "$" & q
		aFormat(9) = "0%"
		aFormat(10) = "0.00%"
		aFormat(11) = "0.00E+00"
		aFormat(12) = "dd/mm/yy"
		aFormat(13) = "dd/\ mmm\ yy"
		aFormat(14) = "dd/\ mmm"
		aFormat(15) = "mmm\ yy"
		aFormat(16) = "h:mm\ AM/PM"
		aFormat(17) = "h:mm:ss\ AM/PM"
		aFormat(18) = "hh:mm"
		aFormat(19) = "hh:mm:ss"
		aFormat(20) = "dd/mm/yy\ hh:mm"
		aFormat(21) = "##0.0E+0"
		aFormat(22) = "mm:ss"
		aFormat(23) = "@"
		
		
		cFORMAT_COUNT_RECORD.opcode = &H1F
		cFORMAT_COUNT_RECORD.length = &H2
		cFORMAT_COUNT_RECORD.Count = Cint(Ubound(aFormat))
		
		Put #FileNumber, , cFORMAT_COUNT_RECORD
		
		For lIndex = Lbound(aFormat) To Ubound(aFormat)
			l = Len(aFormat(lIndex))
			
			cFORMAT_RECORD.opcode = &H1E
			cFORMAT_RECORD.length = Cint(l + 1)
			cFORMAT_RECORD.FormatLenght = Cint(l)
			
			Put #FileNumber, , cFORMAT_RECORD
			
        'Then the actual format
			Dim b As Byte, a As Long
			For a = 1 To l
				b = Asc(Mid$(aFormat(lIndex), a, 1))
				Put #FileNumber, , b
			Next
		Next lIndex
		
		Exit Function
		
	End Function
	
	Public Function InsertHorizPageBreak(lrow As Long) As Integer
		
		On Error Goto Page_Break_Error
		
'the row and column values are written to the excel file as
'unsigned integers. Therefore, must convert the longs to integer.
		If lrow > 32767 Then
			Row% = Cint(lrow - 65536)
		Else
			Row% = Cint(lrow) - 1    'rows/cols in Excel binary file are zero based
		End If
		
		NumHorizPageBreaks = NumHorizPageBreaks + 1
		Redim Preserve HorizPageBreakRows(NumHorizPageBreaks)
		
		HorizPageBreakRows(NumHorizPageBreaks) = Row%
		
		Exit Function
		
		
Page_Break_Error:
		
		Exit Function
		
		
	End Function
	
	Public Function CreateFile(FileName As String) As Integer
		
		On Error Goto Write_Error
		
		BEG_FILE_MARKER.opcode = 9
		BEG_FILE_MARKER.length = 4
		BEG_FILE_MARKER.version = 2
		BEG_FILE_MARKER.ftype = 10
		
		If Dir$(FileName) > "" Then
			Kill FileName
		End If
		
		FileNumber = Freefile
		Open FileName For Binary As #FileNumber
		Put #FileNumber, , BEG_FILE_MARKER  'must always be written first
		
		Call WriteDefaultFormats
		
    'create the Horizontal Page Break array
		Redim HorizPageBreakRows(0)
		NumHorizPageBreaks = 0
		
		OpenFile = 0  'return with no error
		
		Exit Function
		
Write_Error:
		Exit Function
		
	End Function
	
	
	Public Function CloseFile() As Integer
		
		On Error Goto Write_Error
		
		If FileNumber = 0 Then Exit Function
		
		END_FILE_MARKER.opcode = 10
		
		
    'write the horizontal page breaks if necessary
		If NumHorizPageBreaks > 0 Then
       'the Horizontal Page Break array must be in sorted order.
       'Use a simple Bubble sort because the size of this array would
       'be pretty small most of the time. A QuickSort would probably
       'be overkill.
			Dim lLoop1 As Long
			Dim lLoop2 As Long
			Dim lTemp As Long
			For lLoop1 = Ubound(HorizPageBreakRows) To Lbound(HorizPageBreakRows) Step -1
				For lLoop2 = Lbound(HorizPageBreakRows) + 1 To lLoop1
					If HorizPageBreakRows(lLoop2 - 1) > HorizPageBreakRows(lLoop2) Then
						lTemp = HorizPageBreakRows(lLoop2 - 1)
						HorizPageBreakRows(lLoop2 - 1) = HorizPageBreakRows(lLoop2)
						HorizPageBreakRows(lLoop2) = lTemp
					End If
				Next lLoop2
			Next lLoop1
			
       'write the Horizontal Page Break Record
			
			HORIZ_PAGE_BREAK.opcode = 27
			HORIZ_PAGE_BREAK.length = 2 + (NumHorizPageBreaks * 2)
			HORIZ_PAGE_BREAK.NumPageBreaks = NumHorizPageBreaks
			
			Put #FileNumber, , HORIZ_PAGE_BREAK
			
        'now write the actual page break values
        'the MKI$ function is standard in other versions of BASIC but
        'VisualBasic does not have it. A KnowledgeBase article explains
        'how to recreate it (albeit using 16-bit API, I switched it
        'to 32-bit).
			For x% = 1 To Ubound(HorizPageBreakRows)
				Put #FileNumber, , MKI(HorizPageBreakRows(x%))
			Next
		End If
		
		Put #FileNumber, , END_FILE_MARKER
		Close #FileNumber
		
		CloseFile = 0  'return with no error code
		
		Exit Function
		
Write_Error:
		Exit Function
		
	End Function
	
	Function WriteValue(ValueType As Integer, CellFontUsed As Integer, Alignment As Integer, HiddenLocked As Integer, lrow As Long, lcol As Long, value As Variant,  CellFormat As Long ) As Integer
		On Error Goto Write_Error
		
'the row and column values are written to the excel file as
'unsigned integers. Therefore, must convert the longs to integer.
		
		If lrow > 32767 Then
			Row% = Cint(lrow - 65536)
		Else
			Row% = Cint(lrow) - 1    'rows/cols in Excel binary file are zero based
		End If
		
		If lcol > 32767 Then
			col% = Cint(lcol - 65536)
		Else
			col% = Cint(lcol) - 1    'rows/cols in Excel binary file are zero based
		End If
		
		
		Select Case ValueType
		Case 0
			
			INTEGER_RECORD.opcode = 2
			INTEGER_RECORD.length = 9
			INTEGER_RECORD.Row = Row%
			INTEGER_RECORD.col = col%
			INTEGER_RECORD.rgbAttr1 = Cbyte(HiddenLocked)
			INTEGER_RECORD.rgbAttr2 = Cbyte(CellFontUsed + CellFormat)
			INTEGER_RECORD.rgbAttr3 = Cbyte(Alignment)
			INTEGER_RECORD.intValue = Cint(value)
			
			Put #FileNumber, , INTEGER_RECORD
			
		Case 1
			
			NUMBER_RECORD.opcode = 3
			NUMBER_RECORD.length = 15
			NUMBER_RECORD.Row = Row%
			NUMBER_RECORD.col = col%
			NUMBER_RECORD.rgbAttr1 = Cbyte(HiddenLocked)
			NUMBER_RECORD.rgbAttr2 = Cbyte(CellFontUsed + CellFormat)
			NUMBER_RECORD.rgbAttr3 = Cbyte(Alignment)
			NUMBER_RECORD.NumberValue = Cdbl(value)
			
			Put #FileNumber, , NUMBER_RECORD
			
		Case 2
			st$ = Cstr(value)
			l% = Len(st$)
			
			TEXT_RECORD.opcode = 4
			TEXT_RECORD.length = 10
          'Length of the text portion of the record
			TEXT_RECORD.TextLength = l%
          'Total length of the record
			TEXT_RECORD.length = 8 + l%
			TEXT_RECORD.Row = Row%
			TEXT_RECORD.col = col%
			TEXT_RECORD.rgbAttr1 = Cbyte(HiddenLocked)
			TEXT_RECORD.rgbAttr2 = Cbyte(CellFontUsed + CellFormat)
			TEXT_RECORD.rgbAttr3 = Cbyte(Alignment)
			
          'Put record header
			Put #FileNumber, , TEXT_RECORD
			
          'Then the actual string data
			For a = 1 To l%
				b = Asc(Mid$(st$, a, 1))
				Put #FileNumber, , b
			Next
			
			
		End Select
		
		WriteValue = 0   'return with no error
		
		Exit Function
		
Write_Error:
		
		Exit Function
	End Function
End Class


Sub Click(Source As Button)
	Dim OK As Integer	
	Dim retVal As Integer
	Dim ExcelWb As New ExcelFile
	OK = ExcelWb.CreateFile ( "c:\test.xls")
	
	'it is a good idea to set margins, fonts and column widths
	'prior to writing any text/numerics to the spreadsheet. These
    	'should come before setting the fonts.
	
	' set a default row height for the entire spreadsheet (1/20th of a point)
	OK = ExcelWB.SetDefaultRowHeight (14)
	
	' set margins for printing
	OK = ExcelWB.SetMargin (MARGIN_TOP, 1)   'set to 1 inches
	OK = ExcelWB.SetMargin (MARGIN_LEFT, 1 )
	OK = ExcelWB.SetMargin (MARGIN_RIGHT, 1 )
	OK = ExcelWB.SetMargin (MARGIN_BOTTOM, 1 )
	
	OK = ExcelWB.SetHeader("Dies ist eine Hallo Welt Tabelle") ''Header (visible when printing )
	OK = ExcelWB.SetFooter("hier kommt der Footer") ''Footer (visible when printing )
	
	' Up to 4 fonts can be specified for the spreadsheet. This is a
    	' limitation of the Excel 2.1 format. For each value written to the
    	' spreadsheet you can specify which font to use.
	OK = ExcelWB.SetFont("Arial", 10,  FORMAT_NONE) 'FONT_0 ( DEFAULT font for grid labels )
	OK = ExcelWB.SetFont("Verdana", 8,  FORMAT_BOLD) ''FONT_1
	OK = ExcelWB.SetFont("Verdana", 9,  FORMAT_ITALIC) 'FONT_2
	OK = ExcelWB.SetFont("Verdana", 10,  FORMAT_UNDERLINE + FORMAT_BOLD) 'FONT_3
	
	' print GRID Lines ?
	Call ExcelWB.PrintGridLines (True)
	
	'PROTECT the spreadsheet so any cells specified as LOCKED will not be
    	'overwritten. Also, all cells with HIDDEN set will hide their formulae.
    	'PROTECT does not use a password.
	'Call ExcelWb.ProtectSpreadsheet (True)
	
	'write some data
	' Text, centered unsing FONT_0
	OK = ExcelWb.WriteValue( TYPE_LABEL,  FONT_0,  CELL_ALIGN_CENTER, CELL_NORMAL, 1, 1, "X",  0 ) 	
	' Number
	OK = ExcelWb.WriteValue( TYPE_NUMBER,  FONT_0,  CELL_ALIGN_LEFT, CELL_NORMAL, 2, 1, "-123456789",  6 ) 
	' integer
	OK = ExcelWb.WriteValue( TYPE_INTEGER,  FONT_2,  CELL_ALIGN_RIGHT, CELL_NORMAL, 3, 1, "26789",  1 ) 
	' FILL a cell
	OK = ExcelWb.WriteValue( TYPE_LABEL,  FONT_3,  CELL_FILL, CELL_NORMAL, 6, 1, "X",  0 ) 
	' LOCK a cell
	OK = ExcelWb.WriteValue( TYPE_LABEL,  FONT_0,  CELL_NORMAL, CELL_LOCKED, 8, 1, "THIS cell is locked",  0 ) 
	
	OK = ExcelWb.CloseFile ()
End Sub