How do i view a spreadsheet?

Topics: User Forum
May 2, 2010 at 1:48 AM

Hello

I'm new to programming and have written a few basic php scripts. This discussion name is probably trivial to everyone but I hope someone has patience to help.

I'm wanting to view an excel spreadsheet in browser with intereaction but without using excel application. I am hoping this prog will help. Configuration is:

Xampp distribution 1.7.3 on windows

PHP v5.3.1

I can see php__gd2.dll and php_zip.dll are included in \xampp\php\ext directory which I assume means they are activated.

I cannot see php_xml anywhere and I don't know where to find and install this extension. Could someone help me with this?

 

When I run 01simple.php through browser I get:

09:52:27 Create new PHPExcel object 09:52:27 Set properties 09:52:27 Add some data 09:52:27 Rename sheet 09:52:27 Write to Excel2007 format 09:52:28 Peak memory usage: 8.25 MB 09:52:28 Done writing file.

So I assume that things are working.

If someone could help with viewing a spreadsheet that would be appreciated. Is the prob that I do not have php_xml.dll extension?

 

Many thanks

Jeff

 

Coordinator
May 2, 2010 at 10:06 AM
jeffoz1 wrote:

I can see php__gd2.dll and php_zip.dll are included in \xampp\php\ext directory which I assume means they are activated.

I cannot see php_xml anywhere and I don't know where to find and install this extension. Could someone help me with this?

The ext directory is simply a set of files that can be enabled within xampp. The php.ini file identifies those modules that are enabled. If you run a script comprising

<?php
phpinfo();
?>

then you can see a lot of information about your installation, including the location of the php.ini file. php.ini contains a set of entries like:

extension=php_gd2.dll
;extension=php_zip.dll

The ; at the beginning of a line is a comment marker, so that second line extension=php_zip.dll indicates that the php_zip extension isn't enabled, but can be enabled by uncommenting the line (deleting the ; character and restarting apache).

For a xampp 1.7.3 install, it should be enough to ensure that gd2 and zip aren't commented out, without worrying about xml which is included in the PHP build automatically.

 

jeffoz1 wrote:

When I run 01simple.php through browser I get:

09:52:27 Create new PHPExcel object 09:52:27 Set properties 09:52:27 Add some data 09:52:27 Rename sheet 09:52:27 Write to Excel2007 format 09:52:28 Peak memory usage: 8.25 MB 09:52:28 Done writing file.

So I assume that things are working.

 01simple.php creates a file on the webserver, in the same directory as the script itself, so you can find the file there. If you unzipped PHPExcel to C:\xampp\htdocs\PHPExcel, then look for 01simple.xlsx in C:\xampp\htdocs\PHPExcel\Tests

01simple-download-xlsx.php shows how the file can be sent directly to the browser rather than simply stored on the web server.

May 3, 2010 at 1:27 AM

Thank you for your response Mark.

Yes i see that the file is saved and when I use 01simple-download-xlsx.php the file is available through browser. However it still asks for excel to open it. Is there a way that I can view the content without requiring excel? if so, is it possible for there to be some ability for user data input?

Much appreciated

Cheers

Jeff

Coordinator
May 3, 2010 at 9:04 AM
jeffoz1 wrote:

Yes i see that the file is saved and when I use 01simple-download-xlsx.php the file is available through browser. However it still asks for excel to open it. Is there a way that I can view the content without requiring excel? if so, is it possible for there to be some ability for user data input?

You can use the HTML writer to write each worksheet in HTML format.

We do not provide any form of data input... but you can take data from an HTML form and insert that into cells.

May 3, 2010 at 4:21 PM

Thanks again

Is it possible for the html to be written back to the php file. What I am hoping to achieve is for the input spreadsheet to be visible in browser when the the php reader is opened, and for the changes to be seen when input data is 'submit' from html form in same php file.

Sorry if this not clear? essentially the end result is to be able to view a predefined excel spreadsheet in browser which can take user input from a form, and when submitted, for the result with calculations to be reflected in real time. This would save me lot of time in not having to hard code the spreadsheet in php from scratch and style it.

I don't know enough php yet to see from the examples how this might be done. Is it possible to get $objWriter to echo the html without saving it?

Cheers

jeff

Coordinator
May 3, 2010 at 4:33 PM
Edited May 3, 2010 at 4:41 PM
jeffoz1 wrote:

Thanks again

Is it possible for the html to be written back to the php file. What I am hoping to achieve is for the input spreadsheet to be visible in browser when the the php reader is opened, and for the changes to be seen when input data is 'submit' from html form in same php file.

Sorry if this not clear? essentially the end result is to be able to view a predefined excel spreadsheet in browser which can take user input from a form, and when submitted, for the result with calculations to be reflected in real time. This would save me lot of time in not having to hard code the spreadsheet in php from scratch and style it.

I don't know enough php yet to see from the examples how this might be done. Is it possible to get $objWriter to echo the html without saving it?

If you want an HTML page that's editable as though it were a spreadsheet, then you're better off looking at Google Docs.... Google have large, fully paid teams of developers  working on that.

What you want is possible, but not without a lot of work on your part, and a good working knowledge of HTML/CSS/javascript. The following gives some indication of how it might work, but doesn't do all you want, and isn't production quality code.

<?php

/**	Error reporting		**/
error_reporting(E_ALL);


/**	Include path		**/
set_include_path(get_include_path() . PATH_SEPARATOR . 'PHPExcel_NewCalc/Classes/');


$maxRow = 12;
$maxCol = 'F';


$maxCol++;

/**	If the user has submitted the form, then we need to execute a calculation **/
if (isset($_POST['submit'])) {
	/**	So we include PHPExcel to perform the calculations	**/
	include('PHPExcel.php');

	/**	And instantiate a new workbook in memory			**/
	$objPHPExcel = new PHPExcel();
	$objPHPExcel->setActiveSheetIndex(0);
}

/**	Set values for each cell in the grid	**/
for ($column = 'A'; $column != $maxCol; ++$column) {
	for ($row = 1; $row <= $maxRow; ++$row) {
		$cellRef = $column.$row;
		$cellValueID = $cellRef.'_hidden';
		$cellCalcID = $cellRef.'_display';
		//	Set value in the form for redisplay
		$$cellCalcID = $$cellValueID = (isset($_POST[$cellValueID])) ? html_entity_decode($_POST[$cellValueID],ENT_QUOTES,'UTF-8') : '';
		if (get_magic_quotes_gpc()) { $$cellCalcID = stripcslashes($$cellCalcID); }
		if (get_magic_quotes_gpc()) { $$cellValueID = stripcslashes($$cellValueID); }
		/**	If the user has submitted the form, we populate the workbook as well	**/
		if (isset($_POST['submit'])) {
			/**	Set value in the worksheet for calculation	**/
			if (is_numeric($$cellValueID)) { $$cellValueID = (float) $$cellValueID; }
			$objPHPExcel->getActiveSheet()->setCellValue($cellRef, $$cellValueID);
		}
	}
}

/**	Perform any calculations set in the grid	**/
if (isset($_POST['submit'])) {
	/**	Set values for each cell in the grid	**/
	for ($column = 'A'; $column != $maxCol; ++$column) {
		for ($row = 1; $row <= $maxRow; ++$row) {
			$cellRef = $column.$row;
			$cellCalcID = $cellRef.'_display';
			$$cellCalcID = $objPHPExcel->getActiveSheet()->getCell($cellRef)->getCalculatedValue();
			if (is_bool($$cellCalcID)) {
				$$cellCalcID = ($$cellCalcID) ? 'TRUE' : 'FALSE';
			}
		}
	}
}
$focusedCell = (isset($_POST['editingID'])) ? $_POST['editingID'] : 'A1';

?>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

<title>Worksheet Calculator</title>

<style type="text/css">
	.unfocusedCell	{ border-width: 1; border-style: solid; border-color: darkgrey; }
	.focusedCell	{ border-width: 1; border-style: solid; border-color: red; }
	.cellTextBox	{ border-width: 0; border-style: none; border-color: white; }
	.columnHeading	{ border-width: 1; border-style: solid; border-color: darkgrey; background-color: silver; text-align: center; font-weight: bold; }
	.rowHeading		{ border-width: 1; border-style: solid; border-color: darkgrey; background-color: silver; text-align: right; font-weight: bold; }
</style>

<script language=javascript type='text/javascript'>
	var currentCell = '';
	var currentCellValue = '';
	var currentCellCalcValue = '';
	var cellHasChanged = false;

	function clearCellFocus() {
		if (currentCell != '') {
			var previousCellName = currentCell+"_hidden";
			var previousCell = document.getElementById(previousCellName);
			previousCell.parentNode.className = 'unfocusedCell';

			currentCell = '';
			currentCellValue = '';
			currentCellCalcValue = '';

			var editingCellDest = document.getElementById("editingID");
			var editingValueDest = document.getElementById("editingValue");
			editingCellDest.value = '';
			editingValueDest.value = '';
		}
	}

	function setCellFocus(cellID) {
		var newCellName = cellID+"_hidden";
		var newCell = document.getElementById(newCellName);
		newCell.parentNode.className = 'focusedCell';

		var dataSourceName = cellID+"_hidden";
		var dataSource = document.getElementById(dataSourceName);
		var dataValueName = cellID+"_display";
		var dataValue = document.getElementById(dataValueName);

		currentCell = cellID;
		currentCellValue = dataSource.value;
		currentCellCalcValue = dataValue.value;

		var editingCellDest = document.getElementById("editingID");
		var editingValueDest = document.getElementById("editingValue");
		editingCellDest.value = cellID;
		editingValueDest.value = dataSource.value;
	}

	function displayCellFocus(cellID) {
		var displayCellName = cellID+"_display";
		var displayCell = document.getElementById(displayCellName);
		displayCell.focus();
	}

	function editCell(cellID) {
		clearCellFocus();
		setCellFocus(cellID);
	}

	function changeCellFromEditor(e) {
		var keyCode = e.keyCode;

		if (currentCell != '') {
			if ((keyCode == 16) || (keyCode == 44) || (keyCode == 17) ||		//	SHIFT, SYS REQ, CONTROL or ALT GR
				(keyCode == 20) || (keyCode == 144) || (keyCode == 145) ||		//	CAPS LOCK, NUM LOCK, SCROLL LOCK
				(keyCode == 45) || (keyCode == 46) ||							//	INSERT, DELETE
				(keyCode == 36) || (keyCode == 35) ||							//	HOME, END
				(keyCode == 33) || (keyCode == 34) ||							//	PAGE UP, PAGE DOWN
				(keyCode == 38) || (keyCode == 40) ||							//	UP ARROW, DOWN ARROW
				(keyCode == 39) || (keyCode == 37) ||							//	RIGHT ARROW, LEFT ARROW
				(keyCode == 18) || (keyCode == 19) ||							//	ALT, PAUSE/BREAK
				(keyCode == 91) || (keyCode == 92)								//	WINDOWS LEFT, WINDOWS RIGHT
//	93	SELECT
//	112 - 123	F1 - F12
				) {
				null;
			} else if ((keyCode == 9) || (keyCode == 13)) {		//	TAB or ENTER
				refreshForm();
			} else if (keyCode == 27) {							//	ESCAPE
				var editingValue = document.getElementById("editingValue");
				var cellValueName = currentCell+"_hidden";
				var cellValue = document.getElementById(cellValueName);
				cellValue.value = currentCellValue;
				var cellDisplayName = currentCell+"_display";
				var cellDisplayValue = document.getElementById(cellDisplayName);
				cellDisplayValue.value = currentCellCalcValue;
				clearCellFocus();
				cellHasChanged = false;
			} else {
				var editingValue = document.getElementById("editingValue");
				var cellValueName = currentCell+"_hidden";
				var cellValue = document.getElementById(cellValueName);
				cellValue.value = editingValue.value;
				var cellDisplayName = currentCell+"_display";
				var cellDisplayValue = document.getElementById(cellDisplayName);
				cellDisplayValue.value = editingValue.value;
				if (cellValue.value !== currentCellCalcValue) {
					cellHasChanged = true;
				}
			}
		}
	}

	function changeCellFromCell(e) {
		var keyCode = e.keyCode;

		if (currentCell != '') {
			if ((keyCode == 16) || (keyCode == 44) || (keyCode == 17) ||		//	SHIFT, SYS REQ, CONTROL or ALT GR
				(keyCode == 20) || (keyCode == 144) || (keyCode == 145) ||		//	CAPS LOCK, NUM LOCK, SCROLL LOCK
				(keyCode == 45) || (keyCode == 46) ||							//	INSERT, DELETE
				(keyCode == 36) || (keyCode == 35) ||							//	HOME, END
				(keyCode == 33) || (keyCode == 34) ||							//	PAGE UP, PAGE DOWN
				(keyCode == 38) || (keyCode == 40) ||							//	UP ARROW, DOWN ARROW
				(keyCode == 39) || (keyCode == 37) ||							//	RIGHT ARROW, LEFT ARROW
				(keyCode == 18) || (keyCode == 19) ||							//	ALT, PAUSE/BREAK
				(keyCode == 91) || (keyCode == 92)								//	WINDOWS LEFT, WINDOWS RIGHT
				) {
				null;
			} else if ((keyCode == 9) || (keyCode == 13)) {		//	TAB or ENTER
				refreshForm();
			} else if (keyCode == 27) {							//	ESCAPE
				var editingValue = document.getElementById("editingValue");
				var cellValueName = currentCell+"_hidden";
				var cellValue = document.getElementById(cellValueName);
				cellValue.value = currentCellValue;
				var cellDisplayName = currentCell+"_display";
				var cellDisplayValue = document.getElementById(cellDisplayName);
				cellDisplayValue.value = currentCellCalcValue;
				editCell(currentCell);
				cellHasChanged = false;
			} else {
				var editingValue = document.getElementById("editingValue");
				var cellDisplayName = currentCell+"_display";
				var cellDisplayValue = document.getElementById(cellDisplayName);
				editingValue.value = cellDisplayValue.value;
				var cellValueName = currentCell+"_hidden";
				var cellValue = document.getElementById(cellValueName);
				cellValue.value = cellDisplayValue.value;
				if (cellDisplayValue.value !== currentCellCalcValue) {
					cellHasChanged = true;
				}
			}
		}
	}

	function refreshForm() {
		displayCellFocus(currentCell);
		if (cellHasChanged) {
			var cellGrid = document.getElementById("cellGrid");
			cellGrid.submit.click();
		}
	}
</script>
</head>
<body>
<!--	Display the Form	-->

<form id="cellGrid" action="gridCalculator.php" method="post">
	<b>Cell ID:</b> <input id="editingID" name="editingID" type="text" size="6" readonly tabIndex="99999" />
	<b>Cell Value:</b> <input id="editingValue" name="editingValue" type="text" size="40" onKeyUp="javascript:changeCellFromEditor(event);" onBlur="javascript:refreshForm()" tabIndex="99998" />

	<table border="0" cellpadding="0" cellspacing="0">
		<tr><td class="columnHeading">&nbsp;</td>
<?php

for ($column = 'A'; $column != $maxCol; $column++) {
	echo '			<td class="columnHeading">'.$column.'</td>'.PHP_EOL;
}
echo '		</tr>'.PHP_EOL;

for ($row = 1; $row <= $maxRow; ++$row) {
	echo '		<tr><td class="rowHeading">&nbsp;'.$row.'&nbsp;</td>'.PHP_EOL;
	for ($column = 'A'; $column != $maxCol; ++$column) {
		$cellRef = $column.$row;
		$cellValueID = $cellRef.'_hidden';
		$cellCalcID = $cellRef.'_display';
		if ($cellRef == $focusedCell) {
			echo '			<td class="focusedCell">';
		} else {
			echo '			<td class="unfocusedCell">';
		}
		echo '				<input name="'.$cellValueID.'" id="'.$cellValueID.'" type="hidden" value="'.htmlentities($$cellValueID).'" />';
		echo '				<input class="cellTextBox" name="'.$cellCalcID.'" id="'.$cellCalcID.'" type="text" size="14" value="'.htmlentities($$cellCalcID).'" onKeyUp="javascript:changeCellFromCell(event);" onFocus="javascript:editCell(\''.$cellRef.'\');" />';
		echo '			</td>'.PHP_EOL;
	}
	echo '		</tr>'.PHP_EOL;
}

?>
	</table>
	<br /><input name="submit" type="submit" value="calculate">
</form>


<script language=javascript type='text/javascript'>
	setCellFocus('<?php echo (isset($_POST['editingID'])) ? $_POST['editingID'] : 'A1'; ?>');
	displayCellFocus(currentCell);
</script>

</body>
</html>

May 3, 2010 at 5:23 PM

Yes i see. Well I will certainly play with what you have sent me. I'll check google docs out and see what it can do.

thanks again

Cheers

jeff