BIFF 2.1 specifications to write (old) Excel files

Share your advanced PureBasic knowledge/code with the community.
Post Reply
User avatar
pdwyer
Addict
Addict
Posts: 2813
Joined: Tue May 08, 2007 1:27 pm
Location: Chiba, Japan

BIFF 2.1 specifications to write (old) Excel files

Post by pdwyer »

I got permission from Paul Squires to port his powerbasic code to Purebasic, He ported from VB and I noticed that others here have noticed this in the past. ( http://www.purebasic.fr/english/viewtop ... =excel+com ) I used it a bit as a poor-mans "zero dependancy" excel report tool in powerbasic and so I ported it since I don't use that product anymore.

Some might like it, I'm sure some will be annoyed by it's lack of features too :P (so close, yet so far away :D)

I've left Paul Squires original comments in. A following post will be a ported example with comments on how to use this include file.

(No COM, DLLs, automation or installed copy of excel required)

(Don't try to create multple files at once, especially in different threads, it can't handle that) Let me know if you find any (more) bugs

Code: Select all

;Excel.wnc - Include file for BIFF 2.1 specifications to write Excel files.
;
;Converted from VB source to PowerBasic, November 2001.
;Paul Squires (2001) support@planetsquires.com (Freeware)
;
;Copyright (c) 2001 by Paul Squires.
;Although this code is available for free, the author retains the copyright, which means that you 
;cannot do anything with it that is not expressly allowed by the author. In general terms, the author
;would allow the programmer to incorporate the code into their applications. Selling the code by 
;itself is prohibited. 
;
;
;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
;support@planetsquires.com


;constants to hold cell alignment
    #xlsGeneralAlign = 0
    #xlsLeftAlign = 1
    #xlsCentreAlign = 2
    #xlsRightAlign = 3
    #xlsFillCell = 4
    #xlsLeftBorder = 8
    #xlsRightBorder = 16
    #xlsTopBorder = 32
    #xlsBottomBorder = 64
    #xlsShaded = 128

;constants to handle selecting the font for the cell
    ;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

    ;used by rgbAttr1
    ;bits 0-5 must be zero
    ;bit 6 locked/unlocked
    ;bit 7 hidden/not hidden
    #xlsCellNormal = 0
    #xlsCellLocked = 64
    #xlsCellHidden = 128


;set up variables to hold the spreadsheet;s layout
    #xlsLeftMargin = 38
    #xlsRightMargin = 39
    #xlsTopMargin = 40
    #xlsBottomMargin = 41


   ;add these enums together. For example: xlsBold + xlsUnderline
   #xlsNoFormat = 0
   #xlsBold = 1
   #xlsItalic = 2
   #xlsUnderline = 4
   #xlsStrikeout = 8


Structure FONT_RECORD
   opcode.w  ;49
   length.w  ;5+len(fontname)
   FontHeight.w
   
   ;bit0 bold, bit1 italic, bit2 underline, bit3 strikeout, bit4-7 reserved
   FontAttributes1.b
   FontAttributes2.b  ;reserved - always 0
   FontNameLength.b
EndStructure


Structure PASSWORD_RECORD
   opcode.w  ;47
   length.w  ;len(password)
EndStructure


Structure HEADER_FOOTER_RECORD
   opcode.w  ;20 Header, 21 Footer
   length.w  ;1+len(text)
   TextLength.b
EndStructure


Structure PROTECT_SPREADSHEET_RECORD
   opcode.w  ;18
   length.w  ;2
   Protect.w
EndStructure

Structure FORMAT_COUNT_RECORD
   opcode.w  ;1f
   length.w ;2
   Count.w
EndStructure

Structure FORMAT_RECORD
   opcode.w  ;1e
   length.w  ;1+len(format)
   FormatLength.b ;len(format)
EndStructure ;+ followed by the Format-Picture

Structure COLWIDTH_RECORD
   opcode.w  ;36
   length.w  ;4
   col1.b       ;first column
   col2.b       ;last column
   ColumnWidth.w   ;at 1/256th of a character
EndStructure

;Beginning Of File record
Structure BEG_FILE_RECORD
  opcode.w
  length.w
  version.w
  ftype.w
EndStructure

;End Of File record
Structure END_FILE_RECORD
  opcode.w
  length.w
EndStructure

;true/false to print gridlines
Structure PRINT_GRIDLINES_RECORD
  opcode.w
  length.w
  PrintFlag.w
EndStructure

;Integer record
Structure tInteger
  opcode.w
  length.w
  Row.w     ;unsigned integer
  col.w
  
  ;rgbAttr1 handles whether cell is hidden and/or locked
  rgbAttr1.b
  
  ;rgbAttr2 handles the Font# and Formatting assigned to this cell
  rgbAttr2.b
  
  ;rgbAttr3 handles the Cell Alignment/borders/shading
  rgbAttr3.b
  
  intValue.w  ;the actual integer value
EndStructure

;Number record
Structure tNumber
  opcode.w
  length.w
  Row.w
  col.w
  rgbAttr1.b
  rgbAttr2.b
  rgbAttr3.b
  NumberValue.D ;8 Bytes
EndStructure

;Label (Text) record
Structure tText
  opcode.w
  length.w
  Row.w
  col.w
  rgbAttr1.b
  rgbAttr2.b
  rgbAttr3.b
  TextLength.b
EndStructure

Structure MARGIN_RECORD_LAYOUT
  opcode.w
  length.w
  MarginValue.D  ;8 bytes
EndStructure

Structure HPAGE_BREAK_RECORD
  opcode.w
  length.w
  NumPageBreaks.w
EndStructure

Structure DEF_ROWHEIGHT_RECORD
  opcode.w
  length.w
  RowHeight.w
EndStructure

Structure ROW_HEIGHT_RECORD
  opcode.w  ;08
  length.w  ;should always be 16 bytes
  RowNumber.w
  FirstColumn.w
  LastColumn.w
  RowHeight.w  ;written to file as 1/20ths of a point
  internal.w
  DefaultAttributes.b  ;set to zero for no default attributes
  FileOffset.w
  rgbAttr1.b
  rgbAttr2.b
  rgbAttr3.b
EndStructure

Global xlsFileNumber.l
Global xlsBufferSize.l  ;if > 0 buffer is active, also holds size of buffer.

;create an array that will hold the rows where a horizontal page
;break will be inserted just before.

Macro UBound(x):(PeekL((@x) - 8) - 1):EndMacro 


Global Dim xlsHorizPageBreakRows.l(0)
Global xlsNumHorizPageBreaks.l


Declare.l  xlsCreateFile(mFileName.s)
Declare.l  xlsCloseFile()
Declare.l  xlsInsertHorizPageBreak(lrow.l)
Declare.l  xlsWriteInteger( value.w,  lrow.l,  lcol.l,  CellFont.l,  CellAlignment.l,  HiddenLocked.l,  CellFormat.l)
Declare.l  xlsWriteNumber( value.d,  lrow.l,  lcol.l,  CellFont.l,  CellAlignment.l,  HiddenLocked.l,  CellFormat.l)
Declare.l  xlsWriteText(value.s,  lrow.l,  lcol.l,  CellFont.l,  CellAlignment.l,  HiddenLocked.l,  CellFormat.l)
Declare.l  xlsWriteDate(DateString.s,  lrow.l,  lcol.l,  CellFont.l,  CellAlignment.l,  HiddenLocked.l,  CellFormat.l)
Declare.l  xlsSetMargin( Margin.l,  MarginValue.w)
Declare.l  xlsSetColumnWidth( FirstColumn.l,  LastColumn.l,  WidthValue.l)
Declare.l  xlsSetFont(FontName.s,  FontHeight.l,  FontFormat.l)
Declare.l  xlsSetHeader(HeaderText.s)
Declare.l  xlsSetFooter(FooterText.s)
Declare.l  xlsSetFilePassword(PasswordText.s)
Declare.l  xlsPrintGridLines( TrueFalse.l)
Declare.l  xlsProtectSpreadsheet( TrueFalse.l)
Declare.l  xlsWriteDefaultFormats()
Declare.l  xlsSetDefaultRowHeight( HeightValue.l)
Declare.l  xlsSetRowHeight( lrow.l,  HeightValue.l)
Declare.w  ConvertRow( lrow.l)
Declare.w  ConvertCol( lcol.l)
Declare.l  DateToJulian(DateString.s)



Procedure.l xlsCreateFile(mFileName.s)
   
    BEG_FILE_MARKER.BEG_FILE_RECORD
    ;beginning of file
    BEG_FILE_MARKER\opcode = 9
    BEG_FILE_MARKER\length = 4
    BEG_FILE_MARKER\version = 2
    BEG_FILE_MARKER\ftype = 10

    xlsFileNumber = OpenFile(#PB_Any, mFileName)
    
    WriteData(xlsFileNumber,@BEG_FILE_MARKER, SizeOf(BEG_FILE_MARKER))
  
   xlsWriteDefaultFormats()
    
    ;create the Horizontal Page Break array
    ReDim xlsHorizPageBreakRows(0)
    xlsNumHorizPageBreaks = 0
    
    ProcedureReturn  0  ;return with no error
    
EndProcedure



Procedure.l xlsCloseFile()

    If xlsFileNumber = 0 
       ProcedureReturn  -1
       ;EXIT FUNCTION
    EndIf   
    
    ;write the horizontal page breaks if necessary
    If xlsNumHorizPageBreaks > 0
        
        SortArray(xlsHorizPageBreakRows(), #PB_Sort_Ascending)     
       ;write the Horizontal Page Break Record
        HORIZ_PAGE_BREAK.HPAGE_BREAK_RECORD
        HORIZ_PAGE_BREAK\opcode = 27
        HORIZ_PAGE_BREAK\length = 2 + (xlsNumHorizPageBreaks * 2)
        HORIZ_PAGE_BREAK\NumPageBreaks = xlsNumHorizPageBreaks

        WriteData(xlsFileNumber,@HORIZ_PAGE_BREAK, SizeOf(HORIZ_PAGE_BREAK))

        ;now write the actual page break values
        For x.l = 1 To xlsNumHorizPageBreaks
           st.w = (xlsHorizPageBreakRows(x))
           WriteData(xlsFileNumber,@st, SizeOf(st))
        Next
    EndIf
     
    END_FILE_MARKER.END_FILE_RECORD
    ;end of file marker
    END_FILE_MARKER\opcode = 10
    
    WriteData(xlsFileNumber,@END_FILE_MARKER, SizeOf(END_FILE_MARKER))
        
    CloseFile(xlsFileNumber)

    ProcedureReturn  0  ;return with no error code
    
EndProcedure



Procedure.l xlsInsertHorizPageBreak( lrow.l)

;the row and column values are written to the excel file as
;unsigned integers. Therefore, must convert the longs to integer.
    If lrow > 32767
       Row.w = (lrow - 65536)  ;cint()
    Else
       Row.w = (lrow) - 1    ;rows/cols in Excel binary file are zero based
    EndIf
        
    xlsNumHorizPageBreaks = xlsNumHorizPageBreaks + 1
    ReDim xlsHorizPageBreakRows(xlsNumHorizPageBreaks)
    
    xlsHorizPageBreakRows(xlsNumHorizPageBreaks) = Row
    
    ProcedureReturn  0

EndProcedure



Procedure.w ConvertRow( lrow.l)
;the row and column values are written to the excel file as
;integers. Therefore, must convert the longs to integer.
    
    If lrow > 32767
       ProcedureReturn  (lrow - 65536) ;CINT
    Else
       ProcedureReturn  (lrow) - 1    ;rows/cols in Excel binary file are zero based
    EndIf
  
EndProcedure


Procedure.w ConvertCol( lcol.l)
;the row and column values are written to the excel file as
;integers. Therefore, must convert the longs to integer.

    If lcol > 32767
       ProcedureReturn  (lcol - 65536) ;CINT
    Else
       ProcedureReturn  (lcol) - 1    ;rows/cols in Excel binary file are zero based
    EndIf

EndProcedure




Procedure.l xlsWriteInteger( value.w,  lrow.l,  lcol.l,  CellFont.l,  CellAlignment.l,  HiddenLocked.l,  CellFormat.l)

    ;convert the row, col from LONG to INTEGER.
    Row.w = ConvertRow(lrow.l)
    Col.w = ConvertCol(lcol.l)
    
    INTEGER_RECORD.tInteger
    INTEGER_RECORD\opcode = 2
    INTEGER_RECORD\length = 9
    INTEGER_RECORD\Row = Row
    INTEGER_RECORD\col = col
    INTEGER_RECORD\rgbAttr1 = (HiddenLocked.l)   ;CBYT
    INTEGER_RECORD\rgbAttr2 = (CellFont.l + CellFormat.l)
    INTEGER_RECORD\rgbAttr3 = (CellAlignment.l)
    INTEGER_RECORD\intValue = value

    WriteData(xlsFileNumber,@INTEGER_RECORD, SizeOf(INTEGER_RECORD))
    ProcedureReturn  0   ;return with no error
    
EndProcedure



Procedure.l xlsWriteNumber( value.d,  lrow.l,  lcol.l,  CellFont.l,  CellAlignment.l,  HiddenLocked.l,  CellFormat.l)

    ;convert the row, col from LONG to INTEGER.
    Row.w = ConvertRow(lrow.l)
    Col.w = ConvertCol(lcol.l)

    NUMBER_RECORD.tNumber
    NUMBER_RECORD\opcode = 3
    NUMBER_RECORD\length = 15
    NUMBER_RECORD\Row = Row
    NUMBER_RECORD\col = col
    NUMBER_RECORD\rgbAttr1 = (HiddenLocked) ;CBYT
    NUMBER_RECORD\rgbAttr2 = (CellFont.l + CellFormat)
    NUMBER_RECORD\rgbAttr3 = (CellAlignment)
    NUMBER_RECORD\NumberValue = value

    WriteData(xlsFileNumber,@NUMBER_RECORD, SizeOf(NUMBER_RECORD))
    
    ProcedureReturn  0   ;return with no error
    
EndProcedure


Procedure.l xlsWriteText(value.s,  lrow.l,  lcol.l,  CellFont.l,  CellAlignment.l,  HiddenLocked.l,  CellFormat.l)

    ;convert the row, col from LONG to INTEGER.
    Row.w = ConvertRow(lrow.l)
    Col.w = ConvertCol(lcol.l)

    Protected b.b
    st.s = value
    l.l = Len(st)
    
    Protected TEXT_RECORD.tText
    TEXT_RECORD\opcode = 4
    TEXT_RECORD\length = 10
    ;Length of the text portion of the record
    TEXT_RECORD\TextLength = l.l
    
    ;Total length of the record
    TEXT_RECORD\length = 8 + l.l
    
    TEXT_RECORD\Row = Row
    TEXT_RECORD\col = col
      
    TEXT_RECORD\rgbAttr1 = (HiddenLocked.l) ;CBYT
    TEXT_RECORD\rgbAttr2 = (CellFont.l + CellFormat.l)
    TEXT_RECORD\rgbAttr3 = (CellAlignment.l)

    WriteData(xlsFileNumber,@TEXT_RECORD, SizeOf(TEXT_RECORD))
    
    ;Then the actual string data
    For a.l = 1 To l.l
        b = Asc(Mid(st, a, 1))
        WriteData(xlsFileNumber,@b, 1)

    Next


    ProcedureReturn  0   ;return with no error
    
EndProcedure


Procedure.l xlsWriteDate(DateString.s,  lrow.l,  lcol.l,  CellFont.l,  CellAlignment.l,  HiddenLocked.l,  CellFormat.l)

    ;convert the row, col from LONG to INTEGER.
    Row.w = ConvertRow(lrow.l)
    Col.w = ConvertCol(lcol.l)
    
    ;convert the DateString$ from YYYYMMDD to a Julian date number
    value.l= (DateToJulian.l(DateString) - DateToJulian.l("19000100")) + 1
    
    NUMBER_RECORD.tNumber
    NUMBER_RECORD\opcode = 3
    NUMBER_RECORD\length = 15
    NUMBER_RECORD\Row = Row
    NUMBER_RECORD\col = col
    NUMBER_RECORD\rgbAttr1 = (HiddenLocked.l) ;CBYT
    NUMBER_RECORD\rgbAttr2 = (CellFont.l + CellFormat.l)
    NUMBER_RECORD\rgbAttr3 = (CellAlignment.l)
    NUMBER_RECORD\NumberValue = (value.l) ;CDBL

    WriteData(xlsFileNumber,@NUMBER_RECORD, SizeOf(NUMBER_RECORD))

    ProcedureReturn  0   ;return with no error
    
EndProcedure


Procedure.l xlsSetMargin( Margin.l,  MarginValue.w)

    ;write the spreadsheet;s layout information (in inches)
    MARGINRECORD.MARGIN_RECORD_LAYOUT

    ;Margin.l should be one of the following....
    ;#xlsLeftMargin = 38
    ;#xlsRightMargin = 39
    ;#xlsTopMargin = 40
    ;#xlsBottomMargin = 41
    
    MARGINRECORD\opcode = Margin.l
    MARGINRECORD\length = 8
    MARGINRECORD\MarginValue = MarginValue ;in inches

    WriteData(xlsFileNumber,@MARGINRECORD, SizeOf(MARGINRECORD))

    ProcedureReturn  0
    
EndProcedure


Procedure.l xlsSetColumnWidth( FirstColumn.l,  LastColumn.l,  WidthValue.l)

    COLWIDTH.COLWIDTH_RECORD
    COLWIDTH\opcode = 36
    COLWIDTH\length = 4
    COLWIDTH\col1 = (FirstColumn.l) - 1  ;CBYT
    COLWIDTH\col2 = (LastColumn.l) - 1
    COLWIDTH\ColumnWidth = WidthValue.l * 256  ;values are specified as 1/256 of a character

    WriteData(xlsFileNumber,@COLWIDTH, SizeOf(COLWIDTH))

    ProcedureReturn  0
    
EndProcedure



Procedure.l xlsSetFont(FontName.s,  FontHeight.l,  FontFormat.l)

    ;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)
    
    FONTNAME_RECORD.FONT_RECORD
    
    l.l = Len(FontName)
    
    FONTNAME_RECORD\opcode = 49
    FONTNAME_RECORD\length = 5 + l.l
    FONTNAME_RECORD\FontHeight = FontHeight.l * 20
    FONTNAME_RECORD\FontAttributes1 = (FontFormat.l)  ;bold/underline etc...  CBYT
    FONTNAME_RECORD\FontAttributes2 = (0)            ;reserved-always zero!!
    FONTNAME_RECORD\FontNameLength = (l.l)

    WriteData(xlsFileNumber,@FONTNAME_RECORD, SizeOf(FONTNAME_RECORD))

    ;Then the actual font name data
    Protected b.b
    For a.l = 1 To l.l
        b.b = Asc(Mid(FontName, a.l, 1))
        WriteData(xlsFileNumber,@b, 1) 
    Next

    ProcedureReturn  0
    
EndProcedure



Procedure.l xlsSetHeader(HeaderText.s)

    HEADER_RECORD.HEADER_FOOTER_RECORD
    
    l.l = Len(HeaderText)
    
    HEADER_RECORD\opcode = 20
    HEADER_RECORD\length = 1 + l.l
    HEADER_RECORD\TextLength = (l.l)  ;CBYT

    WriteData(xlsFileNumber,@HEADER_RECORD, SizeOf(HEADER_RECORD))

    ;Then the actual Header text
    Protected b.b
    For a.l = 1 To l.l
        b.b = Asc(Mid(HeaderText, a.l, 1))
        WriteData(xlsFileNumber,@b, 1)
    Next


    ProcedureReturn  0

EndProcedure



Procedure.l xlsSetFooter(FooterText.s)

    FOOTER_RECORD.HEADER_FOOTER_RECORD
    
    l.l = Len(FooterText)
    
    FOOTER_RECORD\opcode = 21
    FOOTER_RECORD\length = 1 + l.l
    FOOTER_RECORD\TextLength = (l.l) ;CBYT

    WriteData(xlsFileNumber,@FOOTER_RECORD, SizeOf(FOOTER_RECORD))

    ;Then the actual Header text
    Protected b.b
    For a.l = 1 To l.l
        b.b = Asc(Mid(FooterText, a.l, 1))
        WriteData(xlsFileNumber,@b, 1) 
    Next

    ProcedureReturn  0
    
EndProcedure



Procedure.l xlsSetFilePassword(PasswordText.s)

    FILE_PASSWORD_RECORD.PASSWORD_RECORD
    
    l.l = Len(PasswordText)
    
    FILE_PASSWORD_RECORD\opcode = 47
    FILE_PASSWORD_RECORD\length = l

    WriteData(xlsFileNumber,@FILE_PASSWORD_RECORD, SizeOf(FILE_PASSWORD_RECORD))

    ;Then the actual Password text
    Protected b.b
    For a.l = 1 To l.l
        b.b = Asc(Mid(PasswordText, a.l, 1))
        WriteData(xlsFileNumber,@b, 1)
    Next

    ProcedureReturn  0
    
EndProcedure


Procedure.l xlsPrintGridLines( TrueFalse.l)

    GRIDLINES_RECORD.PRINT_GRIDLINES_RECORD
    
    GRIDLINES_RECORD\opcode = 43
    GRIDLINES_RECORD\length = 2
    
    If TrueFalse.l = 0
       GRIDLINES_RECORD\PrintFlag = 0
    Else
       GRIDLINES_RECORD\PrintFlag = 1
    EndIf

    WriteData(xlsFileNumber,@GRIDLINES_RECORD, SizeOf(GRIDLINES_RECORD))
    
    ProcedureReturn  0
    
EndProcedure




Procedure.l xlsProtectSpreadsheet( TrueFalse.l)

    PROTECT_RECORD.PROTECT_SPREADSHEET_RECORD
    
    PROTECT_RECORD\opcode = 18
    PROTECT_RECORD\length = 2
    
    If TrueFalse.l = 0
       PROTECT_RECORD\Protect = 0
    Else
       PROTECT_RECORD\Protect = 1
    EndIf
      
        WriteData(xlsFileNumber,@PROTECT_RECORD, SizeOf(PROTECT_RECORD))
    
    ProcedureReturn  0
  
EndProcedure


Procedure.l xlsWriteDefaultFormats()
    
    cFORMAT_COUNT_RECORD.FORMAT_COUNT_RECORD
    cFORMAT_RECORD.FORMAT_RECORD
    Protected lIndex.l
    Dim aFormat.s(23) ;As STRING
    aFormatCount = 23
    Protected l.l
    Protected q.s
    q = Chr(34)
    
    aFormat(0) = "General"
    aFormat(1) = "0"
    aFormat(2) = "0.00"
    aFormat(3) = "#,##0"
    aFormat(4) = "#,##0.00"
    aFormat(5) = "#,##0\ " + q + "$" + q + ";\-#,##0\ " + q + "$" + q
    aFormat(6) = "#,##0\ " + q + "$" + q + ";[Red]\-#,##0\ " + q + "$" + q
    aFormat(7) = "#,##0.00\ " + q + "$" + q + ";\-#,##0.00\ " + q + "$" + q
    aFormat(8) = "#,##0.00\ " + q + "$" + q + ";[Red]\-#,##0.00\ " + q + "$" + q
    aFormat(9) = "0%"
    aFormat(10) = "0.00%"
    aFormat(11) = "0.00E+00"
    aFormat(12) = "yyyy-mm-dd"
    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 = $1F
    cFORMAT_COUNT_RECORD\length = $2
    cFORMAT_COUNT_RECORD\Count = aFormatCount ;(UBOUND(aFormat)) ;CBYT
    
    WriteData(xlsFileNumber,@cFORMAT_COUNT_RECORD, SizeOf(cFORMAT_COUNT_RECORD))
    
    For lIndex = 0 To aFormatCount  ;LBOUND(aFormat)
        l = Len(aFormat(lIndex))

        cFORMAT_RECORD\opcode = $1E
        cFORMAT_RECORD\length = l + 1
        cFORMAT_RECORD\FormatLength = l
        
        WriteData(xlsFileNumber,@cFORMAT_RECORD, SizeOf(cFORMAT_RECORD))
    
        ;Then the actual format
        Protected b.b, a.l
        For a = 1 To l
            b.b = Asc(Mid(aFormat(lIndex), a, 1))
            WriteData(xlsFileNumber,@b, 1)

        Next
    Next 

    ProcedureReturn  0
        
EndProcedure



Procedure.l xlsSetDefaultRowHeight( HeightValue.l)

;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 Procedure takes a HeightValue such as
;14 point and converts it the correct size before writing it to the file.

    DEFHEIGHT.DEF_ROWHEIGHT_RECORD
    DEFHEIGHT\opcode = 37
    DEFHEIGHT\length = 2
    DEFHEIGHT\RowHeight = HeightValue.l * 20  ;convert points to 1/20ths of point

    WriteData(xlsFileNumber,@DEFHEIGHT, SizeOf(DEFHEIGHT))

    ProcedureReturn  0

EndProcedure


Procedure.l xlsSetRowHeight( lrow.l,  HeightValue.l)

    ;convert the row, col from LONG to INTEGER.
    Row.w = ConvertRow(lrow.l)

    ;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 Procedure takes a HeightValue such as
    ;14 point and converts it the correct size before writing it to the file.

    ROWHEIGHTREC.ROW_HEIGHT_RECORD
    ROWHEIGHTREC\opcode = 8
    ROWHEIGHTREC\length = 16
    ROWHEIGHTREC\RowNumber = Row
    ROWHEIGHTREC\FirstColumn = 0
    ROWHEIGHTREC\LastColumn = 256
    ROWHEIGHTREC\RowHeight = HeightValue.l * 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

    WriteData(xlsFileNumber,@ROWHEIGHTREC, SizeOf(ROWHEIGHTREC))

    ProcedureReturn  0

EndProcedure


Procedure.l DateToJulian(DateString.s)

  ;DateString$ must be in YYYYMMDD
  
  Protected Elapsed.l
  
  If Len(DateString) <> 8
     ProcedureReturn  0
     ;EXIT FUNCTION
  EndIf   

  Year.l = Val(Left(DateString, 4))
  month.l = Val(Mid(DateString, 5, 2))
  day.l = Val(Right(DateString, 2))
  
  If month.l < 3                      ; January or February?
     month.l = month.l + 12                   ; 13th or 14th month ....
     year = year - 1                            ; .... of prev. year
  EndIf
  
  Elapsed = Int((year.l + 4712) * 365.25)  ; years elapsed
  Elapsed = Elapsed - (year.l / 100)       ; substract century leapdays
  Elapsed = Elapsed + (year.l / 400)       ; re-add valid ones
  Elapsed = Elapsed + (30.6 * (month - 1) + 0.2) ; months elapsed + adjustm.  ;INT()
  
  ProcedureReturn  Elapsed + day.l               ; days of final month

EndProcedure
Last edited by pdwyer on Sun Nov 16, 2008 9:25 am, edited 1 time in total.
Paul Dwyer

“In nature, it’s not the strongest nor the most intelligent who survives. It’s the most adaptable to change” - Charles Darwin
“If you can't explain it to a six-year old you really don't understand it yourself.” - Albert Einstein
Top
User avatar
pdwyer
Addict
Addict
Posts: 2813
Joined: Tue May 08, 2007 1:27 pm
Location: Chiba, Japan

Post by pdwyer »

Example commented code from Paul Squires, also ported to Purebasic

Code: Select all



XIncludeFile "excel.pbi" 


#XLSFALSE = 0
#XLSTRUE = 1;Not #XLSFALSE



    ;Create the new spreadsheet
    mFileName.s = "c:\test.xls"  ;create spreadsheet in the current directory
    stat = xlsCreateFile(mFileName)
    ;stat returns non-zero if an error occured.
    
    ;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
    ;xlsCreateFile function call.
    
    ;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.
    ;stat = xlsSetFilePassword("MyClearTextPwd")
        
    
    ;specify whether to print the gridlines or not
    ;this should come before the setting of fonts and margins
    stat = xlsPrintGridLines(#XLSTRUE)
    
    
    ;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.
    
    stat = xlsSetMargin(#xlsTopMargin, 1.5)   ;set to 1.5 inches
    stat = xlsSetMargin(#xlsLeftMargin, 1.5)
    stat = xlsSetMargin(#xlsRightMargin, 1.5)
    stat = xlsSetMargin(#xlsBottomMargin, 1.5)
    
    
    ;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.
    
    stat = xlsSetFont("Arial", 10, #xlsNoFormat)             ;font0
    stat = xlsSetFont("Arial", 10, #xlsBold)                 ;font1
    stat = xlsSetFont("Arial", 24, #xlsBold + #xlsUnderline) ;font2
    stat = xlsSetFont("Courier", 18, #xlsItalic)             ;font3
    
    
    ;Column widths are specified in Excel as 1/256th of a character.
    stat = xlsSetColumnWidth(1, 1, 50)
    
    ;set the global row height for the entire spreadsheet
    stat = xlsSetDefaultRowHeight(24)
    
    ;set the height of the first two rows a little bigger to allow for the 
    ;title of the spreadsheet.
    stat = xlsSetRowHeight(1, 24)
    stat = xlsSetRowHeight(2, 24)
    
    
    ;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.
    stat = xlsSetHeader("This is the header")
    stat = xlsSetFooter("This is the footer")
    
    
    ;write some data to the spreadsheet
    stat = xlsWriteInteger(20, 6, 1, #xlsFont0, #xlsLeftAlign, #xlsCellNormal, 0)
    
    ;write a cell with a shaded number with a bottom border
    stat = xlsWriteNumber(12123.456, 7, 1, #xlsFont1, #xlsrightAlign + #xlsBottomBorder + #xlsShaded, #xlsCellNormal, 0)
    
    ;write a normal left aligned string using font2 (bold & underline)
    stat = xlsWriteText("This is a test string", 8, 1, #xlsFont2, #xlsLeftAlign, #xlsCellNormal, 0)
    
    ;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!!!
    stat = xlsWriteText("This cell is locked.", 9, 1, #xlsFont3, #xlsLeftAlign, #xlsCellLocked, 0)
    
    ;fill the cell with "F";s
    stat = xlsWriteText("F", 10, 1, #xlsFont3, #xlsFillCell, #xlsCellNormal, 0)
    
    ;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.
    stat = xlsWriteText("If this were a formula it would be hidden!", 11, 1, #xlsFont0, #xlsCentreAlign, #xlsCellHidden, 0)
    stat = xlsWriteText("=14 * 134", 11, 2, #xlsFont0, #xlsCentreAlign, #xlsCellNormal, 0)
    stat = xlsWriteText("14 * 134", 11, 3, #xlsFont0, #xlsCentreAlign, #xlsCellNormal, 0)
    ;write a date to the file. Dates can be written as literal text strings but doing so will not allow
    ;the date to be formatted. The date will be eventually written as a number after conversion to a
    ;Julian date number.
    ;Write todays date..... Use format #12 mm/dd/yy (you can change the different formats by modifying
    ;the code in xlsWriteDefaultFormats. The date is expected to be in the YYYYMMDD format. You can convert
    ;from mm-dd-yyyy format to YYYYMMDD by calling the CTOD$ function.
    mDate.s = FormatDate("%yyyy%mm%dd", Date())
    stat = xlsWriteDate(mDate, 15, 1, #xlsFont1, #xlsLeftAlign, #xlsCellNormal, 12)
    
    mDate = "19991128"
    stat = xlsWriteDate(mDate, 16, 1, #xlsFont1, #xlsLeftAlign, #xlsCellNormal, 13)

    mDate = "20000331"
    stat = xlsWriteDate(mDate, 17, 1, #xlsFont1, #xlsLeftAlign, #xlsCellNormal, 14)
    
    mDate = "20010630"
    stat = xlsWriteDate(mDate, 18, 1, #xlsFont1, #xlsLeftAlign, #xlsCellNormal, 15)

    mDate = "19991023"
    stat = xlsWriteDate(mDate, 19, 1, #xlsFont1, #xlsLeftAlign, #xlsCellNormal, 20)
    
    ;insert a page break
    stat = xlsInsertHorizPageBreak(20)
    stat = xlsInsertHorizPageBreak(40)
    
    ;write consecutive numbers in the column
    ;This demonstrates that this Excel code can exceed the normal BIFF 2.1 and Excel 95 row limit
    ;of 32,767 rows.
    NumRows = 50000
    For x = 1 To NumRows
        stat = xlsWriteNumber(x, 20+x, 1, #xlsFont1, #xlsLeftAlign, #xlsCellNormal, 0)
    Next
    

    ;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.
    stat = xlsProtectSpreadsheet(#XLSTRUE)
    
    
    ;Finally, close the spreadsheet
    stat = xlsCloseFile()
Paul Dwyer

“In nature, it’s not the strongest nor the most intelligent who survives. It’s the most adaptable to change” - Charles Darwin
“If you can't explain it to a six-year old you really don't understand it yourself.” - Albert Einstein
Top
User avatar
Kwai chang caine
Always Here
Always Here
Posts: 5494
Joined: Sun Nov 05, 2006 11:42 pm
Location: Lyon - France

Post by Kwai chang caine »

It's very GREAT :shock:

It's work fine with W2000 :D
I have EXCEL installed in my pc, but it's a wonderful code

Thanks for sharing 8)
ImageThe happiness is a road...
Not a destination
Top
Paul Squires
New User
New User
Posts: 6
Joined: Wed Dec 31, 2003 12:52 am

Post by Paul Squires »

If I remember correctly, some people experienced problems using the spreadsheets in later versions of Excel when the Row Heights are changed via code. Something to keep an eye out for if any of you guys use this code.
Top
User avatar
Rook Zimbabwe
Addict
Addict
Posts: 4322
Joined: Tue Jan 02, 2007 8:16 pm
Location: Cypress TX
Contact:
Contact Rook Zimbabwe

Post by Rook Zimbabwe »

I had issues with ABBKlaus' version of this. I would enter a formula like:

=SUM(A1:a3) using xlsWriteText() and in the actual excel file I would have to physically click on the cell containing that formula and then press ENTER to get it to turn in to a formula.

I know Excel has a FORMULA fuction... are you planning to implement?

I do this:

Code: Select all

    stat = xlsWriteText("=14 * 134", 11, 2, #xlsFont0, #xlsCentreAlign, #xlsCellNormal, 0)
    stat = xlsWriteText("=14 * 134", 11, 3, #xlsFont0, #xlsCentreAlign,#xlsCellHidden, 0)
And they look exactly the same in office 2003
Binarily speaking... it takes 10 to Tango!!!

Image
http://www.bluemesapc.com/
Top
User avatar
pdwyer
Addict
Addict
Posts: 2813
Joined: Tue May 08, 2007 1:27 pm
Location: Chiba, Japan

Post by pdwyer »

@Paul: I tried the code on excel 2003 and it was fine as far as I could tell but I might take a closer look as I didn't test that far

@Rook: I haven't work out how to get formulas in there. From what I read I thought you can add it in text but I experienced the same as you. I didn't try too hard at it since I'm building the excel sheet in code I can calculate things myself 8) (But I would like to do this)

I got a pdf on the spec for BIFF v2 ~ v5 and there was some info about formulas but it was a huge doc that often tried to cover multiple version in one explanantion and I didn't have the time.

If you know of the function name that needs to be added let me know and I can focus my efforts there a little.
Paul Dwyer

“In nature, it’s not the strongest nor the most intelligent who survives. It’s the most adaptable to change” - Charles Darwin
“If you can't explain it to a six-year old you really don't understand it yourself.” - Albert Einstein
Top
User avatar
pdwyer
Addict
Addict
Posts: 2813
Joined: Tue May 08, 2007 1:27 pm
Location: Chiba, Japan

Post by pdwyer »

I took a peek at the PDF on how to add formula's and it's not simple. you have to put it in in a special code. So if you want the user (programmer) to add "2*4+5" the you have to split this into tokens that are RPN (Reverse polish notation) which is like pushing to a stack... ie "2, 4, *, 5, +" then use the codes: tInt(2), tInt(4), tMul, tInt(5), tAdd (do you're own order of operations too it seams)

I put a hex editor on an excel 2 sheet with a formula in it and that's exactly what I see too... this means you need to write a parser first to turn the easy text for the programmer to add into RPN <SIGH> a job for when I'm really bored I think (or really need the feature). It sound like creating room for bugs galore!

Simpler, since you are programmatically creating the sheet to calc the result and just add it yourself :P
Paul Dwyer

“In nature, it’s not the strongest nor the most intelligent who survives. It’s the most adaptable to change” - Charles Darwin
“If you can't explain it to a six-year old you really don't understand it yourself.” - Albert Einstein
Top
User avatar
Rook Zimbabwe
Addict
Addict
Posts: 4322
Joined: Tue Jan 02, 2007 8:16 pm
Location: Cypress TX
Contact:
Contact Rook Zimbabwe

Post by Rook Zimbabwe »

That would explain a great deal. I hope you get bored soon! ;)

I will just settle for outing to excel and let the users formulate!
Binarily speaking... it takes 10 to Tango!!!

Image
http://www.bluemesapc.com/
Top
Post Reply

Return to “Tricks 'n' Tips”