[LON-CAPA-cvs] cvs: loncom /interface lonspreadsheet.pm

matthew lon-capa-cvs@mail.lon-capa.org
Wed, 06 Nov 2002 20:00:13 -0000


matthew		Wed Nov  6 15:00:13 2002 EDT

  Modified files:              
    /loncom/interface	lonspreadsheet.pm 
  Log:
  Module now uses Spreadsheet::WriteExcel
  csv download now writes to a temp file in the prtspool directory and serves
  up a link to the user.
  Excel output is now available - &outsheet_excel writes the currently 
  displayed spreadsheet to an excel file in the prtspool directory and gives the
  user a link.
  &format_plain_rowlabel has been renamed and rewritten to &format_excel_rowlabel
  The output method is now specified with a <select /> form.  html is the 
  default.
  The buttons to insert rows in the spreadsheet have been removed temporarily.
  
  
  
Index: loncom/interface/lonspreadsheet.pm
diff -u loncom/interface/lonspreadsheet.pm:1.133 loncom/interface/lonspreadsheet.pm:1.134
--- loncom/interface/lonspreadsheet.pm:1.133	Tue Nov  5 10:00:27 2002
+++ loncom/interface/lonspreadsheet.pm	Wed Nov  6 15:00:13 2002
@@ -1,5 +1,5 @@
 #
-# $Id: lonspreadsheet.pm,v 1.133 2002/11/05 15:00:27 matthew Exp $
+# $Id: lonspreadsheet.pm,v 1.134 2002/11/06 20:00:13 matthew Exp $
 #
 # Copyright Michigan State University Board of Trustees
 #
@@ -62,6 +62,8 @@
 use HTML::TokeParser;
 use Apache::lonhtmlcommon;
 use Apache::loncoursedata;
+use Apache::File();
+use Spreadsheet::WriteExcel;
 #
 # Caches for coursewide information 
 #
@@ -1370,13 +1372,86 @@
         @Values = ();
     }
     #
-    $r->print('<pre>'.$csvdata."\n</pre>");
+    # Write the CSV data to a file and serve up a link
+    #
+    my $filename = '/prtspool/'.
+        $ENV{'user.name'}.'_'.$ENV{'user.domain'}.'_'.
+        time.'_'.rand(1000000000).'.csv';
+    my $file;
+    unless ($file = Apache::File->new('>'.'/home/httpd'.$filename)) {
+        $r->log_error("Couldn't open $filename for output $!");
+        $r->print("Problems occured in writing the csv file.  ".
+                  "This error has been logged.  ".
+                  "Please alert your LON-CAPA administrator.");
+        $r->print("<pre>\n".$csvdata."</pre>\n");
+        return 0;
+    }
+    print $file $csvdata;
+    close($file);
+    $r->print('<br /><br />'.
+              '<a href="'.$filename.'">Your CSV spreadsheet.</a>'."\n");
     #
     return 1;
 }
 
 sub outsheet_excel {
     my ($sheet,$r) = @_;
+    my $filename = '/prtspool/'.
+        $ENV{'user.name'}.'_'.$ENV{'user.domain'}.'_'.
+        time.'_'.rand(1000000000).'.xls';
+    &Apache::lonnet::logthis("spreadsheet:filename = ".$filename);
+    my $workbook  = Spreadsheet::WriteExcel->new('/home/httpd'.$filename);
+    if (! defined($workbook)) {
+        $r->log_error("Error creating excel spreadsheet $filename: $!");
+        $r->print("Problems creating new Excel file.  ".
+                  "This error has been logged.  ".
+                  "Please alert your LON-CAPA administrator");
+        return 0;
+    }
+    #
+    # The spreadsheet stores temporary data in files, then put them
+    # together.  If needed we should be able to disable this (memory only).
+    # The temporary directory must be specified before calling 'addworksheet'.
+    # File::Temp is used to determine the temporary directory.
+    $workbook->set_tempdir('/home/httpd/perl/tmp');
+    #
+    # Determine the name to give the worksheet
+    my $sheetname;
+    if ($sheet->{'sheettype'} eq 'classcalc') {
+        $sheetname = 'Main';
+    } elsif ($sheet->{'sheettype'} eq 'studentcalc') {
+        $sheetname = $sheet->{'uname'}.'@'.$sheet->{'udom'};
+    } elsif ($sheet->{'sheettype'} eq 'assesscalc') {
+        $sheetname = $sheet->{'uname'}.'@'.$sheet->{'udom'}.' assessment';
+    }
+    my $worksheet = $workbook->addworksheet($sheetname);
+    ####################################
+    # Prepare to output rows
+    ####################################
+    my @Rows = &sort_indicies($sheet);
+    #
+    # Loop through the rows and output them one at a time
+    my $rows_output=0;
+    foreach my $rownum (@Rows) {
+        my ($rowlabel,@rowdata) = &get_row($sheet,$rownum);
+        my $cols_output = 0;
+        my $label = &format_excel_rowlabel($rowlabel);
+        $worksheet->write($rows_output,$cols_output++,$label);
+        if (ref($label)) {
+            $cols_output = (scalar(@$label));
+        }
+        foreach my $cell (@rowdata) {
+            $worksheet->write($rows_output,$cols_output++,
+                              $cell->{'value'});
+        }
+        $rows_output++;
+    }
+    #
+    $workbook->close();
+    # Okay, the spreadsheet is taken care of, so give the user a link.
+    $r->print('<br /><br />'.
+              '<a href="'.$filename.'">Your Excel spreadsheet.</a>'."\n");
+    return 1;
 }
 
 sub outsheet_xml   {
@@ -1385,11 +1460,14 @@
 
 sub outsheet {
     my ($r,$sheet)=@_;
-    if (exists($ENV{'form.showcsv'})) {
+    if (! exists($ENV{'form.output'})) {
+        $ENV{'form.output'} = 'HTML';
+    }
+    if (lc($ENV{'form.output'}) eq 'csv') {
         &outsheet_csv($sheet,$r);
-#    } elsif (exists($ENV{'form.excel'})) {
-#        &outsheet_excel($sheet,$r);
-#    } elsif (exists($ENV{'form.xml'})) {
+    } elsif (lc($ENV{'form.output'}) eq 'excel') {
+        &outsheet_excel($sheet,$r);
+#    } elsif (lc($ENV{'form.output'}) eq 'xml' ) {
 #        &outsheet_xml($sheet,$r);
     } else {
         &outsheet_html($sheet,$r);
@@ -1533,6 +1611,8 @@
     $sheet->{'cnum'}  = $ENV{'course.'.$ENV{'request.course.id'}.'.num'};
     $sheet->{'cdom'}  = $ENV{'course.'.$ENV{'request.course.id'}.'.domain'};
     $sheet->{'chome'} = $ENV{'course.'.$ENV{'request.course.id'}.'.home'};
+    $sheet->{'coursedesc'} = $ENV{'course.'.$ENV{'request.course.id'}.
+                                      'description'};
     $sheet->{'uhome'} = &Apache::lonnet::homeserver($uname,$udom);
     #
     #
@@ -1776,7 +1856,7 @@
     return '"'.$result.'"';
 }
 
-sub format_plain_rowlabel {
+sub format_excel_rowlabel {
     my $rowlabel = shift;
     return '' if ($rowlabel eq '');
     my ($type,$labeldata) = split(':',$rowlabel,2);
@@ -1787,8 +1867,10 @@
         $result = $title;
     } elsif ($type eq 'student') {
         my ($sname,$sdom,$fullname,$section,$id) = split(':',$labeldata);
-            $result = '"'.
-                join('","',($sname,$sdom,$fullname,$section,$id).'"');
+        $section = '' if (! defined($section));
+        $id      = '' if (! defined($id));
+        my @Data = ($sname,$sdom,$fullname,$section,$id);
+        $result = \@Data;
     } elsif ($type eq 'parameter') {
         $labeldata =~ s/<br>/ /g;
         $result = $labeldata;
@@ -2500,6 +2582,9 @@
     if (! exists($ENV{'form.Status'})) {
         $ENV{'form.Status'} = 'Active';
     }
+    if (! exists($ENV{'form.output'})) {
+        $ENV{'form.output'} = 'HTML';
+    }
     # Check this server
     my $loaderror=&Apache::lonnet::overloaderror($r);
     if ($loaderror) { return $loaderror; }
@@ -2753,10 +2838,17 @@
     $r->print('>');
     #
     # CSV format checkbox (classcalc sheets only)
-    $r->print(' Output CSV format: <input type="checkbox" '.
-              'name="showcsv" onClick="submit()"');
-    $r->print(' checked') if ($ENV{'form.showcsv'});
-    $r->print('>');
+    $r->print(' Output as <select name="output" size="1" onClick="submit()">'.
+              "\n");
+    foreach my $mode (qw/HTML CSV EXCEL/) {
+        $r->print('<option value="'.$mode.'"');
+        if ($ENV{'form.output'} eq $mode) {
+            $r->print(' selected ');
+        } 
+        $r->print('>'.$mode.'</option>'."\n");
+    }
+    $r->print("</select>\n");
+    #
     if ($sheet->{'sheettype'} eq 'classcalc') {
         $r->print('&nbsp;Student Status: '.
                   &Apache::lonhtmlcommon::StatusOptions
@@ -2764,13 +2856,13 @@
     }
     #
     # Buttons to insert rows
-    $r->print(<<ENDINSERTBUTTONS);
-<br>
-<input type='button' onClick='insertrow("top");' 
-value='Insert Row Top'>
-<input type='button' onClick='insertrow("bottom");' 
-value='Insert Row Bottom'><br>
-ENDINSERTBUTTONS
+#    $r->print(<<ENDINSERTBUTTONS);
+#<br>
+#<input type='button' onClick='insertrow("top");' 
+#value='Insert Row Top'>
+#<input type='button' onClick='insertrow("bottom");' 
+#value='Insert Row Bottom'><br>
+#ENDINSERTBUTTONS
     # Print out sheet
     &outsheet($r,$sheet);
     $r->print('</form></body></html>');