$pathToFile = __DIR__.'/excel.xlsx';

$data = [
   ['Данные юр. лица', '', '', 'Купленный товар', '', '', ''],
   ['Наименование', 'ИНН', 'Адрес', 'Номенклатура', 'Количество', 'Цена, руб.', 'Дата оплаты', 'Сумма'],
   ['ИП Кошкин', '00111122311', 'Москва ул. Ленина д.5', 'Принтер', 2, 100, '01.01.2024', '=ROUND(E3*F3,2)'],
   ['ИП Собакин', '00111122421', 'Москва ул. Ленина д.6', 'Принтер', 3, 200, '02.01.2024', '=ROUND(E4*F4,2)'],
   ['ИП Конов', '00111122533', 'Москва ул. Ленина д.7', 'Принтер', 1, 300, '03.01.2024', '=ROUND(E5*F5,2)'],
   ['ИП Медведев', '00111122644', 'Москва ул. Ленина д.8', 'Принтер', 5, 400, '04.01.2024', '=ROUND(E6*F6,2)'],
   [null, null, null, null, null, null, null, '=SUM(H3:H6)'],
];

$border = (new BorderBuilder())
   ->setBorderBottom(Color::BLACK, Border::WIDTH_THIN, Border::STYLE_SOLID)
   ->setBorderTop(Color::BLACK, Border::WIDTH_THIN, Border::STYLE_SOLID)
   ->setBorderLeft(Color::BLACK, Border::WIDTH_THIN, Border::STYLE_SOLID)
   ->setBorderRight(Color::BLACK, Border::WIDTH_THIN, Border::STYLE_SOLID)
   ->build();

$defaultStyle = (new StyleBuilder())
   ->setFontSize(11)
   ->setFontName('Calibri')
   ->setShouldWrapText()
   ->setCellAlignment(CellAlignment::CENTER)
   ->setCellVerticalAlignment(CellVerticalAlignment::MIDDLE)
   ->setBorder($border)
   ->build();

$writer = WriterEntityFactory::createXLSXWriter();
$writer->setDefaultRowStyle($defaultStyle);
$writer->setFreezePane('A3');
$writer->openToFile($pathToFile);

$writer->getCurrentSheet()->setAutoFilter('A2:H6');

$row = 0;
foreach ($data as $item){
   $cells = []; $col=0;
   foreach ($item as $v) {

       if ($col < 3) $color = 'FBFCEB';
       elseif ($col < 6) $color = 'D4DFED';
       else $color = 'DDD7E7';

       $style =
           (new StyleBuilder())
           ->setBackgroundColor($color)
           ->build();

       if ($row==0){
           $style = null;
       }


       if ($style){
           if (in_array($col, [0,2,3]) && $row>1){
               $style->setCellAlignment(CellAlignment::LEFT);
           }

           if (in_array($col, [5,7])) {
               $style->setFormat('0.00');
           }
           elseif (in_array($col, [4])) {
               $style->setFormat('0');
           }
           elseif (in_array($col, [1,6])) {
               $style->setFormat('@');
           }

           if ($row==6) {
               $style->setBackgroundColor(null);
           }

       }

       $cells[] = WriterEntityFactory::createCell($v, $style);
       $col++;
   }

   $singleRow = WriterEntityFactory::createRow($cells);
   $writer->addRow($singleRow);
   $row++;
}


$writer->getCurrentSheet()->mergeCells('A1:C1');
$writer->getCurrentSheet()->mergeCells('D1:F1');

$writer->getCurrentSheet()->addColumnDimension(new ColumnDimension('A', 18));
$writer->getCurrentSheet()->addColumnDimension(new ColumnDimension('B', 18));
$writer->getCurrentSheet()->addColumnDimension(new ColumnDimension('C', 25));
$writer->getCurrentSheet()->addColumnDimension(new ColumnDimension('D', 18));
$writer->getCurrentSheet()->addColumnDimension(new ColumnDimension('E', 18));
$writer->getCurrentSheet()->addColumnDimension(new ColumnDimension('F', 18));
$writer->getCurrentSheet()->addColumnDimension(new ColumnDimension('G', 18));
$writer->getCurrentSheet()->addColumnDimension(new ColumnDimension('H', 18));

$writer->close();