Ответы пользователя по тегу PhpSpreadsheet
  • Почему phpspreadsheet генерирует xls с ошибкой в части содержимого?

    @alexgodman Автор вопроса
    Проблема была с кодировкой. Вот код который работает.
    <?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);
    Ответ написан
    1 комментарий