excel cannot open the file because the file format or file extension is not valid #357

janjaw007 · 2024-02-13T05:33:08Z

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.

sayid · 2024-02-13T05:33:28Z

您好，我已经收到您的信件，将尽快回复您。

janjaw007 · 2024-02-13T05:46:51Z

<?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);
}

janjaw007 · 2024-02-13T06:09:43Z

example (30).xlsx

HappyP0R0 · 2024-05-12T11:49:51Z

Any fix on this?
The writeToFile function works, but writeToStdOut() just returns my html page where generation is started as .xlsx file

sayid · 2024-05-12T11:50:22Z

您好，我已经收到您的信件，将尽快回复您。

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

excel cannot open the file because the file format or file extension is not valid #357

excel cannot open the file because the file format or file extension is not valid #357

janjaw007 commented Feb 13, 2024

sayid commented Feb 13, 2024 via email

janjaw007 commented Feb 13, 2024

janjaw007 commented Feb 13, 2024

HappyP0R0 commented May 12, 2024

sayid commented May 12, 2024 via email

excel cannot open the file because the file format or file extension is not valid #357

excel cannot open the file because the file format or file extension is not valid #357

Comments

janjaw007 commented Feb 13, 2024

sayid commented Feb 13, 2024 via email

janjaw007 commented Feb 13, 2024

janjaw007 commented Feb 13, 2024

HappyP0R0 commented May 12, 2024

sayid commented May 12, 2024 via email