SitePoint Sponsor

User Tag List

Results 1 to 20 of 20

Thread: Php Excel

  1. #1
    SitePoint Zealot pavanpuligandla's Avatar
    Join Date
    Sep 2008
    Location
    hyderabad
    Posts
    179
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Php Excel

    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???

  2. #2
    SitePoint Zealot pavanpuligandla's Avatar
    Join Date
    Sep 2008
    Location
    hyderabad
    Posts
    179
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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,
    Attached Images Attached Images
    • File Type: jpg 1.JPG (88.3 KB, 23 views)

  3. #3
    SitePoint Zealot pavanpuligandla's Avatar
    Join Date
    Sep 2008
    Location
    hyderabad
    Posts
    179
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    can anyone please help me..

  4. #4
    SitePoint Evangelist
    Join Date
    Jun 2006
    Location
    Wigan, Lancashire. UK
    Posts
    523
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by pavanpuligandla View Post
    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

  5. #5
    SitePoint Zealot pavanpuligandla's Avatar
    Join Date
    Sep 2008
    Location
    hyderabad
    Posts
    179
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  6. #6
    SitePoint Evangelist
    Join Date
    Jun 2006
    Location
    Wigan, Lancashire. UK
    Posts
    523
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by pavanpuligandla View Post
    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).
    ---
    Development Projects:
    PHPExcel
    PHPPowerPoint

  7. #7
    SitePoint Zealot pavanpuligandla's Avatar
    Join Date
    Sep 2008
    Location
    hyderabad
    Posts
    179
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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,

  8. #8
    SitePoint Evangelist
    Join Date
    Jun 2006
    Location
    Wigan, Lancashire. UK
    Posts
    523
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by pavanpuligandla View Post
    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
    ---
    Development Projects:
    PHPExcel
    PHPPowerPoint

  9. #9
    SitePoint Zealot pavanpuligandla's Avatar
    Join Date
    Sep 2008
    Location
    hyderabad
    Posts
    179
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Another option might be some fancy Macro scripting within Excel itself
    i didnt get u.. can u sen me links regardin this if possible??

  10. #10
    SitePoint Enthusiast
    Join Date
    Aug 2008
    Posts
    44
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  11. #11
    SitePoint Zealot pavanpuligandla's Avatar
    Join Date
    Sep 2008
    Location
    hyderabad
    Posts
    179
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    @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

  12. #12
    SitePoint Enthusiast
    Join Date
    Aug 2008
    Posts
    44
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  13. #13
    SitePoint Zealot pavanpuligandla's Avatar
    Join Date
    Sep 2008
    Location
    hyderabad
    Posts
    179
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  14. #14
    SitePoint Evangelist
    Join Date
    Jun 2006
    Location
    Wigan, Lancashire. UK
    Posts
    523
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by pavanpuligandla View Post
    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?
    ---
    Development Projects:
    PHPExcel
    PHPPowerPoint

  15. #15
    SitePoint Zealot pavanpuligandla's Avatar
    Join Date
    Sep 2008
    Location
    hyderabad
    Posts
    179
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What do you mean by "PHP parsed Excel"?
    php code for reading excel sheet.
    herez the code for tht;

    Code PHP:
    [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??

  16. #16
    SitePoint Zealot pavanpuligandla's Avatar
    Join Date
    Sep 2008
    Location
    hyderabad
    Posts
    179
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Attached Images Attached Images
    • File Type: jpg 1.jpg (125.5 KB, 5 views)

  17. #17
    SitePoint Evangelist
    Join Date
    Jun 2006
    Location
    Wigan, Lancashire. UK
    Posts
    523
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    ---
    Development Projects:
    PHPExcel
    PHPPowerPoint

  18. #18
    SitePoint Zealot pavanpuligandla's Avatar
    Join Date
    Sep 2008
    Location
    hyderabad
    Posts
    179
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  19. #19
    SitePoint Evangelist
    Join Date
    Jun 2006
    Location
    Wigan, Lancashire. UK
    Posts
    523
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    <?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');
    
    ?>
    ---
    Development Projects:
    PHPExcel
    PHPPowerPoint

  20. #20
    SitePoint Zealot pavanpuligandla's Avatar
    Join Date
    Sep 2008
    Location
    hyderabad
    Posts
    179
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thnx alot man..

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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •