php/mysql to MS excel formatted

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
ananth_ak
Forum Newbie
Posts: 23
Joined: Wed Feb 09, 2005 12:42 pm
Location: uk, london
Contact:

php/mysql to MS excel formatted

Post 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]
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

timvw's post in this thread may lead to gold: viewtopic.php?t=37631
Post Reply