PhpExcel many columns causes the file to be broken-CodePudding

I am generating an excel file using phpexcel, the columns are 186 so I have created an array of the header columns and dynamically adding them

            $spreadsheet = new Spreadsheet();
            $spreadsheet->setActiveSheetIndex(0);
            $sheet = $spreadsheet->getActiveSheet();
            $sheet->setTitle(lang('invoices'));
            $col ='A1';
            foreach($columns as $key => $value){
                $sheet->setCellValue($col,  $value);
                  $col; //in a loop;
            }

the file is created well but the columns are broken as seen in the picture below. anyone ever encountered this issue?

UPDATING THE QUESTION

Now the xcel sheet has over 150 columns but not all of them are to be filled with data. only like 15 columns are to be put data.

Iam looping my db result but when inserts the sheet comes with jumping data. here is how I insert the data.

     $row = 0;
            foreach ($data as $data_row) {
                $sheet->setCellValue('A1'. $row, '0');
                $sheet->setCellValue('B1'. $row, $data_row->id);
                $sheet->setCellValue('C1'. $row, $data_row->customer_id);
                $sheet->setCellValue('D1' . $row, $data_row->invoice_id);
                $sheet->setCellValue('E1' . $row, $data_row->invoice_details);
                $sheet->setCellValue('F1' . $row, $data_row->account_set);
                $sheet->setCellValue('G1' . $row, $data_row->issue_date);
                $sheet->setCellValue('H1' . $row, 'TAS');
                $sheet->setCellValue('I1' . $row, $this->sma->formatNo($data_row->currency_rate));
                $sheet->setCellValue('J1' . $row, $this->sma->formatDecimal($data_row->grand_total));
                $sheet->setCellValue('K1' . $row, $this->sma->formatDecimal($data_row->invoice_tax));
                $sheet->setCellValue('L1' . $row, $this->sma->formatDecimal($data_row->grand_total));
                $sheet->setCellValue('M1' . $row, $this->sma->formatDecimal($data_row->grand_total));
                $sheet->setCellValue('M1' . $row, $this->sma->formatDecimal($data_row->invoice_tax));
                $sheet->setCellValue('N1' . $row, $this->sma->formatDecimal($data_row->grand_total));
                $sheet->setCellValue('O1' . $row, $this->sma->formatDecimal($data_row->grand_total));
                $sheet->setCellValue('P1' . $row, $this->sma->formatDecimal($data_row->invoice_tax));
                if ($data_row->source_type == SHIPMENT)
                    $sheet->setCellValue('E1' . $row, 'Newspaper distribution on ' . $this->sma->hrsd($data_row->issue_date) . ' to ' . $data_row->route_name);
                $row  ;
            }

and here is the end result of data

CodePudding user response：

This isn't a PHPExcel or a PhpSpreadsheet issue: what's happening here is that you're misunderstanding how the PHP increment operator works with a string value that contains mixed alpha and numeric characters.

Take a look at the output from your increment when there's no reference to PHPExcel or PhpSpreadsheet to see what's happening here:

$col ='A1';
for($x = 0; $x <= 12;   $x){
    echo $col, PHP_EOL;
      $col; //in a loop;
}

PHP increments the numeric character until it reaches the size limit for that numeric (1 digit, so 1 through 9). It then resets the digit to 0,and increments the alpha (so 'A' becomes 'B'), and subsequent increments will increment the numeric again until it reaches 9, then it will reset the numeric back to 0 and increment the alpha again:

A1
A2
A3
A4
A5
A6
A7
A8
A9
B0
B1
B2
B3

Either set the column to A and concatenate with a hard-coded row 1

$col ='A';
foreach($columns as $key => $value){
    $sheet->setCellValue($col . '1',  $value);
      $col; //in a loop;
}

or make use of PhpSpreadsheet's fromArray() method to populate the whole row from the array that you already have