Php Excel

[pavanpuligandla]

hii all..
i'm developing an online campus portal,
the administrator has to enter student, staff records in an excel sheet, which later he has to upload the same excel to the DB via PHP after logging into my application.

how can i safeguard my excel worksheet from being tampered or updated.
i'm printing the excel sheet which admin has created in my application then, after a button click the records will b sent to DB.

if i use html forms i'll be on safe side, so tht no one knows whtz happening on the backend.

is there any possibility of creating a new worksheet in the browser?(which can be editable also, so tht therez no need to create excels separatley apart from my app.)

any suggestions guys???

[pavanpuligandla]

this is the o/p of PHP parsed excel sheet..have a look at this image
which is not editable..
i need to open a new worksheet in my application using php which can be editable..
any solution for this??
many thanks,

[pavanpuligandla]

can anyone please help me..

[Mark Baker]

is there any possibility of creating a new worksheet in the browser?(which can be editable also, so tht therez no need to create excels separatley apart from my app.)

any suggestions guys???

PHPExcel

[pavanpuligandla]

hii..
've u seen the image which i attached to this thread?
presently i'm using EXCEL SPREADSHEET READER class to read the xls file.
i want to edit this php read excel file in a browser. is it possible?

[Mark Baker]

i want to edit this php read excel file in a browser. is it possible?

Not as far as I'm aware. I don't know of any client-side (javascript) controls that would allow you to edit an Excel file directly in the browser. There may be some ActiveX or java applets that would do what you want.

Of course there is Google Docs. They have a speadsheet editor in a browser (though I've not looked at it that closely yet)

This is a PHP forum, and a PHP solution would be to read the Excel file within a PHP script and generate a form asking users to enter details in the form, then POST that request to a script which updates the Excel file on the server. It might be possible to replace the form with a javascript grid component (e.g. extjs).

[pavanpuligandla]

thnx alot,
so can i edit my excel sheets on the fly with this package(PHPEXCEL)??
my application is like, the admin has to prepare an excel sheet of student records, then he has to logon to the interface, then he can see the php parsed excel which he has entered then uploading it to the database.
suppose if he enters a wrong entry, then he has to go back to the xls file and update there so that he can see the updated values in my application.

without having this problem,, i wanna open a new excel in a web browser and there he can enter the data and upload it to the DB..

many thnaks,

[Mark Baker]

i wanna open a new excel in a web browser and there he can enter the data and upload it to the DB..

Another option might be some fancy Macro scripting within Excel itself

[pavanpuligandla]

Another option might be some fancy Macro scripting within Excel itself

i didnt get u.. can u sen me links regardin this if possible??

[dpn]

I'm guessing you need to use JavaScript for this.
This is a PHP forum so I guess I shouldnt post the code here. But if you can start a thread in the Javascript forum I'll be happy to give you the code.

[pavanpuligandla]

@dpn..

hii,
i dun think my problem is regardin java script.
i'll clear the topic from myside.,

i've an excel sheet which contain student marks and grades which are to b uploaded to MYSQL table.after entering student records, i've to logon to my application, which displays PHP parsed Excel sheet to upload the records( u can c the attached image above).
so my problem is, i'm unable to update the excel sheet which has been parsed by php which is not editable.i wanna make it editable if select any cell on the fly.then i can update records there itself and upload.

other solution is:
i wanna show a new spreadsheet (google spreadsheet) which is an editable,so that i can enter rows under fields then upload it to DB.
hope u understnad my problem..
many thanks

[dpn]

Hmmm...So basically you need to open an editable version of your existing excel file from the browser?

If thats right I'm preeeety sure you'll need a client side language like JavaScript.

[pavanpuligandla]

yes exactly..
i think u've idea about google spreadsheets. they are editable and savable.
my client will enter data in excel sheet and logs into my application then uploads it to the DB, but php parsed excel is not editable.
now i require an editable excel which can also be savable.

will u give me any idea how to do tht??
many thanks for ur reply..
pavan.p

[Mark Baker]

but php parsed excel is not editable.

What do you mean by "PHP parsed Excel"?
It's perfectly possible to open an Excel spreadsheet in PHP, modify it, and then write it back to file. I do it with Excel 2007 and with Excel 5 (Biff5 and Biff8) on a regular basis.

I'm still not entirely clear what you're trying to do here.
Do you want to store the data in an Excel spreadsheet, on on a database?
Do you want to edit the data in Excel, or via a web browser?

[pavanpuligandla]

What do you mean by "PHP parsed Excel"?

php code for reading excel sheet.
herez the code for tht;

[PHP]<?PHP
include"authn.php";
$allow_url_override = 1; // Set to 0 to not allow changed VIA POST or GET
if(!$allow_url_override || !isset($file_to_include))
{
	$file_to_include = "/wamp/www/Project/uploads/INTERNALS.xls";
}
if(!$allow_url_override || !isset($max_rows))
{
	$max_rows = 0; //USE 0 for no max
}
if(!$allow_url_override || !isset($max_cols))
{
	$max_cols = 21; //USE 0 for no max
}
if(!$allow_url_override || !isset($debug))
{
	$debug = 0;  //1 for on 0 for off
}
if(!$allow_url_override || !isset($force_nobr))
{
	$force_nobr = 1;  //Force the info in cells not to wrap unless stated explicitly (newline)
}
 
require_once 'reader.php';
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('CPa25a');
$data->read($file_to_include);
error_reporting(E_ALL ^ E_NOTICE);
//require_once'csmid1.php';
 
echo "
<br>
 
<STYLE>
.table_data
{
	border-style:ridge;
	border-width:1;
 
}
.tab_base
{
	background:#C5D0DD;
	font-weight:bold;
	font-size:14;
	font-family:Verdana;
	//border-style:ridge;
	//border-width:1;
 
	cursor:pointer;
}
.table_sub_heading
{
	background:#CCCCCC;
	font-weight:normal;
	border-style:ridge;
	border-width:1;
 
}
.table_body
{
	background:#FAF9F5;
	font-weight:normal;
	font-size:12;
 
	color:#000000;
	font-family:Times Roman;
	border-style:ridge;
	border-width:0;
	border-spacing: 0px;
	border-collapse: collapse;
}
.tab_loaded
{
	background:#E8EEFA;
	color:black;
	font-weight:bold;
	font-size:13;
	//float:center;
	padding: 0.2em 0.6em 0.2em 26px;
	background: url('http://localhost/MyDreamProject/images/liahover.gif') ;
	font-family:Times Roman;
	//border-style:groove;
	//border-width:1;
	cursor:pointer;
}
</STYLE>
";
function make_alpha_from_numbers($number)
{
	$numeric = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
	if($number<strlen($numeric))
	{
		return $numeric[$number];
	}
	else
	{
		$dev_by = floor($number/strlen($numeric));
		//return "" . make_alpha_from_numbers($dev_by-1) . make_alpha_from_numbers($number-($dev_by*strlen($numeric)));
	}
}
echo "<SCRIPT LANGUAGE='JAVASCRIPT'>
var sheet_HTML = Array();\n";
for($sheet=0;$sheet<count($data->sheets);$sheet++)
{
	$table_output[$sheet] .= "<TABLE CLASS='table_body'>
	<TR>
		<TD>&nbsp;</TD>";
	for($i=0;$i<$data->sheets[$sheet]['numCols']&&($i<=$max_cols||$max_cols==0);$i++)
	{
		$table_output[$sheet] .= "<TD CLASS='table_sub_heading' ALIGN=CENTER>" . make_alpha_from_numbers($i) . "</TD>";
	}
	for($row=1;$row<=$data->sheets[$sheet]['numRows']&&($row<=$max_rows||$max_rows==0);$row++)
	{
		$table_output[$sheet] .= "<TR><TD CLASS='table_sub_heading'>" . $row . "</TD>";
		for($col=1;$col<=$data->sheets[$sheet]['numCols']&&($col<=$max_cols||$max_cols==0);$col++)
		{
			if($data->sheets[$sheet]['cellsInfo'][$row][$col]['colspan'] >=1 && $data->sheets[$sheet]['cellsInfo'][$row][$col]['rowspan'] >=1)
			{
				$this_cell_colspan = " COLSPAN=" . $data->sheets[$sheet]['cellsInfo'][$row][$col]['colspan'];
				$this_cell_rowspan = " ROWSPAN=" . $data->sheets[$sheet]['cellsInfo'][$row][$col]['rowspan'];
				for($i=1;$i<$data->sheets[$sheet]['cellsInfo'][$row][$col]['colspan'];$i++)
				{
					$data->sheets[$sheet]['cellsInfo'][$row][$col+$i]['dontprint']=1;
				}
				for($i=1;$i<$data->sheets[$sheet]['cellsInfo'][$row][$col]['rowspan'];$i++)
				{
					for($j=0;$j<$data->sheets[$sheet]['cellsInfo'][$row][$col]['colspan'];$j++)
					{
						$data->sheets[$sheet]['cellsInfo'][$row+$i][$col+$j]['dontprint']=1;
					}
				}
			}
			else if($data->sheets[$sheet]['cellsInfo'][$row][$col]['colspan'] >=1)
			{
				$this_cell_colspan = " COLSPAN=" . $data->sheets[$sheet]['cellsInfo'][$row][$col]['colspan'];
				$this_cell_rowspan = "";
				for($i=1;$i<$data->sheets[$sheet]['cellsInfo'][$row][$col]['colspan'];$i++)
				{
					$data->sheets[$sheet]['cellsInfo'][$row][$col+$i]['dontprint']=1;
				}
			}
			else if($data->sheets[$sheet]['cellsInfo'][$row][$col]['rowspan'] >=1)
			{
				$this_cell_colspan = "";
				$this_cell_rowspan = " ROWSPAN=" . $data->sheets[$sheet]['cellsInfo'][$row][$col]['rowspan'];
				for($i=1;$i<$data->sheets[$sheet]['cellsInfo'][$row][$col]['rowspan'];$i++)
				{
					$data->sheets[$sheet]['cellsInfo'][$row+$i][$col]['dontprint']=1;
				}
			}
			else
			{
				$this_cell_colspan = "";
				$this_cell_rowspan = "";
			}
			if(!($data->sheets[$sheet]['cellsInfo'][$row][$col]['dontprint']))
			{
				$table_output[$sheet] .= "<TD CLASS='table_data' $this_cell_colspan $this_cell_rowspan>&nbsp;";
				if($force_nobr)
				{
					$table_output[$sheet] .= "<NOBR>";
				}
				$table_output[$sheet] .= nl2br(htmlentities($data->sheets[$sheet]['cells'][$row][$col]));
				if($force_nobr)
				{
					$table_output[$sheet] .= "</NOBR>";
				}
				$table_output[$sheet] .= "</TD>";
			}
		}
		$table_output[$sheet] .= "</TR>";
	}
	$table_output[$sheet] .= "</TABLE>";
	$table_output[$sheet] = str_replace("\n","",$table_output[$sheet]);
	$table_output[$sheet] = str_replace("\r","",$table_output[$sheet]);
	$table_output[$sheet] = str_replace("\t"," ",$table_output[$sheet]);
	if($debug)
	{
		$debug_output = print_r($data->sheets[$sheet],true);
		$debug_output = str_replace("\n","\\n",$debug_output);
		$debug_output = str_replace("\r","\\r",$debug_output);
		$table_output[$sheet] .= "<PRE>$debug_output</PRE>";
	}
	echo "sheet_HTML[$sheet] = \"$table_output[$sheet]\";\n";
}
echo "
function change_tabs(sheet)
{
	//alert('sheet_tab_' + sheet);
	for(i=0;i<" , count($data->sheets) , ";i++)
	{
		document.getElementById('sheet_tab_' + i).className = 'tab_base';
	}
	document.getElementById('table_loader_div').innerHTML=sheet_HTML[sheet];
	document.getElementById('sheet_tab_' + sheet).className = 'tab_loaded';
 
}
</SCRIPT>";
echo "
<TABLE CLASS='table_body' NAME='tab_table'>
<TR>";
for($sheet=0;$sheet<count($data->sheets);$sheet++)
{
	echo "<TD CLASS='tab_base' ID='sheet_tab_$sheet' ALIGN=CENTER
		ONMOUSEDOWN=\"change_tabs($sheet);\">", $data->boundsheets[$sheet]['name'] , "</TD>";
}
 
echo 
"<TR>";
echo "</TABLE>
<DIV ID=table_loader_div></DIV>
<SCRIPT LANGUAGE='JavaScript'>
change_tabs(0);
</SCRIPT>";
//echo "<IFRAME NAME=table_loader_iframe SRC='about:blank' WIDTH=100 HEIGHT=100></IFRAME>";
/*
echo "<PRE>";
print_r($data);
echo "</PRE>";
*/
?>[/PHP]

this will prints the excel cell values rite..
now we can only read this one(as shown in above attached image)
but we cannot edit the rows in that sheet.
now i want to edit the sheet in web browser itself and to save a copy of updated one to replace with original one..
r u okay now??

[pavanpuligandla]

hii.

i'm attaching here another image of a spreadsheet in web browser,
which we can change the cell values on the fly, i want to do like this..
hope u understand my problem this time

[Mark Baker]

All I can suggest is that you discard Spreadsheet_Excel_Reader() which is a read only library, and use one that does support both reading and writing; and use a javascript grid editor for editing the spreadsheet data within the browser window.

Or alternatively, simply use Google Spreadsheet

[pavanpuligandla]

All I can suggest is that you discard Spreadsheet_Excel_Reader() which is a read only library, and use one that does support both reading and writing;

can you post the code for reading and writing usign php??
or atleast tel me which package i've to use, like u suggested me to go for
phpexcel.net earlier.

[Mark Baker]

<?php

include 'PHPExcel.php';
include 'PHPExcel/IOFactory.php';

$reader = PHPExcel_IOFactory::createReader('Excel2007');
$excel = $reader->load('WorkBook.xlsx');

$lastRow = $excel->getActiveSheet()->getHighestRow();
$lastRow++;

$excel->getActiveSheet()->setCellValue('A'.$lastRow, 'Hello');
$excel->getActiveSheet()->setCellValue('B'.$lastRow, 'world!');

$writer = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
$writer->save('WorkBook.xlsx');

?>

[pavanpuligandla]

thnx alot man..

so i can open, edit and save my excel sheet in the web browser..
thnx..