Jump to content


Photo

Extracting All Rows, but getting only one...


  • Please log in to reply
2 replies to this topic

#1 ibanez270dx

ibanez270dx
  • Members
  • PipPipPip
  • Advanced Member
  • 53 posts

Posted 09 August 2006 - 03:52 PM

Hi,
I have a web application that stores downtime data for aircraft. The DB works very well, but I have decided to add another feature - exporting downtime logs (according to month) to Excel. For this, I used a script called "ExcelWriter" by Harish Chauhan. However, the problem I encounter is that when I try to select multiple rows from the database, the application fails. By that I mean that it only displays one row. This is because after every row, there is supposed to be a function that writes the info from the DB to an excel row. Take a look at the code: 

$therows = array("<center>$dwntime_type</center>","<center>$dwntime_date</center>","<center>$dwntime_times</center>",
"<center>$dwntime_hrs</center>","<center>$dwntime_ata</center>","<center>$dwntime_reason</center>",
"<center>$dwntime_solution</center>","<center>$dwntime_log</center>","<center>$dwntime_log_by</center>");
	}

	$excel=new ExcelWriter("$filename.xls");
	
	if($excel==false)	
		echo $excel->error;
		
	$myArr=array("<b>TITLE</b>","<b><i>$monthname, $year</i></b>");
	$excel->writeLine($myArr);

	$myArr=array("<center><b>Type</b></center>","<center><b>Date</b></center>","<center><b>Downtime</b></center>",
"<center><b>Hours</b></center>","<center><b>ATA</b></center>","<center><b>Discrepancy</b></center>",
"<center><b>Resolution</b></center>","<center><b>Logged</b></center>","<center><b>Logged By</b></center>");
	$excel->writeLine($myArr);
	
	$excel->writeLine($therows);

		
				
	$excel->open($filename.xls);

If I try to use .= , I get an error. I think that if I can extract the rows and store them as seperate values, it should work... but I don't know how to do that. Anybody know what I can do?

Thanks in advance,
- Jeff

#2 mewhocorrupts

mewhocorrupts
  • Members
  • PipPipPip
  • Advanced Member
  • 36 posts
  • LocationParker, CO

Posted 09 August 2006 - 04:45 PM

It looks like your opening the file after you write to it.  I'm not familiar with the library in question, but why is it that you instantiate the object, and then write to it.  Afterwards, you open it, and then then it's the end of the script?  Do you have more code?
-mewhocorrupts

#3 ibanez270dx

ibanez270dx
  • Members
  • PipPipPip
  • Advanced Member
  • 53 posts

Posted 09 August 2006 - 05:15 PM

yes, I do have more code... its just not relevent to it. Its kinda long, but I'll post it anyway.

export.php - I changed it around a bit since last post...
include("excelwriter.inc.php");
include("connect.php");
$sql = "SELECT * FROM downtime WHERE aircraft_id='$view_id' AND dwntime_year='$year' AND dwntime_month='$month' ORDER BY '$listorder'";
$result = @mysql_query($sql,$connection) or die(mysql_error());

while ($row = mysql_fetch_array($result)) 
	{
	 $dwntime_type = ucfirst(stripslashes($row['dwntime_type']));
	 $dwntime_hrs = round($row['dwntime_hrs'], 2);
	 $dwntime_s = $row['dwntime_start'];
	 $dwntime_e = $row['dwntime_end'];
	 $dwntime_ata = $row['dwntime_ata'];
	 $dwntime_reason = stripslashes($row['dwntime_reason']);
	 $dwntime_solution = stripslashes($row['dwntime_solution']);
	 $dwntime_log = $row['dwntime_log'];
	 $dwntime_log_by = stripslashes($row['dwntime_log_by']);
	 $dwntime_day = $row['dwntime_day'];
	 $dwntime_month = $row['dwntime_month'];
	 $dwntime_year = $row['dwntime_year'];

	 $dwntime_times = "$dwntime_s - $dwntime_e";

	 $string = "$dwntime_year - $dwntime_month - $dwntime_day";
	 $dwntime_date = str_replace(' ', '', $string);
	 
	 $therows .= array("<center>$dwntime_type</center>","<center>$dwntime_date</center>","<center>$dwntime_times</center>","<center>$dwntime_hrs</center>","<center>$dwntime_ata</center>","<center>$dwntime_reason</center>","<center>$dwntime_solution</center>","<center>$dwntime_log</center>","<center>$dwntime_log_by</center>");
	 $display .= "$excel->writeLine($therows)";
	}


	$excel=new ExcelWriter("$filename.xls");
	
	if($excel==false)	
		echo $excel->error;
		
	$myArr=array("<b>XOJET AMA</b>","<b><i>$monthname, $year</i></b>");
	$excel->writeLine($myArr);

	$myArr=array("<center><b>Type</b></center>","<center><b>Date</b></center>","<center><b>Downtime</b></center>","<center><b>Hours Down</b></center>","<center><b>ATA</b></center>","<center><b>Discrepancy</b></center>","<center><b>Resolution</b></center>","<center><b>Logged</b></center>","<center><b>Logged By</b></center>");
	$excel->writeLine($myArr);
	
	$display .= $excel->writeLine($therows);
	$display;
		
				
	$excel->open($filename.xls);
	echo "Data has been written into $filename.xls successfully. <a href=$filename.xls>Click here to view it</a><p>$therows";


?>

excelwriter.inc.php - written by Harish Chauhan - Edited a little by me
<?php

Class ExcelWriter
 {
	var $fp=null;
	var $error;
	var $state="CLOSED";
	var $newRow=false;
		
	function ExcelWriter($file="")
		{
	  	 return $this->open($file);
		}
			
////////////////////////////////////////////////////////////////////////////////
// OPEN FUNCTION
////////////////////////////////////////////////////////////////////////////////

		function open($file)
			{
			 if($this->state!="CLOSED")
				{
				 $this->error="Error : Another file is open. Please close it to save the current file.";
				 return false;
				}	
			 if(!empty($file))
				{
				 $this->fp=@fopen($file,"w+");
				} else {
				 $this->error="Usage : New ExcelWriter('fileName')";
				 return false;
				}	
			 if($this->fp==false)
				{
				 $this->error="Error: Unable to open/create File.You may not have permmsion to write the file.";
				 return false;
				}
			 $this->state="OPENED";
			 fwrite($this->fp,$this->GetHeader());
			 return $this->fp;
			}

////////////////////////////////////////////////////////////////////////////////
// CLOSE FUNCTION
////////////////////////////////////////////////////////////////////////////////		

		function close()
			{
			 if($this->state!="OPENED")
				{
			 	 $this->error="Error : Please open the file.";
			 	 return false;
				}	
			 if($this->newRow)
				{
			 	 fwrite($this->fp,"</tr>");
			 	 $this->newRow=false;
				}
			 fwrite($this->fp,$this->GetFooter());
			 fclose($this->fp);
			 $this->state="CLOSED";
			 return ;
			}
			
////////////////////////////////////////////////////////////////////////////////
// GETHEADER FUNCTION
////////////////////////////////////////////////////////////////////////////////		
	 							
		function GetHeader()
			{
			 $header = <<<EOH
				<html xmlns:o="urn:schemas-microsoft-com:office:office"	xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40">

				<head>
				 <meta http-equiv=Content-Type content="text/html; charset=us-ascii">
				 <meta name=ProgId content=Excel.Sheet>
				 <!--[if gte mso 9]>
				<xml>
				 <o:DocumentProperties>
				  <o:LastAuthor>Sriram</o:LastAuthor>
				  <o:LastSaved>2005-01-02T07:46:23Z</o:LastSaved>
				  <o:Version>10.2625</o:Version>
				 </o:DocumentProperties>
				 <o:OfficeDocumentSettings>
				  <o:DownloadComponents/>
				 </o:OfficeDocumentSettings>
				</xml>
				 <![endif]-->
				
				<style>
				<!--table
					{mso-displayed-decimal-separator:"\.";
					mso-displayed-thousand-separator:"\,";}
				@page
					{margin:1.0in .75in 1.0in .75in;
					mso-header-margin:.5in;
					mso-footer-margin:.5in;}
				tr
					{mso-height-source:auto;}
				col
					{mso-width-source:auto;}
				br
					{mso-data-placement:same-cell;}
				.style0
					{mso-number-format:General;
					text-align:general;
					vertical-align:bottom;
					white-space:nowrap;
					mso-rotate:0;
					mso-background-source:auto;
					mso-pattern:auto;
					color:windowtext;
					font-size:10.0pt;
					font-weight:400;
					font-style:normal;
					text-decoration:none;
					font-family:Arial;
					mso-generic-font-family:auto;
					mso-font-charset:0;
					border:none;
					mso-protection:locked visible;
					mso-style-name:Normal;
					mso-style-id:0;}
				td
					{mso-style-parent:style0;
					padding-top:1px;
					padding-right:1px;
					padding-left:1px;
					mso-ignore:padding;
					color:windowtext;
					font-size:10.0pt;
					font-weight:400;
					font-style:normal;
					text-decoration:none;
					font-family:Arial;
					mso-generic-font-family:auto;
					mso-font-charset:0;
					mso-number-format:General;
					text-align:general;
					vertical-align:bottom;
					border:none;
					mso-background-source:auto;
					mso-pattern:auto;
					mso-protection:locked visible;
					white-space:nowrap;
					mso-rotate:0;}
				.xl24
					{mso-style-parent:style0;
					white-space:normal;}
				-->
				</style>
				<!--[if gte mso 9]>
				<xml>
				 <x:ExcelWorkbook>
				  <x:ExcelWorksheets>
				   <x:ExcelWorksheet>
					<x:Name>1</x:Name>
					<x:WorksheetOptions>
					 <x:Selected/>
					 <x:ProtectContents>False</x:ProtectContents>
					 <x:ProtectObjects>False</x:ProtectObjects>
					 <x:ProtectScenarios>False</x:ProtectScenarios>
					</x:WorksheetOptions>
				   </x:ExcelWorksheet>
				  </x:ExcelWorksheets>
				  <x:WindowHeight>10005</x:WindowHeight>
				  <x:WindowWidth>10005</x:WindowWidth>
				  <x:WindowTopX>120</x:WindowTopX>
				  <x:WindowTopY>135</x:WindowTopY>
				  <x:ProtectStructure>False</x:ProtectStructure>
				  <x:ProtectWindows>False</x:ProtectWindows>
				 </x:ExcelWorkbook>
				</xml>
				<![endif]-->
				</head>

				<body link=blue vlink=purple>
				<table x:str border=0 cellpadding=0 cellspacing=0 style='border-collapse: collapse;table-layout:fixed;'>
EOH;
			return $header;
		}

////////////////////////////////////////////////////////////////////////////////
// GETFOOTER FUNCTION
////////////////////////////////////////////////////////////////////////////////

		function GetFooter()
			{
			 return "</table></body></html>";
			}
			
////////////////////////////////////////////////////////////////////////////////
// WRITELINE FUNCTION
////////////////////////////////////////////////////////////////////////////////
	 
		function writeLine($line_arr)
			{
			 if($this->state!="OPENED")
				{
				 $this->error="Error : Please open the file.";
				 return false;
				}	
			 if(!is_array($line_arr))
				{
				 $this->error="Error : Argument is not valid. Supply an valid Array.";
				 return false;
			 	}
			 fwrite($this->fp,"<tr>");
			 foreach($line_arr as $col)
			 fwrite($this->fp,"<td class=xl24 width=100>$col</td>");
			 fwrite($this->fp,"</tr>");
			}

////////////////////////////////////////////////////////////////////////////////
// WRITEROW FUNCTION
////////////////////////////////////////////////////////////////////////////////
		
		function writeRow()
			{
			 if($this->state!="OPENED")
				{
				 $this->error="Error : Please open the file.";
				 return false;
				}	
			 if($this->newRow==false)
				{
				 fwrite($this->fp,"<tr>");
				} else {
				 fwrite($this->fp,"</tr><tr>");
				 $this->newRow=true;	
				}
			 }

////////////////////////////////////////////////////////////////////////////////
// WRITECOL FUNCTION
////////////////////////////////////////////////////////////////////////////////

		function writeCol($value)
			{
			 if($this->state!="OPENED")
				{
				 $this->error="Error : Please open the file.";
				 return false;
				}	
			 fwrite($this->fp,"<td class=xl24 width=64 >$value</td>");
			}
		}
?>

I still don't know what is wrong with my code... I've been changing around stuff and moving stuff, etc... Please help me on this!

Thanks,
- Jeff




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users