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; } }
/** * Used to write internal reference hyperlinks such as "Sheet1!A1". * * @access private * @see _writeUrl() * @param integer $row1 Start row * @param integer $col1 Start column * @param integer $row2 End row * @param integer $col2 End column * @param string $url URL string * @return integer */ function _writeUrlInternal($row1, $col1, $row2, $col2, $url) { $record = 0x1b8; // Record identifier $length = 0x0; // Bytes to follow // Strip URL type $url = preg_replace('/^internal:/', '', $url); // Pack the undocumented parts of the hyperlink stream $unknown1 = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B02000000"); // Pack the option flags $options = pack("V", 0x8); // Convert the URL type and to a null terminated wchar string $url .= ""; // character count $url_len = PHPExcel_Shared_String::CountCharacters($url); $url_len = pack('V', $url_len); $url = PHPExcel_Shared_String::ConvertEncoding($url, 'UTF-16LE', 'UTF-8'); // Calculate the data length $length = 0x24 + strlen($url); // Pack the header data $header = pack("vv", $record, $length); $data = pack("vvvv", $row1, $row2, $col1, $col2); // Write the packed data $this->_append($header . $data . $unknown1 . $options . $url_len . $url); return 0; }
$size = 7 + $subSize; $data = $this->_getFormulaFromData(substr($formulaData, 7, $subSize)); break; case 0x29: // Variable reference sub-expression
private static function _convertStringEncoding($string, $charset) { if ($charset != 'UTF-8') { return PHPExcel_Shared_String::ConvertEncoding($string, 'UTF-8', $charset); } return $string; }
function ExportExcelSave($filename, $format, $objPHPExcel) { global $cCharset; $filename = PHPExcel_Shared_String::ConvertEncoding($filename, 'UTF-8', $cCharset); if ($format == "Excel2007") { header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); } else { header('Content-Type: application/vnd.ms-excel'); } header('Content-Disposition: attachment;filename="' . $filename . '";'); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, $format); $objWriter->save('php://output'); }
/** * Loads PHPExcel from file into PHPExcel instance * * @param string $pFilename * @param PHPExcel $objPHPExcel * @return PHPExcel * @throws PHPExcel_Reader_Exception */ public function loadIntoExisting($pFilename, PHPExcel $objPHPExcel) { $lineEnding = ini_get('auto_detect_line_endings'); ini_set('auto_detect_line_endings', true); // Open file $this->_openFile($pFilename); if (!$this->_isValidFormat()) { fclose($this->_fileHandle); throw new PHPExcel_Reader_Exception($pFilename . " is an Invalid Spreadsheet file."); } $fileHandle = $this->_fileHandle; // Skip BOM, if any $this->_skipBOM(); // Create new PHPExcel object while ($objPHPExcel->getSheetCount() <= $this->_sheetIndex) { $objPHPExcel->createSheet(); } $sheet = $objPHPExcel->setActiveSheetIndex($this->_sheetIndex); $escapeEnclosures = array("\\" . $this->_enclosure, $this->_enclosure . $this->_enclosure); // Set our starting row based on whether we're in contiguous mode or not $currentRow = 1; if ($this->_contiguous) { $currentRow = $this->_contiguousRow == -1 ? $sheet->getHighestRow() : $this->_contiguousRow; } // Loop through each line of the file in turn while (($rowData = fgetcsv($fileHandle, 0, $this->_delimiter, $this->_enclosure)) !== FALSE) { $columnLetter = 'A'; foreach ($rowData as $rowDatum) { if ($rowDatum != '' && $this->_readFilter->readCell($columnLetter, $currentRow)) { // Unescape enclosures $rowDatum = str_replace($escapeEnclosures, $this->_enclosure, $rowDatum); // Convert encoding if necessary if ($this->_inputEncoding !== 'UTF-8') { $rowDatum = PHPExcel_Shared_String::ConvertEncoding($rowDatum, 'UTF-8', $this->_inputEncoding); } // Set cell value $sheet->getCell($columnLetter . $currentRow)->setValue($rowDatum); } ++$columnLetter; } ++$currentRow; } // Close file fclose($fileHandle); if ($this->_contiguous) { $this->_contiguousRow = $currentRow; } ini_set('auto_detect_line_endings', $lineEnding); // Return return $objPHPExcel; }
/** * Loads PHPExcel from file into PHPExcel instance * * @access public * @param string $pFilename * @param PHPExcel $objPHPExcel * @return PHPExcel * @throws Exception */ public function loadIntoExisting($pFilename, PHPExcel $objPHPExcel) { // Check if file exists if (!file_exists($pFilename)) { throw new Exception("Could not open " . $pFilename . " for reading! File does not exist."); } // Create new PHPExcel while ($objPHPExcel->getSheetCount() <= $this->_sheetIndex) { $objPHPExcel->createSheet(); } $objPHPExcel->setActiveSheetIndex($this->_sheetIndex); // Open file $fileHandle = fopen($pFilename, 'r'); if ($fileHandle === false) { throw new Exception("Could not open file {$pFilename} for reading."); } // Skip BOM, if any switch ($this->_inputEncoding) { case 'UTF-8': fgets($fileHandle, 4) == "" ? fseek($fileHandle, 3) : fseek($fileHandle, 0); break; default: break; } // Loop through file $currentRow = 0; $rowData = array(); while (($rowData = fgetcsv($fileHandle, 0, $this->_delimiter, $this->_enclosure)) !== FALSE) { ++$currentRow; $rowDataCount = count($rowData); for ($i = 0; $i < $rowDataCount; ++$i) { $columnLetter = PHPExcel_Cell::stringFromColumnIndex($i); if ($rowData[$i] != '' && $this->_readFilter->readCell($columnLetter, $currentRow)) { // Unescape enclosures $rowData[$i] = str_replace("\\" . $this->_enclosure, $this->_enclosure, $rowData[$i]); $rowData[$i] = str_replace($this->_enclosure . $this->_enclosure, $this->_enclosure, $rowData[$i]); // Convert encoding if necessary if ($this->_inputEncoding !== 'UTF-8') { $rowData[$i] = PHPExcel_Shared_String::ConvertEncoding($rowData[$i], 'UTF-8', $this->_inputEncoding); } // Set cell value $objPHPExcel->getActiveSheet()->getCell($columnLetter . $currentRow)->setValue($rowData[$i]); } } } // Close file fclose($fileHandle); // Return return $objPHPExcel; }
/** * Loads PHPExcel from file into PHPExcel instance * * @access public * @param string $pFilename * @param PHPExcel $objPHPExcel * @return PHPExcel * @throws Exception */ public function loadIntoExisting($pFilename, PHPExcel $objPHPExcel) { // Check if file exists if (!file_exists($pFilename)) { throw new Exception("Could not open " . $pFilename . " for reading! File does not exist."); } // Create new PHPExcel while ($objPHPExcel->getSheetCount() <= $this->_sheetIndex) { $objPHPExcel->createSheet(); } $objPHPExcel->setActiveSheetIndex($this->_sheetIndex); // Open file $fileHandle = fopen($pFilename, 'r'); if ($fileHandle === false) { throw new Exception("Could not open file {$pFilename} for reading."); } // Skip BOM, if any switch ($this->_inputEncoding) { case 'UTF-8': fgets($fileHandle, 4) == "" ? fseek($fileHandle, 3) : fseek($fileHandle, 0); break; case 'UTF-16LE': fgets($fileHandle, 3) == "ÿþ" ? fseek($fileHandle, 2) : fseek($fileHandle, 0); break; case 'UTF-16BE': fgets($fileHandle, 3) == "þÿ" ? fseek($fileHandle, 2) : fseek($fileHandle, 0); break; case 'UTF-32LE': fgets($fileHandle, 5) == "ÿþ" ? fseek($fileHandle, 4) : fseek($fileHandle, 0); break; case 'UTF-32BE': fgets($fileHandle, 5) == "þÿ" ? fseek($fileHandle, 4) : fseek($fileHandle, 0); break; default: break; } $escapeEnclosures = array("\\" . $this->_enclosure, $this->_enclosure . $this->_enclosure); // Set our starting row based on whether we're in contiguous mode or not $currentRow = 1; if ($this->_contiguous) { $currentRow = $this->_contiguousRow == -1 ? $objPHPExcel->getActiveSheet()->getHighestRow() : $this->_contiguousRow; } // Loop through each line of the file in turn while (($rowData = fgetcsv($fileHandle, 0, $this->_delimiter, $this->_enclosure)) !== FALSE) { $columnLetter = 'A'; foreach ($rowData as $rowDatum) { if ($rowDatum != '' && $this->_readFilter->readCell($columnLetter, $currentRow)) { // Unescape enclosures $rowDatum = str_replace($escapeEnclosures, $this->_enclosure, $rowDatum); // Convert encoding if necessary if ($this->_inputEncoding !== 'UTF-8') { $rowDatum = PHPExcel_Shared_String::ConvertEncoding($rowDatum, 'UTF-8', $this->_inputEncoding); } // Set cell value $objPHPExcel->getActiveSheet()->getCell($columnLetter . $currentRow)->setValue($rowDatum); } ++$columnLetter; } ++$currentRow; } // Close file fclose($fileHandle); if ($this->_contiguous) { $this->_contiguousRow = $currentRow; } // Return return $objPHPExcel; }
/** * Read additional document summary information */ private function _readDocumentSummaryInformation() { if (!isset($this->_documentSummaryInformation)) { return; } // offset: 0; size: 2; must be 0xFE 0xFF (UTF-16 LE byte order mark) // offset: 2; size: 2; // offset: 4; size: 2; OS version // offset: 6; size: 2; OS indicator // offset: 8; size: 16 // offset: 24; size: 4; section count $secCount = self::_GetInt4d($this->_documentSummaryInformation, 24); // echo '$secCount = ',$secCount,'<br />'; // offset: 28; size: 16; first section's class id: 02 d5 cd d5 9c 2e 1b 10 93 97 08 00 2b 2c f9 ae // offset: 44; size: 4; first section offset $secOffset = self::_GetInt4d($this->_documentSummaryInformation, 44); // echo '$secOffset = ',$secOffset,'<br />'; // section header // offset: $secOffset; size: 4; section length $secLength = self::_GetInt4d($this->_documentSummaryInformation, $secOffset); // echo '$secLength = ',$secLength,'<br />'; // offset: $secOffset+4; size: 4; property count $countProperties = self::_GetInt4d($this->_documentSummaryInformation, $secOffset + 4); // echo '$countProperties = ',$countProperties,'<br />'; // initialize code page (used to resolve string values) $codePage = 'CP1252'; // offset: ($secOffset+8); size: var // loop through property decarations and properties for ($i = 0; $i < $countProperties; ++$i) { // echo 'Property ',$i,'<br />'; // offset: ($secOffset+8) + (8 * $i); size: 4; property ID $id = self::_GetInt4d($this->_documentSummaryInformation, $secOffset + 8 + 8 * $i); // echo 'ID is ',$id,'<br />'; // Use value of property id as appropriate // offset: 60 + 8 * $i; size: 4; offset from beginning of section (48) $offset = self::_GetInt4d($this->_documentSummaryInformation, $secOffset + 12 + 8 * $i); $type = self::_GetInt4d($this->_documentSummaryInformation, $secOffset + $offset); // echo 'Type is ',$type,', '; // initialize property value $value = null; // extract property value based on property type switch ($type) { case 0x2: // 2 byte signed integer $value = self::_GetInt2d($this->_documentSummaryInformation, $secOffset + 4 + $offset); break; case 0x3: // 4 byte signed integer $value = self::_GetInt4d($this->_documentSummaryInformation, $secOffset + 4 + $offset); break; case 0xb: // Boolean $value = self::_GetInt2d($this->_documentSummaryInformation, $secOffset + 4 + $offset); $value = $value == 0 ? false : true; break; case 0x13: // 4 byte unsigned integer // not needed yet, fix later if necessary break; case 0x1e: // null-terminated string prepended by dword string length $byteLength = self::_GetInt4d($this->_documentSummaryInformation, $secOffset + 4 + $offset); $value = substr($this->_documentSummaryInformation, $secOffset + 8 + $offset, $byteLength); $value = PHPExcel_Shared_String::ConvertEncoding($value, 'UTF-8', $codePage); $value = rtrim($value); break; case 0x40: // Filetime (64-bit value representing the number of 100-nanosecond intervals since January 1, 1601) // PHP-Time $value = PHPExcel_Shared_OLE::OLE2LocalDate(substr($this->_documentSummaryInformation, $secOffset + 4 + $offset, 8)); break; case 0x47: // Clipboard format // not needed yet, fix later if necessary break; } switch ($id) { case 0x1: // Code Page $codePage = PHPExcel_Shared_CodePage::NumberToName($value); break; case 0x2: // Category $this->_phpExcel->getProperties()->setCategory($value); break; case 0x3: // Presentation Target // Not supported by PHPExcel break; case 0x4: // Bytes // Not supported by PHPExcel break; case 0x5: // Lines // Not supported by PHPExcel break; case 0x6: // Paragraphs // Not supported by PHPExcel break; case 0x7: // Slides // Not supported by PHPExcel break; case 0x8: // Notes // Not supported by PHPExcel break; case 0x9: // Hidden Slides // Not supported by PHPExcel break; case 0xa: // MM Clips // Not supported by PHPExcel break; case 0xb: // Scale Crop // Not supported by PHPExcel break; case 0xc: // Heading Pairs // Not supported by PHPExcel break; case 0xd: // Titles of Parts // Not supported by PHPExcel break; case 0xe: // Manager $this->_phpExcel->getProperties()->setManager($value); break; case 0xf: // Company $this->_phpExcel->getProperties()->setCompany($value); break; case 0x10: // Links up-to-date // Not supported by PHPExcel break; } } }
/** * Write a string to the specified row and column (zero indexed). * This is the BIFF8 version (no 255 chars limit). * $format is optional. * Returns 0 : normal termination * -2 : row or column out of range * -3 : long string truncated to 255 chars * * @access public * @param integer $row Zero indexed row * @param integer $col Zero indexed column * @param string $str The string to write * @param mixed $format The XF format for the cell * @return integer */ private function _writeLabelSst($row, $col, $str, $xfIndex) { $strlen = PHPExcel_Shared_String::CountCharacters($str, 'UTF-8'); $str = PHPExcel_Shared_String::ConvertEncoding($str, 'UTF-16LE', 'UTF-8'); $encoding = 0x1; $record = 0xfd; // Record identifier $length = 0xa; // Bytes to follow $str = pack('vC', $strlen, $encoding) . $str; /* check if string is already present */ if (!isset($this->_str_table[$str])) { $this->_str_table[$str] = $this->_str_unique++; } $this->_str_total++; $header = pack('vv', $record, $length); $data = pack('vvvV', $row, $col, $xfIndex, $this->_str_table[$str]); $this->_append($header . $data); }
/** * Import data from an Excel file * @param PHPExcel fileHandle * @param Array fieldsData * @param Array keys * @param ImportPage importPageObject * @param Boolean autoinc * @param Boolean useFirstLine * @param String dateFormat * @return Array */ function ImportDataFromExcel($fileHandle, $fieldsData, $keys, $importPageObject, $autoinc, $useFirstLine, $dateFormat) { global $cCharset; $metaData = array(); $metaData["totalRecords"] = 0; $errorMessages = array(); $unprocessedData = array(); $updatedRecords = 0; $addedRecords = 0; $startRow = $useFirstLine ? 1 : 2; foreach ($fileHandle->getWorksheetIterator() as $worksheet) { $highestRow = $worksheet->getHighestRow(); // 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); for ($row = $startRow; $row <= $highestRow; $row++) { $fieldValuesData = array(); for ($col = 0; $col < $highestColumnIndex; $col++) { if (!isset($fieldsData[$col])) { continue; } $importFieldName = $fieldsData[$col]["fName"]; $cell = $worksheet->getCellByColumnAndRow($col, $row); $cellValue = $cell->getValue(); if (PHPExcel_Shared_Date::isDateTime($cell)) { $cellDateFormat = $fileHandle->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->getFormatCode(); $cellTextValue = PHPExcel_Style_NumberFormat::ToFormattedString($cellValue, $cellDateFormat); $cellValue = getDBDateValue($cellTextValue, $cellDateFormat); } else { if (is_a($cellValue, 'PHPExcel_RichText')) { $cellValue = $cellValue->getPlainText(); } if (IsDateFieldType($fieldsData[$col]["type"])) { $cellValue = getDBDateValue($cellValue, $dateFormat); } $error_handler = set_error_handler("empty_error_handler"); $cellValue = PHPExcel_Shared_String::ConvertEncoding($cellValue, $cCharset, 'UTF-8'); if ($error_handler) { set_error_handler($error_handler); } $matches = array(); preg_match('/^="(=.*)"$/i', $cellValue, $matches); if (array_key_exists(1, $matches)) { $cellValue = $matches[1]; } } $fieldValuesData[$importFieldName] = $cellValue; } $importPageObject->importRecord($fieldValuesData, $keys, $autoinc, $addedRecords, $updatedRecords, $errorMessages, $unprocessedData); $metaData["totalRecords"] = $metaData["totalRecords"] + 1; } } $metaData["addedRecords"] = $addedRecords; $metaData["updatedRecords"] = $updatedRecords; $metaData["errorMessages"] = $errorMessages; $metaData["unprocessedData"] = $unprocessedData; return $metaData; }
/** * Convert string to UTF-8. Only used for BIFF5. * * @param string $string * @return string */ private function _decodeCodepage($string) { if ($this->_codepage) { $result = PHPExcel_Shared_String::ConvertEncoding($string, 'UTF-8', $this->_codepage); } return $result; }
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; } }