Solved

PHP export to Excel: Define Excel Sheetname

Posted on 2006-06-16
6
793 Views
Last Modified: 2013-12-12
Hi,
I am currently exporting data from php to excel using the following method:

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=ExcelFile.xls;");
header("Pragma: no-cache");
header("Expires: 0");
echo $data;

This works fine, but the generated excel file has a sheetname of: ".xls]ExcelFile(1)" , and when you try to rename the sheet it causes an error in excel.

How can I define the sheetname in my php file?

Many Thanks,
Matthew Cohen
0
Comment
Question by:mrcandu2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 8

Expert Comment

by:hiteshgupta1
ID: 16919341
Give a try to following code

// connection to mysql server
$con = mysql_connect("localhost","username","password");

// selecting the db
mysql_select_db("DBName",$con);

// preparing sql query
$query = "Select Query";
$result = mysql_query($query);

<B>// this is the title of the excel sheet
$title = "Title\n";
</b>

// looping the query resuls
while ($row = mysql_fetch_array($result)) {
   $cnt++;
   // concatinating the contents with \n character
   $content .= $cnt.",".$row['Field1'].",".$row['Field2'].."\n";
}

$export_data = $title.$content;

$filename = "your writable folder/export.csv";

// Let's make sure the file exists and is writable first.
if (is_writable($filename)) {

   // In our example we're opening $filename in write mode.
   // that's where $export_data will go when we fwrite() it.
   if (!$handle = fopen($filename, 'w')) {
        print "Cannot open file ($filename)";
        exit;
   }

   // Write $export_data to our opened file.
   if (!fwrite($handle, $export_data)) {
       print "Cannot write to file ($filename)";
       exit;
   }
   
   print "Success, wrote to file ($filename)";
   
   fclose($handle);
   header("Pragma: public"); // required
   header("Expires: 0");
   header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
   header("Cache-Control: private",false); // required for certain browsers
   header("Content-Type: application/ms-excel");
   header("Content-Disposition: attachment; filename=Sales_Report.csv");
   header("Content-Transfer-Encoding: binary");
   header("Content-Length: ".filesize($filename));
   readfile("$filename");
} else {
   print "The file $filename is not writable";
}

Hope this helps
0
 

Author Comment

by:mrcandu2
ID: 16919560
Thanks hiteshgupta1 , but I am really looking for an solution to generate an xls file and without writing to a file.
Here is my code in full ........

include("dbconnect.php");

$query = $_POST['query'];
$result = odbc_exec($conn,$query);
$count = odbc_num_fields($result);

//Define Data Variable of ODBC
$data = "";

//header info
for ($i = 1; $i <= $count; $i++)
    {
        $data .= odbc_field_name($result, $i)."\t";
    }
$data .= "\n";

//data Info
while(odbc_fetch_row($result))
    {
        for ($j = 1; $j <= $count; $j++)
      {
      $data .= odbc_result($result, $j)."\t";
      }
        $data .= "\n";
    }
      
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=ExcelFile.xls;");
header("Pragma: no-cache");
header("Expires: 0");

echo $data;

odbc_close($conn);
0
 
LVL 40

Accepted Solution

by:
Richard Quadling earned 125 total points
ID: 16919975
You can cheat and generate an XML file with the appropriate MS XML elements. The advantage to this is that you are not using magic methods, just straight XML.

Save an XLS in XML format and take a look at it.

0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:mrcandu2
ID: 16920151
Ahhhh...... Genius stuff.
This will allow me to do more than what I wanted to do, I will post my final code when finished.
Thanks Very Much.
Mr Candu
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16920428
In fact, EVERYTHING that goes into an XLS file can be put into the XML file. It is just a case of knowing what.

There is a GREAT set of dox on the MS website for MS Office and XML ...

http://www.microsoft.com/downloads/details.aspx?familyid=fe118952-3547-420a-a412-00a2662442d9&displaylang=en

is a start point.
0
 

Author Comment

by:mrcandu2
ID: 16921088
My Final Code.....................

include("dbconnect.php");
$title = $_POST['title'];
$query = $_POST['query'];
$result = odbc_exec($conn,$query);
$count = odbc_num_fields($result);

//XML Blurb
$data = '<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <Worksheet ss:Name="'.$title.'">
  <Table>
   <Row>';

//Field Name Data
for ($i = 1; $i <= $count; $i++)
      {
          $data .= '<Cell><Data ss:Type="String">'.odbc_field_name($result, $i).'</Data></Cell>';
      }
$data .= '</Row>';

//Row Data
while(odbc_fetch_row($result))
    {
            $data .= '<Row>';
            for ($j = 1; $j <= $count; $j++)
                  {
                        $data .= '<Cell><Data ss:Type="String">'.odbc_result($result, $j).'</Data></Cell>';
                  }
            $data .= '</Row>';
      }

//Final XML Blurb
$data .= '</Table>
 </Worksheet>
</Workbook>';
      
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=$title.xls;");
header("Content-Type: application/ms-excel");
header("Pragma: no-cache");
header("Expires: 0");

echo $data;

odbc_close($conn);
0

Featured Post

WordPress Tutorial 4: Recommended Plugins

Now that you have WordPress installed, understand the interface, and know how to install new parts, let’s take a look at our recommended plugins.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question