php + download to excel --> worksheet name

Question

Hi, I am currently exporting data from php to excel using the code as below:

include("dbconnect.php");

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

$count = odbc_num_fields($result);
//Define Variable For ODBC
$data = "";
//Field Name Data
for ($i = 1; $i <= $count; $i++)
    {
        $data .= odbc_field_name($result, $i)."t";
    }

$data .= "n";    
//Row Data
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);

This all 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 (unless you save the file first).

How can I define the sheetname in my php file?

Thanks in advance!

Have a nice weekend:-)

Answer 1

You're not actually creating an xls file, but a tab separated value file. Excel can read this, but it simply populates the data in the first worksheet. Because it's not a true xls file, you can't name the worksheet tabs in any way. One option would be to change your code to use a library that writes true xls files, such as PHPExcel ( http://www.phpexcel.net )... you would then be able to define a name for the worksheet tab within your script.

Answer 2

By changing the

header("Content-type: application/octet-stream");

header("Content-type: application/vnd-ms-excel");

It is downloading without any errors and i am able to save that. the worksheet name is same as excel name.