How to Format Excel File with Styles, Fonts, Colors, Tables etc with Pure PHP

How to format excel file with styles, fonts, colors, tables etc with pure PHP?

The following example would probably help you:

  1 <?php
  2 header('Content-type: application/excel');                                  
  3 header('Content-Disposition: attachment; filename="test.xls"');
  4 ?>
  5 
  6 <table>
  7 <tr><th>Column 1</th><th>Column 2</th></tr>
  8 <tr><td style="font-size:200%">Answer 1</td><td style="color:#f00">Answer 2<    /td></tr>
  9 <tr><td colspan="2" style="font-weight:bold">Answer 3 with 2 columns</td></t    r>
 10 </table>

Save this as a .php file on your server.

What it does is: add headers to force the browser thing you're delivering an Excel file. And then returning a table inside of it. My Excel picks this up perfectly.

Note: the sample HTML comes from the answer that you had already found. I have just added the headers to the beginning.

Note: You just changed your question and added an example. Your example does NOT use html! You are delivering a TAB-delimited file where each line ends with a newline.

The proper way to do this is to really generate HTML, ie, use a <table> at the beginning, use <tr> and </tr> for each row, and put the fields in <td> tags, and then finish with the closing </table>, so basically:

$data='<table>';
// excel content with overloaded terminals
if (mssql_num_rows($resCollection)>0) {
  while ($rowCollection = mssql_fetch_object($resCollection)) {
    $data.='<tr>';
    $data .= '<td>'.$rowCollection->Name."</td><td>".$rowCollection->Address."</td><td>"
            .$rowCollection->TerminalNotes."</td><td>".$rowCollection->TerminalAmount
            ."</td><td>".$rowCollection->DayAmountAll."</td><td>"
            .$rowCollection->LastPaymentTime."</td>";
    $data.='</tr>';

  }
}
$data.='</table>';

Then change your last line to:

echo iconv('utf-8', 'cp1251', "$data"); 

If that works I suggest you change the output to have <th> tags around your table headers, instead of the div that is in your example.

Formatting csv output of query

Since a csv file is nothing more than a text file with some comma seperated values you can't format it. As you kind of pointed out already you want to switch to an xls file (Excel) here.

You could check out some libraries, like PHPExcel, that should make it easy to create and format an Excel file.

Creating Multiple Excel Sheets using Pure PHP - Possible?

You can create the file from scratch - for which you should know the file format and since office 2007 this standard is open.

You can refere http://en.wikipedia.org/wiki/Office_Open_XML to get started!

Export html table data to Excel using JavaScript / JQuery is not working properly in chrome browser

Excel export script works on IE7+, Firefox and Chrome.

function fnExcelReport()
{
    var tab_text="<table border='2px'><tr bgcolor='#87AFC6'>";
    var textRange; var j=0;
    tab = document.getElementById('headerTable'); // id of table

    for(j = 0 ; j < tab.rows.length ; j++) 
    {     
        tab_text=tab_text+tab.rows[j].innerHTML+"</tr>";
        //tab_text=tab_text+"</tr>";
    }

    tab_text=tab_text+"</table>";
    tab_text= tab_text.replace(/<A[^>]*>|<\/A>/g, "");//remove if u want links in your table
    tab_text= tab_text.replace(/<img[^>]*>/gi,""); // remove if u want images in your table
    tab_text= tab_text.replace(/<input[^>]*>|<\/input>/gi, ""); // reomves input params

    var ua = window.navigator.userAgent;
    var msie = ua.indexOf("MSIE "); 

    if (msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./))      // If Internet Explorer
    {
        txtArea1.document.open("txt/html","replace");
        txtArea1.document.write(tab_text);
        txtArea1.document.close();
        txtArea1.focus(); 
        sa=txtArea1.document.execCommand("SaveAs",true,"Say Thanks to Sumit.xls");
    }  
    else                 //other browser not tested on IE 11
        sa = window.open('data:application/vnd.ms-excel,' + encodeURIComponent(tab_text));  

    return (sa);
}

Just create a blank iframe:

<iframe id="txtArea1" style="display:none"></iframe>

Call this function on:

<button id="btnExport" onclick="fnExcelReport();"> EXPORT </button>

PhpSpreadsheet - Download file instead of saving it

I solved it with a workaround. I temporarily save the file on the server, then I load the content into a variable and serve it as a download file. Then I delete the file from the server.

Workaround:

$date = date('d-m-y-'.substr((string)microtime(), 1, 8));
$date = str_replace(".", "", $date);
$filename = "export_".$date.".xlsx";

try {
    $writer = new Xlsx($response["spreadsheet"]);
    $writer->save($filename);
    $content = file_get_contents($filename);
} catch(Exception $e) {
    exit($e->getMessage());
}

header("Content-Disposition: attachment; filename=".$filename);

unlink($filename);
exit($content);

---

Related Topics