$tempRow[] = $cell;
}
$rows[] = $tempRow;
$tempRow = array();
}
if ($_REQUEST['xls_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);
/**
* Handles the whole import logic
*
* @return void
*/
public function doImport()
{
global $db, $error, $timeout_passed, $finished;
$i = 0;
$len = 0;
$buffer = "";
/**
* Read in the file via PMA_importGetNextChunk so that
* it can process compressed files
*/
while (!($finished && $i >= $len) && !$error && !$timeout_passed) {
$data = PMA_importGetNextChunk();
if ($data === false) {
/* subtract data we didn't handle yet and stop processing */
$GLOBALS['offset'] -= strlen($buffer);
break;
} elseif ($data === true) {
/* Handle rest of buffer */
} else {
/* Append new data to buffer */
$buffer .= $data;
unset($data);
}
}
unset($data);
/**
* Disable loading of external XML entities.
*/
libxml_disable_entity_loader();
/**
* Load the XML string
*
* The option LIBXML_COMPACT is specified because it can
* result in increased performance without the need to
* alter the code in any way. It's basically a freebee.
*/
$xml = simplexml_load_string($buffer, "SimpleXMLElement", LIBXML_COMPACT);
unset($buffer);
if ($xml === false) {
$sheets = array();
$GLOBALS['message'] = PMA_Message::error(__('The XML file specified was either malformed or incomplete.' . ' Please correct the issue and try again.'));
$GLOBALS['error'] = true;
} else {
$root = $xml->children('office', true)->{'body'}->{'spreadsheet'};
if (empty($root)) {
$sheets = array();
$GLOBALS['message'] = PMA_Message::error(__('Could not parse OpenDocument Spreadsheet!'));
$GLOBALS['error'] = true;
} else {
$sheets = $root->children('table', true);
}
}
$tables = array();
$max_cols = 0;
$col_count = 0;
$col_names = array();
$tempRow = array();
$tempRows = array();
$rows = array();
/* Iterate over tables */
foreach ($sheets as $sheet) {
$col_names_in_first_row = isset($_REQUEST['ods_col_names']);
/* Iterate over rows */
foreach ($sheet as $row) {
$type = $row->getName();
if (strcmp('table-row', $type)) {
continue;
}
/* Iterate over columns */
foreach ($row as $cell) {
$text = $cell->children('text', true);
$cell_attrs = $cell->attributes('office', true);
if (count($text) != 0) {
$attr = $cell->attributes('table', true);
$num_repeat = (int) $attr['number-columns-repeated'];
$num_iterations = $num_repeat ? $num_repeat : 1;
for ($k = 0; $k < $num_iterations; $k++) {
$value = $this->getValue($cell_attrs, $text);
if (!$col_names_in_first_row) {
$tempRow[] = $value;
} else {
// MySQL column names can't end with a space
// character.
$col_names[] = rtrim($value);
}
++$col_count;
}
continue;
}
$attr = $cell->attributes('table', true);
$num_null = (int) $attr['number-columns-repeated'];
if ($num_null) {
if (!$col_names_in_first_row) {
for ($i = 0; $i < $num_null; ++$i) {
$tempRow[] = 'NULL';
++$col_count;
}
} else {
for ($i = 0; $i < $num_null; ++$i) {
$col_names[] = PMA_getColumnAlphaName($col_count + 1);
++$col_count;
}
}
} else {
if (!$col_names_in_first_row) {
$tempRow[] = 'NULL';
} else {
$col_names[] = PMA_getColumnAlphaName($col_count + 1);
}
++$col_count;
}
}
//Endforeach
/* Find the widest row */
if ($col_count > $max_cols) {
$max_cols = $col_count;
}
/* Don't include a row that is full of NULL values */
if (!$col_names_in_first_row) {
if ($_REQUEST['ods_empty_rows']) {
foreach ($tempRow as $cell) {
if (strcmp('NULL', $cell)) {
$tempRows[] = $tempRow;
break;
}
}
} else {
$tempRows[] = $tempRow;
}
}
$col_count = 0;
$col_names_in_first_row = false;
$tempRow = array();
}
/* Skip over empty sheets */
if (count($tempRows) == 0 || count($tempRows[0]) == 0) {
$col_names = array();
$tempRow = array();
$tempRows = array();
continue;
}
/**
* Fill out each row as necessary to make
* every one exactly as wide as the widest
* row. This included column names.
*/
/* Fill out column names */
for ($i = count($col_names); $i < $max_cols; ++$i) {
$col_names[] = PMA_getColumnAlphaName($i + 1);
}
/* Fill out all rows */
$num_rows = count($tempRows);
for ($i = 0; $i < $num_rows; ++$i) {
for ($j = count($tempRows[$i]); $j < $max_cols; ++$j) {
$tempRows[$i][] = 'NULL';
}
}
/* Store the table name so we know where to place the row set */
$tbl_attr = $sheet->attributes('table', true);
$tables[] = array((string) $tbl_attr['name']);
/* Store the current sheet in the accumulator */
$rows[] = array((string) $tbl_attr['name'], $col_names, $tempRows);
$tempRows = array();
$col_names = array();
$max_cols = 0;
}
unset($tempRow);
unset($tempRows);
unset($col_names);
unset($sheets);
unset($xml);
/**
* Bring accumulated rows into the corresponding table
*/
$num_tbls = count($tables);
for ($i = 0; $i < $num_tbls; ++$i) {
for ($j = 0; $j < count($rows); ++$j) {
if (strcmp($tables[$i][TBL_NAME], $rows[$j][TBL_NAME])) {
continue;
}
if (!isset($tables[$i][COL_NAMES])) {
$tables[$i][] = $rows[$j][COL_NAMES];
}
$tables[$i][ROWS] = $rows[$j][ROWS];
}
}
/* No longer needed */
unset($rows);
/* 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 */
list($db_name, $options) = $this->getDbnameAndOptions($db, 'ODS_DB');
/* 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);
/* Commit any possible data in buffers */
PMA_importRunQuery();
}
/**
* Test for PMA_getColumnAlphaName
*
* @param string $expected Expected result of the function
* @param int $num The column number
*
* @return void
*
* @dataProvider provGetColumnAlphaName
*/
function testGetColumnAlphaName($expected, $num)
{
$this->assertEquals($expected, PMA_getColumnAlphaName($num));
}
/**
* Returns the "Excel" column name (i.e. 1 = "A", 26 = "Z", 27 = "AA", etc.)
*
* This functions uses recursion to build the Excel column name.
*
* The column number (1-26) is converted to the responding
* ASCII character (A-Z) and returned.
*
* If the column number is bigger than 26 (= num of letters in alphabet),
* an extra character needs to be added. To find this extra character,
* the number is divided by 26 and this value is passed to another instance
* of the same function (hence recursion). In that new instance the number is
* evaluated again, and if it is still bigger than 26, it is divided again
* and passed to another instance of the same function. This continues until
* the number is smaller than 26. Then the last called function returns
* the corresponding ASCII character to the function that called it.
* Each time a called function ends an extra character is added to the column name.
* When the first function is reached, the last character is added and the complete
* column name is returned.
*
* @param int $num the column number
*
* @return string The column's "Excel" name
* @access public
*/
function PMA_getColumnAlphaName($num)
{
$A = 65;
// ASCII value for capital "A"
$col_name = "";
if ($num > 26) {
$div = (int) ($num / 26);
$remain = (int) ($num % 26);
// subtract 1 of divided value in case the modulus is 0,
// this is necessary because A-Z has no 'zero'
if ($remain == 0) {
$div--;
}
// recursive function call
$col_name = PMA_getColumnAlphaName($div);
// use modulus as new column number
$num = $remain;
}
if ($num == 0) {
// use 'Z' if column number is 0,
// this is necessary because A-Z has no 'zero'
$col_name .= mb_chr($A + 26 - 1);
} else {
// convert column number to ASCII character
$col_name .= mb_chr($A + $num - 1);
}
return $col_name;
}
/**
* Returns the "Excel" column name (i.e. 1 = "A", 26 = "Z", 27 = "AA", etc.)
* This algorithm only works up to ZZ. it fails on AAA (up to 701 columns)
*
* @author Derek Schaefer (derek.schaefer@gmail.com)
*
* @access public
*
* @uses chr()
* @param int $num
* @return string The column's "Excel" name
*/
function PMA_getColumnAlphaName($num)
{
/* ASCII value for capital "A" */
$A = 65;
$sCol = "";
$iRemain = 0;
/* This algorithm only works up to ZZ. it fails on AAA */
if ($num > 701) {
return $num;
} elseif ($num <= 26) {
if ($num == 0) {
$sCol = chr($A + 26 - 1);
} else {
$sCol = chr($A + $num - 1);
}
} else {
$iRemain = $num / 26 - 1;
if ($num % 26 == 0) {
$sCol = PMA_getColumnAlphaName($iRemain) . PMA_getColumnAlphaName($num % 26);
} else {
$sCol = chr($A + $iRemain) . PMA_getColumnAlphaName($num % 26);
}
}
return $sCol;
}
