Thursday, April 30, 2009

Silly Rabbit, KiXtart is for Scripting?

I must be on a KiXtart kick or something.  I don't know.  It's an addictive scripting language to work with.  As much as CFML seems dated to me now, there are some similarities with it.  They are both relatively easy to pick up and get going on.  A low learning ramp.  But both can (or could) support you as you expand your skills into intermediate and onward through advanced/expert and into the metaphysical.  Where you drink hot tea with incense burning in a temple of computers and hum Tibetan chants, tap a gong, and say something ethereal like "I see, the path, to enlightenment.... it is .... (sip)... over there on... (cough)... the thumbdrive."  You may leave the temple now.

Geez that was dumb.  Where was I going with that?  Who knows.

On to the MEAT!...

First up: Script example of Reading crap from an Excel Spreadsheet


;----------------------------------------------------------------
; Filename..: excel_read.kix
; Author....: David M. Stein
; Date......: 04/30/2009
; Purpose...: read rows and columns in excel spreadsheet table
;----------------------------------------------------------------
$filePath = "c:\somefile.xls" ; make sure you update this
$columns = "1,2,4,6" ; columns to read from, suit yourself
$row = 3 ; row to start reading from, suit yourself
$numRows = 10 ; max number of rows to read from start row

; Do not modify below this point!
$objExcel = CreateObject("Excel.Application")
$objWorkbook = $objExcel.Workbooks.Open($filePath)
;$objExcel.Visible = 1 ; uncomment if want something dumb to look at

$hrow = "Results"

For each $x in Split($columns, ",")
$hrow = $hrow+Chr(9)+"[$x]"
Next
? Trim($hrow)

While ($row < $numRows)
$rowString = ""
For each $colnum in Split($columns, ",")
$cellValue = Trim($objExcel.Cells($row, Val($colnum)).Value)
If $cellValue <> ''
$rowString = $rowString+Chr(9)+$cellValue
Else
$rowString = $rowString+Chr(9)+"--"
EndIf
Next
? "$row"+Chr(9)+Trim($rowString)
$row = $row + 1
Loop

$objExcel.Quit


And, 2nd up... An Example of Writing crap to a Spreadsheet


;----------------------------------------------------------------
; Filename..: excel_write.kix
; Author....: David M. Stein
; Date......: 04/30/2009
; Purpose...: write rows and columns in excel spreadsheet table
; Adapted from KiXtart User's Guide for 4.60 (www.kixtart.org)
;----------------------------------------------------------------
$objExcel = CreateObject("Excel.Application")
If @ERROR = 0
$objExcel.Visible = 1 ; make Excel visible to the user
$Rc = $objExcel.Workbooks.Add ; add a new workbook
$array = "Order #", "Amount", "Tax"
$objExcel.Range("A1:C1").Value = $array ;add some columns

; populate the spreadsheet cells with data
For $i = 0 To 19
$objExcel.Cells(($i+2),1).Value = "ORD" + ($i + 1000)
$objExcel.Cells(($i+2),2).Value = Rnd() / 100
Next

; fill the last column with a formula to compute the sales tax.
$objExcel.Range("C2").Resize(20, 1).Formula = "=B2*0.07"

; format the worksheet
$objExcel.Range("A1:C1").Font.Bold = 1
$objExcel.Range("B2:C22").Style = "Currency"

; add sum-total and double-line separator
$objExcel.Range("B22").Select
$objExcel.Range("B22").Formula = "=SUM(B2:B21)"
$objExcel.Range("B21").Borders(9).LineStyle = -4119 ; xlDouble
$objExcel.Range("B21").Borders(9).Weight = 4 ; xlThick

; apply color fills to heading and left-hand column cells
$objExcel.Range("A1:C1").Interior.Pattern = 1
$objExcel.Range("A1:C1").Interior.TintAndShade = -0.249977111117893
$objExcel.Range("A1:C1").Interior.ThemeColor = 3
$objExcel.Range("A1:C1").Interior.PatternTintAndShade = 0

$objExcel.Range("A2:A21").Interior.Pattern = 1
$objExcel.Range("A2:A21").Interior.TintAndShade = -4.99893185216834E-02
$objExcel.Range("A2:A21").Interior.ThemeColor = 3
$objExcel.Range("A2:A21").Interior.PatternTintAndShade = 0

; auto-fit columns and finish up
$Rc = $objExcel.Range("A1:C1").EntireColumn.AutoFit
$objExcel.UserControl = 1
Else
? @ERROR + " / " @SERROR
EndIf
Post a Comment