<?php

# Demonstrates Spreadsheet::WriteExcel's named colors and the Excel
# color palette.
#
# reverse('©'), March 2002, John McNamara, jmcnamara@cpan.org

# PHP port by Johann Hanne, 2005-11-01

include "config.php";
//настройки подписей столбцов по умолчанию
if (!isset($header_num))  $header_num="№";
if (!isset($header_name)) $header_name="Фамилия Имя Отчество";
if (!isset($header_rank)) $header_rank="Должность";
if (!isset($header_tel))  $header_tel="№ тел.";
if (!isset($header_mail)) $header_mail="E-mail";
if (!isset($header_room)) $header_room="№ ком.";
if (!isset($header_dep))  $header_dep="Отдел";
//

mysql_connect ($dbhost, $username, $password);
mysql_select_db ($maindb);

set_time_limit(10);

require_once "writeexcel/class.writeexcel_workbook.inc.php";
require_once "writeexcel/class.writeexcel_worksheet.inc.php";

//$fname = tempnam("/tmp", "contacts.xls");
$fname = "$maintitle.xls";
$workbook = &new writeexcel_workbook($fname);
# Some common formats
$center  =& $workbook->addformat(array('align' => 'center'));
$heading =& $workbook->addformat(array('align' => 'center', 'bold' => 1));

# Try this to see the default Excel 5 palette
# $workbook->set_palette_xl5();

######################################################################
#
# Demonstrate the named colors.
#

$colors = array(
                'black'=>0x08,
                'blue'=>0x0C,
                'brown'=>0x10,
                'cyan'=>0x0F,
                'gray'=>0x17,
                'green'=>0x11,
                'lime'=>0x0B,
                'magenta'=>0x0E,
                'navy'=>0x12,
                'orange'=>0x35,
                'purple'=>0x14,
                'red'=>0x0A,
                'silver'=>0x16,
                'white'=>0x09,
                'yellow'=>0x0D
               );



######################################################################
// название листа
$worksheet =& $workbook->addworksheet("$maintitle");

// создаем классы
// названия отделов
# Create a border format
$border1 =& $workbook->addformat();
$border1->set_italic();
$border1->set_pattern(0x1);
$border1->set_fg_color(22);
$border1->set_top(1);
$border1->set_bottom(1);
$border1->set_left(1);
$border1->set_right(1);
$border1->set_align('center');
$border1->set_align('vcenter');
$border1->set_merge(); # This is the key feature
# Create another border format. Note you could use copy() here.
$border2 =& $workbook->addformat();
$border2->set_italic();
$border2->set_pattern(0x1);
$border2->set_fg_color(47);
$border2->set_top(1);
$border2->set_bottom(1);
$border2->set_left(1);
$border2->set_right(1);
$border2->set_align('center');
$border2->set_align('vcenter');
$border2->set_merge(); # This is the key feature

// заголовок
$border01 =& $workbook->addformat();
$border01->set_bold();
$border01->set_italic();
$border01->set_align('center');
$border01->set_align('vcenter');
$border01->set_merge(); # This is the key feature
# Create another border format. Note you could use copy() here.
$border02 =& $workbook->addformat();
$border02->set_bold();
$border02->set_align('center');
$border02->set_align('vcenter');
$border02->set_merge(); # This is the key feature

// обычный
$format =& $workbook->addformat(array(
                                        'fg_color' => 9,
                                        'pattern'  => 1,
                                        'border'   => 1,
                                        'align' => 'center'
                                     ));
$format->set_align('vcenter');
$format->set_text_wrap();

// заголовки столбцов
$thead =& $workbook->addformat();
$thead->set_bold();
$thead->set_pattern(0x1);
$thead->set_fg_color(31);
$thead->set_align('center');
$thead->set_align('vcenter');
$thead->set_top(1);
$thead->set_bottom(1);
$thead->set_left(1);
$thead->set_right(1);

// закрепляем верхние две строчки
$worksheet->freeze_panes(2, 0); # 1 row
// ширина столбцов
$worksheet->set_column(0, 0, 3.29);
$worksheet->set_column(1, 1, 34);
$worksheet->set_column(2, 2, 34);
$worksheet->set_column(3, 3, 18);
$worksheet->set_column(4, 4, 30);
$worksheet->set_column(5, 5, 10);
$worksheet->set_column(6, 6, 35);

// заголовок
$worksheet->write(0, 0, $maintitle, $border01);

$worksheet->write_blank(0, 1, $border02);
$worksheet->write_blank(0, 2, $border02);
$worksheet->write_blank(0, 3, $border02);
$worksheet->write_blank(0, 4, $border02);
$worksheet->write_blank(0, 5, $border02);
$worksheet->write_blank(0, 6, $border02);
// полписи столбцов
$worksheet->write(1, 0, $header_num , $thead);
$worksheet->write(1, 1, $header_name, $thead);
$worksheet->write(1, 2, $header_rank,  $thead);
$worksheet->write(1, 3, $header_tel ,  $thead);
$worksheet->write(1, 4, $header_mail,  $thead);
$worksheet->write(1, 5, $header_room,  $thead);
$worksheet->write(1, 6, $header_dep ,  $thead);

$result = @mysql_query("SELECT * FROM t1 WHERE perm <> 'hidden' AND perm <> 'hiddent' ORDER BY id");
$y=2;
$i=1;
$olddep="";
while($value = mysql_fetch_array($result))
 {
 $cdep=$value['dep'];
 if (Trim($cdep) != Trim($olddep))
  {
  $worksheet->write      ($y, 0, $cdep, $border1);
  $worksheet->write_blank($y, 1,                 $border2);
  $worksheet->write_blank($y, 2,                 $border2);
  $worksheet->write_blank($y, 3,                 $border2);
  $worksheet->write_blank($y, 4,                 $border2);
  $worksheet->write_blank($y, 5,                 $border2);
  $worksheet->write_blank($y, 6,                 $border2);
  $y++;
  }
 $olddep=$cdep;

 $worksheet->write($y,0,$i,$format);
 $worksheet->write($y,1,$value[1],$format);
 $worksheet->write($y,2,$value[2],$format);
 $worksheet->write($y,3,$value[3],$format);
 $worksheet->write($y,4,$value[4],$format);
 $worksheet->write($y,5,$value[5],$format);
 $worksheet->write($y,6,$value[6],$format);
 $i++;
 $y++;
 }

/*
for ($i=8;$i<=63;$i++) {
    $format =& $workbook->addformat(array(
                                        'fg_color' => $i,
                                        'pattern'  => 1,
                                        'border'   => 1
                                     ));

    $worksheet->write(($i -7), 0, $i,                    $center);
    $worksheet->write(($i -7), 1, sprintf("0x%02X", $i), $center);
    $worksheet->write(($i -7), 2, 'dasdasdas',            $format);


    # Add the  color names
    foreach ($colors as $color=>$index) {
      if ($i==$index) {
        $worksheet->write(($i -7), 3,$color, $center);
      }
    }
}
*/

$workbook->close();

header("Content-Type: application/x-msexcel; name=\"$maintitle.xls\"");
header("Content-Disposition: inline; filename=\"$maintitle.xls\"");
$fh=fopen($fname, "rb");
fpassthru($fh);
@unlink($fname);

mysql_close();
?>