Excel Readable CSV in PHP

Yes, csv is simple BUT if you want it readable in Excel (at least on Windows), you need to prepend it with a hidden BOM and as UTF8.

Tried every possible suggestions in stackoverflow that says it works BUT it doesn't.

So, try the phpoffice approach. It works for me...

Install

1~$ cd /an/isolated/directory
2~$ composer require php-office/phpspreadsheet
3~$ composer install

Try this code:

 1<?php
 2    require 'vendor/autoload.php';
 3
 4    use PhpOffice\PhpSpreadsheet\Spreadsheet;
 5    use PhpOffice\PhpSpreadsheet\Writer\Csv;
 6
 7
 8    const CSV_FILENAME='csv-example.csv';
 9
10    $data = array (
11        array("George", "male", 91),
12        array("Lito", "male", 40),
13        array("Katarzyna", "female", 18),
14    );
15
16    # It starts as a Spreadsheet...
17    $spreadsheet = new Spreadsheet();
18    $worksheet = $spreadsheet->getActiveSheet();
19
20    # Create the headers
21    $worksheet->getCell("A1")->setValue('name');
22    $worksheet->getCell("B1")->setValue('sex');
23    $worksheet->getCell("C1")->setValue('age');
24
25    # write the data
26    $cell = 2; // at row2 since above headers are in row1
27    foreach ($data as $row) {
28        $cellColumn='A';
29        foreach ($row as $item) {
30            $worksheet->getCell("$cellColumn$cell")->setValue(mb_convert_encoding($item, "UTF-8", "auto"));
31            $cellColumn++;
32        }
33        $cell++;
34
35    }
36
37    // Convert to CSV UTF8-BOM encoding so it is Excel readable
38    $writer = new Csv($spreadsheet);
39    $writer->setUseBOM(true);
40    $writer->save(CSV_FILENAME);

Tips: Verify the encoding is "UTF8-BOM" in Notepad++. In bash you can check using this approach: https://ismael.casimpan.com/quicktasks/check-csv-bom-using-bash/

Reference: