PHP_XLSXWriter
PHP_XLSXWriter copied to clipboard
mk-j
excel cannot open the file because the file format or file extension is not valid
Warning: ZipArchive::close(): Read error: No such file or directory in D:\html\app\xxxx\report\xlsxwriter.class.php on line 122
Warning: readfile(C:\Windows\Temp\xls4D11.tmp): failed to open stream: No such file or directory in D:\html\app\xxxx\report\xlsxwriter.class.php on line 76
when generate file on server the file error like this
excel cannot open the file because the file format or file extension is not valid
and open with notepad it shown error like above
when generate file on xampp the file is no error i dont know why.
<?php
include_once("xlsxwriter.class.php");
ini_set('display_errors', 1);
ini_set('log_errors', 1);
error_reporting(E_ALL & ~E_NOTICE);
$filename = "example.xlsx";
header('Content-disposition: attachment; filename="' . XLSXWriter::sanitize_filename($filename) . '"');
header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header('Content-Transfer-Encoding: binary');
header('Cache-Control: must-revalidate');
header('Pragma: public');
ob_clean();
flush();
$start = isset($_GET['startdate']) ? $_GET['startdate'] : '';
$end = isset($_GET['enddate']) ? $_GET['enddate'] : '';
$campaign_id = isset($_GET['campaign_id']) ? $_GET['campaign_id'] : '';
$campaign_id_lead_list = isset($_GET['campaign_id_lead_list']) ? $_GET['campaign_id_lead_list'] : '';
$campaign_id_selected = isset($_GET['campaign_id_selected']) ? $_GET['campaign_id_selected'] : '';
$campaign_id_selected_lead_list = isset($_GET['campaign_id_selected_lead_list']) ? $_GET['campaign_id_selected_lead_list'] : '';
$status_selected_list = isset($_GET['status_selected_list']) ? $_GET['status_selected_list'] : '';
//echo $start . "+" . $end . "+" . $campaign_id_selected_lead_list;
// $tmp = json_decode($_POST['data'], true);
// $root = "temp/";
// $start = $tmp["startdate"];
// $end = $tmp["enddate"];
// $campaign_id = $tmp["campaign_id"];
// $campaign_id_lead_list = $tmp["campaign_id_lead_list"];
// $campaign_id_selected = $tmp["campaign_id_selected"];
// $campaign_id_selected_lead_list = $tmp["campaign_id_selected_lead_list"];
$start_dd = substr($start, 0, 2); // 16/03/2016
$start_mm = substr($start, 3, 2);
$start_yy = substr($start, 6, 4);
$startdate = $start_yy . $start_mm . $start_dd;
$startdate2 = $start_yy . '-' . $start_mm . '-' . $start_dd;
$startdatelead = $start_yy . $start_mm;
$end_dd = substr($end, 0, 2); // 16/03/2016
$end_mm = substr($end, 3, 2);
$end_yy = substr($end, 6, 4);
$enddate = $end_yy . $end_mm . $end_dd;
$enddate2 = $end_yy . '-' . $end_mm . '-' . $end_dd;
$enddatelead = $end_yy . $end_mm;
//$currentdatetime = date("Y") . '-' . date("m") . '-' . date("d") . ' ' . date("H:i:s");
$report_date = $startdate2 . ' - ' . $enddate2;
// $campaign_name_header = "";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
while ($rLeadName = mysqli_fetch_assoc($resultLeadName)) {
$rowLDN[] = [
$rLeadName['list_name']
];
}
while ($rStatusName = mysqli_fetch_assoc($resultStatusName)) {
$rowSTSN[] = [
$rStatusName['status']
];
}
$currentDate = date("Y-m-d");
$headerReportName = array(
'ReportStatusCustomer' => '@', //text
'REASON' => '@', //text
'Customer' => '@',
'4' => '@',
'5' => '@',
'6' => '@', //custom
'7' => '@',
'8' => '@',
'TSR' => '@',
'Date Time' => '@',
);
// $headerCampaignName = array(
// 'Campaign Name :' => '@', //text
// '$list_name' => $rCampaignName['campaign_name'], //text
// 'Customer' => '@',
// '4' => '@',
// '5' => '@',
// '6' => '@', //custom
// '7' => '@',
// '8' => '@',
// 'TSR' => '@',
// 'Date Time' => '@',
// );
$headerListLotName = array(
'List Lot Name :' => '@', //text
' ' => '@', //text
'Customer' => '@',
'4' => '@',
'5' => '@',
'6' => '@', //custom
'7' => '@',
'8' => '@',
'TSR' => '@',
'Date Time' => '@',
);
// $headerLeadSource = array(
// 'Lead Source :' => '@', //text
// '$list_name' => '@', //text
// 'Customer' => '@',
// '4' => '@',
// '5' => '@',
// '6' => '@', //custom
// '7' => '@',
// '8' => '@',
// 'TSR' => '@',
// 'Date Time' => '@',
// );
$headerLeadSource = array(
'Lead Source :' => '@', //text
implode(',', array_column($rowLDN, 0)) => '@', //text
'Customer' => '@',
'4' => '@',
'5' => '@',
'6' => '@', //custom
'7' => '@',
'8' => '@',
'TSR' => '@',
'Date Time' => '@',
);
$headerPeriod = array(
'Period :' => '@', //text
$startdate2 . " - " . $enddate2 => '@', //text
'Customer' => '@',
'4' => '@',
'5' => '@',
'6' => '@', //custom
'7' => '@',
'8' => '@',
'TSR' => '@',
'Date Time' => '@',
);
$headerStatus = array(
'Status :' => '@', //text
implode(',', array_column($rowSTSN, 0)) => '@', //text
'Customer' => '@',
'4' => '@',
'5' => '@',
'6' => '@', //custom
'7' => '@',
'8' => '@',
'TSR' => '@',
'Date Time' => '@',
);
$headerReportasAt = array(
'Report as at :' => '@', //text
$currentDate => 'date', //text
'Customer' => '@',
'4' => '@',
'5' => '@',
'6' => '@', //custom
'7' => '@',
'8' => '@',
'TSR' => '@',
'Date Time' => '@',
);
$headerWhiteSpace = array(
' ' => '@', //text
' ' => '@', //text
'Customer' => '@',
'4' => '@',
'5' => '@',
'6' => '@', //custom
'7' => '@',
'8' => '@',
'TSR' => '@',
'Date Time' => '@',
);
$header1 = array(
'STATUS' => '@', //text
'REASON' => '@', //text
'Customer' => '@',
'4' => '@',
'5' => '@',
'6' => '@', //custom
'7' => '@',
'8' => '@',
'TSR' => '@',
'Date Time' => '@',
);
$header2 = array(
'STATUS' => 'string', //text
'REASON' => '@', //text
'No.' => 'integer',
'Refer no' => '0',
'Source' => 'string',
'ID' => '0', //custom
'First Name' => 'string',
'Last Name' => 'string',
'TSR' => 'string',
'Date Time' => 'string',
);
// $rows[] = [
// 'status',
// 'reason',
// 'row_number',
// 'OSR_REFERENC',
// 'list_name',
// 'id_num',
// 'first_name',
// 'last_name',
// 'TSR',
// 'date_time',
// ];
while ($rCampaignName = mysqli_fetch_assoc($resultCampaignName)) {
$rowCPN[] = [
$rCampaignName['campaign_name']
];
}
while ($r = mysqli_fetch_assoc($result)) {
$rows[] = [
$r['status'],
$r['reason'],
$r['row_number'],
$r['OSR_REFERENC'],
$r['list_name'],
$r['id_num'],
$r['first_name'],
$r['last_name'],
$r['TSR'],
$r['date_time'],
];
}
$writer = new XLSXWriter();
$writer->setAuthor('Some Author');
$styles1 = array('font' => 'Arial', 'font-size' => 12, 'font-style' => 'bold', 'fill' => '#eee', 'halign' => 'center', 'border' => 'left,right,top,bottom', 'border-style' => 'medium', 'border-color' => '#222', 'widths' => [35, 55, 10, 20, 70, 10, 15, 20, 30, 15]);
$styles3 = array('font' => 'Arial', 'font-size' => 12, 'font-style' => 'bold', 'fill' => '#eee', 'valign' => 'distributed', 'halign' => 'center', 'border' => 'left,right,top,bottom', 'border-style' => 'medium', 'border-color' => '#222', 'widths' => [35, 55, 10, 20, 70, 10, 15, 20, 30, 15]);
$stylesHeaderReportName = array('font' => 'Arial', 'font-size' => 22, 'font-style' => 'bold', 'halign' => 'left', 'border' => 'left,right,top,bottom', 'border-style' => 'thin', 'widths' => [35, 55, 10, 20, 70, 10, 15, 20, 30, 15]);
$stylesHeaderCol1Merge = array('font' => 'Arial', 'font-size' => 16, 'font-style' => 'bold', 'halign' => 'left', 'border' => 'left,right,top,bottom', 'border-style' => 'thin', 'widths' => [35, 55, 10, 20, 70, 10, 15, 20, 30, 15]);
$stylesHeaderCol1MergeEx = array('font' => 'Arial', 'font-size' => 16, 'font-style' => 'bold', 'color' => '#eee', 'halign' => 'left', 'border' => 'left,right,top,bottom', 'border-style' => 'thin', 'widths' => [35, 55, 10, 20, 70, 10, 15, 20, 30, 15]);
$stylesHeaderWhiteSpace = array('font' => 'Arial', 'font-size' => 16, 'font-style' => 'bold', 'color' => '#FFF', 'halign' => 'left', 'border' => 'left,right,top,bottom', 'border-style' => 'thin', 'border-color' => '#eee', 'widths' => [35, 55, 10, 20, 70, 10, 15, 20, 30, 15]);
$styles2 = array('font' => 'Arial', 'font-size' => 10, 'font-style' => 'bold', 'fill' => '#FFE5B4', 'halign' => 'center', 'border' => 'left,right,top,bottom', 'border-style' => 'medium', 'border-color' => '#222');
$writer->writeSheetHeader('BasicFormats', $headerReportName, $stylesHeaderReportName);
$writer->writeSheetHeader(
'BasicFormats',
array(
'Campaign Name :' => '@', //text
implode(',', array_column($rowCPN, 0)) => '@', //text
'Customer' => '@',
'4' => '@',
'5' => '@',
'6' => '@', //custom
'7' => '@',
'8' => '@',
'TSR' => '@',
'Date Time' => '@',
),
$stylesHeaderCol1Merge
);
$writer->writeSheetHeader('BasicFormats', $headerListLotName, $stylesHeaderCol1Merge);
$writer->writeSheetHeader('BasicFormats', $headerLeadSource, $stylesHeaderCol1Merge);
$writer->writeSheetHeader('BasicFormats', $headerPeriod, $stylesHeaderCol1Merge);
$writer->writeSheetHeader('BasicFormats', $headerStatus, $stylesHeaderCol1Merge);
$writer->writeSheetHeader('BasicFormats', $headerReportasAt, $stylesHeaderCol1MergeEx);
$writer->writeSheetHeader('BasicFormats', $headerWhiteSpace, $stylesHeaderWhiteSpace);
$writer->writeSheetHeader('BasicFormats', $header1, $styles3);
$writer->markMergedCell('BasicFormats', $start_row = 0, $start_col = 0, $end_row = 0, $end_col = 9); // merge $headerReportName change start_row and end_row
$writer->markMergedCell('BasicFormats', $start_row = 1, $start_col = 1, $end_row = 1, $end_col = 9); // merge $headerCampaignName change start_row and end_row
$writer->markMergedCell('BasicFormats', $start_row = 2, $start_col = 1, $end_row = 2, $end_col = 9); // merge $headerListLotName change start_row and end_row
$writer->markMergedCell('BasicFormats', $start_row = 3, $start_col = 1, $end_row = 3, $end_col = 9); // merge $headerLeadSource change start_row and end_row
$writer->markMergedCell('BasicFormats', $start_row = 4, $start_col = 1, $end_row = 4, $end_col = 9); // merge $headerPeriod change start_row and end_row
$writer->markMergedCell('BasicFormats', $start_row = 5, $start_col = 1, $end_row = 5, $end_col = 9); // merge $headerStatus change start_row and end_row
$writer->markMergedCell('BasicFormats', $start_row = 6, $start_col = 1, $end_row = 6, $end_col = 9); // merge headerReportasAt change start_row and end_row
$writer->markMergedCell('BasicFormats', $start_row = 7, $start_col = 0, $end_row = 7, $end_col = 0); // merge $headerWhiteSpace change start_row and end_row
$writer->markMergedCell('BasicFormats', $start_row = 8, $start_col = 2, $end_row = 8, $end_col = 7); // merge customer change start_row and end_row
$writer->markMergedCell('BasicFormats', $start_row = 8, $start_col = 0, $end_row = 9, $end_col = 0); // merge status change start_row and end_row
$writer->markMergedCell('BasicFormats', $start_row = 8, $start_col = 1, $end_row = 9, $end_col = 1); // merge reason change start_row and end_row
$writer->markMergedCell('BasicFormats', $start_row = 8, $start_col = 8, $end_row = 9, $end_col = 8); // merge tsr change start_row and end_row
$writer->markMergedCell('BasicFormats', $start_row = 8, $start_col = 9, $end_row = 9, $end_col = 9); // merge date_time change Start_row and end_row
$writer->writeSheetHeader('BasicFormats', $header2, $styles1);
foreach ($rows as $row)
$writer->writeSheetRow('BasicFormats', $row, $styles2);
$writer->writeToStdOut();
//$writer->writeToFile('example.xlsx');
//echo $writer->writeToString();
mysqli_close($conn);
exit(0);
}
Any fix on this? The writeToFile function works, but writeToStdOut() just returns my html page where generation is started as .xlsx file
