SpreadsheetDisplayedFields
Jump to navigation
Jump to search
Spreadsheet Export - Displayed Fields
Posted by Stephen Turner, MIT on Friday, June 10, 2005.
This mod allows a spreadsheet download of the fields displayed on the search results screen. The core RT spreadsheet download includes all ticket fields, irrespective of the fields shown on the screen.
There was a bug in an earlier version of this contrib. On our Linux RT installations, it worked fine. On Solaris, however, a string of end-of-line characters appeared in the spreadsheet before the real content. This meant you had to scroll down to see the content. See the two possible fixes below:
-- I tracked this down in 3.2 to a series of superfluous newlines in ColumnMap. Remove all blank lines in the file and the blank lines in the spreadsheet should disappear. (nick.humphries at pipex net)
-- Thanks Nick! I found something in this code that also fixes the problem. The m->comp() calls write their output to the output stream, and we aren't interested in the output here; we're only interested in the return values. So I added this argument to each comp() call:
{ store => \$mason_output }
This causes the output to be stored in the $mason_output variable and not to be written to the output stream. (Steve Turner)
In summary, the changes are:
- A new Mason component to format the results (/Search/ScreenResults.tsv).
- Modification of /Elements/ScrubHTML to allow all HTML to be removed from a string.
- Modification of /Search/Results.html to add a link to the new download.
The diffs below are based on RT 3.4.2 versions.
/Search/ScreenResults.tsv (new component)
Version 3.4.x
This component uses the same data-retrieval mechanism as the search results process.
<%INIT>
my $Tickets = RT::Tickets->new($session{'CurrentUser'});
$Tickets->FromSQL($ARGS{'Query'});
$Format ||= $RT::DefaultSearchResultFormat;
# Scrub the html of the format string to remove any potential nasties.
$Format = $m->comp('/Elements/ScrubHTML', Content => $Format, DenyAll => 1);
my (@Format) = $m->comp('/Elements/CollectionAsTable/ParseFormat', Format => $Format);
$r->content_type('application/vnd.ms-excel');
my @rows=();
my @header=();
my @cols=();
# used to store m->comp output - prevents the output from being
# written to the output stream. That's because we are not interested in the
# output for these comp() calls, we are interested in the return value.
my $mason_output;
foreach my $column (@Format) {
next if $column->{title} eq 'NEWLINE';
# Extract the column names from the Format array
foreach my $subcol ( @{ $column->{output} } ) {
if ( $subcol =~ /^__(.*?)__$/o ) {
my $col = $1;
push (@cols, $col);
}
}
# Determine the column titles
my $title = $column->{title};
$title =~ s/^__(.*)__$/$1/o;
my $ret = undef;
$ret =
$m->comp( { store => \$mason_output },
'/Elements/RT__Ticket/ColumnMap',
Name => $title,
Attr => 'title'
)
;
$title = $ret ? $ret : $title;
push @header, $title;
}
while ( my $Ticket = $Tickets->Next()) {
my $row;
foreach my $column (@cols) {
my $value = $m->comp({ store => \$mason_output },
'/Elements/RT__Ticket/ColumnMap',
Name => $column, Attr => 'value');
if ( $value && ref($value)) {
my @x = &{ $value }( $Ticket, 0 );
my $i=0;
$row->{ $column } = "";
foreach my $x (@x) {
$row->{ $column } .= ", " if $i > 0; # separating multivalues
if (ref ($x)) {
$row->{ $column } .= $$x;
} else {
$row->{ $column } .= $x;
}
$i++;
}
} else {
$row->{ $column } = $value ;
}
$row->{ $column } =~ s/, <br>//g; # ColumnMap adds <br> tags, which we don't want
}
push @rows, $row;
}
$m->out(join("\t", @header));
$m->out("\n");
foreach my $row (@rows) {
my @row;
foreach my $col(@cols) {
push @row, $row->{"$col"};
}
$m->out(join("\t",@row));
$m->out("\n");
}
$m->abort();
</%INIT>
<%ARGS>
$Query => undef
$Format => undef
$HideResults => 0
$Rows => 50
$Page => 1
$OrderBy => 'id'
$Order => 'ASC'
</%ARGS>
Version 3.8.x
This is adapted from the core Search/Results.tsv, but instead of using the hard-coded set of fields, it uses the logic from the previous version of ScreenResults.tsv to parse the header format. The header and value formatting has been modified to use ProcessColumnMapValue() to take advantage of the column mapping already defined in RT.
%# BEGIN BPS TAGGED BLOCK {{{
%#
%# COPYRIGHT:
%#
%# This software is Copyright (c) 1996-2009 Best Practical Solutions, LLC
%# <jesse@bestpractical.com>
%#
%# (Except where explicitly superseded by other copyright notices)
%#
%#
%# LICENSE:
%#
%# This work is made available to you under the terms of Version 2 of
%# the GNU General Public License. A copy of that license should have
%# been provided with this software, but in any event can be snarfed
%# from www.gnu.org.
%#
%# This work is distributed in the hope that it will be useful, but
%# WITHOUT ANY WARRANTY; without even the implied warranty of
%# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
%# General Public License for more details.
%#
%# You should have received a copy of the GNU General Public License
%# along with this program; if not, write to the Free Software
%# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
%# 02110-1301 or visit their web page on the internet at
%# http://www.gnu.org/licenses/old-licenses/gpl-2.0.html.
%#
%#
%# CONTRIBUTION SUBMISSION POLICY:
%#
%# (The following paragraph is not intended to limit the rights granted
%# to you to modify and distribute this software under the terms of
%# the GNU General Public License and is only of importance to you if
%# you choose to contribute your changes and enhancements to the
%# community by submitting them to Best Practical Solutions, LLC.)
%#
%# By intentionally submitting any modifications, corrections or
%# derivatives to this work, or any other work intended for use with
%# Request Tracker, to Best Practical Solutions, LLC, you confirm that
%# you are the copyright holder for those contributions and you grant
%# Best Practical Solutions, LLC a nonexclusive, worldwide, irrevocable,
%# royalty-free, perpetual, license to use, copy, create derivative
%# works based on those contributions, and sublicense and distribute
%# those contributions and any derivatives thereof.
%#
%# END BPS TAGGED BLOCK }}}
<%ARGS>
$Format => undef
$Query => ''
$OrderBy => 'id'
$Order => 'ASC'
</%ARGS>
<%INIT>
my $Tickets = RT::Tickets->new( $session{'CurrentUser'} );
$Tickets->FromSQL( $Query );
if ( $OrderBy =~ /\|/ ) {
# Multiple Sorts
my @OrderBy = split /\|/, $OrderBy;
my @Order = split /\|/, $Order;
$Tickets->OrderByCols(
map { { FIELD => $OrderBy[$_], ORDER => $Order[$_] } }
( 0 .. $#OrderBy )
);
}
else {
$Tickets->OrderBy( FIELD => $OrderBy, ORDER => $Order );
}
# Use the user's displayed fields
$Format ||= RT->Config->Get('DefaultSearchResultFormat');
# Scrube the html of the format string to remove any potential nasties.
$Format = $m->comp('/Elements/ScrubHTML', Content => $Format, DenyAll => 1);
my (@Format) = $m->comp('/Elements/CollectionAsTable/ParseFormat', Format => $Format);
my @attrs = qw(
id QueueObj->Name Subject Status
TimeEstimated TimeWorked TimeLeft
Priority FinalPriority
OwnerObj->Name
Requestors->MemberEmailAddressesAsString
Cc->MemberEmailAddressesAsString
AdminCc->MemberEmailAddressesAsString
DueObj->ISO ToldObj->ISO CreatedObj->ISO
ResolvedObj->ISO LastUpdatedObj->ISO
);
# HEADER: Map the fields from Format to proper column names
my @header = ();
my @cols = ();
foreach my $column (@Format)
{
next if (!defined ($column->{"attribute"}) || !$column->{"attribute"});
next if ($column->{"title"} && $column->{"title"} eq "NEWLINE");
# Extract the column names from the Format array
my $ret = $m->comp (
"/Elements/ColumnMap",
Class => "RT__Ticket",
Name => $column->{"attribute"},
Attr => "title",
);
my $colname = ProcessColumnMapValue ($ret, Arguments => [ $column->{"title"} ] );
push (@header, $colname);
$ret = $m->comp (
"/Elements/ColumnMap",
Class => "RT__Ticket",
Name => $column->{"attribute"},
Attr => "value",
);
push (@cols, $ret);
}
$r->content_type('application/vnd.ms-excel');
{
$m->out(join("\t", @header));
$m->out("\n");
$m->flush_buffer;
}
my $i = 0;
while ( my $Ticket = $Tickets->Next()) {
my @row;
foreach my $attr (@cols) {
push @row, ProcessColumnMapValue ($attr, Arguments => [ $Ticket, 0 ], Escape => 0);
}
# remove tabs from all field values, they screw up the tsv
for (@row) {
$_ = '' unless defined;
$_ =~ s/(?:\n|\r)//g;
$_ =~ s{\t}{ }g;
}
$m->out(join("\t",@row));
$m->out("\n");
unless (++$i%10) {
$i = 0;
$m->flush_buffer;
}
}
$m->abort();
</%INIT>
Elements/ScrubHTML (modified)
Minor modification - diff -u output:
--- share/html/Elements/ScrubHTML Tue Feb 1 09:20:40 2005 +++ local/html/Elements/ScrubHTML Fri Jun 10 15:50:27 2005 @@ -62,10 +62,12 @@ $scrubber->deny(qw[*]); $scrubber->allow( - qw[A B U P BR I HR BR SMALL EM FONT SPAN DIV UL OL LI DL DT DD]); + qw[A B U P BR I HR BR SMALL EM FONT SPAN DIV UL OL LI DL DT DD]) + unless $DenyAll; $scrubber->comment(0); return ( $scrubber->scrub($Content) ); </%init> <%args> $Content => undef +$DenyAll => undef </%args>
/Search/Results.html
Minor modification - diff -u output:
--- share/html/Search/Results.html Tue Feb 1 09:20:40 2005 +++ local/htmlSearch/Results.html Fri Jun 10 16:00:38 2005 @@ -79,7 +79,8 @@ <div align=right> <a href="<%$RT::WebPath%>/Search/Bulk.html<%$QueryString%>"><&|/l&>Update multiple tickets</&></a><br> <a href="<%$RT::WebPath%>/Search/Results.html<%$QueryString%>"><&|/l&>Bookmarkable link</&></a><br> -<a href="<%$RT::WebPath%>/Search/Results.tsv<%$QueryString%>"><&|/l&>spreadsheet</&></a> | +<a href="<%$RT::WebPath%>/Search/Results.tsv<%$QueryString%>"><&|/l&>spreadsheet (all ticket fields)</&></a> | +<a href="<%$RT::WebPath%>/Search/ScreenResults.tsv<%$QueryString%>"><&|/l&>spreadsheet (displayed ticket fields)</&></a> | <a href="<%$RT::WebPath%>/Search/Results.rdf<%$QueryString%>"><&|/l&>RSS</&></a> | <a href="<%$RT::WebPath%>/Tools/Offline.html<%$QueryString%>"><&|/l&>Work offline</&></a><br> <& /Elements/Callback, _CallbackName => 'SearchActions', QueryString => $QueryString&>
Note: The default ColumnMap entries for date/time should be changed to ISO and not AsString for Excel to parse them correctly.
User Notes
- Updated to work with v3.8.x by GinoLedesma
- This seems to indicate that it works well on 3.4.4 also: DuncanShannon 12/19/2005
http://archives.free.net.ph/message/20051019.135851.95ff58c9.en.html
- This seems to work fine on 3.4.5 as well.
- This only partially works on 3.6.x. The columns come out, but not in the correct order and the Scrub routines aren't working.
- Got it working with the following diff:
--- ScreenResults.tsv.orig Wed Jul 11 17:30:53 2007
+++ ScreenResults.tsv Wed Jul 11 17:29:57 2007
@@ -67,7 +67,8 @@
} else {
$row->{ $column } = $value ;
}
- $row->{ $column } =~ s/, <br>//g; # ColumnMap adds <br> tags, which we don't want
+ $row->{ $column } =~ s/, <br \/>//g; # ColumnMap adds <br /> tags, which we don't want
+ $row->{ $column } =~ s/<em>, (pending other Collection), <\/em>//g;
}
push @rows, $row;
}