Some General Questions from New php Developer

Topics: Developer Forum, User Forum
Sep 18, 2013 at 9:31 PM
Edited Sep 18, 2013 at 9:32 PM
Hello to all and thanks for the chance to learn more about using phpExcel.

I'm doing Office Automation and working mainly with Excel and AutoCAD. In getting started with php I see that reading the docs is a necessary thing and I've read loads of information today. But still seem confused on these issues:
  1. I take it that php cannot read mapped network drives. My web server is running (Xampp) on a Windows XP Pro machine. All that is running fine as is php 5.4.7. But in trying to access some Excel files which are out on some Novell servers I'm seeing that possibly cannot be done with this setup.
  2. As I read about Readers and Writers I get a little confused with the example codes provided in the docs. In my project all I need to do is open up an existing Excel file, populate a few cells with some input and then read a few calculations from other cells, then close the Excel file down. As I read some of the examples, they do not create a writer for this task. So does a writer only need to exist when saving the file?
  3. We have some Excel files which from time to time present a circular reference error but this usually only happens when the files are opened while you have other Excel files open. These files were constructed years ago by engineers and they work fine...so I cannot spend a lot of time trying to track down why this circular reference appears. Not too happy about that myself but that's my situation. So I run the following code to test the use of the files. Note that I had to move the files to the local hard drive to get this working...see above.
<?php

// standard includes
require_once './Classes/PHPExcel.php';
require_once './Classes/PHPExcel/IOFactory.php';

// read in the existing file
$objPHPExcel = PHPExcel_IOFactory::load("../ExcelFiles/MyExcelFile.xls");

// modify/insert data in worksheet cells
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('D5', '48');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('../ExcelFiles/JunkTest.xls');

?>
When I run this, I get this error message which I've cut out loads of text to comply with my NDA's at this place. Hope this is enough. Its basically complaining about that circular reference again. I can open this file in Excel and no such circular reference error happens.
Fatal error: Uncaught exception 'PHPExcel_Calculation_Exception' with message ... -> Cyclic Reference in Formula' in C:\xampp\htdocs\PHPExcel\Classes\PHPExcel\Cell.php:298 Stack trace: #0 C:\xampp\htdocs\PHPExcel\Classes\PHPExcel\Writer\Excel5\Worksheet.php(460): PHPExcel_Cell->getCalculatedValue() #1 C:\xampp\htdocs\PHPExcel\Classes\PHPExcel\Writer\Excel5.php(187): PHPExcel_Writer_Excel5_Worksheet->close() #2 C:\xampp\htdocs\PHPExcel\1TestingExcel.php(19): PHPExcel_Writer_Excel5->save('../BOMFiles/Ang...') #3 {main} thrown in C:\xampp\htdocs\PHPExcel\Classes\PHPExcel\Cell.php on line 298
Any advice would be appreciated.
Sep 19, 2013 at 12:15 AM
Edited Sep 19, 2013 at 12:23 AM
1. I take it that php cannot read mapped network drives. My web server is running (Xampp) on a Windows XP Pro machine. All that is running fine as is php 5.4.7. But in trying to access some Excel files which are out on some Novell servers I'm seeing that possibly cannot be done with this setup.

Not sure if this will help.

I test on my local machine using XAMPP and Window XP Professional.
I have XAMPP installed in C:\xampp, but I have all my php code files in E:\Projects directory.
I initially tried to do a shortcut from under the htdocs directory to the E:\Projects directory, but it would not work.
I had to create an actual folder Symbolic Link from under the htdocs to the E:\Projects directory.

Note: I had to get a program off the internet to create Symbolic Links (also known as Juncture) in Windows XP Professional.

That worked fine, and I was able to create Virtual Hosts for each project I have (via c:\xampp\apache\conf\extra\httpd-vhosts.conf file).
And access those virtual hosts by configuring the windows hosts file (via c:\windows\system32\drivers\etc\hosts file).

Anyway, I can access files just fine via webpage upload regardless of whether file is on a mapped drive or not.
If the code directly accessing files internally (not across the web), then a Symbolic Link might solve your issue.

Just an idea.

Christopher Mullins
Sep 19, 2013 at 11:33 AM
Thanks Chris, and yes after some searching yesterday I found that Windows XP cannot do symbolic links without 3rd party software. That's kind of a flaky method to do symlinks and I assume it doesn't actually create a symlink as much as it's a TSR type program which sits in memory using up resources and like all the other free stuff out there on the Internet it spies on your activities or worse. Call me paranoid, but the IT guys here are even more paranoid than I am and getting their permission to load this would be futile. For the time being I'm making a copy of the files needed on the local hard drive and will have to routinely copy the masters from the server as the Engineering people make changes to them.

The real deal breaker right now is the circular reference issue. I don't understand why Excel file run 99% of the time without any troubles even though this circular reference is present. It's not a question of certain settings that will invoke it. It appears to only happen when you have more than one Excel file open on your desktop and then try to open one of the engineering templates that it will appear. And apparently phpExcel Calculation library finds this nasty little thing and refuses to run. These Excel files were written long ago (1998, I think) and while they work great some of the complex structural calculations in them were limited by Excel only allowing so many IF/THEN nestings, so what would normally be just a single equation had to be busted up into multiple cells. The PE who wrote them is still here but he's off on new projects which means he's very unavailable to help us. I have offered to rewrite all these calcs for them in another language, one where we would not be limited by Excel's limits on nestings. But for now, they do not want to spend the time and resources.

Anyway, it's been an interesting time learning new software like php and I'll keep at it until I can add it as another one of the programming languages I'm fluent with.
Sep 19, 2013 at 12:24 PM
  1. Under which account runs the Apache Server? If it's the local system account, it cannot access network drives
  2. You do not need the writer if you do not want to write the file. And you do not need reader if you do not file read.
  3. For circular references, see Mark's response: https://phpexcel.codeplex.com/discussions/443824
Christopher : php don't know how to deal with shortcut easily but have no trouble with real pathname and to open file on C:\, D:\, etc if he has the rights