PhpSpreadSheet : Writing to a specific WorkSheet

Question

I'm new to PhpSpreadSheet, and I'd like to know if there is a way to load a CSV into a specific WorkSheet ?

I tried the code bellow but it seems to keep loadind the CSVs into the first WorkSheet :/.

<?php

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Csv;

$spreadsheet = new Spreadsheet();
$spreadsheet->setActiveSheetIndex(0);

$pathToCsv1 = 'files/csv_files/1.csv';
$pathToCsv2 = 'files/csv_files/2.csv';
$pathToCsv3 = 'files/csv_files/3.csv';
$pathToCsv4 = 'files/csv_files/4.csv';

$aCsvFiles = array($pathToCsv1, $pathToCsv2, $pathToCsv3, $pathToCsv4);

foreach ($aCsvFiles as $index => $csvFile) {
    $reader = new Csv();
    $reader->setDelimiter(';');
    $reader->loadIntoExisting($csvFile, $spreadsheet);
    $workSheet = $spreadsheet->createSheet();
    $spreadsheet->setActiveSheetIndex($index + 1);
}

$writer = new Xlsx($spreadsheet);
$writer->save('files/xls_files/all.xlsx');

I only get 4.csv in all.xlsx but i have the created WorkSheets

score 0 · Answer 1 · answered Nov 07 '18 at 15:48

Combining Multiple Files into a Single Spreadsheet Object

While you can limit the number of worksheets that are read from a workbook file using the setLoadSheetsOnly() method, certain readers also allow you to combine several individual "sheets" from different files into a single Spreadsheet object, where each individual file is a single worksheet within that workbook. For each file that you read, you need to indicate which worksheet index it should be loaded into using the setSheetIndex() method of the $reader, then use the loadIntoExisting() method rather than the load() method to actually read the file into that worksheet.

Example:

$inputFileType = 'Csv';
$inputFileNames = [
    './sampleData/example1.csv',
    './sampleData/example2.csv'
    './sampleData/example3.csv'
];

/**  Create a new Reader of the type defined in $inputFileType  **/
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);

/**  Extract the first named file from the array list  **/
$inputFileName = array_shift($inputFileNames);
/**  Load the initial file to the first worksheet in a `Spreadsheet` Object  **/
$spreadsheet = $reader->load($inputFileName);
/**  Set the worksheet title (to the filename that we've loaded)  **/
$spreadsheet->getActiveSheet()
    ->setTitle(pathinfo($inputFileName,PATHINFO_BASENAME));

/**  Loop through all the remaining files in the list  **/
foreach($inputFileNames as $sheet => $inputFileName) {
    /**  Increment the worksheet index pointer for the Reader  **/
    $reader->setSheetIndex($sheet+1);
    /**  Load the current file into a new worksheet in Spreadsheet  **/
    $reader->loadIntoExisting($inputFileName,$spreadsheet);
    /**  Set the worksheet title (to the filename that we've loaded)  **/
    $spreadsheet->getActiveSheet()
        ->setTitle(pathinfo($inputFileName,PATHINFO_BASENAME));
}

Note that using the same sheet index for multiple sheets won't append files into the same sheet, but overwrite the results of the previous load. You cannot load multiple CSV files into the same worksheet.

https://phpspreadsheet.readthedocs.io/en/develop/topics/reading-files/#combining-multiple-files-into-a-single-spreadsheet-object

PhpSpreadSheet : Writing to a specific WorkSheet

1 Answers1