Notifications
Clear all
Topic starter
Hi Babynus,
my work on the xlsx importation :
I used the following tool : XLSXReader
Spoiler
<?php
/*
XLSXReader
Greg Neustaetter
Artistic License
XLSXReader is a heavily modified version of:
SimpleXLSX php class v0.4 (Artistic License)
Created by Sergey Schuchkin from http://www.sibvision.ru - professional php developers team 2010-2011
Downloadable here: http://www.phpclasses.org/package/6279-PHP-Parse-and-retrieve-data-from-Excel-XLS-files.html
Key Changes include:
Separation into two classes - one for the Workbook and one for Worksheets
Access to sheets by name or sheet id
Use of ZIP extension
On-demand access of files inside zip
On-demand access to sheet data
No storage of XML objects or XML text
When parsing rows, include empty rows and null cells so that data array has same number of elements for each row
Configuration option for removing trailing empty rows
Better handling of cells with style information but no value
Change of class names and method names
Removed rowsEx functionality including extraction of hyperlinks
*/
class XLSXReader {
protected $sheets = array();
protected $sharedstrings = array();
protected $sheetInfo;
protected $zip;
public $config = array(
'removeTrailingRows' => true
);
// XML schemas
const SCHEMA_OFFICEDOCUMENT = 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument';
const SCHEMA_RELATIONSHIP = 'http://schemas.openxmlformats.org/package/2006/relationships';
const SCHEMA_OFFICEDOCUMENT_RELATIONSHIP = 'http://schemas.openxmlformats.org/officeDocument/2006/relationships';
const SCHEMA_SHAREDSTRINGS = 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings';
const SCHEMA_WORKSHEETRELATION = 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet';
public function __construct($filePath, $config = array()) {
$this->config = array_merge($this->config, $config);
$this->zip = new ZipArchive();
$status = $this->zip->open($filePath);
if($status === true) {
$this->parse();
} else {
throw new Exception("Failed to open $filePath with zip error code: $status");
}
}
// get a file from the zip
protected function getEntryData($name) {
$data = $this->zip->getFromName($name);
if($data === false) {
throw new Exception("File $name does not exist in the Excel file");
} else {
return $data;
}
}
// extract the shared string and the list of sheets
protected function parse() {
$sheets = array();
$relationshipsXML = simplexml_load_string($this->getEntryData("_rels/.rels"));
foreach($relationshipsXML->Relationship as $rel) {
if($rel['Type'] == self::SCHEMA_OFFICEDOCUMENT) {
$workbookDir = dirname($rel['Target']) . '/';
$workbookXML = simplexml_load_string($this->getEntryData($rel['Target']));
foreach($workbookXML->sheets->sheet as $sheet) {
$r = $sheet->attributes('r', true);
$sheets[(string)$r->id] = array(
'sheetId' => (int)$sheet['sheetId'],
'name' => (string)$sheet['name']
);
}
$workbookRelationsXML = simplexml_load_string($this->getEntryData($workbookDir . '_rels/' . basename($rel['Target']) . '.rels'));
foreach($workbookRelationsXML->Relationship as $wrel) {
switch($wrel['Type']) {
case self::SCHEMA_WORKSHEETRELATION:
$sheets[(string)$wrel['Id']]['path'] = $workbookDir . (string)$wrel['Target'];
break;
case self::SCHEMA_SHAREDSTRINGS:
$sharedStringsXML = simplexml_load_string($this->getEntryData($workbookDir . (string)$wrel['Target']));
foreach($sharedStringsXML->si as $val) {
if(isset($val->t)) {
$this->sharedStrings[] = (string)$val->t;
} elseif(isset($val->r)) {
$this->sharedStrings[] = XLSXWorksheet::parseRichText($val);
}
}
break;
}
}
}
}
$this->sheetInfo = array();
foreach($sheets as $rid=>$info) {
$this->sheetInfo[$info['name']] = array(
'sheetId' => $info['sheetId'],
'rid' => $rid,
'path' => $info['path']
);
}
}
// returns an array of sheet names, indexed by sheetId
public function getSheetNames() {
$res = array();
foreach($this->sheetInfo as $sheetName=>$info) {
$res[$info['sheetId']] = $sheetName;
}
return $res;
}
public function getSheetCount() {
return count($this->sheetInfo);
}
// instantiates a sheet object (if needed) and returns an array of its data
public function getSheetData($sheetNameOrId) {
$sheet = $this->getSheet($sheetNameOrId);
return $sheet->getData();
}
// instantiates a sheet object (if needed) and returns the sheet object
public function getSheet($sheet) {
if(is_numeric($sheet)) {
$sheet = $this->getSheetNameById($sheet);
} elseif(!is_string($sheet)) {
throw new Exception("Sheet must be a string or a sheet Id");
}
if(!array_key_exists($sheet, $this->sheets)) {
$this->sheets[$sheet] = new XLSXWorksheet($this->getSheetXML($sheet), $sheet, $this);
}
return $this->sheets[$sheet];
}
public function getSheetNameById($sheetId) {
foreach($this->sheetInfo as $sheetName=>$sheetInfo) {
if($sheetInfo['sheetId'] === $sheetId) {
return $sheetName;
}
}
throw new Exception("Sheet ID $sheetId does not exist in the Excel file");
}
protected function getSheetXML($name) {
return simplexml_load_string($this->getEntryData($this->sheetInfo[$name]['path']));
}
// converts an Excel date field (a number) to a unix timestamp (granularity: seconds)
public static function toUnixTimeStamp($excelDateTime) {
if(!is_numeric($excelDateTime)) {
return $excelDateTime;
}
$d = floor($excelDateTime); // seconds since 1900
$t = $excelDateTime - $d;
return ($d > 0) ? ( $d - 25569 ) * 86400 + $t * 86400 : $t * 86400;
}
}
class XLSXWorksheet {
protected $workbook;
public $sheetName;
protected $data;
public $colCount;
public $rowCount;
protected $config;
public function __construct($xml, $sheetName, XLSXReader $workbook) {
$this->config = $workbook->config;
$this->sheetName = $sheetName;
$this->workbook = $workbook;
$this->parse($xml);
}
// returns an array of the data from the sheet
public function getData() {
return $this->data;
}
protected function parse($xml) {
$this->parseDimensions($xml->dimension);
$this->parseData($xml->sheetData);
}
protected function parseDimensions($dimensions) {
$range = (string) $dimensions['ref'];
$cells = explode(':', $range);
$maxValues = $this->getColumnIndex($cells[1]);
$this->colCount = $maxValues[0] + 1;
$this->rowCount = $maxValues[1] + 1;
}
protected function parseData($sheetData) {
$rows = array();
$curR = 0;
$lastDataRow = -1;
foreach ($sheetData->row as $row) {
$rowNum = (int)$row['r'];
if($rowNum != ($curR + 1)) {
$missingRows = $rowNum - ($curR + 1);
for($i=0; $i colCount,null);
$curR++;
}
}
$curC = 0;
$rowData = array();
foreach ($row->c as $c) {
list($cellIndex,) = $this->getColumnIndex((string) $c['r']);
if($cellIndex !== $curC) {
$missingCols = $cellIndex - $curC;
for($i=0;$iparseCellValue($c);
if(!is_null($val)) {
$lastDataRow = $curR;
}
$rowData[$curC] = $val;
$curC++;
}
$rows[$curR] = array_pad($rowData, $this->colCount, null);
$curR++;
}
if($this->config['removeTrailingRows']) {
$this->data = array_slice($rows, 0, $lastDataRow + 1);
$this->rowCount = count($this->data);
} else {
$this->data = $rows;
}
}
protected function getColumnIndex($cell = 'A1') {
if (preg_match("/([A-Z]+)(d+)/", $cell, $matches)) {
$col = $matches[1];
$row = $matches[2];
$colLen = strlen($col);
$index = 0;
for ($i = $colLen-1; $i >= 0; $i--) {
$index += (ord($col{$i}) - 64) * pow(26, $colLen-$i-1);
}
return array($index-1, $row-1);
}
throw new Exception("Invalid cell index");
}
protected function parseCellValue($cell) {
// $cell['t'] is the cell type
switch ((string)$cell["t"]) {
case "s": // Value is a shared string
if ((string)$cell->v != '') {
$value = $this->workbook->sharedStrings[intval($cell->v)];
} else {
$value = '';
}
break;
case "b": // Value is boolean
$value = (string)$cell->v;
if ($value == '0') {
$value = false;
} else if ($value == '1') {
$value = true;
} else {
$value = (bool)$cell->v;
}
break;
case "inlineStr": // Value is rich text inline
$value = self::parseRichText($cell->is);
break;
case "e": // Value is an error message
if ((string)$cell->v != '') {
$value = (string)$cell->v;
} else {
$value = '';
}
break;
default:
if(!isset($cell->v)) {
return null;
}
$value = (string)$cell->v;
// Check for numeric values
if (is_numeric($value)) {
if ($value == (int)$value) $value = (int)$value;
elseif ($value == (float)$value) $value = (float)$value;
elseif ($value == (double)$value) $value = (double)$value;
}
}
return $value;
}
// returns the text content from a rich text or inline string field
public static function parseRichText($is = null) {
$value = array();
if (isset($is->t)) {
$value[] = (string)$is->t;
} else {
foreach ($is->r as $run) {
$value[] = (string)$run->t;
}
}
return implode(' ', $value);
}
}
And then I updated Importable.php :
Spoiler
<?php
/* ============================================================================
* Habilitation defines right to the application for a menu and a profile.
*/
class Importable extends SqlElement {
// extends SqlElement, so has $id
public $id; // redefine $id to specify its visible place
public $name;
public $_isNameTranslatable = true;
public static $importResult;
public static $cptTotal;
public static $cptDone;
public static $cptUnchanged;
public static $cptCreated;
public static $cptModified;
public static $cptRejected;
public static $cptInvalid;
public static $cptError;
//
public static $cptOK;
public static $cptWarning;
/** ==========================================================================
* Constructor
* @param $id the id of the object in the database (null if not stored yet)
* @return void
*/
function __construct($id = NULL) {
parent::__construct($id);
}
/** ==========================================================================
* Destructor
* @return void
*/
function __destruct() {
parent::__destruct();
}
// ============================================================================*******
// MISCELLANOUS FUNCTIONS
// ============================================================================*******
public static function import($fileName, $class){
require_once '../external/XLSXReader.php';
$fileType=$_REQUEST['fileType'];
$extension=substr(strrchr($fileName,'.'),1) ;
if($extension!=$fileType){
errorLog("ERROR - Type : File Type and Type selected are not consistent");
errorLog("File Name : ".$fileName);
errorLog("Type Selected : ".$fileType);
$msg='ERROR - Type: File Type and Type selected are not consistent
Import aborted
Contact your administrator';
self::$importResult=$msg;
return $msg;
}
switch($extension){
case "csv":
traceLog( "Importation : File type CSV");
traceLog("File Name : ".$fileName);
break;
case "xlsx":
traceLog( "Importation : File type XSLX");
traceLog("File Name : ".$fileName);
break;
default:
errorLog("ERROR - File Type not recognized");
errorLog("File Name : ".$fileName);
$msg='ERROR - File Type not recognized
Import aborted
Contact your administrator';
self::$importResult=$msg;
return $msg;
break;
}
// Control that mbsting is available
if (! function_exists('mb_detect_encoding')) {
errorLog("ERROR - mbstring not enabled - Import cancelled");
$msg='Error - mbstring is not enabled
Import aborted
Contact your administrator';
self::$importResult=$msg;
return $msg;
}
SqlList::cleanAllLists(); // Added for Cron mode : as Cron is never stopped, Static Lists must be freshened
set_time_limit(3600); // 60mn
self::$cptTotal=0;
self::$cptDone=0;
self::$cptUnchanged=0;
self::$cptCreated=0;
self::$cptModified=0;
self::$cptRejected=0;
self::$cptInvalid=0;
self::$cptError=0;
self::$cptOK=0;
self::$cptWarning=0;
if (! class_exists($class)) {
self::$importResult="Cron error : class '$class' is unknown";
self::$cptError=1;
self::$cptRejected=1;
return "ERROR";
}
$obj=new $class();
$captionArray=array();
$captionObjectArray=array();
$objectArray=array();
$titleObject=array();
$idArray=array();
foreach ($obj as $fld=>$val) {
if (is_object($val)) {
$objectArray[$fld]=$val;
foreach ($val as $subfld=>$subval){
$capt=$val->getColCaption($subfld);
if ($subfld!='id' and substr($capt,0,1)!='[') {
$captionArray[$capt]=$subfld;
$captionObjectArray[$capt]=$fld;
}
}
} else {
$capt=$obj->getColCaption($fld);
if (substr($capt,0,1)!='[') {
$captionArray[$capt]=$fld;
}
}
}
switch($extension){
case "csv":
$data=Importable::importCSV($fileName);
break;
case "xlsx":
$data=Importable::importXLSX($fileName);
break;
default:
errorLog("ERROR - File Type not recognized");
errorLog("File Name : ".$fileName);
$msg='ERROR - File Type not recognized
Import aborted
Contact your administrator';
self::$importResult=$msg;
return $msg;
break;
}
$title=null;
$idxId=-1;
$htmlResult="";
$htmlResult.='| ' . $colCaption . " | "; } $htmlResult.= '' . i18n('colResultImport') . ' | |||||
|---|---|---|---|---|---|---|
| '; $htmlResult.= $line; $htmlResult.= ' | '; $htmlResult.= '';
$htmlResult.= ' ERROR : column count is incorrect ';
$htmlResult.= ' | ';
continue;
}
$id = ($idxId >= 0) ? $fields[$idxId] : null;
$obj = new $class($id);
$forceInsert = (!$obj->id and $id and !Sql::isPgsql()) ? true : false;
self::$cptTotal+=1;
foreach ($fields as $idx => $field) {
if (isset($titleObject[$idx])) {
$subClass = $titleObject[$idx];
$subobj = new $subClass();
$dataType = $subobj->getDataType($title[$idx]);
$dataLength = $subobj->getDataLength($title[$idx]);
} else {
$dataType = $obj->getDataType($title[$idx]);
$dataLength = $obj->getDataLength($title[$idx]);
}
if ($dataType == 'varchar') {
if (strlen($field) > $dataLength) {
$field = substr($field, 0, $dataLength);
}
}
// 4.1.0 : Adaptation des formats de date
else if ($dataType == 'date') {
if (!$field == '') {
if ($extension=="xlsx") {
$field=date('Y-m-d',XLSXReader::toUnixTimeStamp($field));
}
if (substr($field, 2, 1) == "/") {
list($jour, $mois, $annee) = explode('/', $field);
$field = date('Y-m-d', mktime(0, 0, 0, $mois, $jour, $annee));
}
}
}
// --------------------------------------
else if (($dataType == 'int' and substr($title[$idx], 0, 2) != 'id') or $dataType == 'decimal') {
$field = str_replace(' ', '', $field);
}
if ($field == '') {
$htmlResult.= '' . htmlEncode($field) . ' | '; continue; } if (strtolower($field) == 'null') { $field = null; } if (substr(trim($field), 0, 1) == '"' and substr(trim($field), -1, 1) == '"') { $field = substr(trim($field), 1, strlen(trim($field)) - 2); } if ($idx == count($fields) - 1) { $field = trim($field, "r"); $field = trim($field, "rn"); } $field = str_replace('""', '"', $field); if (property_exists($obj, $title[$idx])) { if (substr($title[$idx], 0, 2) == 'id' and substr($title[$idx], 0, 4) != 'idle' and strlen($title[$idx]) > 2 and !is_numeric($field)) { $obj->$title[$idx] = SqlList::getIdFromName(substr($title[$idx], 2), $field); } else { $obj->$title[$idx] = $field; } $htmlResult.= '' . htmlEncode($field) . ' | '; continue; } if (isset($titleObject[$idx])) { $subClass = $titleObject[$idx]; if (!is_object($obj->$subClass)) { $obj->$subClass = new $subClass(); } $sub = $obj->$subClass; if (property_exists($subClass, $title[$idx])) { if (substr($title[$idx], 0, 2) == 'id' and substr($title[$idx], 0, 4) != 'idle' and strlen($title[$idx]) > 2 and !is_numeric($field)) { $obj->$subClass->$title[$idx] = SqlList::getIdFromName(substr($title[$idx], 2), $field); } else { $obj->$subClass->$title[$idx] = $field; } $htmlResult.= '' . htmlEncode($field) . ' | '; continue; } } $htmlResult.= '' . htmlEncode($field) . ' | '; continue; } $htmlResult.= ''; //$obj->id=null; if ($forceInsert or !$obj->id) { if (property_exists($obj, "creationDate") and !trim($obj->creationDate)) { $obj->creationDate = date('Y-m-d'); } if (property_exists($obj, "creationDateTime") and !trim($obj->creationDateTime)) { $obj->creationDateTime = date('Y-m-d H:i'); } } Sql::beginTransaction(); if ($forceInsert) { // object with defined id was not found : force insert $result = $obj->insert(); } else { $result = $obj->save(); } if (stripos($result, 'id="lastOperationStatus" value="ERROR"') > 0) { Sql::rollbackTransaction(); $htmlResult.= '' . $result . ''; self::$cptError+=1; } else if (stripos($result, 'id="lastOperationStatus" value="OK"') > 0) { Sql::commitTransaction(); $htmlResult.= '' . $result . ''; self::$cptOK+=1; if (stripos($result, 'id="lastOperation" value="insert"') > 0) { self::$cptCreated+=1; } else if (stripos($result, 'id="lastOperation" value="update"') > 0) { self::$cptModified+=1; } else { // ??? } } else { Sql::commitTransaction(); $htmlResult.= '' . $result . ''; self::$cptWarning+=1; if (stripos($result, 'id="lastOperationStatus" value="INVALID"') > 0) { self::$cptInvalid+=1; } else if (stripos($result, 'id="lastOperationStatus" value="NO_CHANGE"') > 0) { self::$cptUnchanged+=1; } else { // ??? } } $htmlResult.= ' |
I tried to make it universal. import() uses importCSV() or imprtXSLX() to create an array $data and then uses it to import things.
Let me know what you think.
PS : those spoiler smileys are insane x)
PS2 :By the way I forgot to precise : I place the XLSXReader.php file in the external directory.
Posted : 23 May 2013 10H40
Great !
I'll have a look asap, and try to include it in next version !
Thanks for your contribution.
Posted : 23 May 2013 12H33
Hi LEVA,
Your contribution has been included in V3.4.0. B)
Many thanks for your great work 🙂
Posted : 17 Jun 2013 0H46
Topic starter
I hope it was not a big deal to adapt my work, I found some nasty mistakes afterwards ^^
Well I'm glad it was helpful ! 😉
Posted : 17 Jun 2013 10H25
I found some nasty mistakes afterwards
I did not :sick:
Hope I did not miss anything.
If you have some fixings, please share !
Posted : 17 Jun 2013 22H10
Topic starter
Hi Babynus, sorry for the delay.
I was thinking for example at :
if (count($fields) > count($title)) {
$line="";
foreach($fields as $field){
$line.=$field." ;; ";
}
self::$cptError+=1;
$htmlResult.= '';
$htmlResult.= $line;
$htmlResult.= ' ';
$htmlResult.= '';
$htmlResult.= 'ERROR : column count is incorrect';
$htmlResult.= ' ';
continue;
}
This part has nothing to do in import(), but must be in importCSV().
I dont remember any other bug.. and I hardly have time to go back on this..
Posted : 12 Jul 2013 13H27
Hi,
I'm not sure it is an issue.
This part just checks that each line has correct number of fields.
I guest importXLSX() will always provide good number of fields.
So only data from importCSV() will possibly come into this code lines.
I think it is in the good place to display the error line in the result table.
Regards.
Posted : 12 Jul 2013 22H30
Topic starter
Ok babynus.. I guess you've double check my work so I'm not that worried 🙂
Posted : 15 Jul 2013 10H30