Problem with extracting database in .xls format

[surovi99]

Hi,

I am having a problem with extracting database in .xls format from php. I give the codes below. Everything is fine with the codes except that I see a repetition of rows in the extracted .xls format. Could someone help me out?

<?php
//microsoft sql server connection here


//write data to xls file
$fp=fopen("./data.xls","wb");
$query = "SELECT DISTINCT uId,uName, address, contactNo FROM User";

$result=mssql_query($query);
$columns="<table>";
$columns.=
"<tr><td>"."User Id".</td><td>"."User Name."</td><td>".
"Address"."</td><td>"."Contact No"."</td><tr>";

fwrite($fp,$columns);
$columns="</table>";
$data="<table>";

if ($result)
{
while ($r = mssql_fetch_array($result))
{

$uId=$r["uId"];
$uName=$r["uName"];
$address=$r["address"];
$contactNo=$r["contactNo"];

$data.="<tr><td>".$uId."</td><td>".$uName."</td><td>".
$address."</td><td>".$contactNo."</td></tr>";

fwrite($fp,$data);
}

$data="</table>";
echo "<p align=center><font size=5>"."An XLS file has been created.";

}

else {

echo "No data. There maybe an error in the database.";
}

fclose($fp);

mssql_free_result($result);

?>

Many thanks in adavance...

 

[jpadie]

you're output will have all the data in it but within your while loop you are concatenating the $data var. so each loop will have all the previous loops of $data

either put the fwrite command after the loop or remove the concatenate operator "." from the $data = command.

hth
Justin

 

[surovi99]

Thanks a lot Justin. I will try it out tomorrow and let u know.

 

[jpadie]

bear in mind that what you are doing is writing html to a file rather than outputting in xls format

it might be better to output in tab delimited or comma delimited format instead?

 

[surovi99]

I know I am writing html to a file. I used comma delimited format before but my manager isnt satisfied with it. He wants the table attribute values in separate cells. So I used the concatenate operator in $data command so that assigning it to <tr><td>.cell content.</td>.... I will get the attribute values in separate cells. I plan to write the fwrite command after the while loop and let u know how it goes.

Thanks a lot...

 

[skiflyer]

But that's not going to mean much in excel is it?

Maybe I'm wrong and it will... but I think your best solution would be to make a com connection to excel and actually write the file in the .xls format.

 

[sleipnir214]

If you save an HTML file with a .xls file extension, Excel will open it -- at least it does in my tests. It apparently recognizes the HTML.


surovi99:
I'm not sure why you are concatentating the strings at all. Why not just perform the output of each table in the loop?

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[surovi99]

Could u give me some codes how to perform the output of the table in the loop?

 

[sleipnir214]

Just do the print inside the loop.

Changing this:

$data.="<tr><td>".$uId."</td><td>".$uName."</td><td>".
$address."</td><td>".$contactNo."</td></tr>";

For

fwrite ($fp, "<tr><td>".$uId."</td><td>".$uName."</td><td>".$address."</td><td>".$contactNo."</td></tr>");

Is pretty close.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[jpadie]

ok.

for readability of the source code, you might want to put a line break "\r\n" after each "<tr>" character.

it's pretty easy to output directly to excel, as well. search the php.net.

i've spent a few minutes rebuilding your code to accommodate a direct write to excel. i have not tested this but you should be able to see how it works. Note that you, of course, need excel installed on the web-server.

<?php

//microsoft sql server connection here

//write data to xls file

$query = "SELECT DISTINCT uId,uName, address, contactNo  FROM User"; 

$result=mssql_query($query) or die ("Query error: " . mssql_error());

//start an array with the column headers
$data[0] = array ("uID"=>"User ID", "User Name"=>"uName", "address"=>"Address", "contactNo"=>"Contact No.");

$i = 1;


while ($r = mssql_fetch_assoc($result)) //fetches an associative array
{
    //instead of a file write, let's build an array
    $data[$i]['uId']=$r['uId'];
    $data[$i]['uName'] = $r['uName'];
    $data[$i]['address'] = $r['address'];
    $data[$i]['contactNo']=$r['contactNo'];
    $i++ ;
}

// now open both a binary text file for writing
// and an excel com object
$textfile = "c:/output.txt";
$excelfile = "c:/exceloutput.txt";
//binary file
$bfh = fopen($textfile,"wb"); //this opens the text file

//excel file
$excel=new COM("Excel.Application");
$excel->sheetsinnewworkbook=1;
$excel->Workbooks->Add();
$book=$excel->Workbooks(1);
$sheet=$book->Worksheets(1);
$sheet->Name="Script Output";

//files are now open and ready for ouput
//start iterating through the data array

//first write the <table> structure to the text file
fwrite($bfh, "<table>");

for ($i=0, $i<count($data), $i++)
{
  extract ($data[$i]); // make the vars easier to handle
  //write the table data to the text output
  $tabledata = "<tr><td>$uId</td><td>$uName</td><td>$address</td><td>$contactNo</td></tr>";
  fwrite($bfh, $tabledata);
  
  //write the cell refs
   $cell1 = $sheets->Cells($i+1,1) ; #Select the cell (Row Column number) 
   $cell1->activate; #Activate the cell 
   $cell1->value = $uId; 
   $cell2 = $sheets->Cells($i+1,2) ; #Select the cell (Row Column number) 
   $cell2->activate; #Activate the cell 
   $cell2->value = $uName; 
   $cell3 = $sheets->Cells($i+1,3) ; #Select the cell (Row Column number) 
   $cell3->activate; #Activate the cell 
   $cell3->value = $address;  
   $cell4 = $sheets->Cells($i+1,4) ; #Select the cell (Row Column number) 
   $cell4->activate; #Activate the cell 
   $cell4->value = $contactNo; 

}

//close the table tag in the text file
fwrite ($bfh,"</table>");

//close files

  //close text file
  fclose($bfh);
  
  //close excel file
  $book->saveas($excelfile);
  $book->Close(false);
  unset($sheet);
  unset($book);
  $excel->Workbooks->Close();
  $excel->Quit();
  unset($excel);

echo "<p align=center><font size=5>"."An XLS file has been created called $excelfile and a html formatted version at $textfile"; 

} 

mssql_free_result($result); 

?>

 

[surovi99]

Thank you all very much for trying to help me out. But I must say to Justin that I am sorry I dont have the option of using COM connection in my office. And to sleipnir214, I would like to say that I tried today in the office the way u suggested, but it was printing <tr><td>... as well in the excel file but if I use the the concatenate operator in $data command, I could get the table attribute values in separate cells in the xls format. Any further suggestions are welcome. Thank you all again.

 

[surovi99]

I wrote the fwrite command after the while loop and it worked out fine. Thanks a lot Justin.