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;
}
/**
* 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;
}
function getImportExcelData($data, $fields)
{
global $total_records, $cCharset, $columnIndex;
foreach ($data->getWorksheetIterator() as $worksheet) {
$highestRow = $worksheet->getHighestRow();
for ($row = 2; $row <= $highestRow; ++$row) {
for ($col = 0; $col < $columnIndex; ++$col) {
$cell = $worksheet->getCellByColumnAndRow($col, $row);
if (PHPExcel_Shared_Date::isDateTime($cell)) {
$date_format = $cell->getParent()->getParent()->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->getFormatCode();
$value = PHPExcel_Style_NumberFormat::ToFormattedString($cell->getValue(), $date_format);
if (is_a($value, 'PHPExcel_RichText')) {
$value = $value->getPlainText();
}
if ($value) {
$time = array();
if (strtotime($value)) {
$value = strtotime($value);
} else {
$d_format = "";
for ($i = 0; $i < strlen($date_format); $i++) {
$letter = substr(strtolower($date_format), $i, 1);
if ($letter == "d" || $letter == "m" || $letter == "y") {
if (strpos($d_format, $letter) === false) {
$d_format .= $letter;
}
}
}
$value = strtotime(localdatetime2db($value, $d_format));
}
// $value = PHPExcel_Shared_Date::ExcelToPHP($value);
$time = localtime($value, true);
$val = $time["tm_year"] + 1900 . "-" . ($time["tm_mon"] + 1) . "-" . $time["tm_mday"] . " " . $time["tm_hour"] . ":" . $time["tm_min"] . ":" . $time["tm_sec"];
} else {
$val = NULL;
}
} else {
$error_handler = set_error_handler("empty_error_handler");
$val = PHPExcel_Shared_String::ConvertEncoding($cell->getValue(), $cCharset, 'UTF-8');
if (is_a($val, 'PHPExcel_RichText')) {
$val = $val->getPlainText();
}
if ($error_handler) {
set_error_handler($error_handler);
}
}
$arr[$fields[$col]] = $val;
}
$ret = InsertRecord($arr, $row - 2);
$total_records++;
}
break;
}
}
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;
}
protected function getExcelCellValue($cell, $type)
{
switch ($type) {
case 'formatted':
return $cell->getFormattedValue();
case 'calculated':
$value = '' . $cell->getCalculatedValue();
break;
default:
// raw
$value = "{$cell}";
}
return $value !== '' && \PHPExcel_Shared_Date::isDateTime($cell) ? '' . \PHPExcel_Shared_Date::ExcelToPHP($value) : $value;
}
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);
}
}
}
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;
}
/**
*
*
* @param string $ps_source
* @param array $pa_options
* @return bool
*/
public function nextRow()
{
if (!$this->opo_rows) {
return false;
}
while (true) {
if ($this->opn_current_row > 0) {
$this->opo_rows->next();
}
$this->opn_current_row++;
if (!$this->opo_rows->valid()) {
return false;
}
if ($o_row = $this->opo_rows->current()) {
$this->opa_row_buf = array(null);
$o_cells = $o_row->getCellIterator();
$o_cells->setIterateOnlyExistingCells(false);
$va_row = array();
$vb_val_was_set = false;
$vn_col = 0;
$vn_last_col_set = null;
foreach ($o_cells as $o_cell) {
if (PHPExcel_Shared_Date::isDateTime($o_cell)) {
if (!($vs_val = caGetLocalizedDate(PHPExcel_Shared_Date::ExcelToPHP(trim((string) $o_cell->getValue()))))) {
if (!($vs_val = trim(PHPExcel_Style_NumberFormat::toFormattedString((string) $o_cell->getValue(), 'YYYY-MM-DD')))) {
$vs_val = trim((string) $o_cell->getValue());
}
}
$this->opa_row_buf[] = $vs_val;
} else {
$this->opa_row_buf[] = $vs_val = trim((string) $o_cell->getValue());
}
if (strlen($vs_val) > 0) {
$vb_val_was_set = true;
$vn_last_col_set = $vn_col;
}
$vn_col++;
if ($vn_col > 255) {
break;
}
// max 255 columns; some Excel files have *thousands* of "phantom" columns
}
//if (!$vb_val_was_set) {
//return $this->nextRow();
// continue;
//} // skip completely blank rows
return $o_row;
}
}
return false;
}
/**
* @param Schema $schema
*/
public function up(Schema $schema)
{
/**
* @var EntityManager $em
*/
$em = $this->container->get('doctrine')->getManager();
$decType = $em->getRepository('CoreBundle:DocumentType')->findOneBy(['code' => 'DeclOfConf']);
if (!$decType) {
$decType = new DocumentType();
$decType->setName('Declaration of Conformity')->setCode('DeclOfConf');
$em->persist($decType);
}
$certType = $em->getRepository('CoreBundle:DocumentType')->findOneBy(['code' => 'CertOfConf']);
if (!$certType) {
$certType = new DocumentType();
$certType->setName('Certificate of Conformity')->setCode('CertOfConf');
$em->persist($certType);
}
$customer = new Customer();
$customer->setName('LPP S.A.')->setAbbr('lpp')->setIsActive(false)->setAddress('');
$em->persist($customer);
$excel = \PHPExcel_IOFactory::load(__DIR__ . '/files/documents.xlsx');
$sheets = $excel->getAllSheets();
foreach ($sheets as $sheet) {
$title = $sheet->getTitle();
$documentType = preg_match('/ДС/ui', $title) ? $decType : $certType;
preg_match('/\\((?P<tag>.+?)\\)/ui', $title, $matches);
$tag = null;
//add tag
if (isset($matches['tag']) && $matches['tag']) {
$tag = $em->getRepository('CoreBundle:Tag')->findOneBy(['name' => $matches['tag']]);
if (!$tag) {
$tag = new Tag();
$tag->setName($matches['tag']);
$em->persist($tag);
$em->flush();
}
}
$i = 0;
$field = null;
$mapping = [];
while (($field = $sheet->getCellByColumnAndRow($i, 1)->getValue()) == true) {
$mapping[$field] = $i;
$i++;
}
$lastColumn = $i - 1;
$readSheet = true;
$r = 3;
while ($readSheet) {
$document = new Document();
$document->setCustomer($customer)->setType($documentType);
if (isset($tag)) {
$document->addTag($tag);
}
$isEmpty = false;
foreach ($mapping as $field => $col) {
$value = trim($sheet->getCellByColumnAndRow($col, $r)->getValue());
if (!$value && $field == 'name') {
$isEmpty = true;
break;
}
if (\PHPExcel_Shared_Date::isDateTime($sheet->getCellByColumnAndRow($col, $r))) {
$value = \PHPExcel_Shared_Date::ExcelToPHPObject($value);
}
$method = 'set' . ucfirst($field);
if ($value) {
$document->{$method}($value);
}
}
if ($isEmpty) {
$readSheet = false;
} else {
$em->persist($document);
}
$r++;
}
}
$em->flush();
}
/**
* Loads an Excel document and processes all non-empty worksheets or only the selected ones.
* This reads data and cell formatting info as well, can open Excel 2007, 2003 and Excel 5 files.
* The current reader extracts all values and converts dates / times and hyperlinks automatically.
*
* @see importer/BaseImporter::loadData()
*/
public function loadData($path)
{
$ext = afsFileSystem::create()->getExtension($path);
switch ($ext) {
case "xls":
$type = "Excel5";
break;
case "xlsx":
$type = "Excel2007";
break;
case "ods":
$type = "OOCalc";
break;
}
$allowed_mimes = array("application/zip; charset=binary", "application/vnd.ms-office; charset=binary", "application/octet-stream; charset=binary");
try {
$finfo = finfo_open(FILEINFO_MIME);
if (!in_array(finfo_file($finfo, $path), $allowed_mimes)) {
throw new ImporterException("Not a Spreadsheet document!");
}
$method = $this->properties->raw ? "getValue" : "getCalculatedValue";
$objReader = PHPExcel_IOFactory::createReader($type);
$first_sheet = null;
if ($this->properties->worksheet) {
$sheets = explode(",", str_replace(" ", "", $this->properties->worksheet));
if ($this->properties->has_header && !$this->properties->worksheets_as_models) {
$all_sheets = $objReader->listWorksheetNames($path);
$objReader->setLoadSheetsOnly(array((string) $all_sheets[0]));
$objPHPExcel = @$objReader->load($path);
$first_sheet = $objPHPExcel->getSheet(0);
foreach ($first_sheet->getRowIterator() as $row) {
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(true);
foreach ($cellIterator as $cell) {
$this->data[$first_sheet->getTitle()][$row->getRowIndex() - 1][] = $cell->getValue();
}
break;
}
}
$objReader->setLoadSheetsOnly($sheets);
if ($this->properties->worksheets_as_models) {
foreach ($sheets as $sheet) {
if (!class_exists($sheet)) {
throw new ImporterException("Model '" . $sheet . "' does not exist!");
}
}
}
} else {
$objReader->setLoadAllSheets();
}
$objPHPExcel = @$objReader->load($path);
$worksheets = $objPHPExcel->getAllSheets();
foreach ($worksheets as $k => $worksheet) {
if (is_null($worksheet->getCellByColumnAndRow(0, 1)->getValue())) {
unset($worksheets[$k]);
}
}
if (sizeof($worksheets) == 0) {
throw new ImporterException("There is no data to import in file! Did you type worksheet names correctly?");
}
foreach ($worksheets as $worksheet) {
$lineno = 1;
foreach ($worksheet->getRowIterator() as $rk => $row) {
if ($rk == 1 && $first_sheet && $worksheet->getTitle() == $first_sheet->getTitle()) {
continue;
}
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(true);
$lineno++;
$this->properties->lines = $worksheet->getTitle() . ":" . $lineno;
if ($this->properties->worksheets_as_models && $this->isInvalidModel($worksheet->getTitle())) {
throw new ImporterException("Model '" . $worksheet->getTitle() . "' does not exist!");
}
foreach ($cellIterator as $cell) {
if (!is_null($cell) && $cell->getValue()) {
if (PHPExcel_Shared_Date::isDateTime($cell)) {
$value = $cell->getCalculatedValue();
if ($value instanceof PHPExcel_RichText) {
$value = $value->getPlainText();
}
$this->data[$worksheet->getTitle()][$row->getRowIndex() - 1][] = date("Y-m-d H:i:s", PHPExcel_Shared_Date::ExcelToPHP($value));
} else {
if ($cell->hasHyperlink()) {
$this->data[$worksheet->getTitle()][$row->getRowIndex() - 1][] = '<a href="' . $cell->getHyperlink()->getUrl() . '" title="' . $cell->getHyperlink()->getTooltip() . '">' . $cell->{$method}() . '</a>';
} else {
$this->data[$worksheet->getTitle()][$row->getRowIndex() - 1][] = $cell->{$method}();
}
}
}
}
}
}
} catch (Exception $e) {
throw new ImporterException("<b>File</b>: " . $path . "<br /><br /><b>Error</b>: " . $e->getMessage() . "<br/>");
}
}
public function excelBasedConstruct($xls_url, $wdtParameters = array())
{
ini_set("memory_limit", "2048M");
if (!$xls_url) {
throw new WDTException('Excel file not found!');
}
if (!file_exists($xls_url)) {
throw new WDTException('Provided file ' . stripcslashes($xls_url) . ' does not exist!');
}
require_once WDT_ROOT_PATH . '/lib/phpExcel/PHPExcel.php';
$objPHPExcel = new PHPExcel();
if (strpos(strtolower($xls_url), '.xlsx')) {
$objReader = new PHPExcel_Reader_Excel2007();
$objReader->setReadDataOnly(true);
} elseif (strpos(strtolower($xls_url), '.xls')) {
$objReader = new PHPExcel_Reader_Excel5();
$objReader->setReadDataOnly(true);
} elseif (strpos(strtolower($xls_url), '.ods')) {
$objReader = new PHPExcel_Reader_OOCalc();
$objReader->setReadDataOnly(true);
} elseif (strpos(strtolower($xls_url), '.csv')) {
$objReader = new PHPExcel_Reader_CSV();
} else {
throw new WDTException('File format not supported!');
}
$objPHPExcel = $objReader->load($xls_url);
$objWorksheet = $objPHPExcel->getActiveSheet();
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$headingsArray = $objWorksheet->rangeToArray('A1:' . $highestColumn . '1', null, true, true, true);
$headingsArray = $headingsArray[1];
$r = -1;
$namedDataArray = array();
for ($row = 2; $row <= $highestRow; ++$row) {
$dataRow = $objWorksheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, null, true, true, true);
if (isset($dataRow[$row]['A']) && $dataRow[$row]['A'] > '') {
++$r;
foreach ($headingsArray as $dataColumnIndex => $dataColumnHeading) {
$namedDataArray[$r][$dataColumnHeading] = $dataRow[$row][$dataColumnIndex];
if (WDT_DETECT_DATES_IN_EXCEL) {
$cellID = $dataColumnIndex . $row;
if (PHPExcel_Shared_Date::isDateTime($objPHPExcel->getActiveSheet()->getCell($cellID))) {
$namedDataArray[$r][$dataColumnHeading] = PHPExcel_Shared_Date::ExcelToPHP($dataRow[$row][$dataColumnIndex]);
}
}
}
}
}
$namedDataArray = apply_filters('wpdatatables_filter_excel_array', $namedDataArray, $this->getWpId(), $xls_url);
return $this->arrayBasedConstruct($namedDataArray, $wdtParameters);
}
function getImportExcelData($data, $fields, &$error_message, &$goodlines, $keys, $keys_present, $strOriginalTableName, $pageObject, $cipherer, $autoinc, &$total_records)
{
global $cCharset;
foreach ($data->getWorksheetIterator() as $worksheet)
{
$highestRow = $worksheet->getHighestRow();
for ($row = 2; $row <= $highestRow; ++ $row)
{
for ($col = 0; $col < count($fields); ++ $col)
{
$cell = $worksheet->getCellByColumnAndRow($col, $row);
if (PHPExcel_Shared_Date::isDateTime($cell))
{
$date_format=$cell->getParent()->getParent()->getCellXfByIndex( $cell->getXfIndex() )->getNumberFormat()->getFormatCode();
$value=PHPExcel_Style_NumberFormat::ToFormattedString( $cell->getValue(),$date_format);
if(is_a($value, 'PHPExcel_RichText'))
$value = $value->getPlainText();
if($value)
{
$time=array();
if(strtotime($value))
$value=strtotime($value);
else
{
$d_format="";
for($i=0;$i<strlen($date_format);$i++)
{
$letter=substr(strtolower($date_format),$i,1);
if($letter=="d" || $letter=="m" || $letter=="y")
{
if(strpos($d_format,$letter)===false)
$d_format.=$letter;
}
}
$value=strtotime(localdatetime2db($value,$d_format));
}
// $value = PHPExcel_Shared_Date::ExcelToPHP($value);
$time=localtime($value,true);
$val=($time["tm_year"]+1900)."-".($time["tm_mon"]+1)."-".$time["tm_mday"]." ".$time["tm_hour"].":".$time["tm_min"].":".$time["tm_sec"];
}
else
$val=NULL;
}
else
{
$error_handler=set_error_handler("empty_error_handler");
$val=PHPExcel_Shared_String::ConvertEncoding($cell->getValue(), $cCharset, 'UTF-8');
if(is_a($val, 'PHPExcel_RichText'))
$val = $val->getPlainText();
if($error_handler)
set_error_handler($error_handler);
}
preg_match('/^="=(.*|n*)"$/i', $val, $matches);
if (array_key_exists(1, $matches)) {
$val = '='.$matches[1];
}
$arr[$fields[$col]]= $val;
}
$ret = InsertRecord($arr, $row-2, $error_message, $goodlines, $keys, $keys_present,
$strOriginalTableName, $pageObject, $cipherer, $autoinc);
$total_records++;
}
break;
}
}
function import_excel($WebID = "", $file = "")
{
global $xoopsDB, $xoopsTpl;
if (empty($file) or empty($file)) {
return;
}
$myts = MyTextSanitizer::getInstance();
include_once XOOPS_ROOT_PATH . '/modules/tadtools/PHPExcel/IOFactory.php';
$reader = PHPExcel_IOFactory::createReader('Excel5');
$PHPExcel = $reader->load($file);
// 檔案名稱
$sheet = $PHPExcel->getSheet(0);
// 讀取第一個工作表(編號從 0 開始)
$highestRow = $sheet->getHighestRow();
// 取得總列數
$main = "";
// 一次讀取一列
for ($row = 1; $row <= $highestRow; $row++) {
$all = "";
$continue = false;
for ($column = 0; $column <= 5; $column++) {
if (PHPExcel_Shared_Date::isDateTime($sheet->getCellByColumnAndRow($column, $row))) {
$val = PHPExcel_Shared_Date::ExcelToPHPObject($sheet->getCellByColumnAndRow($column, $row)->getValue())->format('Y-m-d');
} else {
$val = $sheet->getCellByColumnAndRow($column, $row)->getCalculatedValue();
}
if ($column == 0 and $val == _MD_TCW_MEM_NUM) {
$continue = true;
}
if ($column <= 4 and empty($val)) {
$continue = true;
}
if ($row == 1 and $column == 1 and $val == _MD_TCW_DEMO_NAME) {
$continue = true;
}
if ($column == 3 and strlen($val) == 6) {
$y = substr($val, 0, 2) + 1911;
$m = substr($val, 2, 2);
$d = substr($val, 4, 2);
$val = "{$y}-{$m}-{$d}";
}
if ($column == 10 and strlen($val) == 9 and substr($val, 0, 1) == 9) {
$val = "0{$val}";
}
$val = $myts->addSlashes($val);
$all .= "\n <td>\n <input type='text' name='c[{$row}][{$column}]' value='{$val}' class='form-control span12'>\n </td>\n ";
}
if ($continue) {
continue;
}
$main .= "<tr>{$all}</tr>";
}
$xoopsTpl->assign('op', 'import_excel');
$xoopsTpl->assign('main', $main);
}
/**
* load data from specially prepared .xls
* @return array
*/
public static function loadFromXls($filename)
{
$data=\PHPExcel_IOFactory::load($filename);
if(!$data) {return false;}
if($data->getSheetCount()==2) {
$sheetIndex=1;
} elseif($data->getSheetCount()==4) {
$sheetIndex=3;
} else {
return false;
}
$data->setActiveSheetIndex($sheetIndex);
$aSheet = $data->getActiveSheet();
//этот массив будет содержать массивы содержащие в себе значения ячеек каждой строки
$list = [];
//получим итератор строки и пройдемся по нему циклом
foreach($aSheet->getRowIterator() as $i => $row){
$list_row = [];
if($sheetIndex==1) {
if($i<2||$i>2&&$i<9) {
continue;
}
} elseif($sheetIndex==3) {
if($i<6) {
continue;
}elseif($i==6) {
$list[]=self::arrFirmRowFromTemplate3($filename);
}
}
//получим итератор ячеек текущей строки
$cellIterator = $row->getCellIterator();
//пройдемся циклом по ячейкам строки
//этот массив будет содержать значения каждой отдельной строки
foreach($cellIterator as $cell){
if(\PHPExcel_Shared_Date::isDateTime($cell)) {
$value=$cell->getValue();
$value=date('d.m.Y',\PHPExcel_Shared_Date::ExcelToPHP($value));
$cell->setValue($value);
}
//заносим значения ячеек одной строки в отдельный массив
$list_row[]=$cell->getCalculatedValue();
}
$list[]=$list_row; //заносим массив со значениями ячеек отдельной строки в "общий массив строк"
}
return $list;
}
/**
* Get next row from file
*
* @return bool Returns true if next row can be returned, false if at end of file
*/
public function nextRow()
{
$this->opa_current_row = array();
if ($this->ops_type == 'xlsx') {
//
// Parse Excel
//
if ($this->opn_current_row > 0) {
$this->opr_file->next();
}
$this->opn_current_row++;
if (!$this->opr_file->valid()) {
return false;
}
if ($o_row = $this->opr_file->current()) {
$this->opa_current_row = array();
$o_cells = $o_row->getCellIterator();
$o_cells->setIterateOnlyExistingCells(false);
$va_row = array();
$vb_val_was_set = false;
$vn_col = 0;
$vn_last_col_set = null;
foreach ($o_cells as $o_cell) {
if (PHPExcel_Shared_Date::isDateTime($o_cell)) {
if (!($vs_val = caGetLocalizedDate(PHPExcel_Shared_Date::ExcelToPHP(trim((string) $o_cell->getValue()))))) {
if (!($vs_val = trim(PHPExcel_Style_NumberFormat::toFormattedString((string) $o_cell->getValue(), 'YYYY-MM-DD')))) {
$vs_val = trim((string) $o_cell->getValue());
}
}
$this->opa_current_row[] = $vs_val;
} else {
$this->opa_current_row[] = $vs_val = trim((string) $o_cell->getValue());
}
if (strlen($vs_val) > 0) {
$vb_val_was_set = true;
$vn_last_col_set = $vn_col;
}
$vn_col++;
if ($vn_col > 255) {
break;
}
// max 255 columns; some Excel files have *thousands* of "phantom" columns
}
return $this->opa_current_row;
}
//}
} else {
//
// Parse text
//
$vn_state = 0;
$vb_in_quote = false;
$this->opn_current_row++;
while (!feof($this->opr_file)) {
$vs_line = '';
while (false !== ($lc = fgetc($this->opr_file))) {
if ($lc == "\n" || $lc == "\r") {
break;
}
$vs_line .= $lc;
}
// skip blank lines (with or without tabs)
if (!$vb_in_quote) {
if (str_replace("\t", '', $vs_line) == '') {
continue;
}
}
$vn_l = mb_strlen($vs_line);
for ($vn_i = 0; $vn_i < $vn_l; $vn_i++) {
if (sizeof($this->opa_current_row) > 255) {
break;
}
$c = mb_substr($vs_line, $vn_i, 1);
switch ($vn_state) {
# -----------------------------------
case 0:
// start of field
$vn_state = 10;
if ($c == $this->ops_text_marker) {
$vb_in_quote = true;
$vs_fld_text = '';
} else {
if ($c == $this->ops_delimiter) {
// empty fields
$this->opa_current_row[] = $vs_fld_text;
$vs_fld_text = '';
$vn_state = 0;
} else {
$vs_fld_text = $c;
}
}
break;
# -----------------------------------
# -----------------------------------
case 10:
// in field
if ($vb_in_quote) {
if ($c == $this->ops_text_marker) {
if (mb_substr($vs_line, $vn_i + 1, 1) != '"') {
// is *not* double quote so leave quoted-ness
$vb_in_quote = false;
} else {
// *is* double quote so treat as single quote in text
$vs_fld_text .= $c;
// add quote
$vn_i++;
// skip next quote
}
break;
} else {
$vs_fld_text .= $c;
break;
}
}
if ($c == $this->ops_delimiter) {
$vn_state = 20;
// fall through
} else {
$vs_fld_text .= $c;
break;
}
# -----------------------------------
# -----------------------------------
case 20:
// end of field
$this->opa_current_row[] = $vs_fld_text;
$vs_fld_text = '';
$vn_state = 0;
break;
# -----------------------------------
}
}
if ($vb_in_quote) {
// add return
$vs_fld_text .= "\n";
} else {
// output last field if not already output
if (strlen($vs_fld_text) > 0) {
$this->opa_current_row[] = $vs_fld_text;
}
return $this->opa_current_row;
}
}
}
return false;
}
/**
* Generates a table based on the provided file and shows a preview
*/
public function previewFileTable($table_data)
{
if (!empty($table_data['file'])) {
$xls_url = urldecode($table_data['file']);
if (strtoupper(substr(PHP_OS, 0, 3)) === 'WIN') {
$xls_url = str_replace(site_url(), str_replace('\\', '/', ABSPATH), $xls_url);
} else {
$xls_url = str_replace(site_url(), ABSPATH, $xls_url);
}
} else {
return _('Empty file', 'wpdatatables');
}
require_once WDT_ROOT_PATH . '/lib/phpExcel/PHPExcel.php';
$objPHPExcel = new PHPExcel();
if (strpos(strtolower($xls_url), '.xlsx')) {
$objReader = new PHPExcel_Reader_Excel2007();
$objReader->setReadDataOnly(true);
} elseif (strpos(strtolower($xls_url), '.xls')) {
$objReader = new PHPExcel_Reader_Excel5();
$objReader->setReadDataOnly(true);
} elseif (strpos(strtolower($xls_url), '.ods')) {
$objReader = new PHPExcel_Reader_OOCalc();
$objReader->setReadDataOnly(true);
} elseif (strpos(strtolower($xls_url), '.csv')) {
$objReader = new PHPExcel_Reader_CSV();
} else {
return _('File format not supported!', 'wpdatatables');
}
$objPHPExcel = $objReader->load($xls_url);
$objWorksheet = $objPHPExcel->getActiveSheet();
$highestRow = $objWorksheet->getHighestRow();
$highestRow = $highestRow > 5 ? 5 : $highestRow;
$highestColumn = $objWorksheet->getHighestColumn();
$headingsArray = $objWorksheet->rangeToArray('A1:' . $highestColumn . '1', null, true, true, true);
$headingsArray = $headingsArray[1];
$r = -1;
$namedDataArray = array();
for ($row = 2; $row <= $highestRow; ++$row) {
$dataRow = $objWorksheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, null, true, true, true);
if (isset($dataRow[$row]['A']) && $dataRow[$row]['A'] > '') {
++$r;
foreach ($headingsArray as $dataColumnIndex => $dataColumnHeading) {
$namedDataArray[$r][$dataColumnHeading] = $dataRow[$row][$dataColumnIndex];
if (WDT_DETECT_DATES_IN_EXCEL) {
$cellID = $dataColumnIndex . $row;
if (PHPExcel_Shared_Date::isDateTime($objPHPExcel->getActiveSheet()->getCell($cellID))) {
$namedDataArray[$r][$dataColumnHeading] = PHPExcel_Shared_Date::ExcelToPHP($dataRow[$row][$dataColumnIndex]);
}
}
}
}
}
$columnTypeArray = WDTTools::detectColumnDataTypes($namedDataArray, $headingsArray);
$possibleColumnTypes = WDTTools::getPossibleColumnTypes();
$ret_val = '';
if (!empty($namedDataArray)) {
ob_start();
include WDT_TEMPLATE_PATH . 'constructor_file_preview.inc.php';
$ret_val = ob_get_contents();
ob_end_clean();
}
return $ret_val;
}
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;
}
public function actionIndex($name)
{
Yii::import('ext.yiiexcel.YiiExcel', true);
Yii::registerAutoloader(array('YiiExcel', 'autoload'), true);
$root = dirname(dirname(__FILE__));
//var_dump($root, $name);
$importFile = $root . "/data/" . $name;
$objDateTime = new DateTime('NOW');
$yes = array();
$no = array();
$cnt = 0;
$ignore = array();
$importFields = Jmuser::fields();
echo 'Import file: ' . $importFile . PHP_EOL;
$objPHPExcel = PHPExcel_IOFactory::load($importFile);
//$objPHPExcel->setReadDataOnly(TRUE);
$objWorksheet = $objPHPExcel->getActiveSheet();
var_dump($importFields);
foreach ($objWorksheet->getRowIterator() as $row) {
$cnt += 1;
if ($cnt <= 1) {
continue;
}
// skip table head
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(FALSE);
$nsUser = array();
#var_dump($cellIterator);
$index = 0;
foreach ($cellIterator as $cell) {
if ($index >= count($importFields)) {
break;
}
$val = $cell->getValue();
print $importFields[$index] . ": " . $val;
if ($importFields[$index] != 'id') {
if (PHPExcel_Shared_Date::isDateTime($cell)) {
if (strlen($val) <= 10) {
$val = trim($cell->getFormattedValue());
$parts = explode("-", $val);
if (3 == count($parts)) {
$val = "{$parts[2]}-{$parts[0]}-{$parts[1]}";
}
}
}
$nsUser[$importFields[$index]] = $val;
}
$index += 1;
}
// var_dump($nsUser);
echo $nsUser['oa'] . " {$cnt}\n";
if (empty($nsUser['oa'])) {
break;
}
$model = Jmuser::model()->findByPk($nsUser['oa']);
if (!$model) {
$model = new Jmuser();
$model->oa = $nsUser['oa'];
}
$changedColumns = array();
foreach ($model->attributes as $k => $v) {
if (!isset($nsUser[$k]) || "" === trim($nsUser[$k])) {
file_put_contents("/tmp/continue.log", __FILE__ . ":" . __LINE__ . " " . $k . " skipped\n", FILE_APPEND);
continue;
}
$v = $nsUser[$k];
if ($model->{$k} == $v) {
file_put_contents("/tmp/continue.log", __FILE__ . ":" . __LINE__ . " " . $k . " skipped\n", FILE_APPEND);
continue;
}
$model->{$k} = $v;
$changedColumns[] = $k;
}
$extra = json_decode($model->extra, true);
$extraMap = array(29 => "clothes", 30 => "luxian", 31 => "分团号", 32 => "出入境领队", 33 => "游览陪同领队", 63 => "去程日期", 64 => "去程航班", 65 => "去程航班时间", 66 => "回程日期", 67 => "回程航班", 68 => "回程航班时间");
foreach ($extraMap as $k => $v) {
$cell = $objWorksheet->getCellByColumnAndRow($k, $row->getRowIndex());
if (PHPExcel_Shared_Date::isDateTime($cell)) {
$extra[$v] = $cell->getFormattedValue();
} else {
$extra[$v] = $cell->getCalculatedValue();
}
}
$model->extra = json_encode($extra);
$celltw = $objWorksheet->getCellByColumnAndRow(34, $row->getRowIndex());
$cellhk = $objWorksheet->getCellByColumnAndRow(40, $row->getRowIndex());
$paper = json_decode($model->paper, true);
$paper['twpassport'] = $celltw->getCalculatedValue();
$paper['hkpassport'] = $cellhk->getCalculatedValue();
$model->paper = json_encode($paper);
/*if (count($changedColumns) == 0) {
$ignore[] = $nsUser['oa'];
continue;
}*/
if ($model->save()) {
file_put_contents("/tmp/import.log", "NS OK: " . json_encode($nsUser) . "\n", FILE_APPEND);
$yes[] = $nsUser['oa'];
} else {
$no[] = $nsUser['oa'];
var_dump($model->getErrors());
file_put_contents("/tmp/import.log", "NS Fail: " . json_encode($nsUser) . "\n", FILE_APPEND);
}
echo $model->oa . PHP_EOL;
}
echo "<p>导入成功" . count($yes) . ", 失败" . count($no) . ", 忽略(无变更)" . count($ignore) . "</p>";
echo "<p>导入成功: " . implode(', ', $yes) . "</p>";
echo "<p>导入失败: " . implode(', ', $no) . "</p>";
echo "<p>无变更: " . implode(', ', $ignore) . "</p>";
}
$_DATOS_EXCEL2[$i]['Name'] = $objFree->getActiveSheet()->getCell('B' . $i)->getCalculatedValue();
$_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);
/**
* Get no more the 100 rows of the file's data to display the file's preview.
* It could augment the fieldsData array
* It requires the PHPExcel plugin
* @param PHPExcel fileHandle
* @param &Array fieldsData The corresponding import fields data
* @return Array
*/
function getPreviewDataFromExcel($fileHandle, &$fieldsData)
{
global $locale_info;
$previewData = array();
$remainNumOfPreviewRows = 100;
foreach ($fileHandle->getWorksheetIterator() as $worksheet) {
if ($remainNumOfPreviewRows <= 0) {
break;
}
// get the number of rows for the current worksheet
$highestRow = $worksheet->getHighestRow();
if ($highestRow > $remainNumOfPreviewRows) {
$highestRow = $remainNumOfPreviewRows;
}
$remainNumOfPreviewRows -= $highestRow;
// get a litteral index of the 'highest' column (e.g. 'K')
$highestColumn = $worksheet->getHighestColumn();
// get an index number of the 'highest' column (e.g. 11)
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
// start traversing rows from the first one that contains columns' names
for ($row = 1; $row <= $highestRow; $row++) {
$rowData = array();
for ($col = 0; $col < $highestColumnIndex; $col++) {
$cell = $worksheet->getCellByColumnAndRow($col, $row);
$cellValue = $cell->getValue();
if ($row > 1) {
$columnMatched = isset($fieldsData[$col]);
if (PHPExcel_Shared_Date::isDateTime($cell)) {
$cellDateFormat = $fileHandle->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->getFormatCode();
$cellTextValue = PHPExcel_Style_NumberFormat::ToFormattedString($cellValue, $cellDateFormat);
$cellValue = getTimeStamp($cellTextValue, $cellDateFormat);
if (!$columnMatched) {
$fieldsData[$col] = array();
}
$fieldsData[$col]["dateTimeType"] = true;
$fieldsData[$col]["requireFormatting"] = true;
} else {
if ($columnMatched && $fieldsData[$col]["dateTimeType"] && !strlen($dateFormat)) {
$dateFormat = ImportPage::extractDateFormat($cellValue);
}
}
}
$rowData[] = $cellValue;
}
$tableData[] = $rowData;
}
}
$previewData["tableData"] = $tableData;
if (ImportPage::hasDateTimeFields($fieldsData)) {
$previewData["dateFormat"] = !strlen($dateFormat) ? $locale_info["LOCALE_SSHORTDATE"] : $dateFormat;
}
return $previewData;
}
public function cargaExcel()
{
$filtro = new Zend_Filter_Alpha();
$this->load->library("excel");
$excel = $this->excel->leerExcel(BASEPATH . "../media/embarazos.xlsx");
$columnas = array();
$worksheet = $excel->setActiveSheetIndex(0);
$primera = true;
foreach ($worksheet->getRowIterator() as $row) {
if (!$primera) {
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(true);
// Loop all cells, even if it is not set
$apellido_paterno = "";
$apellido_materno = "";
$propiedades = array();
$FPP = "";
$FUR = "";
foreach ($cellIterator as $cell) {
if (!is_null($cell) and $cell->getCalculatedValue() != "") {
if ($filtro->filter(trim(substr($cell->getCoordinate(), 0, 2))) == "A") {
$propiedades["RUN"] = $cell->getCalculatedValue();
}
if ($filtro->filter(trim(substr($cell->getCoordinate(), 0, 2))) == "B") {
$apellido_paterno = $cell->getCalculatedValue();
}
if ($filtro->filter(trim(substr($cell->getCoordinate(), 0, 2))) == "C") {
$apellido_materno = $cell->getCalculatedValue();
}
if ($filtro->filter(trim(substr($cell->getCoordinate(), 0, 2))) == "D") {
$propiedades["NOMBRE"] = $cell->getCalculatedValue();
}
if ($filtro->filter(trim(substr($cell->getCoordinate(), 0, 2))) == "E") {
$propiedades["EDAD"] = $cell->getCalculatedValue();
}
if ($filtro->filter(trim(substr($cell->getCoordinate(), 0, 2))) == "F") {
if (PHPExcel_Shared_Date::isDateTime($cell)) {
$fecha = date("Y-m-d", PHPExcel_Shared_Date::ExcelToPHP($cell->getValue()));
$FPP = $fecha;
}
}
if ($filtro->filter(trim(substr($cell->getCoordinate(), 0, 2))) == "G") {
if (PHPExcel_Shared_Date::isDateTime($cell)) {
$fecha = date("Y-m-d", PHPExcel_Shared_Date::ExcelToPHP($cell->getValue()));
$FUR = $fecha;
}
}
}
}
$propiedades["APELLIDO"] = $apellido_paterno . " " . $apellido_materno;
$this->_intoxicacion_model->insert(array("fecha" => DATE("Y-m-d"), "propiedades" => json_encode($propiedades), "FUR" => $FUR, "id_usuario" => 1, "FPP" => $FPP));
}
$primera = false;
}
// print_r($columnas);
}
/**
*
*
* @param string $ps_source
* @param array $pa_options
* @return bool
*/
public function nextRow()
{
if (!$this->opo_rows) {
return false;
}
if ($this->opn_current_row > 0) {
$this->opo_rows->next();
}
$this->opn_current_row++;
if (!$this->opo_rows->valid()) {
return false;
}
if ($o_row = $this->opo_rows->current()) {
$this->opa_row_buf = array(null);
$o_cells = $o_row->getCellIterator();
$o_cells->setIterateOnlyExistingCells(false);
$va_row = array();
$vb_val_was_set = false;
$vn_col = 0;
$vn_last_col_set = null;
foreach ($o_cells as $o_cell) {
if (PHPExcel_Shared_Date::isDateTime($o_cell)) {
if (!($vs_val = caGetLocalizedDate(PHPExcel_Shared_Date::ExcelToPHP(trim((string) $o_cell->getValue()))))) {
$vs_val = trim((string) $o_cell->getValue());
}
$this->opa_row_buf[] = $vs_val;
} else {
$this->opa_row_buf[] = $vs_val = trim((string) $o_cell->getValue());
}
if ($vs_val) {
$vb_val_was_set = true;
$vn_last_col_set = $vn_col;
}
$vn_col++;
}
if (!$vb_val_was_set) {
return $this->nextRow();
}
// skip completely blank rows
//$this->opa_row_buf = array_slice($this->opa_row_buf, 0, $vn_last_col_set);
return $o_row;
}
return false;
}
function procesar_archivo_obras()
{
if (isset($_FILES['archivo_obras']) && !empty($_FILES['archivo_obras'])) {
$intelisis = $this->load->database('intelisis', true);
$oracle = $this->load->database('apedev', true);
//datos de predio id para insertarlos en las otras tablas
$datos_predio = array();
$datos_piso = array();
$inmueble_datos = array();
$letters = range('a', 'z');
$bancos = array("HSBC" => "021", "BBVA Bancomer" => "012");
$headers_inmueble = array(1 => 'Inmueble_Id', 2 => 'Codigo_Inmueble', 3 => 'Nombre_Inmueble', 4 => 'Status', 5 => 'Fecha_Inicio', 7 => 'Razon_Social_Id', 8 => 'BANCO_ID', 9 => 'NO_CUENTA', 10 => 'CLAVE_SERVICIO', 11 => 'Dias_Pago', 14 => 'Creation_Date');
$headers_predial = array(1 => 'Predio_Id', 2 => 'Inmueble_id', 4 => 'Nombre_De_Predio', 5 => 'Estatus_De_Predio', 8 => 'Superficie_Terreno', 9 => 'Calle', 10 => 'Numero_Exterior', 11 => 'Numero_Interior', 12 => 'Colonia', 13 => 'Delegacion_Municipio', 14 => 'Codigo_Postal', 15 => 'Estado', 16 => 'Ciudad');
$headers_piso = array(1 => 'PISO_ID', 2 => 'INMUEBLE_ID', 3 => 'PREDIO_ID', 4 => 'ESTATUS_PISO', 5 => 'CATEGORIA_PISO', 6 => 'NIVEL_PISO', 7 => 'AREA_CONSTRUIDA', 8 => 'AREA_RENTABLE');
$headers_local = array(1 => 'LOCAL_ID', 2 => 'INMUEBLE_ID', 3 => 'PREDIO_ID', 4 => 'PISO_ID', 5 => 'NUMERO', 6 => 'AREA_RENTABLE', 7 => 'TIPO_DE_LOCAL', 8 => 'CATEGORIA_LOCAL', 9 => 'ESTATUS_LOCAL', 20 => 'USO_LOCAL', 21 => 'LOCAL_INTELISIS_ID');
$permitidos = array('xls', 'xlsx', 'XLS', 'XLSX');
$archivoNombre = $_FILES['archivo_obras']['name'];
$archivoTipo = $_FILES['archivo_obras']['type'];
$tamanoH = $_FILES['archivo_obras']['size'];
$ext = pathinfo($archivoNombre, PATHINFO_EXTENSION);
if (in_array($ext, $permitidos)) {
move_uploaded_file($_FILES['archivo_obras']['tmp_name'], DIRORACLE . $archivoNombre);
$this->load->library('excel');
/*
* PHP Excel - Read a simple 2007 XLSX Excel file
*/
/** Set default timezone (will throw a notice otherwise) */
//date_default_timezone_set('America/Los_Angeles');
//date_default_timezone_set('UTC');
$saveTimeZone = date_default_timezone_get();
date_default_timezone_set($saveTimeZone);
//include 'Classes/PHPExcel/IOFactory.php';
$inputFileName = DIRORACLE . $archivoNombre;
// Read your Excel workbook
try {
$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);
} catch (Exception $e) {
die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME) . '": ' . $e->getMessage());
}
$worksheetNames = $objPHPExcel->getSheetNames($inputFileName);
foreach ($worksheetNames as $key => $sheetName) {
// Get worksheet dimensions
$sheet = $objPHPExcel->getSheetByName($sheetName);
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
//Insertar Datos tabla XXAPE_INMUEBLE
if ($sheetName == 'XXAPE_INMUEBLE') {
// Loop para recorrer todos las filas de la hoja
for ($row = 2; $row <= $highestRow; $row++) {
$banco_id = null;
$no_cuenta = null;
$clave_servicio = null;
// Obtener row completo en array
$rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
$temp_insert_inmueble = array();
//Recorrer el row que obtuvimos
foreach ($rowData[0] as $k => $v) {
if (isset($headers_inmueble[$k + 1]) && $headers_inmueble[$k + 1] == 'Inmueble_Id') {
$inmueble_datos[$rowData[0][1]] = $v;
$oracle_data_inmueble = array();
$q = $intelisis->query("SELECT * FROM CUENTAS_BANCARIAS WHERE SUCURSAL_ID = {$v}");
if ($q->num_rows() > 0) {
foreach ($q->result() as $intel_data_cuentas) {
$oracle_data_inmueble = $intel_data_cuentas;
}
$q->free_result();
}
if (!empty($oracle_data_inmueble)) {
$banco_id = $bancos[$oracle_data_inmueble->BANCO];
$no_cuenta = $oracle_data_inmueble->NCUENTA;
$clave_servicio = $oracle_data_inmueble->CLAVEDESERVICIO;
}
$temp_insert_inmueble[$headers_inmueble[$k + 1]] = $v;
} elseif (isset($headers_inmueble[$k + 1]) && $headers_inmueble[$k + 1] == 'BANCO_ID') {
$temp_insert_inmueble[$headers_inmueble[$k + 1]] = $banco_id;
} elseif (isset($headers_inmueble[$k + 1]) && $headers_inmueble[$k + 1] == 'NO_CUENTA') {
$temp_insert_inmueble[$headers_inmueble[$k + 1]] = $no_cuenta;
} elseif (isset($headers_inmueble[$k + 1]) && $headers_inmueble[$k + 1] == 'CLAVE_SERVICIO') {
$temp_insert_inmueble[$headers_inmueble[$k + 1]] = $clave_servicio;
} elseif (isset($headers_inmueble[$k + 1])) {
//Instertar Fecha en formato correcto
if ($headers_inmueble[$k + 1] == "Fecha_Inicio" || $headers_inmueble[$k + 1] == "Creation_Date") {
$cell = $objPHPExcel->getActiveSheet()->getCell(strtoupper($letters[$k]) . $row);
$InvDate = $cell->getValue();
if (PHPExcel_Shared_Date::isDateTime($cell)) {
$InvDate = date("Y-m-d", PHPExcel_Shared_Date::ExcelToPHP($InvDate));
}
$temp_insert_inmueble[$headers_inmueble[$k + 1]] = $InvDate;
} else {
$temp_insert_inmueble[$headers_inmueble[$k + 1]] = $v;
}
}
}
$intelisis->insert('Final1.INMUEBLE', $temp_insert_inmueble);
}
}
//Insertar Datos tabla XXAPE_PREDIAL
if ($sheetName == 'XXAPE_PREDIAL') {
//Autoincrementa PREDIO_ID
$q = $oracle->query("SELECT (MAX(PREDIO_ID)+ 1) as PREDIO_ID FROM xxape_predial");
if ($q->num_rows() > 0) {
foreach ($q->result() as $ora_data_predial) {
$predio_id = $ora_data_predial->PREDIO_ID;
}
$q->free_result();
}
// Loop para recorrer todos las filas de la hoja
for ($row = 2; $row <= $highestRow; $row++) {
// Obtener row completo en array
$rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
$temp_insert_predial = array();
//Recorrer el row que obtuvimos
foreach ($rowData[0] as $k => $v) {
//Insertamos Predio_Id
if (isset($headers_predial[$k + 1]) && $headers_predial[$k + 1] == 'Predio_Id') {
$temp_insert_predial[$headers_predial[$k + 1]] = $predio_id;
$datos_predio[$rowData[0][3]] = $predio_id;
} elseif (isset($headers_predial[$k + 1]) && $headers_predial[$k + 1] == 'Inmueble_id') {
$temp_insert_predial[$headers_predial[$k + 1]] = $inmueble_datos[$rowData[0][2]];
} elseif (isset($headers_predial[$k + 1])) {
$temp_insert_predial[$headers_predial[$k + 1]] = $v;
}
}
++$predio_id;
$intelisis->insert('Final1.PREDIAL', $temp_insert_predial);
}
}
//Insertar Datos tabla XXAPE_PISO
if ($sheetName == 'XXAPE_PISO') {
$q = $oracle->query("SELECT (MAX(PISO_ID)+ 1) as PISO_ID FROM xxape_piso");
if ($q->num_rows() > 0) {
foreach ($q->result() as $ora_data_piso) {
$piso_id = $ora_data_piso->PISO_ID;
}
$q->free_result();
}
// Loop para recorrer todos las filas de la hoja
for ($row = 2; $row <= $highestRow; $row++) {
// Obtener row completo en array
$rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
$temp_insert = array();
//Recorrer el row que obtuvimos
foreach ($rowData[0] as $k => $v) {
//Insertamos Piso_Id
if (isset($headers_piso[$k + 1]) && $headers_piso[$k + 1] == 'PISO_ID') {
$temp_insert[$headers_piso[$k + 1]] = $piso_id;
} elseif (isset($headers_piso[$k + 1]) && $headers_piso[$k + 1] == 'PREDIO_ID') {
$temp_insert[$headers_piso[$k + 1]] = $datos_predio[$v];
} elseif (isset($headers_piso[$k + 1])) {
$temp_insert[$headers_piso[$k + 1]] = $v;
}
if (isset($headers_piso[$k + 1]) && $headers_piso[$k + 1] == 'NIVEL_PISO') {
$datos_piso[$v] = $piso_id;
}
}
++$piso_id;
$intelisis->insert('Final1.PISO', $temp_insert);
}
}
//Insertar Datos tabla XXAPE_LOCAL
if ($sheetName == 'XXAPE_LOCAL') {
$q = $oracle->query("SELECT (MAX(LOCAL_ID)+ 1) as LOCAL_ID FROM xxape_local");
if ($q->num_rows() > 0) {
foreach ($q->result() as $ora_data_local) {
$local_id = $ora_data_local->LOCAL_ID;
}
$q->free_result();
}
// Loop para recorrer todos las filas de la hoja
for ($row = 2; $row <= $highestRow; $row++) {
// Obtener row completo en array
$rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
$temp_insert = array();
//Recorrer el row que obtuvimos
foreach ($rowData[0] as $k => $v) {
//Insertamos Local_Id
if (isset($headers_local[$k + 1]) && $headers_local[$k + 1] == 'LOCAL_ID') {
$temp_insert[$headers_local[$k + 1]] = $local_id;
} elseif (isset($headers_local[$k + 1]) && $headers_local[$k + 1] == 'PREDIO_ID') {
$temp_insert[$headers_local[$k + 1]] = $datos_predio[$v];
} elseif (isset($headers_local[$k + 1]) && $headers_local[$k + 1] == 'PISO_ID') {
$temp_insert[$headers_local[$k + 1]] = $datos_piso[$v];
} elseif (isset($headers_local[$k + 1])) {
$temp_insert[$headers_local[$k + 1]] = $v;
}
}
++$local_id;
$intelisis->insert('Final1.LOCAL', $temp_insert);
}
}
}
$this->session->set_flashdata('msg', '<div class="msg mt20 mb20">El archivo fue cargado.</div>');
} else {
$this->session->set_flashdata('msg', '<div class="msg mt20 mb20">Favor de Ingresar un Formato valido.</div>');
}
redirect('oracle');
}
}
/**
* Write Cell
*
* @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
* @param PHPExcel_Worksheet $pSheet Worksheet
* @param PHPExcel_Cell $pCell Cell
* @param string[] $pStringTable String table
* @param string[] $pFlippedStringTable String table (flipped), for faster index searching
* @throws Exception
*/
private function _writeCell(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null, PHPExcel_Cell $pCell = null, $pStringTable = null, $pFlippedStringTable = null)
{
if (is_array($pStringTable) && is_array($pFlippedStringTable)) {
// Cell
$objWriter->startElement('c');
$objWriter->writeAttribute('r', $pCell->getCoordinate());
// Sheet styles
$aStyles = $pSheet->getStyles();
if (isset($aStyles[$pCell->getCoordinate()])) {
$styleIndex = $this->getParentWriter()->getStylesHashTable()->getIndexForHashCode($aStyles[$pCell->getCoordinate()]->getHashCode());
if ($styleIndex != '') {
$objWriter->writeAttribute('s', $styleIndex);
}
}
// If cell value is supplied, write cell value
if (is_object($pCell->getValue()) || $pCell->getValue() !== '') {
// Map type
$mappedType = $pCell->getDataType();
// Write data type depending on its type
switch (strtolower($mappedType)) {
case 'inlinestr':
// Inline string
$objWriter->writeAttribute('t', $mappedType);
break;
case 's':
// String
$objWriter->writeAttribute('t', $mappedType);
break;
case 'b':
// Boolean
$objWriter->writeAttribute('t', $mappedType);
break;
case 'f':
// Formula
$calculatedValue = null;
if ($this->getParentWriter()->getPreCalculateFormulas()) {
$calculatedValue = $pCell->getCalculatedValue();
} else {
$calculatedValue = $pCell->getValue();
}
if (is_string($calculatedValue)) {
$objWriter->writeAttribute('t', 'str');
}
break;
}
// Write data depending on its type
switch (strtolower($mappedType)) {
case 'inlinestr':
// Inline string
if (!$pCell->getValue() instanceof PHPExcel_RichText) {
$objWriter->writeElement('t', PHPExcel_Shared_String::ControlCharacterPHP2OOXML($pCell->getValue()));
} else {
if ($pCell->getValue() instanceof PHPExcel_RichText) {
$objWriter->startElement('is');
$this->getParentWriter()->getWriterPart('stringtable')->writeRichText($objWriter, $pCell->getValue());
$objWriter->endElement();
}
}
break;
case 's':
// String
if (!$pCell->getValue() instanceof PHPExcel_RichText) {
if (isset($pFlippedStringTable[$pCell->getValue()])) {
$objWriter->writeElement('v', $pFlippedStringTable[$pCell->getValue()]);
}
} else {
if ($pCell->getValue() instanceof PHPExcel_RichText) {
$objWriter->writeElement('v', $pFlippedStringTable[$pCell->getValue()->getHashCode()]);
}
}
break;
case 'f':
// Formula
$objWriter->writeElement('f', substr($pCell->getValue(), 1));
if ($this->getParentWriter()->getOffice2003Compatibility() === false) {
if ($this->getParentWriter()->getPreCalculateFormulas()) {
$calculatedValue = $pCell->getCalculatedValue();
if (substr($calculatedValue, 0, 1) != '#') {
$objWriter->writeElement('v', $calculatedValue);
} else {
$objWriter->writeElement('v', '0');
}
} else {
$objWriter->writeElement('v', '0');
}
}
break;
case 'n':
// Numeric
if (PHPExcel_Shared_Date::isDateTime($pCell)) {
$dateValue = $pCell->getValue();
if (is_string($dateValue)) {
// Error string
$objWriter->writeElement('v', $pFlippedStringTable[$dateValue]);
} elseif (!is_float($dateValue)) {
// PHP serialized date/time or date/time object
$objWriter->writeElement('v', PHPExcel_Shared_Date::PHPToExcel($dateValue));
} else {
// Excel serialized date/time
$objWriter->writeElement('v', $dateValue);
}
} else {
$objWriter->writeElement('v', $pCell->getValue());
}
break;
case 'b':
// Boolean
$objWriter->writeElement('v', $pCell->getValue() ? '1' : '0');
break;
}
}
$objWriter->endElement();
} else {
throw new Exception("Invalid parameters passed.");
}
}
