[Summary view] [Print] [Text view]

   1  <?php
   2  // This file is part of Moodle - http://moodle.org/
   3  //
   4  // Moodle is free software: you can redistribute it and/or modify
   5  // it under the terms of the GNU General Public License as published by
   6  // the Free Software Foundation, either version 3 of the License, or
   7  // (at your option) any later version.
   8  //
   9  // Moodle is distributed in the hope that it will be useful,
  10  // but WITHOUT ANY WARRANTY; without even the implied warranty of
  11  // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  12  // GNU General Public License for more details.
  13  //
  14  // You should have received a copy of the GNU General Public License
  15  // along with Moodle.  If not, see <http://www.gnu.org/licenses/>.
  16  
  17  /**
  18   * This script produces sample Excel and ODF spreadsheets.
  19   *
  20   * @package    core
  21   * @copyright  2013 Petr Skoda {@link http://skodak.org}
  22   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  23   */
  24  
  25  require(__DIR__ . '/../../../config.php');
  26  require_once($CFG->libdir . '/excellib.class.php');
  27  require_once($CFG->libdir . '/odslib.class.php');
  28  
  29  $context = context_system::instance();
  30  $PAGE->set_url('/lib/tests/other/spreadsheettestpage.php');
  31  $PAGE->set_context($context);
  32  
  33  require_login();
  34  require_capability('moodle/site:config', $context);
  35  
  36  $type = optional_param('type', '', PARAM_ALPHANUM);
  37  
  38  if (!in_array($type, array('excel2007', 'ods'))) {
  39      $type = '';
  40  }
  41  
  42  if (!$type) {
  43      $PAGE->set_title('Moodle spreadsheet export test');
  44      $PAGE->set_heading('Moodle spreadsheet export test');
  45  
  46      echo $OUTPUT->header();
  47      echo $OUTPUT->box_start();
  48  
  49      $notes = '
  50  Tested with:
  51  
  52  * MS Excel Viewer 2003 (with Compatibility Pack), 2010
  53  * LibreOffice 3.5, 3.6
  54  * NeoOffice 3.3
  55  * Apple Numbers \'09 (2.3) and Preview
  56  * Google Drive spreadsheet import
  57  * IBM Lotus Symphony 3.0.1
  58  * Gnumeric 1.11
  59  * Calligra Suite 2.4, 2.5
  60  
  61  Known problems:
  62  
  63  * Excel 2007 borders appear too thick in LibreOffice
  64  * Excel 2007 can not be opened in Calligra Suite
  65  ';
  66  
  67      echo markdown_to_html($notes);
  68      echo $OUTPUT->box_end();
  69      echo $OUTPUT->single_button(new moodle_url($PAGE->url, array('type' => 'excel2007')), 'Test Excel 2007 format');
  70      echo $OUTPUT->single_button(new moodle_url($PAGE->url, array('type' => 'ods')), 'Test ODS format');
  71      echo $OUTPUT->footer();
  72      die;
  73  }
  74  
  75  if ($type === 'excel2007') {
  76      $workbook = new MoodleExcelWorkbook('moodletest.xlsx', 'Excel2007');
  77  } else if ($type === 'ods') {
  78      $workbook = new MoodleODSWorkbook('moodletest.ods');
  79  }
  80  
  81  $worksheet = array();
  82  
  83  $worksheet = $workbook->add_worksheet('Supported');
  84  
  85  $worksheet->hide_screen_gridlines();
  86  
  87  $worksheet->write_string(0, 0, 'Moodle worksheet export test', $workbook->add_format(array('color'=>'red', 'size'=>20, 'bold'=>1, 'italic'=>1)));
  88  $worksheet->set_row(0, 25);
  89  $worksheet->write(1, 0, 'Moodle release: '.$CFG->release, $workbook->add_format(array('size'=>8, 'italic'=>1)));
  90  
  91  $worksheet->set_column(0, 0, 20);
  92  $worksheet->set_column(1, 1, 30);
  93  $worksheet->set_column(2, 2, 5);
  94  $worksheet->set_column(3, 3, 30);
  95  $worksheet->set_column(4, 4, 20);
  96  
  97  $miniheading = $workbook->add_format(array('size'=>15, 'bold'=>1, 'italic'=>1, 'underline'=>1));
  98  
  99  
 100  $worksheet->write(2, 0, 'Cell types', $miniheading);
 101  $worksheet->set_row(2, 20);
 102  $worksheet->set_row(3, 5);
 103  
 104  $worksheet->write(4, 0, 'String');
 105  $worksheet->write_string(4, 1, 'Žluťoučký koníček');
 106  
 107  $worksheet->write(5, 0, 'Number as string');
 108  $worksheet->write_string(5, 1, 3.14159);
 109  
 110  $worksheet->write(6, 0, 'Integer');
 111  $worksheet->write_number(6, 1, 666);
 112  
 113  $worksheet->write(7, 0, 'Float');
 114  $worksheet->write_number(7, 1, 3.14159);
 115  
 116  $worksheet->write(8, 0, 'URL');
 117  $worksheet->write_url(8, 1, 'http://moodle.org');
 118  
 119  $worksheet->write(9, 0, 'Date (now)');
 120  $worksheet->write_date(9, 1, time());
 121  
 122  $worksheet->write(10, 0, 'Formula');
 123  $worksheet->write(10, 1, '=1+2');
 124  
 125  $worksheet->write(11, 0, 'Blank');
 126  $worksheet->write_blank(11, 1, $workbook->add_format(array('bg_color'=>'silver')));
 127  
 128  
 129  $worksheet->write(14, 0, 'Text formats', $miniheading);
 130  $worksheet->set_row(14, 20);
 131  $worksheet->set_row(15, 5);
 132  
 133  // Following writes use alternative format array.
 134  $worksheet->write(16, 0, 'Bold', array('bold'=>1));
 135  $worksheet->write(17, 0, 'Italic', array('italic'=>1));
 136  $worksheet->write(18, 0, 'Single underline', array('underline'=>1));
 137  $worksheet->write(19, 0, 'Double underline', array('underline'=>2));
 138  $worksheet->write(20, 0, 'Strikeout', array('strikeout'=>1));
 139  $worksheet->write(21, 0, 'Superscript', array('script'=>1));
 140  $worksheet->write(22, 0, 'Subscript', array('script'=>2));
 141  $worksheet->write(23, 0, 'Red', array('color'=>'red'));
 142  
 143  
 144  $worksheet->write(25, 0, 'Text align', $miniheading);
 145  $worksheet->set_row(25, 20);
 146  $worksheet->set_row(26, 5);
 147  
 148  $worksheet->write(27, 0, 'Wrapped text - Žloťoučký koníček', $workbook->add_format(array('text_wrap'=>true, 'border'=>1)));
 149  $worksheet->set_row(27, 30);
 150  $worksheet->write(27, 1, 'All centered', $workbook->add_format(array('v_align'=>'center', 'h_align'=>'center', 'border'=>1)));
 151  $worksheet->write(28, 0, 'Top', $workbook->add_format(array('align'=>'top', 'border'=>1)));
 152  $worksheet->set_row(28, 25);
 153  $worksheet->write(29, 0, 'Vcenter', $workbook->add_format(array('align'=>'vcenter', 'border'=>1)));
 154  $worksheet->set_row(29, 25);
 155  $worksheet->write(30, 0, 'Bottom', $workbook->add_format(array('align'=>'bottom', 'border'=>1)));
 156  $worksheet->set_row(30, 25);
 157  $worksheet->write(28, 1, 'Left', $workbook->add_format(array('align'=>'left', 'border'=>1)));
 158  $worksheet->write(29, 1, 'Center', $workbook->add_format(array('align'=>'center', 'border'=>1)));
 159  $worksheet->write(30, 1, 'Right', $workbook->add_format(array('align'=>'right', 'border'=>1)));
 160  
 161  $worksheet->write(32, 0, 'Number formats', $miniheading);
 162  $worksheet->set_row(32, 20);
 163  $worksheet->set_row(33, 5);
 164  
 165  $numbers[1] = '0';
 166  $numbers[2] = '0.00';
 167  $numbers[3] = '#,##0';
 168  $numbers[4] = '#,##0.00';
 169  $numbers[11] = '0.00E+00';
 170  $numbers[12] = '# ?/?';
 171  $numbers[13] = '# ??/??';
 172  $numbers[14] = 'mm-dd-yy';
 173  $numbers[15] = 'd-mmm-yy';
 174  $numbers[16] = 'd-mmm';
 175  $numbers[17] = 'mmm-yy';
 176  $numbers[22] = 'm/d/yy h:mm';
 177  $numbers[49] = '@';
 178  
 179  
 180  $worksheet->write_string(34, 0, '1: 0');
 181  $worksheet->write_number(34, 1, 1003.14159, array('num_format'=>1));
 182  $worksheet->write_string(35, 0, '2: 0.00');
 183  $worksheet->write_number(35, 1, 1003.14159, array('num_format'=>2));
 184  $worksheet->write_string(36, 0, '3: #,##0');
 185  $worksheet->write_number(36, 1, 1003.14159, array('num_format'=>3));
 186  $worksheet->write_string(37, 0, '3: #,##0.00');
 187  $worksheet->write_number(37, 1, 1003.14159, array('num_format'=>4));
 188  $worksheet->write_string(38, 0, '11: 0.00E+00');
 189  $worksheet->write_number(38, 1, 3.14159, array('num_format'=>11));
 190  $worksheet->write_string(39, 0, '12: # ?/?');
 191  $worksheet->write_number(39, 1, 3.14, array('num_format'=>12));
 192  $worksheet->write_string(40, 0, '13: # ??/??');
 193  $worksheet->write_number(40, 1, 3.14, array('num_format'=>13));
 194  $worksheet->write_string(41, 0, '15: d-mmm-yy');
 195  $worksheet->write_date(41, 1, time(), array('num_format'=>15));
 196  $worksheet->write_string(42, 0, '22: m/d/yy h:mm');
 197  $worksheet->write_date(42, 1, time(), array('num_format'=>22));
 198  
 199  
 200  
 201  $worksheet->write(2, 3, 'Borders', $miniheading);
 202  
 203  $worksheet->write(4, 3, 'Left', $workbook->add_format(array('left'=>'1')));
 204  $worksheet->write(6, 3, 'Bottom', $workbook->add_format(array('bottom'=>'1')));
 205  $worksheet->write(8, 3, 'Right', $workbook->add_format(array('right'=>'1')));
 206  $worksheet->write(10, 3, 'Top', $workbook->add_format(array('top'=>'1')));
 207  $worksheet->write(12, 3, 'Thick borders', $workbook->add_format(array('border'=>'2')));
 208  
 209  
 210  $worksheet->write(14, 3, 'Background colours', $miniheading);
 211  
 212  $worksheet->write(16, 3, 'Yellow', $workbook->add_format(array('bg_color'=>'yellow')));
 213  $worksheet->write(17, 3, 'Red', $workbook->add_format(array('bg_color'=>'red')));
 214  $worksheet->write(18, 3, 'Green', $workbook->add_format(array('bg_color'=>'green')));
 215  $worksheet->write(19, 3, 'Blue', $workbook->add_format(array('bg_color'=>12)));
 216  $worksheet->write(20, 3, 'Cyan', $workbook->add_format(array('bg_color'=>'#00FFFF')));
 217  
 218  
 219  $worksheet->write(25, 3, 'Cell merging', $miniheading);
 220  
 221  $worksheet->merge_cells(27, 3, 28, 3);
 222  $worksheet->write(27, 3, 'Vertical merging of cells', $workbook->add_format(array('bg_color'=>'silver')));
 223  
 224  $worksheet->merge_cells(30, 3, 30, 4);
 225  $worksheet->write(30, 3, 'Horizontal merging of cells', $workbook->add_format(array('pattern'=>1, 'bg_color'=>'silver')));
 226  $worksheet->set_column(4, 4, 5);
 227  
 228  $worksheet->set_row(44, null, null, true);
 229  $worksheet->write(44, 0, 'Hidden row', array('bg_color'=>'yellow'));
 230  
 231  $worksheet->set_column(5, 5, null, null, true);
 232  $worksheet->write(0, 5, 'Hidden column', array('bg_color'=>'yellow'));
 233  
 234  
 235  $worksheet->write(45, 0, 'Outline row 1');
 236  $worksheet->set_row(45, null, null, false, 1);
 237  $worksheet->write(46, 0, 'Outline row 2');
 238  $worksheet->set_row(46, null, null, false, 2);
 239  
 240  $worksheet->write(0, 6, 'Outline column 1');
 241  $worksheet->set_column(6, 6, 20, null, false, 1);
 242  $worksheet->write(0, 7, 'Outline column 2');
 243  $worksheet->set_column(7, 7, 20, null, false, 2);
 244  
 245  
 246  
 247  // Some unfinished stuff.
 248  
 249  $worksheet2 = $workbook->add_worksheet('Unsupported');
 250  $worksheet2->write(0, 0, 'Incomplete and missing features', $workbook->add_format(array('size'=>20, 'bold'=>1, 'italic'=>1)));
 251  $worksheet2->set_row(0, 25);
 252  $worksheet2->set_column(1, 1, 25);
 253  
 254  $worksheet2->write(3, 1, 'Gray row - buggy');
 255  $worksheet2->set_row(3, null, array('bg_color'=>'silver'));
 256  $worksheet2->write(2, 6, 'Gray column - buggy');
 257  $worksheet2->set_column(6, 6, 20, array('bg_color'=>'silver'));
 258  
 259  $worksheet2->hide_gridlines();
 260  
 261  $worksheet2->write(5, 0, 'Outline text - not implemented', array('outline'=>1));
 262  $worksheet2->write(6, 0, 'Shadow text - not implemented', array('outline'=>1));
 263  
 264  $worksheet2->write(8, 0, 'Pattern 1');
 265  $worksheet2->write_blank(8, 1, array('pattern'=>1));
 266  $worksheet2->write(9, 0, 'Pattern 2');
 267  $worksheet2->write_blank(9, 1, array('pattern'=>2));
 268  $worksheet2->write(10, 0, 'Pattern 3');
 269  $worksheet2->write_blank(10, 1, array('pattern'=>3));
 270  
 271  
 272  // Other worksheet tests follow.
 273  
 274  $worksheet3 = $workbook->add_worksheet('Žlutý:koníček?přeskočil mrňavoučký potůček');
 275  $worksheet3->write(1, 0, 'Test long Unicode worksheet name.');
 276  
 277  
 278  $worksheet4 = $workbook->add_worksheet('');
 279  $worksheet4->write(1, 0, 'Test missing worksheet name.');
 280  
 281  $workbook->close();
 282  die;