Bonjour,

J'essaye de me mettre à Spreadsheet car je dois importer des donner en provenance d'un fichier excel/tableur.
Je ne trouve pas leur doc très clair et je n'arrive pas à faire ce que je veux.
Je veux importer le contenu du fichier et formater les dates au format qui va bien pour les enregistrer dans la bdd mysql (donc format YYYY-MM-JJ)
C'est donc sur cette partie que je bloc.
Je suppose bien qu'il faut utiliser getNumberFormat() et/ou setFormatCode('AAAA-MM-JJ') vers la ligne 65, mais comment ?

Voici mon code actuel :

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
 
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\IReader;
use PhpOffice\PhpSpreadsheet\IOFactory;
 
\PhpOffice\PhpSpreadsheet\Cell\Cell::setValueBinder( new \PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder() );
 
class chunkReadFilter implements \PhpOffice\PhpSpreadsheet\Reader\IReadFilter
{
    private $_startRow = 0;
 
    private $_endRow = 0;
 
    /**
     * We expect a list of the rows that we want to read to be passed into the constructor.
     *
     * @param mixed $startRow
     * @param mixed $chunkSize
     */
    public function __construct($startRow, $chunkSize)
    {
        $this->_startRow = $startRow;
        $this->_endRow = $startRow + $chunkSize;
    }
 
    public function readCell($column, $row, $worksheetName = '')
    {
        //  Only read the heading row, and the rows that were configured in the constructor
//        if (($row == 1) || ($row >= $this->_startRow && $row < $this->_endRow)) {
        if  ($row >= $this->_startRow && $row < $this->_endRow) {
            return true;
        }
 
        return false;
    }
}
 
 
    $inputFileType = 'Xls';
    $inputFileName = '06largescale.xlsx';
//$inputFileName = __DIR__ . '/../../06largescale.xlsx';
 
    /**  Identify the type of $inputFileName  **/
    $inputFileType = \PhpOffice\PhpSpreadsheet\IOFactory::identify($inputFileName);
    /**  Create a new Reader of the type that has been identified  **/
    $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
    /**  Load $inputFileName to a Spreadsheet Object  **/
    $spreadsheet = $reader->load($inputFileName);
 
    /*  Define how many rows we want for each "chunk"  **/
    $chunkSize = 20;
 
    /*  Loop to read our worksheet in "chunk size" blocks  **/
    for ($startRow = 2; $startRow <= 240; $startRow += $chunkSize) {
        echo 'Loading WorkSheet using configurable filter for headings row 1 and for rows ',$startRow,' to ',($startRow + $chunkSize - 1),'<br />';
        /*  Create a new Instance of our Read Filter, passing in the limits on which rows we want to read  **/
        $chunkFilter = new chunkReadFilter($startRow, $chunkSize);
        /*  Tell the Reader that we want to use the new Read Filter that we've just Instantiated  **/
        $reader->setReadFilter($chunkFilter);
        /*  Load only the rows that match our filter from $inputFileName to a PhpSpreadsheet Object  **/
        $spreadsheet = $reader->load($inputFileName);
 
 
        $sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
 
        var_dump($sheetData);
 
        if (empty($sheetData[$startRow]['A']) || ($sheetData[$startRow]['A'] === '') && empty($sheetData[$startRow]['B']) || ($sheetData[$startRow]['B'] === '') ) break;
 
 
    }
 
foreach ($sheetData as $data) {
      /* boucle pour  enregistrer chaque ligne dans ma bdd (après vérif des données) */
}
Avez-vous une idée ?
Merci