public function ProcessFileContent()
{
$objPHPExcel = PHPExcel_IOFactory::load($this->file);
// Format is as follows:
// (gray bg) [ <description of data> ], <relation1>, <relationN>
// <srcConcept>, <tgtConcept1>, <tgtConceptN>
// <srcAtomA>, <tgtAtom1A>, <tgtAtomNA>
// <srcAtomB>, <tgtAtom1B>, <tgtAtomNB>
// <srcAtomC>, <tgtAtom1C>, <tgtAtomNC>
// Output is function call:
// InsPair($relation,$srcConcept,$srcAtom,$tgtConcept,$tgtAtom)
// Loop over all worksheets
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
// Loop through all rows
$highestrow = $worksheet->getHighestRow();
$highestcolumn = $worksheet->getHighestColumn();
$highestcolumnnr = PHPExcel_Cell::columnIndexFromString($highestcolumn);
$row = 1;
// Go to the first row where a table starts.
for ($i = $row; $i <= $highestrow; $i++) {
$row = $i;
if (substr($worksheet->getCell('A' . $row)->getValue(), 0, 1) === '[') {
break;
}
}
// We are now at the beginning of a table or at the end of the file.
$line = array();
// Line is a buffer of one or more related (subsequent) excel rows
while ($row <= $highestrow) {
// Read this line as an array of values
$values = array();
// values is a buffer containing the cells in a single excel row
for ($columnnr = 0; $columnnr < $highestcolumnnr; $columnnr++) {
$columnletter = PHPExcel_Cell::stringFromColumnIndex($columnnr);
$cell = $worksheet->getCell($columnletter . $row);
$cellvalue = (string) $cell->getCalculatedValue();
// overwrite $cellvalue in case of datetime
// the @ is a php indicator for a unix timestamp (http://php.net/manual/en/datetime.formats.compound.php), later used for typeConversion
if (PHPExcel_Shared_Date::isDateTime($cell) && !empty($cellvalue)) {
$cellvalue = '@' . (string) PHPExcel_Shared_Date::ExcelToPHP($cellvalue);
}
$values[] = $cellvalue;
}
$line[] = $values;
// add line (array of values) to the line buffer
$row++;
// Is this relation table done? Then we parse the current values into function calls and reset it
$firstCellInRow = (string) $worksheet->getCell('A' . $row)->getCalculatedValue();
if (substr($firstCellInRow, 0, 1) === '[') {
// Relation table is complete, so it can be processed.
$this->ParseLines($line);
$line = array();
}
}
// Last relation table remains to be processed.
$this->ParseLines($line);
$line = array();
}
}
function excel_to_date($id)
{
$value = $id->getValue();
if ($value != '') {
if (PHPExcel_Shared_Date::isDateTime($id)) {
$data = PHPExcel_Shared_Date::ExcelToPHP($value);
$date = date('Y-m-d', $data);
} else {
if (date_create($value)) {
$date = date_format(date_create($value), 'Y-m-d');
} else {
$date = '0000-00-00';
}
}
} else {
$date = '0000-00-00';
}
return $date;
}
function insertarCargarExcepciones($array)
{
$uploadOk = 1;
$time = time();
$fecha = date("Y-m-d", $time);
$target_dir = "../documents/";
$target_file = $target_dir . basename($_FILES["archivoExcepcion"]["name"]);
move_uploaded_file($array["archivoExcepcion"]["tmp_name"], $target_file);
set_include_path(get_include_path() . PATH_SEPARATOR . '../complements/PHPExcel-1.8/Classes/');
$inputFileType = 'Excel2007';
include 'PHPExcel/IOFactory.php';
$inputFileName = $target_file;
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($inputFileName);
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
require_once "../db/conexiones.php";
$consulta = new Conexion();
foreach ($sheetData as $key => $datos) {
$nombreSinAcentos = sanear_string($datos['B']);
$nombre = strtoupper(trim($nombreSinAcentos));
$datosEmpleado = $consulta->Conectar("postgres", "SELECT * FROM userinfo WHERE UPPER(name)='" . $nombre . "'");
if ($datosEmpleado) {
$sqlInsert = $this->invoco->Conectar("postgres", "INSERT INTO horario_excepcion (user_id, desde, hasta, banda_id) VALUES (" . $datosEmpleado[0]['userid'] . ",'" . date("Y-m-d", PHPExcel_Shared_Date::ExcelToPHP($objPHPExcel->getActiveSheet()->getCell('D' . $key)->getvalue())) . "', '" . date("Y-m-d", PHPExcel_Shared_Date::ExcelToPHP($objPHPExcel->getActiveSheet()->getCell('E' . $key)->getvalue())) . "'," . $datos['C'] . ")");
}
}
return "Se insertaron los datos Exitosamente!";
}
/**
* Format cell data type
* @param \PHPExcel_Cell $cell
* @return string
*/
protected function reformatCellDataType(\PHPExcel_Cell $cell)
{
$value = $cell->getValue();
//datetime
if (\PHPExcel_Shared_Date::isDateTime($cell)) {
$format = $this->cellFormat['dateTime'];
return date($format, \PHPExcel_Shared_Date::ExcelToPHP($value));
}
return $value;
}
public function getAllData()
{
$this->excel_data = array();
$objWorksheet = $this->excel->getActiveSheet();
// Get the highest row number and column letter referenced in the worksheet
$highestRow = $objWorksheet->getHighestRow();
// e.g. 10
$highestColumn = $objWorksheet->getHighestColumn();
// e.g 'F'
if ($this->maxColumn != null) {
$highestColumn = $this->maxColumn;
}
// Increment the highest column letter
$highestColumn++;
for ($row = 1; $row <= $highestRow; ++$row) {
$line = array();
for ($col = 'A'; $col != $highestColumn; ++$col) {
// var_dump($objWorksheet->getCell($col . $row)
// ->getValue());
// print "<br/>\r\n";
// continue;
$cell = $objWorksheet->getCell($col . $row);
$val = "" . $cell->getValue();
// var_dump($col, $row,$val);
if ($row == $this->titleLine && $val == "") {
$highestColumn = $col++;
break;
}
if (PHPExcel_Shared_Date::isDateTime($cell)) {
$val = PHPExcel_Shared_Date::ExcelToPHP($val);
} else {
$val = "" . $val;
}
$line[] = $val;
}
$this->excel_data[] = $line;
}
return $this->excel_data;
}
/**
* Convert a value in a pre-defined format to a PHP string
*
* @param mixed $value Value to format
* @param string $format Format code
* @return string Formatted string
*/
public static function toFormattedString($value = '', $format = '')
{
if (!is_numeric($value)) {
return $value;
}
if (preg_match("/^[hmsdy]/i", $format)) {
// custom datetime format
// dvc: convert Excel formats to PHP date formats
// first remove escapes related to non-format characters
// OpenOffice.org uses upper-case number formats, e.g. 'YYYY', convert to lower-case
$format = strtolower($format);
$format = str_replace('\\', '', $format);
// 4-digit year
$format = str_replace('yyyy', 'Y', $format);
// 2-digit year
$format = str_replace('yy', 'y', $format);
// first letter of month - no php equivalent
$format = str_replace('mmmmm', 'M', $format);
// full month name
$format = str_replace('mmmm', 'F', $format);
// short month name
$format = str_replace('mmm', 'M', $format);
// mm is minutes if time or month w/leading zero
$format = str_replace(':mm', ':i', $format);
// tmp place holder
$format = str_replace('mm', 'x', $format);
// month no leading zero
$format = str_replace('m', 'n', $format);
// month leading zero
$format = str_replace('x', 'm', $format);
// 12-hour suffix
$format = str_replace('am/pm', 'A', $format);
// tmp place holder
$format = str_replace('dd', 'x', $format);
// days no leading zero
$format = str_replace('d', 'j', $format);
// days leading zero
$format = str_replace('x', 'd', $format);
// seconds
$format = str_replace('ss', 's', $format);
// fractional seconds - no php equivalent
$format = str_replace('.s', '', $format);
if (!strpos($format, 'A')) {
// 24-hour format
$format = str_replace('h', 'H', $format);
}
// user defined flag symbol????
$format = str_replace(';@', '', $format);
return date($format, PHPExcel_Shared_Date::ExcelToPHP($value));
} else {
if (preg_match('/%$/', $format)) {
// % number format
if (preg_match('/\\.[#0]+/i', $format, $m)) {
$s = substr($m[0], 0, 1) . (strlen($m[0]) - 1);
$format = str_replace($m[0], $s, $format);
}
if (preg_match('/^[#0]+/', $format, $m)) {
$format = str_replace($m[0], strlen($m[0]), $format);
}
$format = '%' . str_replace('%', "f%%", $format);
return sprintf($format, 100 * $value);
} else {
if (preg_match("/^([0-9.,-]+)\$/", $value)) {
if ($format === self::FORMAT_NUMBER) {
return sprintf('%1.0f', $value);
} else {
if ($format === self::FORMAT_NUMBER_00) {
return sprintf('%1.2f', $value);
} else {
if ($format === self::FORMAT_NUMBER_COMMA_SEPARATED1 || $format === self::FORMAT_NUMBER_COMMA_SEPARATED2) {
return number_format($value, 2, ',', '.');
} else {
if ($format === self::FORMAT_PERCENTAGE) {
return round(100 * $value, 0) . '%';
} else {
if ($format === self::FORMAT_PERCENTAGE_00) {
return round(100 * $value, 2) . '%';
} else {
if ($format === self::FORMAT_DATE_YYYYMMDD || $format === self::FORMAT_DATE_YYYYMMDD2) {
return date('Y-m-d', 1 * $value);
} else {
if ($format === self::FORMAT_DATE_DDMMYYYY) {
return date('d/m/Y', 1 * $value);
} else {
if ($format === 'yyyy/mm/dd;@') {
return date('Y/m/d', 1 * $value);
} else {
if ($format === self::FORMAT_CURRENCY_USD_SIMPLE) {
return '$' . number_format($value, 2);
} else {
if ($format === self::FORMAT_CURRENCY_USD) {
return '$' . number_format($value);
} else {
if ($format === self::FORMAT_CURRENCY_EUR_SIMPLE) {
return 'EUR ' . sprintf('%1.2f', $value);
}
}
}
}
}
}
}
}
}
}
}
}
return $value;
}
}
}
private function _importADH(&$excelFile)
{
$worksheet = $excelFile->getSheetByName('ADH');
$worksheetTitle = $worksheet->getTitle();
$highestRow = $worksheet->getHighestRow();
$highestColumn = 'O';
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
//echo '<br>'. $worksheetTitle .'<table border="1"><tr>';
ini_set('max_execution_time', '500');
//SLW 15-06: initializer of the GN- (codes)
$GNTable = array();
$CountryTable = array();
$dateVerdict;
$dateIN;
$dateOUT;
$description;
$status;
$regulationCode;
$regulationURL;
$statusTime = 0;
$commentTime = 0;
$linkTime = 0;
$dataSource = $this->CustomsDuty->getDataSource();
$dataSource2 = $this->Code->getDataSource();
$dataSource->begin();
$dataSource2->begin();
$timepre = microtime(true);
for ($row = 2; $row <= $highestRow; ++$row) {
$dateIN = date('d-m-Y', PHPExcel_Shared_Date::ExcelToPHP($worksheet->getCellByColumnAndRow(ADH::$IN_DATUM, $row)->getValue()));
$dateOUT = date('d-m-Y', PHPExcel_Shared_Date::ExcelToPHP($worksheet->getCellByColumnAndRow(ADH::$EIND_DATUM, $row)->getValue()));
array_push($GNTable, $worksheet->getCellByColumnAndRow(ADH::$GS_POST_1, $row)->getValue());
array_push($GNTable, $worksheet->getCellByColumnAndRow(ADH::$GS_POST_2, $row)->getValue());
array_push($GNTable, $worksheet->getCellByColumnAndRow(ADH::$GN_CODE_1, $row)->getValue());
array_push($GNTable, $worksheet->getCellByColumnAndRow(ADH::$GN_CODE_2, $row)->getValue());
array_push($CountryTable, $worksheet->getCellByColumnAndRow(ADH::$LAND_OORSPR, $row)->getValue());
$CountryTable = $this->_tableCheck($CountryTable, false);
array_push($GNTable, $worksheet->getCellByColumnAndRow(ADH::$UITGEBR_GCODE, $row)->getValue());
$GNTable = $this->_tableCheck($GNTable, true);
if ($worksheet->getCellByColumnAndRow(ADH::$BRON, $row)->hasHyperlink()) {
$regulationURL = $worksheet->getCellByColumnAndRow(ADH::$BRON, $row)->getHyperlink()->getUrl();
}
if (PHPExcel_Shared_Date::isDateTime($worksheet->getCellByColumnAndRow(ADH::$DATUM_INBRENG, $row))) {
$dateVerdict = trim($worksheet->getCellByColumnAndRow(ADH::$DATUM_INBRENG, $row)->getValue());
}
if ($dateVerdict !== NUll && $dateVerdict !== '') {
$dateVerdict = date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($worksheet->getCellByColumnAndRow(ADH::$DATUM_INBRENG, $row)->getValue()));
}
$dateIN = $worksheet->getCellByColumnAndRow(ADH::$IN_DATUM, $row)->getValue();
if ($dateIN !== NULL) {
$dateIN = date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($dateIN));
}
$dateOUT = $worksheet->getCellByColumnAndRow(ADH::$EIND_DATUM, $row)->getValue();
if ($dateOUT !== NULL) {
$dateOUT = date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($dateOUT));
}
$status = $worksheet->getCellByColumnAndRow(ADH::$STATUS, $row)->getValue();
$time_pre = microtime(true);
$this->_saveStatuses($status);
$time_post = microtime(true);
$statusTime += $time_post - $time_pre;
// }
$regulationCode = $worksheet->getCellByColumnAndRow(ADH::$VERORD_CODE, $row)->getValue();
$this->_importRegulations($regulationURL, $regulationCode, $dateVerdict, $dateIN, $dateOUT, $status);
$time_pre = microtime(true);
$this->_saveComment($GNTable, $worksheet->getCellByColumnAndRow(ADH::$UITGEBR_OMSCHR, $row)->getValue());
$time_post = microtime(true);
$commentTime += $time_post - $time_pre;
$time_pre = microtime(true);
$this->_createLink($GNTable, $CountryTable, $regulationCode);
$time_post = microtime(true);
$linkTime += $time_post - $time_pre;
$GNTable = array();
}
$timepro = microtime(true);
echo "<br>time before commit: " . ($timepro - $timepre) . "<br>";
$dataSource->commit();
$dataSource2->commit();
echo "status time: " . $statusTime . "<br>comment time: " . $commentTime . "<br>link time: " . $linkTime;
$this->Controller->autoRender = false;
}
/**
* Функция осуществляет преобразование в DateTime object
* @param type $string строка дата/время полученная из Excel
* @return \DateTime object
* @throws \Exception
*/
public function renderDateString($string)
{
if (!strlen($string)) {
return null;
}
$date = \PHPExcel_Shared_Date::ExcelToPHP(trim($string));
return new \DateTime(gmdate('d-m-Y H:i:s', $date));
}
/**
* Read FORMULA record
* This record contains the token array and the result of a
* formula cell.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
private function _readFormula()
{
$pos = $this->_pos;
$length = $this->_GetInt2d($this->_data, $pos + 2);
$recordData = substr($this->_data, $pos + 4, $length);
$pos += 4;
// offset: 0; size: 2; row index
$row = $this->_GetInt2d($this->_data, $pos);
// offset: 2; size: 2; col index
$column = $this->_GetInt2d($this->_data, $pos + 2);
$columnString = PHPExcel_Cell::stringFromColumnIndex($column);
// Read cell?
if (!is_null($this->getReadFilter()) && $this->getReadFilter()->readCell($columnString, $row + 1, $this->_phpSheet->getTitle())) {
// offset: 4; size: 2; XF index
$xfindex = $this->_GetInt2d($this->_data, $pos + 4);
// offset: 6; size: 8; result of the formula
if (ord($this->_data[$pos + 6]) == 0 && ord($this->_data[$pos + 12]) == 255 && ord($this->_data[$pos + 13]) == 255) {
//String formula. Result follows in appended STRING record
$dataType = PHPExcel_Cell_DataType::TYPE_STRING;
$soff = $pos + $length;
$scode = $this->_GetInt2d($this->_data, $soff);
$slength = $this->_GetInt2d($this->_data, $soff + 2);
$sdata = substr($this->_data, $soff + 4, $slength);
if ($this->_version == self::XLS_BIFF8) {
$string = $this->_readUnicodeStringLong($sdata);
$value = $string['value'];
} else {
$string = $this->_readByteStringLong($sdata);
$value = $string['value'];
}
} elseif (ord($this->_data[$pos + 6]) == 1 && ord($this->_data[$pos + 12]) == 255 && ord($this->_data[$pos + 13]) == 255) {
//Boolean formula. Result is in +2; 0=false,1=true
$dataType = PHPExcel_Cell_DataType::TYPE_BOOL;
$value = (bool) ord($this->_data[$pos + 8]);
} elseif (ord($this->_data[$pos + 6]) == 2 && ord($this->_data[$pos + 12]) == 255 && ord($this->_data[$pos + 13]) == 255) {
//Error formula. Error code is in +2
$dataType = PHPExcel_Cell_DataType::TYPE_ERROR;
$value = $this->_mapErrorCode(ord($this->_data[$pos + 8]));
} elseif (ord($this->_data[$pos + 6]) == 3 && ord($this->_data[$pos + 12]) == 255 && ord($this->_data[$pos + 13]) == 255) {
//Formula result is a null string
$dataType = PHPExcel_Cell_DataType::TYPE_NULL;
$value = '';
} else {
// forumla result is a number, first 14 bytes like _NUMBER record
$dataType = PHPExcel_Cell_DataType::TYPE_NUMERIC;
$value = $this->_createNumber($pos);
}
// add cell style
if (!$this->_readDataOnly) {
$this->_phpSheet->getStyle($columnString . ($row + 1))->applyFromArray($this->_xf[$xfindex]);
if (PHPExcel_Shared_Date::isDateTimeFormatCode($this->_xf[$xfindex]['numberformat']['code'])) {
$value = PHPExcel_Shared_Date::ExcelToPHP($value);
}
}
// offset: 14: size: 2; option flags, recalculate always, recalculate on open etc.
// offset: 16: size: 4; not used
// offset: 20: size: variable; formula structure
$formulaStructure = substr($recordData, 20);
// add cell value
try {
if ($this->_version != self::XLS_BIFF8) {
throw new Exception('Not BIFF8. Can only read BIFF8 formulas');
}
$formula = $this->_getFormulaFromStructure($formulaStructure);
// get human language
$this->_phpSheet->getCell($columnString . ($row + 1))->setValueExplicit('=' . $formula, PHPExcel_Cell_DataType::TYPE_FORMULA);
} catch (Exception $e) {
$this->_phpSheet->setCellValueExplicit($columnString . ($row + 1), $value, $dataType);
}
}
// move stream pointer to next record
$this->_pos += 4 + $length;
}
/**
* EOMONTH
*
* Returns the serial number for the last day of the month that is the indicated number of months before or after start_date.
* Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month.
*
* @param long $dateValue Excel date serial value or a standard date string
* @param int $adjustmentMonths Number of months to adjust by
* @return long Excel date serial value
*/
public static function EOMONTH($dateValue = 1, $adjustmentMonths = 0)
{
$dateValue = self::flattenSingleValue($dateValue);
$adjustmentMonths = floor(self::flattenSingleValue($adjustmentMonths));
if (!is_numeric($adjustmentMonths)) {
return self::$_errorCodes['value'];
}
if (is_string($dateValue = self::_getDateValue($dateValue))) {
return self::$_errorCodes['value'];
}
// Execute function
$PHPDateObject = self::_adjustDateByMonths($dateValue, $adjustmentMonths + 1);
$adjustDays = (int) $PHPDateObject->format('d');
$adjustDaysString = '-' . $adjustDays . ' days';
$PHPDateObject->modify($adjustDaysString);
switch (self::getReturnDateType()) {
case self::RETURNDATE_EXCEL:
return (double) PHPExcel_Shared_Date::PHPToExcel($PHPDateObject);
break;
case self::RETURNDATE_PHP_NUMERIC:
return (int) PHPExcel_Shared_Date::ExcelToPHP(PHPExcel_Shared_Date::PHPToExcel($PHPDateObject));
break;
case self::RETURNDATE_PHP_OBJECT:
return $PHPDateObject;
break;
}
}
/**
* @param 文件 $file
* @return Exel 内容 array
* @throws PHPExcel_Exception
* @throws PHPExcel_Reader_Exception
*/
public function importExcel($file)
{
if (!file_exists($file)) {
return array("error" => 0, 'message' => 'file not found!');
}
chmod($file, 0777);
if (!is_readable($file)) {
return array("error" => 0, 'message' => 'file is not readable');
}
Vendor("PHPExcel.IOFactory");
//兼容多种版本的Excel
$objReader = PHPExcel_IOFactory::createReader('Excel5');
$PHPReader = $objReader->load($file);
/* if(!$PHPReader){
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$PHPReader = $objReader->load($file);
if(!$PHPReader){
$objReader = PHPExcel_IOFactory::createReader('Excel5');
$PHPReader = $objReader->load($file);
if(!$PHPReader){
return array("error"=>0,'message'=>'文件格式错误');
}
}
} */
if (!isset($PHPReader)) {
return array("error" => 0, 'message' => 'read error!');
}
$allWorksheets = $PHPReader->getAllSheets();
$i = 0;
foreach ($allWorksheets as $objWorksheet) {
$sheetname = $objWorksheet->getTitle();
$allRow = $objWorksheet->getHighestRow();
//how many rows
$highestColumn = $objWorksheet->getHighestColumn();
//how many columns
$allColumn = PHPExcel_Cell::columnIndexFromString($highestColumn);
$array[$i]["Title"] = $sheetname;
$array[$i]["Cols"] = $allColumn;
$array[$i]["Rows"] = $allRow;
$arr = array();
$isMergeCell = array();
foreach ($objWorksheet->getMergeCells() as $cells) {
//merge cells
foreach (PHPExcel_Cell::extractAllCellReferencesInRange($cells) as $cellReference) {
$isMergeCell[$cellReference] = true;
}
}
for ($currentRow = 1; $currentRow <= $allRow; $currentRow++) {
$row = array();
for ($currentColumn = 0; $currentColumn < $allColumn; $currentColumn++) {
$cell = $objWorksheet->getCellByColumnAndRow($currentColumn, $currentRow);
$afCol = PHPExcel_Cell::stringFromColumnIndex($currentColumn + 1);
$bfCol = PHPExcel_Cell::stringFromColumnIndex($currentColumn - 1);
$col = PHPExcel_Cell::stringFromColumnIndex($currentColumn);
$address = $col . $currentRow;
$value = $objWorksheet->getCell($address)->getValue();
if (substr($value, 0, 1) == '=') {
return array("error" => 0, 'message' => 'can not use the formula!');
exit;
}
if ($cell->getDataType() == PHPExcel_Cell_DataType::TYPE_NUMERIC) {
$cellstyleformat = $cell->getStyle($cell->getCoordinate())->getNumberFormat();
$formatcode = $cellstyleformat->getFormatCode();
if (preg_match('/^([$[A-Z]*-[0-9A-F]*])*[hmsdy]/i', $formatcode)) {
$value = gmdate("Y-m-d", PHPExcel_Shared_Date::ExcelToPHP($value));
} else {
$value = PHPExcel_Style_NumberFormat::toFormattedString($value, $formatcode);
}
}
if ($isMergeCell[$col . $currentRow] && $isMergeCell[$afCol . $currentRow] && !empty($value)) {
$temp = $value;
} elseif ($isMergeCell[$col . $currentRow] && $isMergeCell[$col . ($currentRow - 1)] && empty($value)) {
$value = $arr[$currentRow - 1][$currentColumn];
} elseif ($isMergeCell[$col . $currentRow] && $isMergeCell[$bfCol . $currentRow] && empty($value)) {
$value = $temp;
}
//$value = iconv( "UTF-8","gb2312", $content);
$row[$currentColumn] = $value;
}
$arr[$currentRow] = $row;
}
$array[$i]["Content"] = $arr;
$i++;
}
spl_autoload_register(array('Think', 'autoload'));
//must, resolve ThinkPHP and PHPExcel conflicts
unset($objWorksheet);
unset($PHPReader);
unset($PHPExcel);
unlink($file);
return array("error" => 1, "data" => $array);
}
$F = $objPHPExcel->getActiveSheet()->getCell('F' . $i)->getCalculatedValue();
$G = $objPHPExcel->getActiveSheet()->getCell('G' . $i)->getCalculatedValue();
$H = $objPHPExcel->getActiveSheet()->getCell('H' . $i)->getCalculatedValue();
$I = $objPHPExcel->getActiveSheet()->getCell('I' . $i)->getCalculatedValue();
$J = $objPHPExcel->getActiveSheet()->getCell('J' . $i)->getCalculatedValue();
$K = $objPHPExcel->getActiveSheet()->getCell('K' . $i)->getCalculatedValue();
$L = $objPHPExcel->getActiveSheet()->getCell('L' . $i)->getCalculatedValue();
$M = $objPHPExcel->getActiveSheet()->getCell('M' . $i)->getCalculatedValue();
$N = $objPHPExcel->getActiveSheet()->getCell('N' . $i)->getCalculatedValue();
$fechaA = PHPExcel_Shared_Date::ExcelToPHP($A);
$dateA = date('Y-m-d', $fechaA);
$fechaB = PHPExcel_Shared_Date::ExcelToPHP($B);
$dateB = date('H:i:s', $fechaB);
$fechaC = PHPExcel_Shared_Date::ExcelToPHP($C);
$dateC = date('Y-m-d', $fechaC);
$fechaD = PHPExcel_Shared_Date::ExcelToPHP($D);
$dateD = date('H:i:s', $fechaD);
$H = utf8_decode($H);
$L = utf8_decode($L);
$N = utf8_decode($N);
mysqli_query($cn, "INSERT INTO despacho_solicitud_temp (desde_fecha,desde_hora,hasta_fecha,hasta_hora,\n\t\t\t\t\t\t\t\t\t\t\t\tblock,tipo,circulacion_trenes,vias,desde_sector,hasta_sector,empresa,encargados,telefonos,descripcion) VALUES ('{$dateA}','{$dateB}','{$dateC}','{$dateD}','{$E}',\n\t\t\t\t\t\t\t\t\t\t\t\t'{$F}','{$G}','{$H}','{$I}','{$J}','{$K}','{$L}','{$M}','{$N}')");
$query2 = "delete from despacho_solicitud_temp where block=''";
mysqli_query($link, $query2);
if ($objPHPExcel->getActiveSheet()->getCell('A' . $i)->getCalculatedValue() == NULL) {
$param = 1;
}
$i++;
$contador = $contador + 1;
}
$totalingresos = $contador - 1;
} else {
/**
* Get date from Excel sheet for given column and row. Convert Excel date to format acceptable by TimeExpressionParser if necessary.
* @param PHPExcel_Worksheet $po_sheet The work sheet
* @param int $pn_row_num row number (zero indexed)
* @param string|int $pm_col either column number (zero indexed) or column letter ('A', 'BC')
* @param int $pn_offset Offset to adf to the timestamp (can be used to fix timezone issues or simple to move dates around a little bit)
* @return string|null the date, if a value exists
*/
function caPhpExcelGetDateCellContent($po_sheet, $pn_row_num, $pm_col, $pn_offset = 0)
{
if (!is_int($pn_offset)) {
$pn_offset = 0;
}
if (!is_numeric($pm_col)) {
$pm_col = PHPExcel_Cell::columnIndexFromString($pm_col) - 1;
}
$o_val = $po_sheet->getCellByColumnAndRow($pm_col, $pn_row_num);
$vs_val = trim((string) $o_val);
if (strlen($vs_val) > 0) {
$vn_timestamp = PHPExcel_Shared_Date::ExcelToPHP(trim((string) $o_val->getValue())) + $pn_offset;
if (!($vs_return = caGetLocalizedDate($vn_timestamp, array('dateFormat' => 'iso8601', 'timeOmit' => false)))) {
$vs_return = $vs_val;
}
} else {
$vs_return = null;
}
return $vs_return;
}
public function update_stock_via_excel()
{
if (isset($_FILES['file']) && $_FILES['file']['size'] > 0) {
$excel2 = PHPExcel_IOFactory::createReader('Excel2007');
$excel2 = $objPHPExcel = $excel2->load($_FILES["file"]["tmp_name"]);
// Empty Sheet
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
$temp = array();
// Loop through each row of the worksheet in turn
for ($row = 2; $row <= $highestRow; $row++) {
// Read a row of data into an array
$rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
if ($rowData[0][11] == 0) {
$unit_of_issue = "Pack_Size";
$total_units = $rowData[0][11];
$stock_level = $rowData[0][10];
} elseif ($rowData[0][10] >= 1) {
$unit_of_issue = "Unit_Size";
$total_units = $rowData[0][10] * $rowData[0][6];
$stock_level = $rowData[0][10];
}
$InvDate = date('t M Y', PHPExcel_Shared_Date::ExcelToPHP($rowData[0][9]));
array_push($temp, array('commodity_id' => $rowData[0][0], 'unit_size' => $rowData[0][5], 'batch_no' => $rowData[0][7], 'manu' => $rowData[0][8], 'expiry_date' => $InvDate, 'stock_level' => $stock_level, 'total_unit_count' => $rowData[0][6], 'unit_issue' => $unit_of_issue, 'total_units' => $total_units, 'source_of_item' => $rowData[0][3], 'supplier' => $rowData[0][2]));
}
unset($objPHPExcel);
$this->autosave_update_stock($temp, $this->session->userdata('facility_id'));
}
}
/**
* Loads PHPExcel from file
*
* @param string $pFilename
* @throws Exception
*/
public function load($pFilename)
{
// Check if file exists
if (!file_exists($pFilename)) {
throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
}
// Initialisations
$excel = new PHPExcel();
$excel->removeSheetByIndex(0);
// Use ParseXL for the hard work.
$this->_ole = new PHPExcel_Shared_OLERead();
$this->_rowoffset = $this->_coloffset = 0;
$this->_defaultEncoding = 'ISO-8859-1';
$this->_encoderFunction = function_exists('mb_convert_encoding') ? 'mb_convert_encoding' : 'iconv';
// get excel data
$res = $this->_ole->read($pFilename);
// oops, something goes wrong (Darko Miljanovic)
if ($res === false) {
// check error code
if ($this->_ole->error == 1) {
// bad file
throw new Exception('The filename ' . $pFilename . ' is not readable');
} elseif ($this->_ole->error == 2) {
throw new Exception('The filename ' . $pFilename . ' is not recognised as an Excel file');
}
// check other error codes here (eg bad fileformat, etc...)
}
$this->_data = $this->_ole->getWorkBook();
$this->_pos = 0;
/**
* PARSE WORKBOOK
*
**/
$pos = 0;
$code = ord($this->_data[$pos]) | ord($this->_data[$pos + 1]) << 8;
$length = ord($this->_data[$pos + 2]) | ord($this->_data[$pos + 3]) << 8;
$version = ord($this->_data[$pos + 4]) | ord($this->_data[$pos + 5]) << 8;
$substreamType = ord($this->_data[$pos + 6]) | ord($this->_data[$pos + 7]) << 8;
if ($version != self::XLS_BIFF8 && $version != self::XLS_BIFF7) {
return false;
}
if ($substreamType != self::XLS_WorkbookGlobals) {
return false;
}
$pos += $length + 4;
$code = ord($this->_data[$pos]) | ord($this->_data[$pos + 1]) << 8;
$length = ord($this->_data[$pos + 2]) | ord($this->_data[$pos + 3]) << 8;
$recordData = substr($this->_data, $pos + 4, $length);
while ($code != self::XLS_Type_EOF) {
switch ($code) {
case self::XLS_Type_SST:
/**
* SST - Shared String Table
*
* This record contains a list of all strings used anywhere
* in the workbook. Each string occurs only once. The
* workbook uses indexes into the list to reference the
* strings.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
**/
// offset: 0; size: 4; total number of strings
// offset: 4; size: 4; number of unique strings
$spos = $pos + 4;
$limitpos = $spos + $length;
$uniqueStrings = $this->_GetInt4d($this->_data, $spos + 4);
$spos += 8;
// loop through the Unicode strings (16-bit length)
for ($i = 0; $i < $uniqueStrings; $i++) {
if ($spos == $limitpos) {
// then we have reached end of SST record data
$opcode = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
$conlength = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
if ($opcode != self::XLS_Type_CONTINUE) {
// broken file, something is wrong
return -1;
}
$spos += 4;
$limitpos = $spos + $conlength;
}
// Read in the number of characters in the Unicode string
$numChars = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
$spos += 2;
// option flags
$optionFlags = ord($this->_data[$spos]);
$spos++;
// bit: 0; mask: 0x01; 0 = compressed; 1 = uncompressed
$asciiEncoding = ($optionFlags & 0x1) == 0;
// bit: 2; mask: 0x02; 0 = ordinary; 1 = Asian phonetic
$extendedString = ($optionFlags & 0x4) != 0;
// Asian phonetic
// bit: 3; mask: 0x03; 0 = ordinary; 1 = Rich-Text
$richString = ($optionFlags & 0x8) != 0;
if ($richString) {
// Read in the crun
// number of Rich-Text formatting runs
$formattingRuns = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
$spos += 2;
}
if ($extendedString) {
// size of Asian phonetic setting
$extendedRunLength = $this->_GetInt4d($this->_data, $spos);
$spos += 4;
}
// read in the characters
$len = $asciiEncoding ? $numChars : $numChars * 2;
if ($spos + $len < $limitpos) {
$retstr = substr($this->_data, $spos, $len);
$spos += $len;
} else {
// found countinue record
$retstr = substr($this->_data, $spos, $limitpos - $spos);
$bytesRead = $limitpos - $spos;
// remaining characters in Unicode string
$charsLeft = $numChars - ($asciiEncoding ? $bytesRead : $bytesRead / 2);
$spos = $limitpos;
// keep reading the characters
while ($charsLeft > 0) {
// record data
$opcode = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
// length of continue record data
$conlength = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
if ($opcode != self::XLS_Type_CONTINUE) {
// broken file, something is wrong
return -1;
}
$spos += 4;
$limitpos = $spos + $conlength;
// option flags are repeated when Unicode string is split by a continue record
// OpenOffice.org documentation 5.21
$option = ord($this->_data[$spos]);
$spos += 1;
if ($asciiEncoding && $option == 0) {
// 1st fragment compressed
// this fragment compressed
$len = min($charsLeft, $limitpos - $spos);
$retstr .= substr($this->_data, $spos, $len);
$charsLeft -= $len;
$asciiEncoding = true;
} elseif (!$asciiEncoding && $option != 0) {
// 1st fragment uncompressed
// this fragment uncompressed
$len = min($charsLeft * 2, $limitpos - $spos);
$retstr .= substr($this->_data, $spos, $len);
$charsLeft -= $len / 2;
$asciiEncoding = false;
} elseif (!$asciiEncoding && $option == 0) {
// 1st fragment uncompressed
// this fragment compressed
$len = min($charsLeft, $limitpos - $spos);
for ($j = 0; $j < $len; $j++) {
$retstr .= $this->_data[$spos + $j] . chr(0);
}
$charsLeft -= $len;
$asciiEncoding = false;
} else {
// 1st fragment compressed
// this fragment uncompressed
$newstr = '';
for ($j = 0; $j < strlen($retstr); $j++) {
$newstr = $retstr[$j] . chr(0);
}
$retstr = $newstr;
$len = min($charsLeft * 2, $limitpos - $spos);
$retstr .= substr($this->_data, $spos, $len);
$charsLeft -= $len / 2;
$asciiEncoding = false;
}
$spos += $len;
}
}
//$retstr = ($asciiEncoding) ?
// $retstr : $this->_encodeUTF16($retstr);
// convert string according codepage and BIFF version
if ($version == self::XLS_BIFF8) {
$retstr = $this->_encodeUTF16($retstr, $asciiEncoding);
} else {
// SST only occurs in BIFF8, so why this part?
$retstr = $this->_decodeCodepage($retstr);
}
$fmtRuns = array();
if ($richString) {
// list of formatting runs
for ($j = 0; $j < $formattingRuns; $j++) {
// first formatted character; zero-based
$charPos = $this->_getInt2d($this->_data, $spos + $j * 4);
// index to font record
$fontIndex = $this->_getInt2d($this->_data, $spos + 2 + $j * 4);
$fmtRuns[] = array('charPos' => $charPos, 'fontIndex' => $fontIndex);
}
$spos += 4 * $formattingRuns;
}
if ($extendedString) {
// For Asian phonetic settings, we skip the extended string data
$spos += $extendedRunLength;
}
$this->_sst[] = array('value' => $retstr, 'fmtRuns' => $fmtRuns);
}
break;
case self::XLS_Type_FILEPASS:
/**
* SHEETPROTECTION
*
* This record is part of the File Protection Block. It
* contains information about the read/write password of the
* file. All record contents following this record will be
* encrypted.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
return false;
break;
case self::XLS_Type_EXTERNALBOOK:
break;
case self::XLS_Type_EXTSHEET:
// external sheet references provided for named cells
if ($version == self::XLS_BIFF8) {
$xpos = $pos + 4;
$xcnt = ord($this->_data[$xpos]) | ord($this->_data[$xpos + 1]) << 8;
for ($x = 0; $x < $xcnt; $x++) {
$this->_extshref[$x] = ord($this->_data[$xpos + 4 + 6 * $x]) | ord($this->_data[$xpos + 5 + 6 * $x]) << 8;
}
}
// this if statement is going to replace the above one later
if ($version == self::XLS_BIFF8) {
// offset: 0; size: 2; number of following ref structures
$nm = $this->_GetInt2d($recordData, 0);
for ($i = 0; $i < $nm; $i++) {
$this->_ref[] = array('externalBookIndex' => $this->_getInt2d($recordData, 2 + 6 * $i), 'firstSheetIndex' => $this->_getInt2d($recordData, 4 + 6 * $i), 'lastSheetIndex' => $this->_getInt2d($recordData, 6 + 6 * $i));
}
}
break;
case self::XLS_Type_NAME:
/**
* DEFINEDNAME
*
* This record is part of a Link Table. It contains the name
* and the token array of an internal defined name. Token
* arrays of defined names contain tokens with aberrant
* token classes.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
// retrieves named cells
$npos = $pos + 4;
$opts = ord($this->_data[$npos]) | ord($this->_data[$npos + 1]) << 8;
$nlen = ord($this->_data[$npos + 3]);
$flen = ord($this->_data[$npos + 4]) | ord($this->_data[$npos + 5]) << 8;
$fpos = $npos + 14 + 1 + $nlen;
$nstr = substr($this->_data, $npos + 15, $nlen);
$ftoken = ord($this->_data[$fpos]);
if ($ftoken == 58 && $opts == 0 && $flen == 7) {
$xref = ord($this->_data[$fpos + 1]) | ord($this->_data[$fpos + 2]) << 8;
$frow = ord($this->_data[$fpos + 3]) | ord($this->_data[$fpos + 4]) << 8;
$fcol = ord($this->_data[$fpos + 5]);
if (array_key_exists($xref, $this->_extshref)) {
$fsheet = $this->_extshref[$xref];
} else {
$fsheet = '';
}
$this->_namedcells[$nstr] = array('sheet' => $fsheet, 'row' => $frow, 'column' => $fcol);
}
break;
case self::XLS_Type_FORMAT:
/**
* FORMAT
*
* This record contains information about a number format.
* All FORMAT records occur together in a sequential list.
*
* In BIFF2-BIFF4 other records referencing a FORMAT record
* contain a zero-based index into this list. From BIFF5 on
* the FORMAT record contains the index itself that will be
* used by other records.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
//$indexCode = ord($this->_data[$pos + 4]) | ord($this->_data[$pos + 5]) << 8;
$indexCode = $this->_GetInt2d($recordData, 0);
/*
if ($version == self::XLS_BIFF8) {
*/
$formatString = $this->_readUnicodeStringLong(substr($recordData, 2));
/*
} else {
$numchars = ord($this->_data[$pos + 6]);
$formatString = substr($this->_data, $pos + 7, $numchars*2);
}
*/
$this->_formatRecords[$indexCode] = $formatString;
// now also stored in array _format[]
// _formatRecords[] will be removed from code later
$this->_numberFormat[$indexCode] = $formatString;
break;
case self::XLS_Type_FONT:
/**
* FONT
*/
$this->_font[] = $this->_readFont($recordData);
break;
case self::XLS_Type_XF:
/**
* XF - Extended Format
*
* This record contains formatting information for cells,
* rows, columns or styles.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
$indexCode = ord($this->_data[$pos + 6]) | ord($this->_data[$pos + 7]) << 8;
if (array_key_exists($indexCode, $this->_dateFormats)) {
$this->_formatRecords['xfrecords'][] = array('type' => 'date', 'format' => $this->_dateFormats[$indexCode], 'code' => $indexCode);
} elseif (array_key_exists($indexCode, $this->_percentFormats)) {
$this->_formatRecords['xfrecords'][] = array('type' => 'percent', 'format' => $this->_percentFormats[$indexCode], 'code' => $indexCode);
} elseif (array_key_exists($indexCode, $this->_numberFormats)) {
$this->_formatRecords['xfrecords'][] = array('type' => 'number', 'format' => $this->_numberFormats[$indexCode], 'code' => $indexCode);
} else {
if ($indexCode > 0 && isset($this->_formatRecords[$indexCode])) {
// custom formats...
$formatstr = $this->_formatRecords[$indexCode];
if ($formatstr) {
// dvc: reg exp changed to custom date/time format chars
if (preg_match("/^[hmsdy]/i", $formatstr)) {
// custom datetime format
// dvc: convert Excel formats to PHP date formats
// first remove escapes related to non-format characters
$formatstr = str_replace('\\', '', $formatstr);
// 4-digit year
$formatstr = str_replace('yyyy', 'Y', $formatstr);
// 2-digit year
$formatstr = str_replace('yy', 'y', $formatstr);
// first letter of month - no php equivalent
$formatstr = str_replace('mmmmm', 'M', $formatstr);
// full month name
$formatstr = str_replace('mmmm', 'F', $formatstr);
// short month name
$formatstr = str_replace('mmm', 'M', $formatstr);
// mm is minutes if time or month w/leading zero
$formatstr = str_replace(':mm', ':i', $formatstr);
// tmp place holder
$formatstr = str_replace('mm', 'x', $formatstr);
// month no leading zero
$formatstr = str_replace('m', 'n', $formatstr);
// month leading zero
$formatstr = str_replace('x', 'm', $formatstr);
// 12-hour suffix
$formatstr = str_replace('AM/PM', 'A', $formatstr);
// tmp place holder
$formatstr = str_replace('dd', 'x', $formatstr);
// days no leading zero
$formatstr = str_replace('d', 'j', $formatstr);
// days leading zero
$formatstr = str_replace('x', 'd', $formatstr);
// seconds
$formatstr = str_replace('ss', 's', $formatstr);
// fractional seconds - no php equivalent
$formatstr = str_replace('.S', '', $formatstr);
if (!strpos($formatstr, 'A')) {
// 24-hour format
$formatstr = str_replace('h', 'H', $formatstr);
}
// user defined flag symbol????
$formatstr = str_replace(';@', '', $formatstr);
$this->_formatRecords['xfrecords'][] = array('type' => 'date', 'format' => $formatstr, 'code' => $indexCode);
} else {
if (preg_match('/%$/', $formatstr)) {
// % number format
if (preg_match('/\\.[#0]+/i', $formatstr, $m)) {
$s = substr($m[0], 0, 1) . (strlen($m[0]) - 1);
$formatstr = str_replace($m[0], $s, $formatstr);
}
if (preg_match('/^[#0]+/', $formatstr, $m)) {
$formatstr = str_replace($m[0], strlen($m[0]), $formatstr);
}
$formatstr = '%' . str_replace('%', "f%%", $formatstr);
$this->_formatRecords['xfrecords'][] = array('type' => 'percent', 'format' => $formatstr, 'code' => $indexCode);
} else {
// dvc: changed to add format to unknown for debug
$this->_formatRecords['xfrecords'][] = array('type' => 'other', 'format' => $this->_defaultFormat, 'code' => $indexCode);
}
}
}
} else {
// dvc: changed to add format to unknown for debug
if (isset($this->_formatRecords[$indexCode])) {
$formatstr = $this->_formatRecords[$indexCode];
$type = 'undefined';
} else {
$formatstr = $this->_defaultFormat;
$type = 'default';
}
$this->_formatRecords['xfrecords'][] = array('type' => $type, 'format' => $formatstr, 'code' => $indexCode);
}
}
// store styles in xf array
$this->_xf[] = $this->_readBIFF8Style($recordData);
break;
case self::XLS_Type_NINETEENFOUR:
/**
* DATEMODE
*
* This record specifies the base date for displaying date
* values. All dates are stored as count of days past this
* base date. In BIFF2-BIFF4 this record is part of the
* Calculation Settings Block. In BIFF5-BIFF8 it is
* stored in the Workbook Globals Substream.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
$this->_nineteenFour = ord($this->_data[$pos + 4]) == 1;
/*
if (ord($this->_data[$pos + 4]) == 1) {
PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_MAC_1904);
} else {
PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_WINDOWS_1900);
}
*/
break;
case self::XLS_Type_BOUNDSHEET:
/**
* SHEET
*
* This record is located in the Workbook Globals
* Substream and represents a sheet inside the workbook.
* One SHEET record is written for each sheet. It stores the
* sheet name and a stream offset to the BOF record of the
* respective Sheet Substream within the Workbook Stream.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
$rec_offset = $this->_GetInt4d($this->_data, $pos + 4);
$rec_typeFlag = ord($this->_data[$pos + 8]);
$rec_visibilityFlag = ord($this->_data[$pos + 9]);
$rec_length = ord($this->_data[$pos + 10]);
if ($version == self::XLS_BIFF8) {
$compressedUTF16 = (ord($this->_data[$pos + 11]) & 0x1) == 0;
$rec_length = $compressedUTF16 ? $rec_length : $rec_length * 2;
$rec_name = $this->_encodeUTF16(substr($this->_data, $pos + 12, $rec_length), $compressedUTF16);
} elseif ($version == self::XLS_BIFF7) {
$rec_name = substr($this->_data, $pos + 11, $rec_length);
}
$this->_boundsheets[] = array('name' => $rec_name, 'offset' => $rec_offset);
break;
case self::XLS_Type_CODEPAGE:
/**
* CODEPAGE
*
* This record stores the text encoding used to write byte
* strings, stored as MS Windows code page identifier.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
$codepage = $this->_GetInt2d($this->_data, $pos + 4);
switch ($codepage) {
case 367:
// ASCII
$this->_codepage = "ASCII";
break;
case 437:
//OEM US
$this->_codepage = "CP437";
break;
case 720:
//OEM Arabic
// currently not supported by libiconv
$this->_codepage = "";
break;
case 737:
//OEM Greek
$this->_codepage = "CP737";
break;
case 775:
//OEM Baltic
$this->_codepage = "CP775";
break;
case 850:
//OEM Latin I
$this->_codepage = "CP850";
break;
case 852:
//OEM Latin II (Central European)
$this->_codepage = "CP852";
break;
case 855:
//OEM Cyrillic
$this->_codepage = "CP855";
break;
case 857:
//OEM Turkish
$this->_codepage = "CP857";
break;
case 858:
//OEM Multilingual Latin I with Euro
$this->_codepage = "CP858";
break;
case 860:
//OEM Portugese
$this->_codepage = "CP860";
break;
case 861:
//OEM Icelandic
$this->_codepage = "CP861";
break;
case 862:
//OEM Hebrew
$this->_codepage = "CP862";
break;
case 863:
//OEM Canadian (French)
$this->_codepage = "CP863";
break;
case 864:
//OEM Arabic
$this->_codepage = "CP864";
break;
case 865:
//OEM Nordic
$this->_codepage = "CP865";
break;
case 866:
//OEM Cyrillic (Russian)
$this->_codepage = "CP866";
break;
case 869:
//OEM Greek (Modern)
$this->_codepage = "CP869";
break;
case 874:
//ANSI Thai
$this->_codepage = "CP874";
break;
case 932:
//ANSI Japanese Shift-JIS
$this->_codepage = "CP932";
break;
case 936:
//ANSI Chinese Simplified GBK
$this->_codepage = "CP936";
break;
case 949:
//ANSI Korean (Wansung)
$this->_codepage = "CP949";
break;
case 950:
//ANSI Chinese Traditional BIG5
$this->_codepage = "CP950";
break;
case 1200:
//UTF-16 (BIFF8)
$this->_codepage = "UTF-16LE";
break;
case 1250:
// ANSI Latin II (Central European)
$this->_codepage = "CP1250";
break;
case 1251:
//ANSI Cyrillic
$this->_codepage = "CP1251";
break;
case 1252:
//ANSI Latin I (BIFF4-BIFF7)
$this->_codepage = "CP1252";
break;
case 1253:
//ANSI Greek
$this->_codepage = "CP1253";
break;
case 1254:
//ANSI Turkish
$this->_codepage = "CP1254";
break;
case 1255:
//ANSI Hebrew
$this->_codepage = "CP1255";
break;
case 1256:
//ANSI Arabic
$this->_codepage = "CP1256";
break;
case 1257:
//ANSI Baltic
$this->_codepage = "CP1257";
break;
case 1258:
//ANSI Vietnamese
$this->_codepage = "CP1258";
break;
case 1361:
//ANSI Korean (Johab)
$this->_codepage = "CP1361";
break;
case 10000:
//Apple Roman
// currently not supported by libiconv
$this->_codepage = "";
break;
case 32768:
//Apple Roman
// currently not supported by libiconv
$this->_codepage = "";
break;
case 32769:
//ANSI Latin I (BIFF2-BIFF3)
// currently not supported by libiconv
$this->_codepage = "";
break;
}
break;
}
$pos += $length + 4;
$code = ord($this->_data[$pos]) | ord($this->_data[$pos + 1]) << 8;
$length = ord($this->_data[$pos + 2]) | ord($this->_data[$pos + 3]) << 8;
$recordData = substr($this->_data, $pos + 4, $length);
}
/**
*
* PARSE THE INDIVIDUAL SHEETS
*
**/
foreach ($this->_boundsheets as $key => $val) {
// add sheet to PHPExcel object
$sheet = $excel->createSheet();
$sheet->setTitle((string) $val['name']);
$this->_sn = $key;
$spos = $val['offset'];
$cont = true;
// read BOF
$code = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
$length = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
$version = ord($this->_data[$spos + 4]) | ord($this->_data[$spos + 5]) << 8;
$substreamType = ord($this->_data[$spos + 6]) | ord($this->_data[$spos + 7]) << 8;
if ($version != self::XLS_BIFF8 && $version != self::XLS_BIFF7) {
return -1;
}
if ($substreamType != self::XLS_Worksheet) {
return -2;
}
$spos += $length + 4;
while ($cont) {
$lowcode = ord($this->_data[$spos]);
if ($lowcode == self::XLS_Type_EOF) {
break;
}
$code = $lowcode | ord($this->_data[$spos + 1]) << 8;
$length = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
$recordData = substr($this->_data, $spos + 4, $length);
$spos += 4;
$this->_sheets[$this->_sn]['maxrow'] = $this->_rowoffset - 1;
$this->_sheets[$this->_sn]['maxcol'] = $this->_coloffset - 1;
unset($this->_rectype);
unset($this->_formula);
unset($this->_formula_result);
$this->_multiplier = 1;
// need for format with %
switch ($code) {
case self::XLS_Type_DIMENSION:
/**
* DIMENSION
*
* This record contains the range address of the used area
* in the current sheet.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
if (!isset($this->_numRows)) {
if ($length == 10 || $version == self::XLS_BIFF7) {
$this->_sheets[$this->_sn]['numRows'] = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
$this->_sheets[$this->_sn]['numCols'] = ord($this->_data[$spos + 6]) | ord($this->_data[$spos + 7]) << 8;
} else {
$this->_sheets[$this->_sn]['numRows'] = ord($this->_data[$spos + 4]) | ord($this->_data[$spos + 5]) << 8;
$this->_sheets[$this->_sn]['numCols'] = ord($this->_data[$spos + 10]) | ord($this->_data[$spos + 11]) << 8;
}
}
break;
case self::XLS_Type_MERGEDCELLS:
/**
* MERGEDCELLS
*
* This record contains the addresses of merged cell ranges
* in the current sheet.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
if ($version == self::XLS_BIFF8 && !$this->_readDataOnly) {
$cellRanges = $this->_GetInt2d($this->_data, $spos);
for ($i = 0; $i < $cellRanges; $i++) {
$fr = $this->_GetInt2d($this->_data, $spos + 8 * $i + 2);
// first row
$lr = $this->_GetInt2d($this->_data, $spos + 8 * $i + 4);
// last row
$fc = $this->_GetInt2d($this->_data, $spos + 8 * $i + 6);
// first column
$lc = $this->_GetInt2d($this->_data, $spos + 8 * $i + 8);
// last column
// this part no longer needed, instead apply cell merge on PHPExcel worksheet object
/*
if ($lr - $fr > 0) {
$this->_sheets[$this->_sn]['cellsInfo'][$fr + 1][$fc + 1]['rowspan'] = $lr - $fr + 1;
}
if ($lc - $fc > 0) {
$this->_sheets[$this->_sn]['cellsInfo'][$fr + 1][$fc + 1]['colspan'] = $lc - $fc + 1;
}
*/
$sheet->mergeCellsByColumnAndRow($fc, $fr + 1, $lc, $lr + 1);
}
}
break;
case self::XLS_Type_RK:
case self::XLS_Type_RK2:
/**
* RK
*
* This record represents a cell that contains an RK value
* (encoded integer or floating-point value). If a
* floating-point value cannot be encoded to an RK value,
* a NUMBER record will be written. This record replaces the
* record INTEGER written in BIFF2.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
$row = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
$column = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
$rknum = $this->_GetInt4d($this->_data, $spos + 6);
$numValue = $this->_GetIEEE754($rknum);
/*
if ($this->_isDate($spos)) {
list($string, $raw) = $this->_createDate($numValue);
} else {
$raw = $numValue;
if (isset($this->_columnsFormat[$column + 1])){
$this->_curformat = $this->_columnsFormat[$column + 1];
}
$string = sprintf($this->_curformat,$numValue*$this->_multiplier);
}
*/
// offset 4; size: 2; index to XF record
$xfindex = $this->_getInt2d($recordData, 4);
// add BIFF8 style information
if ($version == self::XLS_BIFF8 && !$this->_readDataOnly) {
$sheet->getStyleByColumnAndRow($column, $row + 1)->applyFromArray($this->_xf[$xfindex]);
if (PHPExcel_Shared_Date::isDateTimeFormatCode($this->_xf[$xfindex]['numberformat']['code'])) {
$numValue = (int) PHPExcel_Shared_Date::ExcelToPHP($numValue);
}
}
//$this->_addcell($row, $column, $string, $raw);
//$sheet->setCellValueByColumnAndRow($column, $row + 1, $string);
$sheet->setCellValueByColumnAndRow($column, $row + 1, $numValue);
break;
case self::XLS_Type_LABELSST:
/**
* LABELSST
*
* This record represents a cell that contains a string. It
* replaces the LABEL record and RSTRING record used in
* BIFF2-BIFF5.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
$row = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
$column = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
$xfindex = ord($this->_data[$spos + 4]) | ord($this->_data[$spos + 5]) << 8;
$index = $this->_GetInt4d($this->_data, $spos + 6);
//$this->_addcell($row, $column, $this->_sst[$index]);
if ($fmtRuns = $this->_sst[$index]['fmtRuns']) {
// then we have rich text
$richText = new PHPExcel_RichText($sheet->getCellByColumnAndRow($column, $row + 1));
$charPos = 0;
for ($i = 0; $i <= count($this->_sst[$index]['fmtRuns']); $i++) {
if (isset($fmtRuns[$i])) {
$text = mb_substr($this->_sst[$index]['value'], $charPos, $fmtRuns[$i]['charPos'] - $charPos, 'UTF-8');
$charPos = $fmtRuns[$i]['charPos'];
} else {
$text = mb_substr($this->_sst[$index]['value'], $charPos, mb_strlen($this->_sst[$index]['value']), 'UTF-8');
}
if (mb_strlen($text) > 0) {
$textRun = $richText->createTextRun($text);
if (isset($fmtRuns[$i - 1])) {
if ($fmtRuns[$i - 1]['fontIndex'] < 4) {
$fontIndex = $fmtRuns[$i - 1]['fontIndex'];
} else {
// this has to do with that index 4 is omitted in all BIFF versions for some strange reason
// check the OpenOffice documentation of the FONT record
$fontIndex = $fmtRuns[$i - 1]['fontIndex'] - 1;
}
$textRun->getFont()->applyFromArray($this->_font[$fontIndex]);
}
}
}
} else {
$sheet->setCellValueByColumnAndRow($column, $row + 1, $this->_sst[$index]['value']);
}
// add BIFF8 style information
if ($version == self::XLS_BIFF8 && !$this->_readDataOnly) {
$sheet->getStyleByColumnAndRow($column, $row + 1)->applyFromArray($this->_xf[$xfindex]);
}
break;
case self::XLS_Type_MULRK:
/**
* MULRK - Multiple RK
*
* This record represents a cell range containing RK value
* cells. All cells are located in the same row.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
$row = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
$colFirst = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
$colLast = ord($this->_data[$spos + $length - 2]) | ord($this->_data[$spos + $length - 1]) << 8;
$columns = $colLast - $colFirst + 1;
$tmppos = $spos + 4;
for ($i = 0; $i < $columns; $i++) {
// offset: 0; size: 2; index to XF record
$xfindex = $this->_getInt2d($recordData, 4 + 6 * $i);
// offset: 2; size: 4; RK value
$numValue = $this->_GetIEEE754($this->_GetInt4d($this->_data, $tmppos + 2));
/*
if ($this->_isDate($tmppos-4)) {
list($string, $raw) = $this->_createDate($numValue);
} else {
$raw = $numValue;
if (isset($this->_columnsFormat[$colFirst + $i + 1])){
$this->_curformat = $this->_columnsFormat[$colFirst+ $i + 1];
}
$string = sprintf($this->_curformat, $numValue *
$this->_multiplier);
}
*/
//$this->_addcell($row, $colFirst + $i, $string, $raw);
if ($version == self::XLS_BIFF8 && !$this->_readDataOnly) {
$sheet->getStyleByColumnAndRow($colFirst + $i, $row + 1)->applyFromArray($this->_xf[$xfindex]);
if (PHPExcel_Shared_Date::isDateTimeFormatCode($this->_xf[$xfindex]['numberformat']['code'])) {
$numValue = (int) PHPExcel_Shared_Date::ExcelToPHP($numValue);
}
}
//$sheet->setCellValueByColumnAndRow($colFirst + $i, $row + 1, $string);
$sheet->setCellValueByColumnAndRow($colFirst + $i, $row + 1, $numValue);
$tmppos += 6;
}
break;
case self::XLS_Type_NUMBER:
/**
* NUMBER
*
* This record represents a cell that contains a
* floating-point value.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
$row = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
$column = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
// offset 4; size: 2; index to XF record
$xfindex = $this->_GetInt2d($recordData, 4);
$numValue = $this->_createNumber($spos);
/*
if ($this->_isDate($spos)) {
$numValue = $this->_createNumber($spos);
list($string, $raw) = $this->_createDate($numValue);
} else {
if (isset($this->_columnsFormat[$column + 1])) {
$this->_curformat = $this->_columnsFormat[$column + 1];
}
$raw = $this->_createNumber($spos);
$string = sprintf($this->_curformat, $raw * $this->_multiplier);
}
*/
// add BIFF8 style information
if ($version == self::XLS_BIFF8 && !$this->_readDataOnly) {
$sheet->getStyleByColumnAndRow($column, $row + 1)->applyFromArray($this->_xf[$xfindex]);
if (PHPExcel_Shared_Date::isDateTimeFormatCode($this->_xf[$xfindex]['numberformat']['code'])) {
$numValue = (int) PHPExcel_Shared_Date::ExcelToPHP($numValue);
}
}
//$this->_addcell($row, $column, $string, $raw);
//$sheet->setCellValueByColumnAndRow($column, $row + 1, $string);
$sheet->setCellValueByColumnAndRow($column, $row + 1, $numValue);
break;
case self::XLS_Type_FORMULA:
case self::XLS_Type_FORMULA2:
/**
* FORMULA
*
* This record contains the token array and the result of a
* formula cell.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
// offset: 0; size: 2; row index
$row = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
// offset: 2; size: 2; col index
$column = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
// offset: 4; size: 2; XF index
$xfindex = ord($this->_data[$spos + 4]) | ord($this->_data[$spos + 5]) << 8;
// offset: 6; size: 8; result of the formula
if (ord($this->_data[$spos + 6]) == 0 && ord($this->_data[$spos + 12]) == 255 && ord($this->_data[$spos + 13]) == 255) {
//String formula. Result follows in appended STRING record
$this->_formula_result = 'string';
$soff = $spos + $length;
$scode = ord($this->_data[$soff]) | ord($this->_data[$soff + 1]) << 8;
$sopt = ord($this->_data[$soff + 6]);
// only reads byte strings...
if ($scode == self::XLS_Type_STRING && $sopt == '0') {
$slen = ord($this->_data[$soff + 4]) | ord($this->_data[$soff + 5]) << 8;
$string = substr($this->_data, $soff + 7, ord($this->_data[$soff + 4]) | ord($this->_data[$soff + 5]) << 8);
} else {
$string = 'NOT FOUND';
}
$raw = $string;
} elseif (ord($this->_data[$spos + 6]) == 1 && ord($this->_data[$spos + 12]) == 255 && ord($this->_data[$spos + 13]) == 255) {
//Boolean formula. Result is in +2; 0=false,1=true
$this->_formula_result = 'boolean';
$raw = ord($this->_data[$spos + 8]);
if ($raw) {
$string = "TRUE";
} else {
$string = "FALSE";
}
} elseif (ord($this->_data[$spos + 6]) == 2 && ord($this->_data[$spos + 12]) == 255 && ord($this->_data[$spos + 13]) == 255) {
//Error formula. Error code is in +2
$this->_formula_result = 'error';
$raw = ord($this->_data[$spos + 8]);
$string = 'ERROR:' . $raw;
} elseif (ord($this->_data[$spos + 6]) == 3 && ord($this->_data[$spos + 12]) == 255 && ord($this->_data[$spos + 13]) == 255) {
//Formula result is a null string
$this->_formula_result = 'null';
$raw = '';
$string = '';
} else {
// forumla result is a number, first 14 bytes like _NUMBER record
$string = $this->_createNumber($spos);
/*
$this->_formula_result = 'number';
if ($this->_isDate($spos)) {
$numValue = $this->_createNumber($spos);
list($string, $raw) = $this->_createDate($numValue);
} else {
if (isset($this->_columnsFormat[$column + 1])){
$this->_curformat = $this->_columnsFormat[$column + 1];
}
$raw = $this->_createNumber($spos);
$string = sprintf($this->_curformat, $raw * $this->_multiplier);
}
*/
}
// save the raw formula tokens for end user interpretation
// Excel stores as a token record
$this->_rectype = 'formula';
// read formula record tokens ...
$tokenlength = ord($this->_data[$spos + 20]) | ord($this->_data[$spos + 21]) << 8;
for ($i = 0; $i < $tokenlength; $i++) {
$this->_formula[$i] = ord($this->_data[$spos + 22 + $i]);
}
// add BIFF8 style information
if ($version == self::XLS_BIFF8 && !$this->_readDataOnly) {
$sheet->getStyleByColumnAndRow($column, $row + 1)->applyFromArray($this->_xf[$xfindex]);
if (PHPExcel_Shared_Date::isDateTimeFormatCode($this->_xf[$xfindex]['numberformat']['code'])) {
$string = (int) PHPExcel_Shared_Date::ExcelToPHP($string);
}
}
//$this->_addcell($row, $column, $string, $raw);
$sheet->setCellValueByColumnAndRow($column, $row + 1, $string);
// offset: 14: size: 2; option flags, recalculate always, recalculate on open etc.
// offset: 16: size: 4; not used
// offset: 20: size: variable; formula structure
// WORK IN PROGRESS: TRUE FORMULA SUPPORT
// resolve BIFF8 formula tokens into human readable formula
// so it can be added as formula
// $formulaStructure = substr($recordData, 20);
// $formulaString = $this->_getFormulaStringFromStructure($formulaStructure); // get human language
break;
case self::XLS_Type_BOOLERR:
/**
* BOOLERR
*
* This record represents a Boolean value or error value
* cell.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
// offset: 0; size: 2; row index
$row = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
// offset: 2; size: 2; column index
$column = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
// offset: 4; size: 2; index to XF record
$xfindex = $this->_GetInt2d($recordData, 4);
// offset: 6; size: 1; the boolean value or error value
$value = ord($recordData[6]);
// offset: 7; size: 1; 0=boolean; 1=error
$isError = ord($recordData[7]);
if (!$isError) {
$sheet->getCellByColumnAndRow($column, $row + 1)->setValueExplicit((bool) $value, PHPExcel_Cell_DataType::TYPE_BOOL);
}
// add BIFF8 style information
if ($version == self::XLS_BIFF8 && !$this->_readDataOnly) {
$sheet->getStyleByColumnAndRow($column, $row + 1)->applyFromArray($this->_xf[$xfindex]);
}
break;
case self::XLS_Type_ROW:
/**
* ROW
*
* This record contains the properties of a single row in a
* sheet. Rows and cells in a sheet are divided into blocks
* of 32 rows.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
if (!$this->_readDataOnly) {
// offset: 0; size: 2; index of this row
$r = $this->_GetInt2d($recordData, 0);
// offset: 2; size: 2; index to column of the first cell which is described by a cell record
// offset: 4; size: 2; index to column of the last cell which is described by a cell record, increased by 1
// offset: 6; size: 2;
// bit: 14-0; mask: 0x7FF; height of the row, in twips = 1/20 of a point
$height = (0x7ff & $this->_GetInt2d($recordData, 6)) >> 0;
// bit: 15: mask: 0x8000; 0 = row has custom height; 1= row has default height
$useDefaultHeight = (0x8000 & $this->_GetInt2d($recordData, 6)) >> 15;
if (!$useDefaultHeight) {
$sheet->getRowDimension($r + 1)->setRowHeight($height / 20);
}
// offset: 8; size: 2; not used
// offset: 10; size: 2; not used in BIFF5-BIFF8
// offset: 12; size: 4; option flags and default row formatting
// bit: 2-0: mask: 0x00000007; outline level of the row
$level = (0x7 & $this->_GetInt4d($recordData, 12)) >> 0;
$sheet->getRowDimension($r + 1)->setOutlineLevel($level);
// bit: 4; mask: 0x00000010; 1 = outline group start or ends here... and is collapsed
$isCollapsed = (0x10 & $this->_GetInt4d($recordData, 12)) >> 4;
$sheet->getRowDimension($r + 1)->setCollapsed($isCollapsed);
// bit: 5; mask: 0x00000020; 1 = row is hidden
$isHidden = (0x20 & $this->_GetInt4d($recordData, 12)) >> 5;
$sheet->getRowDimension($r + 1)->setVisible(!$isHidden);
}
break;
case self::XLS_Type_DBCELL:
/**
* DBCELL
*
* This record is written once in a Row Block. It contains
* relative offsets to calculate the stream position of the
* first cell record for each row. The offset list in this
* record contains as many offsets as ROW records are
* present in the Row Block.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
break;
case self::XLS_Type_MULBLANK:
/**
* MULBLANK - Multiple BLANK
*
* This record represents a cell range of empty cells. All
* cells are located in the same row
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
// offset: 0; size: 2; index to row
$row = $this->_GetInt2d($recordData, 0);
// offset: 2; size: 2; index to first column
$fc = $this->_GetInt2d($recordData, 2);
// offset: 4; size: 2 x nc; list of indexes to XF records
// add BIFF8 style information
if ($version == self::XLS_BIFF8 && !$this->_readDataOnly) {
for ($i = 0; $i < $length / 2 - 4; $i++) {
$xfindex = $this->_GetInt2d($recordData, 4 + 2 * $i);
$sheet->getStyleByColumnAndRow($fc + $i, $row + 1)->applyFromArray($this->_xf[$xfindex]);
}
}
// offset: 6; size 2; index to last column (not needed)
break;
case self::XLS_Type_LABEL:
/**
* LABEL
*
* This record represents a cell that contains a string. In
* BIFF8 it is usually replaced by the LABELSST record.
* Excel still uses this record, if it copies unformatted
* text cells to the clipboard.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
$row = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
$column = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
/*
$this->_addcell($row, $column, substr($this->_data, $spos + 8,
ord($this->_data[$spos + 6]) | ord($this->_data[$spos + 7]) << 8));
*/
$sheet->setCellValueByColumnAndRow($column, $row + 1, substr($this->_data, $spos + 8, ord($this->_data[$spos + 6]) | ord($this->_data[$spos + 7]) << 8));
break;
case self::XLS_Type_PROTECT:
/**
* PROTECT - Sheet protection (BIFF2 through BIFF8)
* if this record is omitted, then it also means no sheet protection
*/
if (!$this->_readDataOnly) {
// offset: 0; size: 2;
// bit 0, mask 0x01; sheet protection
$isSheetProtected = (0x1 & $this->_GetInt2d($recordData, 0)) >> 0;
switch ($isSheetProtected) {
case 0:
break;
case 1:
$sheet->getProtection()->setSheet(true);
break;
}
}
break;
case self::XLS_Type_PASSWORD:
/**
* PASSWORD - Sheet protection (hashed) password (BIFF2 through BIFF8)
*/
if (!$this->_readDataOnly) {
// offset: 0; size: 2; 16-bit hash value of password
$password = strtoupper(dechex($this->_GetInt2d($recordData, 0)));
// the hashed password
$sheet->getProtection()->setPassword($password, true);
}
break;
case self::XLS_Type_COLINFO:
/**
* COLINFO - Column information
*/
if (!$this->_readDataOnly) {
// offset: 0; size: 2; index to first column in range
$fc = $this->_GetInt2d($recordData, 0);
// first column index
// offset: 2; size: 2; index to last column in range
$lc = $this->_GetInt2d($recordData, 2);
// first column index
// offset: 4; size: 2; width of the column in 1/256 of the width of the zero character
$width = $this->_GetInt2d($recordData, 4);
// offset: 6; size: 2; index to XF record for default column formatting
// offset: 8; size: 2; option flags
// bit: 0; mask: 0x0001; 1= columns are hidden
$isHidden = (0x1 & $this->_GetInt2d($recordData, 8)) >> 0;
// bit: 10-8; mask: 0x0700; outline level of the columns (0 = no outline)
$level = (0x700 & $this->_GetInt2d($recordData, 8)) >> 8;
// bit: 12; mask: 0x1000; 1 = collapsed
$isCollapsed = (0x1000 & $this->_GetInt2d($recordData, 8)) >> 12;
// offset: 10; size: 2; not used
for ($i = $fc; $i <= $lc; $i++) {
$sheet->getColumnDimensionByColumn($i)->setWidth($width / 256);
$sheet->getColumnDimensionByColumn($i)->setVisible(!$isHidden);
$sheet->getColumnDimensionByColumn($i)->setOutlineLevel($level);
$sheet->getColumnDimensionByColumn($i)->setCollapsed($isCollapsed);
}
}
break;
case self::XLS_Type_DEFCOLWIDTH:
// offset: 0; size: 2; row index
$width = $this->_GetInt2d($recordData, 0);
$sheet->getDefaultColumnDimension()->setWidth($width);
break;
case self::XLS_Type_DEFAULTROWHEIGHT:
// offset: 0; size: 2; option flags
// offset: 2; size: 2; default height for unused rows, (twips 1/20 point)
$height = $this->_GetInt2d($recordData, 2);
$sheet->getDefaultRowDimension()->setRowHeight($height / 20);
break;
case self::XLS_Type_BLANK:
// offset: 0; size: 2; row index
$row = $this->_GetInt2d($recordData, 0);
// offset: 2; size: 2; col index
$col = $this->_GetInt2d($recordData, 2);
// offset: 4; size: 2; XF index
$xfindex = $this->_GetInt2d($recordData, 4);
// add BIFF8 style information
if ($version == self::XLS_BIFF8 && !$this->_readDataOnly) {
$sheet->getStyleByColumnAndRow($col, $row + 1)->applyFromArray($this->_xf[$xfindex]);
}
break;
case self::XLS_Type_SHEETPR:
// offset: 0; size: 2
// bit: 6; mask: 0x0040; 0 = outline buttons above outline group
$isSummaryBelow = (0x40 & $this->_GetInt2d($recordData, 0)) >> 6;
$sheet->setShowSummaryBelow($isSummaryBelow);
// bit: 7; mask: 0x0080; 0 = outline buttons left of outline group
$isSummaryRight = (0x80 & $this->_GetInt2d($recordData, 0)) >> 7;
$sheet->setShowSummaryRight($isSummaryRight);
break;
case self::XLS_Type_EOF:
$cont = false;
break;
default:
break;
}
$spos += $length;
}
if (!isset($this->_sheets[$this->_sn]['numRows'])) {
$this->_sheets[$this->_sn]['numRows'] = $this->_sheets[$this->_sn]['maxrow'];
}
if (!isset($this->_sheets[$this->_sn]['numCols'])) {
$this->_sheets[$this->_sn]['numCols'] = $this->_sheets[$this->_sn]['maxcol'];
}
}
/*
foreach($this->_boundsheets as $index => $details) {
$sheet = $excel->getSheet($index);
// read all the columns of all the rows !
$numrows = $this->_sheets[$index]['numRows'];
$numcols = $this->_sheets[$index]['numCols'];
for ($row = 0; $row < $numrows; $row++) {
for ($col = 0; $col < $numcols; $col++) {
$cellcontent = $cellinfo = null;
if (isset($this->_sheets[$index]['cells'][$row][$col])===true) {
$cellcontent = $this->_sheets[$index]['cells'][$row][$col];
} else {
continue;
}
if (isset($this->_sheets[$index]['cellsInfo'][$row][$col])===true) {
$cellinfo = $this->_sheets[$index]['cellsInfo'][$row][$col];
}
$sheet->setCellValueByColumnAndRow($col, $row + 1,
$cellcontent);
}
}
};
*/
return $excel;
}
private static function _filterTestInDateGroupSet($cellValue, $dataSet)
{
$dateSet = $dataSet['filterValues'];
$blanks = $dataSet['blanks'];
if ($cellValue == '' || $cellValue === NULL) {
return $blanks;
}
if (is_numeric($cellValue)) {
$dateValue = PHPExcel_Shared_Date::ExcelToPHP($cellValue);
if ($cellValue < 1) {
// Just the time part
$dtVal = date('His', $dateValue);
$dateSet = $dateSet['time'];
} elseif ($cellValue == floor($cellValue)) {
// Just the date part
$dtVal = date('Ymd', $dateValue);
$dateSet = $dateSet['date'];
} else {
// date and time parts
$dtVal = date('YmdHis', $dateValue);
$dateSet = $dateSet['dateTime'];
}
foreach ($dateSet as $dateValue) {
// Use of substr to extract value at the appropriate group level
if (substr($dtVal, 0, strlen($dateValue)) == $dateValue) {
return TRUE;
}
}
}
return FALSE;
}
for ($row = 2; $row <= $highestRow; $row++) {
$array['rows'][] = array('c' => array(array('v' => 'Date(' . date('Y, m , j', strtotime('-1 month', strtotime(date('Y-m-j', PHPExcel_Shared_Date::ExcelToPHP($sheet->getCellByColumnAndRow(0, $row)->getValue()))))) . ')'), array('v' => $sheet->getCellByColumnAndRow('1', $row)->getValue()), array('v' => $sheet->getCellByColumnAndRow('2', $row)->getValue()), array('v' => $sheet->getCellByColumnAndRow('3', $row)->getValue())));
}
echoResponse(200, $array);
}
});
$app->get('/etude/:id/courbe/patient', function ($id) {
$db = new DbHandler();
$query = "SELECT laboratoire.libelle as lab,etude.libelle as et FROM etude , laboratoire WHERE etude.id_laboratoire=laboratoire.id and etude.id={$id}";
$response = $db->execute($query);
foreach ($response as &$value) {
$rep = "../../data/" . $value['lab'] . "/" . $value['et'] . "/Courbe_Patients.xlsx";
}
date_default_timezone_set('Europe/Paris');
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadDataOnly(true);
if (file_exists($rep)) {
$objPHPExcel = $objReader->load($rep);
$sheet = $objPHPExcel->getActiveSheet();
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
// Loop through each row of the worksheet in turn
$array['cols'][] = array('type' => 'date', 'id' => 'date', 'label' => 'Date');
$array['cols'][] = array('type' => 'number', 'id' => 'Theorique', 'label' => 'Théorique');
$array['cols'][] = array('type' => 'number', 'id' => 'Recrutement patient', 'label' => 'Recrutement patients');
for ($row = 2; $row <= $highestRow; $row++) {
$array['rows'][] = array('c' => array(array('v' => 'Date(' . date('Y, m , j', strtotime('-1 month', strtotime(date('Y-m-j', PHPExcel_Shared_Date::ExcelToPHP($sheet->getCellByColumnAndRow(0, $row)->getValue()))))) . ')'), array('v' => $sheet->getCellByColumnAndRow('1', $row)->getValue()), array('v' => $sheet->getCellByColumnAndRow('2', $row)->getValue())));
}
echoResponse(200, $array);
}
});
/**
* Convert a value in a pre-defined format to a PHP string
*
* @param mixed $value Value to format
* @param string $format Format code
* @param array $callBack Callback function for additional formatting of string
* @return string Formatted string
*/
public static function toFormattedString($value = '', $format = '', $callBack = null)
{
// For now we do not treat strings although section 4 of a format code affects strings
if (!is_numeric($value)) {
return $value;
}
// For 'General' format code, we just pass the value although this is not entirely the way Excel does it,
// it seems to round numbers to a total of 10 digits.
if ($format === 'General') {
return $value;
}
// Get the sections, there can be up to four sections
$sections = explode(';', $format);
// Fetch the relevant section depending on whether number is positive, negative, or zero?
// Text not supported yet.
// Here is how the sections apply to various values in Excel:
// 1 section: [POSITIVE/NEGATIVE/ZERO/TEXT]
// 2 sections: [POSITIVE/ZERO/TEXT] [NEGATIVE]
// 3 sections: [POSITIVE/TEXT] [NEGATIVE] [ZERO]
// 4 sections: [POSITIVE] [NEGATIVE] [ZERO] [TEXT]
switch (count($sections)) {
case 1:
$format = $sections[0];
break;
case 2:
$format = $value >= 0 ? $sections[0] : $sections[1];
$value = abs($value);
// Use the absolute value
break;
case 3:
$format = $value > 0 ? $sections[0] : ($value < 0 ? $sections[1] : $sections[2]);
$value = abs($value);
// Use the absolute value
break;
case 4:
$format = $value > 0 ? $sections[0] : ($value < 0 ? $sections[1] : $sections[2]);
$value = abs($value);
// Use the absolute value
break;
default:
// something is wrong, just use first section
$format = $sections[0];
break;
}
// Save format with color information for later use below
$formatColor = $format;
// Strip color information
$color_regex = '/^\\[[a-zA-Z]+\\]/';
$format = preg_replace($color_regex, '', $format);
// Let's begin inspecting the format and converting the value to a formatted string
if (preg_match('/^(\\[\\$[A-Z]*-[0-9A-F]*\\])*[hmsdy]/i', $format)) {
// datetime format
// dvc: convert Excel formats to PHP date formats
// strip off first part containing e.g. [$-F800] or [$USD-409]
// general syntax: [$<Currency string>-<language info>]
// language info is in hexadecimal
$format = preg_replace('/^(\\[\\$[A-Z]*-[0-9A-F]*\\])/i', '', $format);
// OpenOffice.org uses upper-case number formats, e.g. 'YYYY', convert to lower-case
$format = strtolower($format);
$format = strtr($format, self::$_dateFormatReplacements);
if (!strpos($format, 'A')) {
// 24-hour time format
$format = strtr($format, self::$_dateFormatReplacements24);
} else {
// 12-hour time format
$format = strtr($format, self::$_dateFormatReplacements12);
}
$value = gmdate($format, PHPExcel_Shared_Date::ExcelToPHP($value));
} else {
if (preg_match('/%$/', $format)) {
// % number format
if ($format === self::FORMAT_PERCENTAGE) {
$value = round(100 * $value, 0) . '%';
} else {
if (preg_match('/\\.[#0]+/i', $format, $m)) {
$s = substr($m[0], 0, 1) . (strlen($m[0]) - 1);
$format = str_replace($m[0], $s, $format);
}
if (preg_match('/^[#0]+/', $format, $m)) {
$format = str_replace($m[0], strlen($m[0]), $format);
}
$format = '%' . str_replace('%', 'f%%', $format);
$value = sprintf($format, 100 * $value);
}
} else {
if (preg_match("/^([0-9.,-]+)\$/", $value)) {
if ($format === self::FORMAT_CURRENCY_EUR_SIMPLE) {
$value = 'EUR ' . sprintf('%1.2f', $value);
} else {
// In Excel formats, "_" is used to add spacing, which we can't do in HTML
$format = preg_replace('/_./', '', $format);
// Some non-number characters are escaped with \, which we don't need
$format = preg_replace("/\\\\/", '', $format);
// Some non-number strings are quoted, so we'll get rid of the quotes
$format = preg_replace('/"/', '', $format);
// TEMPORARY - Convert # to 0
$format = preg_replace('/\\#/', '0', $format);
// Find out if we need thousands separator
$useThousands = preg_match('/,/', $format);
if ($useThousands) {
$format = preg_replace('/,/', '', $format);
}
if (preg_match('/0?.*\\?\\/\\?/', $format, $m)) {
//echo 'Format mask is fractional '.$format.' <br />';
$sign = $value < 0 ? '-' : '';
$integerPart = floor(abs($value));
$decimalPart = trim(fmod(abs($value), 1), '0.');
$decimalLength = strlen($decimalPart);
$decimalDivisor = pow(10, $decimalLength);
$GCD = PHPExcel_Calculation_Functions::GCD($decimalPart, $decimalDivisor);
$adjustedDecimalPart = $decimalPart / $GCD;
$adjustedDecimalDivisor = $decimalDivisor / $GCD;
if (strpos($format, '0') !== false || substr($format, 0, 3) == '? ?') {
if ($integerPart == 0) {
$integerPart = '';
}
$value = "{$sign}{$integerPart} {$adjustedDecimalPart}/{$adjustedDecimalDivisor}";
} else {
$adjustedDecimalPart += $integerPart * $adjustedDecimalDivisor;
$value = "{$sign}{$adjustedDecimalPart}/{$adjustedDecimalDivisor}";
}
} else {
// Handle the number itself
$number_regex = "/(\\d+)(\\.?)(\\d*)/";
if (preg_match($number_regex, $format, $matches)) {
$left = $matches[1];
$dec = $matches[2];
$right = $matches[3];
if ($useThousands) {
$localeconv = localeconv();
if ($localeconv['thousands_sep'] == '' || $localeconv['decimal_point'] == '') {
$value = number_format($value, strlen($right), $localeconv['mon_decimal_point'], $localeconv['mon_thousands_sep']);
} else {
$value = number_format($value, strlen($right), $localeconv['decimal_point'], $localeconv['thousands_sep']);
}
} else {
$sprintf_pattern = "%1." . strlen($right) . "f";
$value = sprintf($sprintf_pattern, $value);
}
$value = preg_replace($number_regex, $value, $format);
}
}
}
}
}
}
// Additional formatting provided by callback function
if ($callBack !== null) {
list($writerInstance, $function) = $callBack;
$value = $writerInstance->{$function}($value, $formatColor);
}
return $value;
}
function extract_ligne($sheet, $row, $highestColumnIndex)
{
//fonction permettant d'extraire une ligne d'un fichier excel, pour une ligne donnée, étant fourni la colonne maxi au format numérique
$entree = array();
// On boucle sur les cellule de la ligne
for ($col = 0; $col <= $highestColumnIndex; ++$col) {
//on affecte la cellule
$cell = $sheet->getCellByColumnAndRow($col, $row);
//Pour chaque colonne on affecte au tableau $entree
switch ($cell->GetColumn()) {
case "A":
$entree['numero'] = $cell->Getvalue();
break;
case "B":
$entree['date_ouverture'] = PHPExcel_Shared_Date::ExcelToPHP($cell->getvalue());
break;
case "C":
$entree['date_maj'] = PHPExcel_Shared_Date::ExcelToPHP($cell->getvalue());
break;
case "D":
$testdate = $cell->Getvalue();
if ($testdate != "") {
$entree['date_cloture'] = PHPExcel_Shared_Date::ExcelToPHP($cell->getvalue());
} else {
$entree['date_cloture'] = "";
}
break;
case "E":
$entree['titre'] = $cell->Getvalue();
break;
case "F":
$entree['description'] = $cell->Getvalue();
break;
case "G":
$entree['action_maj'] = $cell->Getvalue();
break;
case "H":
$entree['solution'] = $cell->Getvalue();
break;
case "I":
$entree['etat'] = $cell->Getvalue();
break;
case "J":
$entree['ci'] = $cell->Getvalue();
break;
case "K":
$entree['user_creation'] = $cell->Getvalue();
break;
case "L":
$entree['user_maj'] = $cell->Getvalue();
break;
case "M":
$entree['user_cloture'] = $cell->Getvalue();
break;
case "N":
$entree['groupe_createur'] = $cell->Getvalue();
break;
case "O":
$entree['groupe_affectation'] = $cell->Getvalue();
break;
case "P":
$entree['groupe_cloture'] = $cell->Getvalue();
break;
case "U":
$entree['priorite'] = $cell->Getvalue();
break;
case "V":
$entree['code_cloture'] = $cell->Getvalue();
break;
case "W":
$entree['fournisseur'] = $cell->Getvalue();
break;
case "X":
$entree['ref'] = $cell->Getvalue();
break;
case "Z":
$entree['element_ci'] = $cell->Getvalue();
break;
case "AA":
$entree['user_beneficiaire'] = $cell->Getvalue();
break;
case "AB":
$entree['candidat_bdd'] = $cell->Getvalue();
break;
case "AD":
$entree['user_email'] = $cell->Getvalue();
break;
case "AE":
$entree['user_tel_fixe'] = $cell->Getvalue();
break;
case "AF":
$entree['user_tel_portable'] = $cell->Getvalue();
break;
}
}
//on renvoie le tableau contenant les résultats
return $entree;
}
public function select($source)
{
$path = $this->connection;
$excel = PHPExcel_IOFactory::createReaderForFile($path);
$excel->setReadDataOnly(false);
$excel = $excel->load($path);
$excRes = new ExcelResult();
$excelWS = $excel->getActiveSheet();
$addFields = true;
$coords = array();
if ($source->get_source() == '*') {
$coords['start_row'] = 0;
$coords['end_row'] = false;
} else {
$c = array();
preg_match("/^([a-zA-Z]+)(\\d+)/", $source->get_source(), $c);
if (count($c) > 0) {
$coords['start_row'] = (int) $c[2];
} else {
$coords['start_row'] = 0;
}
$c = array();
preg_match("/:(.+)(\\d+)\$/U", $source->get_source(), $c);
if (count($c) > 0) {
$coords['end_row'] = (int) $c[2];
} else {
$coords['end_row'] = false;
}
}
$i = $coords['start_row'];
$end = 0;
while ($coords['end_row'] == false && $end < $this->emptyLimit || $coords['end_row'] !== false && $i < $coords['end_row']) {
$r = array();
$emptyNum = 0;
for ($j = 0; $j < count($this->config->text); $j++) {
$col = PHPExcel_Cell::columnIndexFromString($this->config->text[$j]['name']) - 1;
$cell = $excelWS->getCellByColumnAndRow($col, $i);
if (PHPExcel_Shared_Date::isDateTime($cell)) {
$r[PHPExcel_Cell::stringFromColumnIndex($col)] = PHPExcel_Shared_Date::ExcelToPHP($cell->getValue());
} else {
if ($cell->getDataType() == 'f') {
$r[PHPExcel_Cell::stringFromColumnIndex($col)] = $cell->getCalculatedValue();
} else {
$r[PHPExcel_Cell::stringFromColumnIndex($col)] = $cell->getValue();
}
}
if ($r[PHPExcel_Cell::stringFromColumnIndex($col)] == '') {
$emptyNum++;
}
}
if ($emptyNum < count($this->config->text)) {
$r['id'] = $i;
$excRes->addRecord($r);
$end = 0;
} else {
if (DHX_IGNORE_EMPTY_ROWS == false) {
$r['id'] = $i;
$excRes->addRecord($r);
}
$end++;
}
$i++;
}
return $excRes;
}
$F = $objPHPExcel->getActiveSheet()->getCell('F' . $i)->getCalculatedValue();
$G = $objPHPExcel->getActiveSheet()->getCell('G' . $i)->getCalculatedValue();
$H = $objPHPExcel->getActiveSheet()->getCell('H' . $i)->getCalculatedValue();
$I = $objPHPExcel->getActiveSheet()->getCell('I' . $i)->getCalculatedValue();
$J = $objPHPExcel->getActiveSheet()->getCell('J' . $i)->getCalculatedValue();
$K = $objPHPExcel->getActiveSheet()->getCell('K' . $i)->getCalculatedValue();
$L = $objPHPExcel->getActiveSheet()->getCell('L' . $i)->getCalculatedValue();
$M = $objPHPExcel->getActiveSheet()->getCell('M' . $i)->getCalculatedValue();
$N = $objPHPExcel->getActiveSheet()->getCell('N' . $i)->getCalculatedValue();
$fechaK = PHPExcel_Shared_Date::ExcelToPHP($K);
$dateK = date('Y-m-d H:i:s', $fechaK);
$fechaL = PHPExcel_Shared_Date::ExcelToPHP($L);
$dateL = date('Y-m-d H:i:s', $fechaL);
$fechaM = PHPExcel_Shared_Date::ExcelToPHP($M);
$dateM = date('Y-m-d H:i:s', $fechaM);
$fechaN = PHPExcel_Shared_Date::ExcelToPHP($N);
$dateN = date('Y-m-d H:i:s', $fechaN);
$time_difference = strtotime($dateN) - strtotime($dateM);
$minutes = round($time_difference / 60);
mysqli_query($cn, "INSERT INTO servicios_temporales (porteador,tren,prog_especial,circulacion,\n\t\t\t\t\t\t\t\t\t\t\t\ttipo_equipo,num_equipo,km_prog,km_reales,est_origen_real,est_destino_real,h_salida_prog,\n\t\t\t\t\t\t\t\t\t\t\t\th_salida_real,h_llegada_prog,h_llegada_real,dif_minutos) VALUES ('{$A}','{$B}','{$C}','{$D}','{$E}',\n\t\t\t\t\t\t\t\t\t\t\t\t'{$F}','{$G}','{$H}','{$I}','{$J}','{$dateK}','{$dateL}','{$dateM}','{$dateN}','{$minutes}')");
mysqli_query($cn, "DELETE FROM servicios_temporales WHERE porteador=''");
if ($objPHPExcel->getActiveSheet()->getCell('A' . $i)->getCalculatedValue() == NULL) {
$param = 1;
}
$i++;
$contador = $contador + 1;
}
$totalingresos = $contador - 1;
header("Location: index.php");
} else {
echo "Debe cargar el archivo";
function transferExcelProps($moduletype, $prop, $val)
{
global $adb;
global $current_user;
// $accountproparr=array('bill_state','accounttype','industry','ownership');
// $contactproparr=array('department','title','salutationtype','cf_547','cf_549','cf_551','cf_553','cf_555');
if ($moduletype == 1) {
if ($prop == 'smownerid') {
$sql = "select ec_users.id from ec_users where user_name='{$val}' ";
//echo $sql;
$result = $adb->query($sql);
$userid = $adb->query_result($result, 0, "id");
if (empty($userid)) {
$userid = $current_user->id;
}
return $userid;
} elseif ($prop == 'account_id') {
return $this->add_create_account($val);
} elseif ($prop == 'contact_id') {
return $this->add_create_contact($val);
} elseif ($prop == 'duedate') {
if (is_numeric($val)) {
return date("Y-m-d", PHPExcel_Shared_Date::ExcelToPHP($val));
}
}
}
return $val;
}
public function actionFullupload()
{
/*
$price_update = Prices::model()->findByAttributes(['trade_date'=>'0000-00-00', 'instrument_id' =>43],
[
'condition'=>'price!=:price',
'params'=>array('price'=>17.420),
]
);
var_dump($price_update);
exit;
*/
$model = new Uploads();
//$path = Yii::app()->basePath.'../../uploads/';
$path = Yii::getPathOfAlias('webroot') . '/uploads/';
if (isset($_POST['Uploads'])) {
Yii::import('ext.phpexcel.XPHPExcel');
XPHPExcel::init();
ini_set('max_execution_time', 150000);
ini_set("memory_limit", "128M");
require_once Yii::app()->basePath . '/extensions/XLSXReader/XLSXReader.php';
//OKarray(2) { ["Uploads"]=> array(2) { ["instrument_id"]=> string(2) "12" ["upload_description"]=> string(5) "sfggs" } ["yt0"]=> string(6) "Upload" }
$model->attributes = $_POST['Uploads'];
if ($upload_file = self::uploadMultifile($model, 'upload_file', $path)) {
$model->upload_file = implode(",", $upload_file);
}
$model->user_id = Yii::app()->user->id;
//$instrument_id = $model->instrument_id;
//////////////////////////////////////////
if ($model->validate()) {
//Upload File //
if ($model->save()) {
$upload_file_id = Yii::app()->db->getLastInsertID();
$csvFile = CUploadedFile::getInstance($model, 'upload_file', '../../uploads/');
$tempLoc = Yii::getPathOfAlias('webroot') . '/uploads/' . $model->upload_file;
$xlsx = new XLSXReader($tempLoc);
$data = $xlsx->getSheetData('Sheet1');
$instruments = Instruments::model()->findAll(array('select' => 'id, instrument'));
$instruments_for_returns_update = [];
foreach ($data as $dat) {
$trade_date = gmdate('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($dat['0']));
$instrument_name = trim($dat['1']);
$price = $dat['2'];
$currency = $dat['3'];
$instrument = Instruments::model()->findByAttributes(['instrument' => $instrument_name, 'is_current' => 1]);
if ($instrument) {
$instrument_id = $instrument->id;
$instruments_for_returns_update[] = $instrument_id;
} else {
$new_instrument = new Instruments();
$new_instrument->instrument = $instrument_name;
$new_instrument->price_uploaded = 1;
$new_instrument->currency = $currency;
$new_instrument->save();
$instrument_id = $new_instrument->id;
$instruments_for_returns_update[] = $instrument_id;
}
$existing_record = Prices::model()->findByAttributes(['trade_date' => $trade_date, 'instrument_id' => $instrument_id]);
if ($existing_record) {
if ($existing_record->price !== $price) {
$existing_record->price = $price;
$existing_record->upload_file_id = $upload_file_id;
$existing_record->save();
}
} else {
$new_price = new Prices();
$new_price->instrument_id = $instrument_id;
$new_price->trade_date = $trade_date;
$new_price->price = $price;
$new_price->upload_file_id = $upload_file_id;
//$new_price->name = $instrument_name;
$new_price->save();
}
}
$unique_instruments_for_returns_update = array_unique($instruments_for_returns_update);
Returns::model()->instrumnetReturnsUpdate($unique_instruments_for_returns_update);
Yii::app()->user->setFlash('success', "Prices Uploaded!");
@chmod($tempLoc, 0777);
@unlink($tempLoc);
//unlink(Yii::getPathOfAlias('webroot').'/uploads/'.$model->upload_file);
//$this->redirect(array('view','id'=>$model->id));
$user_data = Users::model()->findByPk(Yii::app()->user->id);
$step_completed = $user_data->step_completed;
if ($user_data->user_role == 2 && $step_completed < 2) {
$user_data->step_completed = 1;
$user_data->save();
$this->redirect(Yii::app()->baseUrl . '/site/admin');
}
//else{
// $this->redirect(Yii::app()->baseUrl.'/site/admin');
// $this->render('overview', ['user_data' => $user_data]); }
}
}
///////////////////////////////////////////
}
$this->render('upload_form', array('model' => $model));
}
$_DATOS_EXCEL2[$i]['Date_of_collection'] = $objFree->getActiveSheet()->getCell('C' . $i)->getCalculatedValue();
$_DATOS_EXCEL2[$i]['Collected_by'] = $objFree->getActiveSheet()->getCell('D' . $i)->getCalculatedValue();
$_DATOS_EXCEL2[$i]['Site_alias'] = $objFree->getActiveSheet()->getCell('E' . $i)->getCalculatedValue();
$_DATOS_EXCEL2[$i]['id_storage'] = $objFree->getActiveSheet()->getCell('F' . $i)->getCalculatedValue();
$_DATOS_EXCEL2[$i]['Latitude'] = $objFree->getActiveSheet()->getCell('G' . $i)->getCalculatedValue();
$_DATOS_EXCEL2[$i]['Longitude'] = $objFree->getActiveSheet()->getCell('H' . $i)->getCalculatedValue();
$_DATOS_EXCEL2[$i]['Altitude'] = $objFree->getActiveSheet()->getCell('I' . $i)->getCalculatedValue();
$_DATOS_EXCEL2[$i]['Type_of_sample'] = $objFree->getActiveSheet()->getCell('J' . $i)->getCalculatedValue();
$_DATOS_EXCEL2[$i]['Characteristics_of_sample'] = $objFree->getActiveSheet()->getCell('K' . $i)->getCalculatedValue();
$_DATOS_EXCEL2[$i]['Additional_comments'] = $objFree->getActiveSheet()->getCell('L' . $i)->getCalculatedValue();
$_DATOS_EXCEL2[$i]['subio'] = $objFree->getActiveSheet()->getCell('N' . $i)->getCalculatedValue();
$_DATOS_EXCEL2[$i]['lab'] = $objFree->getActiveSheet()->getCell('M' . $i)->getCalculatedValue();
$cell = $objFree->getActiveSheet()->getCell('C' . $i);
$InvDate = $cell->getValue();
if (PHPExcel_Shared_Date::isDateTime($cell)) {
$InvDate = date($format = "Y-m-d", PHPExcel_Shared_Date::ExcelToPHP($InvDate));
}
$_DATOS_EXCEL2[$i]['Date_of_collection'] = $InvDate;
$bus = $_DATOS_EXCEL2[$i]['lab'];
$busLab = utf8_decode($bus);
$traducelab = "Select id_lab from lab where Name='{$busLab}'";
$cs1 = mysql_query($traducelab, $cn);
while ($resula = mysql_fetch_array($cs1)) {
$Id_laboratorio = $resula[0];
}
$_DATOS_EXCEL2[$i]['lab'] = $Id_laboratorio;
$colle = $_DATOS_EXCEL2[$i]['Collected_by'];
$busCollec = utf8_decode($colle);
$traduceuser = "******";
$cs2 = mysql_query($traduceuser, $cn);
while ($resula1 = mysql_fetch_array($cs2)) {
/**
* Test if cell date value is matches a set of values defined by a set of months
*
* @param mixed $cellValue
* @param mixed[] $monthSet
* @return boolean
*/
private static function _filterTestInPeriodDateSet($cellValue, $monthSet)
{
// Blank cells are always ignored, so return a FALSE
if ($cellValue == '' || $cellValue === NULL) {
return FALSE;
}
if (is_numeric($cellValue)) {
$dateValue = date('m', PHPExcel_Shared_Date::ExcelToPHP($cellValue));
if (in_array($dateValue, $monthSet)) {
return TRUE;
}
}
return FALSE;
}
/**
* @param $value
* @return array
*/
protected function _dateFormValue($value)
{
if ($value) {
$t = strtotime($value);
if (!$t) {
$t = \PHPExcel_Shared_Date::ExcelToPHP($value);
}
$value = date('d/m/Y', $t);
return $value;
}
return null;
}
/**
* Loads PHPExcel from file
*
* @param string $pFilename
* @throws Exception
*/
public function load($pFilename)
{
// Check if file exists
if (!file_exists($pFilename)) {
throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
}
// Initialisations
$excel = new PHPExcel();
$excel->removeSheetByIndex(0);
$zip = new ZipArchive();
$zip->open($pFilename);
$rels = simplexml_load_string($zip->getFromName("_rels/.rels"));
//~ http://schemas.openxmlformats.org/package/2006/relationships");
foreach ($rels->Relationship as $rel) {
switch ($rel["Type"]) {
case "http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties":
$xmlCore = simplexml_load_string($zip->getFromName("{$rel['Target']}"));
$xmlCore->registerXPathNamespace("dc", "http://purl.org/dc/elements/1.1/");
$xmlCore->registerXPathNamespace("dcterms", "http://purl.org/dc/terms/");
$xmlCore->registerXPathNamespace("cp", "http://schemas.openxmlformats.org/package/2006/metadata/core-properties");
$docProps = $excel->getProperties();
$docProps->setCreator((string) self::array_item($xmlCore->xpath("dc:creator")));
$docProps->setLastModifiedBy((string) self::array_item($xmlCore->xpath("cp:lastModifiedBy")));
$docProps->setCreated(strtotime(self::array_item($xmlCore->xpath("dcterms:created"))));
//! respect xsi:type
$docProps->setModified(strtotime(self::array_item($xmlCore->xpath("dcterms:modified"))));
//! respect xsi:type
$docProps->setTitle((string) self::array_item($xmlCore->xpath("dc:title")));
$docProps->setDescription((string) self::array_item($xmlCore->xpath("dc:description")));
$docProps->setSubject((string) self::array_item($xmlCore->xpath("dc:subject")));
$docProps->setKeywords((string) self::array_item($xmlCore->xpath("cp:keywords")));
$docProps->setCategory((string) self::array_item($xmlCore->xpath("cp:category")));
break;
case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument":
$dir = dirname($rel["Target"]);
$relsWorkbook = simplexml_load_string($zip->getFromName("{$dir}/_rels/" . basename($rel["Target"]) . ".rels"));
//~ http://schemas.openxmlformats.org/package/2006/relationships");
$relsWorkbook->registerXPathNamespace("rel", "http://schemas.openxmlformats.org/package/2006/relationships");
$sharedStrings = array();
$xpath = self::array_item($relsWorkbook->xpath("rel:Relationship[@Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings']"));
$xmlStrings = simplexml_load_string($zip->getFromName("{$dir}/{$xpath['Target']}"));
//~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
if (isset($xmlStrings) && isset($xmlStrings->si)) {
foreach ($xmlStrings->si as $val) {
if (isset($val->t)) {
$sharedStrings[] = PHPExcel_Shared_String::ControlCharacterOOXML2PHP((string) $val->t);
} elseif (isset($val->r)) {
$sharedStrings[] = $this->_parseRichText($val);
}
}
}
$worksheets = array();
foreach ($relsWorkbook->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet") {
$worksheets[(string) $ele["Id"]] = $ele["Target"];
}
}
$styles = array();
$cellStyles = array();
$xpath = self::array_item($relsWorkbook->xpath("rel:Relationship[@Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles']"));
$xmlStyles = simplexml_load_string($zip->getFromName("{$dir}/{$xpath['Target']}"));
//~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
$numFmts = $xmlStyles->numFmts[0];
if ($numFmts) {
$numFmts->registerXPathNamespace("sml", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
}
if (!$this->_readDataOnly) {
foreach ($xmlStyles->cellXfs->xf as $xf) {
$numFmt = PHPExcel_Style_NumberFormat::FORMAT_GENERAL;
if ($numFmts && $xf["numFmtId"]) {
$tmpNumFmt = self::array_item($numFmts->xpath("sml:numFmt[@numFmtId={$xf['numFmtId']}]"));
if (isset($tmpNumFmt["formatCode"])) {
$numFmt = (string) $tmpNumFmt["formatCode"];
} else {
if ((int) $xf["numFmtId"] < 165) {
$numFmt = PHPExcel_Style_NumberFormat::builtInFormatCode((int) $xf["numFmtId"]);
}
}
}
//$numFmt = str_replace('mm', 'i', $numFmt);
//$numFmt = str_replace('h', 'H', $numFmt);
$styles[] = (object) array("numFmt" => $numFmt, "font" => $xmlStyles->fonts->font[intval($xf["fontId"])], "fill" => $xmlStyles->fills->fill[intval($xf["fillId"])], "border" => $xmlStyles->borders->border[intval($xf["borderId"])], "alignment" => $xf->alignment, "protection" => $xf->protection, "applyAlignment" => isset($xf["applyAlignment"]) && ((string) $xf["applyAlignment"] == 'true' || (string) $xf["applyAlignment"] == '1'), "applyBorder" => isset($xf["applyBorder"]) && ((string) $xf["applyBorder"] == 'true' || (string) $xf["applyBorder"] == '1'), "applyFill" => isset($xf["applyFill"]) && ((string) $xf["applyFill"] == 'true' || (string) $xf["applyFill"] == '1'), "applyFont" => isset($xf["applyFont"]) && ((string) $xf["applyFont"] == 'true' || (string) $xf["applyFont"] == '1'), "applyNumberFormat" => isset($xf["applyNumberFormat"]) && ((string) $xf["applyNumberFormat"] == 'true' || (string) $xf["applyNumberFormat"] == '1'), "applyProtection" => isset($xf["applyProtection"]) && ((string) $xf["applyProtection"] == 'true' || (string) $xf["applyProtection"] == '1'));
}
foreach ($xmlStyles->cellStyleXfs->xf as $xf) {
$numFmt = PHPExcel_Style_NumberFormat::FORMAT_GENERAL;
if ($numFmts && $xf["numFmtId"]) {
$tmpNumFmt = self::array_item($numFmts->xpath("sml:numFmt[@numFmtId={$xf['numFmtId']}]"));
if (isset($tmpNumFmt["formatCode"])) {
$numFmt = (string) $tmpNumFmt["formatCode"];
} else {
if ((int) $xf["numFmtId"] < 165) {
$numFmt = PHPExcel_Style_NumberFormat::builtInFormatCode((int) $xf["numFmtId"]);
}
}
}
$cellStyles[] = (object) array("numFmt" => $numFmt, "font" => $xmlStyles->fonts->font[intval($xf["fontId"])], "fill" => $xmlStyles->fills->fill[intval($xf["fillId"])], "border" => $xmlStyles->borders->border[intval($xf["borderId"])], "alignment" => $xf->alignment, "protection" => $xf->protection, "applyAlignment" => true, "applyBorder" => true, "applyFill" => true, "applyFont" => true, "applyNumberFormat" => true, "applyProtection" => true);
}
}
$dxfs = array();
if (!$this->_readDataOnly) {
foreach ($xmlStyles->dxfs->dxf as $dxf) {
$style = new PHPExcel_Style();
$this->_readStyle($style, $dxf);
$dxfs[] = $style;
}
foreach ($xmlStyles->cellStyles->cellStyle as $cellStyle) {
if (intval($cellStyle['builtinId']) == 0) {
if (isset($cellStyles[intval($cellStyle['xfId'])])) {
// Set default style
$style = new PHPExcel_Style();
$this->_readStyle($style, $cellStyles[intval($cellStyle['xfId'])]);
PHPExcel_Style::setDefaultStyle($style);
}
}
}
}
$xmlWorkbook = simplexml_load_string($zip->getFromName("{$rel['Target']}"));
//~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
// Set base date
if ($xmlWorkbook->workbookPr) {
if (isset($xmlWorkbook->workbookPr['date1904'])) {
$date1904 = (string) $xmlWorkbook->workbookPr['date1904'];
if ($date1904 == "true" || $date1904 == "1") {
PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_MAC_1904);
}
}
}
$sheetId = 0;
foreach ($xmlWorkbook->sheets->sheet as $eleSheet) {
// Check if sheet should be skipped
if (isset($this->_loadSheetsOnly) && !in_array((string) $eleSheet["name"], $this->_loadSheetsOnly)) {
continue;
}
// Load sheet
$docSheet = $excel->createSheet();
$docSheet->setTitle((string) $eleSheet["name"]);
$fileWorksheet = $worksheets[(string) self::array_item($eleSheet->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "id")];
$xmlSheet = simplexml_load_string($zip->getFromName("{$dir}/{$fileWorksheet}"));
//~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
$sharedFormulas = array();
if (isset($xmlSheet->sheetViews) && isset($xmlSheet->sheetViews->sheetView)) {
if (isset($xmlSheet->sheetViews->sheetView['zoomScale'])) {
$docSheet->getSheetView()->setZoomScale(intval($xmlSheet->sheetViews->sheetView['zoomScale']));
}
if (isset($xmlSheet->sheetViews->sheetView['zoomScaleNormal'])) {
$docSheet->getSheetView()->setZoomScaleNormal(intval($xmlSheet->sheetViews->sheetView['zoomScaleNormal']));
}
if (isset($xmlSheet->sheetViews->sheetView['showGridLines'])) {
$docSheet->setShowGridLines($xmlSheet->sheetViews->sheetView['showGridLines'] ? true : false);
}
if (isset($xmlSheet->sheetViews->sheetView->pane)) {
if (isset($xmlSheet->sheetViews->sheetView->pane['topLeftCell'])) {
$docSheet->freezePane((string) $xmlSheet->sheetViews->sheetView->pane['topLeftCell']);
} else {
$xSplit = 0;
$ySplit = 0;
if (isset($xmlSheet->sheetViews->sheetView->pane['xSplit'])) {
$xSplit = 1 + intval($xmlSheet->sheetViews->sheetView->pane['xSplit']);
}
if (isset($xmlSheet->sheetViews->sheetView->pane['ySplit'])) {
$ySplit = 1 + intval($xmlSheet->sheetViews->sheetView->pane['ySplit']);
}
$docSheet->freezePaneByColumnAndRow($xSplit, $ySplit);
}
}
}
if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->outlinePr)) {
if (isset($xmlSheet->sheetPr->outlinePr['summaryRight']) && $xmlSheet->sheetPr->outlinePr['summaryRight'] == false) {
$docSheet->setShowSummaryRight(false);
} else {
$docSheet->setShowSummaryRight(true);
}
if (isset($xmlSheet->sheetPr->outlinePr['summaryBelow']) && $xmlSheet->sheetPr->outlinePr['summaryBelow'] == false) {
$docSheet->setShowSummaryBelow(false);
} else {
$docSheet->setShowSummaryBelow(true);
}
}
if (isset($xmlSheet->sheetFormatPr)) {
if (isset($xmlSheet->sheetFormatPr['customHeight']) && ((string) $xmlSheet->sheetFormatPr['customHeight'] == '1' || strtolower((string) $xmlSheet->sheetFormatPr['customHeight']) == 'true') && isset($xmlSheet->sheetFormatPr['defaultRowHeight'])) {
$docSheet->getDefaultRowDimension()->setRowHeight((double) $xmlSheet->sheetFormatPr['defaultRowHeight']);
}
if (isset($xmlSheet->sheetFormatPr['defaultColWidth'])) {
$docSheet->getDefaultColumnDimension()->setWidth((double) $xmlSheet->sheetFormatPr['defaultColWidth']);
}
}
if (isset($xmlSheet->cols) && !$this->_readDataOnly) {
foreach ($xmlSheet->cols->col as $col) {
for ($i = intval($col["min"]) - 1; $i < intval($col["max"]); ++$i) {
if ($col["bestFit"]) {
$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setAutoSize(true);
}
if ($col["hidden"]) {
$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setVisible(false);
}
if ($col["collapsed"]) {
$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setCollapsed(true);
}
if ($col["outlineLevel"] > 0) {
$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setOutlineLevel(intval($col["outlineLevel"]));
}
$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setWidth(floatval($col["width"]));
if (intval($col["max"]) == 16384) {
break;
}
}
}
}
if (isset($xmlSheet->printOptions) && !$this->_readDataOnly) {
if ($xmlSheet->printOptions['gridLinesSet'] == 'true' && $xmlSheet->printOptions['gridLinesSet'] == '1') {
$docSheet->setShowGridlines(true);
}
if ($xmlSheet->printOptions['gridLines'] == 'true' || $xmlSheet->printOptions['gridLines'] == '1') {
$docSheet->setPrintGridlines(true);
}
if ($xmlSheet->printOptions['horizontalCentered']) {
$docSheet->getPageSetup()->setHorizontalCentered(true);
}
if ($xmlSheet->printOptions['verticalCentered']) {
$docSheet->getPageSetup()->setVerticalCentered(true);
}
}
foreach ($xmlSheet->sheetData->row as $row) {
if ($row["ht"] && !$this->_readDataOnly) {
$docSheet->getRowDimension(intval($row["r"]))->setRowHeight(floatval($row["ht"]));
}
if ($row["hidden"] && !$this->_readDataOnly) {
$docSheet->getRowDimension(intval($row["r"]))->setVisible(false);
}
if ($row["collapsed"]) {
$docSheet->getRowDimension(intval($row["r"]))->setCollapsed(true);
}
if ($row["outlineLevel"] > 0) {
$docSheet->getRowDimension(intval($row["r"]))->setOutlineLevel(intval($row["outlineLevel"]));
}
foreach ($row->c as $c) {
$r = (string) $c["r"];
$cellDataType = (string) $c["t"];
// Read cell?
if (!is_null($this->getReadFilter())) {
$coordinates = PHPExcel_Cell::coordinateFromString($r);
if (!$this->getReadFilter()->readCell($coordinates[0], $coordinates[1], $docSheet->getTitle())) {
break;
}
}
// Read cell!
switch ($cellDataType) {
case "s":
if ((string) $c->v != '') {
$value = $sharedStrings[intval($c->v)];
if ($value instanceof PHPExcel_RichText) {
$value = clone $value;
}
} else {
$value = '';
}
break;
case "b":
$value = (string) $c->v;
if ($value == '0') {
$value = false;
} else {
if ($value == '1') {
$value = true;
} else {
$value = (bool) $c->v;
}
}
break;
case "inlineStr":
$value = $this->_parseRichText($c->is);
break;
case "e":
if (!isset($c->f)) {
$value = (string) $c->v;
} else {
$value = "={$c->f}";
}
break;
default:
if (!isset($c->f)) {
$value = (string) $c->v;
} else {
// Formula
$value = "={$c->f}";
$cellDataType = 'f';
// Shared formula?
if (isset($c->f['t']) && strtolower((string) $c->f['t']) == 'shared') {
$instance = (string) $c->f['si'];
if (!isset($sharedFormulas[(string) $c->f['si']])) {
$sharedFormulas[$instance] = array('master' => $r, 'formula' => $value);
} else {
$master = PHPExcel_Cell::coordinateFromString($sharedFormulas[$instance]['master']);
$current = PHPExcel_Cell::coordinateFromString($r);
$difference = array(0, 0);
$difference[0] = PHPExcel_Cell::columnIndexFromString($current[0]) - PHPExcel_Cell::columnIndexFromString($master[0]);
$difference[1] = $current[1] - $master[1];
$helper = PHPExcel_ReferenceHelper::getInstance();
$x = $helper->updateFormulaReferences($sharedFormulas[$instance]['formula'], 'A1', $difference[0], $difference[1]);
$value = $x;
}
}
}
break;
}
// Check for numeric values
if (is_numeric($value) && $cellDataType != 's') {
if ($value == (int) $value) {
$value = (int) $value;
} elseif ($value == (double) $value) {
$value = (double) $value;
} elseif ($value == (double) $value) {
$value = (double) $value;
}
}
// Rich text?
if ($value instanceof PHPExcel_RichText && $this->_readDataOnly) {
$value = $value->getPlainText();
}
// Assign value
if ($cellDataType != '') {
$docSheet->setCellValueExplicit($r, $value, $cellDataType);
} else {
$docSheet->setCellValue($r, $value);
}
// Style information?
if ($c["s"] && !$this->_readDataOnly) {
if (isset($styles[intval($c["s"])])) {
$this->_readStyle($docSheet->getStyle($r), $styles[intval($c["s"])]);
}
if ($cellDataType != 's' && PHPExcel_Shared_Date::isDateTimeFormat($docSheet->getStyle($r)->getNumberFormat())) {
if (preg_match("/^([0-9.,-]+)\$/", $value)) {
$docSheet->setCellValue($r, PHPExcel_Shared_Date::ExcelToPHP($value));
}
}
}
// Set rich text parent
if ($value instanceof PHPExcel_RichText && !$this->_readDataOnly) {
$value->setParent($docSheet->getCell($r));
}
}
}
$conditionals = array();
if (!$this->_readDataOnly) {
foreach ($xmlSheet->conditionalFormatting as $conditional) {
foreach ($conditional->cfRule as $cfRule) {
if (((string) $cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_NONE || (string) $cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_CELLIS || (string) $cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_CONTAINSTEXT) && isset($dxfs[intval($cfRule["dxfId"])])) {
$conditionals[(string) $conditional["sqref"]][intval($cfRule["priority"])] = $cfRule;
}
}
}
foreach ($conditionals as $ref => $cfRules) {
ksort($cfRules);
$conditionalStyles = array();
foreach ($cfRules as $cfRule) {
$objConditional = new PHPExcel_Style_Conditional();
$objConditional->setConditionType((string) $cfRule["type"]);
$objConditional->setOperatorType((string) $cfRule["operator"]);
if ((string) $cfRule["text"] != '') {
$objConditional->setText((string) $cfRule["text"]);
}
if (count($cfRule->formula) > 1) {
foreach ($cfRule->formula as $formula) {
$objConditional->addCondition((string) $formula);
}
} else {
$objConditional->addCondition((string) $cfRule->formula);
}
$objConditional->setStyle(clone $dxfs[intval($cfRule["dxfId"])]);
$conditionalStyles[] = $objConditional;
}
// Extract all cell references in $ref
$aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($ref);
foreach ($aReferences as $reference) {
$docSheet->getStyle($reference)->setConditionalStyles($conditionalStyles);
}
}
}
$aKeys = array("sheet", "objects", "scenarios", "formatCells", "formatColumns", "formatRows", "insertColumns", "insertRows", "insertHyperlinks", "deleteColumns", "deleteRows", "selectLockedCells", "sort", "autoFilter", "pivotTables", "selectUnlockedCells");
if (!$this->_readDataOnly) {
foreach ($aKeys as $key) {
$method = "set" . ucfirst($key);
$docSheet->getProtection()->{$method}($xmlSheet->sheetProtection[$key] == "true");
}
}
if (!$this->_readDataOnly) {
$docSheet->getProtection()->setPassword((string) $xmlSheet->sheetProtection["password"], true);
if ($xmlSheet->protectedRanges->protectedRange) {
foreach ($xmlSheet->protectedRanges->protectedRange as $protectedRange) {
$docSheet->protectCells((string) $protectedRange["sqref"], (string) $protectedRange["password"], true);
}
}
}
if ($xmlSheet->autoFilter && !$this->_readDataOnly) {
$docSheet->setAutoFilter((string) $xmlSheet->autoFilter["ref"]);
}
if ($xmlSheet->mergeCells->mergeCell && !$this->_readDataOnly) {
foreach ($xmlSheet->mergeCells->mergeCell as $mergeCell) {
$docSheet->mergeCells((string) $mergeCell["ref"]);
}
}
if (!$this->_readDataOnly) {
$docPageMargins = $docSheet->getPageMargins();
$docPageMargins->setLeft(floatval($xmlSheet->pageMargins["left"]));
$docPageMargins->setRight(floatval($xmlSheet->pageMargins["right"]));
$docPageMargins->setTop(floatval($xmlSheet->pageMargins["top"]));
$docPageMargins->setBottom(floatval($xmlSheet->pageMargins["bottom"]));
$docPageMargins->setHeader(floatval($xmlSheet->pageMargins["header"]));
$docPageMargins->setFooter(floatval($xmlSheet->pageMargins["footer"]));
}
if (!$this->_readDataOnly) {
$docPageSetup = $docSheet->getPageSetup();
if (isset($xmlSheet->pageSetup["orientation"])) {
$docPageSetup->setOrientation((string) $xmlSheet->pageSetup["orientation"]);
}
if (isset($xmlSheet->pageSetup["paperSize"])) {
$docPageSetup->setPaperSize(intval($xmlSheet->pageSetup["paperSize"]));
}
if (isset($xmlSheet->pageSetup["scale"])) {
$docPageSetup->setScale(intval($xmlSheet->pageSetup["scale"]));
}
if (isset($xmlSheet->pageSetup["fitToHeight"]) && intval($xmlSheet->pageSetup["fitToHeight"]) > 0) {
$docPageSetup->setFitToHeight(intval($xmlSheet->pageSetup["fitToHeight"]));
}
if (isset($xmlSheet->pageSetup["fitToWidth"]) && intval($xmlSheet->pageSetup["fitToWidth"]) > 0) {
$docPageSetup->setFitToWidth(intval($xmlSheet->pageSetup["fitToWidth"]));
}
}
if (!$this->_readDataOnly) {
$docHeaderFooter = $docSheet->getHeaderFooter();
$docHeaderFooter->setDifferentOddEven($xmlSheet->headerFooter["differentOddEven"] == 'true');
$docHeaderFooter->setDifferentFirst($xmlSheet->headerFooter["differentFirst"] == 'true');
$docHeaderFooter->setScaleWithDocument($xmlSheet->headerFooter["scaleWithDoc"] == 'true');
$docHeaderFooter->setAlignWithMargins($xmlSheet->headerFooter["alignWithMargins"] == 'true');
$docHeaderFooter->setOddHeader((string) $xmlSheet->headerFooter->oddHeader);
$docHeaderFooter->setOddFooter((string) $xmlSheet->headerFooter->oddFooter);
$docHeaderFooter->setEvenHeader((string) $xmlSheet->headerFooter->evenHeader);
$docHeaderFooter->setEvenFooter((string) $xmlSheet->headerFooter->evenFooter);
$docHeaderFooter->setFirstHeader((string) $xmlSheet->headerFooter->firstHeader);
$docHeaderFooter->setFirstFooter((string) $xmlSheet->headerFooter->firstFooter);
}
if ($xmlSheet->rowBreaks->brk && !$this->_readDataOnly) {
foreach ($xmlSheet->rowBreaks->brk as $brk) {
if ($brk["man"]) {
$docSheet->setBreak("A{$brk['id']}", PHPExcel_Worksheet::BREAK_ROW);
}
}
}
if ($xmlSheet->colBreaks->brk && !$this->_readDataOnly) {
foreach ($xmlSheet->colBreaks->brk as $brk) {
if ($brk["man"]) {
$docSheet->setBreak(PHPExcel_Cell::stringFromColumnIndex($brk["id"]) . "1", PHPExcel_Worksheet::BREAK_COLUMN);
}
}
}
if ($xmlSheet->dataValidations && !$this->_readDataOnly) {
foreach ($xmlSheet->dataValidations->dataValidation as $dataValidation) {
// Uppercase coordinate
$range = strtoupper($dataValidation["sqref"]);
// Extract all cell references in $range
$aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($range);
foreach ($aReferences as $reference) {
// Create validation
$docValidation = $docSheet->getCell($reference)->getDataValidation();
$docValidation->setType((string) $dataValidation["type"]);
$docValidation->setErrorStyle((string) $dataValidation["errorStyle"]);
$docValidation->setOperator((string) $dataValidation["operator"]);
$docValidation->setAllowBlank($dataValidation["allowBlank"] != 0);
$docValidation->setShowDropDown($dataValidation["showDropDown"] == 0);
$docValidation->setShowInputMessage($dataValidation["showInputMessage"] != 0);
$docValidation->setShowErrorMessage($dataValidation["showErrorMessage"] != 0);
$docValidation->setErrorTitle((string) $dataValidation["errorTitle"]);
$docValidation->setError((string) $dataValidation["error"]);
$docValidation->setPromptTitle((string) $dataValidation["promptTitle"]);
$docValidation->setPrompt((string) $dataValidation["prompt"]);
$docValidation->setFormula1((string) $dataValidation->formula1);
$docValidation->setFormula2((string) $dataValidation->formula2);
}
}
}
// Add hyperlinks
$hyperlinks = array();
if (!$this->_readDataOnly) {
// Locate hyperlink relations
if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
$relsWorksheet = simplexml_load_string($zip->getFromName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels"));
//~ http://schemas.openxmlformats.org/package/2006/relationships");
foreach ($relsWorksheet->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink") {
$hyperlinks[(string) $ele["Id"]] = (string) $ele["Target"];
}
}
}
// Loop trough hyperlinks
if ($xmlSheet->hyperlinks) {
foreach ($xmlSheet->hyperlinks->hyperlink as $hyperlink) {
// Link url
$linkRel = $hyperlink->attributes('http://schemas.openxmlformats.org/officeDocument/2006/relationships');
if (isset($linkRel['id'])) {
$docSheet->getCell($hyperlink['ref'])->getHyperlink()->setUrl($hyperlinks[(string) $linkRel['id']]);
}
if (isset($hyperlink['location'])) {
$docSheet->getCell($hyperlink['ref'])->getHyperlink()->setUrl('sheet://' . (string) $hyperlink['location']);
}
// Tooltip
if (isset($hyperlink['tooltip'])) {
$docSheet->getCell($hyperlink['ref'])->getHyperlink()->setTooltip((string) $hyperlink['tooltip']);
}
}
}
}
// Add comments
$comments = array();
$vmlComments = array();
if (!$this->_readDataOnly) {
// Locate comment relations
if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
$relsWorksheet = simplexml_load_string($zip->getFromName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels"));
//~ http://schemas.openxmlformats.org/package/2006/relationships");
foreach ($relsWorksheet->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/comments") {
$comments[(string) $ele["Id"]] = (string) $ele["Target"];
}
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing") {
$vmlComments[(string) $ele["Id"]] = (string) $ele["Target"];
}
}
}
// Loop trough comments
foreach ($comments as $relName => $relPath) {
// Load comments file
$relPath = PHPExcel_Shared_File::realpath(dirname("{$dir}/{$fileWorksheet}") . "/" . $relPath);
$commentsFile = simplexml_load_string($zip->getFromName($relPath));
// Utility variables
$authors = array();
// Loop trough authors
foreach ($commentsFile->authors->author as $author) {
$authors[] = (string) $author;
}
// Loop trough contents
foreach ($commentsFile->commentList->comment as $comment) {
$docSheet->getComment((string) $comment['ref'])->setAuthor($authors[(string) $comment['authorId']]);
$docSheet->getComment((string) $comment['ref'])->setText($this->_parseRichText($comment->text));
}
}
// Loop trough VML comments
foreach ($vmlComments as $relName => $relPath) {
// Load VML comments file
$relPath = PHPExcel_Shared_File::realpath(dirname("{$dir}/{$fileWorksheet}") . "/" . $relPath);
$vmlCommentsFile = simplexml_load_string($zip->getFromName($relPath));
$vmlCommentsFile->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
$shapes = $vmlCommentsFile->xpath('//v:shape');
foreach ($shapes as $shape) {
$shape->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
if (isset($shape['style'])) {
$style = (string) $shape['style'];
$fillColor = strtoupper(substr((string) $shape['fillcolor'], 1));
$column = null;
$row = null;
$clientData = $shape->xpath('.//x:ClientData');
if (is_array($clientData)) {
$clientData = $clientData[0];
if (isset($clientData['ObjectType']) && (string) $clientData['ObjectType'] == 'Note') {
$temp = $clientData->xpath('.//x:Row');
if (is_array($temp)) {
$row = $temp[0];
}
$temp = $clientData->xpath('.//x:Column');
if (is_array($temp)) {
$column = $temp[0];
}
}
}
if (!is_null($column) && !is_null($row)) {
// Set comment properties
$comment = $docSheet->getCommentByColumnAndRow($column, $row + 1);
$comment->getFillColor()->setRGB($fillColor);
// Parse style
$styleArray = explode(';', str_replace(' ', '', $style));
foreach ($styleArray as $stylePair) {
$stylePair = explode(':', $stylePair);
if ($stylePair[0] == 'margin-left') {
$comment->setMarginLeft($stylePair[1]);
}
if ($stylePair[0] == 'margin-top') {
$comment->setMarginTop($stylePair[1]);
}
if ($stylePair[0] == 'width') {
$comment->setWidth($stylePair[1]);
}
if ($stylePair[0] == 'height') {
$comment->setHeight($stylePair[1]);
}
if ($stylePair[0] == 'visibility') {
$comment->setVisible($stylePair[1] == 'visible');
}
}
}
}
}
}
// Header/footer images
if ($xmlSheet->legacyDrawingHF && !$this->_readDataOnly) {
if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
$relsWorksheet = simplexml_load_string($zip->getFromName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels"));
//~ http://schemas.openxmlformats.org/package/2006/relationships");
$vmlRelationship = '';
foreach ($relsWorksheet->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing") {
$vmlRelationship = self::dir_add("{$dir}/{$fileWorksheet}", $ele["Target"]);
}
}
if ($vmlRelationship != '') {
// Fetch linked images
$relsVML = simplexml_load_string($zip->getFromName(dirname($vmlRelationship) . '/_rels/' . basename($vmlRelationship) . '.rels'));
//~ http://schemas.openxmlformats.org/package/2006/relationships");
$drawings = array();
foreach ($relsVML->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image") {
$drawings[(string) $ele["Id"]] = self::dir_add($vmlRelationship, $ele["Target"]);
}
}
// Fetch VML document
$vmlDrawing = simplexml_load_string($zip->getFromName($vmlRelationship));
$vmlDrawing->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
$hfImages = array();
$shapes = $vmlDrawing->xpath('//v:shape');
foreach ($shapes as $shape) {
$shape->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
$imageData = $shape->xpath('//v:imagedata');
$imageData = $imageData[0];
$imageData = $imageData->attributes('urn:schemas-microsoft-com:office:office');
$style = self::toCSSArray((string) $shape['style']);
$hfImages[(string) $shape['id']] = new PHPExcel_Worksheet_HeaderFooterDrawing();
if (isset($imageData['title'])) {
$hfImages[(string) $shape['id']]->setName((string) $imageData['title']);
}
$hfImages[(string) $shape['id']]->setPath("zip://{$pFilename}#" . $drawings[(string) $imageData['relid']], false);
$hfImages[(string) $shape['id']]->setResizeProportional(false);
$hfImages[(string) $shape['id']]->setWidth($style['width']);
$hfImages[(string) $shape['id']]->setHeight($style['height']);
$hfImages[(string) $shape['id']]->setOffsetX($style['margin-left']);
$hfImages[(string) $shape['id']]->setOffsetY($style['margin-top']);
$hfImages[(string) $shape['id']]->setResizeProportional(true);
}
$docSheet->getHeaderFooter()->setImages($hfImages);
}
}
}
}
// TODO: Make sure drawings and graph are loaded differently!
if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
$relsWorksheet = simplexml_load_string($zip->getFromName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels"));
//~ http://schemas.openxmlformats.org/package/2006/relationships");
$drawings = array();
foreach ($relsWorksheet->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing") {
$drawings[(string) $ele["Id"]] = self::dir_add("{$dir}/{$fileWorksheet}", $ele["Target"]);
}
}
if ($xmlSheet->drawing && !$this->_readDataOnly) {
foreach ($xmlSheet->drawing as $drawing) {
$fileDrawing = $drawings[(string) self::array_item($drawing->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "id")];
$relsDrawing = simplexml_load_string($zip->getFromName(dirname($fileDrawing) . "/_rels/" . basename($fileDrawing) . ".rels"));
//~ http://schemas.openxmlformats.org/package/2006/relationships");
$images = array();
if ($relsDrawing && $relsDrawing->Relationship) {
foreach ($relsDrawing->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image") {
$images[(string) $ele["Id"]] = self::dir_add($fileDrawing, $ele["Target"]);
}
}
}
$xmlDrawing = simplexml_load_string($zip->getFromName($fileDrawing))->children("http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing");
if ($xmlDrawing->oneCellAnchor) {
foreach ($xmlDrawing->oneCellAnchor as $oneCellAnchor) {
if ($oneCellAnchor->pic->blipFill) {
$blip = $oneCellAnchor->pic->blipFill->children("http://schemas.openxmlformats.org/drawingml/2006/main")->blip;
$xfrm = $oneCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->xfrm;
$outerShdw = $oneCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->effectLst->outerShdw;
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName((string) self::array_item($oneCellAnchor->pic->nvPicPr->cNvPr->attributes(), "name"));
$objDrawing->setDescription((string) self::array_item($oneCellAnchor->pic->nvPicPr->cNvPr->attributes(), "descr"));
$objDrawing->setPath("zip://{$pFilename}#" . $images[(string) self::array_item($blip->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "embed")], false);
$objDrawing->setCoordinates(PHPExcel_Cell::stringFromColumnIndex($oneCellAnchor->from->col) . ($oneCellAnchor->from->row + 1));
$objDrawing->setOffsetX(PHPExcel_Shared_Drawing::EMUToPixels($oneCellAnchor->from->colOff));
$objDrawing->setOffsetY(PHPExcel_Shared_Drawing::EMUToPixels($oneCellAnchor->from->rowOff));
$objDrawing->setResizeProportional(false);
$objDrawing->setWidth(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($oneCellAnchor->ext->attributes(), "cx")));
$objDrawing->setHeight(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($oneCellAnchor->ext->attributes(), "cy")));
if ($xfrm) {
$objDrawing->setRotation(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($xfrm->attributes(), "rot")));
}
if ($outerShdw) {
$shadow = $objDrawing->getShadow();
$shadow->setVisible(true);
$shadow->setBlurRadius(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "blurRad")));
$shadow->setDistance(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "dist")));
$shadow->setDirection(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($outerShdw->attributes(), "dir")));
$shadow->setAlignment((string) self::array_item($outerShdw->attributes(), "algn"));
$shadow->getColor()->setRGB(self::array_item($outerShdw->srgbClr->attributes(), "val"));
$shadow->setAlpha(self::array_item($outerShdw->srgbClr->alpha->attributes(), "val") / 1000);
}
$objDrawing->setWorksheet($docSheet);
}
}
}
if ($xmlDrawing->twoCellAnchor) {
foreach ($xmlDrawing->twoCellAnchor as $twoCellAnchor) {
if ($twoCellAnchor->pic->blipFill) {
$blip = $twoCellAnchor->pic->blipFill->children("http://schemas.openxmlformats.org/drawingml/2006/main")->blip;
$xfrm = $twoCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->xfrm;
$outerShdw = $twoCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->effectLst->outerShdw;
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName((string) self::array_item($twoCellAnchor->pic->nvPicPr->cNvPr->attributes(), "name"));
$objDrawing->setDescription((string) self::array_item($twoCellAnchor->pic->nvPicPr->cNvPr->attributes(), "descr"));
$objDrawing->setPath("zip://{$pFilename}#" . $images[(string) self::array_item($blip->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "embed")], false);
$objDrawing->setCoordinates(PHPExcel_Cell::stringFromColumnIndex($twoCellAnchor->from->col) . ($twoCellAnchor->from->row + 1));
$objDrawing->setOffsetX(PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->from->colOff));
$objDrawing->setOffsetY(PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->from->rowOff));
$objDrawing->setResizeProportional(false);
$objDrawing->setWidth(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($xfrm->ext->attributes(), "cx")));
$objDrawing->setHeight(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($xfrm->ext->attributes(), "cy")));
if ($xfrm) {
$objDrawing->setRotation(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($xfrm->attributes(), "rot")));
}
if ($outerShdw) {
$shadow = $objDrawing->getShadow();
$shadow->setVisible(true);
$shadow->setBlurRadius(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "blurRad")));
$shadow->setDistance(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "dist")));
$shadow->setDirection(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($outerShdw->attributes(), "dir")));
$shadow->setAlignment((string) self::array_item($outerShdw->attributes(), "algn"));
$shadow->getColor()->setRGB(self::array_item($outerShdw->srgbClr->attributes(), "val"));
$shadow->setAlpha(self::array_item($outerShdw->srgbClr->alpha->attributes(), "val") / 1000);
}
$objDrawing->setWorksheet($docSheet);
}
}
}
}
}
}
// Loop trough definedNames
if ($xmlWorkbook->definedNames) {
foreach ($xmlWorkbook->definedNames->definedName as $definedName) {
// Extract range
$extractedRange = (string) $definedName;
if (strpos($extractedRange, '!') !== false) {
$extractedRange = substr($extractedRange, strpos($extractedRange, '!') + 1);
}
$extractedRange = str_replace('$', '', $extractedRange);
// Valid range?
if (stripos((string) $definedName, '#REF!') !== false || $extractedRange == '') {
continue;
}
// Some definedNames are only applicable if we are on the same sheet...
if ($definedName['localSheetId'] == $sheetId) {
// Switch on type
switch ((string) $definedName['name']) {
case '_xlnm._FilterDatabase':
$docSheet->setAutoFilter($extractedRange);
break;
case '_xlnm.Print_Titles':
// Split $extractedRange
$extractedRange = explode(',', $extractedRange);
// Set print titles
if (isset($extractedRange[0])) {
$range = explode(':', $extractedRange[0]);
if (PHPExcel_Worksheet::extractSheetTitle($range[0]) != '') {
$range[0] = PHPExcel_Worksheet::extractSheetTitle($range[0]);
}
$range[0] = str_replace('$', '', $range[0]);
if (PHPExcel_Worksheet::extractSheetTitle($range[1]) != '') {
$range[1] = PHPExcel_Worksheet::extractSheetTitle($range[1]);
}
$range[1] = str_replace('$', '', $range[1]);
$docSheet->getPageSetup()->setColumnsToRepeatAtLeft($range);
}
if (isset($extractedRange[1])) {
$range = explode(':', $extractedRange[1]);
if (PHPExcel_Worksheet::extractSheetTitle($range[0]) != '') {
$range[0] = PHPExcel_Worksheet::extractSheetTitle($range[0]);
}
$range[0] = str_replace('$', '', $range[0]);
if (PHPExcel_Worksheet::extractSheetTitle($range[1]) != '') {
$range[1] = PHPExcel_Worksheet::extractSheetTitle($range[1]);
}
$range[1] = str_replace('$', '', $range[1]);
$docSheet->getPageSetup()->setRowsToRepeatAtTop($range);
}
break;
case '_xlnm.Print_Area':
$docSheet->getPageSetup()->setPrintArea($extractedRange);
break;
default:
$excel->addNamedRange(new PHPExcel_NamedRange((string) $definedName['name'], $docSheet, $extractedRange, true));
break;
}
} else {
// "Global" definedNames
$locatedSheet = null;
$extractedSheetName = '';
if (strpos((string) $definedName, '!') !== false) {
// Extract sheet name
$extractedSheetName = PHPExcel_Worksheet::extractSheetTitle((string) $definedName);
// Locate sheet
$locatedSheet = $excel->getSheetByName($extractedSheetName);
}
if (!is_null($locatedSheet)) {
$excel->addNamedRange(new PHPExcel_NamedRange((string) $definedName['name'], $locatedSheet, $extractedRange, false));
}
}
}
}
// Garbage collect...
$docSheet->garbageCollect();
// Next sheet id
++$sheetId;
}
if (!$this->_readDataOnly) {
$excel->setActiveSheetIndex(intval($xmlWorkbook->bookView->workbookView["activeTab"]));
}
break;
}
}
return $excel;
}
public function supportexcelimport()
{
$file_name = substr($this->input->get('url'), strpos($this->input->get('url'), "?file=") + 6);
if ($this->session->userdata('marker') != 1) {
redirect($this->index());
} else {
//Check If User Has Authority(program_magement) To Import Support
if ($this->user_model->get_user_role('program_management', $this->session->userdata('userroleid'))) {
$file = "C:\\xampp\\htdocs\\attribution\\server\\php\\files\\suportimport.xlsx";
$no_empty_rows = TRUE;
$this->mechanisms_model->empty_mechanisms_support_errors();
$this->load->library('excel');
//read file from path
$objPHPExcel = PHPExcel_IOFactory::load($file);
$sheetname = 'MER category option combos';
//get only the Cell Collection
//$active=$objPHPExcel->setActiveSheetIndexByName($sheetname);
$cell_collection = $objPHPExcel->getActiveSheet()->getCellCollection();
$highestColumm = $objPHPExcel->setActiveSheetIndex(0)->getHighestColumn();
$highestRow = $objPHPExcel->setActiveSheetIndex(0)->getHighestRow();
//echo 'getHighestColumn() = [' . $highestColumm . ']<br/>';
//echo 'getHighestRow() = [' . $highestRow . ']<br/>';
//$cell_collection= array_map('array_filter', $objPHPExcel);
$rows = $highestRow;
//echo $rows."active </br>";
$empty_cells_alert = "";
$count = 1;
$empty_column = 1;
$data_rows = 1;
$mechanisms_name = "";
$mechanisms_id = "";
$mechanisms_uid = "";
$attribution_key = "";
//extract to a PHP readable array format
//print_r($cell_collection);
foreach ($cell_collection as $cell) {
//Only Get Rows With All Columns Filled
if ($objPHPExcel->getActiveSheet()->getCell("A" . $count)->getValue() != null && $objPHPExcel->getActiveSheet()->getCell("B" . $count)->getValue() != null && $objPHPExcel->getActiveSheet()->getCell("C" . $count)->getValue() != null && $objPHPExcel->getActiveSheet()->getCell("D" . $count)->getValue() != null && $objPHPExcel->getActiveSheet()->getCell("E" . $count)->getValue() != null && $objPHPExcel->getActiveSheet()->getCell("F" . $count)->getValue() != null && $objPHPExcel->getActiveSheet()->getCell("G" . $count)->getValue() != null) {
if ($cell == "A" . $count) {
//Get Mechanism Name
$column = 'A';
$row = $objPHPExcel->getActiveSheet()->getCell($cell)->getRow();
$data_value = $objPHPExcel->getActiveSheet()->getCell($cell)->getValue();
if ($row != 1 && $data_value != '') {
$organization_name = $data_value;
}
} elseif ($cell == "B" . $count) {
$column = 'B';
$row = $objPHPExcel->getActiveSheet()->getCell($cell)->getRow();
$data_value = $objPHPExcel->getActiveSheet()->getCell($cell)->getValue();
if ($row != 1 && $data_value != '') {
$mechanism_name = $data_value;
}
} elseif ($cell == "C" . $count) {
$column = 'C';
$row = $objPHPExcel->getActiveSheet()->getCell($cell)->getRow();
$data_value = $objPHPExcel->getActiveSheet()->getCell($cell)->getValue();
if ($row != 1 && $data_value != '') {
$datim_id = $data_value;
}
} elseif ($cell == "D" . $count) {
$column = 'D';
$row = $objPHPExcel->getActiveSheet()->getCell($cell)->getRow();
$data_value = $objPHPExcel->getActiveSheet()->getCell($cell)->getValue();
if ($row != 1 && $data_value != '') {
$program_name = $data_value;
}
} elseif ($cell == "E" . $count) {
$column = 'E';
$row = $objPHPExcel->getActiveSheet()->getCell($cell)->getRow();
$data_value = $objPHPExcel->getActiveSheet()->getCell($cell)->getValue();
if ($row != 1 && $data_value != '') {
$support_type = $data_value;
}
} elseif ($cell == "F" . $count) {
$column = 'F';
$row = $objPHPExcel->getActiveSheet()->getCell($cell)->getRow();
$data_value = $objPHPExcel->getActiveSheet()->getCell($cell)->getValue();
if ($row != 1 && $data_value != '') {
$cell = $objPHPExcel->getActiveSheet()->getCell('F' . $row);
$InvDate = $cell->getValue();
if (PHPExcel_Shared_Date::isDateTime($cell)) {
$InvDate = date("Y-m-d", PHPExcel_Shared_Date::ExcelToPHP($InvDate));
}
$start_date = $InvDate;
}
} elseif ($cell == "G" . $count) {
$count = $count + 1;
$column = 'G';
$row = $objPHPExcel->getActiveSheet()->getCell($cell)->getRow();
$data_value = $objPHPExcel->getActiveSheet()->getCell($cell)->getValue();
if ($row != 1 && $data_value != '') {
$cell = $objPHPExcel->getActiveSheet()->getCell('G' . $row);
$InvDate = $cell->getValue();
if (PHPExcel_Shared_Date::isDateTime($cell)) {
$InvDate = date("Y-m-d", PHPExcel_Shared_Date::ExcelToPHP($InvDate));
}
$end_date = $InvDate;
$data_rows = $data_rows + 1;
$update = $this->mechanisms_model->support_excel_import($organization_name, $mechanism_name, $datim_id, $program_name, $support_type, $start_date, $end_date);
if ($update != 1) {
$this->mechanisms_model->support_import_errors($organization_name, $mechanism_name, $datim_id, $program_name, $support_type, $start_date, $end_date, $update);
}
}
//Get Rows With Partial Column Data
} elseif ($objPHPExcel->getActiveSheet()->getCell("A" . $count)->getValue() != null || $objPHPExcel->getActiveSheet()->getCell("B" . $count)->getValue() != null || $objPHPExcel->getActiveSheet()->getCell("C" . $count)->getValue() != null || $objPHPExcel->getActiveSheet()->getCell("D" . $count)->getValue() != null || $objPHPExcel->getActiveSheet()->getCell("E" . $count)->getValue() != null || $objPHPExcel->getActiveSheet()->getCell("F" . $count)->getValue() != null || $objPHPExcel->getActiveSheet()->getCell("G" . $count)->getValue() != null) {
$empty_cells_alert[$empty_column] = "Empty Cell In Row {$count}";
$empty_column = $empty_column + 1;
$count = $count + 1;
$no_empty_rows = FALSE;
}
}
}
$data = array('message' => "Support Information Has Been Successfully Uploaded Into The Database");
echo json_encode($data);
} else {
$data['message'] = "Kindly Contact The Administrator You Have No Access Rights To This Module";
$this->load->view('error', $data);
}
}
}
$recommendation_URL = $row_array['L'];
} else {
$recommendation_URL = "";
}
if (isset($row_array['P'])) {
$date_of_evidence = date('d-M-Y', PHPExcel_Shared_Date::ExcelToPHP($row_array['P']));
} else {
$date_of_evidence = "";
}
if (isset($row_array['Q'])) {
$date_of_addition = date('d-M-Y', PHPExcel_Shared_Date::ExcelToPHP($row_array['Q']));
} else {
$date_of_addition = "";
}
if (isset($row_array['R'])) {
$date_last_validation = date('d-M-Y', PHPExcel_Shared_Date::ExcelToPHP($row_array['R']));
} else {
$date_last_validation = "";
}
if (isset($row_array['S'])) {
$author_last_validation = $row_array['S'];
} else {
$author_last_validation = "";
}
if (isset($row_array['T'])) {
$author_addition = $row_array['T'];
} else {
$author_addition = "";
}
// Skip processing if not all required data items are present
if ($human_class_label == "" or $recommendation_in_english == "") {
function getSheetArray(&$reader, $index, $heading)
{
// Get sheet array
$index = (int) $index;
if ($index < 0) {
$index = 0;
}
if ($index >= $reader->getSheetCount()) {
$index = $reader->getSheetCount() - 1;
}
$sheet = $reader->getSheet($index);
$rows = $sheet->getHighestRow();
$cols = PHPExcel_Cell::columnIndexFromString($sheet->getHighestColumn());
$arr = array();
if (count($heading) == 0) {
return $arr;
}
for ($i = 0; $i < $rows; $i++) {
if ($i == 0) {
continue;
}
for ($j = 0; $j < $cols; $j++) {
$arr[$i - 1][$heading[$j]] = $this->getCell($sheet, $i, $j + 1);
if (($heading[$j] == 'date_start' || $heading[$j] == 'date_end' || $heading[$j] == 'date_available') && $arr[$i - 1][$heading[$j]] != '0000-00-00') {
$arr[$i - 1][$heading[$j]] = date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($arr[$i - 1][$heading[$j]]));
}
}
}
return $arr;
}
