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

Request notifications

Q-Wiki has page trash/delete and ability to insert OpenACS procedures for creating pages with dynamic content.

more at: http://openacs.org/xowiki/q-wiki

It depends on:

Q-Forms which provides an easy way to build forms and render form posts.

More at http://openacs.org/xowiki/q-forms

Spreadsheet package which has procedures for importing, exporting and manipulating tables. (Does not handle spreadsheets yet).

More at http://openacs.org/xowiki/spreadsheet

cheers!

Collapse
Posted by Dave Bauer on

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

}
Collapse
Posted by Benjamin Brink on
@Dave,

Very interesting. Thank you for posting. I'll look at the code tonight.

Collapse
Posted by Benjamin Brink on
@Dave, is the code you posted GPL'd?

The oo and variable as command looks like a really powerful way to get lots of work done with little fuss.

The spreadsheet package may end up duplicating some of the features of TclUNO.

TclUNO appears to be targeting spreadsheet super users / work teams. Looks to be very powerful. I'll keep it in mind for when I help found a spacecraft manufacturing venture. ;-)

The spreadsheet package is more aimed at collaboration by users not interested in maintaining a server on their desktop, and for integration with other web-apps for manipulating tables of data.