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