本文整理汇总了PHP中PHPExcel_Cell::coordinateFromString方法的典型用法代码示例。如果您正苦于以下问题：PHP PHPExcel_Cell::coordinateFromString方法的具体用法？PHP PHPExcel_Cell::coordinateFromString怎么用？PHP PHPExcel_Cell::coordinateFromString使用的例子？那么, 这里精选的方法代码示例或许可以为您提供帮助。您也可以进一步了解该方法所在类PHPExcel_Cell的用法示例。
在下文中一共展示了PHPExcel_Cell::coordinateFromString方法的15个代码示例，这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞，您的评价将有助于系统推荐出更棒的PHP代码示例。
示例1: load
//.........这里部分代码省略.........
$docSheet->getPageSetup()->setHorizontalCentered(true);
}
if ($xmlSheet->printOptions['verticalCentered']) {
$docSheet->getPageSetup()->setVerticalCentered(true);
}
}
if ($xmlSheet && $xmlSheet->sheetData && $xmlSheet->sheetData->row) {
foreach ($xmlSheet->sheetData->row as $row) {
if ($row["ht"] && !$this->_readDataOnly) {
$docSheet->getRowDimension(intval($row["r"]))->setRowHeight(floatval($row["ht"]));
}
if ($row["hidden"] && !$this->_readDataOnly) {
$docSheet->getRowDimension(intval($row["r"]))->setVisible(false);
}
if ($row["collapsed"]) {
$docSheet->getRowDimension(intval($row["r"]))->setCollapsed(true);
}
if ($row["outlineLevel"] > 0) {
$docSheet->getRowDimension(intval($row["r"]))->setOutlineLevel(intval($row["outlineLevel"]));
}
if ($row["s"] && !$this->_readDataOnly) {
$docSheet->getRowDimension(intval($row["r"]))->setXfIndex(intval($row["s"]));
}
foreach ($row->c as $c) {
$r = (string) $c["r"];
$cellDataType = (string) $c["t"];
$value = null;
$calculatedValue = null;
// Read cell?
if (!is_null($this->getReadFilter())) {
$coordinates = PHPExcel_Cell::coordinateFromString($r);
if (!$this->getReadFilter()->readCell($coordinates[0], $coordinates[1], $docSheet->getTitle())) {
continue;
}
}
// echo '<b>Reading cell '.$coordinates[0].$coordinates[1].'</b><br />';
// print_r($c);
// echo '<br />';
// echo 'Cell Data Type is '.$cellDataType.': ';
//
// Read cell!
switch ($cellDataType) {
case "s":
// echo 'String<br />';
if ((string)$c->v != '') {
$value = $sharedStrings[intval($c->v)];
if ($value instanceof PHPExcel_RichText) {
$value = clone $value;
}
} else {
$value = '';
}
break;
case "b":
// echo 'Boolean<br />';
if (!isset($c->f)) {
$value = $this->_castToBool($c);
} else {
// Formula示例2: coord_E2L
public function coord_E2L($coord)
{
$Xcoord=PHPExcel_Cell::coordinateFromString($coord);
return array(
PHPExcel_Cell::columnIndexFromString($Xcoord[0])-1,
$Xcoord[1]-1
);
}示例3: getCell
/**
* @param string|int $Column Name or Index
* @param null|int $Row Index
*
* @return Cell
*/
public function getCell($Column, $Row = null)
{
if (preg_match('![a-z]!is', $Column)) {
$Coordinate = \PHPExcel_Cell::coordinateFromString($Column);
$Column = \PHPExcel_Cell::columnIndexFromString($Coordinate[0]) - 1;
$Row = $Coordinate[1];
} else {
$Row += 1;
}
return new Cell($Column, $Row);
}示例4: load
//.........这里部分代码省略.........
$docSheet->setPrintGridlines(TRUE);
}
if (self::boolean((string) $xmlSheet->printOptions['horizontalCentered'])) {
$docSheet->getPageSetup()->setHorizontalCentered(TRUE);
}
if (self::boolean((string) $xmlSheet->printOptions['verticalCentered'])) {
$docSheet->getPageSetup()->setVerticalCentered(TRUE);
}
}
if ($xmlSheet && $xmlSheet->sheetData && $xmlSheet->sheetData->row) {
foreach ($xmlSheet->sheetData->row as $row) {
if ($row["ht"] && !$this->_readDataOnly) {
$docSheet->getRowDimension(intval($row["r"]))->setRowHeight(floatval($row["ht"]));
}
if (self::boolean($row["hidden"]) && !$this->_readDataOnly) {
$docSheet->getRowDimension(intval($row["r"]))->setVisible(FALSE);
}
if (self::boolean($row["collapsed"])) {
$docSheet->getRowDimension(intval($row["r"]))->setCollapsed(TRUE);
}
if ($row["outlineLevel"] > 0) {
$docSheet->getRowDimension(intval($row["r"]))->setOutlineLevel(intval($row["outlineLevel"]));
}
if ($row["s"] && !$this->_readDataOnly) {
$docSheet->getRowDimension(intval($row["r"]))->setXfIndex(intval($row["s"]));
}
foreach ($row->c as $c) {
$r = (string) $c["r"];
$cellDataType = (string) $c["t"];
$value = null;
$calculatedValue = null;
// Read cell?
if ($this->getReadFilter() !== NULL) {
$coordinates = PHPExcel_Cell::coordinateFromString($r);
if (!$this->getReadFilter()->readCell($coordinates[0], $coordinates[1], $docSheet->getTitle())) {
continue;
}
}
// echo '<b>Reading cell '.$coordinates[0].$coordinates[1].'</b><br />';
// print_r($c);
// echo '<br />';
// echo 'Cell Data Type is '.$cellDataType.': ';
//
// Read cell!
switch ($cellDataType) {
case "s":
// echo 'String<br />';
if ((string) $c->v != '') {
$value = $sharedStrings[intval($c->v)];
if ($value instanceof PHPExcel_RichText) {
$value = clone $value;
}
} else {
$value = '';
}
break;
case "b":
// echo 'Boolean<br />';
if (!isset($c->f)) {
$value = self::_castToBool($c);
} else {
// Formula
$this->_castToFormula($c, $r, $cellDataType, $value, $calculatedValue, $sharedFormulas, '_castToBool');
if (isset($c->f['t'])) {
$att = array();
$att = $c->f;示例5: save
/**
* Save PHPExcel to file
*
* @param string $pFileName
* @throws Exception
*/
public function save($pFilename = null)
{
// Fetch sheet
$sheet = $this->_phpExcel->getSheet($this->_sheetIndex);
// Open file
$fileHandle = fopen($pFilename, 'w');
if ($fileHandle === false) {
throw new Exception("Could not open file {$pFilename} for writing.");
}
// Get cell collection
$cellCollection = $sheet->getCellCollection();
// Write headers
$this->_writeHTMLHeader($fileHandle);
$this->_writeStyles($fileHandle, $sheet);
$this->_writeTableHeader($fileHandle);
// Get worksheet dimension
$dimension = explode(':', $sheet->calculateWorksheetDimension());
$dimension[0] = PHPExcel_Cell::coordinateFromString($dimension[0]);
$dimension[0][0] = PHPExcel_Cell::columnIndexFromString($dimension[0][0]) - 1;
$dimension[1] = PHPExcel_Cell::coordinateFromString($dimension[1]);
$dimension[1][0] = PHPExcel_Cell::columnIndexFromString($dimension[1][0]) - 1;
// Loop trough cells
$rowData = null;
for ($row = $dimension[0][1]; $row <= $dimension[1][1]; $row++) {
// Start a new row
$rowData = array();
// Loop trough columns
for ($column = $dimension[0][0]; $column <= $dimension[1][0]; $column++) {
// Cell exists?
if ($sheet->cellExistsByColumnAndRow($column, $row)) {
$rowData[$column] = $sheet->getCellByColumnAndRow($column, $row);
} else {
$rowData[$column] = '';
}
}
// Write row
$this->_writeRow($fileHandle, $sheet, $rowData, $row - 1);
}
// Write footers
$this->_writeTableFooter($fileHandle);
$this->_writeHTMLFooter($fileHandle);
// Close file
fclose($fileHandle);
}示例6: _writeCFHeader
/**
* Write CFHeader record
*/
private function _writeCFHeader()
{
$record = 0x1b0;
// Record identifier
$length = 0x16;
// Bytes to follow
$numColumnMin = null;
$numColumnMax = null;
$numRowMin = null;
$numRowMax = null;
$arrConditional = array();
foreach ($this->_phpSheet->getConditionalStylesCollection() as $cellCoordinate => $conditionalStyles) {
foreach ($conditionalStyles as $conditional) {
if ($conditional->getConditionType() == PHPExcel_Style_Conditional::CONDITION_EXPRESSION || $conditional->getConditionType() == PHPExcel_Style_Conditional::CONDITION_CELLIS) {
if (!in_array($conditional->getHashCode(), $arrConditional)) {
$arrConditional[] = $conditional->getHashCode();
}
// Cells
$arrCoord = PHPExcel_Cell::coordinateFromString($cellCoordinate);
if (!is_numeric($arrCoord[0])) {
$arrCoord[0] = PHPExcel_Cell::columnIndexFromString($arrCoord[0]);
}
if (is_null($numColumnMin) || $numColumnMin > $arrCoord[0]) {
$numColumnMin = $arrCoord[0];
}
if (is_null($numColumnMax) || $numColumnMax < $arrCoord[0]) {
$numColumnMax = $arrCoord[0];
}
if (is_null($numRowMin) || $numRowMin > $arrCoord[1]) {
$numRowMin = $arrCoord[1];
}
if (is_null($numRowMax) || $numRowMax < $arrCoord[1]) {
$numRowMax = $arrCoord[1];
}
}
}
}
$needRedraw = 1;
$cellRange = pack('vvvv', $numRowMin - 1, $numRowMax - 1, $numColumnMin - 1, $numColumnMax - 1);
$header = pack('vv', $record, $length);
$data = pack('vv', count($arrConditional), $needRedraw);
$data .= $cellRange;
$data .= pack('v', 0x1);
$data .= $cellRange;
$this->_append($header . $data);
}示例7: oneAnchor2twoAnchor
/**
* Convert 1-cell anchor coordinates to 2-cell anchor coordinates
* This function is ported from PEAR Spreadsheet_Writer_Excel with small modifications
*
* Calculate the vertices that define the position of the image as required by
* the OBJ record.
*
* +------------+------------+
* | A | B |
* +-----+------------+------------+
* | |(x1,y1) | |
* | 1 |(A1)._______|______ |
* | | | | |
* | | | | |
* +-----+----| BITMAP |-----+
* | | | | |
* | 2 | |______________. |
* | | | (B2)|
* | | | (x2,y2)|
* +---- +------------+------------+
*
* Example of a bitmap that covers some of the area from cell A1 to cell B2.
*
* Based on the width and height of the bitmap we need to calculate 8 vars:
* $col_start, $row_start, $col_end, $row_end, $x1, $y1, $x2, $y2.
* The width and height of the cells are also variable and have to be taken into
* account.
* The values of $col_start and $row_start are passed in from the calling
* function. The values of $col_end and $row_end are calculated by subtracting
* the width and height of the bitmap from the width and height of the
* underlying cells.
* The vertices are expressed as a percentage of the underlying cell width as
* follows (rhs values are in pixels):
*
* x1 = X / W *1024
* y1 = Y / H *256
* x2 = (X-1) / W *1024
* y2 = (Y-1) / H *256
*
* Where: X is distance from the left side of the underlying cell
* Y is distance from the top of the underlying cell
* W is the width of the cell
* H is the height of the cell
*
* @param PHPExcel_Worksheet $sheet
* @param string $coordinates E.g. 'A1'
* @param integer $offsetX Horizontal offset in pixels
* @param integer $offsetY Vertical offset in pixels
* @param integer $width Width in pixels
* @param integer $height Height in pixels
* @return array
*/
public static function oneAnchor2twoAnchor($sheet, $coordinates, $offsetX, $offsetY, $width, $height)
{
list($column, $row) = PHPExcel_Cell::coordinateFromString($coordinates);
$col_start = PHPExcel_Cell::columnIndexFromString($column) - 1;
$row_start = $row - 1;
$x1 = $offsetX;
$y1 = $offsetY;
// Initialise end cell to the same as the start cell
$col_end = $col_start;
// Col containing lower right corner of object
$row_end = $row_start;
// Row containing bottom right corner of object
// Zero the specified offset if greater than the cell dimensions
if ($x1 >= self::sizeCol($sheet, PHPExcel_Cell::stringFromColumnIndex($col_start))) {
$x1 = 0;
}
if ($y1 >= self::sizeRow($sheet, $row_start + 1)) {
$y1 = 0;
}
$width = $width + $x1 - 1;
$height = $height + $y1 - 1;
// Subtract the underlying cell widths to find the end cell of the image
while ($width >= self::sizeCol($sheet, PHPExcel_Cell::stringFromColumnIndex($col_end))) {
$width -= self::sizeCol($sheet, PHPExcel_Cell::stringFromColumnIndex($col_end));
++$col_end;
}
// Subtract the underlying cell heights to find the end cell of the image
while ($height >= self::sizeRow($sheet, $row_end + 1)) {
$height -= self::sizeRow($sheet, $row_end + 1);
++$row_end;
}
// Bitmap isn't allowed to start or finish in a hidden cell, i.e. a cell
// with zero height or width.
if (self::sizeCol($sheet, PHPExcel_Cell::stringFromColumnIndex($col_start)) == 0) {
return;
}
if (self::sizeCol($sheet, PHPExcel_Cell::stringFromColumnIndex($col_end)) == 0) {
return;
}
if (self::sizeRow($sheet, $row_start + 1) == 0) {
return;
}
if (self::sizeRow($sheet, $row_end + 1) == 0) {
return;
}
// Convert the pixel values to the percentage value expected by Excel
$x1 = $x1 / self::sizeCol($sheet, PHPExcel_Cell::stringFromColumnIndex($col_start)) * 1024;
$y1 = $y1 / self::sizeRow($sheet, $row_start + 1) * 256;
//.........这里部分代码省略.........
示例8: _updateSingleCellReference
/**
* Update single cell reference
*
* @param string $pCellReference Single cell reference
* @param int $pBefore Insert before this one
* @param int $pNumCols Number of columns to increment
* @param int $pNumRows Number of rows to increment
* @return string Updated cell reference
* @throws Exception
*/
private function _updateSingleCellReference($pCellReference = 'A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0)
{
if (strpos($pCellReference, ':') === false && strpos($pCellReference, ',') === false) {
// Get coordinates of $pBefore
$beforeColumn = 'A';
$beforeRow = 1;
list($beforeColumn, $beforeRow) = PHPExcel_Cell::coordinateFromString($pBefore);
// Get coordinates
$newColumn = 'A';
$newRow = 1;
list($newColumn, $newRow) = PHPExcel_Cell::coordinateFromString($pCellReference);
// Make sure the reference can be used
if ($newColumn == '' && $newRow == '') {
return $pCellReference;
}
// Verify which parts should be updated
$updateColumn = PHPExcel_Cell::columnIndexFromString($newColumn) >= PHPExcel_Cell::columnIndexFromString($beforeColumn) && strpos($newColumn, '$') === false && strpos($beforeColumn, '$') === false;
$updateRow = $newRow >= $beforeRow && strpos($newRow, '$') === false && strpos($beforeRow, '$') === false;
// Create new column reference
if ($updateColumn) {
$newColumn = PHPExcel_Cell::stringFromColumnIndex(PHPExcel_Cell::columnIndexFromString($newColumn) - 1 + $pNumCols);
}
// Create new row reference
if ($updateRow) {
$newRow = $newRow + $pNumRows;
}
// Return new reference
return $newColumn . $newRow;
} else {
throw new Exception("Only single cell references may be passed to this method.");
}
}示例9: _writeSheetData
/**
* Write SheetData
*
* @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
* @param PHPExcel_Worksheet $pSheet Worksheet
* @param string[] $pStringTable String table
* @throws PHPExcel_Writer_Exception
*/
private function _writeSheetData(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null, $pStringTable = null)
{
if (is_array($pStringTable)) {
// Flipped stringtable, for faster index searching
$aFlippedStringTable = $this->getParentWriter()->getWriterPart('stringtable')->flipStringTable($pStringTable);
// sheetData
$objWriter->startElement('sheetData');
// Get column count
$colCount = PHPExcel_Cell::columnIndexFromString($pSheet->getHighestColumn());
// Highest row number
$highestRow = $pSheet->getHighestRow();
// Loop through cells
$cellsByRow = array();
foreach ($pSheet->getCellCollection() as $cellID) {
$cellAddress = PHPExcel_Cell::coordinateFromString($cellID);
$cellsByRow[$cellAddress[1]][] = $cellID;
}
$currentRow = 0;
while ($currentRow++ < $highestRow) {
// Get row dimension
$rowDimension = $pSheet->getRowDimension($currentRow);
// Write current row?
$writeCurrentRow = isset($cellsByRow[$currentRow]) || $rowDimension->getRowHeight() >= 0 || $rowDimension->getVisible() == false || $rowDimension->getCollapsed() == true || $rowDimension->getOutlineLevel() > 0 || $rowDimension->getXfIndex() !== null;
if ($writeCurrentRow) {
// Start a new row
$objWriter->startElement('row');
$objWriter->writeAttribute('r', $currentRow);
$objWriter->writeAttribute('spans', '1:' . $colCount);
// Row dimensions
if ($rowDimension->getRowHeight() >= 0) {
$objWriter->writeAttribute('customHeight', '1');
$objWriter->writeAttribute('ht', PHPExcel_Shared_String::FormatNumber($rowDimension->getRowHeight()));
}
// Row visibility
if ($rowDimension->getVisible() == false) {
$objWriter->writeAttribute('hidden', 'true');
}
// Collapsed
if ($rowDimension->getCollapsed() == true) {
$objWriter->writeAttribute('collapsed', 'true');
}
// Outline level
if ($rowDimension->getOutlineLevel() > 0) {
$objWriter->writeAttribute('outlineLevel', $rowDimension->getOutlineLevel());
}
// Style
if ($rowDimension->getXfIndex() !== null) {
$objWriter->writeAttribute('s', $rowDimension->getXfIndex());
$objWriter->writeAttribute('customFormat', '1');
}
// Write cells
if (isset($cellsByRow[$currentRow])) {
foreach ($cellsByRow[$currentRow] as $cellAddress) {
// Write cell
$this->_writeCell($objWriter, $pSheet, $cellAddress, $pStringTable, $aFlippedStringTable);
}
}
// End row
$objWriter->endElement();
}
}
$objWriter->endElement();
} else {
throw new PHPExcel_Writer_Exception("Invalid parameters passed.");
}
}示例10: close
//.........这里部分代码省略.........
$this->_spOffsets = $spOffsets;
break;
case 'PHPExcel_Shared_Escher_DgContainer_SpgrContainer_SpContainer':
// initialize
$data = '';
// build the data
// write group shape record, if necessary?
if ($this->_object->getSpgr()) {
$recVer = 0x1;
$recInstance = 0x0;
$recType = 0xf009;
$length = 0x10;
$recVerInstance = $recVer;
$recVerInstance |= $recInstance << 4;
$header = pack('vvV', $recVerInstance, $recType, $length);
$data .= $header . pack('VVVV', 0, 0, 0, 0);
}
// write the shape record
$recVer = 0x2;
$recInstance = $this->_object->getSpType();
// shape type
$recType = 0xf00a;
$length = 0x8;
$recVerInstance = $recVer;
$recVerInstance |= $recInstance << 4;
$header = pack('vvV', $recVerInstance, $recType, $length);
$data .= $header . pack('VV', $this->_object->getSpId(), $this->_object->getSpgr() ? 0x5 : 0xa000);
// the options
if ($this->_object->getOPTCollection()) {
$optData = '';
$recVer = 0x3;
$recInstance = count($this->_object->getOPTCollection());
$recType = 0xf00b;
foreach ($this->_object->getOPTCollection() as $property => $value) {
$optData .= pack('vV', $property, $value);
}
$length = strlen($optData);
$recVerInstance = $recVer;
$recVerInstance |= $recInstance << 4;
$header = pack('vvV', $recVerInstance, $recType, $length);
$data .= $header . $optData;
}
// the client anchor
if ($this->_object->getStartCoordinates()) {
$clientAnchorData = '';
$recVer = 0x0;
// TODO: check
$recInstance = 0x0;
// TODO: check
$recType = 0xf010;
// start coordinates
list($column, $row) = PHPExcel_Cell::coordinateFromString($this->_object->getStartCoordinates());
$c1 = PHPExcel_Cell::columnIndexFromString($column) - 1;
$r1 = $row - 1;
// start offsetX
$startOffsetX = $this->_object->getStartOffsetX();
// start offsetY
$startOffsetY = $this->_object->getStartOffsetY();
// end coordinates
list($column, $row) = PHPExcel_Cell::coordinateFromString($this->_object->getEndCoordinates());
$c2 = PHPExcel_Cell::columnIndexFromString($column) - 1;
$r2 = $row - 1;
// end offsetX
$endOffsetX = $this->_object->getEndOffsetX();
// end offsetY
$endOffsetY = $this->_object->getEndOffsetY();
$clientAnchorData = pack('vvvvvvvvv', 0x0, $c1, $startOffsetX, $r1, $startOffsetY, $c2, $endOffsetX, $r2, $endOffsetY);
$length = strlen($clientAnchorData);
$recVerInstance = $recVer;
$recVerInstance |= $recInstance << 4;
$header = pack('vvV', $recVerInstance, $recType, $length);
$data .= $header . $clientAnchorData;
}
// the client data, just empty for now
if (!$this->_object->getSpgr()) {
$clientDataData = '';
$recVer = 0x0;
// TODO: check
$recInstance = 0x0;
// TODO: check
$recType = 0xf011;
$length = strlen($clientDataData);
$recVerInstance = $recVer;
$recVerInstance |= $recInstance << 4;
$header = pack('vvV', $recVerInstance, $recType, $length);
$data .= $header . $clientDataData;
}
// write the record
$recVer = 0xf;
$recInstance = 0x0;
$recType = 0xf004;
$length = strlen($data);
$recVerInstance = $recVer;
$recVerInstance |= $recInstance << 4;
$header = pack('vvV', $recVerInstance, $recType, $length);
$this->_data = $header . $data;
break;
}
return $this->_data;
}示例11: 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;
}示例12: _readBIFF8CellRangeAddressB
/**
* Reads a cell range address in BIFF8 for shared formulas. Uses positive and negative values for row and column
* to indicate offsets from a base cell
* section 3.3.4
*
* @param string $subData
* @param string $baseCell Base cell
* @return string Cell range address
*/
private function _readBIFF8CellRangeAddressB($subData, $baseCell = 'A1')
{
list($baseCol, $baseRow) = PHPExcel_Cell::coordinateFromString($baseCell);
$baseCol = PHPExcel_Cell::columnIndexFromString($baseCol) - 1;
// TODO: if cell range is just a single cell, should this funciton
// not just return e.g. 'A1' and not 'A1:A1' ?
// offset: 0; size: 2; relative index to first row (0... 65535) should be treated as offset (-32768... 32767)
$frIndex = $this->_GetInt2d($subData, 0);
$frIndex = $frIndex <= 32767 ? $frIndex : $frIndex - 65536;
$fr = $baseRow + $frIndex;
// offset: 2; size: 2; relative index to first row (0... 65535) should be treated as offset (-32768... 32767)
$lrIndex = $this->_GetInt2d($subData, 2);
$lrIndex = $lrIndex <= 32767 ? $lrIndex : $lrIndex - 65536;
$lr = $baseRow + $lrIndex;
// offset: 4; size: 2; relative index to first column
// bit: 7-0; mask 0x00FF; column index
$fcIndex = 0xff & $this->_GetInt2d($subData, 4);
$fcIndex = $fcIndex <= 127 ? $fcIndex : $fcIndex - 256;
$fc = PHPExcel_Cell::stringFromColumnIndex($baseCol + $fcIndex);
// offset: 6; size: 2; relative index to first column
// bit: 7-0; mask 0x00FF; column index
$lcIndex = 0xff & $this->_GetInt2d($subData, 6);
$lcIndex = $lcIndex <= 127 ? $lcIndex : $lcIndex - 256;
$lc = PHPExcel_Cell::stringFromColumnIndex($baseCol + $lcIndex);
return "{$fc}{$fr}:{$lc}{$lr}";
}示例13: rangeBoundaries
/**
* Calculate range boundaries
*
* @param string $pRange Cell range (e.g. A1:A1)
* @return array Range coordinates (Start Cell, End Cell) where Start Cell and End Cell are arrays (Column Number, Row Number)
*/
public static function rangeBoundaries($pRange = 'A1:A1')
{
// Uppercase coordinate
$pRange = strtoupper($pRange);
// Extract range
if (strpos($pRange, ':') === false) {
$rangeA = $rangeB = $pRange;
} else {
list($rangeA, $rangeB) = explode(':', $pRange);
}
// Calculate range outer borders
$rangeStart = PHPExcel_Cell::coordinateFromString($rangeA);
$rangeEnd = PHPExcel_Cell::coordinateFromString($rangeB);
// Translate column into index
$rangeStart[0] = PHPExcel_Cell::columnIndexFromString($rangeStart[0]);
$rangeEnd[0] = PHPExcel_Cell::columnIndexFromString($rangeEnd[0]);
return array($rangeStart, $rangeEnd);
}示例14: 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;
}示例15: _updateSingleCellReference
/**
* Update single cell reference
*
* @param string $pCellReference Single cell reference
* @param int $pBefore Insert before this one
* @param int $pNumCols Number of columns to increment
* @param int $pNumRows Number of rows to increment
* @return string Updated cell reference
* @throws Exception
*/
private function _updateSingleCellReference($pCellReference = 'A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0)
{
if (strpos($pCellReference, ':') === false) {
// Get coordinates of $pBefore
$beforeColumn = 'A';
$beforeRow = 1;
list($beforeColumn, $beforeRow) = PHPExcel_Cell::coordinateFromString($pBefore);
// Get coordinates
$newColumn = 'A';
$newRow = 1;
list($newColumn, $newRow) = PHPExcel_Cell::coordinateFromString($pCellReference);
// Should this cell be updated?
if (PHPExcel_Cell::columnIndexFromString($newColumn) >= PHPExcel_Cell::columnIndexFromString($beforeColumn) && $newRow >= $beforeRow) {
// Create new column reference
$newColumn = PHPExcel_Cell::stringFromColumnIndex(PHPExcel_Cell::columnIndexFromString($newColumn) - 1 + $pNumCols);
// Create new row reference
$newRow = $newRow + $pNumRows;
// Return new reference
return $newColumn . $newRow;
} else {
return $pCellReference;
}
} else {
throw new Exception("Only single cell references may be passed to this method.");
}
}