1

When I generate an excel sheet from the database it shows an error that "excel file does not match format. Do you want to open it anyway?" and it says to click on ok button to upgrade format of excel file. When I click on ok it works fine... but in mobile its not open.

i want to generate microsoft excel file with no error.

//generating Excel File
$setSql = "SELECT * from demo table";
$setRec = mysqli_query($db, $setSql);  

$header_name="DATA LIST IN EXCEL";
$Event= "This is a demo";
date_default_timezone_set("Asia/Kolkata");
$date='Export Date:-'.date("l, jS \of F Y h:i:s A");
$columnHeader = ''; 
$columnHeader = "Name" . "\t" . "Date" . "\t". "Mode No" . "\t". "Address" . "\t". "eduction"."\t"."Organisation" . "\t". "Paid Status" . "\t";    
$setData = '';  

while ($rec = mysqli_fetch_row($setRec)) {  
    $rowData = '';  
    foreach ($rec as $value) {  
        $value = '"' . $value . '"' . "\t";  
        $rowData .= $value;  
    }  
    $setData .= trim($rowData) . "\n";  
}    
header("Content-type: application/octet-stream");  
header("Content-Disposition: attachment; filename= file_name.xls");  
header("Pragma: no-cache");  
header("Expires: 0");  
echo $header_name ."\t\t\t\t\t\t\t\n". $Event ."\t\t\t". $date ."\n". ucwords($columnHeader) . "\n" . $setData . "\n";
Lelio Faieta
  • 6,457
  • 7
  • 40
  • 74
Dhyaneshwar Shukla.
  • 65
  • 1
  • 6
  • What you try to generate is not an Excel file, as these do not contain raw data seperated by tabs – Nico Haase Feb 16 '19 at 20:36
  • Possible duplicate of [Export to CSV via PHP](https://stackoverflow.com/questions/4249432/export-to-csv-via-php) – Nico Haase Feb 16 '19 at 20:36
  • Added code. Pending peer review – Sourcerer Feb 19 '19 at 09:28
  • you are creating a CSV file with an extension of xls. You can do two things: name the file as CSV and open it in excel without errors or you can use a php library like PHPEXCEL to write the file as a real XLS file – Lelio Faieta Feb 19 '19 at 11:15

1 Answers1

1

Here you have our code.

Notice

ob_clean before download

This is the tricky point. If you have some content in the output buffer (maybe for an incorrect include file) it is sent with the file. So, you have to clean it before any download command

Added BOM header to CSV file

And, if you plan to open the file with Excel, and the file is UTF8, you have to add BOM header

public function reportAsset($budgetPeriod_id)
    {
        $timeProcess = round(microtime(true) / 1000);
        if ($budgetPeriod_id) {
            $budget = \App\Models\Invoicing\BudgetPeriod::select(['description'])->find((int) $budgetPeriod_id);
            $client = \App\Models\Structure\Client::select(['description'])->find($this->client_id);
            $filename = date('Ymd').'_'.$budget->description . '_' . $client->description . '.csv';
        $headers = [
            'Content-type' => 'text/csv;charset=UTF-8',
            'Content-Disposition' => 'attachment; filename=' . $filename,
            'Pragma' => 'no-cache',
            'Expires' => '0',
        ];

        $output = fopen($filename, "w");
        // JMA: Add BOM header
        fputs($output, $bom =( chr(0xEF) . chr(0xBB) . chr(0xBF) ));
        fputcsv($output, [_i('Asset_id'), _i('Asset'), _i('Client Category'), _i('Budget'), _i('Spent'), _i('Available'),_i('Spent') .' %', _i('# Invoices')], ';');
        $query = \App\Models\Invoicing\AssetBudget::query();
        $query->has('asset')
            ->has('clientCategory')
            ->with('asset:PROCESS_ID,Identificador_Processo', 'clientCategory:id,description')

            ->orderBy('asset_id', 'clientCategory_id', 'clientCategory.id')
            ->selectRaw('amount as total, asset_id, budgetPeriod_id, clientCategory_id')
            ->where('budgetPeriod_id', $budgetPeriod_id)
            ->chunk($this::BUDGET_CHUNK, function ($chunk_query) use ($budgetPeriod_id, $output, $timeProcess) {

            foreach ((array) $chunk_query as $report) {

                foreach ($report as $rep) {

                    $row = [];


                    // JMA: The amount has to be the individual amount per asset
                    //      So. we read asset_invoices where the invoice is in the budget period and category
                    // TODO: Replace this piece of code with consumedBudgetByRequest function in Invoicing BudgetController
                    // TODO: Try with calculateBudget but is not the same structure
                    //$invoices = \App\Library\Invoicing\BudgetCalculator::calculateBudget($rep->budgetPeriod_id, $rep->clientCategory_id, (array)$rep->asset_id);

                    $invoices=AssetInvoice::whereHas('invoice' , function ($invoice) use ($rep) {
                        $invoice->where('budgetPeriod_id',$rep->budgetPeriod_id)
                        ->where('clientCategory_id',$rep->clientCategory_id);
                        }
                    )
                        ->selectRaw('count(asset_id) as nInvoices, sum(amount) as spent')
                        ->where('asset_id',$rep->asset_id)
                        ->first();

                    // Log::debug('BudgetController->reportAsset: Invoices found='.$invoices->nInvoices.' spent='.$invoices->spent);

                    $row['asset_id'] = $rep->asset->PROCESS_ID;
                    $row['Identificador_Processo'] = $rep->asset->Identificador_Processo;
                    $row['clientCategory'] = $rep->clientCategory->description;
                    $row["budget"] = floatval($rep->total);
                    $row["spent"] = floatval($invoices->spent);
                    $row["available"] = $row["budget"] - $row["spent"];
                    if(floatval($rep->total)==0 ){
                        $row["percentaje"] = '';
                    }else{
                        $row["percentaje"] = number_format((float)(floatval($invoices->spent)*100)/ floatval($rep->total), 2, '.', ''); 
                    }

                    $row["nInvoices"] = floatval($invoices->nInvoices);
                    // Uncomment this line to monitor time consumption
                    // $row["times"] = $timeProcess - round(microtime(true) / 1000);

                    fputcsv($output, $row, ';');

                }

            }
        });

        fclose($output);

        // CHECK THIS: Clean output buffer before sending files (avoid initial whitespaces)
        if (ob_get_contents()) {
            ob_clean();
        }
        // Send csv file as response
        return response()->download($filename, $filename, $headers)->deleteFileAfterSend(true);

    }
Sourcerer
  • 1,891
  • 1
  • 19
  • 32
  • The explanation was in my previous answer but a moderator deleted it. See the comment in code: // CHECK THIS: Clean output buffer before sending files (avoid initial whitespaces). – Sourcerer Feb 27 '19 at 12:21