本文整理汇总了PHP中PHPExcel_Cell::extractAllCellReferencesInRange方法的典型用法代码示例。如果您正苦于以下问题：PHP PHPExcel_Cell::extractAllCellReferencesInRange方法的具体用法？PHP PHPExcel_Cell::extractAllCellReferencesInRange怎么用？PHP PHPExcel_Cell::extractAllCellReferencesInRange使用的例子？那么恭喜您, 这里精选的方法代码示例或许可以为您提供帮助。您也可以进一步了解该方法所在类PHPExcel_Cell的用法示例。
在下文中一共展示了PHPExcel_Cell::extractAllCellReferencesInRange方法的15个代码示例，这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞，您的评价将有助于系统推荐出更棒的PHP代码示例。
示例1: load
//.........这里部分代码省略.........
(string)$cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_CONTAINSTEXT ||
(string)$cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_EXPRESSION
) && 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 && $xmlSheet && $xmlSheet->sheetProtection) {
foreach ($aKeys as $key) {
$method = "set" . ucfirst($key);
$docSheet->getProtection()->$method($xmlSheet->sheetProtection[$key] == "true");
}
}
if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->sheetProtection) {
$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 && $xmlSheet->autoFilter && !$this->_readDataOnly) {
$docSheet->setAutoFilter((string) $xmlSheet->autoFilter["ref"]);
}
if ($xmlSheet && $xmlSheet->mergeCells && $xmlSheet->mergeCells->mergeCell && !$this->_readDataOnly) {
foreach ($xmlSheet->mergeCells->mergeCell as $mergeCell) {
$docSheet->mergeCells((string) $mergeCell["ref"]);
}
}示例2: extractNamedRange
/**
* Extract range values
*
* @param string &$pRange String based range representation
* @param PHPExcel_Worksheet $pSheet Worksheet
* @return mixed Array of values in range if range contains more than one element. Otherwise, a single value is returned.
* @param boolean $resetLog Flag indicating whether calculation log should be reset or not
* @throws PHPExcel_Calculation_Exception
*/
public function extractNamedRange(&$pRange = 'A1', PHPExcel_Worksheet $pSheet = NULL, $resetLog = TRUE)
{
// Return value
$returnValue = array();
// echo 'extractNamedRange('.$pRange.')<br />';
if ($pSheet !== NULL) {
$pSheetName = $pSheet->getTitle();
// echo 'Current sheet name is '.$pSheetName.'<br />';
// echo 'Range reference is '.$pRange.'<br />';
if (strpos($pRange, '!') !== false) {
// echo '$pRange reference includes sheet reference',PHP_EOL;
list($pSheetName, $pRange) = PHPExcel_Worksheet::extractSheetTitle($pRange, true);
// echo 'New sheet name is '.$pSheetName,PHP_EOL;
// echo 'Adjusted Range reference is '.$pRange,PHP_EOL;
$pSheet = $this->_workbook->getSheetByName($pSheetName);
}
// Named range?
$namedRange = PHPExcel_NamedRange::resolveRange($pRange, $pSheet);
if ($namedRange !== NULL) {
$pSheet = $namedRange->getWorksheet();
// echo 'Named Range '.$pRange.' (';
$pRange = $namedRange->getRange();
$splitRange = PHPExcel_Cell::splitRange($pRange);
// Convert row and column references
if (ctype_alpha($splitRange[0][0])) {
$pRange = $splitRange[0][0] . '1:' . $splitRange[0][1] . $namedRange->getWorksheet()->getHighestRow();
} elseif (ctype_digit($splitRange[0][0])) {
$pRange = 'A' . $splitRange[0][0] . ':' . $namedRange->getWorksheet()->getHighestColumn() . $splitRange[0][1];
}
// echo $pRange.') is in sheet '.$namedRange->getWorksheet()->getTitle().'<br />';
// if ($pSheet->getTitle() != $namedRange->getWorksheet()->getTitle()) {
// if (!$namedRange->getLocalOnly()) {
// $pSheet = $namedRange->getWorksheet();
// } else {
// return $returnValue;
// }
// }
} else {
return PHPExcel_Calculation_Functions::REF();
}
// Extract range
$aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($pRange);
// var_dump($aReferences);
if (!isset($aReferences[1])) {
// Single cell (or single column or row) in range
list($currentCol, $currentRow) = PHPExcel_Cell::coordinateFromString($aReferences[0]);
$cellValue = NULL;
if ($pSheet->cellExists($aReferences[0])) {
$returnValue[$currentRow][$currentCol] = $pSheet->getCell($aReferences[0])->getCalculatedValue($resetLog);
} else {
$returnValue[$currentRow][$currentCol] = NULL;
}
} else {
// Extract cell data for all cells in the range
foreach ($aReferences as $reference) {
// Extract range
list($currentCol, $currentRow) = PHPExcel_Cell::coordinateFromString($reference);
// echo 'NAMED RANGE: $currentCol='.$currentCol.' $currentRow='.$currentRow.'<br />';
$cellValue = NULL;
if ($pSheet->cellExists($reference)) {
$returnValue[$currentRow][$currentCol] = $pSheet->getCell($reference)->getCalculatedValue($resetLog);
} else {
$returnValue[$currentRow][$currentCol] = NULL;
}
}
}
// print_r($returnValue);
// echo '<br />';
}
// Return
return $returnValue;
}示例3: load
//.........这里部分代码省略.........
}
}
}
$conditionals = array();
if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->conditionalFormatting) {
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 || (string) $cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_EXPRESSION) && 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 && $xmlSheet && $xmlSheet->sheetProtection) {
foreach ($aKeys as $key) {
$method = "set" . ucfirst($key);
$docSheet->getProtection()->{$method}(self::boolean((string) $xmlSheet->sheetProtection[$key]));
}
}
if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->sheetProtection) {
$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 && $xmlSheet->autoFilter && !$this->_readDataOnly) {
$autoFilter = $docSheet->getAutoFilter();
$autoFilter->setRange((string) $xmlSheet->autoFilter["ref"]);
foreach ($xmlSheet->autoFilter->filterColumn as $filterColumn) {
$column = $autoFilter->getColumnByOffset((int) $filterColumn["colId"]);
// Check for standard filters
if ($filterColumn->filters) {
$column->setFilterType(PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_FILTER);
$filters = $filterColumn->filters;
if (isset($filters["blank"]) && $filters["blank"] == 1) {
$column->createRule()->setRule(NULL, '')->setRuleType(PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_FILTER);
}示例4: extractNamedRange
/**
* Extract range values
*
* @param string &$pRange String based range representation
* @param PHPExcel_Worksheet $pSheet Worksheet
* @return mixed Array of values in range if range contains more than one element. Otherwise, a single value is returned.
* @throws Exception
*/
public function extractNamedRange(&$pRange = 'A1', PHPExcel_Worksheet $pSheet = null, $resetLog = true)
{
// Return value
$returnValue = array();
// echo 'extractNamedRange('.$pRange.')<br />';
if (!is_null($pSheet)) {
// echo 'Current sheet name is '.$pSheet->getTitle().'<br />';
// echo 'Range reference is '.$pRange.'<br />';
if (strpos($pRange, '!') !== false) {
// echo '$pRange reference includes sheet reference<br />';
$worksheetReference = PHPExcel_Worksheet::extractSheetTitle($pRange, true);
$pSheet = $pSheet->getParent()->getSheetByName($worksheetReference[0]);
// echo 'New sheet name is '.$pSheet->getTitle().'<br />';
$pRange = $worksheetReference[1];
// echo 'Adjusted Range reference is '.$pRange.'<br />';
}
// Named range?
$namedRange = PHPExcel_NamedRange::resolveRange($pRange, $pSheet);
if (!is_null($namedRange)) {
$pSheet = $namedRange->getWorksheet();
//// echo 'Named Range '.$pRange.' (';
$pRange = $namedRange->getRange();
//// echo $pRange.') is in sheet '.$namedRange->getWorksheet()->getTitle().'<br />';
// if ($pSheet->getTitle() != $namedRange->getWorksheet()->getTitle()) {
// if (!$namedRange->getLocalOnly()) {
// $pSheet = $namedRange->getWorksheet();
// } else {
// return $returnValue;
// }
// }
} else {
return PHPExcel_Calculation_Functions::REF();
}
// Extract range
$aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($pRange);
if (count($aReferences) == 1) {
list($currentCol, $currentRow) = PHPExcel_Cell::coordinateFromString($aReferences[0]);
if ($pSheet->cellExists($aReferences[0])) {
$returnValue[$currentRow][$currentCol] = $pSheet->getCell($aReferences[0])->getCalculatedValue($resetLog);
} else {
$returnValue[$currentRow][$currentCol] = NULL;
}
} else {
// Extract cell data
foreach ($aReferences as $reference) {
// Extract range
list($currentCol, $currentRow) = PHPExcel_Cell::coordinateFromString($reference);
// echo 'NAMED RANGE: $currentCol='.$currentCol.' $currentRow='.$currentRow.'<br />';
if ($pSheet->cellExists($reference)) {
$returnValue[$currentRow][$currentCol] = $pSheet->getCell($reference)->getCalculatedValue($resetLog);
} else {
$returnValue[$currentRow][$currentCol] = NULL;
}
}
}
// print_r($returnValue);
// echo '<br />';
}
// Return
return $returnValue;
}示例5: _readHyperLink
//.........这里部分代码省略.........
// detect type of hyperlink (there are 4 types)
$hyperlinkType = null;
if ($isUNC) {
$hyperlinkType = 'UNC';
} else {
if (!$isFileLinkOrUrl) {
$hyperlinkType = 'workbook';
} else {
if (ord($recordData[$offset]) == 0x3) {
$hyperlinkType = 'local';
} else {
if (ord($recordData[$offset]) == 0xe0) {
$hyperlinkType = 'URL';
}
}
}
}
switch ($hyperlinkType) {
case 'URL':
// section 5.58.2: Hyperlink containing a URL
// e.g. http://example.org/index.php
// offset: var; size: 16; GUID of URL Moniker
$offset += 16;
// offset: var; size: 4; size (in bytes) of character array of the URL including trailing zero word
$us = $this->_GetInt4d($recordData, $offset);
$offset += 4;
// offset: var; size: $us; character array of the URL, no Unicode string header, always 16-bit characters, zero-terminated
$url = $this->_encodeUTF16(substr($recordData, $offset, $us - 2), false);
$url .= $hasText ? '#' : '';
$offset += $us;
break;
case 'local':
// section 5.58.3: Hyperlink to local file
// examples:
// mydoc.txt
// ../../somedoc.xls#Sheet!A1
// offset: var; size: 16; GUI of File Moniker
$offset += 16;
// offset: var; size: 2; directory up-level count.
$upLevelCount = $this->_GetInt2d($recordData, $offset);
$offset += 2;
// offset: var; size: 4; character count of the shortened file path and name, including trailing zero word
$sl = $this->_GetInt4d($recordData, $offset);
$offset += 4;
// offset: var; size: sl; character array of the shortened file path and name in 8.3-DOS-format (compressed Unicode string)
$shortenedFilePath = substr($recordData, $offset, $sl);
$shortenedFilePath = $this->_encodeUTF16($shortenedFilePath, true);
$shortenedFilePath = substr($shortenedFilePath, 0, -1);
// remove trailing zero
$offset += $sl;
// offset: var; size: 24; unknown sequence
$offset += 24;
// extended file path
// offset: var; size: 4; size of the following file link field including string lenth mark
$sz = $this->_GetInt4d($recordData, $offset);
$offset += 4;
// only present if $sz > 0
if ($sz > 0) {
// offset: var; size: 4; size of the character array of the extended file path and name
$xl = $this->_GetInt4d($recordData, $offset);
$offset += 4;
// offset: var; size 2; unknown
$offset += 2;
// offset: var; size $xl; character array of the extended file path and name.
$extendedFilePath = substr($recordData, $offset, $xl);
$extendedFilePath = $this->_encodeUTF16($extendedFilePath, false);
$offset += $xl;
}
// construct the path
$url = str_repeat('..\\', $upLevelCount);
$url .= $sz > 0 ? $extendedFilePath : $shortenedFilePath;
// use extended path if available
$url .= $hasText ? '#' : '';
break;
case 'UNC':
// section 5.58.4: Hyperlink to a File with UNC (Universal Naming Convention) Path
// todo: implement
return;
case 'workbook':
// section 5.58.5: Hyperlink to the Current Workbook
// e.g. Sheet2!B1:C2, stored in text mark field
$url = 'sheet://';
break;
default:
return;
}
if ($hasText) {
// offset: var; size: 4; character count of text mark including trailing zero word
$tl = $this->_GetInt4d($recordData, $offset);
$offset += 4;
// offset: var; size: var; character array of the text mark without the # sign, no Unicode header, always 16-bit characters, zero-terminated
$text = $this->_encodeUTF16(substr($recordData, $offset, 2 * ($tl - 1)), false);
$url .= $text;
}
// apply the hyperlink to all the relevant cells
foreach (PHPExcel_Cell::extractAllCellReferencesInRange($cellRange) as $coordinate) {
$this->_phpSheet->getCell($coordinate)->getHyperLink()->setUrl($url);
}
}
}示例6: extractRange
/**
* Extract range values
*
* @param string $pRange String based range representation
* @param PHPExcel_Worksheet $pSheet Worksheet
* @return mixed Array of values in range if range contains more than one element. Otherwise, a single value is returned.
* @throws Exception
*/
public function extractRange($pRange = 'A1', PHPExcel_Worksheet $pSheet = null)
{
// Return value
$returnValue = array();
// Worksheet given?
if (!is_null($pSheet)) {
// Worksheet reference?
if (strpos($pRange, '!') !== false) {
$worksheetReference = PHPExcel_Worksheet::extractSheetTitle($pRange, true);
$pSheet = $pSheet->getParent()->getSheetByName($worksheetReference[0]);
$pRange = $worksheetReference[1];
}
// Named range?
$namedRange = PHPExcel_NamedRange::resolveRange($pRange, $pSheet);
if (!is_null($namedRange)) {
$pRange = $namedRange->getRange();
if ($pSheet->getHashCode() != $namedRange->getWorksheet()->getHashCode()) {
if (!$namedRange->getLocalOnly()) {
$pSheet = $namedRange->getWorksheet();
} else {
return '';
}
}
}
// Extract range
$aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($pRange);
if (count($aReferences) == 1) {
return $pSheet->getCell($aReferences[0])->getCalculatedValue();
}
// Extract cell data
foreach ($aReferences as $reference) {
// Extract range
list($currentCol, $currentRow) = PHPExcel_Cell::coordinateFromString($reference);
$returnValue[$currentCol][$currentRow] = $pSheet->getCell($reference)->getCalculatedValue();
}
}
// Return
return $returnValue;
}示例7: load
//.........这里部分代码省略.........
if ($c["s"] && !$this->_readDataOnly) {
if (isset($styles[intval($c["s"])])) {
$this->_readStyle($docSheet->getStyle($r), $styles[intval($c["s"])]);
}
if (PHPExcel_Shared_Date::isDateTimeFormat($docSheet->getStyle($r)->getNumberFormat())) {
if (preg_match("/^([0-9.,-]+)\$/", $value)) {
$docSheet->setCellValue($r, PHPExcel_Shared_Date::ExcelToPHP($value));
}
}
}
}
}
$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"]);
$objConditional->setCondition((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"]));示例8: importExcel
/**
* @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);
}示例9: setConditionalStyles
/**
* Set Conditional Styles. Only used on supervisor.
*
* @param PHPExcel_Style_Conditional[] $pValue Array of condtional styles
* @return PHPExcel_Style
*/
public function setConditionalStyles($pValue = null)
{
if (is_array($pValue)) {
foreach (PHPExcel_Cell::extractAllCellReferencesInRange($this->getXSelectedCells()) as $cellReference) {
$this->getActiveSheet()->setConditionalStyles($cellReference, $pValue);
}
}
return $this;
}示例10: _processDomElement
//.........这里部分代码省略.........
// echo 'END OF LIST ENTRY:' , '<br />';
} else {
if ($cellContent > '') {
$this->_flushCell($sheet, $column, $row, $cellContent);
}
++$row;
// echo 'LIST ENTRY: ' , '<br />';
$this->_processDomElement($child, $sheet, $row, $column, $cellContent);
// echo 'END OF LIST ENTRY:' , '<br />';
$this->_flushCell($sheet, $column, $row, $cellContent);
$column = 'A';
}
break;
case 'table':
$this->_flushCell($sheet, $column, $row, $cellContent);
$column = $this->_setTableStartColumn($column);
// echo 'START OF TABLE LEVEL ' , $this->_tableLevel , '<br />';
if ($this->_tableLevel > 1) {
--$row;
}
$this->_processDomElement($child, $sheet, $row, $column, $cellContent);
// echo 'END OF TABLE LEVEL ' , $this->_tableLevel , '<br />';
$column = $this->_releaseTableStartColumn();
if ($this->_tableLevel > 1) {
++$column;
} else {
++$row;
}
break;
case 'thead':
case 'tbody':
$this->_processDomElement($child, $sheet, $row, $column, $cellContent);
break;
case 'tr':
$column = $this->_getTableStartColumn();
$cellContent = '';
// echo 'START OF TABLE ' , $this->_tableLevel , ' ROW<br />';
$this->_processDomElement($child, $sheet, $row, $column, $cellContent);
++$row;
// echo 'END OF TABLE ' , $this->_tableLevel , ' ROW<br />';
break;
case 'th':
case 'td':
// echo 'START OF TABLE ' , $this->_tableLevel , ' CELL<br />';
$this->_processDomElement($child, $sheet, $row, $column, $cellContent);
// echo 'END OF TABLE ' , $this->_tableLevel , ' CELL<br />';
while (isset($this->rowspan[$column . $row])) {
++$column;
}
$this->_flushCell($sheet, $column, $row, $cellContent);
// if (isset($attributeArray['style']) && !empty($attributeArray['style'])) {
// $styleAry = $this->getPhpExcelStyleArray($attributeArray['style']);
//
// if (!empty($styleAry)) {
// $sheet->getStyle($column . $row)->applyFromArray($styleAry);
// }
// }
if (isset($attributeArray['rowspan']) && isset($attributeArray['colspan'])) {
//create merging rowspan and colspan
$columnTo = $column;
for ($i = 0; $i < $attributeArray['colspan'] - 1; $i++) {
++$columnTo;
}
$range = $column . $row . ':' . $columnTo . ($row + $attributeArray['rowspan'] - 1);
foreach (\PHPExcel_Cell::extractAllCellReferencesInRange($range) as $value) {
$this->rowspan[$value] = true;
}
$sheet->mergeCells($range);
$column = $columnTo;
} elseif (isset($attributeArray['rowspan'])) {
//create merging rowspan
$range = $column . $row . ':' . $column . ($row + $attributeArray['rowspan'] - 1);
foreach (\PHPExcel_Cell::extractAllCellReferencesInRange($range) as $value) {
$this->rowspan[$value] = true;
}
$sheet->mergeCells($range);
} elseif (isset($attributeArray['colspan'])) {
//create merging colspan
$columnTo = $column;
for ($i = 0; $i < $attributeArray['colspan'] - 1; $i++) {
++$columnTo;
}
$sheet->mergeCells($column . $row . ':' . $columnTo . $row);
$column = $columnTo;
}
++$column;
break;
case 'body':
$row = 1;
$column = 'A';
$content = '';
$this->_tableLevel = 0;
$this->_processDomElement($child, $sheet, $row, $column, $cellContent);
break;
default:
$this->_processDomElement($child, $sheet, $row, $column, $cellContent);
}
}
}
}示例11: load
//.........这里部分代码省略.........
if ($c["s"] && !$this->_readDataOnly) {
if (isset($styles[intval($c["s"])])) {
$this->_readStyle($docSheet->getStyle($r), $styles[intval($c["s"])]);
}
if (PHPExcel_Shared_Date::isDateTimeFormat($docSheet->getStyle($r)->getNumberFormat())) {
if (preg_match("/^([0-9.,-]+)\$/", $value)) {
$docSheet->setCellValue($r, PHPExcel_Shared_Date::ExcelToPHP($value));
}
}
}
}
}
$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"]);
$objConditional->setCondition((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"]));示例12: mergeCells
/**
* Set merge on a cell range
*
* @param string $pRange Cell range (e.g. A1:E1)
* @throws Exception
* @return PHPExcel_Worksheet
*/
public function mergeCells($pRange = 'A1:A1')
{
// Uppercase coordinate
$pRange = strtoupper($pRange);
if (strpos($pRange, ':') !== false) {
$this->_mergeCells[$pRange] = $pRange;
// make sure cells are created
$aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($pRange);
foreach ($aReferences as $reference) {
$this->getCell($reference);
}
} else {
throw new Exception('Merge must be set on a range of cells.');
}
return $this;
}示例13: extractRange
/**
* Extract range values
*
* @param string $pRange String based range representation
* @param PHPExcel_Worksheet $pSheet Worksheet
* @return array Array of values in range
* @throws Exception
*/
public function extractRange($pRange = 'A1', PHPExcel_Worksheet $pSheet = null)
{
// Return value
$returnValue = array();
// Worksheet given?
if (!is_null($pSheet)) {
// Extract range
$aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($pRange);
// Extract cell data
foreach ($aReferences as $reference) {
// Extract range
$currentCol = 0;
$currentRow = 0;
list($currentCol, $currentRow) = PHPExcel_Cell::coordinateFromString($reference);
$returnValue[$currentCol][$currentRow] = $pSheet->getCell($reference)->getCalculatedValue();
}
}
// Return
return $returnValue;
}示例14: getMerged
function getMerged($address, $mergedCells)
{
if ($mergedCells) {
$address = strtoupper($address);
foreach ($mergedCells as $mergedRange) {
if (strpos($mergedRange, ':') !== false) {
// get the cells in the range
$aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($mergedRange);
if ($aReferences) {
foreach ($aReferences as $aCell) {
if ($aCell == $address) {
return prepareMergeRange($mergedRange);
}
}
}
}
}
}
return [];
}示例15: mergeCells
/**
* Set merge on a cell range
*
* @param string $pRange Cell range (e.g. A1:E1)
* @throws PHPExcel_Exception
* @return PHPExcel_Worksheet
*/
public function mergeCells($pRange = 'A1:A1')
{
// Uppercase coordinate
$pRange = strtoupper($pRange);
if (strpos($pRange, ':') !== false) {
$this->_mergeCells[$pRange] = $pRange;
// make sure cells are created
// get the cells in the range
$aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($pRange);
// create upper left cell if it does not already exist
$upperLeft = $aReferences[0];
if (!$this->cellExists($upperLeft)) {
$this->getCell($upperLeft)->setValueExplicit(null, PHPExcel_Cell_DataType::TYPE_NULL);
}
// create or blank out the rest of the cells in the range
$count = count($aReferences);
for ($i = 1; $i < $count; $i++) {
$this->getCell($aReferences[$i])->setValueExplicit(null, PHPExcel_Cell_DataType::TYPE_NULL);
}
} else {
throw new PHPExcel_Exception('Merge must be set on a range of cells.');
}
return $this;
}