<?php
require_once 'CompoundDocument.inc.php';
require_once 'BiffWorkbook.inc.php';
$fileName = 'test.xls';
if (!is_readable($fileName)) {
die('Cannot read ' . $fileName);
}
$doc = new CompoundDocument('utf-8');
$doc->parse(file_get_contents($fileName));
$wb = new BiffWorkbook($doc);
$wb->parse();
foreach ($wb->sheets as $sheetName => $sheet) {
echo '<h1>' . $sheetName . '</h1>';
echo '<table cellspacing = "0">';
for ($row = 0; $row < $sheet->rows(); $row++) {
echo '<tr>';
for ($col = 0; $col < $sheet->cols(); $col++) {
if (!isset($sheet->cells[$row][$col])) {
continue;
}
$cell = $sheet->cells[$row][$col];
echo '<td style = "' . $cell->style->css() . '" rowspan = "' . $cell->rowspan . '" colspan = "' . $cell->colspan . '">';
echo is_null($cell->value) ? ' ' : $cell->value;
echo '</td>';
}
echo '</tr>';
}
echo '</table>';
}
function getItem()
{
$item = parent::getItem();
// Initial parsing of the Excel file to determine field types
if ($item->filename) {
require_once JPATH_COMPONENT_ADMINISTRATOR . DS . 'helper' . DS . 'BiffWorkbook.inc.php';
// set up sanitize filter
$config = array('separator' => '');
$filter = KFilter::factory('slug', $config);
try {
$doc = new CompoundDocument('utf-8');
$doc->parse(file_get_contents(JPATH_COMPONENT_ADMINISTRATOR . DS . 'uploads' . DS . $item->filename));
$wb = new BiffWorkbook($doc);
$wb->parse();
} catch (Exception $e) {
$app = JFactory::getApplication();
$app->enqueueMessage($e->getMessage());
return false;
}
foreach ($wb->sheets as $sheetName => $sheet) {
for ($col = 0; $col < $sheet->cols(); $col++) {
if (!isset($sheet->cells[0][$col])) {
continue;
}
$cell = $sheet->cells[0][$col];
if (is_null($cell->value)) {
// skip column
} else {
$columnname = $filter->sanitize($cell->value);
$isnumeric = true;
$isint = true;
$isdate = true;
$strlen = 0;
for ($row = 1; $row < $sheet->rows(); $row++) {
if (!isset($sheet->cells[$row][$col])) {
continue;
}
if (is_null($sheet->cells[$row][$col]->value)) {
continue;
}
$value = $sheet->cells[$row][$col]->value;
if (is_numeric($value)) {
if (is_int($value) == false) {
$isint = false;
}
} else {
$isnumeric = false;
$strlen = max($strlen, strlen($value));
// could it also be a date?
$datearr = date_parse_from_format("j/n/y", $value);
//needs PHP 5.3
if ($datearr['year'] && $datearr['month'] && $datearr['day']) {
// valid date
} else {
// no valid date
$isdate = false;
}
}
}
if ($isnumeric) {
$columns[$columnname] = 'DECIMAL(10,2)';
if ($isint) {
$columns[$columnname] = 'INT(11)';
}
} else {
if ($isdate) {
$columns[$columnname] = 'DATE';
} else {
$columns[$columnname] = 'VARCHAR (' . $strlen . ')';
}
}
}
}
}
$item->columns = $columns;
}
$this->_item = $item;
return $item;
}
/**
* Create mysql table and import data
*
* @param object The current item
* @return boolean Returns true on success, false on failure
*/
function importExcel($component)
{
$app = JFactory::getApplication();
if (!isset($component->columns)) {
$app->enqueueMessage(JText::_('No columns'));
return false;
}
try {
$doc = new CompoundDocument('utf-8');
$doc->parse(file_get_contents(JPATH_COMPONENT_ADMINISTRATOR . DS . 'uploads' . DS . $component->filename));
$wb = new BiffWorkbook($doc);
$wb->parse();
} catch (Exception $e) {
$app->enqueueMessage($e->getMessage());
return false;
}
$componentname = $this->filter->sanitize($component->name);
$itemsname = $component->itemsname ? $component->itemsname : 'items';
$itemname = $component->itemname ? $component->itemname : 'item';
$itemsname = $this->filter->sanitize($itemsname);
$itemname = $this->filter->sanitize($itemname);
$tablename = '#__' . $componentname . '_' . $itemsname;
// CREATE TABLE
$fields = array();
$db = JFactory::getDBO();
$query = 'CREATE TABLE `' . $tablename . '` (';
$keyfield = $componentname . '_' . $itemname . '_id';
$fields[] = $keyfield;
$query .= '`' . $keyfield . '` SERIAL,';
$col = 0;
foreach ($component->columns as $columnname => $columntype) {
$query .= '`' . $columnname . '` ' . $columntype . ' NOT NULL,';
$coltypes[$col] = $columntype;
$col++;
}
$query .= 'PRIMARY KEY ( `' . $keyfield . '` ) ';
$query .= ' );';
$db->setQuery($query);
$db->query();
// INSERT INTO
foreach ($wb->sheets as $sheetName => $sheet) {
for ($row = 1; $row < $sheet->rows(); $row++) {
$query = 'INSERT INTO ' . $tablename . ' VALUES (';
$query .= "'',";
// autoincrement key
for ($col = 0; $col < $sheet->cols(); $col++) {
if (isset($sheet->cells[$row][$col])) {
$value = $sheet->cells[$row][$col]->value;
} else {
$value = '';
}
if ($coltypes[$col] == 'DATE' && $value) {
$datearr = date_parse_from_format("j/n/y", $value);
//needs PHP 5.3
$value = $datearr['year'] . '-' . $datearr['month'] . '-' . $datearr['day'];
}
$query .= $db->Quote($value) . ', ';
}
//remove last comma
$query = substr($query, 0, strlen($query) - 2);
$query .= ');';
$db->setQuery($query);
$db->query();
}
}
return true;
}
