libraries/import/xlsx.php

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