function Pman_Core_SimpleExcel($data, $cfg)
{
// print_r($cfg);exit;
require_once 'Spreadsheet/Excel/Writer.php';
// Creating a workbook
$outfile2 = $this->tempName('xls');
// var_dump($outfile2);
$workbook = new Spreadsheet_Excel_Writer($outfile2);
//$workbook = new Spreadsheet_Excel_Writer();
$workbook->setVersion(8);
// sending HTTP headers
$this->workbook = $workbook;
$formats = array();
$cfg['formats'] = isset($cfg['formats']) ? $cfg['formats'] : array();
foreach ($cfg['formats'] as $f => $fcfg) {
$this->formats[$f] =& $workbook->addFormat();
foreach ((array) $fcfg as $k => $v) {
$this->formats[$f]->{'set' . $k}($v);
}
}
if (!empty($cfg['workbook'])) {
$this->buildPage(array(), $data, $cfg);
} elseif (!empty($cfg['workbooks'])) {
foreach ($cfg['workbooks'] as $i => $wcfg) {
$this->buildPage(array(), $data[$i], $wcfg);
}
}
// if workbooks == false - > the user can call buildpage..
if (!empty($cfg['leave_open'])) {
$this->outfile2 = $outfile2;
return;
}
$workbook->close();
$this->outfile2 = $outfile2;
}
/**
* создает файл в формате XLS (Microsoft Excel) на основе переданных данных
*
* @param array $data содержимое ячеек таблицы
* @param string $name имя отчета
* @return string содержимое файла
*/
public function xls(&$data, $name)
{
$fileName = date("Y-m-d_H-i-s");
$workBook = new Spreadsheet_Excel_Writer();
$workBook->setTempDir(BASEPATH . 'cache/');
$workBook->setVersion(8);
$workBook->send(__("report") . "_{$fileName}.xls");
$formatBold =& $workBook->addFormat();
$formatBold->setBold();
$formatTitle =& $workBook->addFormat();
$formatTitle->setBold();
$formatTitle->setColor('black');
$formatTitle->setPattern(1);
$formatTitle->setFgColor('gray');
$formatTitle->setAlign('merge');
$workSheet =& $workBook->addWorksheet('Report');
$workSheet->setInputEncoding('utf-8');
$row_count = 0;
foreach ($data as $row) {
$col_count = 0;
foreach ($row as $column) {
$workSheet->write($row_count, $col_count, $column, $row_count ? $formatBold : $formatTitle);
$col_count++;
}
$row_count++;
}
// ширина столбцов
// $workSheet->setColumn(0, 0, 30);
// $workSheet->setColumn(2, 2, 30);
$workBook->close();
}
public function WriteToXLS($filename)
{
$xls = new Spreadsheet_Excel_Writer($filename);
$sheet = $xls->addWorksheet('auto2you.ru');
$sheet->setColumn(0, 0, 130);
$sheet->setColumn(1, 1, 20);
$sheet->setColumn(2, 2, 15);
$sheet->setColumn(3, 3, 30);
$titleFormat = $xls->addFormat();
$titleFormat->setFontFamily('Helvetica');
$titleFormat->setBold();
$titleFormat->setAlign('center');
$itemFormat = $xls->addFormat();
$titles = array('Имя', 'Город', 'Телефон', 'E-mail');
foreach ($titles as $ind => $title) {
$sheet->write(0, $ind, $title, $titleFormat);
}
$ind = 1;
foreach ($this->data as $item) {
$sheet->write($ind, 0, $item['name'], $itemFormat);
$sheet->write($ind, 1, $item['city'], $itemFormat);
$sheet->write($ind, 2, $item['phone'], $itemFormat);
$sheet->write($ind, 3, $item['email'], $itemFormat);
$ind++;
}
$xls->close();
}
/**
* Excel保存
*/
function saveToExcel($data, $filename)
{
Configure::write('debug', 0);
App::import('Vendor', 'include_path');
App::import('Vendor', 'Spreadsheet_Excel_Writer', array('file' => 'Spreadsheet' . DS . 'Excel' . DS . 'Writer.php'));
$workbook = new Spreadsheet_Excel_Writer();
$workbook->send($filename);
$worksheet =& $workbook->addWorksheet('story');
$format =& $workbook->addFormat();
$format->setSize(9);
$header_format =& $workbook->addFormat();
$header_format->setSize(9);
$header_format->setFgColor('gray');
// ヘッダー
$header = array('Story Id', 'Story', 'Description', 'Story Points', sprintf(__('Count of %s', true), __('Task', true)), sprintf(__('Sum of %s', true), __('Remaining Hours', true)), 'Businessvalue', 'Sprint', 'Priority', 'Resolution', 'Team', 'Created');
$row = 0;
$col = 0;
for ($i = 0; $i < count($header); $i++) {
$worksheet->write($row, $col, $this->sjis(__($header[$i], true)), $header_format);
$col++;
}
// データ
$row++;
foreach ($data as $item) {
$col = 0;
$worksheet->writeNumber($row, $col, $this->sjis($item["Story"]["id"]), $format);
$col++;
$worksheet->write($row, $col, $this->sjis($item["Story"]["name"]), $format);
$col++;
$worksheet->write($row, $col, $this->sjis($item["Story"]["description"]), $format);
$col++;
$worksheet->write($row, $col, $this->sjis($item["Story"]["storypoints"]), $format);
$col++;
$worksheet->write($row, $col, $this->sjis($item["Story"]["task_count"]), $format);
$col++;
$worksheet->write($row, $col, $this->sjis($item["Story"]["total_hours"]), $format);
$col++;
$worksheet->write($row, $col, $this->sjis($item["Story"]["businessvalue"]), $format);
$col++;
$worksheet->write($row, $col, $this->sjis($item["Sprint"]["name"]), $format);
$col++;
$worksheet->write($row, $col, $this->sjis($item["Priority"]["name"]), $format);
$col++;
$worksheet->write($row, $col, $this->sjis(@$item["Resolution"]["name"]), $format);
$col++;
$worksheet->write($row, $col, $this->sjis(@$item["Team"]["name"]), $format);
$col++;
$worksheet->write($row, $col, date('Y-m-d', strtotime($item["Story"]["created"])), $format);
$col++;
$row++;
}
// 横幅設定
$width = array(4, 50, 50, 10, 10, 10, 10, 10, 20, 10, 10);
for ($i = 0; $i < count($width); $i++) {
$worksheet->setColumn($i, $i, $width[$i]);
}
$workbook->close();
exit;
}
/**
* Genera un reporte en Excel
*
* @param array $result
* @param array $sumArray
* @param string $title
* @param array $weightArray
* @param array $headerArray
*/
function xls($result, $sumArray, $title, $weightArray, $headerArray)
{
error_reporting(0);
$file = md5(uniqid());
$config = Config::read('config');
$active_app = Router::get_application();
$workbook = new Spreadsheet_Excel_Writer("public/temp/{$file}.xls");
$worksheet =& $workbook->addWorksheet();
$titulo_verdana =& $workbook->addFormat(array('fontfamily' => 'Verdana', 'size' => 20));
$titulo_verdana2 =& $workbook->addFormat(array('fontfamily' => 'Verdana', 'size' => 18));
$workbook->setCustomColor(12, 0xf2, 0xf2, 0xf2);
$column_title =& $workbook->addFormat(array('fontfamily' => 'Verdana', 'size' => 12, 'fgcolor' => 12, 'border' => 1, 'bordercolor' => 'black', "halign" => 'center'));
$column =& $workbook->addFormat(array('fontfamily' => 'Verdana', 'size' => 11, 'border' => 1, 'bordercolor' => 'black'));
$column_centered =& $workbook->addFormat(array('fontfamily' => 'Verdana', 'size' => 11, 'border' => 1, 'bordercolor' => 'black', "halign" => 'center'));
$worksheet->write(0, 0, strtoupper($config->{$active_app}->name), $titulo_verdana);
$worksheet->write(1, 0, "REPORTE DE " . strtoupper($title), $titulo_verdana2);
$worksheet->write(2, 0, "FECHA " . date("Y-m-d"), $titulo_verdana2);
for ($i = 0; $i <= count($headerArray) - 1; $i++) {
$worksheet->setColumn($i, $i, $weightArray[$i]);
$worksheet->write(4, $i, $headerArray[$i], $column_title);
}
$l = 5;
foreach ($result as $row) {
for ($i = 0; $i <= count($row) - 1; $i++) {
if (!is_numeric($row[$i])) {
$worksheet->writeString($l, $i, $row[$i], $column);
} else {
$worksheet->writeString($l, $i, $row[$i], $column_centered);
}
}
$l++;
}
$workbook->close();
error_reporting(E_ALL ^ E_STRICT);
if (isset($raw_output)) {
print "<script type='text/javascript'> window.open('" . KUMBIA_PATH . "temp/" . $file . ".xls', null); </script>";
} else {
Generator::forms_print("<script type='text/javascript'> window.open('" . KUMBIA_PATH . "temp/" . $file . ".xls', null); </script>");
}
}
function renderXls()
{
$workbook = new Spreadsheet_Excel_Writer();
// sending HTTP headers
$workbook->send($this->getKursus()->getKursusNavn());
// Creating a worksheet
$worksheet = $workbook->addWorksheet('Deltagere');
$format_bold = $workbook->addFormat();
$format_bold->setBold();
$format_bold->setSize(8);
$format_italic = $workbook->addFormat();
$format_italic->setItalic();
$format_italic->setSize(8);
$format =& $workbook->addFormat();
$format->setSize(8);
$i = 0;
$worksheet->write($i, 0, 'Vejle Idrætshøjskole: ' . $this->getKursus()->getKursusNavn(), $format_bold);
$i = 2;
foreach ($this->getKursus()->getTilmeldinger() as $deltager) {
$worksheet->write($i, 0, $deltager->get('navn'), $style);
$worksheet->write($i, 1, $deltager->get('cpr'), $style);
$i++;
}
$worksheet->hideGridLines();
// Let's send the file
$data = $workbook->close();
$response = new k_HttpResponse(200, $data);
$response->setEncoding(NULL);
$response->setContentType("application/excel");
/*
$response->setHeader("Content-Length", strlen($data));
$response->setHeader("Content-Disposition", "attachment;filename=\"\"");
$response->setHeader("Content-Transfer-Encoding", "binary");
$response->setHeader("Cache-Control", "Public");
$response->setHeader("Pragma", "public");
*/
throw $response;
}
/**
* Streams to the browser the provided array of objects as a basic Excel document
* with headers. if the objects have an associated Map class, then footers will be
* added to sum any numeric fields. otherwise no footers are added
*
* Note that PEAR Spreadsheet_Excel_Writer must be installed
* @link http://pear.php.net/package/Spreadsheet_Excel_Writer
*
* @param Array an array of Phreezable objects, obtained for example, using DataSet->ToObjectArray
* @param Phreezer $phreezer is needed to get field maps
* @param string (optional) The title of the report
*/
static function OutputAsExcel(array $objects, Phreezer $phreezer, $reportTitle = "Data Export", $fileName = "export.xls")
{
require_once 'Spreadsheet/Excel/Writer.php';
// create the workbook and worksheet
$workbook = new Spreadsheet_Excel_Writer();
$worksheet = $workbook->addWorksheet("Export");
$BOLD_MED =& $workbook->addFormat();
$BOLD_MED->setSize(16);
$BOLD_MED->SetBold();
$BOLD_REG =& $workbook->addFormat();
$BOLD_REG->setSize(11);
$BOLD_REG->SetBold();
$NORMAL =& $workbook->addFormat();
$NORMAL->setSize(11);
$CURRENCY =& $workbook->addFormat();
$CURRENCY->setNumFormat('0.00');
$CURRENCY->setSize(11);
$CURRENCY->setAlign('right');
$worksheet->writeString(0, 0, $reportTitle, $BOLD_MED);
// default to no columns
$fields = array();
$columns = array();
$is_numeric = array();
$fieldmap_exists = false;
// print the headers
// while we're looping, also parse the fields so we don't have to do
// it repeatedly when looping through data
if (isset($objects[0])) {
try {
// see if there is a fieldmap for this object
$fields = $phreezer->GetFieldMaps(get_class($objects[0]));
$fieldmap_exists = true;
// these are the columns we'll use for enumeration from here on
$columns = array_keys($fields);
} catch (Exception $ex) {
// no fieldmaps exist, so use the reflection class instead
$reflect = new ReflectionClass($objects[0]);
$publicAttributes = $reflect->getProperties(ReflectionProperty::IS_PUBLIC);
$staticAttributes = $reflect->getStaticProperties();
// only include non-static public properties
$props = array_diff($publicAttributes, $staticAttributes);
foreach ($props as $prop) {
$column = $prop->getName();
$columns[] = $column;
}
}
$current_column = 0;
foreach ($columns as $column) {
// save this so we don't check it every time when looping through data
$is_numeric[$column] = $fieldmap_exists ? $fields[$column]->IsNumeric() : false;
$worksheet->writeString(2, $current_column, $column, $BOLD_REG);
$current_column++;
}
}
$current_row = 3;
// loop through all of the data
foreach ($objects as $object) {
$current_column = 0;
foreach ($columns as $column) {
if ($fieldmap_exists == false || $is_numeric[$column] == true) {
$worksheet->write($current_row, $current_column, $object->{$column}, $NORMAL);
} else {
$worksheet->writeString($current_row, $current_column, $object->{$column}, $NORMAL);
}
$current_column++;
}
$current_row++;
}
// lastly write to the footer to sum the numeric columns
$current_column = 0;
foreach ($columns as $column) {
if ($is_numeric[$column]) {
$columnLetter = ExportUtility::GetColumnLetter($current_column);
$formula = "=SUM(" . $columnLetter . "3:" . $columnLetter . ($current_row - 1) . ")";
// notice the @ sign in front because this will fire a deprecated warning due to use of "split"
@$worksheet->write($current_row, $current_column, $formula, $BOLD_REG);
}
$current_column++;
}
$workbook->send($fileName);
// this has errors suppressed due to strict mode
@$workbook->close();
}
}
// end foreach record/timepoint
if (is_array($data) && $data > 0) {
fwrite(STDERR, "Start Excel \n");
/**
* Intitialize excel objects and open the files
*/
require_once "Spreadsheet/Excel/Writer.php";
// path to nihpd dir where db reports are stored
$pathToDir = "/data/web/prod/htdocs/nihpd/Database/Reports_Services/Reports/";
// new worksheet
$workbookSummary = new Spreadsheet_Excel_Writer($pathToDir . $filename . ".xls");
// Create the worksheet
$worksheetSummary =& $workbookSummary->addWorksheet('Summary');
// create format objects for the workbook
$formatTitle =& $workbookSummary->addFormat(array('font' => 'arial', 'size' => 10));
$formatSummary =& $workbookSummary->addFormat(array('font' => 'arial', 'size' => 10));
$formatSummaryBold =& $workbookSummary->addFormat(array('font' => 'arial', 'size' => 10));
// title
$formatTitle->setBold();
$formatTitle->setAlign('left');
// bold fields
$formatSummaryBold->setBold();
$formatSummaryBold->setAlign('center');
// data fields
$formatSummary->setRight(0.75);
/*
* build excel file
*/
// set the title and date
// setting them last so that spreadsheet would select
}
else
echo 'nix<br>';
var_dump($stg_obj);
exit;
*/
// Creating a workbook
$workbook = new Spreadsheet_Excel_Writer();
// sending HTTP headers
$workbook->send("LVPlanung.xls");
$workbook->setVersion(8);
// Creating a worksheet
$worksheet =& $workbook->addWorksheet($studiensemester_kurzbz);
$worksheet->setInputEncoding('utf-8');
//Formate Definieren
$format_bold =& $workbook->addFormat();
$format_bold->setBold();
$format_number =& $workbook->addFormat();
$format_number->setNumFormat('0,0.00');
$format_number_bold =& $workbook->addFormat();
$format_number_bold->setNumFormat('0,0.00');
$format_number_bold->setBold();
$zeile = 0;
$spalte = 0;
$worksheet->write($zeile, $spalte, "Studiengang", $format_bold);
$maxlength[$spalte] = 11;
$worksheet->write($zeile, ++$spalte, "Institut", $format_bold);
$maxlength[$spalte] = 8;
$worksheet->write($zeile, ++$spalte, "Koordinator", $format_bold);
$maxlength[$spalte] = 11;
$worksheet->write($zeile, ++$spalte, "Lektor", $format_bold);
}
$arr = getDays($time);
$fromdate = $arr['monday']['year'] . "-" . $arr['monday']['mon'] . "-" . $arr['monday']['mday'];
$todate = $arr['sunday']['year'] . "-" . $arr['sunday']['mon'] . "-" . $arr['sunday']['mday'];
$pathname = $fromdate . "-" . $todate;
if (!is_dir("userfiles/" . $pathname)) {
mkdir("userfiles/" . $pathname, 0777);
chmod("userfiles/" . $pathname, 0777);
}
if ($totalRows_rsUser > 0) {
// Show if recordset not empty
// creating spreadsheet
do {
$user_id = $row_rsUser['user_id'];
$workbook = new Spreadsheet_Excel_Writer('userfiles/' . $pathname . '/' . $user_id . '.xls');
$formatTop =& $workbook->addFormat(array('Size' => 14, 'Align' => 'left', 'Color' => 'black', 'FgColor' => 'white', 'Border' => 0));
$format =& $workbook->addFormat(array('Size' => 14, 'Align' => 'left', 'Color' => 'black', 'FgColor' => 'white', 'Border' => 1));
$format1 =& $workbook->addFormat(array('Size' => 11, 'Align' => 'left', 'Color' => 'black', 'FgColor' => 'white', 'Border' => 1));
$formatbold =& $workbook->addFormat(array('Size' => 11, 'Align' => 'left', 'Color' => 'black', 'FgColor' => 'white', 'Bold' => 1, 'Border' => 1));
$formatboldcenter =& $workbook->addFormat(array('Size' => 11, 'Align' => 'center', 'Color' => 'black', 'FgColor' => 'white', 'Bold' => 1, 'Border' => 1));
$formatborder =& $workbook->addFormat(array('Border' => 1));
$worksheet =& $workbook->addWorksheet();
$row = 0;
// first row
// row 1
$excel = "\r\n";
for ($x = 0; $x < 11; $x++) {
$worksheet->writeString($row, $x, "", $formatTop);
}
$row++;
for ($x = 0; $x < 11; $x++) {
$Addresses = new Addresses($Database);
# verify that user is logged in
$User->check_user_session();
# we dont need any errors!
//ini_set('display_errors', 0);
# fetch subnet details
$subnet = (array) $Subnets->fetch_subnet(null, $_GET['subnetId']);
# fetch all IP addresses in subnet
$addresses = $Addresses->fetch_subnet_addresses($_GET['subnetId'], "ip_addr", "asc");
# get all custom fields
$custom_fields = $Tools->fetch_custom_fields('ipaddresses');
# Create a workbook
$filename = "phpipam_subnet_export.xls";
$workbook = new Spreadsheet_Excel_Writer();
//formatting headers
$format_header =& $workbook->addFormat();
$format_header->setBold();
$format_header->setColor('black');
$format_header->setSize(12);
//format vlan
$format_vlan =& $workbook->addFormat();
$format_vlan->setColor('black');
$format_vlan->setSize(11);
//formatting titles
$format_title =& $workbook->addFormat();
$format_title->setColor('black');
$format_title->setFgColor(22);
//light gray
$format_title->setBottom(1);
$format_title->setTop(1);
$format_title->setAlign('left');
function cw_xls_get_serials($product_id, $serials)
{
global $config, $tables;
$xls = new Spreadsheet_Excel_Writer();
$xls->send("price_list.xls");
$xls->setVersion(8);
$format_header =& $xls->addFormat();
$format_header->setBold();
$sheet =& $xls->addWorksheet(cw_get_langvar_by_name('lbl_serial_numbers', null, false, true));
$sheet->setInputEncoding('UTF-8');
$header = array();
$header[] = array(cw_get_langvar_by_name('lbl_sku', null, false, true));
$header[] = array(cw_get_langvar_by_name('lbl_serial_number', null, false, true));
cw_xls_write_row($sheet, $format_header, 0, $header);
$sheet->setColumn(0, count($header), 20);
$product = cw_query_first("select productcode from {$tables['products']} where product_id='{$product_id}'");
if ($serials) {
$index = 1;
foreach ($serials as $serial) {
$body = array();
$body[] = array($product['productcode']);
$body[] = array($serial['sn']);
cw_xls_write_row($sheet, 0, $index, $body);
$index++;
}
}
$xls->close();
exit(0);
}
$graph->title = _USERSEPERUSERTYPE;
echo json_encode($graph);
exit;
}
} catch (Exception $e) {
handleAjaxExceptions($e);
}
} catch (Exception $e) {
handleNormalFlowExceptions($e);
}
if (isset($_GET['excel'])) {
require_once 'Spreadsheet/Excel/Writer.php';
$workBook = new Spreadsheet_Excel_Writer();
$workBook->setTempDir(G_UPLOADPATH);
$workBook->setVersion(8);
$formatExcelHeaders =& $workBook->addFormat(array('Size' => 14, 'Bold' => 1, 'HAlign' => 'left'));
$headerFormat =& $workBook->addFormat(array('border' => 0, 'bold' => '1', 'size' => '11', 'color' => 'black', 'fgcolor' => 22, 'align' => 'center'));
$formatContent =& $workBook->addFormat(array('HAlign' => 'left', 'Valign' => 'top', 'TextWrap' => 1));
$headerBigFormat =& $workBook->addFormat(array('HAlign' => 'center', 'FgColor' => 22, 'Size' => 16, 'Bold' => 1));
$titleCenterFormat =& $workBook->addFormat(array('HAlign' => 'center', 'Size' => 11, 'Bold' => 1));
$titleLeftFormat =& $workBook->addFormat(array('HAlign' => 'left', 'Size' => 11, 'Bold' => 1));
$fieldLeftFormat =& $workBook->addFormat(array('HAlign' => 'left', 'Size' => 10));
$fieldRightFormat =& $workBook->addFormat(array('HAlign' => 'right', 'Size' => 10));
$fieldCenterFormat =& $workBook->addFormat(array('HAlign' => 'center', 'Size' => 10));
//first tab
$workSheet =& $workBook->addWorksheet("System info");
$workSheet->setInputEncoding('utf-8');
$workSheet->setColumn(0, 0, 5);
$workSheet->write(1, 1, _BASICINFO . " (" . formatTimestamp($from) . " - " . formatTimestamp($to) . ")", $headerFormat);
$workSheet->mergeCells(1, 1, 1, 2);
$workSheet->setColumn(1, 3, 30);
function ExportToExcel($fileName, $sheetName, $rst)
{
$columnHeaders = array();
/* Creating a workbook */
$workbook = new Spreadsheet_Excel_Writer($fileName);
/* Creating a worksheet */
$worksheet =& $workbook->addWorksheet($sheetName);
$worksheet1 =& $workbook->addWorksheet("REG");
$worksheet2 =& $workbook->addWorksheet("MGT");
$worksheet3 =& $workbook->addWorksheet("RET");
$worksheet4 =& $workbook->addWorksheet("CNS");
$monthArray = array("01" => "Jan", "02" => "Feb", "03" => "Mar", "04" => "Apr", "05" => "May", "06" => "Jun", "07" => "Jul", "08" => "Aug", "09" => "Sep", "10" => "Oct", "11" => "Nov", "12" => "Dec");
$row = 0;
$col = 0;
$rowREG = 0;
$rowMGT = 0;
$rowRET = 0;
$rowCNS = 0;
$colREG = 0;
$colMGT = 0;
$colRET = 0;
$colCNS = 0;
//Set color
//setBgColor
$workbook->setCustomColor(12, 255, 151, 95);
$format =& $workbook->addFormat(array('Align' => 'center', 'Bold' => 1, 'Border' => 1));
$format->setFontFamily('Trebuchet MS');
$format->setFgColor(12);
$formatNormal =& $workbook->addFormat(array('Border' => 1, 'Align' => 'left'));
$formatNormal->setFontFamily('Trebuchet MS');
/* Freeze Panes */
$array = array(1, 0, 1, 6);
$worksheet->freezePanes($array);
$row = 0;
$col = 0;
if ($rst && !$rst->EOF) {
$columnHeaders = $rst->FieldTypesArray();
/* The actual data */
for ($i = 0; $i < count($columnHeaders); $i++) {
$worksheet->write($row, $col, $columnHeaders[$i]->name, $format);
$worksheet1->write($row, $col, $columnHeaders[$i]->name, $format);
$worksheet2->write($row, $col, $columnHeaders[$i]->name, $format);
$worksheet3->write($row, $col, $columnHeaders[$i]->name, $format);
$worksheet4->write($row, $col, $columnHeaders[$i]->name, $format);
$worksheet->setColumn($i, $i, 20);
$worksheet1->setColumn($i, $i, 20);
$worksheet2->setColumn($i, $i, 20);
$worksheet3->setColumn($i, $i, 20);
$worksheet4->setColumn($i, $i, 20);
$col++;
}
$rowREG++;
$rowMGT++;
$rowRET++;
$rowCNS++;
while (!$rst->EOF) {
$row++;
$col = 0;
$colREG = 0;
$colMGT = 0;
$colRET = 0;
$colCNS = 0;
$flag1 = 0;
$flag2 = 0;
$flag3 = 0;
$flag4 = 0;
$empNum = array();
for ($i = 0; $i < count($columnHeaders); $i++) {
$value = $rst->fields[$i];
$value2 = $value;
if (!isset($value) or $value == "") {
$worksheet->write($row, $col++, "", $formatNormal);
$value2 = "";
} else {
if (eregi("(<a href.*>)(.*)(</a>)", $value, $regs)) {
$worksheet->write($row, $col++, $regs[2], $formatNormal);
$value2 = $regs[2];
} else {
if (eregi("(<font.*>)(.*)(</font>)", $value, $regs)) {
$worksheet->write($row, $col++, $regs[2], $formatNormal);
$value2 = $regs[2];
} else {
if (ereg("([0-9]{2})(/)([0-9]{2})(/)([0-9]{4})", $value, $regs2)) {
$dateValue = $regs2[1] . "-" . $monthArray[$regs2[3]] . "-" . $regs2[5];
$worksheet->write($row, $col++, $dateValue, $formatNormal);
} else {
$worksheet->write($row, $col++, $value, $formatNormal);
}
}
}
if ($i == 0 && eregi("([a-zA-Z]{3})(.*)", $value2, $empNum)) {
$value2 = $empNum[2];
}
}
if ($empNum[1] == "REG") {
$worksheet1->write($rowREG, $colREG++, $value2, $formatNormal);
$flag1 = 1;
}
if ($empNum[1] == "MGT") {
$worksheet2->write($rowMGT, $colMGT++, $value2, $formatNormal);
$flag2 = 1;
}
if ($empNum[1] == "RET") {
$worksheet3->write($rowRET, $colRET++, $value2, $formatNormal);
$flag3 = 1;
}
if ($empNum[1] == "CNS") {
$worksheet4->write($rowCNS, $colCNS++, $value2, $formatNormal);
$flag4 = 1;
}
}
$rst->MoveNext();
if ($flag1) {
$rowREG++;
}
if ($flag2) {
$rowMGT++;
}
if ($flag3) {
$rowRET++;
}
if ($flag4) {
$rowCNS++;
}
}
$rst->close();
}
// Let's send the file
$workbook->close();
}
/**
* Формирует Excel отчет по действиям конкретного админа
*
* @param string $sAdminName имя админа
* @param string $sAdminSurname фамилия админа
* @param array $filter фильтр
*/
function printReport($sAdminName = '', $sAdminSurname = '', $filter = array())
{
// имя итогового файла
$sWorkTitle = 'Admin report.xls';
// подключаем pear
require_once 'Spreadsheet/Excel/Writer.php';
// создаем документ
$workbook = new Spreadsheet_Excel_Writer();
$workbook->setVersion(8);
// создаем лист
$worksheet =& $workbook->addWorksheet('1');
$worksheet->setInputEncoding('CP1251');
$worksheet->write(0, 0, 'Администратор ' . $sAdminName . ' ' . $sAdminSurname . ' [' . $log[0]['adm_login'] . ']');
$worksheet->write(1, 0, $GLOBALS['host'] . '/users/' . $log[0]['adm_login']);
$d_sty = array('NumFormat' => 'DD MMM, YYYY HH:MM:SS');
$td_sty = array('FontFamily' => 'Calibri', 'VAlign' => 'vequal_space', 'Align' => 'center', 'Border' => 1, 'BorderColor' => 'black');
$th_sty = array('FontFamily' => 'Arial', 'Size' => 10, 'Align' => 'center', 'Border' => 1, 'BorderColor' => 'black', 'Bold' => 1);
$format_top =& $workbook->addFormat($th_sty);
$format_td =& $workbook->addFormat($td_sty);
$format_date =& $workbook->addFormat(array_merge($td_sty, $d_sty));
$format_top->setTextWrap(1);
$aHeader = array('Действие', 'Объект', 'Ссылка', 'Дата');
for ($i = 0; $i < count($aHeader); $i++) {
$worksheet->write(3, $i, $aHeader[$i], $format_top);
}
// данные
$this->filter = $filter;
$log = $this->getLog($count, 1, 'general', 0, true);
if ($log) {
$nCnt = 1;
foreach ($log as $aOne) {
$sObjName = $aOne['object_name'] ? $aOne['object_name'] : '<без названия>';
$sObjLink = $aOne['object_link'] ? getAbsUrl($aOne['object_link']) : '';
$sDate = $aOne['act_time'] ? date('Y-m-d H:i:s', strtotime($aOne['act_time'])) : 'не известно';
$worksheet->write($nCnt + 3, 0, $aOne['act_name'] . ' ' . admin_log::$aObj[$aOne['obj_code']]['short'], $format_td);
$worksheet->write($nCnt + 3, 1, $sObjName, $format_td);
$worksheet->write($nCnt + 3, 2, $sObjLink, $format_td);
$worksheet->write($nCnt + 3, 3, $sDate, $format_date);
$nCnt++;
}
}
// отправляем на скачивание
$workbook->send($sWorkTitle);
// закрываем документ
$workbook->close();
}
require_once '../../include/functions.inc.php';
require_once '../../include/Excel/excel.php';
require_once '../../include/studiengang.class.php';
require_once '../../include/studiensemester.class.php';
require_once '../../include/mail.class.php';
$stsem = new studiensemester();
$semester_aktuell = $stsem->getaktorNext();
$file = 'lehrauftragsgesamtliste.xls';
// Creating a workbook
$workbook = new Spreadsheet_Excel_Writer($file);
$workbook->setVersion(8);
// Creating a worksheet
$worksheet =& $workbook->addWorksheet("Lektoren");
$worksheet->setInputEncoding('utf-8');
//Formate Definieren
$format_left =& $workbook->addFormat();
$format_left->setLeft(2);
$format_bold =& $workbook->addFormat();
$format_bold->setBold();
$format_bold_border =& $workbook->addFormat();
$format_bold_border->setBold();
$format_bold_border->setBorder(2);
$format_bold_center =& $workbook->addFormat();
$format_bold_center->setBold();
$format_bold_center->setAlign('center');
$format_bold_center->setBorder(2);
$format_number =& $workbook->addFormat();
$format_number->setNumFormat('0,0.00');
$format_number_bold =& $workbook->addFormat();
$format_number_bold->setNumFormat('0,0.00');
$format_number_bold->setBold();
/*Display heading*/
$qury_lang = $con->recordselect("SELECT * FROM users");
$sheet1 = array(array('User Name', 'User Email'));
$i = 1;
while ($fetch_Record = mysql_fetch_assoc($qury_lang)) {
/*echo '<pre>';
print_r($fetch_Record);
echo '</pre>';*/
$sheet3 = array();
array_push($sheet3, $fetch_Record["name"]);
array_push($sheet3, base64_decode($fetch_Record["emailAddress"]));
$sheet1[$i] = $sheet3;
$i++;
}
$workbook = new Spreadsheet_Excel_Writer();
$format_und =& $workbook->addFormat();
$format_und->setBottom(2);
//thick
$format_und->setBold();
$format_und->setColor('black');
$format_und->setFontFamily('Arial');
$format_und->setSize(8);
$format_reg =& $workbook->addFormat();
$format_reg->setColor('black');
$format_reg->setFontFamily('Arial');
$format_reg->setSize(8);
$arr = array('user_detail' => $sheet1);
foreach ($arr as $wbname => $rows) {
$rowcount = count($rows);
$colcount = count($rows[0]);
$worksheet =& $workbook->addWorksheet($wbname);
$teaid = $_GET['teaid'];
$name = $_GET['name'];
$term = $_GET['nterm'];
//$nroom='育秀111';
//echo $nroom;文件gbk编码，数据库utf8输入utf8;
//$term='2015-2016第一学期';
if ($teaid == '' || $term == '') {
exit('Invalid request');
}
$cou_sql = "select * from `tea_course` where tea_id='" . $teaid . "' and cou_term='" . $term . "' limit 1";
//echo $cou_sql;/**
$cou_sql = mysql_fetch_assoc(mysql_query($cou_sql));
$filenm = iconv("utf8", "gb2312", $name) . "老师课程表";
$workbook = new Spreadsheet_Excel_Writer();
//$workbook->setVersion(8);
$format_title =& $workbook->addFormat();
$format_title->setBold();
$format_title->setSize('16');
$format_title->setAlign('merge');
$format_border =& $workbook->addFormat();
$format_border->setBorder(1);
$format_border->setAlign('top');
$format_border->setUnLocked();
$format_border->setTextWrap();
$format_border_l =& $workbook->addFormat();
$format_border_l->setBorder(1);
$format_border_l->setAlign('vcenter');
$format_border_l->setUnLocked();
$format_border_l->setTextWrap();
$format_border_t =& $workbook->addFormat();
$format_border_t->setTop(2);
}
}
}
if (isset($_REQUEST['format']) && $_REQUEST['format'] == 'xls') {
// Creating a workbook
$workbook = new Spreadsheet_Excel_Writer();
// sending HTTP headers
$workbook->send($p->t('zeitsperre/zeitsperren') . ".xls");
$workbook->setVersion(8);
// Creating a worksheet
$worksheet =& $workbook->addWorksheet($p->t('zeitsperre/zeitsperren'));
$worksheet->setInputEncoding('utf-8');
$worksheet->setZoom(85);
$worksheet->freezePanes(array(1, 0, 1, 0));
//Formate Definieren
$format_header =& $workbook->addFormat();
$format_header->setBold();
$format_header->setAlign("center");
$format_header->setVAlign('vcenter');
$format_header->setTextWrap();
$format_header_feiertag =& $workbook->addFormat();
$format_header_feiertag->setBold();
$format_header_feiertag->setAlign("center");
$format_header_feiertag->setVAlign('vcenter');
$format_header_feiertag->setFgColor('yellow');
$format_header_feiertag->setTextWrap();
$format_namen =& $workbook->addFormat();
$format_namen->setVAlign('top');
$format_legende =& $workbook->addFormat();
$format_legende->setBold();
$format_legende->setAlign("left");
if ($tila == 'ohita' or $hylkaa > 0) {
$api_status = FALSE;
$lue_data_virheelliset_rivit[$rivilaskuri - 1] = $excelrivit[$rivilaskuri - 1];
}
}
lue_data_echo("<br><font class='message'>" . t("Päivitettiin") . " {$lask} " . t("riviä") . "!</font><br><br>");
// Kirjoitetaan LOG fileen lopputagi, jotta tiedetään että ajo on valmis
if ($lue_data_output_file != "") {
lue_data_echo("## LUE-DATA-EOF ##");
// Kirjoitetaan vielä loppuun virheelliset rivit
if (count($lue_data_virheelliset_rivit) > 0) {
if (include 'Spreadsheet/Excel/Writer.php') {
$workbook = new Spreadsheet_Excel_Writer($lue_data_err_file);
$workbook->setVersion(8);
$worksheet = $workbook->addWorksheet(t('Virheelliset rivit'));
$format_bold = $workbook->addFormat();
$format_bold->setBold();
$excelrivi = 0;
$excelsarake = 0;
$worksheet->write($excelrivi, $excelsarake++, ucfirst(t("Alkuperäinen rivinumero")), $format_bold);
foreach ($excelrivit[0] as $otsikko) {
$worksheet->write($excelrivi, $excelsarake++, ucfirst($otsikko), $format_bold);
}
$excelrivi++;
$excelsarake = 0;
foreach ($lue_data_virheelliset_rivit as $rivinro => $lue_data_virheellinen_rivi) {
$worksheet->writeNumber($excelrivi, $excelsarake++, $rivinro + 1);
foreach ($lue_data_virheellinen_rivi as $lue_data_virheellinen_sarake) {
$worksheet->writeString($excelrivi, $excelsarake++, $lue_data_virheellinen_sarake);
}
$excelrivi++;
public static function exportToXls($data, $file = false, $alignments = array())
{
require_once 'Spreadsheet/Excel/Writer.php';
$workBook = new Spreadsheet_Excel_Writer($file);
$workBook->setTempDir(G_UPLOADPATH);
$workBook->setVersion(8);
$workSheet =& $workBook->addWorksheet('info');
$workSheet->setInputEncoding('utf-8');
$columnIndex = 0;
foreach (current($data) as $key => $value) {
$maxColumnWidths[$columnIndex][] = mb_strlen($key);
$alignments[$columnIndex] ? $align = $alignments[$columnIndex] : ($align = 'left');
$workSheet->write(0, $columnIndex++, $key, $workBook->addFormat(array('HAlign' => $align, 'Size' => 11, 'Bold' => 1)));
}
$rowIndex = 1;
foreach ($data as $rowData) {
$columnIndex = 0;
foreach ($rowData as $cell) {
$maxColumnWidths[$columnIndex][] = mb_strlen($cell);
if ($alignments[$columnIndex]) {
$align = $alignments[$columnIndex];
$workSheet->write($rowIndex, $columnIndex++, $cell, $workBook->addFormat(array('HAlign' => $align)));
} else {
$workSheet->write($rowIndex, $columnIndex++, $cell);
}
}
$rowIndex++;
}
foreach ($maxColumnWidths as $columnIndex => $widths) {
$workSheet->setColumn($columnIndex, $columnIndex, max($widths) + 2);
}
$workBook->close();
if (!$file) {
$workBook->send('export.xls');
}
}
include '../opendb.php';
require_once 'Spreadsheet/Excel/Writer.php';
$workbook = new Spreadsheet_Excel_Writer();
$sql = $_SESSION['sql'];
$result = mysql_query($sql) or die(mysql_error());
//first of all unset these variables
unset($_SESSION['report_header']);
unset($_SESSION['report_values']);
//note that the header contain the three columns and its a array
$_SESSION['report_header'] = array("productid", "productname", "stockno", "partno", "sku", "qty", "img", "catname", " manufacturer", "flag");
// now the excel data field should be two dimentational array with all column
//loop through the needed cycle
$filename = "jjorders_" . date('dS M Y') . "." . "xls";
$worksheet = $workbook->addWorksheet();
$titleText = 'JJ ELECTRONICS ORDER FORM ' . date('dS M Y');
$titleFormat = $workbook->addFormat();
$titleFormat->setFontFamily('Helvetica');
$titleFormat->setBold();
$titleFormat->setColor('Black');
$titleFormat->setPattern(1);
$titleFormat->setFgColor("navy");
$titleFormat->setSize('20');
$worksheet->write(0, 2, $titleText, $titleFormat);
$worksheet->write(0, 3, '', $titleFormat);
$worksheet->write(0, 4, '', $titleFormat);
$worksheet->write(0, 5, '', $titleFormat);
$titleFormat->setSize('14');
$titleFormat->setFgColor('grey');
$titleFormat->setColor('white');
$worksheet->setColumn(0, 0, 15, 0);
$worksheet->setColumn(1, 1, 40, 0);
# search subnets
if (@$_REQUEST['subnets'] == "on") {
$result_subnets = $Tools->search_subnets($search_term, $search_term_edited['high'], $search_term_edited['low'], $_REQUEST['ip']);
}
# search vlans
if (@$_REQUEST['vlans'] == "on") {
$result_vlans = $Tools->search_vlans($search_term);
}
/*
* Write xls
*********************/
// Create a workbook
$filename = _("phpipam_search_export_") . $search_term . ".xls";
$workbook = new Spreadsheet_Excel_Writer();
//formatting titles
$format_title =& $workbook->addFormat();
$format_title->setColor('black');
$format_title->setFgColor(22);
//light gray
$format_title->setBottom(2);
$format_title->setAlign('left');
$lineCount = 0;
//for line change
$m = 0;
//for section change
/* -- Create a worksheet for addresses -- */
if (sizeof($result_addresses) > 0) {
$worksheet =& $workbook->addWorksheet(_('Addresses'));
//write headers
$x = 0;
$worksheet->write($lineCount, $x, _('ip address'), $format_title);
//prepare the header response, based on the export type
if ($export_type == 'xls') {
require 'Spreadsheet/Excel/Writer.php';
// Creating a workbook
$workbook = new Spreadsheet_Excel_Writer();
$workbook->setTempDir($mf_settings['upload_dir']);
// sending HTTP headers
$workbook->send("{$clean_form_name}.xls");
if (function_exists('iconv')) {
$workbook->setVersion(8);
}
// Creating a worksheet
$clean_form_name = substr($clean_form_name, 0, 30);
//must be less than 31 characters
$worksheet =& $workbook->addWorksheet($clean_form_name);
$format_bold =& $workbook->addFormat();
$format_bold->setBold();
$format_bold->setFgColor(22);
$format_bold->setPattern(1);
$format_bold->setBorder(1);
if (function_exists('iconv')) {
$worksheet->setInputEncoding('UTF-8');
}
$format_wrap = $workbook->addFormat();
$format_wrap->setTextWrap();
$i = 0;
foreach ($column_labels as $label) {
$worksheet->write(0, $i, $label, $format_bold);
$i++;
}
} else {
/**
* @param boolean $echo
* @return boolean
*/
public function export($echo = true)
{
static $date_formats = array('MYSQLI_TYPE_DATE' => 'yyyy-mm-dd', 'MYSQLI_TYPE_DATETIME' => 'yyyy-mm-dd hh:mm:ss', 'MYSQLI_TYPE_NEWDATE' => 'yyyy-mm-dd', 'MYSQLI_TYPE_TIME' => 'hh:mm:ss', 'MYSQLI_TYPE_TIMESTAMP' => 'yyyy-mm-dd hh:mm:ss');
$fields = $this->getFieldCount();
if (!$fields) {
return false;
}
$addtitles = $this->titles;
$filename = $this->filename;
$freezepanes = $this->freezepanes;
$formats = $this->formats;
$setwidths = $this->widths;
$sheetname = $this->sheetname;
$widths = array_fill(0, $fields, 0);
$path = sys_get_temp_dir() . '/' . $filename;
# quiet logs
$old_error_reporting = error_reporting(error_reporting() & ~E_NOTICE & ~E_STRICT & ~E_DEPRECATED);
$workbook = new Spreadsheet_Excel_Writer($path);
$workbook->setTempDir(sys_get_temp_dir());
$formats = array();
$title_formats = array();
foreach ($this->metadata as $metadata) {
$format = $workbook->addFormat();
$format->setAlign($metadata->align);
$title_formats[] = $format;
$format = $workbook->addFormat();
$format->setAlign($metadata->align);
while (true) {
if ($metadata->format && $metadata->numeric) {
$s = '#,##0';
if ($metadata->places > 0) {
$s .= '.' . str_repeat('0', $metadata->places);
}
if ($metadata->field_type == 'MYSQLI_TYPE_YEAR') {
$s = '#0';
}
$format->setNumFormat($s);
break;
}
if (isset($date_formats[$metadata->field_type])) {
$format->setNumFormat($date_formats[$metadata->field_type]);
break;
}
break;
}
$formats[] = $format;
}
$worksheet = $workbook->addWorksheet($sheetname);
$worksheet->setInputEncoding('UTF-8');
$row = 0;
$col = 0;
if ($addtitles) {
$field_names = $this->getFieldNames();
foreach ($field_names as $field_name) {
$strlen = $this->write($worksheet, $row, $col, $field_name, $title_formats[$col]);
$widths[$col] = max($widths[$col], $strlen);
++$col;
}
++$row;
$col = 0;
}
$freezepanes = $freezepanes && $addtitles ? array(1, 0, 1, 0) : array();
if (count($freezepanes)) {
$worksheet->freezePanes($freezepanes);
$worksheet->setSelection($freezepanes[0], $freezepanes[1], $freezepanes[2], $freezepanes[3]);
}
$hasNumIndex = true;
$row_count = $this->getRowCount();
for ($row_num = 0; $row_num < $row_count; ++$row_num) {
$the_row = $this->getRow($row_num);
if ($hasNumIndex) {
for ($i = 0; $i < $fields; ++$i) {
$v = $the_row[$i];
#$strlen = $this->write($worksheet, $row, $col, $v, $formats);
$strlen = $this->writex($worksheet, $row, $col, $v, $formats[$i], $this->metadata[$i]);
$widths[$col] = max($widths[$col], $strlen);
++$col;
}
} else {
foreach ($the_row as $k => $v) {
#$strlen = $this->write($worksheet, $row, $col, $v, $formats);
$strlen = $this->writex($worksheet, $row, $col, $v, $formats[$i], $this->metadata[$i]);
$widths[$col] = max($widths[$col], $strlen);
++$col;
}
}
++$row;
$col = 0;
}
if ($setwidths) {
for ($i = 0; $i < $fields; ++$i) {
$w = 0.29 + $widths[$i];
$worksheet->setColumn($i, $i, $w);
}
}
$this->tempfile = $path;
$rv = false;
if (is_file($path)) {
$rv = file_get_contents($path);
}
$workbook->close();
error_reporting($old_error_reporting);
if (is_file($path)) {
$rv = file_get_contents($path);
unlink($path);
}
if ($echo) {
$workbook->send($filename);
echo $rv;
}
return $rv;
}
/**
* @deprecated since version 70171
* Проблема с Office 2010 - #0028695, теперь используем generate2
* Основной метод генерации файла
*
* @param type $payouts Массив с данными по выплатам
* @param type $paybacks Массив с данными по возвратам
*
* @return type
*/
public function generate($payouts, $paybacks)
{
$file_name = $this->getTempFileName();
$workbook = new Spreadsheet_Excel_Writer($this->temp_file_dir . $file_name);
$workbook->setVersion(8);
$worksheet = $workbook->addWorksheet('');
$worksheet->setInputEncoding('windows-1251');
$fmtT =& $workbook->addFormat(array('VAlign' => 'top', 'Align' => 'center', 'Size' => '10', 'Bold' => 1));
$fmtT->setTextWrap();
$fmtM =& $workbook->addFormat(array('VAlign' => 'top', 'Align' => 'left', 'Size' => '10'));
$fmtM->setTextWrap();
$worksheet->write(0, 0, '№', $fmtT);
$worksheet->write(0, 1, 'Номер сделки', $fmtT);
$worksheet->write(0, 2, 'Исполнитель (ФИО)', $fmtT);
$worksheet->write(0, 3, 'Сумма выплаты', $fmtT);
$worksheet->write(0, 4, 'Реквизиты', $fmtT);
$worksheet->write(0, 5, 'Заказчик (ФИО)', $fmtT);
$worksheet->write(0, 6, "Информационное письмо\nисполнителю", $fmtT);
$worksheet->setRow(0, 30);
$worksheet->setColumn(0, 0, 5);
$worksheet->setColumn(1, 1, 20);
$worksheet->setColumn(2, 2, 40);
$worksheet->setColumn(3, 3, 25);
$worksheet->setColumn(4, 4, 70);
$worksheet->setColumn(5, 5, 40);
$worksheet->setColumn(6, 6, 30);
$n = 1;
foreach ($payouts as $key => $el) {
$reqv = $this->formatter->details(array('uid' => $el['frl_id'], 'email' => $el['email']));
$lines = substr_count($reqv, "\n");
$url = $this->getUrl($el['path'], $el['fname']);
$worksheet->write($n, 0, $key + 1, $fmtM);
$worksheet->write($n, 1, $this->formatOrderName($el['order_id']), $fmtM);
$worksheet->write($n, 2, $el['frl_fio'], $fmtM);
$worksheet->write($n, 3, $el['price'], $fmtM);
$worksheet->write($n, 4, $reqv, $fmtM);
$worksheet->write($n, 5, $el['emp_fio'], $fmtM);
$worksheet->writeUrl($n, 6, $url, $url, $fmtM);
$worksheet->setRow($n, $lines * 12);
++$n;
}
$n += 3;
$worksheet->write($n, 0, '№', $fmtT);
$worksheet->write($n, 1, 'Номер сделки', $fmtT);
$worksheet->write($n, 2, 'Исполнитель (ФИО)', $fmtT);
$worksheet->write($n, 3, 'Сумма выплаты', $fmtT);
$worksheet->write($n, 4, 'Реквизиты', $fmtT);
$worksheet->write($n, 5, 'Заказчик (ФИО)', $fmtT);
$worksheet->write($n, 6, 'Отчет об арбитражном рассмотрении', $fmtT);
$worksheet->setRow($n, 30);
++$n;
foreach ($paybacks as $key => $el) {
$reqv = $this->formatter->details(array('uid' => $el['emp_id'], 'email' => $el['email']));
$lines = substr_count($reqv, "\n");
$url = $this->getUrl($el['path'], $el['fname']);
$worksheet->write($n, 0, $key + 1, $fmtM);
$worksheet->write($n, 1, $this->formatOrderName($el['order_id']), $fmtM);
$worksheet->write($n, 2, $el['frl_fio'], $fmtM);
$worksheet->write($n, 3, $el['price'], $fmtM);
$worksheet->write($n, 4, $reqv, $fmtM);
$worksheet->write($n, 5, $el['emp_fio'], $fmtM);
$worksheet->writeUrl($n, 6, $url, $url, $fmtM);
$worksheet->setRow($n, $lines * 12);
++$n;
}
$workbook->close();
return $this->path_payout . $this->uploadFile($file_name);
}
//Set the size of all the columns
$worksheet->setColumn(0, 0, 13.29);
$worksheet->setColumn(1, 1, 4.29);
$worksheet->setColumn(2, 7, 3.29);
$worksheet->setColumn(8, 9, 4.29);
$worksheet->setColumn(10, 11, 3.29);
$worksheet->setColumn(12, 12, 4.29);
$worksheet->setColumn(13, 14, 3.29);
$worksheet->setColumn(15, 16, 4.29);
$worksheet->setColumn(17, 18, 3.29);
$worksheet->setColumn(19, 20, 4.29);
$worksheet->setColumn(21, 22, 3.29);
$worksheet->setColumn(23, 23, 4.29);
$worksheet->setColumn(24, 24, 6.0);
//Header Formatting
$header =& $workbook->addFormat();
$header->setBold();
$header->setColor('black');
$header->setSize('10');
//Write the headers
$worksheet->write(0, 3, 'MUSIC PERFORMANCE', $header);
$worksheet->write(0, 11, 'VISUAL PERFORMANCE', $header);
$worksheet->write(0, 19, 'GENERAL EFFECT', $header);
//Secondary Header Formatting
$header2 =& $workbook->addFormat();
$header2->setColor('black');
$header2->setSize('10');
//Write Secondary Headers
$worksheet->write(1, 2, 'Individual', $header2);
$worksheet->write(1, 6, ' Ensemble', $header2);
$worksheet->write(1, 10, ' Individual', $header2);
/**
* function iCal2xls
*
* Convert iCal file to xls format and send file to browser (default) or save xls file to disk
* Definition iCal : rcf2445, http://kigkonsult.se/downloads/index.php#rfc
* Using iCalcreator: http://kigkonsult.se/downloads/index.php#iCalcreator
* Based on PEAR Spreadsheet_Excel_Writer-0.9.1 (and OLE-1.0.0RC1)
* to be installed as
* pear install channel://pear.php.net/OLE-1.0.0RC1
* pear install channel://pear.php.net/Spreadsheet_Excel_Writer-0.9.1
*
* @author Kjell-Inge Gustafsson <*****@*****.**>
* @since 3.0 - 2011-12-21
* @param object $calendar opt. iCalcreator calendar instance
* @return bool returns FALSE when error
*/
public function iCal2xls($calendar = FALSE)
{
$timeexec = array('start' => microtime(TRUE));
if ($this->log) {
$this->log->log(' ********** START **********', PEAR_LOG_NOTICE);
}
/** check input/output directory and filename */
$inputdirFile = $outputdirFile = '';
$inputFileParts = $outputFileParts = array();
$remoteInput = $remoteOutput = FALSE;
if ($calendar) {
$inputdirFile = $calendar->getConfig('DIRFILE');
$inputFileParts = pathinfo($inputdirFile);
$inputFileParts['dirname'] = realpath($inputFileParts['dirname']);
if ($this->log) {
$this->log->log('fileParts:' . var_export($inputFileParts, TRUE), PEAR_LOG_DEBUG);
}
} elseif (FALSE === $this->_fixIO('input', 'ics', $inputdirFile, $inputFileParts, $remoteInput)) {
if ($this->log) {
$this->log->log(number_format(microtime(TRUE) - $timeexec['start'], 5) . ' sec', PEAR_LOG_ERR);
$this->log->log("ERROR 2, invalid input ({$inputdirFile})", PEAR_LOG_ERR);
$this->log->flush();
}
return FALSE;
}
if (FALSE === $this->_fixIO('output', FALSE, $outputdirFile, $outputFileParts, $remoteOutput)) {
if (FALSE === $this->setConfig('outputfilename', $inputFileParts['filename'] . '.xls')) {
if ($this->log) {
$this->log->log(number_format(microtime(TRUE) - $timeexec['start'], 5) . ' sec', PEAR_LOG_ERR);
$this->log->log('ERROR 3, invalid output (' . $inputFileParts['filename'] . '.csv)', PEAR_LOG_ERR);
$this->log->flush();
}
return FALSE;
}
$outputdirFile = $this->getConfig('outputdirectory') . DIRECTORY_SEPARATOR . $inputFileParts['filename'] . '.xls';
$outputFileParts = pathinfo($outputdirFile);
if ($this->log) {
$this->log->log("output set to '{$outputdirFile}'", PEAR_LOG_INFO);
}
}
if ($this->log) {
$this->log->log("INPUT..FILE:{$inputdirFile}", PEAR_LOG_NOTICE);
$this->log->log("OUTPUT.FILE:{$outputdirFile}", PEAR_LOG_NOTICE);
}
$save = $this->getConfig('save');
if ($calendar) {
$calnl = $calendar->getConfig('nl');
} else {
/** iCalcreator set config, read and parse input iCal file */
$calendar = new vcalendar();
if (FALSE !== ($unique_id = $this->getConfig('unique_id'))) {
$calendar->setConfig('unique_id', $unique_id);
}
$calnl = $calendar->getConfig('nl');
if ($remoteInput) {
if (FALSE === $calendar->setConfig('url', $inputdirFile)) {
if ($this->log) {
$this->log->log("ERROR 3 INPUT FILE:'{$inputdirFile}' iCalcreator: invalid url", 3);
}
return FALSE;
}
} else {
if (FALSE === $calendar->setConfig('directory', $inputFileParts['dirname'])) {
if ($this->log) {
$this->log->log("ERROR 4 INPUT FILE:'{$inputdirFile}' iCalcreator: invalid directory: '" . $inputFileParts['dirname'] . "'", 3);
$this->log->flush();
}
return FALSE;
}
if (FALSE === $calendar->setConfig('filename', $inputFileParts['basename'])) {
if ($this->log) {
$this->log->log("ERROR 5 INPUT FILE:'{$inputdirFile}' iCalcreator: invalid filename: '" . $inputFileParts['basename'] . "'", 3);
$this->log->flush();
}
return FALSE;
}
}
if (FALSE === $calendar->parse()) {
if ($this->log) {
$this->log->log("ERROR 6 INPUT FILE:'{$inputdirFile}' iCalcreator parse error", 3);
$this->log->flush();
}
return FALSE;
}
}
// end if( !$calendar )
$timeexec['fileOk'] = microtime(TRUE);
if (!function_exists('iCaldate2timestamp')) {
function iCaldate2timestamp($d)
{
if (6 > count($d)) {
return mktime(0, 0, 0, $d['month'], $d['day'], $d['year']);
} else {
return mktime($d['hour'], $d['min'], $d['sec'], $d['month'], $d['day'], $d['year']);
}
}
}
if (!function_exists('fixiCalString')) {
function fixiCalString($s)
{
global $calnl;
$s = str_replace('\\,', ',', $s);
$s = str_replace('\\;', ';', $s);
$s = str_replace('\\n ', chr(10), $s);
$s = str_replace('\\\\', '\\', $s);
$s = str_replace("{$calnl}", chr(10), $s);
return utf8_decode($s);
}
}
/** Creating a workbook */
require_once 'Spreadsheet/Excel/Writer.php';
if ($save) {
$workbook = new Spreadsheet_Excel_Writer($outputdirFile);
} else {
$workbook = new Spreadsheet_Excel_Writer();
}
$workbook->setVersion(8);
// Use Excel97/2000 Format
/** opt. sending HTTP headers */
if (!$save) {
$workbook->send($outputFileParts['basename']);
}
/** Creating a worksheet */
$worksheet =& $workbook->addWorksheet($inputFileParts['filename']);
/** fix formats */
$format_bold =& $workbook->addFormat();
$format_bold->setBold();
$timeexec['wrkbkOk'] = microtime(TRUE);
/** info rows */
$row = -1;
$worksheet->writeString(++$row, 0, 'kigkonsult.se', $format_bold);
$worksheet->writeString($row, 1, ICALCREATOR_VERSION, $format_bold);
$worksheet->writeString($row, 2, ICALCNVVERSION . ' iCal2xls', $format_bold);
$worksheet->writeString($row, 3, date('Y-m-d H:i:s'));
$filename = $remoteInput ? $inputdirFile : $inputFileParts['basename'];
$worksheet->writeString(++$row, 0, 'iCal input', $format_bold);
$worksheet->writeString($row, 1, $filename);
$worksheet->writeString($row, 2, 'xls output', $format_bold);
$worksheet->writeString($row, 3, $outputFileParts['basename']);
if (FALSE !== ($prop = $calendar->getProperty('CALSCALE'))) {
$worksheet->writeString(++$row, 0, 'CALSCALE', $format_bold);
$worksheet->writeString($row, 1, $prop);
}
if (FALSE !== ($prop = $calendar->getProperty('METHOD'))) {
$worksheet->writeString(++$row, 0, 'METHOD', $format_bold);
$worksheet->writeString($row, 1, $prop);
}
while (FALSE !== ($xprop = $calendar->getProperty())) {
$worksheet->writeString(++$row, 0, $xprop[0], $format_bold);
$worksheet->writeString($row, 1, $xprop[1]);
}
$timeexec['infoOk'] = microtime(TRUE);
if (FALSE === ($propsToSkip = $this->getConfig('skip'))) {
$propsToSkip = array();
}
/** fix property order list */
$proporderOrg = array();
for ($key = 2; $key < 99; $key++) {
if (FALSE !== ($value = $this->getConfig($key))) {
$proporderOrg[$value] = $key;
if ($this->log) {
$this->log->log("{$value} in column {$key}", 7);
}
}
}
/** fix vtimezone property order list */
$proporder = $proporderOrg;
$proporder['TYPE'] = 0;
$proporder['ORDER'] = 1;
$props = array('TZID', 'LAST-MODIFIED', 'TZURL', 'DTSTART', 'TZOFFSETTO', 'TZOFFSETFROM', 'COMMENT', 'RRULE', 'RDATE', 'TZNAME');
$pix = 2;
foreach ($props as $prop) {
if (isset($proporder[$prop])) {
continue;
}
if (in_array($prop, $propsToSkip)) {
if ($this->log) {
$this->log->log("'{$prop}' removed from output", 7);
}
continue;
}
while (in_array($pix, $proporder)) {
$pix++;
}
$proporder[$prop] = $pix++;
}
/** remove unused properties from and add x-props to property order list */
$maxpropix = 11;
if ($maxpropix != count($proporder) - 1) {
$maxpropix = count($proporder) - 1;
}
$compsinfo = $calendar->getConfig('compsinfo');
$potmp = array();
$potmp[0] = 'TYPE';
$potmp[1] = 'ORDER';
foreach ($compsinfo as $cix => $compinfo) {
if ('vtimezone' != $compinfo['type']) {
continue;
}
$comp = $calendar->getComponent($compinfo['ordno']);
foreach ($compinfo['props'] as $propName => $propcnt) {
if (!in_array($propName, $potmp) && isset($proporder[$propName])) {
$potmp[$proporder[$propName]] = $propName;
} elseif ('X-PROP' == $propName) {
while ($xprop = $comp->getProperty()) {
if (!in_array($xprop[0], $potmp)) {
$maxpropix += 1;
$potmp[$maxpropix] = $xprop[0];
}
// end if
}
// end while xprop
}
// end X-PROP
}
// end $compinfo['props']
if (isset($compinfo['sub'])) {
foreach ($compinfo['sub'] as $compinfo2) {
foreach ($compinfo2['props'] as $propName => $propcnt) {
if (!in_array($propName, $potmp) && isset($proporder[$propName])) {
$potmp[$proporder[$propName]] = $propName;
} elseif ('X-PROP' == $propName) {
$scomp = $comp->getComponent($compinfo2['ordno']);
while ($xprop = $scomp->getProperty()) {
if (!in_array($xprop[0], $potmp)) {
$maxpropix += 1;
$potmp[$maxpropix] = $xprop[0];
}
// end if
}
// end while xprop
}
// end X-PROP
}
// end $compinfo['sub']['props']
}
// end foreach( $compinfo['sub']
}
// end if( isset( $compinfo['sub']
}
// end foreach compinfo - vtimezone
ksort($potmp, SORT_NUMERIC);
$proporder = array_flip(array_values($potmp));
if ($this->log) {
$this->log->log("timezone proporder=" . implode(',', array_flip($proporder)), 7);
}
/** create vtimezone info */
if (2 < count($proporder)) {
$row += 1;
/** create vtimezone header row */
foreach ($proporder as $propName => $col) {
if (isset($this->config[$propName])) {
$worksheet->writeString($row, $col, $this->config[$propName], $format_bold);
// check map of userfriendly name to iCal property name
if ($this->log) {
$this->log->log("header row, col={$col}: {$propName}, replaced by " . $this->config[$propName], 7);
}
} else {
$worksheet->writeString($row, $col, $propName, $format_bold);
}
}
$allowedProps = array('VTIMEZONE' => array('TZID', 'LAST-MODIFIED', 'TZURL'), 'STANDARD' => array('DTSTART', 'TZOFFSETTO', 'TZOFFSETFROM', 'COMMENT', 'RDATE', 'RRULE', 'TZNAME'), 'DAYLIGHT' => array('DTSTART', 'TZOFFSETTO', 'TZOFFSETFROM', 'COMMENT', 'RDATE', 'RRULE', 'TZNAME'));
/** create vtimezone data rows */
foreach ($compsinfo as $cix => $compinfo) {
if ('vtimezone' != $compinfo['type']) {
continue;
}
$row += 1;
$worksheet->writeString($row, $proporder['TYPE'], $compinfo['type']);
$worksheet->writeString($row, $proporder['ORDER'], $compinfo['ordno']);
$comp = $calendar->getComponent($compinfo['ordno']);
foreach ($proporder as $propName => $col) {
if ('TYPE' == $propName || 'ORDER' == $propName) {
continue;
}
if ('X-' == substr($propName, 0, 2)) {
continue;
}
if (!in_array($propName, $allowedProps['VTIMEZONE'])) {
// check if component allows property
if ($this->log) {
$this->log->log("ERROR 7, INPUT FILE:'{$inputdirFile}' iCalcreator: unvalid property for component '" . $compinfo['type'] . "': '{$propName}'", PEAR_LOG_INFO);
}
continue;
}
if (isset($compinfo['props'][$propName])) {
if ('LAST-MODIFIED' == $propName) {
$fcn = 'createLastModified';
} else {
$fcn = 'create' . strtoupper(substr($propName, 0, 1)) . strtolower(substr($propName, 1));
}
if (!method_exists($comp, $fcn)) {
if ($this->log) {
$this->log->log('ERROR 8 INPUT FILE:"' . $filename . '" iCalcreator: unknown property: "' . $propName . '" (' . $fcn . ')', PEAR_LOG_INFO);
}
continue;
}
$output = str_replace("{$calnl} ", '', rtrim($comp->{$fcn}()));
$output = str_replace($propName . ';', '', $output);
$output = str_replace($propName . ':', '', $output);
$worksheet->writeString($row, $proporder[$propName], fixiCalString($output));
}
}
// end foreach( $proporder
if (isset($compinfo['props']['X-PROP'])) {
while ($xprop = $comp->getProperty()) {
$output = str_replace("{$calnl} ", '', rtrim($xprop[1]));
$worksheet->writeString($row, $proporder[$xprop[0]], fixiCalString($output));
}
}
if (isset($compinfo['sub'])) {
foreach ($compinfo['sub'] as $compinfo2) {
$row += 1;
$worksheet->writeString($row, $proporder['TYPE'], $compinfo2['type']);
$worksheet->writeString($row, $proporder['ORDER'], $compinfo['ordno'] . ':' . $compinfo2['ordno']);
$scomp = $comp->getComponent($compinfo2['ordno']);
foreach ($proporder as $propName => $col) {
if ('TYPE' == $propName || 'ORDER' == $propName) {
continue;
}
if ('X-' == substr($propName, 0, 2)) {
continue;
}
if (!in_array($propName, $allowedProps[strtoupper($compinfo2['type'])])) {
// check if component allows property
if ($this->log) {
$this->log->log("ERROR 9, INPUT FILE:'{$inputdirFile}' iCalcreator: unvalid property for component '" . $compinfo2['type'] . "': '{$propName}'", PEAR_LOG_INFO);
}
continue;
}
if (isset($compinfo2['props'][$propName])) {
$fcn = 'create' . strtoupper(substr($propName, 0, 1)) . strtolower(substr($propName, 1));
if (!method_exists($scomp, $fcn)) {
if ($this->log) {
$this->log->log('ERROR 10 INPUT FILE:"' . $filename . '" iCalcreator: unknown property: "' . $propName . '" (' . $fcn . ')', PEAR_LOG_INFO);
}
continue;
}
$output = str_replace("{$calnl} ", '', rtrim($scomp->{$fcn}()));
$output = str_replace($propName . ';', '', $output);
$output = str_replace($propName . ':', '', $output);
$worksheet->writeString($row, $proporder[$propName], fixiCalString($output));
}
}
// end foreach( $proporder
if (isset($compinfo2['props']['X-PROP'])) {
while ($xprop = $scomp->getProperty()) {
$output = str_replace("{$calnl} ", '', rtrim($xprop[1]));
$worksheet->writeString($row, $proporder[$xprop[0]], fixiCalString($output));
}
}
}
// end foreach( $compinfo['sub']
}
// end if( isset( $compinfo['sub']['props'] ))
}
// end foreach
}
// end vtimezone
$timeexec['zoneOk'] = microtime(TRUE);
$maxColCount = count($proporder);
/** fix property order list */
$proporder = $proporderOrg;
$proporder['TYPE'] = 0;
$proporder['ORDER'] = 1;
$props = array('UID', 'DTSTAMP', 'SUMMARY', 'DTSTART', 'DURATION', 'DTEND', 'DUE', 'RRULE', 'RDATE', 'EXRULE', 'EXDATE', 'DESCRIPTION', 'CATEGORIES', 'ORGANIZER', 'LOCATION', 'RESOURCES', 'CONTACT', 'URL', 'COMMENT', 'PRIORITY', 'ATTENDEE', 'CLASS', 'TRANSP', 'SEQUENCE', 'STATUS', 'COMPLETED', 'CREATED', 'LAST-MODIFIED', 'ACTION', 'TRIGGER', 'REPEAT', 'ATTACH', 'FREEBUSY', 'RELATED-TO', 'REQUEST-STATUS', 'GEO', 'PERCENT-COMPLETE', 'RECURRENCE-ID');
$pix = 2;
foreach ($props as $prop) {
if (isset($proporder[$prop])) {
continue;
}
if (in_array($prop, $propsToSkip)) {
if ($this->log) {
$this->log->log("'{$prop}' removed from output", 7);
}
continue;
}
while (in_array($pix, $proporder)) {
$pix++;
}
$proporder[$prop] = $pix++;
}
/** remove unused properties from and add x-props to property order list */
if ($maxpropix < count($proporder) - 1) {
$maxpropix = count($proporder) - 1;
}
$potmp = array();
$potmp[0] = 'TYPE';
$potmp[1] = 'ORDER';
// $potmp[2] = 'UID';
foreach ($compsinfo as $cix => $compinfo) {
if ('vtimezone' == $compinfo['type']) {
continue;
}
foreach ($compinfo['props'] as $propName => $propcnt) {
if (!in_array($propName, $potmp) && isset($proporder[$propName])) {
$potmp[$proporder[$propName]] = $propName;
} elseif ('X-PROP' == $propName) {
$comp = $calendar->getComponent($compinfo['ordno']);
while ($xprop = $comp->getProperty()) {
if (!in_array($xprop[0], $potmp)) {
$maxpropix += 1;
$potmp[$maxpropix] = $xprop[0];
}
// end if
}
// while( $xprop
}
// end elseif( 'X-PROP'
}
// end foreach( $compinfo['props']
if (isset($compinfo['sub'])) {
foreach ($compinfo['sub'] as $compinfo2) {
foreach ($compinfo2['props'] as $propName => $propcnt) {
if (!in_array($propName, $potmp) && isset($proporder[$propName])) {
$potmp[$proporder[$propName]] = $propName;
} elseif ('X-PROP' == $propName) {
$scomp = $comp->getComponent($compinfo2['ordno']);
while ($xprop = $scomp->getProperty()) {
if (!in_array($xprop[0], $potmp)) {
$maxpropix += 1;
$potmp[$maxpropix] = $xprop[0];
}
// end if
}
// end while xprop
}
// end X-PROP
}
// end $compinfo['sub']['props']
}
// end foreach( $compinfo['sub']
}
// end if( isset( $compinfo['sub']
}
ksort($potmp, SORT_NUMERIC);
$proporder = array_flip(array_values($potmp));
if ($this->log) {
$this->log->log("comp proporder=" . implode(',', array_flip($proporder)), 7);
}
if ($maxColCount < count($proporder)) {
$maxColCount = count($proporder);
}
/** create header row */
$row += 1;
foreach ($proporder as $propName => $col) {
if (isset($this->config[$propName])) {
$worksheet->writeString($row, $col, $this->config[$propName], $format_bold);
// check map of userfriendly name to iCal property name
if ($this->log) {
$this->log->log("header row, col={$col}: {$propName}, replaced by " . $this->config[$propName], 7);
}
} else {
$worksheet->writeString($row, $col, $propName, $format_bold);
}
}
$allowedProps = array('VEVENT' => array('ATTACH', 'ATTENDEE', 'CATEGORIES', 'CLASS', 'COMMENT', 'CONTACT', 'CREATED', 'DESCRIPTION', 'DTEND', 'DTSTAMP', 'DTSTART', 'DURATION', 'EXDATE', 'RXRULE', 'GEO', 'LAST-MODIFIED', 'LOCATION', 'ORGANIZER', 'PRIORITY', 'RDATE', 'RECURRENCE-ID', 'RELATED-TO', 'RESOURCES', 'RRULE', 'REQUEST-STATUS', 'SEQUENCE', 'STATUS', 'SUMMARY', 'TRANSP', 'UID', 'URL'), 'VTODO' => array('ATTACH', 'ATTENDEE', 'CATEGORIES', 'CLASS', 'COMMENT', 'COMPLETED', 'CONTACT', 'CREATED', 'DESCRIPTION', 'DTSTAMP', 'DTSTART', 'DUE', 'DURATION', 'EXDATE', 'EXRULE', 'GEO', 'LAST-MODIFIED', 'LOCATION', 'ORGANIZER', 'PERCENT', 'PRIORITY', 'RDATE', 'RECURRENCE-ID', 'RELATED-TO', 'RESOURCES', 'RRULE', 'REQUEST-STATUS', 'SEQUENCE', 'STATUS', 'SUMMARY', 'UID', 'URL'), 'VJOURNAL' => array('ATTACH', 'ATTENDEE', 'CATEGORIES', 'CLASS', 'COMMENT', 'CONTACT', 'CREATED', 'DESCRIPTION', 'DTSTAMP', 'DTSTART', 'EXDATE', 'EXRULE', 'LAST-MODIFIED', 'ORGANIZER', 'RDATE', 'RECURRENCE-ID', 'RELATED-TO', 'RRULE', 'REQUEST-STATUS', 'SEQUENCE', 'STATUS', 'SUMMARY', 'UID', 'URL'), 'VFREEBUSY' => array('ATTENDEE', 'COMMENT', 'CONTACT', 'DTEND', 'DTSTAMP', 'DTSTART', 'DURATION', 'FREEBUSY', 'ORGANIZER', 'UID', 'URL'), 'VALARM' => array('ACTION', 'ATTACH', 'ATTENDEE', 'DESCRIPTION', 'DURATION', 'REPEAT', 'SUMMARY', 'TRIGGER'));
/** create data rows */
foreach ($compsinfo as $cix => $compinfo) {
if ('vtimezone' == $compinfo['type']) {
continue;
}
$row += 1;
$worksheet->writeString($row, $proporder['TYPE'], $compinfo['type']);
$worksheet->writeString($row, $proporder['ORDER'], $compinfo['ordno']);
// $worksheet->write( $row, $proporder['UID'], $compinfo['uid'] );
$comp = $calendar->getComponent($compinfo['ordno']);
foreach ($proporder as $propName => $col) {
if ('TYPE' == $propName || 'ORDER' == $propName) {
continue;
}
if ('X-' == substr($propName, 0, 2)) {
continue;
}
if (!in_array($propName, $allowedProps[strtoupper($compinfo['type'])])) {
// check if component allows property
if ($this->log) {
$this->log->log("ERROR 11, INPUT FILE:'{$inputdirFile}' iCalcreator: unvalid property for component '" . $compinfo['type'] . "': '{$propName}'", PEAR_LOG_INFO);
}
continue;
}
if (isset($compinfo['props'][$propName])) {
switch ($propName) {
case 'LAST-MODIFIED':
$fcn = 'createLastModified';
break;
case 'RECURRENCE-ID':
$fcn = 'createRecurrenceid';
break;
case 'RELATED-TO':
$fcn = 'createRelatedTo';
break;
case 'REQUEST-STATUS':
$fcn = 'createRequestStatus';
break;
case 'PERCENT-COMPLETE':
$fcn = 'createPercentComplete';
break;
default:
$fcn = 'create' . strtoupper(substr($propName, 0, 1)) . strtolower(substr($propName, 1));
}
if (!method_exists($comp, $fcn)) {
if ($this->log) {
$this->log->log("ERROR 12 INPUT FILE:'{$filename}' iCalcreator: unknown property: '{$propName}' ({$fcn})", PEAR_LOG_INFO);
}
continue;
}
$output = str_replace("{$calnl} ", '', rtrim($comp->{$fcn}()));
$output = str_replace($propName . ';', '', $output);
$output = str_replace($propName . ':', '', $output);
$worksheet->writeString($row, $proporder[$propName], fixiCalString($output));
}
}
// end foreach( $proporder
if (isset($compinfo['props']['X-PROP'])) {
while ($xprop = $comp->getProperty()) {
$output = str_replace("{$calnl} ", '', rtrim($xprop[1]));
$worksheet->writeString($row, $proporder[$xprop[0]], fixiCalString($output));
}
}
if (isset($compinfo['sub'])) {
foreach ($compinfo['sub'] as $compinfo2) {
$row += 1;
$worksheet->writeString($row, $proporder['TYPE'], $compinfo2['type']);
$worksheet->writeString($row, $proporder['ORDER'], $compinfo['ordno'] . ':' . $compinfo2['ordno']);
$scomp = $comp->getComponent($compinfo2['ordno']);
foreach ($proporder as $propName => $col) {
if ('TYPE' == $propName || 'ORDER' == $propName) {
continue;
}
if ('X-' == substr($propName, 0, 2)) {
continue;
}
if (!in_array($propName, $allowedProps[strtoupper($compinfo2['type'])])) {
// check if component allows property
if ($this->log) {
$this->log->log("ERROR 13, INPUT FILE:'{$inputdirFile}' iCalcreator: unvalid property for component '" . $compinfo2['type'] . "': '{$propName}'", PEAR_LOG_INFO);
}
continue;
}
if (isset($compinfo2['props'][$propName])) {
$fcn = 'create' . strtoupper(substr($propName, 0, 1)) . strtolower(substr($propName, 1));
if (!method_exists($scomp, $fcn)) {
if ($this->log) {
$this->log->log("ERROR 14 INPUT FILE:'{$filename}' iCalcreator: unknown property: '{$propName}' ({$fcn})", PEAR_LOG_INFO);
}
continue;
}
$output = str_replace("{$calnl} ", '', rtrim($scomp->{$fcn}()));
$output = str_replace($propName . ';', '', $output);
$output = str_replace($propName . ':', '', $output);
$worksheet->writeString($row, $proporder[$propName], fixiCalString($output));
}
// end if( isset( $compinfo2['props'][$propName]
}
// end foreach( $proporder
if (isset($compinfo2['props']['X-PROP'])) {
while ($xprop = $scomp->getProperty()) {
$output = str_replace("{$calnl} ", '', rtrim($xprop[1]));
$output = str_replace('\\n ', chr(10), $output);
$worksheet->writeString($row, $proporder[$xprop[0]], fixiCalString($output));
}
}
// end if( isset( $compinfo2['props']['X-PROP']
}
// end foreach( $compinfo['sub']
}
// end if( isset( $compinfo['sub']
}
// foreach( $compsinfo as
if ($this->log) {
$timeexec['exit'] = microtime(TRUE);
$msg = "'{$filename}'";
$msg .= ' fileOk:' . number_format($timeexec['fileOk'] - $timeexec['start'], 5);
$msg .= ' wrkbkOk:' . number_format($timeexec['wrkbkOk'] - $timeexec['fileOk'], 5);
$msg .= ' infoOk:' . number_format($timeexec['infoOk'] - $timeexec['wrkbkOk'], 5);
$msg .= ' zoneOk:' . number_format($timeexec['zoneOk'] - $timeexec['infoOk'], 5);
$msg .= ' compOk:' . number_format($timeexec['exit'] - $timeexec['zoneOk'], 5);
$msg .= ' total:' . number_format($timeexec['exit'] - $timeexec['start'], 5) . 'sec';
$msg .= ', ' . ($row + 1) . " rows, {$maxColCount} cols";
$this->log->log($msg, PEAR_LOG_DEBUG);
$msg = "'{$filename}' (" . count($compsinfo) . ' components) start:' . date('H:i:s', $timeexec['start']);
$msg .= ' total:' . number_format($timeexec['exit'] - $timeexec['start'], 5) . 'sec';
if ($save) {
$msg .= " saved as '{$outputdirFile}'";
} else {
$msg .= " redirected as '" . $outputFileParts['basename'] . "'";
}
$this->log->log($msg, PEAR_LOG_NOTICE);
}
/** Close and, opt., send the file */
if ($this->log) {
$this->log->flush();
}
$workbook->close();
return TRUE;
}
$Result = new Result();
$User = new User($Database);
$Admin = new Admin($Database);
$Tools = new Tools($Database);
# verify that user is logged in
$User->check_user_session();
# fetch all l2 domains
$vlan_domains = $Admin->fetch_all_objects("vlanDomains", "id");
# get all custom fields
$custom_fields = $Tools->fetch_custom_fields('vlans');
# Create a workbook
$today = date("Ymd");
$filename = $today . "_phpipam_VLAN_export.xls";
$workbook = new Spreadsheet_Excel_Writer();
//formatting headers
$format_header =& $workbook->addFormat();
$format_header->setBold();
$format_header->setColor('black');
$format_header->setSize(12);
$format_header->setAlign('left');
//formatting content
$format_text =& $workbook->addFormat();
// Create a worksheet
$worksheet_name = "VLANs";
$worksheet =& $workbook->addWorksheet($worksheet_name);
$lineCount = 0;
$rowCount = 0;
//write headers
if (isset($_GET['name']) && $_GET['name'] == "on") {
$worksheet->write($lineCount, $rowCount, _('Name'), $format_header);
$rowCount++;
private static function _xls()
{
$t = $GLOBALS["t"];
require "lib/spreadsheet/Writer.php";
$xls = new Spreadsheet_Excel_Writer();
$xls->setTempDir(SIMPLE_CACHE . "/output/");
$xls->setVersion(8);
$sheet = $xls->addWorksheet($t["title"]);
$sheet->setInputEncoding('utf-8');
$sheet->freezePanes(array(1, 0, 1, 0));
$bold = $xls->addFormat();
$bold->setBold();
$normal = $xls->addFormat();
$normal->setVAlign("top");
$normal->setTextWrap();
$data = self::_build_data(true);
$row = 0;
$col = 0;
if (count($data) > 0) {
$col = 0;
$sheet->setColumn(0, count($data[0]) - 1, 15);
foreach ($data[0] as $field) {
if (empty($field["name"])) {
continue;
}
$sheet->write($row, $col++, $field["displayname"], $bold);
}
$row++;
foreach ($data as $asset) {
$col = 0;
foreach ($asset as $aval) {
if (!isset($aval["filter"])) {
continue;
}
$sheet->write($row, $col++, trim(strip_tags($aval["filter"])), $normal);
}
$row++;
}
} else {
$header = self::_build_fields();
$col = 0;
$sheet->setColumn(0, count($header) - 1, 15);
foreach ($header as $field) {
$sheet->write($row, $col++, $field, $bold);
}
}
$xls->close();
}
