/**
* Формирует .xls данные о деньгах работодателей по текущим незакрытым сделкам за указанный период.
*
* @param array $filter фильтр по дате.
*/
public function printRevisionReport($filter = null)
{
if (!$filter['to']) {
$filter['to'] = array('day' => date('d'), 'month' => date('n'), 'year' => date('Y'));
}
if (!($rep = $this->getRevisionReport($filter))) {
return false;
}
$period = $this->_createPeriodStr($filter);
$rpss = self::$reports_ss['REV'];
$COL_START = 0;
require_once 'Spreadsheet/Excel/Writer.php';
$workbook = new Spreadsheet_Excel_Writer();
$workbook->send('СБР_сверка_' . str_replace('.', '_', $period[0]) . '.xls');
$body_sty = array('FontFamily' => 'Arial', 'Size' => 10, 'Bold' => 1);
$td_sty = array('FontFamily' => 'Calibri', 'VAlign' => 'vequal_space', 'Align' => 'center', 'Size' => 11, 'Border' => 1, 'BorderColor' => 'black', 'NumFormat' => '#');
$th_sty = array('FontFamily' => 'Arial', 'Size' => 10, 'Align' => 'center', 'Border' => 1, 'BorderColor' => 'black', 'Bold' => 1);
$l_sty = array('Align' => 'left');
$r_sty = array('Align' => 'right', 'NumFormat' => '### ### ##0.00');
$fmtBODY =& $workbook->addFormat($body_sty);
$fmtBODYR =& $workbook->addFormat($body_sty + $r_sty);
$fmtTD =& $workbook->addFormat($td_sty);
$fmtTH =& $workbook->addFormat($th_sty);
$fmtTH->setTextWrap(1);
$fmtTDL =& $workbook->addFormat($l_sty + $td_sty);
$fmtTDR =& $workbook->addFormat($r_sty + $td_sty);
$worksheet = $workbook->addWorksheet('Сверка');
$worksheet->setInputEncoding('windows-1251');
$worksheet->setZoom(75);
$worksheet->setColumn(0, 0, 2);
// Заголовок
$worksheet->write(0, $COL_START, $rpss['name'], $fmtBODY);
$worksheet->write(1, $COL_START, 'Остатки кредиторской задолженности по сервису «Безопасная Сделка»', $fmtBODY);
$worksheet->write(2, $COL_START, $period[0], $fmtBODY);
$worksheet->write(2, 5, 'Дата ' . date('d.m.Y H:i'), $fmtBODY);
$worksheet->mergeCells(0, $COL_START, 0, 6);
// Шапка таблицы.
$i = $COL_START;
foreach ($rpss['columns'] as $f => $col) {
$worksheet->setColumn($i, $i, $col[1][0]);
$worksheet->write(4, $i, $col[0], $fmtTH);
++$i;
}
// Таблица.
$i = 5;
$sum_all = 0;
foreach ($rep as $row) {
$j = $COL_START;
$sum_all += $row['sum_dept'];
foreach ($rpss['columns'] as $nm => $col) {
$worksheet->write($i, $j, htmlspecialchars_decode($row[$nm], ENT_QUOTES), $col[1][1] == 'right' ? $fmtTDR : ($col[1][1] == 'left' ? $fmtTDL : $fmtTD));
++$j;
}
++$i;
}
// Дно таблицы (итого).
$worksheet->write($i, 0, 'ИТОГО', $fmtBODYR);
$worksheet->mergeCells($i, 0, $i, 2);
$c1 = Spreadsheet_Excel_Writer::rowcolToCell(5, 3);
$c2 = Spreadsheet_Excel_Writer::rowcolToCell($i - 1, 3);
$worksheet->writeFormula($i, 3, "=SUM({$c1}:{$c2})", $fmtSR);
$c1 = Spreadsheet_Excel_Writer::rowcolToCell(5, 4);
$c2 = Spreadsheet_Excel_Writer::rowcolToCell($i - 1, 4);
$worksheet->writeFormula($i, 4, "=SUM({$c1}:{$c2})", $fmtSR);
$c1 = Spreadsheet_Excel_Writer::rowcolToCell(5, 5);
$c2 = Spreadsheet_Excel_Writer::rowcolToCell($i - 1, 5);
$worksheet->writeFormula($i, 5, "=SUM({$c1}:{$c2})", $fmtSR);
$i += 3;
$r = explode('.', (string) $sum_all);
$worksheet->write($i, 0, "На {$filter['to']['day']}." . ($filter['to']['month'] < 10 ? '0' . $filter['to']['month'] : $filter['to']['month']) . ".{$filter['to']['year']} задолженность ООО \"Ваан\" в пользу Работодателей по сервису «Безопасная Сделка»", $fmtBODY);
$worksheet->write($i + 1, 0, 'Cоставляет ' . intval($r[0]) . ' руб. ' . intval($r[1]) . ' коп.', $fmtBODY);
$workbook->close();
}
<?php
require_once 'Spreadsheet/Excel/Writer.php';
$workbook = new Spreadsheet_Excel_Writer('rowcol.xls');
$worksheet1 =& $workbook->addWorksheet("rowcol");
$first = 1;
$last = 10;
for ($i = $first; $i <= $last; $i++) {
$worksheet1->write($i, 1, $i);
}
$cell1 = Spreadsheet_Excel_Writer::rowcolToCell($first, 1);
$cell2 = Spreadsheet_Excel_Writer::rowcolToCell($last, 1);
$worksheet1->write($last + 1, 0, "Total =");
$worksheet1->writeFormula($last + 1, 1, "=SUM({$cell1}:{$cell2})");
$workbook->close();
