read excel file as database

osusano

hello there i dont know if this is possible.. what if i got an excell file full of informations.. and i want this file to be read as my database so that i can trasfer it to the screen? is there a way?

the other one is reverse.. i have my datbase and i create a script wherein i can choose structures i need and transfer the data into an excel file..

can both of this possible? if yes could you pls tell me how if its oks with you

stolzyboy

don't know about the first, but to do the second you will need to be on a win32 machine and use the COM object which is unavailable to linux

jsim

Take a look at www.softclub.org
This PHP class can import data from an Excel file without using COM. Therefore it will works on any PHP platform, not only on win32.

jpmoriarty

This PHP class can import data from an Excel file without using COM. Therefore it will works on any PHP platform, not only on win32.

That's not the bit that Stoltzboy said you needed win32 for though - he was talking about the second bit.

And the using excel as a database has been brought up a number of times, but i think i inadvertantly posted the response somewhere else and not on this board, so here it is again for nostalgia's sake.

http://www.asp101.com/samples/xl.asp which should lead you trhough it (and has further links that should help). It goes through using excel as an ADO etc etc. I quote though:

The option this sample illustrates is much simpler than the two listed above. Unfortunately, this simplicity is a double-edged sword. While this option is extremely easy to implement and scales gracefully, you are somewhat limited in terms of functionality. It works great for transfering basic data (from any data source you want) into an Excel sheet, but anything beyond basic formulas can become quite troublesome. But... if this option will do everything you want, it's probably the best way to go.

Also you should consider using something like access (which although not perfect would be better than excel), where you can link an excel table into it, and then use asp or php to get to the table through ODBC.

coditoergosum

Umm... that article is talking about ASP. ASP is the enemy.

As far as generating an excel document from PHP, you can do it easily by outputting a tab delimited text file, and send a header stating it's an excel document. I have a script that does this, and it works beautifully... it pull info from a MySQL db, and spits it out as tab delimited text. Here's the bulk of it (minus the db connection stuff):


if (!$retid) { echo( mysql_error()); } 
		else { 
			$content = "Date\tContact Name\tType of contact\tSDC Member\tActual Iforce Issue\tescalated\tEscalated To\tContact Type\tTime Opened\tTime Closed\tTotal Time\tAgent\tNotes\n";
			// Get results 
			while ($row = mysql_fetch_array($retid)) { 
				$date = $row["date"]; 
				$caller_name = $row["caller_name"]; 
				$caller_category = $row["caller_category"]; 
				$contact_method = $row["contact_method"]; 
				$sdc_member = $row["sdc_member"]; 
				$iforce_issue = $row["iforce_issue"]; 
				$escalated = $row["escalated"]; 
				$escalated_to = $row["escalated_to"]; 
				$time_opened = $row["time_opened"]; 
				$time_closed = $row["time_closed"]; 
				$total_time = $row["total_time"]; 
				$agent = $row["agent"]; 
				$notes = $row["notes"]; 
				$notes = preg_replace("(\r\n|\n|\r)", " ", $notes); 

			$content = $content . "$date\t$caller_name\t$contact_method\t$sdc_member\t$iforce_issue\t$escalated\t";
			$content = $content . "$escalated_to\t$caller_category\t$time_opened\t$time_closed\t$total_time\t$agent\t$notes\n";
		}
	}
header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=calls.xls");
echo("$content");

Tswitz00

I think your code will help out alot of what im trying to do. I have a flash form that gets data from a user hits a php file and emails the results to me and my boss. But now he wants to keep track of everyone who filled out the form in one excell spread sheet so I am assuming I can just use your code

$content = "Date\tContact Name\tType of contact\tSDC Member\tActual Iforce Issue\tescalated\tEscalated To\tContact Type\tTime Opened\tTime Closed\tTotal Time\tAgent\tNotes\n";
// Get results
while ($row = mysql_fetch_array($retid)) {

$date = $row["date"];

$caller_name = $row["caller_name"];

$caller_category = $row["caller_category"];

$contact_method = $row["contact_method"];

$sdc_member = $row["sdc_member"];

$iforce_issue = $row["iforce_issue"];

$escalated = $row["escalated"];

$escalated_to = $row["escalated_to"];

$time_opened = $row["time_opened"];

$time_closed = $row["time_closed"];

$total_time = $row["total_time"];

$agent = $row["agent"];

$notes = $row["notes"];

$notes = preg_replace("(\r\n|\n|\r)", " ", $notes);

            $content = $content . " $date\t$caller_name\t$contact_method\t$sdc_member

t$iforce_issue\t$escalated\t";
$content = $content . " $escalated_to\t$caller_category\t$time_opened\t$ti
me_closed\t$total_time\t$agent\t$notes\n";
}
}
header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=calls.xls");
echo("$content");

and change the variables, correct? But how would i need to modify the code to open the same excell file that was created by the first user and just keep adding on to that same file each time someone fills out the form?

plex303

this might work for you.
php_writeexcel
http://www.bettina-attack.de/jonny/view.php/projects/php_writeexcel/