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(); }