libraries/import/xls.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * Microsoft Office Excel 97-2003 XLS import plugin for phpMyAdmin
   5  *
   6  * @todo    Pretty much everything
   7  * @version $Id$
   8  * @package phpMyAdmin-Import
   9  */
  10
  11 if (! defined('PHPMYADMIN')) {
  12     exit;
  13 }
  14
  15 /**
  16  * The possible scopes for $plugin_param are: 'table', 'database', and 'server'
  17  */
  18
  19 if (isset($plugin_list)) {
  20     $plugin_list['xls'] = array(
  21         'text' => 'strImportXLS',
  22         'extension' => 'xls',
  23         'options' => array(
  24                 array('type' => 'bool', 'name' => 'col_names', 'text' => 'strImportColNames'),
  25             ),
  26         'options_text' => 'strOptions',
  27         );
  28     /* We do not define function when plugin is just queried for information above */
  29     return;
  30 }
  31
  32 ini_set('memory_limit', '256M');
  33 set_time_limit(120);
  34
  35 /* Append the PHPExcel directory to the include path variable */
  36 set_include_path(get_include_path() . PATH_SEPARATOR . getcwd() . '/libraries/PHPExcel/');
  37
  38 require_once './libraries/PHPExcel/PHPExcel.php';
  39 require_once './libraries/PHPExcel/PHPExcel/Reader/Excel5.php';
  40
  41 $objReader = new PHPExcel_Reader_Excel5();
  42 $objReader->setReadDataOnly(true);
  43 $objReader->setLoadAllSheets();
  44 $objPHPExcel = $objReader->load($import_file);
  45
  46 $sheet_names = $objPHPExcel->getSheetNames();
  47 $num_sheets = count($sheet_names);
  48
  49 $tables = array();
  50 $tempRow = array();
  51 $rows = array();
  52 $col_names = array();
  53
  54 for ($s = 0; $s < $num_sheets; ++$s) {
  55     $current_sheet = $objPHPExcel->getSheet($s);
  56
  57     $num_rows = $current_sheet->getHighestRow();
  58     $num_cols = PMA_getColumnNumberFromName($current_sheet->getHighestColumn());
  59
  60     if ($num_rows != 1 && $num_cols != 1) {
  61         for ($r = 1; $r <= $num_rows; ++$r) {
  62             for ($c = 0; $c < $num_cols; ++$c) {
  63                 $cell = $current_sheet->getCellByColumnAndRow($c, $r)->getCalculatedValue();
  64
  65                 if (! strcmp($cell, '')) {
  66                     $cell = 'NULL';
  67                 }
  68
  69                 $tempRow[] = $cell;
  70             }
  71
  72             $rows[] = $tempRow;
  73             $tempRow = array();
  74         }
  75
  76         if ($_REQUEST['xls_col_names']) {
  77             $col_names = array_splice($rows, 0, 1);
  78             $col_names = $col_names[0];
  79             for ($j = 0; $j < $num_cols; ++$j) {
  80                 if (! strcmp('NULL', $col_names[$j])) {
  81                     $col_names[$j] = PMA_getColumnAlphaName($j + 1);
  82                 }
  83             }
  84         } else {
  85             for ($n = 0; $n < $num_cols; ++$n) {
  86                 $col_names[] = PMA_getColumnAlphaName($n + 1);
  87             }
  88         }
  89
  90         $tables[] = array($sheet_names[$s], $col_names, $rows);
  91
  92         $col_names = array();
  93         $rows = array();
  94     }
  95 }
  96
  97 unset($objPHPExcel);
  98 unset($objReader);
  99 unset($rows);
 100 unset($tempRow);
 101 unset($col_names);
 102
 103 /* Obtain the best-fit MySQL types for each column */
 104 $analyses = array();
 105
 106 $len = count($tables);
 107 for ($i = 0; $i < $len; ++$i) {
 108     $analyses[] = PMA_analyzeTable($tables[$i]);
 109 }
 110
 111 /**
 112  * string $db_name (no backquotes)
 113  *
 114  * array $table = array(table_name, array() column_names, array()() rows)
 115  * array $tables = array of "$table"s
 116  *
 117  * array $analysis = array(array() column_types, array() column_sizes)
 118  * array $analyses = array of "$analysis"s
 119  *
 120  * array $create = array of SQL strings
 121  *
 122  * array $options = an associative array of options
 123  */
 124
 125 /* Set database name to the currently selected one, if applicable */
 126 if (strlen($db)) {
 127     $db_name = $db;
 128     $options = array('create_db' => false);
 129 } else {
 130     $db_name = 'XLS_DB';
 131     $options = NULL;
 132 }
 133
 134 /* Non-applicable parameters */
 135 $create = NULL;
 136
 137 /* Created and execute necessary SQL statements from data */
 138 PMA_buildSQL($db_name, $tables, $analyses, $create, $options);
 139
 140 unset($tables);
 141 unset($analyses);
 142
 143 $finished = true;
 144 $error = false;
 145
 146 /* Commit any possible data in buffers */
 147 PMA_importRunQuery();
 148 ?>