Home>

Data for each worker is extracted from the work table (worker ID, work date, work time), and daily reports, monthly reports, and total Excel are converted into workbooks for each worker.
We are thinking of outputting all together.
An Excel file of templates is prepared, which is a daily report, monthly report, and total sheet.
When outputting, daily reports are divided into sheets for each date, monthly reports are divided into sheets for each month, and the total is a total of all data in one sheet.

Problems i am experiencing

Because PhpSpreadsheet is inexperienced (old PHPExcel is inexperienced. PHP itself is also inexperienced), in order to check the movement for the time being, information on the net
Check the movement while checking, read the template, set the data on the three sheets, and output the Excel file
confirmed.
However, there is a situation where you cannot copy the sheet, set the data for the copied sheet, and delete the original sheet.
(Excel file output is not reached)
Anyway, it is inexperienced and there are no people familiar with it.

Applicable source code
<? php
          require_once 'c: \ windows \ system32 \ vendor \ autoload.php';
          $reader = new PhpOffice \ PhpSpreadsheet \ Reader \ Xlsx ();
          $reader->setIncludeCharts (TRUE);
          $book = $reader->load ('nippou_template.xlsx');
          $book->setActiveSheetIndex (0);
          $sheet = $book->getActiveSheet ();
          $sheet = $baseSheet->copy ();
          $sheet->setTitle ("nippou_new". $sheetNum, false);
          $book->addSheet ($sheet);
          $book->setActiveSheetIndex (4);
          $sheet = $book->getActiveSheet ();
          $sheet->setCellValue ('C2', 'AAAAAAAA');
          $book->setActiveSheetIndex (1);
          $sheet = $book->getActiveSheet ();
          $sheet = $baseSheet->copy ();
          $sheet->setTitle ("soukei_new". $sheetNum, false);
          $book->addSheet ($sheet);
          $book->setActiveSheetIndex (5);
          $sheet = $book->getActiveSheet ();
          $sheet->setCellValue ('D3', 'AAAAAAAA');
          $sheet->setCellValue ('V3', 'BBBBBBBB');
          $sheet->setCellValue ('L3', 'CCCCCCCC');
          header ("Content-Description: File Transfer");
          header ('Content-Disposition: attachment;filename = "nippou_test_download.xlsx"');
          header ('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
          header ('Content-Transfer-Encoding: binary');
          header ('Cache-Control: must-revalidate, post-check = 0, pre-check = 0');
          header ('Expires: 0');
          ob_end_clean ();// Clear buffer
          $writer = new PhpOffice \ PhpSpreadsheet \ Writer \ Xlsx ($book);
          $writer->save ('php: // output');
?>
php
  • Answer # 1

    I also had a hard time creating a system for Excel output in PHP as well, with no experience in development and alone.
    Since PHPSpreadSheet hits more information than Japanese information of PHPSpreadSheet,
    I rewritten how to write PHPExcel to PHPSpreadSheet version while watching Migration from PHPExcel in the official document.
    I do n’t think it ’s all in it, but it ’s a great replacement for most things.

    Sheet copy

    Copying a sheet requires cloning the original sheet into a book.
    It's on the official document.

    Copying Worksheets

    $clonedWorksheet = clone $spreadsheet->getSheetByName ('Worksheet 1');
    $clonedWorksheet->setTitle ('Copy of Worksheet 1');
    $spreadsheet->addSheet ($clonedWorksheet);

    ■ Put values ​​in the copied sheet

    Same as putting a value in an existing sheet.
    In the above example, after setting the value in$clonedWorksheet, insert it into the book and you will get the desired result.
    After inserting it into the workbook, just specify the sheet and fill in the values, just like the code shown.

    I don't know how the index selects the sheet, so
    It is a method to get by sheet name.

    $sheet = $book->->setActiveSheetIndexByName (sheet name);
    $sheet->setCellValue ('D3', 'AAAAAAAA');// Set the value


    If the date is given the month as the seat name, this may be easier to understand.

    ■ Delete original sheet

    This is also in the official documentation.
    Removing a Worksheet

    $sheetIndex = $spreadsheet->getIndex (
        $spreadsheet->getSheetByName ('Worksheet 1')
    );
    $spreadsheet->removeSheetByIndex ($sheetIndex);


    I'm sorry, but deleting the active sheet results in an error
    I have a clear memory of "Why is it!"
    If you get an error, you may want to doubt that area.

    As an aside, you should also check the version of PHPSpreadSheet.
    I don't know where the timing is, but it seems that the name and hierarchy (namespace?) Of the Cell class has changed
    If I didn't check which version of the information I looked up on the net, I could get angry that "I don't have such a class" even if I wrote it in the same way.