工作簿和工作表的操作

概述和快速入門向導

使用單元格

• 利用坐標給單元格賦值
  // 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數(shù)據(jù)類型
  略
• 賦一個日期或者時間值
  略
• 賦一個開頭是0的數(shù)字的值
  略
• 從數(shù)組賦值給單元格
  $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();
  還有兩種獲得值的函數(shù)，可參見相關用法：
  // 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();
• 將一片單元格的數(shù)據(jù)讀取到一個數(shù)組中
  $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()方法將返回整個表格的數(shù)據(jù)
  rangeToArray()方法將返回一片范圍的表格的數(shù)據(jù)
  namedRangeToArray()方法將根據(jù)設定好的named range返回單元格數(shù)據(jù)

遍歷單元格

• 通過迭代器來遍歷單元格數(shù)據(jù)
  `$objReader = PHPExcel_IOFactory::createReader('Excel2007');
  $objReader->setReadDataOnly(TRUE);
  $objPHPExcel = $objReader->load("test.xlsx");

$objWorksheet = $objPHPExcel->getActiveSheet();

echo '<table>' . PHP_EOL;
foreach ($objWorksheet->getRowIterator() as $row) {
echo '<tr>' . 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 '<td>' . $cell->getValue() . '</td>' . PHP_EOL;
}
echo '</tr>' . PHP_EOL;
}
echo '</table>' . PHP_EOL;`

• 通過索引來遍歷單元格數(shù)據(jù)
  `$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 '<table>' . "\n";
for ($row = 1; $row <= $highestRow; ++$row) {
echo '<tr>' . PHP_EOL;
for ($col = 0; $col <= $highestColumnIndex; ++$col) {
echo '<td>' . $objWorksheet->getCellByColumnAndRow($col, $row) ->getValue() . '</td>' . PHP_EOL;
}
echo '</tr>' . PHP_EOL;
}
echo '</table>' . 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 '<table>' . "\n";
for ($row = 1; $row <= $highestRow; ++$row) {
echo '<tr>' . PHP_EOL;
for ($col = 'A'; $col != $highestColumn; ++$col) {
echo '<td>' . $objWorksheet->getCell($col . $row) ->getValue() . '</td>' . PHP_EOL;
}
echo '</tr>' . PHP_EOL;
}
echo '</table>' . PHP_EOL;`

• Using value binders to facilitate data entry
  較復雜，用不到，略