Exported file cannot open it says file format or file extension is not valid
<?php
require 'vendor_office/autoload.php'; // Make sure to include the path to your autoload.php file
include 'includes/connection.php';
include 'php/year.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
$department = $_GET['dept'];
$department = preg_replace('/[^a-zA-Z0-9\-]/', '', $department); // Remove all characters except letters, numbers, and hyphens
$filename = $department . '.xlsx';
// Function to sanitize the data for Excel export
function sanitizeData($data)
{
// Remove any HTML and PHP tags
$data = strip_tags($data);
// Convert special characters to HTML entities
$data = htmlentities($data, ENT_QUOTES);
// Remove any line breaks or tabs
$data = str_replace(array("\r", "\n", "\t"), '', $data);
return $data;
}
// Fetch the data from the database using prepared statement to avoid SQL injection
$sql = "SELECT pcar.title, pcar.description,
IF(targets.year = ?, COALESCE(targets.totalt, 0), 0) AS totalt,
IF(targets.year = ?, COALESCE(targets.qt1, 0), 0) AS qt1,
IF(targets.year = ?, COALESCE(targets.qt2, 0), 0) AS qt2,
IF(targets.year = ?, COALESCE(targets.qt3, 0), 0) AS qt3,
IF(targets.year = ?, COALESCE(targets.qt4, 0), 0) AS qt4,
COALESCE(pcar_data.pcar_id, 0) AS ppcar_id,
COALESCE(pcar.id, 0) AS dpcar_id,
COALESCE(pcar.description, Null) AS pcar_des,
COALESCE(pcar_data.qa1, 0) AS qa1,
COALESCE(pcar_data.qa2, 0) AS qa2,
COALESCE(pcar_data.qa3, 0) AS qa3,
COALESCE(pcar_data.qa4, 0) AS qa4,
COALESCE(pcar_data.totala, 0) AS totala,
COALESCE(pcar_data.remarks, Null) AS remarks,
COALESCE(pcar_data.evidence, Null) AS evidence,
COALESCE(pcar_data.evidence_unique_name, Null) AS evidence_un,
COALESCE(pcar_data.user_id, 0) AS user_id,
COALESCE(pcar_data.user, 0) AS user
FROM pcar
LEFT JOIN pcar_data ON pcar.id = pcar_data.pcar_id
AND pcar_data.year = ?
LEFT JOIN account ON pcar_data.user_id = account.id
LEFT JOIN targets ON pcar.id = targets.pcart_id
WHERE pcar.department IN (?, 'ALL DEPT')
ORDER BY pcar.id";
// Prepare the statement
$stmt = $link->prepare($sql);
// Bind the parameters for the year and department
$stmt->bind_param("sssssss", $year2, $year2, $year2, $year2, $year2, $year2, $department);
// Execute the query
$stmt->execute();
// Get the result set
$result = $stmt->get_result();
// Check if there are any results
if ($result->num_rows > 0) {
// Create a new PhpSpreadsheet spreadsheet
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
// Create the table header
$sheet->setCellValue('A1', 'Services Programs/Projects');
$sheet->setCellValue('B1', 'Total Targets');
$sheet->setCellValue('C1', 'Q1');
$sheet->setCellValue('D1', 'Q2');
$sheet->setCellValue('E1', 'Q3');
$sheet->setCellValue('F1', 'Q4');
$sheet->setCellValue('G1', 'Q1 Accomplishments');
$sheet->setCellValue('H1', 'Q2 Accomplishments');
$sheet->setCellValue('I1', 'Q3 Accomplishments');
$sheet->setCellValue('J1', 'Q4 Accomplishments');
$sheet->setCellValue('K1', 'Total Accomplishments');
$sheet->setCellValue('L1', 'Remarks');
$sheet->setCellValue('M1', 'Evidence');
// Loop through the data and populate the Excel sheet
$rowNumber = 2;
while ($row = $result->fetch_assoc()) {
$sheet->setCellValue('A' . $rowNumber, sanitizeData($row['title']));
$sheet->setCellValue('B' . $rowNumber, sanitizeData($row['totalt']));
$sheet->setCellValue('C' . $rowNumber, sanitizeData($row['qt1']));
$sheet->setCellValue('D' . $rowNumber, sanitizeData($row['qt2']));
$sheet->setCellValue('E' . $rowNumber, sanitizeData($row['qt3']));
$sheet->setCellValue('F' . $rowNumber, sanitizeData($row['qt4']));
$sheet->setCellValue('G' . $rowNumber, sanitizeData($row['qa1']));
$sheet->setCellValue('H' . $rowNumber, sanitizeData($row['qa2']));
$sheet->setCellValue('I' . $rowNumber, sanitizeData($row['qa3']));
$sheet->setCellValue('J' . $rowNumber, sanitizeData($row['qa4']));
$sheet->setCellValue('K' . $rowNumber, sanitizeData($row['totala']));
$sheet->setCellValue('L' . $rowNumber, sanitizeData($row['remarks']));
$sheet->setCellValue('M' . $rowNumber, sanitizeData($row['evidence']));
$rowNumber++;
}
$writer = new Xlsx($spreadsheet);
// Save the Excel file to a temporary location
$tempFilename = tempnam(sys_get_temp_dir(), 'excel');
$writer->save($tempFilename);
// Provide the Excel file for download
header('Content-Type: application/vnd.openxmlformats- officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $filename . '"');
header('Cache-Control: max-age=0');
header('Expires: 0');
header('Cache-Control: must-revalidate');
header('Pragma: public');
header('Content-Length: ' . filesize($tempFilename));
// Output the contents of the file
readfile($tempFilename);
// Delete the temporary Excel file
unlink($tempFilename);
exit;
} else {
// No results found
echo "No data found.";
}
// Close the prepared statement and database connection
$stmt->close();
$link->close();
?>
Export the data from table
