Ejemplo n.º 1
2
<?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) ? '&nbsp;' : $cell->value;
            echo '</td>';
        }
        echo '</tr>';
    }
    echo '</table>';
}
Ejemplo n.º 2
0
 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;
 }
Ejemplo n.º 3
0
 /**
  * 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;
 }