Solved

VB.net 2K5 dataset to Biff8 (MS Excel)

Posted on 2006-11-21
17
2,576 Views
Last Modified: 2008-03-10
I need to create an excel 97 or greater file from code. It must be biff8 (no html). I cannot buy anything and I must have open source (no references to dll's). I also cannot go automation.

I found a great VB6 example at:

http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=11898&lngWId=1

However I can't reach the author and I need to port it to VB25K. Can anyone provide me a VB.net example on how to create a true biff8 file without automation? I'd need to take a typed dataset and pass a table to a worksheet in the workbook.

Thanks for any help you can provide.

~TRF
0
Comment
Question by:TheRoyalFalcon
  • 10
  • 7
17 Comments
 
LVL 96

Accepted Solution

by:
Bob Learned earned 500 total points
ID: 17997731
ExcelFile class:

Imports System.IO

Friend Class ExcelFile

    '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.

    'Paul Squires, November 10, 2001
    'rambo2000@canada.com

    'Added default-cellformats: Dieter Hauk January 8, 2001 dieter.hauk@epost.de
    'Added default row height: Matthew Brewster November 9, 2001

    '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).
    Private Declare Sub CopyMemoryMKI Lib "Kernel32" Alias "RtlMoveMemory" _
            (ByVal hDest As String, ByRef hSource As Short, ByVal iBytes As Integer)

    'enum to handle the various types of values that can be written
    'to the excel file.
    Public Enum ValueTypes
        xlsinteger = 0
        xlsnumber = 1
        xlsText = 2
    End Enum

    'enum to hold cell alignment
    Public Enum CellAlignment
        xlsGeneralAlign = 0
        xlsLeftAlign = 1
        xlsCentreAlign = 2
        xlsrightAlign = 3
        xlsFillCell = 4
        xlsLeftBorder = 8
        xlsRightBorder = 16
        xlsTopBorder = 32
        xlsBottomBorder = 64
        xlsShaded = 128
    End Enum

    'enum to handle selecting the font for the cell
    Public Enum CellFont
        'used by rgbAttr2
        'bits 0-5 handle the *picture* formatting, not bold/underline etc...
        'bits 6-7 handle the font number
        xlsFont0 = 0
        xlsFont1 = 64
        xlsFont2 = 128
        xlsFont3 = 192
    End Enum

    Public Enum CellHiddenLocked
        'used by rgbAttr1
        'bits 0-5 must be zero
        'bit 6 locked/unlocked
        'bit 7 hidden/not hidden
        xlsNormal = 0
        xlsLocked = 64
        xlsHidden = 128
    End Enum

    'set up variables to hold the spreadsheet's layout
    Public Enum MarginTypes
        xlsLeftMargin = 38
        xlsRightMargin = 39
        xlsTopMargin = 40
        xlsBottomMargin = 41
    End Enum

    Public Enum FontFormatting
        'add these enums together. For example: xlsBold + xlsUnderline
        xlsNoFormat = 0
        xlsBold = 1
        xlsItalic = 2
        xlsUnderline = 4
        xlsStrikeout = 8
    End Enum

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

    Private Structure PASSWORD_RECORD
        Dim opcode As Short '47
        Dim length As Short 'len(password)
    End Structure

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

    Private Structure PROTECT_SPREADSHEET_RECORD
        Dim opcode As Short '18
        Dim length As Short '2
        Dim Protect As Short
    End Structure

    Private Structure FORMAT_COUNT_RECORD
        Dim opcode As Short '1f
        Dim length As Short '2
        Dim Count As Short
    End Structure

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

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

    'Beginning Of File record
    Private Structure BEG_FILE_RECORD
        Dim opcode As Short
        Dim length As Short
        Dim version As Short
        Dim ftype As Short
    End Structure

    'End Of File record
    Private Structure END_FILE_RECORD
        Dim opcode As Short
        Dim length As Short
    End Structure

    'true/false to print gridlines
    Private Structure PRINT_GRIDLINES_RECORD
        Dim opcode As Short
        Dim length As Short
        Dim PrintFlag As Short
    End Structure

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

    'Number record
    Private Structure tNumber
        Dim opcode As Short
        Dim length As Short
        Dim Row As Short
        Dim col As Short
        Dim rgbAttr1 As Byte
        Dim rgbAttr2 As Byte
        Dim rgbAttr3 As Byte
        Dim NumberValue As Double '8 Bytes
    End Structure

    'Label (Text) record
    Private Structure tText
        Dim opcode As Short
        Dim length As Short
        Dim Row As Short
        Dim col As Short
        Dim rgbAttr1 As Byte
        Dim rgbAttr2 As Byte
        Dim rgbAttr3 As Byte
        Dim TextLength As Byte
    End Structure

    Private Structure MARGIN_RECORD_LAYOUT
        Dim opcode As Short
        Dim length As Short
        Dim MarginValue As Double '8 bytes
    End Structure

    Private Structure HPAGE_BREAK_RECORD
        Dim opcode As Short
        Dim length As Short
        Dim NumPageBreaks As Short
    End Structure

    Private Structure DEF_ROWHEIGHT_RECORD
        Dim opcode As Short
        Dim length As Short
        Dim RowHeight As Short
    End Structure

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

    Private FileNumber As Short
    Private BEG_FILE_MARKER As BEG_FILE_RECORD
    Private END_FILE_MARKER As END_FILE_RECORD
    Private HORIZ_PAGE_BREAK As HPAGE_BREAK_RECORD

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

    Public Sub New()
        MyBase.New()

        'Set up default values for records
        'These should be the values that are the same for every record of these types

        BEG_FILE_MARKER.opcode = 9
        BEG_FILE_MARKER.length = 4
        BEG_FILE_MARKER.version = 2
        BEG_FILE_MARKER.ftype = 10

        END_FILE_MARKER.opcode = 10

    End Sub

    Public Sub CreateFile(ByVal FileName As String)

        If File.Exists(FileName) Then
            File.Delete(FileName)
        End If

        FileNumber = FreeFile()
        FileOpen(FileNumber, FileName, OpenMode.Binary)
        FilePut(FileNumber, BEG_FILE_MARKER) 'must always be written first

        Call WriteDefaultFormats()

        'create the Horizontal Page Break array
        ReDim HorizPageBreakRows(0)
        NumHorizPageBreaks = 0

    End Sub

    Public Sub CloseFile()

        'write the horizontal page breaks if necessary
        Dim lLoop1 As Integer
        Dim lLoop2 As Integer
        Dim lTemp As Integer

        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.
            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

            FilePut(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 As Integer = 1 To HorizPageBreakRows.GetUpperBound(0)
                FilePut(FileNumber, MKI(HorizPageBreakRows(x)))
            Next
        End If

        FilePut(FileNumber, END_FILE_MARKER)
        FileClose(FileNumber)

    End Sub

    Public Sub InsertHorizPageBreak(ByVal rowIndex As Integer)
        Dim Row As Short

        'the row and column values are written to the excel file as
        'unsigned integers. Therefore, must convert the longs to integer.
        If rowIndex > 32767 Then
            Row = CShort(rowIndex - 65536)
        Else
            Row = CShort(rowIndex) - 1 'rows/cols in Excel binary file are zero based
        End If

        NumHorizPageBreaks = NumHorizPageBreaks + 1
        ReDim Preserve HorizPageBreakRows(NumHorizPageBreaks)

        HorizPageBreakRows(NumHorizPageBreaks) = Row

    End Sub

    Public Sub WriteValue(ByVal ValueType As ValueTypes, ByVal CellFontUsed As CellFont, ByVal Alignment As CellAlignment, ByVal HiddenLocked As CellHiddenLocked, ByVal rowIndex As Integer, ByVal colIndex As Integer, ByVal value As Object, Optional ByVal CellFormat As Integer = 0)
        Dim st As String
        Dim col As Short
        Dim Row As Short

        'the row and column values are written to the excel file as
        'unsigned integers. Therefore, must convert the longs to integer.

        If rowIndex > 32767 Then
            Row = CShort(rowIndex - 65536)
        Else
            Row = CShort(rowIndex) - 1 'rows/cols in Excel binary file are zero based
        End If

        If colIndex > 32767 Then
            col = CShort(colIndex - 65536)
        Else
            col = CShort(colIndex) - 1 'rows/cols in Excel binary file are zero based
        End If

        Dim INTEGER_RECORD As tInteger
        Dim NUMBER_RECORD As tNumber
        Dim TEXT_RECORD As tText

        Select Case ValueType

            Case ValueTypes.xlsinteger
                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 = CShort(value)

                FilePut(FileNumber, INTEGER_RECORD)

            Case ValueTypes.xlsnumber
                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)

                If TypeOf value Is Date Then
                    NUMBER_RECORD.NumberValue = CType(value, Date).ToOADate()
                Else
                    NUMBER_RECORD.NumberValue = CDbl(value)
                End If

                FilePut(FileNumber, NUMBER_RECORD)

            Case ValueTypes.xlsText
                st = value.ToString()
                Dim l As Integer = 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
                FilePut(FileNumber, TEXT_RECORD)

                'Then the actual string data
                For a As Integer = 1 To l
                    Dim b As Byte = Asc(Mid(st, a, 1))
                    FilePut(FileNumber, b)
                Next

        End Select

    End Sub

    Public Sub SetMargin(ByVal Margin As MarginTypes, ByVal MarginValue As Double)

        '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

        FilePut(FileNumber, MarginRecord)

    End Sub

    Public Sub SetColumnWidth(ByVal FirstColumn As Byte, ByVal LastColumn As Byte, ByVal WidthValue As Short)

        Dim COLWIDTH As COLWIDTH_RECORD

        COLWIDTH.opcode = 36
        COLWIDTH.length = 4
        COLWIDTH.col1 = FirstColumn - 1
        COLWIDTH.col2 = LastColumn - 1
        COLWIDTH.ColumnWidth = WidthValue * 256 'values are specified as 1/256 of a character

        FilePut(FileNumber, COLWIDTH)

    End Sub

    Public Sub SetFont(ByVal FontName As String, ByVal FontHeight As Short, ByVal FontFormat As FontFormatting)
        Dim l As Short

        '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))

        FilePut(FileNumber, FONTNAME_RECORD)

        'Then the actual font name data

        For a As Integer = 1 To l
            Dim b As Byte = Asc(Mid(FontName, a, 1))
            FilePut(FileNumber, b)
        Next

    End Sub

    Public Sub SetHeader(ByVal HeaderText As String)
        Dim l As Short

        Dim HEADER_RECORD As HEADER_FOOTER_RECORD

        l = HeaderText.Length

        HEADER_RECORD.opcode = 20
        HEADER_RECORD.length = 1 + l
        HEADER_RECORD.TextLength = CByte(Len(HeaderText))

        FilePut(FileNumber, HEADER_RECORD)

        'Then the actual Header text

        For a As Integer = 1 To l
            Dim b As Byte = Asc(Mid(HeaderText, a, 1))
            FilePut(FileNumber, b)
        Next

    End Sub

    Public Sub SetFooter(ByVal FooterText As String)

        Dim FOOTER_RECORD As HEADER_FOOTER_RECORD

        Dim l As Integer = FooterText.Length

        FOOTER_RECORD.opcode = 21
        FOOTER_RECORD.length = 1 + l
        FOOTER_RECORD.TextLength = CByte(Len(FooterText))

        FilePut(FileNumber, FOOTER_RECORD)

        'Then the actual Header text

        For a As Integer = 1 To l
            Dim b As Byte = Asc(Mid(FooterText, a, 1))

            FilePut(FileNumber, b)
        Next

    End Sub

    Public Sub SetFilePassword(ByVal PasswordText As String)

        Dim FILE_PASSWORD_RECORD As PASSWORD_RECORD

        Dim l As Integer = PasswordText.Length

        FILE_PASSWORD_RECORD.opcode = 47
        FILE_PASSWORD_RECORD.length = l

        FilePut(FileNumber, FILE_PASSWORD_RECORD)

        'Then the actual Password text

        For a As Integer = 1 To l
            Dim b As Byte = Asc(Mid(PasswordText, a, 1))
            FilePut(FileNumber, b)
        Next

    End Sub

    Public WriteOnly Property PrintGridLines() As Boolean
        Set(ByVal Value As Boolean)
            Dim GRIDLINES_RECORD As PRINT_GRIDLINES_RECORD

            GRIDLINES_RECORD.opcode = 43
            GRIDLINES_RECORD.length = 2
            GRIDLINES_RECORD.PrintFlag = CByte(Value)

            FilePut(FileNumber, GRIDLINES_RECORD)

        End Set
    End Property

    Public WriteOnly Property ProtectSpreadsheet() As Boolean
        Set(ByVal Value As Boolean)
            Dim PROTECT_RECORD As PROTECT_SPREADSHEET_RECORD

            PROTECT_RECORD.opcode = 18
            PROTECT_RECORD.length = 2
            PROTECT_RECORD.Protect = CByte(Value)

            FilePut(FileNumber, PROTECT_RECORD)
        End Set
    End Property


    Public Sub WriteDefaultFormats()

        Dim cFORMAT_COUNT_RECORD As FORMAT_COUNT_RECORD
        Dim cFORMAT_RECORD As FORMAT_RECORD
        Dim aFormat(23) As String
        Dim l As Integer
        Dim q As String = 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 = &H1FS
        cFORMAT_COUNT_RECORD.length = &H2S
        cFORMAT_COUNT_RECORD.Count = CShort(UBound(aFormat))

        FilePut(FileNumber, cFORMAT_COUNT_RECORD)

        For lIndex As Integer = LBound(aFormat) To UBound(aFormat)
            l = aFormat(lIndex).Length
            cFORMAT_RECORD.opcode = &H1ES
            cFORMAT_RECORD.length = CShort(l + 1)
            cFORMAT_RECORD.FormatLenght = CShort(l)

            FilePut(FileNumber, cFORMAT_RECORD)

            'Then the actual format
            For a As Integer = 1 To l
                Dim b As Byte = Asc(Mid(aFormat(lIndex), a, 1))
                FilePut(FileNumber, b)
            Next
        Next lIndex

    End Sub

    Private Function MKI(ByVal x As Short) As String
        Dim temp As New String(" "c, 2)
        'used for writing integer array values to the disk file
        CopyMemoryMKI(temp, x, 2I)
        Return temp
    End Function

    Public Sub SetDefaultRowHeight(ByVal HeightValue As Short)

        '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

        FilePut(FileNumber, DEFHEIGHT)

    End Sub

    Public Sub SetRowHeight(ByVal lrow As Integer, ByVal HeightValue As Short)

        Dim Row As Short

        '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 = CShort(lrow - 65536)
        Else
            Row = CShort(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

        FilePut(FileNumber, ROWHEIGHTREC)

    End Sub

End Class
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 17997733
MainForm:

Friend Class MainForm
    Inherits System.Windows.Forms.Form

    Private Sub cmdCreate_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles cmdCreate.Click
        Dim FileName As String

        Dim myExcelFile As New ExcelFile

        Dim d As Date
        'Create the new spreadsheet
        FileName = ".\vbtest.xls" 'create spreadsheet in the current directory
        myExcelFile.CreateFile(FileName)

        'set a Password for the file. If set, the rest of the spreadsheet will
        'be encrypted. If a password is used it must immediately follow the
        'CreateFile method.
        'This is different then protecting the spreadsheet (see below).
        'NOTE: For some reason this function does not work. Excel will
        'recognize that the file is password protected, but entering the password
        'will not work. Also, the file is not encrypted. Therefore, do not use
        'this function until I can figure out why it doesn't work. There is not
        'much documentation on this function available.
        '.SetFilePassword "PAUL"



        'specify whether to print the gridlines or not
        'this should come before the setting of fonts and margins
        myExcelFile.PrintGridLines = False


        '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.

        myExcelFile.SetMargin(ExcelFile.MarginTypes.xlsTopMargin, 1.5) 'set to 1.5 inches
        myExcelFile.SetMargin(ExcelFile.MarginTypes.xlsLeftMargin, 1.5)
        myExcelFile.SetMargin(ExcelFile.MarginTypes.xlsRightMargin, 1.5)
        myExcelFile.SetMargin(ExcelFile.MarginTypes.xlsBottomMargin, 1.5)

        'to insert a Horizontal Page Break you need to specify the row just
        'after where you want the page break to occur. You can insert as many
        'page breaks as you wish (in any order).
        myExcelFile.InsertHorizPageBreak(10)
        myExcelFile.InsertHorizPageBreak(20)

        'set a default row height for the entire spreadsheet (1/20th of a point)
        myExcelFile.SetDefaultRowHeight(14)

        '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.

        myExcelFile.SetFont("Arial", 10, ExcelFile.FontFormatting.xlsNoFormat) 'font0
        myExcelFile.SetFont("Arial", 10, ExcelFile.FontFormatting.xlsBold) 'font1
        myExcelFile.SetFont("Arial", 10, ExcelFile.FontFormatting.xlsBold + ExcelFile.FontFormatting.xlsUnderline) 'font2
        myExcelFile.SetFont("Courier", 16, ExcelFile.FontFormatting.xlsBold + ExcelFile.FontFormatting.xlsItalic) 'font3


        'Column widths are specified in Excel as 1/256th of a character.
        myExcelFile.SetColumnWidth(1, 5, 18)

        'Set special row heights for row 1 and 2
        myExcelFile.SetRowHeight(1, 30)
        myExcelFile.SetRowHeight(2, 30)

        'set any header or footer that you want to print on
        'every page. This text will be centered at the top and/or
        'bottom of each page. The font will always be the font that
        'is specified as font0, therefore you should only set the
        'header/footer after specifying the fonts through SetFont.
        myExcelFile.SetHeader("BIFF 2.1 API")
        myExcelFile.SetFooter("Paul Squires - Excel BIFF Class")

        'write a normal left aligned string using font3 (Courier Italic)
        myExcelFile.WriteValue(ExcelFile.ValueTypes.xlsText, ExcelFile.CellFont.xlsFont3, ExcelFile.CellAlignment.xlsLeftAlign, ExcelFile.CellHiddenLocked.xlsNormal, 1, 1, "Quarterly Report")
        myExcelFile.WriteValue(ExcelFile.ValueTypes.xlsText, ExcelFile.CellFont.xlsFont1, ExcelFile.CellAlignment.xlsLeftAlign, ExcelFile.CellHiddenLocked.xlsNormal, 2, 1, "Cool Guy Corporation")

        'write some data to the spreadsheet
        'Use the default format #3 "#,##0" (refer to the WriteDefaultFormats function)
        'The WriteDefaultFormats function is compliments of Dieter Hauk in Germany.
        myExcelFile.WriteValue(ExcelFile.ValueTypes.xlsinteger, ExcelFile.CellFont.xlsFont0, ExcelFile.CellAlignment.xlsLeftAlign, ExcelFile.CellHiddenLocked.xlsNormal, 6, 1, 2000, 3)


        'write a cell with a shaded number with a bottom border
        myExcelFile.WriteValue(ExcelFile.ValueTypes.xlsnumber, ExcelFile.CellFont.xlsFont1, ExcelFile.CellAlignment.xlsrightAlign + ExcelFile.CellAlignment.xlsBottomBorder + ExcelFile.CellAlignment.xlsShaded, ExcelFile.CellHiddenLocked.xlsNormal, 7, 1, 12123.456, 4)

        'write a normal left aligned string using font2 (bold & underline)
        myExcelFile.WriteValue(ExcelFile.ValueTypes.xlsText, ExcelFile.CellFont.xlsFont2, ExcelFile.CellAlignment.xlsLeftAlign, ExcelFile.CellHiddenLocked.xlsNormal, 8, 1, "This is a test string")

        'write a locked cell. The cell will not be able to be overwritten, BUT you
        'must set the sheet PROTECTION to on before it will take effect!!!
        myExcelFile.WriteValue(ExcelFile.ValueTypes.xlsText, ExcelFile.CellFont.xlsFont3, ExcelFile.CellAlignment.xlsLeftAlign, ExcelFile.CellHiddenLocked.xlsLocked, 9, 1, "This cell is locked")

        'fill the cell with "F"'s
        myExcelFile.WriteValue(ExcelFile.ValueTypes.xlsText, ExcelFile.CellFont.xlsFont0, ExcelFile.CellAlignment.xlsFillCell, ExcelFile.CellHiddenLocked.xlsNormal, 10, 1, "F")

        'write a hidden cell to the spreadsheet. This only works for cells
        'that contain formulae. Text, Number, Integer value text can not be hidden
        'using this feature. It is included here for the sake of completeness.
        myExcelFile.WriteValue(ExcelFile.ValueTypes.xlsText, ExcelFile.CellFont.xlsFont0, ExcelFile.CellAlignment.xlsCentreAlign, ExcelFile.CellHiddenLocked.xlsHidden, 11, 1, "If this were a formula it would be hidden!")

        'write some dates to the file. NOTE: you need to write dates as xlsNumber
        d = New DateTime(2001, 1, 15)
        myExcelFile.WriteValue(ExcelFile.ValueTypes.xlsnumber, ExcelFile.CellFont.xlsFont0, ExcelFile.CellAlignment.xlsCentreAlign, ExcelFile.CellHiddenLocked.xlsNormal, 15, 1, d, 12)

        d = New DateTime(1999, 12, 31)
        myExcelFile.WriteValue(ExcelFile.ValueTypes.xlsnumber, ExcelFile.CellFont.xlsFont0, ExcelFile.CellAlignment.xlsCentreAlign, ExcelFile.CellHiddenLocked.xlsNormal, 16, 1, d, 12)

        d = New DateTime(2002, 1, 4)
        myExcelFile.WriteValue(ExcelFile.ValueTypes.xlsnumber, ExcelFile.CellFont.xlsFont0, ExcelFile.CellAlignment.xlsCentreAlign, ExcelFile.CellHiddenLocked.xlsNormal, 17, 1, d, 12)

        d = New DateTime(1998, 10, 21)
        myExcelFile.WriteValue(ExcelFile.ValueTypes.xlsnumber, ExcelFile.CellFont.xlsFont0, ExcelFile.CellAlignment.xlsCentreAlign, ExcelFile.CellHiddenLocked.xlsNormal, 18, 1, d, 12)

        '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.
        myExcelFile.ProtectSpreadsheet = True

        'Finally, close the spreadsheet
        myExcelFile.CloseFile()

        MsgBox("Excel BIFF Spreadsheet created." & vbCrLf & "Filename: " & FileName, MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "Excel Class")

    End Sub

    Private Sub cmdCancel_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles cmdCancel.Click
        Me.Close()
    End Sub

    Private Sub MainForm_Load(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles MyBase.Load

        ChDir(My.Application.Info.DirectoryPath)

    End Sub
End Class

Bob
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 17997737
MainForm.designer:

<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> Partial Class MainForm
#Region "Windows Form Designer generated code "
      <System.Diagnostics.DebuggerNonUserCode()> Public Sub New()
            MyBase.New()
            'This call is required by the Windows Form Designer.
            InitializeComponent()
      End Sub
      'Form overrides dispose to clean up the component list.
      <System.Diagnostics.DebuggerNonUserCode()> Protected Overloads Overrides Sub Dispose(ByVal Disposing As Boolean)
            If Disposing Then
                  If Not components Is Nothing Then
                        components.Dispose()
                  End If
            End If
            MyBase.Dispose(Disposing)
      End Sub
      'Required by the Windows Form Designer
      Private components As System.ComponentModel.IContainer
      Public ToolTip1 As System.Windows.Forms.ToolTip
      Public WithEvents cmdCancel As System.Windows.Forms.Button
      Public WithEvents cmdCreate As System.Windows.Forms.Button
      Public WithEvents Label15 As System.Windows.Forms.Label
      Public WithEvents Label14 As System.Windows.Forms.Label
      Public WithEvents Label13 As System.Windows.Forms.Label
      Public WithEvents Label12 As System.Windows.Forms.Label
      Public WithEvents Label11 As System.Windows.Forms.Label
      Public WithEvents Label10 As System.Windows.Forms.Label
      Public WithEvents Label3 As System.Windows.Forms.Label
      Public WithEvents Label4 As System.Windows.Forms.Label
      Public WithEvents Label5 As System.Windows.Forms.Label
      Public WithEvents Label6 As System.Windows.Forms.Label
      Public WithEvents Label8 As System.Windows.Forms.Label
      Public WithEvents Label9 As System.Windows.Forms.Label
      Public WithEvents Label7 As System.Windows.Forms.Label
      Public WithEvents Label2 As System.Windows.Forms.Label
      Public WithEvents Label1 As System.Windows.Forms.Label
      'NOTE: The following procedure is required by the Windows Form Designer
      'It can be modified using the Windows Form Designer.
      'Do not modify it using the code editor.
      <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.components = New System.ComponentModel.Container
        Dim resources As System.ComponentModel.ComponentResourceManager = New System.ComponentModel.ComponentResourceManager(GetType(MainForm))
        Me.ToolTip1 = New System.Windows.Forms.ToolTip(Me.components)
        Me.cmdCancel = New System.Windows.Forms.Button
        Me.cmdCreate = New System.Windows.Forms.Button
        Me.Label15 = New System.Windows.Forms.Label
        Me.Label14 = New System.Windows.Forms.Label
        Me.Label13 = New System.Windows.Forms.Label
        Me.Label12 = New System.Windows.Forms.Label
        Me.Label11 = New System.Windows.Forms.Label
        Me.Label10 = New System.Windows.Forms.Label
        Me.Label3 = New System.Windows.Forms.Label
        Me.Label4 = New System.Windows.Forms.Label
        Me.Label5 = New System.Windows.Forms.Label
        Me.Label6 = New System.Windows.Forms.Label
        Me.Label8 = New System.Windows.Forms.Label
        Me.Label9 = New System.Windows.Forms.Label
        Me.Label7 = New System.Windows.Forms.Label
        Me.Label2 = New System.Windows.Forms.Label
        Me.Label1 = New System.Windows.Forms.Label
        Me.SuspendLayout()
        '
        'cmdCancel
        '
        Me.cmdCancel.BackColor = System.Drawing.SystemColors.Control
        Me.cmdCancel.Cursor = System.Windows.Forms.Cursors.Default
        Me.cmdCancel.Font = New System.Drawing.Font("Arial", 8.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.cmdCancel.ForeColor = System.Drawing.SystemColors.ControlText
        Me.cmdCancel.Location = New System.Drawing.Point(201, 361)
        Me.cmdCancel.Name = "cmdCancel"
        Me.cmdCancel.RightToLeft = System.Windows.Forms.RightToLeft.No
        Me.cmdCancel.Size = New System.Drawing.Size(81, 33)
        Me.cmdCancel.TabIndex = 2
        Me.cmdCancel.Text = "Cancel"
        Me.cmdCancel.UseVisualStyleBackColor = False
        '
        'cmdCreate
        '
        Me.cmdCreate.BackColor = System.Drawing.SystemColors.Control
        Me.cmdCreate.Cursor = System.Windows.Forms.Cursors.Default
        Me.cmdCreate.Font = New System.Drawing.Font("Arial", 8.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.cmdCreate.ForeColor = System.Drawing.SystemColors.ControlText
        Me.cmdCreate.Location = New System.Drawing.Point(105, 361)
        Me.cmdCreate.Name = "cmdCreate"
        Me.cmdCreate.RightToLeft = System.Windows.Forms.RightToLeft.No
        Me.cmdCreate.Size = New System.Drawing.Size(81, 33)
        Me.cmdCreate.TabIndex = 1
        Me.cmdCreate.Text = "Create"
        Me.cmdCreate.UseVisualStyleBackColor = False
        '
        'Label15
        '
        Me.Label15.AutoSize = True
        Me.Label15.BackColor = System.Drawing.Color.Transparent
        Me.Label15.Cursor = System.Windows.Forms.Cursors.Default
        Me.Label15.Font = New System.Drawing.Font("Arial", 8.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.Label15.ForeColor = System.Drawing.SystemColors.ControlText
        Me.Label15.Location = New System.Drawing.Point(39, 307)
        Me.Label15.Name = "Label15"
        Me.Label15.RightToLeft = System.Windows.Forms.RightToLeft.No
        Me.Label15.Size = New System.Drawing.Size(231, 14)
        Me.Label15.TabIndex = 16
        Me.Label15.Text = "(11) Abiltiy to set the height of individual rows."
        '
        'Label14
        '
        Me.Label14.AutoSize = True
        Me.Label14.BackColor = System.Drawing.Color.Transparent
        Me.Label14.Cursor = System.Windows.Forms.Cursors.Default
        Me.Label14.Font = New System.Drawing.Font("Arial", 8.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.Label14.ForeColor = System.Drawing.SystemColors.ControlText
        Me.Label14.Location = New System.Drawing.Point(39, 289)
        Me.Label14.Name = "Label14"
        Me.Label14.RightToLeft = System.Windows.Forms.RightToLeft.No
        Me.Label14.Size = New System.Drawing.Size(332, 14)
        Me.Label14.TabIndex = 15
        Me.Label14.Text = "(10) Default (global) row heights that affect the entire spreadsheet."
        '
        'Label13
        '
        Me.Label13.AutoSize = True
        Me.Label13.BackColor = System.Drawing.Color.Transparent
        Me.Label13.Cursor = System.Windows.Forms.Cursors.Default
        Me.Label13.Font = New System.Drawing.Font("Arial", 8.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.Label13.ForeColor = System.Drawing.SystemColors.ControlText
        Me.Label13.Location = New System.Drawing.Point(43, 271)
        Me.Label13.Name = "Label13"
        Me.Label13.RightToLeft = System.Windows.Forms.RightToLeft.No
        Me.Label13.Size = New System.Drawing.Size(136, 14)
        Me.Label13.TabIndex = 14
        Me.Label13.Text = "(9) Horizontal Page Breaks"
        '
        'Label12
        '
        Me.Label12.AutoSize = True
        Me.Label12.BackColor = System.Drawing.Color.Transparent
        Me.Label12.Cursor = System.Windows.Forms.Cursors.Default
        Me.Label12.Font = New System.Drawing.Font("Arial", 8.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.Label12.ForeColor = System.Drawing.SystemColors.ControlText
        Me.Label12.Location = New System.Drawing.Point(43, 255)
        Me.Label12.Name = "Label12"
        Me.Label12.RightToLeft = System.Windows.Forms.RightToLeft.No
        Me.Label12.Size = New System.Drawing.Size(391, 14)
        Me.Label12.TabIndex = 13
        Me.Label12.Text = "(8) The spreadsheet can be password protected so all contents are encrypted."
        '
        'Label11
        '
        Me.Label11.AutoSize = True
        Me.Label11.BackColor = System.Drawing.Color.Transparent
        Me.Label11.Cursor = System.Windows.Forms.Cursors.Default
        Me.Label11.Font = New System.Drawing.Font("Arial", 8.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.Label11.ForeColor = System.Drawing.SystemColors.ControlText
        Me.Label11.Location = New System.Drawing.Point(43, 237)
        Me.Label11.Name = "Label11"
        Me.Label11.RightToLeft = System.Windows.Forms.RightToLeft.No
        Me.Label11.Size = New System.Drawing.Size(236, 14)
        Me.Label11.TabIndex = 12
        Me.Label11.Text = "(7) You can specify whether to print GridLines."
        '
        'Label10
        '
        Me.Label10.AutoSize = True
        Me.Label10.BackColor = System.Drawing.Color.Transparent
        Me.Label10.Cursor = System.Windows.Forms.Cursors.Default
        Me.Label10.Font = New System.Drawing.Font("Arial", 8.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.Label10.ForeColor = System.Drawing.SystemColors.ControlText
        Me.Label10.Location = New System.Drawing.Point(43, 219)
        Me.Label10.Name = "Label10"
        Me.Label10.RightToLeft = System.Windows.Forms.RightToLeft.No
        Me.Label10.Size = New System.Drawing.Size(366, 14)
        Me.Label10.TabIndex = 11
        Me.Label10.Text = "(6) You can specify Headers and Footers to appear on each printed page."
        '
        'Label3
        '
        Me.Label3.AutoSize = True
        Me.Label3.BackColor = System.Drawing.Color.Transparent
        Me.Label3.Cursor = System.Windows.Forms.Cursors.Default
        Me.Label3.Font = New System.Drawing.Font("Arial", 8.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.Label3.ForeColor = System.Drawing.SystemColors.ControlText
        Me.Label3.Location = New System.Drawing.Point(43, 104)
        Me.Label3.Name = "Label3"
        Me.Label3.RightToLeft = System.Windows.Forms.RightToLeft.No
        Me.Label3.Size = New System.Drawing.Size(212, 14)
        Me.Label3.TabIndex = 10
        Me.Label3.Text = "(1) Set the spreadsheet margins in inches."
        '
        'Label4
        '
        Me.Label4.AutoSize = True
        Me.Label4.BackColor = System.Drawing.Color.Transparent
        Me.Label4.Cursor = System.Windows.Forms.Cursors.Default
        Me.Label4.Font = New System.Drawing.Font("Arial", 8.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.Label4.ForeColor = System.Drawing.SystemColors.ControlText
        Me.Label4.Location = New System.Drawing.Point(43, 122)
        Me.Label4.Name = "Label4"
        Me.Label4.RightToLeft = System.Windows.Forms.RightToLeft.No
        Me.Label4.Size = New System.Drawing.Size(229, 14)
        Me.Label4.TabIndex = 9
        Me.Label4.Text = "(2) Set individual or a range of column widths."
        '
        'Label5
        '
        Me.Label5.BackColor = System.Drawing.Color.Transparent
        Me.Label5.Cursor = System.Windows.Forms.Cursors.Default
        Me.Label5.Font = New System.Drawing.Font("Arial", 8.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.Label5.ForeColor = System.Drawing.SystemColors.ControlText
        Me.Label5.Location = New System.Drawing.Point(43, 140)
        Me.Label5.Name = "Label5"
        Me.Label5.RightToLeft = System.Windows.Forms.RightToLeft.No
        Me.Label5.Size = New System.Drawing.Size(349, 46)
        Me.Label5.TabIndex = 8
        Me.Label5.Text = "(3) Maximum rows allowed is 65536 which is campatible with Office 97. In order to" & _
            " maintain compatibility with Excel 2.1 the maximum rows you should use is 16384." & _
            ""
        '
        'Label6
        '
        Me.Label6.BackColor = System.Drawing.Color.Transparent
        Me.Label6.Cursor = System.Windows.Forms.Cursors.Default
        Me.Label6.Font = New System.Drawing.Font("Arial", 8.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.Label6.ForeColor = System.Drawing.SystemColors.ControlText
        Me.Label6.Location = New System.Drawing.Point(42, 188)
        Me.Label6.Name = "Label6"
        Me.Label6.RightToLeft = System.Windows.Forms.RightToLeft.No
        Me.Label6.Size = New System.Drawing.Size(349, 31)
        Me.Label6.TabIndex = 7
        Me.Label6.Text = "(4) You can specify the font, alignment and formatting for individual cells."
        '
        'Label8
        '
        Me.Label8.BackColor = System.Drawing.SystemColors.Control
        Me.Label8.Cursor = System.Windows.Forms.Cursors.Default
        Me.Label8.Font = New System.Drawing.Font("Arial", 8.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.Label8.ForeColor = System.Drawing.SystemColors.ControlText
        Me.Label8.Location = New System.Drawing.Point(43, 201)
        Me.Label8.Name = "Label8"
        Me.Label8.RightToLeft = System.Windows.Forms.RightToLeft.No
        Me.Label8.Size = New System.Drawing.Size(343, 25)
        Me.Label8.TabIndex = 6
        Me.Label8.Text = "(5) Handles cell borders, shading, locking and hiding."
        '
        'Label9
        '
        Me.Label9.BackColor = System.Drawing.SystemColors.Control
        Me.Label9.Cursor = System.Windows.Forms.Cursors.Default
        Me.Label9.Font = New System.Drawing.Font("Arial", 8.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.Label9.ForeColor = System.Drawing.SystemColors.ControlText
        Me.Label9.Location = New System.Drawing.Point(281, 428)
        Me.Label9.Name = "Label9"
        Me.Label9.RightToLeft = System.Windows.Forms.RightToLeft.No
        Me.Label9.Size = New System.Drawing.Size(133, 13)
        Me.Label9.TabIndex = 5
        Me.Label9.Text = "rambo2000@canada.com"
        Me.Label9.TextAlign = System.Drawing.ContentAlignment.TopRight
        '
        'Label7
        '
        Me.Label7.BackColor = System.Drawing.SystemColors.Control
        Me.Label7.Cursor = System.Windows.Forms.Cursors.Default
        Me.Label7.Font = New System.Drawing.Font("Arial", 8.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.Label7.ForeColor = System.Drawing.SystemColors.ControlText
        Me.Label7.Location = New System.Drawing.Point(7, 428)
        Me.Label7.Name = "Label7"
        Me.Label7.RightToLeft = System.Windows.Forms.RightToLeft.No
        Me.Label7.Size = New System.Drawing.Size(185, 13)
        Me.Label7.TabIndex = 4
        Me.Label7.Text = "Paul Squires November 10, 2001"
        '
        'Label2
        '
        Me.Label2.BackColor = System.Drawing.SystemColors.Control
        Me.Label2.Cursor = System.Windows.Forms.Cursors.Default
        Me.Label2.Font = New System.Drawing.Font("Arial", 8.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.Label2.ForeColor = System.Drawing.SystemColors.ControlText
        Me.Label2.Location = New System.Drawing.Point(10, 79)
        Me.Label2.Name = "Label2"
        Me.Label2.RightToLeft = System.Windows.Forms.RightToLeft.No
        Me.Label2.Size = New System.Drawing.Size(376, 25)
        Me.Label2.TabIndex = 3
        Me.Label2.Text = "The Excel class is able to:"
        '
        'Label1
        '
        Me.Label1.BackColor = System.Drawing.SystemColors.Control
        Me.Label1.Cursor = System.Windows.Forms.Cursors.Default
        Me.Label1.Font = New System.Drawing.Font("Arial", 8.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.Label1.ForeColor = System.Drawing.SystemColors.ControlText
        Me.Label1.Location = New System.Drawing.Point(10, 9)
        Me.Label1.Name = "Label1"
        Me.Label1.RightToLeft = System.Windows.Forms.RightToLeft.No
        Me.Label1.Size = New System.Drawing.Size(403, 58)
        Me.Label1.TabIndex = 0
        Me.Label1.Text = resources.GetString("Label1.Text")
        '
        'MainForm
        '
        Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 14.0!)
        Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
        Me.BackColor = System.Drawing.SystemColors.Control
        Me.ClientSize = New System.Drawing.Size(458, 448)
        Me.Controls.Add(Me.cmdCancel)
        Me.Controls.Add(Me.cmdCreate)
        Me.Controls.Add(Me.Label15)
        Me.Controls.Add(Me.Label14)
        Me.Controls.Add(Me.Label13)
        Me.Controls.Add(Me.Label12)
        Me.Controls.Add(Me.Label11)
        Me.Controls.Add(Me.Label10)
        Me.Controls.Add(Me.Label3)
        Me.Controls.Add(Me.Label4)
        Me.Controls.Add(Me.Label5)
        Me.Controls.Add(Me.Label6)
        Me.Controls.Add(Me.Label8)
        Me.Controls.Add(Me.Label9)
        Me.Controls.Add(Me.Label7)
        Me.Controls.Add(Me.Label2)
        Me.Controls.Add(Me.Label1)
        Me.Cursor = System.Windows.Forms.Cursors.Default
        Me.Font = New System.Drawing.Font("Arial", 8.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedSingle
        Me.Location = New System.Drawing.Point(3, 22)
        Me.MaximizeBox = False
        Me.MinimizeBox = False
        Me.Name = "MainForm"
        Me.RightToLeft = System.Windows.Forms.RightToLeft.No
        Me.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen
        Me.Text = "Excel Class Demo"
        Me.ResumeLayout(False)
        Me.PerformLayout()

    End Sub
#End Region
End Class

Bob
0
 
LVL 1

Author Comment

by:TheRoyalFalcon
ID: 18012557
Hi Bob,

Thanks for taking the time to reply to this. I really appreciate it. I had let VS2k5 convert the project earlier in the day before posting this. Once I addressed a couple issues like the Any type it seemed to work okay. However I see you flushed it out even more. THANK YOU.

Big issue, I didn't notice on the above linked peice of code, but it is for Excel 2.1. Ouch! When you open a file created using the above code, it is formatted Excel 2.1. It looks fine, but Excel97 through 2K3 prompts to convert it on close and tells the user that it is currently Excel 2.1. My boss and the LOB would flip if they saw that (even if I did explain that it is irelevant, BIFF2.1 to BIFF11 are all viewable with Excel97 or greater). A big portion of the LOB and some external customers are still on Office97. ITS is trying to move them to 2K3 (I'm pushing for 2K7) but even so some of our externals are staying 97 for a while so I have to stick with BIFF8 even though I'd rather go the 2K2 xml route.

I looked over the file format for BIFF8 at www.openoffice.org hoping I could "upgrade" the above code from 2.1 to 8. However, writing binary files isn't something I've had to do in years and in my development window I doubt I could get it working before my deadline.

There is no chance I could pull this off without days of work, but you have jedi programming skills where I'm more a strom trooper with a little bigger than average blaster. Is there any chance you can take the code above and modify it to produce BIFF8 using the specs at: http://sc.openoffice.org/excelfileformat.pdf

If not I understand it could be really time consuming.

On a side alternative, I also found I can take a report created for the report viewer and render it to Excel. It works great for a simple single sheet Excel file. However, it turns off the gridelines (which I have to have on by default). I'm using this code example:

ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.VisualStudio.v80.en/rs_vsrpts/html/35357cde-4d27-495b-aefb-a6d500f4fa3d.htm

and tweaked it to follow this C# example:
http://www.developer.com/net/net/article.php/11087_3639346_2

I'll post each in a moment. Any idea how I can take this and get the grid lines on when the file is first opened. Although I'd rather write the BIFF8 file from scratch (because I can create worksheets per book, greater control over formatting, and misc tasks) I'd go this route if I could get the gridlines to show by default because it is so easy to implement.

Any thoughts on this would be greatly appreciated. Thanks again, I do appreciate your help!

steve

0
 
LVL 1

Author Comment

by:TheRoyalFalcon
ID: 18012559
'ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.VisualStudio.v80.en/rs_vsrpts/html/35357cde-4d27-495b-aefb-a6d500f4fa3d.htm

Imports System.IO
Imports System.Data
Imports System.Text
Imports System.Drawing.Imaging
Imports System.Drawing.Printing
Imports System.Collections.Generic
Imports Microsoft.Reporting.WinForms

Public Class Demo
    Implements IDisposable

    private m_currentPageIndex As Integer
    private m_streams As IList(Of Stream)

    Private Function LoadSalesData() As DataTable
        Dim dataSet As New DataSet()
        dataSet.ReadXml("c:\My Reports\data.xml")
        Return dataSet.Tables(0)
    End Function

    Private Function CreateStream(name As String,_
      fileNameExtension As String, _
     encoding As Encoding, mimeType As String, willSeek As Boolean)
As Stream
        Dim stream As Stream = _
            New FileStream("c:\My Reports\" + _
             name + "." + fileNameExtension, FileMode.Create)
        m_streams.Add(stream)
        Return stream
    End Function

    Private Sub Export(report As LocalReport)
        Dim deviceInfo As string = _
          "<DeviceInfo>" + _
          "  <OutputFormat>EMF</OutputFormat>" + _
          "  <PageWidth>8.5in</PageWidth>" + _
          "  <PageHeight>11in</PageHeight>" + _
          "  <MarginTop>0.25in</MarginTop>" + _
          "  <MarginLeft>0.25in</MarginLeft>" + _
          "  <MarginRight>0.25in</MarginRight>" + _
          "  <MarginBottom>0.25in</MarginBottom>" + _
          "</DeviceInfo>"
        Dim warnings() As Warning = Nothing
        m_streams = New List(Of Stream)()
        report.Render("Image", deviceInfo, AddressOf CreateStream,
warnings)

        Dim stream As Stream
        For Each stream In m_streams
            stream.Position = 0
        Next
    End Sub

    Private Sub PrintPage(sender As Object, ev As
PrintPageEventArgs)
        Dim pageImage As New Metafile(m_streams(m_currentPageIndex))
        ev.Graphics.DrawImage(pageImage, ev.PageBounds)
     
        m_currentPageIndex += 1
        ev.HasMorePages = (m_currentPageIndex < m_streams.Count)
    End Sub

    Private Sub Print()
        Const printerName As String = "Microsoft Office Document
Image Writer"

        If m_streams Is Nothing Or m_streams.Count = 0 Then
            Return
        End If

        Dim printDoc As New PrintDocument()
        printDoc.PrinterSettings.PrinterName = printerName
        If Not printDoc.PrinterSettings.IsValid Then
            Dim msg As String = String.Format("Can't find printer
""{0}"".", printerName)
            Console.WriteLine(msg)
            Return
        End If
        AddHandler printDoc.PrintPage, AddressOf PrintPage
        printDoc.Print()
    End Sub

    Private Sub Run()
        Dim report As LocalReport = new LocalReport()
        report.ReportPath = "c:\My Reports\Report.rdlc"
        report.DataSources.Add(new ReportDataSource("Sales",
LoadSalesData()))

        Export(report)

        m_currentPageIndex = 0
        Print()
    End Sub

    Public Overloads Sub Dispose() Implements IDisposable.Dispose
        If Not (m_streams Is Nothing) Then
            Dim stream As Stream
            For Each stream In m_streams
               stream.Close()
            Next
            m_streams = Nothing
        End If
    End Sub

    Public Shared Sub Main(args as string())
        Using demo As Demo = new Demo()
            demo.Run()
        End Using
    End Sub
End Class
0
 
LVL 1

Author Comment

by:TheRoyalFalcon
ID: 18012562
'http://www.developer.com/net/net/article.php/11087_3639346_2

string mimeType          = null;
string encoding          = null;
string fileNameExtension = null;
string[] streams         = null;
Microsoft.Reporting.WinForms.Warning[] warnings = null;
string deviceInfo =
   "<DeviceInfo>
       <SimplePageHeaders>True</SimplePageHeaders>
    </DeviceInfo>";
Microsoft.Reporting.WinForms.LocalReport localReport
   = new Microsoft.Reporting.WinForms.LocalReport();
localReport.ReportPath =
   @"..\..\Local Report Store Information.rdlc";
this.Adventure.EnforceConstraints = false;
localReport.DataSources.Add(new Microsoft.Reporting.WinForms.
   ReportDataSource("Sales", GetData()));
byte[] bytes = reportViewer1.LocalReport.Render("Excel",
   deviceInfo, out mimeType, out encoding, out fileNameExtension,
   out streams, out warnings);
System.IO.FileStream fileStream =
   new System.IO.FileStream(@"c:\ExcelExportSample.xls",
                            System.IO.FileMode.Create);
fileStream.Write(bytes, 0, bytes.Length);
fileStream.Close();

0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 18018539
We would have to find out what the binary differences are for Biff-8.

Bob
0
 
LVL 1

Author Comment

by:TheRoyalFalcon
ID: 18020202
Trying to read through the 222 page document outlining the file format at the moment. But I doubt I could get this working, as 2.1 and 8 are very different.

I did, however, find a VB6 Biff8 project at the following location:

http://www.freevbcode.com/ShowCode.asp?ID=6887

However, I'm running into issues getting it converted right now. If you get a moment and can look at it, could you let me know if you can convert it? I will post back if I get it worked out.

Thanks in advance.

~steve
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 1

Author Comment

by:TheRoyalFalcon
ID: 18036905
I got something working using the reportviewer control. Very powerful, but very little documentation. I will post back what I did shortly. Thanks.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 18044603
ReportViewer control?

Bob
0
 
LVL 1

Author Comment

by:TheRoyalFalcon
ID: 18047349
There is a reporting control, the reportviewer control, that MS has in VS2K5 (not sure if it was in prior VS or not). It is used to tie into SQL reporting services and server reports. However it also can be configured to display reports configured against a dataset using a local reports definition. It took me a while but I found out how to make it export reports to excel via code. The neat thing is it allows you to even generate spearate worksheets within the workbook. The output also is in true biff8 format.

Because the query pulls years worth of data and can run for serveral minutes I had to thread this off. So I had my background worker component fire off the function creating the report.

'Here is my background worker do work call:
    Private Sub BackgroundWorker1_DoWork(ByVal sender As System.Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker1.DoWork

        Try

            Dim bw As BackgroundWorker = CType(sender, BackgroundWorker)

            'Dim arg As Integer = Fix(e.Argument)
            Dim x As Object

            x = e.Argument
            e.Result = fxExport02(x(0), x(1), bw)

        Catch excpE As Exception

            Dim excp As clsException = New clsException(excpE)
            MessageBox.Show(excp.strSimpleMsg, excp.strSimpleTitle)
            excp = Nothing

        Finally

        End Try

    End Sub


'Here is the code for the fx:

    Private Function fxExport02(ByVal blnFixed As Boolean, ByVal intCustId As Integer, ByVal bw As BackgroundWorker) As Boolean

        Try

            Dim dtTicklerDate As Date
            Dim intSequenceNumber As Integer
            Dim strCustomer As String
            Dim strDateRange As String

            Dim strSQL As String
            strSQL = "SELECT * FROM tblClient WHERE CustomerId = " & intCustId

            Dim blnReportFixed As Boolean
            Dim strReportType As String

            If blnFixed = True Then
                blnReportFixed = True
                strReportType = "Fixed"
            Else
                blnReportFixed = False
                strReportType = "Equity"
            End If

            Dim cnn As New SqlConnection(objSystem.ConnectionStrSQL.ToString)

            cnn.Open()

            Dim cmd As New SqlCommand(strSQL, cnn)
            Dim sdr As SqlDataReader = cmd.ExecuteReader

            If sdr.HasRows = False Then

                Exit Try

            Else
                sdr.Read()
                dtTicklerDate = sdr("TicklerDate")
                intSequenceNumber = sdr("SequenceNumber")
                strCustomer = sdr("Name")

            End If

            sdr.Close()

            Me.DsTCobra01.tblExlExportAll.Clear()
            Me.TblExlExportAllTableAdapter.Connection = cnn

            Me.TblExlExportAllTableAdapter.fill(Me.DsTCobra01.tblExlExportAll, _
            blnReportFixed, dtTicklerDate, intSequenceNumber, Me.dtpDateBegin.Value, dtpDateEnd.Value)

            cnn.Close()

            strDateRange = Me.dtpDateBegin.Value.ToShortDateString & " - " & _
            Me.dtpDateEnd.Value.ToShortDateString

            Dim params(1) As ReportParameter

            params(0) = New ReportParameter("strCustomer", strCustomer & " " & strReportType, True)
            params(1) = New ReportParameter("strDateRange", strDateRange, True)

            Me.ReportViewer1.LocalReport.SetParameters(params)

            Me.ReportViewer1.LocalReport.Refresh()

            Dim strDeviceInfo As String = "<DeviceInfo><SimplePageHeaders>True</SimplePageHeaders></DeviceInfo>"
            Dim mimeType As String = vbNullString
            Dim encoding As String = vbNullString
            Dim fileNameExtension As String = vbNullString
            Dim streams As String() = Nothing
            Dim warnings As Microsoft.Reporting.WinForms.Warning() = Nothing
            Dim bytes As Byte()

            bytes = Me.ReportViewer1.LocalReport.Render("Excel", strDeviceInfo, _
            mimeType, encoding, fileNameExtension, streams, warnings)

            Dim strPath As String
            strPath = Me.FolderBrowserDialog1.SelectedPath.ToString

            strPath = strPath.TrimEnd("\")

            Dim strFile As String
            strFile = strCustomer.Trim("&") & "_" & strReportType & "_" & _
            Format(Now(), "yyyyMMdd").ToString & "_" & Format(Now(), "hhmmss").ToString & ".xls"

            Dim fileStream As New System.IO.FileStream(strPath & "\" & strFile, System.IO.FileMode.Create)

            fileStream.Write(bytes, 0, bytes.Length)
            fileStream.Close()

        Catch excpE As Exception
            Dim excp As clsException = New clsException(excpE)
            MessageBox.Show(excp.strSimpleMsg, excp.strSimpleTitle)
            excp = Nothing

        Finally

        End Try
    End Function


------

Nothing to fancy, but it's giving me a true biff8 file complete with sheets and basically all I had to do was create one local report pointed to the dataset table. From there I let the report do all the work.

I had approached the LOB about the 2.1 format, but they were concerned (and understandably) how external customers would react to the old format prompt. With this soln they are getting a true biff8 which is great and I have a lot less programming to create one. Of course I'm not sure when I will need to do something like this again, but next time it will require a fraction of the time.

Thanks again and have a good one!
~steve
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 18047713
Does that handle BIFF8X?  Or, even the 2007 format?

Bob
0
 
LVL 1

Author Comment

by:TheRoyalFalcon
ID: 18048522
What little documentation I could find on the topic was taken from here:

http://msdn2.microsoft.com/en-us/library/ms159836.aspx

It looks to be BIFF8 and not 8X but I’m not sure. I’m using 2K3 and my target group is 50/50 97 and 2K3. No one is getting prompts to save as . . . but since Excel 11 can open BIFF8 and 97 can open BIFF8X (less the new features) I’m not sure. However, with the way the XLS file is render via the reporting tool (based off a basic report definition) I’m not sure how one would tap into the Excel XP extended features anyway. It supports basic formatting, formulas, and structure. The only thing I could think to do to test it would be to identify a XP or 2003 specific feature and try to pass it to the report. If I get time soon I will try it out and see what happens. I’ll let you know what I see.

Thanks again.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 18076428
Did you make any headway?

Bob
0
 
LVL 1

Author Comment

by:TheRoyalFalcon
ID: 18077325
Hi Bob,

I’ve been out the last couple days. My wife, son, and I are all sick. I just got back to work today.

I got the Excel output piece working using the reportviewer. It’s very slick. I created a report based on the dataset and then used the code above to save it in Excel format (with multiple worksheets too boot). It worked great and seems to be flexible for basic Excel files (which is all I should ever need). The nice thing, it is you can create a report, and hence an excel BIFF8 export, fast.

I couldn’t find a good XP-function to toss into a report and output to excel. So all I can say for sure is that it is BIFF8, but I’m not sure if it is BIFF8X or not.

Thanks again.

~steve
0
 
LVL 1

Author Comment

by:TheRoyalFalcon
ID: 18142627
For anyone looking to create a BIFF8 file from VB.net 2005, and you don't need something too elaborate, you should use the reportviewer control. Follow the code above and save yourself alot of work. There is no need to write a file from scratch unless you have really special needs.

If you're looking for a free "write the file from scratch" solution, at the time of this posting, you aren't going to find a good one. I know I looked for weeks. Save yourself sometime and let the control do it for you. Put a local report def together and you're good to go (formatting and all).

Good luck.

~steve
0
 
LVL 1

Author Comment

by:TheRoyalFalcon
ID: 18142630
Bob thanks for your help. I really appreciate it.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now