search

mk-j / PHP_XLSXWriter

Lightweight XLSX Excel Spreadsheet Writer in PHP
MIT License
1.84k stars 663 forks source link

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

Open janjaw007 opened 6 months ago

janjaw007 commented 6 months ago


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 commented 6 months ago

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

janjaw007 commented 6 months ago 
<?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 commented 6 months ago

example (30).xlsx

HappyP0R0 commented 3 months ago

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

sayid commented 3 months ago

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