<?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; }