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

Question

I need a way to write an excel file with format through Pure PHP. WARNING:Please, this Q not about the libs, plugins and so on- do not disturb. I've googled a lot and didn`t find the Answer, but in some article on StackOverflow, some guy mentioned the HTML/CSS format way HTML format solutions that didn't work and I've tried to do this, but in file which was saved columns where filled with tags and nothing happens. May be there are some professionals who can help me with pure code writing an Excel through PHP, HTML, CSS or something.

And again with example, I've already used concept like this wich didn't help:

    // headers for excel
$headers .= "<div style='font:bold 14px Times;'>Журнал остатков и инкассаций</div>\n";
$headers .= "Начало периода\t ".date('d.m.Y')."0:00 \n";
$headers .= "Начало периода\t ".date('d.m.Y')."23:59 \n\n";

$headers .= "Терминал\t";
$headers .= "Место расположения\t";
$headers .= "Дата\t";
$headers .= "Время\t";
$headers .= "Сумма инкассации\t";
$headers .= "Банкноты\t";

// excel content with overloaded terminals
if (mssql_num_rows($resCollection)>0) {
  while ($rowCollection = mssql_fetch_object($resCollection)) {

    $data .= $rowCollection->Name."\t".$rowCollection->Address."\t"
            .$rowCollection->TerminalNotes."\t".$rowCollection->TerminalAmount
            ."\t".$rowCollection->DayAmountAll."\t"
            .$rowCollection->LastPaymentTime."\n";

  }
}

$fileName = 'collection'.date('d_m_Y_H_i_s').'.xls';


header("Content-type: application/vnd.ms-excel");// application/excel had also been used
header("Content-Disposition: attachment; filename=$fileName");
echo iconv('utf-8', 'cp1251', "$headers\n$data");

I've used not only divs and tables too - it didn't work though.

Eljakim · Accepted Answer · 2011-07-08T07:00:23.347

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.

Now it's just up to you to query the database and generate the table data dynamically. — Eljakim, Jul 08 '11 at 05:59
this type of thing has worked for me previously - it's much simper than trying to write a native XLS file. the only problem is, non Excel Spreadsheet Apps (i.e. Numbers.app, OpenOffice, etc) are probably not going to read the file correctly. — Stephen, Jul 08 '11 at 06:28
So, for me - the question is open and it didn't work properly. — Arthur Kushman, Jul 08 '11 at 06:51
@zeusakm: What were the results that you got using my example? Have you tried it? — Eljakim, Jul 08 '11 at 06:56
@zeusakm: I have added another example in my answer based on your expanded question. — Eljakim, Jul 08 '11 at 07:00
Yeah thanks for that and the point to mention is that there should be mix with \t or \n symbols - if table only table should be used. — Arthur Kushman, Jul 08 '11 at 08:09
"FYI"... or whatever... Styles works **only** inline, doesn't work by — Máxima Alekz, Jun 24 '17 at 14:58

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

1 Answers1

Linked