<?php
require_once 'Writer.php';
// Creating a workbook
$workbook = new Excel_Writer();
// sending HTTP headers
$workbook->send('test.xls');
// Creating a worksheet
$worksheet1 =& $workbook->addWorksheet('Movitex');
// The actual data
$worksheet1->setInputEncoding('CP1251');
$worksheet1->write(0, 0, 'Name');
$worksheet1->write(0, 1, 'Age');
$worksheet1->write(1, 0, 'John Smith');
$worksheet1->write(1, 1, 30);
$worksheet1->write(2, 0, 'Johann Schmidt');
$worksheet1->write(2, 1, 31);
$worksheet1->write(3, 0, 'Juan Herrera');
$worksheet1->write(3, 1, 32);
//void Worksheet::setOutline ([bool $visible=true [, bool $symbols_below=true [, bool $symbols_right=true [, bool $auto_style=false]]]])
$worksheet1->setOutline(true, false, true, false);
$worksheet1->freezePanes(array(8, 0, 8, 0, 2));
$worksheet1->mergeCells(0, 1, 5, 4);
$worksheet1->setRow(12, 15, 0, 0, 1);
$worksheet1->setRow(13, 15, 0, 0, 2);
$worksheet1->setRow(14, 15, 0, 0, 2);
$worksheet1->setRow(15, 15, 0, 0, 1);
//void Worksheet::setOutline ([bool $visible=true [, bool $symbols_below=true [, bool $symbols_right=true [, bool $auto_style=false]]]])
// Let's send the file
$workbook->close();
//require_once('OLEwriter.php');
function render($RecordSet, $titleArray, $fields, $fileName)
{
echo <<<STYLE
<style>
table.excel {
\tborder-style:ridge;
\tborder-width:1;
\tborder-collapse:collapse;
\tfont-family:sans-serif;
\tfont-size:12px;
}
table.excel thead th, table.excel tbody th {
\tbackground:#CCCCCC;
\tborder-style:ridge;
\tborder-width:1;
\ttext-align: center;
\tvertical-align:bottom;
}
table.excel tbody th {
\ttext-align:center;
\twidth:20px;
}
table.excel tbody td {
\tvertical-align:bottom;
}
table.excel tbody td {
padding: 0 3px;
\tborder: 1px solid #EEEEEE;
}
table.excel tr:hover {
\tcolor: #09F;
\tbackground-color: #D6D6D6;
}
</style>
STYLE;
try {
error_reporting(E_ALL ^ E_NOTICE);
ini_set('display_errors', 0);
if ('' == $fileName) {
$fileName = uniqid();
}
$filePath = App_Env::getPathOfUserTemporaryFolder() . "/" . $fileName . '.xls';
$workbook = new Excel_Writer($filePath);
$workbook->setLandscape();
$workbook->setTitle($this->filter()->getCaptionText());
// query
$i = 0;
$dataSet = array();
foreach ($RecordSet as $rows) {
//array_unique ( $row );
$j = 0;
foreach ($fields as $filed) {
$dataSet[$i][$j++] = $this->filter()->fieldTranform($rows, $filed);
}
++$i;
}
// set data
$workbook->setData($dataSet);
$lastcol = $j - 1;
$columnConfig = $this->_columnConfig;
$widths = array();
$tableWidth = $this->filter()->getTableWidth();
if (false != strpos($tableWidth, "%")) {
$tableWidth = "100";
}
//echo $tableWidth;
$counter_blank_cell = 0;
$total_cell_width = 0;
foreach ($fields as $filed) {
$width = $columnConfig->fields[$filed]->width;
if (false != strpos($width, "%")) {
$width = str_replace("%", "", $width);
$w = $width * $tableWidth / 100;
} else {
$w = str_replace("px", "", $width);
}
if ($w == '') {
$counter_blank_cell += 1;
}
$total_cell_width += $w;
$widths[] = $w;
}
if ($counter_blank_cell == 0) {
$counter_blank_cell = 1;
}
for ($i = 0; $i < count($widths); $i++) {
if ($widths[$i] == '') {
$widths[$i] = ($tableWidth - $total_cell_width) / $counter_blank_cell;
}
}
$format = array('right' => 1, 'left' => 1, 'border' => 1);
$i = 0;
foreach ($fields as $filed) {
$align = in_array($columnConfig->fields[$filed]->align, App_Model_Config::$alignList) ? $columnConfig->fields[$filed]->align : 'left';
$workbook->setColumn($i, $i, $widths[$i], array_merge($format, array('align' => $align)));
++$i;
}
//$workbook->setColumn(0,0,15 ,array_merge($format,array( 'align'=>'center')) );
//$workbook->setColumn(1,1,35,array_merge($format,array('align'=>'left')));
//$workbook->setColumn(2,4,20,array_merge($format,array('align'=>'center','NumFormat'=>'_-* #,##0.00_-')));
//$workbook->setColumn(5,$lastcol,10,array_merge($format,array('align'=>'right','NumFormat'=>'_-* #,##0.00_-')));
//set header
for ($i = 0; $i < count($titleArray); $i++) {
$titleArray[$i] = ucfirst(App_Util::splitByCaps($this->_translate($titleArray[$i])));
}
$header[0] = $titleArray;
//$header[0] = array('เธฃเธซเธฑเธชเธงเธดเธ—เธขเธฒเธฅเธฑเธข','เธงเธดเธ—เธขเธฒเธฅเธฑเธขเธ�เธธเธกเธ�เธ�',"เธงเธ�เน€เธ�เธดเธ�เธ—เธตเน�เน�เธ”เน�เธฃเธฑเธ� \n เธ�เธฑเธ”เธชเธฃเธฃ","เธฃเธงเธกเน€เธ�เธดเธ� \nเธ�เธ�เธ�เธฃเธฐเธกเธฒเธ“เธ—เธตเน� \nเน�เธ”เน�เธฃเธฑเธ�เน�เธญเธ�","เธฃเน�เธญเธขเธฅเธฐ\n เธ•เน�เธญ \nเธงเธ�เน€เธ�เธดเธ�เธ—เธตเน� เน�เธ”เน�เธฃเธฑเธ�เธ�เธฑเธ”เธชเธฃเธฃ",'เธ�เธณเธ�เธงเธ�เน€เธ�เธดเธ�เธ—เธตเน�เน�เธ”เน�เธฃเธฑเธ�เน�เธญเธ�');
//$header[1] = array('','','','','');
//for($i=1;$i<=$lastcol-4;$i++){
/// $header[0][] ='';
// $header[1][] = 'เธ�เธฃเธฑเน�เธ�เธ—เธตเน� '.$i ;
//}
//array_pop( $header[0]);
$workbook->setHeader($header);
//$first_row , $first_col , $last_row , $last_col
$firstRow = $workbook->getFirstRow();
///echo $lastcol;
//$workbook->mergeCells($firstRow,0,$firstRow+1,0);
//$workbook->mergeCells($firstRow,1,$firstRow+1,1);
//$workbook->mergeCells($firstRow,2,$firstRow+1,2);
//$workbook->mergeCells($firstRow,3,$firstRow+1,3);
//$workbook->mergeCells($firstRow,4,$firstRow+1,4);
//$workbook->mergeCells($firstRow,5,$firstRow,$lastcol);
//addLastRow;
//$workbook->setFormula ('lastrow',0,0,'' );
//$workbook->setFormula ('lastrow',1,1,'เธฃเธงเธกเธ—เธธเธ�เธงเธดเธ—เธขเธฒเธฅเธฑเธข' );
//$workbook->setFormula ('lastrow',2,3,'SUMCOLUMN');
//$workbook->setFormula ('lastrow',4,4,'=(D[row]/C[row])*100');
//$workbook->setFormula ('lastrow',5,$lastcol,'SUMCOLUMN');
echo $workbook->render();
} catch (Exception $e) {
echo $e->getCode(), 'error : ', $e->getMessage();
echo $e->getTraceAsString();
}
}