Dynamically Generating Excel Files from Web Data using SpreadScript

#!/bin/sh
# the next line restarts using tclsh \
export LD_LIBRARY_PATH=/home/tkm/lib; exec /usr/local/bin/tclsh "$0" "$@"

load /home/tkm/lib/tcl/spreadscript.so spreadscript

set SPREADSCRIPT_LICENSE "/home/tkm/bin/SpreadScript-license.txt"

set QuoteServer         finance.yahoo.com
set QuoteCGI            d/quotes.csv


proc do_http_get { theServer theFile theParams } {
  set len [string length $theParams]
  set websock [socket $theServer 80]
  puts $websock "GET /$theFile?$theParams HTTP/1.0\n"
  flush $websock
  set result [read $websock]
  close $websock
  return $result
  }


proc storevalue {value sheet row col} {

  global SS_HJUST_CENTER SS_FONT_STYLE_ITALIC

  if {[regexp {("[^\"]*"|N/A)} $value match text]} {
    sheet setText $row $col [string trim $text "\""]
    if {[string match "N/A" $text]} {
      sheet setHorizontalJustification $row $col $SS_HJUST_CENTER
      sheet setFontStyle $row $col $SS_FONT_STYLE_ITALIC
      }
    } else {
    sheet setNumber $row $col $value
    }
  }


proc storequotes {line sheet row} {

  global SS_HJUST_CENTER SS_FMT_COMMA

  # debug
  #puts $line

  set col 1
  foreach word [split $line ,] {
    storevalue $word sheet $row $col
    incr col
    }

  sheet setHorizontalJustification $row 3 $SS_HJUST_CENTER
  sheet setHorizontalJustification $row 4 $SS_HJUST_CENTER
  sheet setFormat $row 9 $SS_FMT_COMMA
  sheet setPlaces $row 9 0
  }


# main

set page \
[do_http_get $QuoteServer $QuoteCGI "$env(QUERY_STRING)"]

regexp "\n\n(.*)" $page match text

# debug statement
#puts "Content-Type: text/plain\n"

# initialize the library, passing in the license file location

init $SPREADSCRIPT_LICENSE

# Create a book

Workbook book

# make this book the active book (important)

book activate

# create a blank sheet

Worksheet sheet -this [book createSheet "My Quotes"]

sheet setDefaultFormat $SS_FMT_COMMA
sheet setDefaultPlaces 2

# create a header row

set row 1
sheet setText $row 1 "Symbol"
sheet setColumnWidths 1 1 800
sheet setText $row 2 "Last Trade"
sheet setColumnWidths 2 2 1200
sheet setText $row 3 "Date"
sheet setText $row 4 "Time"
sheet setText $row 5 "Change"
sheet setText $row 6 "Opening Price"
sheet setColumnWidths 6 6 1500
sheet setText $row 7 "Day's High"
sheet setColumnWidths 7 7 1100
sheet setText $row 8 "Day's low"
sheet setColumnWidths 8 8 1100
sheet setText $row 9 "Volume Traded"
sheet setColumnWidths 9 9 1600

for { set col 1 } { $col <= 9 } { incr col } {
  sheet setFontStyle $row $col $SS_FONT_STYLE_BOLD
  sheet setHorizontalJustification $row $col $SS_HJUST_CENTER
  }

foreach line [split $text \n] {
  if {[string length $line] == 0} break
  incr row
  storequotes $line sheet $row
  }

# Create a temporary file name for saving the .xls file

set tmpfile "/tmp/[pid].xls"

book save $tmpfile $SS_EXCEL_95

# Generate the HTTP boilerplate

puts "Accept-Ranges: bytes"
puts "Content-Length: [file size $tmpfile]"
puts "Connection: close"
puts "Content-Type: application/vnd.ms-excel\n"

# Insert the .xls file in the HTTP stream and then delete it

set f [open $tmpfile r]
fconfigure $f -translation binary
set data [read $f]
close $f
puts -nonewline stdout $data
file delete -force $tmpfile