php cell,PHPExcel开发文档（二） --关于单元格使用的操作

工作簿和工作表的操作

概述和快速入门向导

使用单元格

利用坐标给单元格赋值

// Set cell A1 with a string value $objPHPExcel->getActiveSheet()->setCellValue('A1', 'PHPExcel'); // Set cell A2 with a numeric value $objPHPExcel->getActiveSheet()->setCellValue('A2', 12345.6789); // Set cell A3 with a boolean value $objPHPExcel->getActiveSheet()->setCellValue('A3', TRUE); // Set cell A4 with a formula $objPHPExcel->getActiveSheet()->setCellValue( 'A4', '=IF(A3, CONCATENATE(A1, " ", A2), CONCATENATE(A2, " ", A1))');

或者

$objPHPExcel->getActiveSheet() ->getCell('B8') ->setValue('Some value');

excel数据类型

略

赋一个日期或者时间值

略

赋一个开头是0的数字的值

略

从数组赋值给单元格

$arrayData = array( array(NULL, 2010, 2011, 2012), array('Q1', 12, 15, 21), array('Q2', 56, 73, 86), array('Q3', 52, 61, 69), array('Q4', 30, 32, 0), ); $objPHPExcel->getActiveSheet() ->fromArray( $arrayData, // The data to set NULL, // Array values with this value will not be set 'C3' // Top left coordinate of the worksheet range where // we want to set these values (default is A1) );

通过坐标读取单元格的值

// Get the value fom cell A1 $cellValue = $objPHPExcel->getActiveSheet()->getCell('A1') ->getValue();

还有两种获得值的函数，可参见相关用法：

// Get the value fom cell A4 $cellValue = $objPHPExcel->getActiveSheet()->getCell('A4') ->getCalculatedValue();

// Get the value fom cell A6 $cellValue = $objPHPExcel->getActiveSheet()->getCell('A6') ->getFormattedValue();

通过行列值来给单元格赋值

// Set cell B5 with a string value $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 5, 'PHPExcel');

通过行列值来获取单元格的值

// Get the value fom cell B5 $cellValue = $objPHPExcel->getActiveSheet()->getCellByColumnAndRow(1, 5) ->getValue();

或者

// Get the value fom cell A4 $cellValue = $objPHPExcel->getActiveSheet()->getCellByColumnAndRow(0, 4) ->getCalculatedValue();

将一片单元格的数据读取到一个数组中

$dataArray = $objPHPExcel->getActiveSheet() ->rangeToArray( 'C3:E5', // The worksheet range that we want to retrieve NULL, // Value that should be returned for empty cells TRUE, // Should formulas be calculated (the equivalent of getCalculatedValue() for each cell) TRUE, // Should values be formatted (the equivalent of getFormattedValue() for each cell) TRUE // Should the array be indexed by cell row and cell column );

还有

toArray()方法将返回整个表格的数据

rangeToArray()方法将返回一片范围的表格的数据

namedRangeToArray()方法将根据设定好的named range返回单元格数据

遍历单元格

通过迭代器来遍历单元格数据

`$objReader = PHPExcel_IOFactory::createReader('Excel2007');

$objReader->setReadDataOnly(TRUE);

$objPHPExcel = $objReader->load("test.xlsx");

$objWorksheet = $objPHPExcel->getActiveSheet();

echo '

foreach ($objWorksheet->getRowIterator() as $row) {

echo '

' . PHP_EOL;

$cellIterator = $row->getCellIterator();

$cellIterator->setIterateOnlyExistingCells(FALSE); // This loops through all cells,

// even if a cell value is not set.

// By default, only cells that have a value

// set will be iterated.

foreach ($cellIterator as $cell) {

echo '

' . $cell->getValue() . '' . PHP_EOL;

}

echo '

' . PHP_EOL;

}

echo '

' . PHP_EOL;`

通过索引来遍历单元格数据

`$objReader = PHPExcel_IOFactory::createReader('Excel2007');

$objReader->setReadDataOnly(TRUE);

$objPHPExcel = $objReader->load("test.xlsx");

$objWorksheet = $objPHPExcel->getActiveSheet();

// Get the highest row and column numbers referenced in the worksheet

$highestRow = $objWorksheet->getHighestRow(); // e.g. 10

$highestColumn = $objWorksheet->getHighestColumn(); // e.g 'F'

$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); // e.g. 5

echo '

for ($row = 1; $row <= $highestRow; ++$row) {

echo '

' . PHP_EOL;

for ($col = 0; $col <= $highestColumnIndex; ++$col) {

echo '

' . $objWorksheet->getCellByColumnAndRow($col, $row) ->getValue() . '' . PHP_EOL;

}

echo '

' . PHP_EOL;

}

echo '

' . PHP_EOL;也可以使用下面的风格$objReader = PHPExcel_IOFactory::createReader('Excel2007');

$objReader->setReadDataOnly(TRUE);

$objPHPExcel = $objReader->load("test.xlsx");

$objWorksheet = $objPHPExcel->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'

// Increment the highest column letter

$highestColumn++;

echo '

for ($row = 1; $row <= $highestRow; ++$row) {

echo '

' . PHP_EOL;

for ($col = 'A'; $col != $highestColumn; ++$col) {

echo '

' . $objWorksheet->getCell($col . $row) ->getValue() . '' . PHP_EOL;

}

echo '

' . PHP_EOL;

}

echo '

' . PHP_EOL;`

Using value binders to facilitate data entry

较复杂，用不到，略