require_once('PHP_EXEL/Classes/PHPExcel.php');
require_once('PHP_EXEL/Classes/PHPExcel/Writer/Excel5.php');
$xls = new PHPExcel();
$xls->setActiveSheetIndex(0);
$sheet = $xls->getActiveSheet();
$sheet->setTitle('Счет на оплату'.time());


/* CONFIGURATION XLS FILES */

/* STYLE */

$styleArray = array('borders' => array(	'outline' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM,	'color' => array('argb' => '#000000'),	),	),);
$styleArray_HARD = array('font'=>array('size'=>'14','name'=>'Arial','bold'=>'true'),
'alignment'=>array('vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER));
$styleArray_BOLD = array('font'=>array('size'=>'10','name'=>'Arial','bold'=>'true'),
'alignment'=>array('vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER));
$styleArray_BOLD_price = array('font'=>array('size'=>'11','name'=>'Arial','bold'=>'true'),
'alignment'=>array('vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER),'borders' => array(	'outline' => array('style' => PHPExcel_Style_Border::BORDER_THIN,	'color' => array('argb' => '#000000'),	),	));
$styleArray_SMALL = array('font'=>array('size'=>'11','name'=>'Arial'),
'alignment'=>array('vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER),'borders' => array(	'outline' => array('style' => PHPExcel_Style_Border::BORDER_THIN,	'color' => array('argb' => '#000000'),	),	));
$styleArray_SMALL_NO_BORD = array('font'=>array('size'=>'11','name'=>'Arial'),
'alignment'=>array('vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER));
$styleArray_CENTER = array('font'=>array('size'=>'10','name'=>'Arial'),
'alignment'=>array('vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER));
$styleArray_BOLD_bottom_PRICE = array('font'=>array('size'=>'9','name'=>'Arial','bold'=>'true'),
'alignment'=>array('vertical' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT));
$styleArray_BOLD_bottom_PRICE_CEIL = array('font'=>array('size'=>'9','name'=>'Arial','bold'=>'true'),
'alignment'=>array('vertical' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT));

/* POSITION */

$sheet->getColumnDimension('A')->setWidth(1);
$sheet->getColumnDimension('B')->setWidth(4);
$sheet->getColumnDimension('C')->setWidth(4);
$sheet->getColumnDimension('D')->setWidth(4);
$sheet->getColumnDimension('E')->setWidth(2);
$sheet->getColumnDimension('F')->setWidth(2);
$sheet->getColumnDimension('G')->setWidth(2);
$sheet->getColumnDimension('H')->setWidth(2);
$sheet->getColumnDimension('I')->setWidth(2);
$sheet->getColumnDimension('J')->setWidth(2);
$sheet->getColumnDimension('K')->setWidth(2);
$sheet->getColumnDimension('L')->setWidth(2);
$sheet->getColumnDimension('M')->setWidth(2);
$sheet->getColumnDimension('N')->setWidth(2);
$sheet->getColumnDimension('O')->setWidth(2);
$sheet->getColumnDimension('P')->setWidth(3);
$sheet->getColumnDimension('Q')->setWidth(3);
$sheet->getColumnDimension('R')->setWidth(3);
$sheet->getColumnDimension('S')->setWidth(3);
$sheet->getColumnDimension('T')->setWidth(1);
$sheet->getColumnDimension('U')->setWidth(3);
$sheet->getColumnDimension('V')->setWidth(3);
$sheet->getColumnDimension('W')->setWidth(3);
$sheet->getColumnDimension('X')->setWidth(1);
$sheet->getColumnDimension('Y')->setWidth(3);
$sheet->getColumnDimension('Z')->setWidth(3);
$sheet->getColumnDimension('AA')->setWidth(2);
$sheet->getColumnDimension('AB')->setWidth(2);
$sheet->getColumnDimension('AC')->setWidth(3);
$sheet->getColumnDimension('AD')->setWidth(3);
$sheet->getColumnDimension('AF')->setWidth(3);
$sheet->getColumnDimension('AG')->setWidth(1);
$sheet->getColumnDimension('AH')->setWidth(3);
$sheet->getColumnDimension('AI')->setWidth(3);
$sheet->getColumnDimension('AJ')->setWidth(3);
$sheet->getColumnDimension('AK')->setWidth(2);
$sheet->getColumnDimension('AE')->setWidth(3);
$sheet->getRowDimension('6')->setRowHeight(30);
$sheet->getRowDimension('4')->setRowHeight(30);
$sheet->getRowDimension('9')->setRowHeight(20);

/* ONE PLUS */

$sheet->mergeCells('B1:AK2');
$sheet->mergeCells('B4:E4');
$sheet->mergeCells('F4:AK4');
$sheet->mergeCells('B6:E6');
$sheet->mergeCells('F6:AK6');
$sheet->mergeCells('B8:C8');
$sheet->mergeCells('D8:S8');
$sheet->mergeCells('T8:W8');
$sheet->mergeCells('X8:AA8');
$sheet->mergeCells('AB8:AG8');
$sheet->mergeCells('AH8:AK8');



/* BODY */

$sheet->getStyle('B1:AK2')->applyFromArray($styleArray_HARD);
$sheet->getStyle('F4')->applyFromArray($styleArray_BOLD);
$sheet->getStyle('F6')->applyFromArray($styleArray_BOLD);
$sheet->getStyle('B4')->applyFromArray($styleArray_CENTER);
$sheet->getStyle('B6')->applyFromArray($styleArray_CENTER);
$sheet->getStyle('B8')->applyFromArray($styleArray_BOLD_price);
$sheet->getStyle('D8')->applyFromArray($styleArray_BOLD_price);
$sheet->getStyle('T8')->applyFromArray($styleArray_BOLD_price);
$sheet->getStyle('X8')->applyFromArray($styleArray_BOLD_price);
$sheet->getStyle('AB8')->applyFromArray($styleArray_BOLD_price);
$sheet->getStyle('AH8')->applyFromArray($styleArray_BOLD_price);


$sheet->mergeCells('B8:C8');
$sheet->mergeCells('D8:S8');
$sheet->mergeCells('T8:W8');
$sheet->mergeCells('X8:AA8');
$sheet->mergeCells('AB8:AG8');
$sheet->mergeCells('AH8:AK8');


/* ECHO RESULT */

$sheet->setCellValue("B1", $all_h1.$date.' '.$g);
$sheet->setCellValue("B4", 'Поставщик:');
$sheet->setCellValue("B6", 'Покупатель:');
$sheet->setCellValue("F4", "ООО «Формула Безопасности-Урал», ИНН 6670397828, КПП 667001001, 620137, Свердловская обл,Екатеринбург г, Студенческая ул, дом № 1, корпус 18, оф.110");
$sheet->getStyle("F4")->getAlignment()->setWrapText(true);
$sheet->setCellValue("F6", $inp);
$sheet->setCellValue("B8", '№');
$sheet->setCellValue("D8", 'Товары (работы, услуги)');
$sheet->setCellValue("T8", 'Кол-во');
$sheet->setCellValue("X8", 'Ед.');
$sheet->setCellValue("AB8", 'Цена');
$sheet->setCellValue("AH8", 'Сумма');


$ex_Q = explode(',',$_GET['prod_q']);
$ex_T = explode(',',$_GET['tables']);
$max = count($ex_T)-1;
$pos = 9;
$total_NUM_XLS = '';
$total_price_XLS = '';
for($i=1;$i<=$max;$i++){

$sheet->getRowDimension($pos)->setRowHeight(16);

/* STILESHES CASE */

$sheet->getStyle('B'.$pos.':C'.$pos)->applyFromArray($styleArray_SMALL);
$sheet->getStyle('D'.$pos.':S'.$pos)->applyFromArray($styleArray_SMALL);
$sheet->getStyle('T'.$pos.':W'.$pos)->applyFromArray($styleArray_SMALL);
$sheet->getStyle('X'.$pos.':AA'.$pos)->applyFromArray($styleArray_SMALL);
$sheet->getStyle('AB'.$pos.':AG'.$pos)->applyFromArray($styleArray_SMALL);
$sheet->getStyle('AH'.$pos.':AK'.$pos)->applyFromArray($styleArray_SMALL);

/* SUMM CASE */

$sheet->mergeCells('B'.$pos.':C'.$pos);
$sheet->mergeCells('D'.$pos.':S'.$pos);
$sheet->mergeCells('T'.$pos.':W'.$pos);
$sheet->mergeCells('X'.$pos.':AA'.$pos);
$sheet->mergeCells('AB'.$pos.':AG'.$pos);
$sheet->mergeCells('AH'.$pos.':AK'.$pos);

/* NEW LINE CASE */

$sheet->getStyle('B'.$pos.':C'.$pos)->getAlignment()->setWrapText(true);
$sheet->getStyle('D'.$pos.':S'.$pos)->getAlignment()->setWrapText(true);
$sheet->getStyle('T'.$pos.':W'.$pos)->getAlignment()->setWrapText(true);
$sheet->getStyle('X'.$pos.':AA'.$pos)->getAlignment()->setWrapText(true);
$sheet->getStyle('AB'.$pos.':AG'.$pos)->getAlignment()->setWrapText(true);
$sheet->getStyle('AH'.$pos.':AK'.$pos)->getAlignment()->setWrapText(true);


if(empty($total_NUM_XLS)){$total_NUM_XLS .=  $ex_Q[$i];}else{$total_NUM_XLS = $total_NUM_XLS + $ex_Q[$i];}


$NAMES_TOVAR = mysql_fetch_assoc(mysql_query("SELECT `NAME` FROM `b_iblock_element` WHERE `ID`='{$ex_T[$i]}'"));
$moresumm_p = mysql_fetch_assoc(mysql_query("SELECT `PRICE` FROM  `b_catalog_price` WHERE `PRODUCT_ID`='{$ex_T[$i]}'"));

$sheet->setCellValue('B'.$pos,$i);
$sheet->setCellValue('D'.$pos,$NAMES_TOVAR['NAME']);
$sheet->setCellValue('X'.$pos,'шт.');
$sheet->setCellValue('T'.$pos,$ex_Q[$i]);
$sheet->setCellValue('AB'.$pos,$moresumm_p['PRICE']);
$all_total_price = $ex_Q[$i]*$moresumm_p['PRICE'];
if(empty($total_price_XLS)){$total_price_XLS .=  $all_total_price;}else{$total_price_XLS = $total_price_XLS + $all_total_price;}
$sheet->setCellValue('AH'.$pos,$all_total_price);

$pos++;
$max2 = $pos-1;



if($i == $max){

$F11 = $pos + 1;
$F12 = $F11 + 1;
$F13 = $F12 + 1;
$F14 = $F13 + 1;
$F15 = $F14 + 1;
$F16 = $F15 + 3;


$sheet->getStyle('B8:AK'.$max2)->applyFromArray($styleArray);
$sheet->getStyle('B'.$F15.':AK'.$F15)->applyFromArray($styleArray_SMALL_NO_BORD);
$sheet->getStyle('B'.$F14.':AK'.$F14)->applyFromArray($styleArray_SMALL_NO_BORD);
$sheet->getStyle('B'.$F16.':S'.$F16)->applyFromArray($styleArray_SMALL_NO_BORD);
$sheet->getStyle('T'.$F16.':AK'.$F16)->applyFromArray($styleArray_SMALL_NO_BORD);

$sheet->mergeCells('X'.$F11.':AG'.$F11);$sheet->mergeCells('AH'.$F11.':AK'.$F11);
$sheet->mergeCells('X'.$F12.':AG'.$F12);$sheet->mergeCells('AH'.$F12.':AK'.$F12);
$sheet->mergeCells('X'.$F13.':AG'.$F13);$sheet->mergeCells('AH'.$F13.':AK'.$F13);
$sheet->mergeCells('B'.$F14.':AK'.$F14);
$sheet->mergeCells('B'.$F15.':AK'.$F15);
$sheet->mergeCells('B'.$F16.':S'.$F16);
$sheet->mergeCells('T'.$F16.':AK'.$F16);





/* TOTAL PRICE */
$sheet->setCellValue("AH".$F11, $total_price_XLS);
$sheet->setCellValue("AH".$F13, $total_price_XLS);
$sheet->setCellValue("B".$F14, 'Всего наименований '.$total_NUM_XLS.', на сумму '.$total_price_XLS.' руб.');
$sheet->setCellValue("B".$F16, 'Руководитель ________________________________');
$sheet->setCellValue("T".$F16, 'Бухгалтер ____________________________________');
/* TOTAL NUM */


$all_summ = $total_price_XLS;

$cols = array(); 
while ($all_summ > 0) { 
    $cols[] = $all_summ % 10; 
    $all_summ = intval($all_summ / 10);  
} 
$cols_num = count($cols);
$summ_text = '';
for($ost = $cols_num;$ost>=0;$ost--){
if($cols[$ost]<5 and $cols[$ost]>1){$ok='я';}elseif($cols[$ost]==1){$ok='ь';}else{$ok='ей';}
if($cols[$ost]>1 and $cols[$ost]<5){$ok_2='а';}elseif($cols[$ost]==1){$ok_2='';}else{$ok_2='ов';}
if($cols[$ost]>1 and $cols[$ost]<5){$ok_3='и';}elseif($cols[$ost]==1){$ok_3='а';}else{$ok_3='';}
if($ost==0){$text='';$ii = (int)$cols[$ost];}
elseif($ost==1){$text='';$ii = (int)$cols[$ost].'0';}
elseif($ost==2){$text='';$ii = (int)$cols[$ost].'00';}
elseif($ost==3){$text='тысяч'.$ok_3;$ii = (int)$cols[$ost];}
elseif($ost==4){$text='';$ii = (int)$cols[$ost].'0';}
elseif($ost==5){$text='';$ii = (int)$cols[$ost].'00';}
elseif($ost==6){$text='миллион'.$ok_2;$ii = (int)$cols[$ost];}
elseif($ost==7){$text='';$ii = (int)$cols[$ost].'0';}
elseif($ost==8){$text='';$ii = (int)$cols[$ost].'00';}
elseif($ost==9){$text='миллирад'.$ok_2;$ii = (int)$cols[$ost];}
elseif($ost==10){$text='';$ii = (int)$cols[$ost].'0';}
elseif($ost==11){$text='';$ii = (int)$cols[$ost].'00';}
else{$text='';}
if($ost==3){$summ_text.='тысяч'.$ok_3.' ';$ii = (int)$cols[$ost];}else{}
if($cols[$ost]==0){}else{
if($ost == $cols_num-1){$summ_text.=$array_col_ROOT[$ii].' '.$text.' ';}else{
$summ_text.=$array_col[$ii].' '.$text.' ';}
}
}
$summ_text.='рубл'.$ok;$ii = (int)$cols[$ost];

$sheet->setCellValue("B".$F15, $summ_text);



/* NDS PRICE */
$NDS_FROM_XLS = ceil($total_price_XLS)/100*18;
$sheet->setCellValue("AH".$F12, $NDS_FROM_XLS);

$sheet->getStyle("X".$F11)->applyFromArray($styleArray_BOLD_bottom_PRICE);
$sheet->getStyle("X".$F12)->applyFromArray($styleArray_BOLD_bottom_PRICE);
$sheet->getStyle("X".$F13)->applyFromArray($styleArray_BOLD_bottom_PRICE);

$sheet->getStyle('AH'.$F11.':AK'.$F11)->applyFromArray($styleArray_BOLD_bottom_PRICE_CEIL);
$sheet->getStyle('AH'.$F12.':AK'.$F12)->applyFromArray($styleArray_BOLD_bottom_PRICE_CEIL);
$sheet->getStyle('AH'.$F13.':AK'.$F13)->applyFromArray($styleArray_BOLD_bottom_PRICE_CEIL);

$sheet->setCellValue("X".$F11, 'Итого:');
$sheet->setCellValue("X".$F12, 'НДС (18%, включен в цену):');
$sheet->setCellValue("X".$F13, 'Всего к оплате:');

}else{}
}

/* WRITE IN FILE */

header ( "Expires: Mon, 1 Apr 1974 05:00:00 GMT" );
 header ( "Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT" );
 header ( "Cache-Control: no-cache, must-revalidate" );
 header ( "Pragma: no-cache" );
 header ( "Content-type: application/vnd.ms-excel" );
 $sssc4et = time();
 header ( "Content-Disposition: attachment; filename=Счет №  $sssc4et.xls" );
 
// Выводим содержимое файла
 $objWriter = new PHPExcel_Writer_Excel5($xls);
 $objWriter->save('php://output');