PhpSpreadSheet :写入特定的WorkSheet

Question

我是PhpSpreadSheet新手，我想知道是否有一种方法将CSV加载到特定的WorkSheet中？

我尝试了下面的代码，但是它似乎将CSV装载到第一个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');

我只在all.xlsx中得到4.csv，但是我已经创建了WorkSheets

​

​

Answer 1

将多个文件组合成一个电子表格对象

虽然可以使用setLoadSheetsOnly()方法限制从工作簿文件中读取的工作表的数量，但某些读取器还允许将来自不同文件的多个“工作表”组合到单个Spreadsheet对象中，其中每个单独的文件都是该工作簿中的单个工作表。对于所读取的每个文件，您需要使用$reader的$reader方法指示它应该加载到哪个工作表索引中，然后使用loadIntoExisting()方法而不是load()方法将文件实际读取到该工作表中。

示例：

$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));
}

请注意，对多个工作表使用相同的工作表索引不会将文件附加到同一个工作表中，而是会覆盖以前加载的结果。不能将多个CSV文件加载到同一个工作表中。

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