From: Daniel Berger <djberge@...55host11.interprise.com>
Date: 2002-08-20T04:22:08+09:00
Subject: PHB's fear, Spreadsheet alpha1 is here!

Hi all,

If you've ever had the need to write a file on any platform that was readable by MS Excel, than take note!

This module is based on version .11 of John McNamara's Spreadsheet::WriteExcel.

What it can do:

You can write plain strings and numbers into specific rows and columns on a single worksheet that is readable by 
MS Excel.

You can do this on *any* platform.  It does NOT require COM.  I ran my own tests on Solaris 9.

What it can't do:

There is no cell or text formatting of any kind.
There is only one possible worksheet
It may not be readable by gnumeric, etc (not tested)

Why did you use version .11 instead of the latest (.37)?

Because John McNamara recommended that, for a basic version, I should start there.  Also, I wanted to get 
something out the door fast, and to port the latest version would have required much more time.

Example of usage:

require 'spreadsheet'

xl = Spreadsheet::Excel.new("test.xls")
xl.write(5,5,"Hello")
xl.write(2,3,777)
xl.close

This current class layout is subject to change (thus, alpha1).

Feedback welcome!

Regards,

Dan

# spreadsheet.rb
# For more detailed comments, see .11 of Spreadsheet::WriteExcel
# available on search.cpan.org
module Spreadsheet

   class OutOfRangeException < RuntimeError; end
   class InvalidTypeException < RuntimeError; end

   class Excel 

      RowMax = 65536
      ColMax = 256
      StrMax = 255

      def initialize(filename)
         @fh = File.new(filename,"w+")      
         @filename = filename
         @dim_offset = 0

         # Big Endian or Little Endian architecture?
         if [1].pack("I") == [1].pack("N")
            @byte_order = 1
         else
            @byte_order = 0
         end

         write_bof()
         write_dimensions()
      end

      # Using BIFF2
      def write_bof

         name    = 0x0809 # Record identifier
         len     = 0x0008 # Number of bytes
         version = 0x0000
         type    = 0x0010 # Worksheet
         build   = 0x0000 # Set to zero
         year    = 0x0000 # Set to zero

         header = [name,len].pack("vv")
         data   = [version,type,build,year].pack("vvvv")
         hd     = header + data

         @fh.print(hd)

      end

      def write_dimensions(row_min=0,row_max=0,col_min=0,col_max=0)

         row_max += 1
         col_max += 1
         
         name = 0x0000
         len  = 0x000A
         res  = 0x0000

         header = [name,len].pack("vv")
         data   = [row_min,row_max,col_min,col_max,res].pack("vvvvv")
         hd     = header + data

         @dim_offset = @fh.tell
         @fh.print(hd)

      end

      def write_eof
         
         name = 0x000A
         len  = 0x0000

         header = [name,len].pack("vv")
         @fh.print(header)
         @fh.seek(@dim_offset,0)
         write_dimensions()

      end

      def close
         write_eof()
         @fh.close
      end

      def write(row=0,col=0,data=nil)
         if data.type.to_s == "String"
            write_string(row,col,data)
         elsif data.type.to_s == "Fixnum"
            write_number(row,col,data)
         else
            raise InvalidTypeException
         end 
      end

      def write_string(row,col,string)

         raise OutOfRangeException if row > RowMax
         raise OutOfRangeException if col > ColMax

         name = 0x0204 # Record identifier
         xf   = 0x0000 # Cell format

         strlen = string.length

         string.slice!(0,StrMax) if strlen > StrMax

         len  = 0x0008 + strlen # Bytes to follow

         header = [name,len].pack("vv")
         data   = [row,col,xf,strlen].pack("vvvv")
         hds = header + data + string

         @fh.print(hds)
      end

      def write_number(row,col,num)

         raise OutOfRangeException if row > RowMax
         raise OutOfRangeException if col > ColMax

         name = 0x0203
         len  = 0x000E
         xf   = 0x0000

         header = [name,len].pack("vv")
         data   = [row,col,xf].pack("vvv")
         xld    = [num].pack("d")

         xld = xld.to_s.reverse if @byte_order == 1

         hdx = header + data + xld

         @fh.print(hdx)

      end

   end
end