spreadsheet - PhpSpreadsheet setAutoSize(true) 不适用于 php 7.4.9 中的某些列

Question

预期行为：

当前行为：

我正在使用以下参数，

1. PHP 7.4.9
2. Zend 框架 1
3. PhpOffice\PhpSpreadsheet\电子表格

我正在使用以下函数从数组生成 Excel 工作表，

public function array_to_excel_download($array, $columnTypeList = array()) 
{
  $spreadsheet = new Spreadsheet();
    
  // set column type
  $columnNum='A';
  if($columnTypeList) {
    foreach ($columnTypeList as $columnType) {
        $spreadsheet->getActiveSheet()->getStyle($columnNum++)->getNumberFormat()- 
        >setFormatCode(constant("phpOffice\PhpSpreadsheet\Style\NumberFormat::".$columnType));
    }
  }

  // Fill worksheet from values in array
  $spreadsheet->getActiveSheet()->fromArray($array, null, 'A1');

  // Rename worksheet
  $spreadsheet->getActiveSheet()->setTitle('Participants Detail');

  // Apply to all columns in sheet
  $defaultFont = isset($font['name']) ? $font['name'] : 'Arial';
  $defaultStyle = array('font' => array('size' => 11, 'name' => $defaultFont));


  if (!empty($array) && isset($array[1])) {

    // Get First Row of sheet
    $firstLetter = Coordinate::stringFromColumnIndex(1);
    $lastLetter = Coordinate::stringFromColumnIndex(count($array[1]));
    
    if(count($arrMergeExcelRange) > 0){
        $headerRange = "{$firstLetter}1:{$lastLetter}2";
        $spreadsheet->getActiveSheet()->freezePane('A3');
        
        // START - MERGE COLUMN
        foreach ($arrMergeExcelRange as $valMergeExcelRange){
            
            if(isset($valMergeExcelRange['min']) && isset($valMergeExcelRange['max'])) {
                $range= Coordinate::stringFromColumnIndex($valMergeExcelRange['min'])."1:".Coordinate::stringFromColumnIndex($valMergeExcelRange['max'])."1";
                $spreadsheet->getActiveSheet()->mergeCells($range);
                $spreadsheet->getActiveSheet()->getStyle($headerRange)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            }
            // FOR ROW MERGE
            if (isset($valMergeExcelRange['rmin']) && isset($valMergeExcelRange['rmax'])) {
                $range= Coordinate::stringFromColumnIndex($valMergeExcelRange['rmin'])."1:".Coordinate::stringFromColumnIndex($valMergeExcelRange['rmax'])."2";
                $spreadsheet->getActiveSheet()->mergeCells($range);
                $spreadsheet->getActiveSheet()->getStyle($headerRange)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
            }
        }                
        /* END - MERGE COLUMN */
        
    } else{
        $headerRange = "{$firstLetter}1:{$lastLetter}1";
        $spreadsheet->getActiveSheet()->freezePane('A2');
    }
      
    $dataCount = count($array); 
    $footerRange = "{$firstLetter}{$dataCount}:{$lastLetter}$dataCount";
    
    // Apply to first row in sheet
    $firstRow = array(
        'fill' => array(
            'fillType' => Fill::FILL_SOLID,
            'startColor' => array('argb' => '808080'),
        ),
        'font' => array(
            'bold' => true,
            'color' => array('rgb' => 'FFFFFF'),
            'size' => 11,
            'name' => $defaultFont
        )
    );            
    $lastRow = array(
        'font' => array(
            'bold' => true,
            'size' => 11,
            'name' => $defaultFont
        )
    );
    
    $spreadsheet->getActiveSheet()->getStyle($headerRange)->applyFromArray($firstRow);
                
    // Apply border to data part
    $borderStyle = array(
        'borders' => array(
            'allBorders' => array(
                'borderStyle' => Border::BORDER_THIN
            )
        )
    );
    
    $dataRange="{$firstLetter}1:{$lastLetter}{$dataCount}";
    $spreadsheet->getActiveSheet()->getStyle($dataRange)->applyFromArray($borderStyle); 
    $spreadsheet->getActiveSheet()->getStyle($dataRange)->getAlignment()->setWrapText(true);
    
    // START - SET NUMBER FORMAT TO "Generated Parent Code" COLUMN            
    $columNumbOfGeneratedParentCode = 0;
    foreach ($array[0] as $keyArray=>$valArray){
        if($keyArray == 'generatedParentCode'){
            $columNumbOfGeneratedParentCode++;
            break;
        }
    }
    if($columNumbOfGeneratedParentCode != 0){               
        $colLetter = Coordinate::stringFromColumnIndex($columNumbOfGeneratedParentCode);
        $generatedParentCodeRange = "{$colLetter}1:{$colLetter}{$dataCount}";
        $spreadsheet->getActiveSheet()->getStyle($generatedParentCodeRange)->getNumberFormat()->setFormatCode('0.0');               
    }            
    // END - SET NUMBER FORMAT TO "Generated Parent Code" COLUMN
}
        
// Cell auto width
foreach($spreadsheet->getActiveSheet()->getColumnIterator() as $columnID) {
    $spreadsheet->getActiveSheet()->getColumnDimension($columnID->getColumnIndex())->setAutoSize(true);
}

// Apply to first row in sheet
$colorArr = array( 
    array (
        'fill' => array(
            'fillType' => Fill::FILL_SOLID,
            'startColor' => array('argb' => 'FFFF00'),
        ),
        'font' => array(
            'bold' => true,
            'color' => array('rgb' => '000000'),
            'size' => 11,
            'name' => $defaultFont
        )
    ),
    array (
        'fill' => array(
            'fillType' => Fill::FILL_SOLID,
            'startColor' => array('argb' => '90EE90'),
        ),
        'font' => array(
            'bold' => true,
            'color' => array('rgb' => '000000'),
            'size' => 11,
            'name' => $defaultFont
        )
    )
);
        
// Set Orientation, size and scaling
$pageSetupOrientation = PageSetup::ORIENTATION_PORTRAIT;
if(strtolower($fileOrientation)=="landscape"){
    $pageSetupOrientation = PageSetup::ORIENTATION_LANDSCAPE;
}
$spreadsheet->getActiveSheet()->getPageSetup()->setOrientation($pageSetupOrientation);

$spreadsheet->getActiveSheet()->getPageSetup()->setPaperSize(PageSetup::PAPERSIZE_A4);
$spreadsheet->getActiveSheet()->getPageSetup()->setFitToPage(true);
$spreadsheet->getActiveSheet()->getPageSetup()->setFitToWidth(1);
$spreadsheet->getActiveSheet()->getPageSetup()->setFitToHeight(0);
$spreadsheet->getActiveSheet()->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(1, 1);
$spreadsheet->getActiveSheet()->getStyle( $spreadsheet->getActiveSheet()->calculateWorksheetDimension() )->applyFromArray($defaultStyle);
      
ob_start();

// Redirect output to a client's web browser (Xlsx)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$filename.'"');
header('Cache-Control: no-store, no-cache,must-revalidate, post-check=0, pre-check=0');
header('Content-Description: File Transfer');        
header('Content-Transfer-Encoding: binary');
header('Expires: Sat, 26 Jul 1997 05:00:00 GMT');
header ('Pragma: no-cache'); // HTTP/1.0
$objWriter = IOFactory::createWriter($spreadsheet, 'Xlsx');
$objWriter->save('php://output');
exit;
}

我在数组下面作为 $array 传递，

$array = array(
array(
    'parentCode' => 'ParentCode',
    'firstName' => 'FirstName',
    'lastName' => 'LastName',
    'status' => 'Status',
    'initialPassword' => 'InitialPassword',
    'language' => 'Language',
    'createdDate' => 'CreatedDate',
    'modifiedDate' => 'ModifiedDate'
),
array(
    'parentCode' => '10060.0',
    'firstName' => 'abc',
    'lastName' => 'abc',
    'status' => 'Open',
    'initialPassword' => 'z9aqW4G1sZ',
    'language' => 'English (UK)',
    'createdDate' => '29.10.2021 15:38',
    'modifiedDate' => '15.11.2021 18:38'
),
array(
    'parentCode' => '10061.0',
    'firstName' => 'test',
    'lastName' => 'test',
    'status' => 'Open',
    'initialPassword' => '9Au7WN16Zt',
    'language' => 'English (UK)',
    'createdDate' => '29.10.2021 15:41',
    'modifiedDate' => '29.10.2021 15:42'
)
);

并且在数组下方为 $columnTypeList，

$columnTypeList = array(
   0 => 'FORMAT_TEXT',
   1 => 'FORMAT_TEXT',
   2 => 'FORMAT_TEXT',
   3 => 'FORMAT_TEXT',
   4 => 'FORMAT_TEXT',
   5 => 'FORMAT_TEXT',
   6 => 'FORMAT_TEXT',
   7 => 'FORMAT_TEXT'
);

生成场景并为此提供一些解决方案。先感谢您！

标签： spreadsheetphpspreadsheetphp-7.4