[LON-CAPA-cvs] cvs: loncom /interface lonspreadsheet.pm
matthew
lon-capa-cvs@mail.lon-capa.org
Thu, 07 Nov 2002 15:37:02 -0000
This is a MIME encoded message
--matthew1036683422
Content-Type: text/plain
matthew Thu Nov 7 10:37:02 2002 EDT
Modified files:
/loncom/interface lonspreadsheet.pm
Log:
Added "Multi-Sheet Excel" output. Tested on small class, not on large.
Broke the Excel handling routines into smaller pieces to facilitate this.
Multi-Sheet Excel (recursive excel as it is known internally) is only
available at the course sheet level.
--matthew1036683422
Content-Type: text/plain
Content-Disposition: attachment; filename="matthew-20021107103702.txt"
Index: loncom/interface/lonspreadsheet.pm
diff -u loncom/interface/lonspreadsheet.pm:1.134 loncom/interface/lonspreadsheet.pm:1.135
--- loncom/interface/lonspreadsheet.pm:1.134 Wed Nov 6 15:00:13 2002
+++ loncom/interface/lonspreadsheet.pm Thu Nov 7 10:37:02 2002
@@ -1,5 +1,5 @@
#
-# $Id: lonspreadsheet.pm,v 1.134 2002/11/06 20:00:13 matthew Exp $
+# $Id: lonspreadsheet.pm,v 1.135 2002/11/07 15:37:02 matthew Exp $
#
# Copyright Michigan State University Board of Trustees
#
@@ -1109,9 +1109,8 @@
# ========================================================== End of Spreadsheet
# =============================================================================
-
#
-# Procedures for screen output
+# Procedures for spreadsheet output
#
# --------------------------------------------- Produce output row n from sheet
@@ -1145,9 +1144,15 @@
return @sortidx;
}
-########################################################################
-########################################################################
-
+#############################################################
+### ###
+### Spreadsheet Output Routines ###
+### ###
+#############################################################
+
+############################################
+## HTML output routines ##
+############################################
sub html_editable_cell {
my ($cell,$bgcolor) = @_;
my $result;
@@ -1179,9 +1184,6 @@
return ' '.$value.' ';
}
-########################################################################
-########################################################################
-
sub outsheet_html {
my ($sheet,$r) = @_;
my ($num_uneditable,$realm,$row_type);
@@ -1351,6 +1353,9 @@
return 1;
}
+############################################
+## csv output routines ##
+############################################
sub outsheet_csv {
my ($sheet,$r) = @_;
my $csvdata = '';
@@ -1394,19 +1399,97 @@
return 1;
}
+############################################
+## Excel output routines ##
+############################################
+sub outsheet_recursive_excel {
+ my ($sheet,$r) = @_;
+ return undef if ($sheet->{'sheettype'} ne 'classcalc');
+ my ($workbook,$filename) = &create_excel_spreadsheet($sheet,$r);
+ return undef if (! defined($workbook));
+ #
+ # Create main worksheet
+ my $main_worksheet = $workbook->addworksheet('main');
+ #
+ # Figure out who the students are
+ my %f=&getformulas($sheet);
+ my $count = 0;
+ $r->print("<br />\n");
+ $r->rflush();
+ foreach (keys(%f)) {
+ next if ($_!~/^A(\d+)/ || $1 == 0 || ($f{$_}=~/^[!~-]/));
+ $count++;
+ my ($sname,$sdom) = split(':',$f{$_});
+ my $student_excel_worksheet=$workbook->addworksheet($sname.'@'.$sdom);
+ # Create a new spreadsheet
+ my $studentsheet = &makenewsheet($sname,$sdom,'studentcalc',undef);
+ # Read in the spreadsheet definition
+ &readsheet($studentsheet,'default_studentcalc');
+ # Determine the structure (contained assessments, etc) of the sheet
+ &updatesheet($studentsheet);
+ # Load in the (possibly cached) data from the assessment sheets
+ &loadrows($studentsheet);
+ # Compute the sheet
+ &calcsheet($studentsheet);
+ &Apache::lonnet::logthis("Sheet value for A0 = ".$sheet->{'values'}->{'A0'});
+ # Stuff the sheet into excel
+ &export_sheet_as_excel($studentsheet,$student_excel_worksheet);
+ if ($count % 5 == 0) {
+ $r->print($count.' students completed<br />');
+ $r->rflush();
+ }
+ }
+ #
+ $r->print('All students spreadsheets completed<br />');
+ $r->rflush();
+ #
+ # &export_sheet_as_excel fills $worksheet with the data from $sheet
+ &export_sheet_as_excel($sheet,$main_worksheet);
+ #
+ $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_excel {
my ($sheet,$r) = @_;
+ my ($workbook,$filename) = &create_excel_spreadsheet($sheet,$r);
+ return undef if (! defined($workbook));
+ 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);
+ #
+ # &export_sheet_as_excel fills $worksheet with the data from $sheet
+ &export_sheet_as_excel($sheet,$worksheet);
+ #
+ $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 create_excel_spreadsheet {
+ my ($sheet,$r) = @_;
my $filename = '/prtspool/'.
$ENV{'user.name'}.'_'.$ENV{'user.domain'}.'_'.
time.'_'.rand(1000000000).'.xls';
- &Apache::lonnet::logthis("spreadsheet:filename = ".$filename);
+ #&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;
+ return undef;
}
#
# The spreadsheet stores temporary data in files, then put them
@@ -1416,15 +1499,12 @@
$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);
+ return ($workbook,$filename);
+}
+
+sub export_sheet_as_excel {
+ my $sheet = shift;
+ my $worksheet = shift;
####################################
# Prepare to output rows
####################################
@@ -1446,18 +1526,22 @@
}
$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;
+ return;
}
+############################################
+## XML output routines ##
+############################################
sub outsheet_xml {
my ($sheet,$r) = @_;
+ ## Someday XML
+ ## Will be rendered for the user
+ ## But not on this day
}
+##
+## Outsheet - calls other outsheet_* functions
+##
sub outsheet {
my ($r,$sheet)=@_;
if (! exists($ENV{'form.output'})) {
@@ -1467,6 +1551,8 @@
&outsheet_csv($sheet,$r);
} elsif (lc($ENV{'form.output'}) eq 'excel') {
&outsheet_excel($sheet,$r);
+ } elsif (lc($ENV{'form.output'}) eq 'recursive excel') {
+ &outsheet_recursive_excel($sheet,$r);
# } elsif (lc($ENV{'form.output'}) eq 'xml' ) {
# &outsheet_xml($sheet,$r);
} else {
@@ -2074,7 +2160,7 @@
# ------------------------------------------------ Load data for one assessment
-sub loadstudent {
+sub loadstudent{
my ($sheet)=@_;
my %c=();
my %f=&getformulas($sheet);
@@ -2126,8 +2212,10 @@
#
sub loadcourse {
my ($sheet,$r)=@_;
+ #
my %c=();
my %f=&getformulas($sheet);
+ #
my $total=0;
foreach (keys(%f)) {
if ($_=~/^A(\d+)/) {
@@ -2450,7 +2538,7 @@
}
#
# Not cached
- #
+ #
my ($newsheet)=&makenewsheet($uname,$udom,$stype,$usymb);
&readsheet($newsheet,$fn);
&updatesheet($newsheet);
@@ -2579,10 +2667,14 @@
sub handler {
my $r=shift;
+ my ($sheettype) = ($r->uri=~/\/(\w+)$/);
+
if (! exists($ENV{'form.Status'})) {
$ENV{'form.Status'} = 'Active';
}
- if (! exists($ENV{'form.output'})) {
+ if ( ! exists($ENV{'form.output'}) ||
+ ($sheettype ne 'classcalc' &&
+ lc($ENV{'form.output'}) eq 'recursive excel')) {
$ENV{'form.output'} = 'HTML';
}
# Check this server
@@ -2688,8 +2780,7 @@
undef %updatedata;
}
# Read new sheet or modified worksheet
- $r->uri=~/\/(\w+)$/;
- my ($sheet)=&makenewsheet($aname,$adom,$1,$ENV{'form.usymb'});
+ my ($sheet)=&makenewsheet($aname,$adom,$sheettype,$ENV{'form.usymb'});
#
# If a new formula had been entered, go from work copy
if ($ENV{'form.unewfield'}) {
@@ -2840,12 +2931,19 @@
# CSV format checkbox (classcalc sheets only)
$r->print(' Output as <select name="output" size="1" onClick="submit()">'.
"\n");
- foreach my $mode (qw/HTML CSV EXCEL/) {
+ 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");
+ }
+ if ($sheet->{'sheettype'} eq 'classcalc') {
+ $r->print('<option value="recursive excel"');
+ if ($ENV{'form.output'} eq 'recursive excel') {
+ $r->print(' selected ');
+ }
+ $r->print(">Multi-Sheet Excel</option>\n");
}
$r->print("</select>\n");
#
--matthew1036683422--