PHP code example of threshold / php-excel

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

threshold / php-excel example snippets

$writer = new \Threshold\PhpExcel\Writer\Writer();
$writer
    ->setShouldCreateNewSheetsAutomatically(false)
    ->setShouldUseInlineStrings(true)
    ->openToBrowser("test.xlsx");

$sheet = $writer->getCurrentSheet();
$sheet
    ->setName("Statistics")
    ->setMergeRanges(["A1:B1"]);

$writer->addRow(\Threshold\PhpExcel\Writer\Factory\WriterEntityFactory::createRow([
    \Threshold\PhpExcel\Writer\Factory\WriterEntityFactory::createCell(
        "Statistics",
        (new \Threshold\PhpExcel\Writer\Entity\Style\Style())
            ->setCellAlignment(\Threshold\PhpExcel\Writer\Entity\Style\CellAlignment::CENTER)
            ->setFontBold())
]));

$writer->close();

$writer = new \Threshold\PhpExcel\Writer\Writer();
$writer
    ->setShouldCreateNewSheetsAutomatically(false)
    ->setShouldUseInlineStrings(true)
    ->openToFile("path/to/save/file/test.xlsx");

$sheet = $writer->getCurrentSheet();
$sheet
    ->setName("Statistics")
    ->setMergeRanges(["A1:B1"]);

$writer->addRow(\Threshold\PhpExcel\Writer\Factory\WriterEntityFactory::createRow([
    \Threshold\PhpExcel\Writer\Factory\WriterEntityFactory::createCell(
        "Statistics",
        (new \Threshold\PhpExcel\Writer\Entity\Style\Style())
            ->setCellAlignment(\Threshold\PhpExcel\Writer\Entity\Style\CellAlignment::CENTER)
            ->setFontBold())
]));

$writer->close();

// In each round, you must state that you are in the last round and how many rounds there will be in total.
$loopMax     = $request->query->get("loopMax", 1);
$loopCounter = $request->query->get("loopCounter", 1);
$writer      = new \Threshold\PhpExcel\Writer\Writer();
$writer
    ->setTempFolder("your/temp/folder/path/if/you/want")
    ->setShouldCreateNewSheetsAutomatically(false)
    ->setShouldUseInlineStrings(true)
    ->setDefaultRowStyle((new \Threshold\PhpExcel\Writer\Entity\Style\Style())
        ->setFontName("Calibri")
        ->setFontSize(11))
    ->setLoop($loopMax, $loopCounter); // Here you have to pass the circles,
                                       // from this it will know whether the writing will continue
                                       // or the file must be closed.
    
// $writer->close() until the last round returns the name of the temporary folder
// created in the first round in the temporary path,
// this must be specified here in order to continue generating the XLSX file.
if ($request->query->get("tempFolderName")) {
    $writer
        ->setTempFolderName($request->query->get("tempFolderName"));
}

$writer->openToFile("php://output")

$sheet = $writer->getCurrentSheet();
$sheet
    ->setName("Statistics");
    
if ($loopCounter === 1) {
    // The merged cells are counted from the last written row in each round,
    // so for example the cell merging of the address only needs to be entered in the first round.
    // In later rounds, the "A1:B1" cell merge will not apply to the first row, for example,
    // if we wrote 15 rows in the previous round, then cells A and B of the 16th row will be merged.
    // In this case, "A1:B1" will be as if we entered "A16:B16" in the first round.
    $sheet->setMergeRanges(["A1:B1"]);
    
    $writer->addRow(\Threshold\PhpExcel\Writer\Factory\WriterEntityFactory::createRow([
        \Threshold\PhpExcel\Writer\Factory\WriterEntityFactory::createCell("Statistics", (new \Threshold\PhpExcel\Writer\Entity\Style\Style())
            ->setCellAlignment(\Threshold\PhpExcel\Writer\Entity\Style\CellAlignment::CENTER)
            ->setBorder((new \Threshold\PhpExcel\Writer\Entity\Style\Border())
                ->setBorderTop(\Threshold\PhpExcel\Writer\Entity\Style\Color::BLACK, \Threshold\PhpExcel\Writer\Entity\Style\Border::WIDTH_MEDIUM, \Threshold\PhpExcel\Writer\Entity\Style\Border::STYLE_SOLID)
                ->setBorderRight(\Threshold\PhpExcel\Writer\Entity\Style\Color::BLACK, \Threshold\PhpExcel\Writer\Entity\Style\Border::WIDTH_MEDIUM, \Threshold\PhpExcel\Writer\Entity\Style\Border::STYLE_SOLID)
                ->setBorderBottom(\Threshold\PhpExcel\Writer\Entity\Style\Color::BLACK, \Threshold\PhpExcel\Writer\Entity\Style\Border::WIDTH_MEDIUM, \Threshold\PhpExcel\Writer\Entity\Style\Border::STYLE_SOLID)
                ->setBorderLeft(\Threshold\PhpExcel\Writer\Entity\Style\Color::BLACK, \Threshold\PhpExcel\Writer\Entity\Style\Border::WIDTH_MEDIUM, \Threshold\PhpExcel\Writer\Entity\Style\Border::STYLE_SOLID))
            ->setFontName("Arial")
            ->setFontBold()
            ->setFontSize(20)),
        \Threshold\PhpExcel\Writer\Factory\WriterEntityFactory::createCell("", (new \Threshold\PhpExcel\Writer\Entity\Style\Style()) // Create empty cells with same Style to have borders
            ->setCellAlignment(\Threshold\PhpExcel\Writer\Entity\Style\CellAlignment::CENTER)
            ->setBorder((new \Threshold\PhpExcel\Writer\Entity\Style\Border())
                ->setBorderTop(\Threshold\PhpExcel\Writer\Entity\Style\Color::BLACK, \Threshold\PhpExcel\Writer\Entity\Style\Border::WIDTH_MEDIUM, \Threshold\PhpExcel\Writer\Entity\Style\Border::STYLE_SOLID)
                ->setBorderRight(\Threshold\PhpExcel\Writer\Entity\Style\Color::BLACK, \Threshold\PhpExcel\Writer\Entity\Style\Border::WIDTH_MEDIUM, \Threshold\PhpExcel\Writer\Entity\Style\Border::STYLE_SOLID)
                ->setBorderBottom(\Threshold\PhpExcel\Writer\Entity\Style\Color::BLACK, \Threshold\PhpExcel\Writer\Entity\Style\Border::WIDTH_MEDIUM, \Threshold\PhpExcel\Writer\Entity\Style\Border::STYLE_SOLID)
                ->setBorderLeft(\Threshold\PhpExcel\Writer\Entity\Style\Color::BLACK, \Threshold\PhpExcel\Writer\Entity\Style\Border::WIDTH_MEDIUM, \Threshold\PhpExcel\Writer\Entity\Style\Border::STYLE_SOLID))
            ->setFontBold()->setFontSize(20))
    ]));
}

// We write this line in each round. In the first round it goes to cell "A2", in the second round to cell "A3" and so on.
$writer->addRow(\Threshold\PhpExcel\Writer\Factory\WriterEntityFactory::createRow([
    \Threshold\PhpExcel\Writer\Factory\WriterEntityFactory::createCell("Round: " . $loopCounter)
]));

if (($writerResponse = $writer->close()) === true) {
    header('Content-Type: application/zip');
    header('Content-Disposition: attachment; filename="test.xlsx"');
} else {
    header('Content-Type: application/json; charset=utf-8');
    echo json_encode(["tempFolderName" => $writerResponse]);
}
exit;

$writer->sortSheetsByName(); // abc sorting
$writer->sortSheetsByName(true); // abc reverse sorting

$writer->openToFile("example.xlsx", true);
$writer->openToBrowser("example.xlsx", true);
$sheet = $writer->addNewSheetAndMakeItCurrent();

$requestWorksheetNames = ["a", "b", "c"]; // first request
$requestWorksheetNames = ["d", "b", "f"]; // second request

// loop over the names each request
foreach ($requestWorksheetNames as $worksheetName) {
    $sheet = $writer->getSheetByName($worksheetName); // In the first request, none of them exist, so all of them are created, but in the second request, the "b" worksheet already exists, so no new one is created, but the existing one can be written further.
}
$writer->sortSheetsByName(); // And at the end, you can sort them in order.

$sheet = $writer->getSheetByName("example");

if ($sheet->isNew()) {
    // add header
}