PHP Spreadsheet_Excel_Writer::addFormat方法代码示例

 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);
//.........这里部分代码省略.........

 /**
  * Формирует 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();
 }