Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 814
  • Last Modified:

PHP export to Excel: Define Excel Sheetname

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
mrcandu2
Asked:
mrcandu2
  • 3
  • 2
1 Solution
 
hiteshgupta1Commented:
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
 
mrcandu2Author Commented:
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
 
Richard QuadlingSenior Software DeverloperCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
mrcandu2Author Commented:
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
 
Richard QuadlingSenior Software DeverloperCommented:
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
 
mrcandu2Author Commented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now