[SOLVED] php to excel format

[kevin_newbie]

Hello,

 

I created a snippet that allows me to get mysql data onto an excel file but I want to know is there a way to format these cells and merge them to?

 

<?php
$submit = $_POST['submit'];
if ($submit != "")
{
include 'connect.php';
$sql = "Select * from form";
$now_date = date('mdY');
$title = "Dump For Table form from Database $now_date";
//execute query
$result = mysql_query($sql);
if (isset($submit))
{
$file_type = "vnd.ms-excel";
$file_ending = "xls";
}
header("Content-Type: application/$file_type");
header("Content-Disposition: attachment; filename=Coupons_$now_date.$file_ending");
header("Pragma: no-cache");
header("Expires: 0");

echo("$title\n");
$sep = "\t";
$sep = "\t";
for ($i = 0; $i < mysql_num_fields($result); $i++)
{
	echo mysql_field_name($result,$i) . "\t";
}
print("\n");

while($row = mysql_fetch_row($result))
{
	$schema_insert = "";
	for($j=0; $j<mysql_num_fields($result);$j++)
	{
		if(!isset($row[$j]))
			$schema_insert .= "NULL".$sep;
		elseif ($row[$j] != "")
			$schema_insert .= "$row[$j]".$sep;
		else
			$schema_insert .= "".$sep;
	}
	$schema_insert = str_replace($sep."$", "", $schema_insert);

	$schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
	$schema_insert .= "\t";
	print(trim($schema_insert));
	print "\n";
}
}
else
    {
?>

<form action = "<?= $_SERVER['PHP_SELF'];?>" method = "post">
<input type = "submit" name = "submit" value = "Download Spreadsheet">
</form>
<?
}
?>

 

[Garethp]

Ofcourse there is.

 

Take

 

   {
      $schema_insert = "";
      for($j=0; $j<mysql_num_fields($result);$j++)
      {
         if(!isset($row[$j]))
            $schema_insert .= "NULL".$sep;
         elseif ($row[$j] != "")
            $schema_insert .= "$row[$j]".$sep;
         else
            $schema_insert .= "".$sep;
      }
      $schema_insert = str_replace($sep."$", "", $schema_insert);

      $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
      $schema_insert .= "\t";
      print(trim($schema_insert));
      print "\n";
   }

 

You could have

 

   {
      $schema_insert = "";
$schema_insert .= $row['users'] . " " . $row['email'] . $sep;
      $schema_insert = str_replace($sep."$", "", $schema_insert);

      $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
      $schema_insert .= "\t";
      print(trim($schema_insert));
      print "\n";
   }

 

Which instead of taking every single column, would just display the column users and email in one cell. This is ofcourse based on your code, so you should be able to do with it whatever you choose

[kevin_newbie]

What i meant by format is like put background colors on the headers and auto width on each column so that it is all clean. Sorry if that I wasn't clear about what I wanted. When I meant merge was like merge the first row which is the title because right now it lies on cell one but I want to merge across that entire row.

 

Thanks though for what you did I can use it for other projects

[Mark Baker]

There is.

You create a real Excel file using a library such as PHPExcel

[kevin_newbie]

Thank you very much