Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

Solved 2K5 dataset to Biff8 (MS Excel)

Posted on 2006-11-21
Medium Priority
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:

However I can't reach the author and I need to port it to VB25K. Can anyone provide me a 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.

Question by:TheRoyalFalcon
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 7
LVL 96

Accepted Solution

Bob Learned earned 2000 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

    'Added default-cellformats: Dieter Hauk January 8, 2001
    '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

        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

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

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

        FilePut(FileNumber, END_FILE_MARKER)

    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)
            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)
            Row = CShort(rowIndex) - 1 'rows/cols in Excel binary file are zero based
        End If

        If colIndex > 32767 Then
            col = CShort(colIndex - 65536)
            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()
                    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)

        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)


        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)


        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)

    End Sub

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


        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)

    End Sub

    Public Sub SetFooter(ByVal FooterText As String)


        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)

    End Sub

    Public Sub SetFilePassword(ByVal PasswordText As String)


        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)

    End Sub

    Public WriteOnly Property PrintGridLines() As Boolean
        Set(ByVal Value As Boolean)

            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)

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


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


        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
LVL 96

Expert Comment

by:Bob Learned
ID: 17997733

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

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

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

        '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

        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
    End Sub

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


    End Sub
End Class

LVL 96

Expert Comment

by:Bob Learned
ID: 17997737

<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> Partial Class MainForm
#Region "Windows Form Designer generated code "
      <System.Diagnostics.DebuggerNonUserCode()> Public Sub New()
            'This call is required by the Windows Form Designer.
      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
                  End If
            End If
      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.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
        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
        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."
        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."
        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"
        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."
        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."
        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."
        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."
        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."
        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." & _
        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."
        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."
        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 = ""
        Me.Label9.TextAlign = System.Drawing.ContentAlignment.TopRight
        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"
        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:"
        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")
        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.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"

    End Sub
#End Region
End Class

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


Author Comment

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

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:


and tweaked it to follow this C# example:

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!



Author Comment

ID: 18012559

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)
        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>" + _
        Dim warnings() As Warning = Nothing
        m_streams = New List(Of Stream)()
        report.Render("Image", deviceInfo, AddressOf CreateStream,

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

    Private Sub PrintPage(sender As Object, ev As
        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
        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)
        End If
        AddHandler printDoc.PrintPage, AddressOf PrintPage
    End Sub

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


        m_currentPageIndex = 0
    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
            m_streams = Nothing
        End If
    End Sub

    Public Shared Sub Main(args as string())
        Using demo As Demo = new Demo()
        End Using
    End Sub
End Class

Author Comment

ID: 18012562

string mimeType          = null;
string encoding          = null;
string fileNameExtension = null;
string[] streams         = null;
Microsoft.Reporting.WinForms.Warning[] warnings = null;
string 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",
fileStream.Write(bytes, 0, bytes.Length);

LVL 96

Expert Comment

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


Author Comment

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:

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.


Author Comment

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

Expert Comment

by:Bob Learned
ID: 18044603
ReportViewer control?


Author Comment

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


            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


        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


            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"
                blnReportFixed = False
                strReportType = "Equity"
            End If

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


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

            If sdr.HasRows = False Then

                Exit Try

                dtTicklerDate = sdr("TicklerDate")
                intSequenceNumber = sdr("SequenceNumber")
                strCustomer = sdr("Name")

            End If


            Me.TblExlExportAllTableAdapter.Connection = cnn

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


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

            Dim params(1) As ReportParameter

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



            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)

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


        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!
LVL 96

Expert Comment

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


Author Comment

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

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

Expert Comment

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


Author Comment

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.


Author Comment

ID: 18142627
For anyone looking to create a BIFF8 file from 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.


Author Comment

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

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

722 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