Page 1 of 1

php/mysql to MS excel formatted

Posted: Tue Sep 06, 2005 4:53 am
by ananth_ak
Hello,

Using PHP i have managed to export MySql data into an excel spreedsheet, the code can be found below. the data has been exported unformatted, so the the fields in the excel spreadsheet have to be changed manually so all the data can be seen properly.

is it possible to add some formatting to my php code or on ms excel so that when i export the data i dont have to manually adjust the field lines?

is it possible to pipe in two result sets into the same excel page?

is there any software available to format data into a form structure without the use of excel?

thanks for your time

Ananth

Code: Select all

<?

$conn3 =@mysql_connect("localhost", "anandha", "anandha")
or die("Could not connect");

$rs3 = @mysql_select_db("othours", $conn3)
or die ("Could not select database");

$sql3 = "select otdetails.userid,userdetails.fullname, userdetails.grade, userdetails.department,otdetails.otdate,otdetails.starttime,otdetails.endtime,otdetails.reason,otdetails.totalhours from otdetails, userdetails where (userdetails.userid = otdetails.userid) and (otdetails.userid = '$uid') and (otdetails.otdate >= '$startdate' and  otdetails.otdate <'$enddate') order by otdetails.otdate;";

$rs3 = mysql_query($sql3, $conn3)
or die ("Could not execute query");


$fields = mysql_num_fields($rs3);

for ($i = 0; $i < $fields; $i++) {
    $header .= mysql_field_name($rs3, $i) . "\t";
}


while($row = mysql_fetch_row($rs3)) {
    $line = '';
    foreach($row as $value) {
        if ((!isset($value)) OR ($value == "")) {
            $value = "\t";
        } else {
            $value = str_replace('"', '""', $value);
            $value = '"' . $value . '"' . "\t";
        }
        $line .= $value;
    }
    $data .= trim($line)."\n";
}
$data = str_replace("\r","",$data);


if ($data == "") {
    $data = "\n(0) Records Found!\n";
}

header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=othours.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";



?>

feyd | Please use

Code: Select all

and

Code: Select all

tags where approriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]

Posted: Tue Sep 06, 2005 4:59 am
by feyd
timvw's post in this thread may lead to gold: viewtopic.php?t=37631