How can PHP and SQL be used to generate an Excel spreadsheet with summarized data for monthly duty schedules?

To generate an Excel spreadsheet with summarized data for monthly duty schedules using PHP and SQL, you can query the database for the necessary information, then use a library like PHPExcel to create the Excel file with the summarized data. 

<?php
// Connect to your database
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Query the database for monthly duty schedules
$sql = "SELECT employee_name, SUM(hours_worked) AS total_hours
        FROM duty_schedules
        WHERE MONTH(date) = 1
        GROUP BY employee_name";

$result = $conn->query($sql);

// Create Excel file
require_once 'PHPExcel.php';

$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Employee Name');
$objPHPExcel->getActiveSheet()->setCellValue('B1', 'Total Hours Worked');

$row = 2;
while ($row_data = $result->fetch_assoc()) {
    $objPHPExcel->getActiveSheet()->setCellValue('A' . $row, $row_data['employee_name']);
    $objPHPExcel->getActiveSheet()->setCellValue('B' . $row, $row_data['total_hours']);
    $row++;
}

// Save Excel file
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('monthly_duty_schedule.xlsx');

$conn->close();
?>

Keywords

PHP SQL Excel spreadsheet monthly duty schedules summarized data

Related Questions