Link to home
Start Free TrialLog in
Avatar of Lia Nungaray
Lia NungarayFlag for United States of America

asked on

Formatting exported Excel spreadsheet from PHP

I have a few tables that can be exported to an Excel spreadsheet by using the following piece of code:

//Following is the place to decide to output as HTML or create the file instead.
//If requested parameter has been sent by the link to this php pave
//then it will set the headers so it will send it as "file" as in my sample
if (isset($_GET['xldownload']))
  //Also checking xldownload parameter value to make sure it is set correctly
  //Not necessary but good habit to expect a known value instead only checking for the existence
  if (intval($_GET['xldownload'])==1) {
    //removing the {DOWNLOADLINK}, because this will not be shown in the HTML page
    $data = str_replace("{DOWNLOADLINK}","",$data);
    header('Content-type: application/');
    header('Content-Disposition: attachment; filename=tabledata.xls');
  //Sending output as HTML to the client browser
  //So we need to put a link for download
  //We use the same script name however with xldownload=1 value (check out the href property of the anchor
  $data = str_replace("{DOWNLOADLINK}","<a href=\"sample.php?xldownload=1\">Download File</a>",$data);
  echo $data;

Many thanks to smozgur by the way, works great! But now I'm running into a tiny and almost insignifant problem, but I would like to correct it nevertheless... When I click on the link to export the table, the formatting is not kept. The table looks very nice in a browser, but it looks awful in Excel. I wouldn't like for whomever has to download the table to have the need to format the spreadsheet every time... Any ideas?
Avatar of amit_g
Flag of United States of America image

If you are using any CSS to format the page, that might cause it. Change the CSS link to absolute or add a <base> in page's head section.

<link rel="STYLESHEET" href="../style/default.css" type="text/css">
<link rel="STYLESHEET" href="" type="text/css">

or add

<BASE href="">
Avatar of Lia Nungaray


I'm actually placing the CSS directly into the php page:

include '../mysql_connect.php';
if (isset($_GET["periodname"]))
  $periodName = $_GET["periodname"];
if (isset($_GET["pstartdate"]))
  $pstartDate = $_GET["pstartdate"];
if (isset($_GET["penddate"]))
  $pendDate = $_GET["penddate"];
//Set counter to format table
//Set variable to count total Mondays - Saturdays
//Set variable to count Sundays

table.rpt {
      border-width: 1px;
      border-spacing: 1px;
      border-style: none;

What kind of formatting is lost? Could you show us it in work?
The formatting that is shown in the Excel spreadsheet differs only in color and in border thickness. I would like the cell's borders not to be so thick. Is there a way to change this Excel setting from PHP?
Avatar of amit_g
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I ended up changing colors that were more close to the different color options Excel gives when formatting cells. Thanks anyways!