Translation update done using Pootle.
[phpmyadmin-themes.git] / libraries / import / xlsx.php
blob54796230f8075e83ac1d83c83cc5a937c71c24e5
1 <?php
2 /* vim: set expandtab sw=4 ts=4 sts=4: */
3 /**
4 * Microsoft Office Excel 2007 XLSX import plugin for phpMyAdmin
6 * @todo Pretty much everything
7 * @package phpMyAdmin-Import
8 */
10 if (! defined('PHPMYADMIN')) {
11 exit;
14 /**
15 * The possible scopes for $plugin_param are: 'table', 'database', and 'server'
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')
27 'options_text' => __('Options'),
29 /* We do not define function when plugin is just queried for information above */
30 return;
33 ini_set('memory_limit', '256M');
34 set_time_limit(120);
36 /* Append the PHPExcel directory to the include path variable */
37 set_include_path(get_include_path() . PATH_SEPARATOR . getcwd() . '/libraries/PHPExcel/');
39 require_once './libraries/PHPExcel/PHPExcel.php';
40 require_once './libraries/PHPExcel/PHPExcel/Reader/Excel2007.php';
42 $objReader = new PHPExcel_Reader_Excel2007();
43 $objReader->setReadDataOnly(true);
44 $objReader->setLoadAllSheets();
45 $objPHPExcel = $objReader->load($import_file);
47 $sheet_names = $objPHPExcel->getSheetNames();
48 $num_sheets = count($sheet_names);
50 $tables = array();
51 $tempRow = array();
52 $rows = array();
53 $col_names = array();
55 for ($s = 0; $s < $num_sheets; ++$s) {
56 $current_sheet = $objPHPExcel->getSheet($s);
58 $num_rows = $current_sheet->getHighestRow();
59 $num_cols = PMA_getColumnNumberFromName($current_sheet->getHighestColumn());
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();
66 if (! strcmp($cell, '')) {
67 $cell = 'NULL';
70 $tempRow[] = $cell;
73 $rows[] = $tempRow;
74 $tempRow = array();
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);
85 } else {
86 for ($n = 0; $n < $num_cols; ++$n) {
87 $col_names[] = PMA_getColumnAlphaName($n + 1);
91 $tables[] = array($sheet_names[$s], $col_names, $rows);
93 $col_names = array();
94 $rows = array();
98 unset($objPHPExcel);
99 unset($objReader);
100 unset($rows);
101 unset($tempRow);
102 unset($col_names);
104 /* Obtain the best-fit MySQL types for each column */
105 $analyses = array();
107 $len = count($tables);
108 for ($i = 0; $i < $len; ++$i) {
109 $analyses[] = PMA_analyzeTable($tables[$i]);
113 * string $db_name (no backquotes)
115 * array $table = array(table_name, array() column_names, array()() rows)
116 * array $tables = array of "$table"s
118 * array $analysis = array(array() column_types, array() column_sizes)
119 * array $analyses = array of "$analysis"s
121 * array $create = array of SQL strings
123 * array $options = an associative array of options
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;
135 /* Non-applicable parameters */
136 $create = NULL;
138 /* Created and execute necessary SQL statements from data */
139 PMA_buildSQL($db_name, $tables, $analyses, $create, $options);
141 unset($tables);
142 unset($analyses);
144 $finished = true;
145 $error = false;
147 /* Commit any possible data in buffers */
148 PMA_importRunQuery();