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