wjk tcom excel sample

This was my first experience with tcom - The goal of this program was to be able to use it as a template to create other spreadsheet specific programs for entering data into a spreadsheet from which I was going to export data and import into a cleaner/larger spreadsheet. One word of caution: Do not open spreadsheet with another program at the same time. If you copy this to your PC, remember to get rid of the 3 leading spaces.
   wm title . "Tcl TCOM Excel Input Program"
   wm resizable . 0 0
   wm protocol . WM_DELETE_WINDOW closem
   #[email protected]
   package require tcom

   set excel [::tcom::ref createobj Excel.Application]
   $excel Visible 0
   set workbooks [$excel Workbooks]
   if { [ file exists "C:\\test.xls" ] != 1 } {
   	set workbook [$workbooks Add]
   	$workbook SaveAs {C:\test.xls}
   	}
   set workbook [$workbooks Open {C:\test.xls}]
   set worksheets [$workbook Worksheets]
   set worksheet [$worksheets Item [expr 1]]
   set cells [$worksheet Cells]
   set sheet 1
   set EMPTY ""
   set goto 1
   set NODEFAULTROWS 999

   ########################################################################
   ############ User can edit the program information below ###############
   ########################################################################

   ##### Where to start input/delete - normally 1st row
   ##### You may want to start at a higher row number as some users
   ##### have a tendancy to use 1st few rows for title/key and non-related information etc.
   ##### which is pretty but not database related.

   set start 1
   ##    Example:  set start 7

   ##### How many fields (columns) default shown here is 7 MAX is 15

   set numberoffields 7

   ##    Example:  set numberoffields 3

   ##### What labels you wish to apply for the GUI - there should be same number
   ##### of labels as the number of fields above...
   ##### These can be modified to match your needs ie {"Last Name: " "First Name: " etc.}

   set labels {"Header 1: "\
   "Header 2: "\
   "Header 3: "\
   "Header 4: "\
   "Header 5: "\
   "Header 6: "\
   "Header 7: "}

   ##     Example:
   #set labels {"First name: "\
   #"Last name:"\
   #"Extension: "}

   ########################################################################
   ########################################################################

   set howmany ""
   set textvariables ""
   set columns ""
   for { set y 1 } {$y <= $numberoffields } { incr y} {
   	lappend howmany $y
   	lappend textvariables "entry$y"
   	switch $y {
    		1 { lappend columns "A" }
     		2 {  lappend columns "B" }
    		3 {  lappend columns "C" }
    		4 {  lappend columns "D" }
     		5 {  lappend columns "E" }
     		6 {  lappend columns "F" }
     		7 {  lappend columns "G" }
     		8 {  lappend columns "H" }
     		9 {  lappend columns "I" }
     		10 { lappend columns "J" }
     		11 { lappend columns "K" }
     		12 { lappend columns "L" }
     		13 { lappend columns "M" }
     		14 { lappend columns "N" }
     		15 { lappend columns "O" }
   		default {}
   	}}

   foreach Number $howmany Label $labels Variable $textvariables {
   	label .l$Number -text $Label -font { helvetica 9 bold} -relief flat
   	entry .e$Number -textvariable $Variable -font { helvetica 9 } -width 40
   }

   label .message -text "Message: " -relief ridge -font { helvetica 9 bold }
   label .error -width 50 -textvariable ErrorMsg -relief ridge -bg #efffff

   for { set Number 1 } { $Number <= $numberoffields } { incr Number } {
   	grid .l$Number -row $Number -column 1 -sticky e
   	grid .e$Number -row $Number -column 2 -sticky ew
   	}
   grid .message -row 10 -column 1 -sticky e
   grid .error -row 10 -column 2 -sticky ew

   frame .f2
   button .f2.b1 -text "GoTo" -bg lightblue -font { helvetica 9 bold } -command {
   	set ErrorMsg ""
   	if { $goto >= 1 && $goto <= $NODEFAULTROWS } {
   		foreach Column $columns DataSource $textvariables {
   			set $DataSource [[$cells Item $goto $Column] Value]
   		}} else {
   		set ErrorMsg "Number has to be between 1 and $NODEFAULTROWS"
   		foreach DataSource $textvariables {
   			set $DataSource ""
   		}}
   	.f2.b2 configure -state active -activebackground lightblue
   	.f2.b3 configure -state active -activebackground lightblue
   	}

   entry .f2.e1 -width 5 -text 1 -textvariable goto -font {elvetica 9 }

   button .f2.b1a -text "Next" -bg lightblue -font { helvetica 9 bold } -command {
   	set ErrorMsg ""
   	incr goto
   	if { $goto >= 1 && $goto <= $NODEFAULTROWS } {
   		foreach Column $columns DataSource $textvariables {
   			set $DataSource [[$cells Item $goto $Column] Value]
   			}
   		.f2.b2 configure -state active -activebackground lightblue
   		.f2.b3 configure -state active -activebackground lightblue
   		} else {
   			set goto 1
   			set ErrorMsg "Number has to be between 1 and $NODEFAULTROWS"
   			foreach DataSource $textvariables {
   				set $DataSource ""
   				}
   			.f2.b2 configure -state disabled -bg lightblue
   			.f2.b3 configure -state disabled -bg lightblue
   	}}

   button .f2.b1b -text "Back" -bg lightblue -font { helvetica 9 bold } -command {
   	set ErrorMsg ""
   	set goto [expr $goto - 1 ]
   	if { $goto >= 1 && $goto <= $NODEFAULTROWS } {
   		foreach Column $columns DataSource $textvariables {
   			set $DataSource [[$cells Item $goto $Column] Value]
   			}
   		.f2.b2 configure -state active -activebackground lightblue
   		.f2.b3 configure -state active -activebackground lightblue
   		} else {
   			set goto 1
   			set ErrorMsg "Number has to be between 1 and $NODEFAULTROWS"
   			foreach DataSource $textvariables {
   				set $DataSource ""
   				}
   			.f2.b2 configure -state disabled -bg lightblue
   			.f2.b3 configure -state disabled -bg lightblue
   	}}

   button .f2.b2 -state disabled -text "Replace" -bg lightblue -fg black  -disabledforeground blue \
   	-font { helvetica 9 bold } -command {
   	if {$entry1 != "" } {
   		if { $goto >= 1 && $goto <$NODEFAULTROWS } {
   			foreach Column $columns DataSource $textvariables {
   				$cells Item $goto $Column [expr $$DataSource]
   				set $DataSource ""
   			}
   			.f2.b2 configure -state disabled -bg lightblue
   			.f2.b3 configure -state disabled -bg lightblue
   			} else {
   			set ErrorMsg "Number has to be between 1 and $NODEFAULTROWS"
   			set goto 1
   			.f2.b2 configure -state disabled -bg lightblue
   			.f2.b3 configure -state disabled -bg lightblue
   		}} else {
   			set ErrorMsg "First Field is required!"
   	}}

   button .f2.b3 -state disabled -text "Delete" -bg lightblue -fg black  -disabledforeground blue \
   	-font { helvetica 9 bold } -command {
   	if { $goto >= 1 && $goto <$NODEFAULTROWS } {
   		foreach Column $columns DataSource $textvariables {
   			$cells Item $goto $Column $EMPTY
   			set $DataSource ""
   			}
   			.f2.b2 configure -state disabled -bg lightblue -fg black
   			.f2.b3 configure -state disabled -bg lightblue -fg black
   		} else {
   			set ErrorMsg "Number has to be between 1 and $NODEFAULTROWS"
   			set goto 1
   			.f2.b2 configure -state disabled -bg lightblue -fg black
   			.f2.b3 configure -state disabled -bg lightblue -fg black
   	}}

   button .f2.b3a -text "Delete Last" -bg lightblue -font { helvetica 9 bold } -command {
   	for { set Row [expr $start + 1 ] } { $Row <= $NODEFAULTROWS } { incr Row } {
   		if { [[$cells Item $start A ] Value ] == "" } {
   			set Row 999999
   			set ErrorMsg "No records to delete from Excel Sheet $sheet!"
   		} else {
   		if { [[$cells Item $Row A] Value ] == "" } {
   			set Row [ expr $Row - 1 ]
   			foreach Column $columns DataSource $textvariables {
   				$cells Item $Row $Column $EMPTY
   				set $DataSource ""
   			}
   		set ErrorMsg "Deleted last entry from Excel Sheet $sheet!"
   		set Row 999999
   	}}}}

   button .f2.b4 -text "Add record" -bg lightblue -font { helvetica 9 bold } -command {
   	set goto 1
   	.f2.b2 configure -state disabled -bg lightblue
   	.f2.b3 configure -state disabled -bg lightblue
   	set ErrorMsg ""
   	for { set Row $start} { $Row <= $NODEFAULTROWS } { incr Row } {
   		if { [[$cells Item $Row A] Value ] == "" } {
   			if {$entry1 != "" } {
   				foreach Column $columns DataSource $textvariables {
   					$cells Item $Row $Column [expr $$DataSource]
   					set $DataSource ""
   					}
   				set Row 999999
   				set ErrorMsg "Adding record to Excel Sheet $sheet!"
   			} else {
   				set ErrorMsg "First Field is required"
   				set Row 999999
   		}}}
   		update
   	}

   pack .f2.b1 -side left
   pack .f2.e1 -side left
   pack .f2.b1a -side left
   pack .f2.b1b -side left
   pack .f2.b2 -side left
   pack .f2.b3 -side left
   pack .f2.b3a -side left
   pack .f2.b4 -side left

   grid .f2 -row 19 -column 1 -columnspan 2

   frame .f1
   button .f1.b2 -text "Tab it" -bg lightgreen -font { helvetica 9 bold } -command {
   	set line ""
   	set fp1 [ open "C:\\test.txt" w+ ]
   	for { set Row $start} { $Row <= $NODEFAULTROWS } { incr Row } {
   		if { [[$cells Item $Row A] Value ] == "" } {
   			set Row 999999
   		} else {
   			set line ""
   			foreach Column $columns {
   				set line $line[[$cells Item $Row $Column] Value]\t
   			}
   			regsub {\t$} $line "" line
   			puts $fp1 "$line"
   		}}
   	flush $fp1
   	close $fp1
   	}

   button .f1.b2a -text "Comma it" -bg lightgreen -font { helvetica 9 bold } -command {
   	set line ""
   	set fp1 [ open "C:\\test.cvs" w+ ]
   	for { set Row $start } { $Row <= $NODEFAULTROWS } { incr Row } {
   		if { [[$cells Item $Row A] Value ] == "" } {
   			set Row 999999
   		} else {
   			set line ""
   			foreach Column $columns {
   				set line $line[[$cells Item $Row $Column] Value],
   			}
   			regsub {,$} $line "" line
   			puts $fp1 "$line"
   		}}
   	flush $fp1
   	close $fp1
   	}

   button .f1.b2b -text "Show xls" -bg lightgreen -font { helvetica 9 bold } -command {
   	if {[$excel Visible] == 1} {
   		$excel Visible 0
   	} else {
   		$excel Visible 1
   	}}

   button .f1.b2c -text "Backup" -bg lightgreen -font { helvetica 9 bold } -command {
   	set ErrorMsg "Backup only copies original file that you started with!"
   	file copy -force "C:\\test.xls" "C:\\testbak.xls"
   	}

   button .f1.b2d -text "Clear" -bg lightgreen -font { helvetica 9 bold } -command {
   	set ErrorMsg ""
   	set goto 1
   	.f2.b2 configure -state disabled -bg lightblue
   	.f2.b3 configure -state disabled -bg lightblue
   	foreach DataSource $textvariables {
   		set $DataSource ""
   	}}

   button .f1.b2f -text "Change Sheets" -bg lightgreen -font { helvetica 9 bold } -command {
   	if { $sheet == 1 } {
   		set worksheet [$worksheets Item [expr 2]]
   		set cells [$worksheet Cells]
   		set sheet 2
   		set ErrorMsg "You are now using Sheet 2 - select proper Excel Tab to view"
   	} elseif { $sheet == 2 } {
   		set worksheet [$worksheets Item [expr 3]]
   		set cells [$worksheet Cells]
   		set sheet 3
   		set ErrorMsg "You are now using Sheet 3 - select proper Excel Tab to view"
   	} else {
   		set worksheet [$worksheets Item [expr 1]]
   		set cells [$worksheet Cells]
   		set sheet 1
   		set ErrorMsg "You are now using Sheet 1 - select proper Excel Tab to view"
   	}}

   button .f1.b3 -text "Quit" -bg pink -font { helvetica 9 bold } -command {
   	$excel Visible 0
   	$excel Quit
   	unset excel
   	exit 0
   	}

   pack .f1.b2 -side left
   pack .f1.b2a -side left
   pack .f1.b2b -side left
   pack .f1.b2c -side left
   pack .f1.b2d -side left
   pack .f1.b2f -side left
   pack .f1.b3 -side left

   grid .f1 -row 20 -column 1 -columnspan 2

   proc closem { } {
   	global excel
   	$excel Quit
   	unset excel
   	exit 0
   }
Category Example	Category Windows