function createXLSAdv(&$rows, $row_labels = null, $row_headers = null, $title = null)
{
ob_clean();
ob_start();
require_once JPATH_COMPONENT_SITE . DS . 'libraries' . DS . 'pear' . DS . 'PEAR.php';
require_once JPATH_COMPONENT_SITE . DS . 'libraries' . DS . 'Excel' . DS . 'Writer.php';
// Creating a workbook
$workbook = new Spreadsheet_Excel_Writer();
$worksheet = $workbook->addWorksheet($title);
$BIFF = new Spreadsheet_Excel_Writer_BIFFwriter();
$format = new Spreadsheet_Excel_Writer_Format($BIFF);
$format->setBold(1);
$format->setAlign('center');
for ($k = 0; $k < count($row_labels); $k++) {
$worksheet->write(0, $k, $row_headers[$row_labels[$k]], $format);
}
for ($i = 0; $i < count($rows); $i++) {
for ($k = 0; $k < count($row_labels); $k++) {
$worksheet->write($i + 1, $k, $rows[$i][$row_labels[$k]]);
}
}
$workbook->close();
$attachment = ob_get_contents();
@ob_end_clean();
echo $attachment;
}
/**
* Write a formula to the specified row and column (zero indexed).
* The textual representation of the formula is passed to the parser in
* Parser.php which returns a packed binary string.
*
* Returns 0 : normal termination
* -1 : formula errors (bad formula)
* -2 : row or column out of range
*
* @access public
* @param integer $row Zero indexed row
* @param integer $col Zero indexed column
* @param string $formula The formula text string
* @param mixed $format The optional XF format
* @return integer
*/
public function writeFormula($row, $col, $formula, $format = null)
{
$record = 0x6;
// Record identifier
// Excel normally stores the last calculated value of the formula in $num.
// Clearly we are not in a position to calculate this a priori. Instead
// we set $num to zero and set the option flags in $grbit to ensure
// automatic calculation of the formula when the file is opened.
//
$xf = $this->_XF($format);
// The cell format
$num = 0x0;
// Current value of formula
$grbit = 0x3;
// Option flags
$unknown = 0x0;
// Must be zero
// Check that row and col are valid and store max and min values
if ($this->_checkRowCol($row, $col) == false) {
return -2;
}
// Strip the '=' or '@' sign at the beginning of the formula string
if (preg_match("/^=/", $formula)) {
$formula = preg_replace("/(^=)/", "", $formula);
} elseif (preg_match("/^@/", $formula)) {
$formula = preg_replace("/(^@)/", "", $formula);
} else {
// Error handling
$this->writeString($row, $col, 'Unrecognised character for formula');
return -1;
}
// Parse the formula using the parser in Parser.php
$error = $this->_parser->parse($formula);
if ($this->isError($error)) {
$this->writeString($row, $col, $error->getMessage());
return -1;
}
$formula = $this->_parser->toReversePolish();
if ($this->isError($formula)) {
$this->writeString($row, $col, $formula->getMessage());
return -1;
}
$formlen = strlen($formula);
// Length of the binary string
$length = 0x16 + $formlen;
// Length of the record data
$header = pack("vv", $record, $length);
$data = pack("vvvdvVv", $row, $col, $xf, $num, $grbit, $unknown, $formlen);
$this->_append($header . $data . $formula);
return 0;
}
function createXLS($result) {
ob_clean();
ob_start();
set_time_limit(0);
error_reporting(0);
require_once JPATH_COMPONENT_SITE . '/libraries/pear/PEAR.php';
require_once (JPATH_ROOT . '/components/com_bids/libraries/Excel/Writer.php');
// Creating a workbook
$workbook = new Spreadsheet_Excel_Writer();
$workbook->setTempDir(JPATH_ROOT.DS.'tmp');
$worksheet = $workbook->addWorksheet('Exported Auctions');
$BIFF = new Spreadsheet_Excel_Writer_BIFFwriter();
$format = new Spreadsheet_Excel_Writer_Format($BIFF);
$format->setBold(1);
$format->setAlign('center');
$colNr = 0;
$worksheet->write(0, $colNr++, "User", $format);
$worksheet->write(0, $colNr++, "Title", $format);
$worksheet->write(0, $colNr++, "Short description");
$worksheet->write(0, $colNr++, "Description");
$worksheet->write(0, $colNr++, "Initial price");
$worksheet->write(0, $colNr++, "Currency");
$worksheet->write(0, $colNr++, "BIN price");
$worksheet->write(0, $colNr++, "Auction type");
$worksheet->write(0, $colNr++, "Automatic");
$worksheet->write(0, $colNr++, "Payment");
$worksheet->write(0, $colNr++, "Shipment Info");
$worksheet->write(0, $colNr++, "Start date");
$worksheet->write(0, $colNr++, "End date");
$worksheet->write(0, $colNr++, "Param: picture");
$worksheet->write(0, $colNr++, "Param: add_picture");
$worksheet->write(0, $colNr++, "Param: auto_accept_bin");
$worksheet->write(0, $colNr++, "Param: bid_counts");
$worksheet->write(0, $colNr++, "Param: max_price");
$worksheet->write(0, $colNr++, "Published");
$worksheet->write(0, $colNr++, "Category");
$worksheet->write(0, $colNr++, "Highest Bid");
for ($i = 0; $i < count($result); $i++) {
$colNr = 0;
$worksheet->write($i + 1, $colNr++, $result[$i]->username);
$worksheet->write($i + 1, $colNr++, $result[$i]->title);
$worksheet->write($i + 1, $colNr++, $result[$i]->shortdescription);
$worksheet->write($i + 1, $colNr++, $result[$i]->description);
$worksheet->write($i + 1, $colNr++, $result[$i]->initial_price);
$worksheet->write($i + 1, $colNr++, $result[$i]->currency);
$worksheet->write($i + 1, $colNr++, $result[$i]->BIN_price);
switch ($result[$i]->auction_type) {
case 1:
$worksheet->write($i + 1, $colNr++, 'public');
break;
case 2:
$worksheet->write($i + 1, $colNr++, 'private');
break;
case 3:
$worksheet->write($i + 1, $colNr++, 'BIN only');
break;
}
$worksheet->write($i + 1, $colNr++, $result[$i]->automatic);
$worksheet->write($i + 1, $colNr++, $result[$i]->payment_method);
$worksheet->write($i + 1, $colNr++, $result[$i]->shipment_info);
$worksheet->write($i + 1, $colNr++, $result[$i]->start_date);
$worksheet->write($i + 1, $colNr++, $result[$i]->end_date);
$params = explode("\n", $result[$i]->params);
$tmp = explode("=", $params[0]); // picture param
$worksheet->write($i + 1, $colNr++, $tmp[1]);
$tmp = explode("=", $params[1]); // add_picture
$worksheet->write($i + 1, $colNr++, $tmp[1]);
$tmp = explode("=", $params[2]); // auto_accept_bin
$worksheet->write($i + 1, $colNr++, $tmp[1]);
$tmp = explode("=", $params[3]); // bid_counts
$worksheet->write($i + 1, $colNr++, $tmp[1]);
$tmp = explode("=", $params[4]); // max_price
$worksheet->write($i + 1, $colNr++, $tmp[1]);
$worksheet->write($i + 1, $colNr++, $result[$i]->published);
$worksheet->write($i + 1, $colNr++, $result[$i]->catname);
$worksheet->write($i + 1, $colNr++, $result[$i]->highest_bid);
$worksheet->setColumn(0, 0, 9);
$worksheet->setColumn(1, 12, 25);
}
$workbook->close();
$attachment = ob_get_clean();
return $attachment;
}
