Why does phpspreadsheet generate xls with an error on the content part?

Question

PHP

Hello!
There is a site on Bitrix, I'm trying to add xls generation with the contents of the basket.
Decided to use phpspreadsheet 1.17 for this.
Wrote this code:

<?php
require 'vendor/autoload.php';
include 'arResult.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xls;
use PhpOffice\PhpSpreadsheet\Style\{Fill, Alignment};
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

//шапка таблицы
$sheet->setCellValueByColumnAndRow(1, 1, 'Изображение');
$sheet->setCellValueByColumnAndRow(2, 1, 'Артикул');
$sheet->setCellValueByColumnAndRow(3, 1, 'Название');
$sheet->setCellValueByColumnAndRow(4, 1, 'Цена');
$sheet->setCellValueByColumnAndRow(5, 1, 'Количество');
$sheet->setCellValueByColumnAndRow(6, 1, 'Сумма');
$sheet->getColumnDimensionByColumn(1)->setWidth(25);
$sheet->getColumnDimensionByColumn(3)->setWidth(50);
$sheet->getColumnDimensionByColumn(5)->setAutoSize(true);
$sheet->getStyle('A1:F1')->applyFromArray([
    'font' => [
        'color' => [
            'rgb' => 'ffffff'
        ]
    ],
    'fill' => [
        'fillType' => Fill::FILL_SOLID,
        'color' => [
            'rgb' => '185b81'
        ]
    ],
]);
$sheet->getStyle('A:F')->getAlignment()->applyFromArray([
    'horizontal' => Alignment::HORIZONTAL_CENTER,
    'vertical' => Alignment::VERTICAL_CENTER,
    'wrapText' => true,
]);


foreach ($arResult as $i => $arItem)
{
    $row = $i + 2;
    //картинка
    $drawing = new Drawing();
    $drawing->setResizeProportional(true);
    $drawing->setName('Название картинки');
    $drawing->setDescription('Описание картинки');


    $drawing->setPath(__DIR__ . $arItem['SRC']);
    $drawing->setCoordinates('A'.$row);
    $drawing->setWidth(163);
    $drawing->setOffsetX(10);
    $drawing->setOffsetY(10);
    $drawing->setHeight(50);
    $drawing->setWorksheet($sheet);

    $dimension = $sheet->getRowDimension($row);
    $dimension->setRowHeight(50);

    //остальные поля
    $sheet->setCellValueByColumnAndRow(2, $row, $row);
    $sheet->setCellValueByColumnAndRow(3, $row, $arItem['NAME']);
    $sheet->setCellValueByColumnAndRow(4, $row, $arItem['PRICE']);
    $sheet->setCellValueByColumnAndRow(5, $row, $arItem['QUANTITY']);
    $sheet->setCellValueByColumnAndRow(6, $row, $arItem['QUANTITY'] * $arItem['PRICE']);
}


$writer = new Xls($spreadsheet);

//Вывод файла
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="simple.xls"');

$writer->save('php://output');

file is received using this form

<form action="/test.php" target="_blank">
    <button id="xls">Получить xls</button>
</form>

On the local server, this code works fine and produces the following content:


However, when I try to do the same on the production site, I get an "error in part of the content in the book"

When you click on the Yes button, the data is displayed, but all the styles flies.


The question is what could be the problem and how to fix it?

Answer 1

The problem was with the encoding. Here is the code that works.

<?php
require($_SERVER["DOCUMENT_ROOT"]."/bitrix/modules/main/include/prolog_before.php");


use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use PhpOffice\PhpSpreadsheet\Style\{Fill, Alignment};



$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

$dbBasketItems = CSaleBasket::GetList(
    array( "NAME" => "ASC", "ID" => "ASC" ),
    array(
        "FUSER_ID" => CSaleBasket::GetBasketUserID(),
        "LID" => SITE_ID,
        "ORDER_ID" => "NULL",
    ),
    false,
    false,
    array()
);

//шапка
$sheet->setCellValueByColumnAndRow(1, 1, 'Изображение');
$sheet->setCellValueByColumnAndRow(2, 1, 'Артикул');
$sheet->setCellValueByColumnAndRow(3, 1, 'Название');
$sheet->setCellValueByColumnAndRow(4, 1, 'Цена');
$sheet->setCellValueByColumnAndRow(5, 1, 'Количество');
$sheet->setCellValueByColumnAndRow(6, 1, 'Сумма');
$sheet->getColumnDimensionByColumn(1)->setWidth(25);
$sheet->getColumnDimensionByColumn(3)->setWidth(50);
$sheet->getColumnDimensionByColumn(5)->setAutoSize(true);
$sheet->getStyle('A1:F1')->applyFromArray([
    'font' => [
        'color' => [
            'rgb' => 'ffffff'
        ]
    ],
    'fill' => [
        'fillType' => Fill::FILL_SOLID,
        'color' => [
            'rgb' => '185b81'
        ]
    ],
]);
$sheet->getStyle('A:F')->getAlignment()->applyFromArray([
    'horizontal' => Alignment::HORIZONTAL_CENTER,
    'vertical' => Alignment::VERTICAL_CENTER,
    'wrapText' => true,
]);


$row = 2;
while ($arItem = $dbBasketItems->Fetch())
{
    CModule::IncludeModule("iblock");

    $arSelect = Array("PREVIEW_PICTURE", "PROPERTY_ARTIKULARKTEN", "PROPERTY_ARTNUMBER");
    $arFilter = Array("IBLOCK_ID"=>14, 'ID' => $arItem["PRODUCT_ID"]);
    $res = CIBlockElement::GetList(Array(), $arFilter, false, Array(), $arSelect);
    if($arElem = $res->GetNext()){
        $arItem['IMG_SRC'] = $_SERVER["DOCUMENT_ROOT"] . CFile::GetPath($arElem["PREVIEW_PICTURE"]);

        $arItem['ARTIKUL'] = $arElem["PROPERTY_ARTIKULARKTEN_VALUE"] ?? $arElem["PROPERTY_ARTNUMBER_VALUE"];
    }

    $drawing = new Drawing();
    $drawing->setResizeProportional(true);
    $drawing->setName($arItem['NAME']);
    $drawing->setDescription($arItem['NAME']);
    $drawing->setPath($arItem['IMG_SRC']);
    $drawing->setCoordinates('A'.$row);
    $drawing->setWidth(163);
    $drawing->setOffsetX(10);
    $drawing->setOffsetY(10);
    $drawing->setHeight(50);
    $drawing->setWorksheet($sheet);
    $dimension = $sheet->getRowDimension($row);
    $dimension->setRowHeight(50);

    $sheet->setCellValueByColumnAndRow(2, $row, $arItem['ARTIKUL']);
    $sheet->setCellValueByColumnAndRow(3, $row, $arItem['NAME']);
    $sheet->setCellValueByColumnAndRow(4, $row, $arItem['PRICE']);
    $sheet->setCellValueByColumnAndRow(5, $row, $arItem['QUANTITY']);
    $sheet->setCellValueByColumnAndRow(6, $row, $arItem['QUANTITY'] * $arItem['PRICE']);
    $row++;
}

$writer = new Xlsx($spreadsheet);

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" );
header('Content-Disposition: attachment; filename="basket.xlsx"');


$tmpfile = tmpfile();
$path = stream_get_meta_data($tmpfile)['uri'];
if (function_exists("mb_internal_encoding"))
{
    $oldEncoding = mb_internal_encoding();
    mb_internal_encoding('latin1');
    $writer->save($path);
    mb_internal_encoding($oldEncoding);
} else {
    $writer->save($path);
}
readfile($path);
fclose($tmpfile);

Answer 2

I suspect that in different versions of PHP or config

Answer 3

specify absolute paths + security policy settings on the http server check - for example, everything except the main formats can be blocked there.