本文整理汇总了PHP中PHPExcel_Worksheet::setCellValue方法的典型用法代码示例。如果您正苦于以下问题：PHP PHPExcel_Worksheet::setCellValue方法的具体用法？PHP PHPExcel_Worksheet::setCellValue怎么用？PHP PHPExcel_Worksheet::setCellValue使用的例子？那么, 这里精选的方法代码示例或许可以为您提供帮助。您也可以进一步了解该方法所在类PHPExcel_Worksheet的用法示例。
在下文中一共展示了PHPExcel_Worksheet::setCellValue方法的15个代码示例，这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞，您的评价将有助于系统推荐出更棒的PHP代码示例。
示例1: insertNewBefore
/**
* Insert a new column, updating all possible related data
*
* @param int $pBefore Insert before this one
* @param int $pNumCols Number of columns to insert
* @param int $pNumRows Number of rows to insert
* @throws Exception
*/
public function insertNewBefore($pBefore = 'A1', $pNumCols = 0, $pNumRows = 0, PHPExcel_Worksheet $pSheet = null)
{
// Get a copy of the cell collection
/*$aTemp = $pSheet->getCellCollection();
$aCellCollection = array();
foreach ($aTemp as $key => $value) {
$aCellCollection[$key] = clone $value;
}*/
$aCellCollection = $pSheet->getCellCollection();
// Get coordinates of $pBefore
$beforeColumn = 'A';
$beforeRow = 1;
list($beforeColumn, $beforeRow) = PHPExcel_Cell::coordinateFromString($pBefore);
// Clear cells if we are removing columns or rows
$highestColumn = $pSheet->getHighestColumn();
$highestRow = $pSheet->getHighestRow();
// 1. Clear column strips if we are removing columns
if ($pNumCols < 0 && PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 + $pNumCols > 0) {
for ($i = 1; $i <= $highestRow - 1; ++$i) {
for ($j = PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1 + $pNumCols; $j <= PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2; ++$j) {
$coordinate = PHPExcel_Cell::stringFromColumnIndex($j) . $i;
$pSheet->removeConditionalStyles($coordinate);
if ($pSheet->cellExists($coordinate)) {
$pSheet->getCell($coordinate)->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_NULL);
$pSheet->getCell($coordinate)->setXfIndex(0);
}
}
}
}
// 2. Clear row strips if we are removing rows
if ($pNumRows < 0 && $beforeRow - 1 + $pNumRows > 0) {
for ($i = PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1; $i <= PHPExcel_Cell::columnIndexFromString($highestColumn) - 1; ++$i) {
for ($j = $beforeRow + $pNumRows; $j <= $beforeRow - 1; ++$j) {
$coordinate = PHPExcel_Cell::stringFromColumnIndex($i) . $j;
$pSheet->removeConditionalStyles($coordinate);
if ($pSheet->cellExists($coordinate)) {
$pSheet->getCell($coordinate)->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_NULL);
$pSheet->getCell($coordinate)->setXfIndex(0);
}
}
}
}
// Loop through cells, bottom-up, and change cell coordinates
while ($cell = $pNumCols < 0 || $pNumRows < 0 ? array_shift($aCellCollection) : array_pop($aCellCollection)) {
// New coordinates
$newCoordinates = PHPExcel_Cell::stringFromColumnIndex(PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1 + $pNumCols) . ($cell->getRow() + $pNumRows);
// Should the cell be updated? Move value and cellXf index from one cell to another.
if (PHPExcel_Cell::columnIndexFromString($cell->getColumn()) >= PHPExcel_Cell::columnIndexFromString($beforeColumn) && $cell->getRow() >= $beforeRow) {
// Update cell styles
$pSheet->getCell($newCoordinates)->setXfIndex($cell->getXfIndex());
$cell->setXfIndex(0);
// Insert this cell at its new location
if ($cell->getDataType() == PHPExcel_Cell_DataType::TYPE_FORMULA) {
// Formula should be adjusted
$pSheet->setCellValue($newCoordinates, $this->updateFormulaReferences($cell->getValue(), $pBefore, $pNumCols, $pNumRows));
} else {
// Formula should not be adjusted
$pSheet->setCellValue($newCoordinates, $cell->getValue());
}
// Clear the original cell
$pSheet->setCellValue($cell->getCoordinate(), '');
}
}
// Duplicate styles for the newly inserted cells
$highestColumn = $pSheet->getHighestColumn();
$highestRow = $pSheet->getHighestRow();
if ($pNumCols > 0 && PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 > 0) {
for ($i = $beforeRow; $i <= $highestRow - 1; ++$i) {
// Style
$coordinate = PHPExcel_Cell::stringFromColumnIndex(PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2) . $i;
if ($pSheet->cellExists($coordinate)) {
$xfIndex = $pSheet->getCell($coordinate)->getXfIndex();
$conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ? $pSheet->getConditionalStyles($coordinate) : false;
for ($j = PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1; $j <= PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 + $pNumCols; ++$j) {
$pSheet->getCellByColumnAndRow($j, $i)->setXfIndex($xfIndex);
if ($conditionalStyles) {
$cloned = array();
foreach ($conditionalStyles as $conditionalStyle) {
$cloned[] = clone $conditionalStyle;
}
$pSheet->setConditionalStyles(PHPExcel_Cell::stringFromColumnIndex($j) . $i, $cloned);
}
}
}
}
}
if ($pNumRows > 0 && $beforeRow - 1 > 0) {
for ($i = PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1; $i <= PHPExcel_Cell::columnIndexFromString($highestColumn) - 1; ++$i) {
// Style
$coordinate = PHPExcel_Cell::stringFromColumnIndex($i) . ($beforeRow - 1);
if ($pSheet->cellExists($coordinate)) {
$xfIndex = $pSheet->getCell($coordinate)->getXfIndex();
//.........这里部分代码省略.........
示例2: addTransactionPage
private function addTransactionPage(PHPExcel_Worksheet $activeSheet)
{
$activeSheet->setTitle('Транзакции')->setCellValue('A12', 'Статус')->setCellValue('B12', 'Тип')->setCellValue('C12', 'ID')->setCellValue('D12', 'Дата')->setCellValue('E12', 'IP')->setCellValue('F12', 'ГЕО')->setCellValue('G12', 'URL цели')->setCellValue('H12', 'Источник')->setCellValue('I12', 'Материал')->setCellValue('J12', 'Выплата')->setCellValue('K12', 'Вознаграждение')->setCellValue('L12', 'Зароботок')->setCellValue('M12', 'Цель');
$row = 13;
$availableStatuses = ActionsLog::getAvailableStatuses();
foreach ($this->transactionData['rows'] as $tr) {
$activeSheet->setCellValue('A' . $row, $availableStatuses[$tr['status']])->setCellValue('B' . $row, $tr['source_type_name'])->setCellValue('C' . $row, $tr['id'])->setCellValue('D' . $row, Yii::app()->dateFormatter->formatDateTime($tr['date']))->setCellValue('E' . $row, $tr['ip'])->setCellValue('F' . $row, $tr['geo'])->setCellValue('G' . $row, $tr['target_url_decoded'])->setCellValue('H' . $row, $tr['source_name'])->setCellValue('I' . $row, $tr['target_name'])->setCellValue('J' . $row, $tr['payment'])->setCellValue('K' . $row, $tr['reward'])->setCellValue('L' . $row, $tr['debit'])->setCellValue('M' . $row, $tr['action_name']);
$row++;
}
$activeSheet->setCellValue('J' . $row, $this->transactionData['total']['payment'])->setCellValue('K' . $row, $this->transactionData['total']['reward'])->setCellValue('L' . $row, $this->transactionData['total']['debit']);
$activeSheet->getColumnDimension('A')->setWidth(16.3 * 1.05);
$activeSheet->getColumnDimension('B')->setWidth(16.43 * 1.05);
$activeSheet->getColumnDimension('C')->setWidth(5 * 1.05);
$activeSheet->getColumnDimension('D')->setWidth(17.86 * 1.05);
$activeSheet->getColumnDimension('E')->setWidth(14.14 * 1.05);
$activeSheet->getColumnDimension('F')->setWidth(34 * 1.05);
$activeSheet->getColumnDimension('G')->setWidth(31 * 1.05);
$activeSheet->getColumnDimension('H')->setWidth(30.86 * 1.05);
$activeSheet->getColumnDimension('I')->setWidth(19.14 * 1.05);
$activeSheet->getColumnDimension('J')->setWidth(8.57 * 1.05);
$activeSheet->getColumnDimension('K')->setWidth(8.57 * 1.05);
$activeSheet->getColumnDimension('L')->setWidth(8.57 * 1.05);
$activeSheet->getColumnDimension('M')->setWidth(30.7 * 1.05);
$activeSheet->getStyle('A12:M' . $row)->getAlignment()->setWrapText(true);
$this->formatTable($activeSheet, 'A', '12', 'M', $row, array('formatTotal' => true, 'innerRowHeight' => -1, 'headerRowHeight' => 27));
$this->addLogo($activeSheet);
$this->setHeader($activeSheet, $this->getHeaders());
$this->setPageFit($activeSheet, self::FIT_TO_WIDTH, PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
}示例3: addTableTotal
/**
* Добавляет в таблицу сводные данные по отчету
*
* @param PHPExcel_Worksheet $activeSheet
* @param array $reportData
* @param $rowc
*/
protected function addTableTotal(PHPExcel_Worksheet $activeSheet, array $reportData, $rowc)
{
$activeSheet->setCellValue('A' . $rowc, 'Итог')->setCellValue('E' . $rowc, $reportData['total']['debit'])->setCellValue('F' . $rowc, $reportData['total']['sum'])->setCellValue('H' . $rowc, $reportData['total']['sum_with_vat']);
$activeSheet->getStyle('A' . $rowc . ':K' . $rowc)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'dbe5f1')), 'font' => array('bold' => true, 'color' => array('rgb' => '000000'))));
$activeSheet->setCellValue('A' . ($rowc + 2), 'Не оплачено (без учета ндс):')->setCellValue('E' . ($rowc + 2), $reportData['total']['not_paid'])->setCellValue('A' . ($rowc + 3), 'Оплачено (без учета ндс):')->setCellValue('E' . ($rowc + 3), $reportData['total']['paid']);
$activeSheet->getStyle('A' . ($rowc + 2) . ':E' . ($rowc + 2))->getFill()->applyFromArray(array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'f2dddc')));
$activeSheet->getStyle('A' . ($rowc + 3) . ':E' . ($rowc + 3))->getFill()->applyFromArray(array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'eaf1dd')));
}示例4: buildFooter
private function buildFooter()
{
$column_index = 0;
foreach ($this->responseTableView->getFooterData() as $footer) {
$column_name = Utility::getNameFromNumber($column_index++);
$this->sheet->setCellValue($column_name . $this->row_index, $footer);
$this->sheet->getStyle($column_name . $this->row_index)->getFont()->setBold(true);
}
}示例5: _signupSheet
protected function _signupSheet($event, $includeEndingTerms, $includeNotEndingTerms)
{
$sheet = new PHPExcel_Worksheet($this->_excelDoc, 'Signup Sheet for Workshop ' . $event['workshopTitle']);
// Set up the margins so the header doesn't bleed into the page
$sheet->getPageMargins()->setTop(1.5);
// Make a three column page layout
$sheet->getColumnDimension('A')->setWidth(16);
$sheet->getColumnDimension('B')->setWidth(16);
$sheet->getColumnDimension('C')->setWidth(45);
$config = new Zend_Config_Xml(APPLICATION_PATH . '/configs/config.xml', 'production');
$date = new DateTime($event['date']);
$startTime = new DateTime($event['startTime']);
$endTime = new DateTime($event['endTime']);
// Set the header on odd pages.
// The code formatting is off because the header doesn't ignore spaces.
/*
* Format:
* Title
* Room name
* date('D, M d, Y') (startTime('g:i A') - endTime('g:i A'))
* Instructors
*
*/
$sheet->getHeaderFooter()->setOddHeader('&C&B&14' . $event['workshopTitle'] . '&14&B&12 ' . chr(10) . $event['location'] . chr(10) . $date->format('l, M d, Y') . '(' . $startTime->format('g:i A') . ' - ' . $endTime->format('g:i A') . ')' . chr(10) . 'Instructor: ' . implode(',', $event['instructors']) . '&12&C');
// Write Column Headers for the table
$sheet->setCellValue('A1', 'First Name');
$sheet->setCellValue('B1', 'Last Name');
$sheet->setCellValue('C1', 'Signature');
// reformat it a little bit in a simpler way for us to use it in our
// spreadsheet printin' loop
$rows = array();
foreach ($event['attendeeList'] as $a) {
$rows[] = array($a['firstName'], $a['lastName']);
}
$signin = new PHPExcel_Style();
$signin->applyFromArray(array('borders' => array('bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN))));
$rowCounter = 3;
foreach ($rows as $row) {
$row = array_values($row);
// put the totals in the row
$char = self::A;
foreach ($row as $cell) {
$sheet->setCellValue(chr($char) . $rowCounter, $cell);
$char++;
}
$rowCounter++;
}
$tableHeaderStyle = new PHPExcel_Style();
$tableHeaderStyle->applyFromArray($this->_tableHeaderStyleArray);
$tableBodyStyle = new PHPExcel_Style();
$tableBodyStyle->applyFromArray($this->_contentStyleArray);
$sheet->setSharedStyle($tableHeaderStyle, 'A1:C1');
$sheet->setSharedStyle($tableBodyStyle, 'A3:B' . ($rowCounter - 1));
$sheet->setSharedStyle($signin, 'C3:C' . ($rowCounter - 1));
return $sheet;
}示例6: render
public function render($data)
{
$column = 'A';
foreach ($data as $value) {
$cellCoordinates = $column++ . $this->offset;
$this->sheet->setCellValue($cellCoordinates, $value);
}
$this->offset++;
return '';
}示例7: copyRows
/**
* 行を完全コピーする
*
* http://blog.kotemaru.org/old/2012/04/06.html より
* @param PHPExcel_Worksheet $sheet
* @param int $srcRow
* @param int $dstRow
* @param int $height
* @param int $width
* @throws PHPExcel_Exception
*/
function copyRows(PHPExcel_Worksheet $sheet, $srcRow, $dstRow, $height, $width)
{
for ($row = 0; $row < $height; $row++) {
// セルの書式と値の複製
for ($col = 0; $col < $width; $col++) {
$cell = $sheet->getCellByColumnAndRow($col, $srcRow + $row);
$style = $sheet->getStyleByColumnAndRow($col, $srcRow + $row);
$dstCell = PHPExcel_Cell::stringFromColumnIndex($col) . (string) ($dstRow + $row);
$sheet->setCellValue($dstCell, $cell->getValue());
$sheet->duplicateStyle($style, $dstCell);
}
// 行の高さ複製。
$h = $sheet->getRowDimension($srcRow + $row)->getRowHeight();
$sheet->getRowDimension($dstRow + $row)->setRowHeight($h);
}
// セル結合の複製
// - $mergeCell="AB12:AC15" 複製範囲の物だけ行を加算して復元。
// - $merge="AB16:AC19"
foreach ($sheet->getMergeCells() as $mergeCell) {
$mc = explode(":", $mergeCell);
$col_s = preg_replace("/[0-9]*/", "", $mc[0]);
$col_e = preg_replace("/[0-9]*/", "", $mc[1]);
$row_s = (int) preg_replace("/[A-Z]*/", "", $mc[0]) - $srcRow;
$row_e = (int) preg_replace("/[A-Z]*/", "", $mc[1]) - $srcRow;
// 複製先の行範囲なら。
if (0 <= $row_s && $row_s < $height) {
$merge = $col_s . (string) ($dstRow + $row_s) . ":" . $col_e . (string) ($dstRow + $row_e);
$sheet->mergeCells($merge);
}
}
}示例8: exportAttributeNames
/**
* @param \PHPExcel_Worksheet $sheet
*/
public function exportAttributeNames($sheet)
{
$row = 1;
foreach ($this->_standardAttributes as $name => $standardAttribute) {
$sheet->setCellValue($standardAttribute->column . $row, $name);
}
}示例9: addLine
/**
* @param \PHPExcel_Worksheet $sheet
* @param integer $offset
* @param array $values
*/
public function addLine($sheet, $offset, $values)
{
$column = 'A';
foreach ($values as $value) {
$cellCoordinates = $column++ . (string) $offset;
$sheet->setCellValue($cellCoordinates, $value);
}
}示例10: writeData
protected function writeData(\PHPExcel_Worksheet $worksheet)
{
foreach ($this->activeDataProvider->getModels() as $row => $model) {
foreach ($this->columns as $col => $column) {
$columnIndex = \PHPExcel_Cell::stringFromColumnIndex($col) . ($row + 2);
switch ($column->format) {
case Column::FormatRaw:
$worksheet->setCellValue($columnIndex, $column->getValue($model));
break;
case Column::FormatUri:
$worksheet->setCellValue($columnIndex, $column->getValue($model));
$worksheet->getCell($columnIndex)->getHyperlink()->setUrl('"' . $column->getValue($model) . '"');
break;
}
}
}
}示例11: buildBody
private function buildBody()
{
$column_index = 0;
$row_index = $this->row_index;
$column_name = Utility::getNameFromNumber($column_index);
foreach ($this->analyze->getAlternativesNames() as $alternative_name) {
$this->sheet->setCellValue($column_name . $row_index, $alternative_name);
$row_index++;
}
$column_index = 1;
$column_name = Utility::getNameFromNumber($column_index);
foreach ($this->analyze->getData() as $alternatives) {
$row_index = $this->row_index;
foreach ($alternatives as $value) {
$this->sheet->setCellValue($column_name . $row_index++, $value);
}
$column_name = Utility::getNameFromNumber(++$column_index);
}
}示例12: exportExcel
/**
* @param Worksheet $worksheet
* @param array $style_h2
* @return Worksheet
* @throws \PHPExcel_Exception
*/
public function exportExcel(Worksheet $worksheet, array $style_h2)
{
$last_row = $worksheet->getHighestDataRow();
$last_row += 2;
$max_col = $worksheet->getHighestDataColumn();
$worksheet->mergeCells("A{$last_row}:{$max_col}{$last_row}");
$worksheet->setCellValue("A{$last_row}", utf8_encode($this->getTitulo()));
$worksheet->getStyle("A{$last_row}:{$max_col}{$last_row}")->applyFromArray($style_h2);
$worksheet->getRowDimension($last_row)->setRowHeight(20);
$last_row += 2;
$worksheet->setCellValue("C{$last_row}", utf8_encode('Opción'));
$worksheet->setCellValue("D{$last_row}", 'Votos');
$first_row = $last_row;
$last_row += 1;
foreach ($this->getDatos() as $key => $dato) {
$worksheet->setCellValue("B{$last_row}", $key + 1);
$worksheet->setCellValue("C{$last_row}", utf8_encode($dato[0]));
if (mb_strlen($dato[0]) > 45) {
$worksheet->getRowDimension($last_row)->setRowHeight(27);
}
$worksheet->setCellValue("D{$last_row}", $dato[1]);
$last_row++;
}
$last_row -= 1;
$worksheet->getStyle("C{$first_row}:D{$last_row}")->applyFromArray($this->getEstiloTabla('center', true));
$first_row++;
$worksheet->getStyle("B{$first_row}:D{$last_row}")->applyFromArray($this->getEstiloTabla());
$first_row -= 1;
$top_chart = $first_row - 1;
$bottom_chart = $first_row + 12;
$chart1 = $this->getChart($first_row, $last_row, $top_chart, $bottom_chart);
$worksheet->addChart($chart1);
$worksheet->setCellValue("A{$bottom_chart}", "");
return $worksheet;
}示例13: writeSums
/**
* write sums for active columns
* only if configured in self::$COLUMN_CONFIG
*/
protected function writeSums()
{
for ($x = count($this->activeColumns_arr) - 1; $x >= 0; $x--) {
$columnName = $this->activeColumns_arr[$x];
$fieldConf = array_key_exists($columnName, self::$COLUMN_CONFIG) ? self::$COLUMN_CONFIG[$columnName] : array();
$doSum = array_key_exists('sum', $fieldConf) ? $fieldConf['sum'] : false;
$curAddr = self::excelAddr($x, self::EXCEL_HEADER_OFFSET + count($this->exportData_arr));
if (!$doSum) {
if ($x == 0 && $this->anySumsWereAdded) {
$this->sheet->setCellValue($curAddr, $this->kga['lang']['total']);
}
continue;
}
$this->anySumsWereAdded = true;
$this->sheet->setCellValue($curAddr, "=SUM(" . self::excelRange($x, self::EXCEL_HEADER_OFFSET, $x, self::EXCEL_HEADER_OFFSET + count($this->exportData_arr) - 1) . ")");
}
}示例14: _set_row
/**
* Sets cells of a single row
*
* @param int $row
* @param mixed $cell_values
* @param boolean $header
* @return Worksheet
*/
private function _set_row($row, &$data, $header = FALSE)
{
$column = 0;
$format = NULL;
$type = PHPExcel_Cell_DataType::TYPE_STRING;
foreach ($this->columns as $key => $name) {
$value = NULL;
if (is_array($data)) {
$value = $data[$key];
} elseif (is_object($data)) {
if (method_exists($data, $key)) {
$value = $data->{$key}();
} elseif (isset($data->{$key})) {
$value = $data->{$key};
}
}
// Determine cell type and format
if ($header === FALSE) {
$type = Arr::get($this->types, $key);
}
// Set cell value
$coordinates = PHPExcel_Cell::stringFromColumnIndex($column) . $row;
if ($type !== NULL) {
$this->_worksheet->setCellValueExplicit($coordinates, $value, $type);
} else {
$this->_worksheet->setCellValue($coordinates, $value);
}
$column++;
}
return $this;
}示例15: addTableTotal
/**
* Добавляет в таблицу сводные данные по отчету
*
* @param PHPExcel_Worksheet $activeSheet
* @param array $reportData
* @param $rowc
*/
protected function addTableTotal(PHPExcel_Worksheet $activeSheet, array $reportData, $rowc)
{
$activeSheet->setCellValue('A' . $rowc, 'Итог')->setCellValue('C' . $rowc, $reportData['total']['shows'])->setCellValue('D' . $rowc, $reportData['total']['clicks'])->setCellValue('E' . $rowc, $reportData['total']['ctr'])->setCellValue('F' . $rowc, $reportData['total']['clickfraud'])->setCellValue('G' . $rowc, $reportData['total']['price']);
$activeSheet->getStyle('A' . $rowc . ':D' . $rowc)->applyFromArray(array('font' => array('bold' => true)));
}