Forum OpenACS Q&A: Re: announcing: q-wiki, q-forms, spreadsheet

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

}