Forum OpenACS Q&A: Re: announcing: q-wiki, q-forms, spreadsheet
Posted by
Dave Bauer
on 03/12/13 11:36 PM
Hi,
I have TclUNO support for using OpenOffice/LibreOffice (using libreoffice seems to be the way to go these days) to export XLS format spreadsheets. You could export a OpenOffice calc format as well if you prefer.
http://tcluno.sourceforge.net/
http://www.tugm.de/Projekte/TCLUNO/index.html.en
ad_library { TclUno support to drive openoffice } ad_proc list_oo_create {deskName} { Setup a new handle to a tcluno document } { upvar $deskName desk # use the tcl version of tcluno not the shared library # since libtcluno.so does not come with the tcluno source # nor are there any instructions on how to build it set ::argv [list -urtp] package require tcluno_soffice set desk(desktop) [::tcluno_soffice::initDesktop] set desk(filterSequence) [$desk(desktop) tcluno::createUnoSequence Any] } ad_proc list_oo_ss {} { Setup a spreadsheet } { upvar desk desk upvar spreadsheet spreadsheet list_oo_create desk set spreadsheet [$desk(desktop) loadComponentFromURL "private:factory/scalc\ " "_blank" 0 $desk(filterSequence)] set sheets [$spreadsheet getSheets] set sheet [$sheets getByIndex 0] } ad_proc list_oo_ss_set_row {sheet rownum rowlist {col 0}} { Set a row of values from list } { foreach elm $rowlist { set cell [$sheet getCellByPosition $col $row] if {[llength $elm] == 1} { set val $elm } else { foreach {p v} $elm { if {$p ne "value"} { $cell setPropertyValue $p $v } else { set val $v } } } list_oo_ss_cell_setvalue $cell $val incr col } } ad_proc list_oo_ss_cell_setvalue {cell value} { Set a value, figure out the datatype } { if {[string is double -strict $value]} { set type setValue } else { set type setString } $cell $type $value } ad_proc list_oo_save_doc {deskName document filename} { upvar $deskName desk switch [file extension $filename] { .xls { set msExcelFilter [$desk(desktop) tcluno::createUnoStructHelper com\ .sun.star.beans.PropertyValue {FilterName -1 {MS Excel 97} 0}] $desk(desktop) tcluno::appendUnoSequence $desk(filterSequence) $msE\ xcelFilter } } $document storeAsURL file://$filename $desk(filterSequence) } ad_proc list_multirow_to_xls {name filename} { Take a multirow and output as an XLS file } { list_oo_ss set sheets [$spreadsheet getSheets] set sheet [$sheets getByIndex 0] # Creates the '_eval' columns and aggregates template::list::prepare_for_rendering -name $name template::list::get_reference -name $name set __list_name $name set __output {} set __groupby $list_properties(groupby) set __rownum 0 set __colnum 0 foreach __element_name $list_properties(elements) { template::list::element::get_reference -list_name $name -element_name $\ __element_name if {!$element_properties(hide_p)} { lappend __csv_cols $__element_name set cell [$sheet getCellByPosition $__colnum $__rownum] $cell setPropertyValue CharWeight 200 $cell setString [template::list::csv_quote $element_properties(labe\ l)] incr __colnum } } incr __rownum set __rowcount [template::multirow size $list_properties(multirow)] # Output rows ns_log notice "Multirow = $list_properties(multirow) size = $__rowcount" template::multirow foreach $list_properties(multirow) { ns_log notice "__rownum=$__rownum" set group_lastnum_p 0 if {$__groupby ne ""} { if {$__rownum < $__rowcount} { # check if the next row's group column is the same as this one set next_group [template::multirow get $list_properties(multiro\ w) [expr {$__rownum + 1}] $__groupby] if {[set $__groupby] ne $next_group} { set group_lastnum_p 1 } } else { set group_lastnum_p 1 } } if {$__groupby eq "" \ || $group_lastnum_p} { set __cols [list] set __colnum 0 ns_log notice "__colnum=$__colnum" foreach __element_name $__csv_cols { set val "" if {![string match "*___*_group" $__element_name]} { template::list::element::get_reference \ -list_name $__list_name \ -element_name $__element_name \ -local_name __element_properties if { [info exists $__element_properties(csv_col)] } { set val [set $__element_properties(csv_col)] } else { set val [set $__element_name] } } { set val [set $__element_name] } set cell [$sheet getCellByPosition $__colnum $__rownum] list_oo_ss_cell_setvalue $cell $val incr __colnum } } incr __rownum } set oh [ns_conn outputheaders] if {$filename eq ""} {set filename $__list_name} set filename [string map {" " - ".xls" ""} $filename] set tmpdir [ns_mktemp /var/www/tmp/XXXXXX] set tmpfile [file join $tmpdir $filename] ns_log notice "TMPFILE = $tmpfile" list_oo_save_doc desk $spreadsheet $tmpfile ns_set cput $oh Content-Disposition "attachment;filename=${filename}.xls" ns_returnfile 200 application/msexcel $tmpfile file delete -force $tmpfile }