Repository: PHPOffice/PhpSpreadsheet (php)

Issue:
Allow differing column counts per row in CSV export

This is:

- [ ] a bug report
- [x] a feature request
- [ ] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

### What is the expected behavior?

There should be an option for each row in a CSV file to have a different number of columns.

Consider a spreadsheet where the first few rows are not actually column headers, but rather some custom heading (in my case a requirement of some third party system consuming the CSV file).

Represented as an array, this might look like:

```php
$data = [
    ['Pretext', 'More pretext'],
    ['this', 'is not', 'a header'],
    ['value 1'],
    ['value 2'],
    ['value 3']
];
```

And as a spreadsheet:

A       | B            | C  
------- | ------------ | ---------- 
Pretext | More pretext |           
this    | is not       | a header  
value 1 |              |           
value 2 |              |           
value 3 |              |           

Writing to a CSV should have the option to exclude the blank cells on each row:

```
"Pretext","More pretext"
"this","is not","a header"
"value 1"
"value 2"
"value 3"
```

### What is the current behavior?

Currently, the spreadsheet described above would save as a csv like:

```
"Pretext","More pretext",
"this","is not","a header"
"value 1",,
"value 2",,
"value 3",,
```

Where the row with the most columns dictates the column count for all rows, and any that fall short are filled with blanks.

### What are the steps to reproduce?

Please provide a [Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve) of code that exhibits the issue without relying on an external Excel file or a web server:

```php
<?php

namespace PhpOffice\PhpSpreadsheetTests\Writer\Csv;

use PHPUnit\Framework\TestCase;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Shared\File;

class VariableColumnsTest extends TestCase
{
    public function testVariableColumns()
    {
        $spreadsheet = new Spreadsheet();
        $spreadsheet->setActiveSheetIndex(0);
        $spreadsheet->getActiveSheet()->setCellValue('A1', 'A1');
        $spreadsheet->getActiveSheet()->setCellValue('B1', 'B1');
        $spreadsheet->getActiveSheet()->setCellValue('A2', 'A2');
        $spreadsheet->getActiveSheet()->setCellValue('B2', 'B2');
        $spreadsheet->getActiveSheet()->setCellValue('C2', 'C2');
        $spreadsheet->getActiveSheet()->setCellValue('A3', 'A3');

        $filename = tempnam(File::sysGetTempDir(), 'phpspreadsheet-test');
        $writer = IOFactory::createWriter($spreadsheet, 'Csv');
        $writer->save($filename);

        $contents = file_get_contents($filename);

        $rows = explode(PHP_EOL, $contents);

        // current
        $this->assertEquals('"A1","B1",""', $rows[0]);
        $this->assertEquals('"A2","B2","C2"', $rows[1]);
        $this->assertEquals('"A3","",""', $rows[2]);

        // desired
        $this->assertEquals('"A1","B1"', $rows[0]);
        $this->assertEquals('"A2","B2","C2"', $rows[1]);
        $this->assertEquals('"A3"', $rows[2]);
    }
}
```


### Which versions of PhpSpreadsheet and PHP are affected?

PhpSpreadsheet 1.10.1

PHP 7.3

Pull request:
CSV Writer Allow Varying Number of Columns
Supersedes PR #1415 by @AndrewMonty, which went stale in May 2020, and which is not directly usable due to changes between now and then. Fix #1414, which also went stale; I will remove the stale status and reopen the issue pending the merging of this PR.

Add an option to CSV Writer so that it writes the cells for a row only through the highest data column used in the row, rather than through the highest data column used in the worksheet.

This is:

- [ ] a bugfix
- [x] a new feature
- [ ] refactoring
- [ ] additional unit tests

Checklist:

- [x] Changes are covered by unit tests
  - [] Changes are covered by existing unit tests
  - [x] New unit tests have been added
- [x] Code style is respected
- [x] Commit message explains **why** the change is made (see https://github.com/erlang/otp/wiki/Writing-good-commit-messages)
- [ ] CHANGELOG.md contains a short summary of the change and a link to the pull request if applicable
- [ ] Documentation is updated as necessary

### Why this change is needed?

Provide an explanation of why this change is needed, with links to any Issues (if appropriate).
If this is a bugfix or a new feature, and there are no existing Issues, then please also create an issue that will make it easier to track progress with this PR.


Patch:
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 229a32022..12b595933 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -10,6 +10,7 @@ and this project adheres to [Semantic Versioning](https://semver.org).
 ### Added
 
 - Xlsx Reader Optionally Ignore Rows With No Cells. [Issue #3982](https://github.com/PHPOffice/PhpSpreadsheet/issues/3982) [PR #4035](https://github.com/PHPOffice/PhpSpreadsheet/pull/4035)
+- Option for CSV output file to have varying numbers of columns for each row. [Issue #1415](https://github.com/PHPOffice/PhpSpreadsheet/issues/1415) [PR #4076](https://github.com/PHPOffice/PhpSpreadsheet/pull/4076)
 
 ### Changed
 
@@ -33,7 +34,8 @@ and this project adheres to [Semantic Versioning](https://semver.org).
 - Mpdf and Tcpdf Borders on Merged Cells. [Issue #3557](https://github.com/PHPOffice/PhpSpreadsheet/issues/3557) [PR #4047](https://github.com/PHPOffice/PhpSpreadsheet/pull/4047)
 - Xls Conditional Format Improvements. [PR #4030](https://github.com/PHPOffice/PhpSpreadsheet/pull/4030) [PR #4033](https://github.com/PHPOffice/PhpSpreadsheet/pull/4033)
 - Conditional Range Unions and Intersections [Issue #4039](https://github.com/PHPOffice/PhpSpreadsheet/issues/4039) [PR #4042](https://github.com/PHPOffice/PhpSpreadsheet/pull/4042)
-- Csv Reader allow use of html mimetype. [Issue #4036](https://github.com/PHPOffice/PhpSpreadsheet/issues/4036) [PR #4049](https://github.com/PHPOffice/PhpSpreadsheet/pull/4040)
+- Csv Reader allow use of html mimetype. [Issue #4036](https://github.com/PHPOffice/PhpSpreadsheet/issues/4036) [PR #4040](https://github.com/PHPOffice/PhpSpreadsheet/pull/4040)
+- Problem rendering line chart with missing plot label. [PR #4074](https://github.com/PHPOffice/PhpSpreadsheet/pull/4074)
 - More RTL in Xlsx/Html Comments [Issue #4004](https://github.com/PHPOffice/PhpSpreadsheet/issues/4004) [PR #4065](https://github.com/PHPOffice/PhpSpreadsheet/pull/4065)
 - Empty String in sharedStrings. [Issue #4063](https://github.com/PHPOffice/PhpSpreadsheet/issues/4063) [PR #4064](https://github.com/PHPOffice/PhpSpreadsheet/pull/4064)
 
diff --git a/docs/topics/reading-and-writing-to-file.md b/docs/topics/reading-and-writing-to-file.md
index 389917952..aa6298df9 100644
--- a/docs/topics/reading-and-writing-to-file.md
+++ b/docs/topics/reading-and-writing-to-file.md
@@ -679,6 +679,18 @@ $writer->setOutputEncoding('SJIS-WIN');
 $writer->save("05featuredemo.csv");
 ```
 
+#### Writing CSV files with varying numbers of columns
+
+A CSV file can have a different number of columns in each row. This
+differs from the default behavior when saving as a .csv in Excel, but
+can be enabled in PhpSpreadsheet by using the following code:
+
+``` php
+$writer = new \PhpOffice\PhpSpreadsheet\Writer\Csv($spreadsheet);
+$writer->setVariableColumns(true);
+$writer->save("05featuredemo.csv");
+```
+
 #### Decimal and thousands separators
 
 If the worksheet you are exporting contains numbers with decimal or
diff --git a/src/PhpSpreadsheet/Writer/Csv.php b/src/PhpSpreadsheet/Writer/Csv.php
index 6ce968d8c..de7de30a1 100644
--- a/src/PhpSpreadsheet/Writer/Csv.php
+++ b/src/PhpSpreadsheet/Writer/Csv.php
@@ -3,6 +3,7 @@
 namespace PhpOffice\PhpSpreadsheet\Writer;
 
 use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
+use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
 use PhpOffice\PhpSpreadsheet\Spreadsheet;
 use Stringable;
 
@@ -54,6 +55,13 @@ class Csv extends BaseWriter
      */
     private string $outputEncoding = '';
 
+    /**
+     * Whether number of columns should be allowed to vary
+     * between rows, or use a fixed range based on the max
+     * column overall.
+     */
+    private bool $variableColumns = false;
+
     /**
      * Create a new CSV.
      */
@@ -105,7 +113,17 @@ class Csv extends BaseWriter
         $maxRow = $sheet->getHighestDataRow();
 
         // Write rows to file
+        $row = 0;
         foreach ($sheet->rangeToArrayYieldRows("A1:$maxCol$maxRow", '', $this->preCalculateFormulas) as $cellsArray) {
+            ++$row;
+            if ($this->variableColumns) {
+                $column = $sheet->getHighestDataColumn($row);
+                if ($column === 'A' && !$sheet->cellExists("A$row")) {
+                    $cellsArray = [];
+                } else {
+                    array_splice($cellsArray, Coordinate::columnIndexFromString($column));
+                }
+            }
             $this->writeLine($this->fileHandle, $cellsArray);
         }
 
@@ -303,4 +321,26 @@ class Csv extends BaseWriter
         }
         fwrite($fileHandle, $line);
     }
+
+    /**
+     * Get whether number of columns should be allowed to vary
+     * between rows, or use a fixed range based on the max
+     * column overall.
+     */
+    public function getVariableColumns(): bool
+    {
+        return $this->variableColumns;
+    }
+
+    /**
+     * Set whether number of columns should be allowed to vary
+     * between rows, or use a fixed range based on the max
+     * column overall.
+     */
+    public function setVariableColumns(bool $pValue): self
+    {
+        $this->variableColumns = $pValue;
+
+        return $this;
+    }
 }


Test patch:
diff --git a/tests/PhpSpreadsheetTests/Writer/Csv/VariableColumnsTest.php b/tests/PhpSpreadsheetTests/Writer/Csv/VariableColumnsTest.php
new file mode 100644
index 000000000..2d7270814
--- /dev/null
+++ b/tests/PhpSpreadsheetTests/Writer/Csv/VariableColumnsTest.php
@@ -0,0 +1,81 @@
+<?php
+
+declare(strict_types=1);
+
+namespace PhpOffice\PhpSpreadsheetTests\Writer\Csv;
+
+use PhpOffice\PhpSpreadsheet\Shared\File;
+use PhpOffice\PhpSpreadsheet\Spreadsheet;
+use PhpOffice\PhpSpreadsheet\Writer\Csv;
+use PHPUnit\Framework\TestCase;
+
+class VariableColumnsTest extends TestCase
+{
+    public function testVariableColumns(): void
+    {
+        $spreadsheet = new Spreadsheet();
+        $sheet = $spreadsheet->getActiveSheet();
+        $sheet->fromArray(
+            [
+                [1, 2, 3, 4],
+                [1, 2],
+                [1, 2, 3, 4, 5],
+                [],
+                [1],
+                [1, 2, 3],
+            ]
+        );
+
+        $filename = File::temporaryFilename();
+        $writer = new Csv($spreadsheet);
+        $writer->setVariableColumns(true);
+        $writer->save($filename);
+
+        $contents = (string) file_get_contents($filename);
+        unlink($filename);
+        $spreadsheet->disconnectWorksheets();
+
+        $rows = explode(PHP_EOL, $contents);
+
+        self::assertSame('"1","2","3","4"', $rows[0]);
+        self::assertSame('"1","2"', $rows[1]);
+        self::assertSame('"1","2","3","4","5"', $rows[2]);
+        self::assertSame('', $rows[3]);
+        self::assertSame('"1"', $rows[4]);
+        self::assertSame('"1","2","3"', $rows[5]);
+    }
+
+    public function testFixedColumns(): void
+    {
+        $spreadsheet = new Spreadsheet();
+        $sheet = $spreadsheet->getActiveSheet();
+        $sheet->fromArray(
+            [
+                [1, 2, 3, 4],
+                [1, 2],
+                [1, 2, 3, 4, 5],
+                [],
+                [1],
+                [1, 2, 3],
+            ]
+        );
+
+        $filename = File::temporaryFilename();
+        $writer = new Csv($spreadsheet);
+        self::assertFalse($writer->getVariableColumns());
+        $writer->save($filename);
+
+        $contents = (string) file_get_contents($filename);
+        unlink($filename);
+        $spreadsheet->disconnectWorksheets();
+
+        $rows = explode(PHP_EOL, $contents);
+
+        self::assertSame('"1","2","3","4",""', $rows[0]);
+        self::assertSame('"1","2","","",""', $rows[1]);
+        self::assertSame('"1","2","3","4","5"', $rows[2]);
+        self::assertSame('"","","","",""', $rows[3]);
+        self::assertSame('"1","","","",""', $rows[4]);
+        self::assertSame('"1","2","3","",""', $rows[5]);
+    }
+}