0

I have queries regarding the PhPExcel library for as I am stuck at a point and not getting how to move ahead. It will be really grateful if I could get help on how can I read, write, & save a .xlsm (mostly this, it has no macros but yeah many calculations) or .xlsx file by passing certain values from a web page and then retrieving the data out of it. The file is a workbook with like 3-4 excel sheets in it, all connected to one another, like the value passing from the web page goes on to sheet 1 but all the calculations & relevant values fetched to perform those calculations comes from sheet 2 and then the respective changes are made on sheet 1 again and the cells to be displayed on the web page are on sheet 1. To elaborate more it is like if I enter a value on the web page, it opens the designated excel file, reads it, writes the changes specified in it and then saves those changes. Then again it opens it, reads the required cells and displays them on the web page. The problem is it is taking a lot of time for reading the file and saving the changes passes from the website and also does not display the expected output and throw errors as shown below after the code.

`if (isset($_POST['submit'])) {
    if ($_POST['input'] == 0) { 
        echo 'The output equals to 0';
    } else {
        include 'C:\wamp64\www\website\excelvideo\PHPExcel-1.8\Classes\PHPExcel\IOFactory.php';

        $objPHPExcel = PHPExcel_IOFactory::load('C:\wamp64\www\website\excelvideo\401k.xlsx');

        $objPHPExcel->getActiveSheet()->setCellValue('C2', $_POST['input']);
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel2007");
        echo 'Data Saved';
        $objWriter->save('401k.xlsx');

        echo '<label for="output">Output is as follows:</label><br/>';

        $objPHPExcel = PHPExcel_IOFactory::load('C:\wamp64\www\website\excelvideo\401k.xlsx');
        echo $objPHPExcel->getActiveSheet()->getCell('C17')->getCalculatedValue().'<br />';`

error snippet

CSNinja
  • 7
  • 4
  • 1
    Please, just don't! This isn't what spreadsheets are for. Use a real database – Mark Baker Sep 08 '17 at 15:53
  • I know I should use any database, but this has to be done as a proof of concept for a specific task, so kindly let me know if there is any possibility. – CSNinja Sep 08 '17 at 16:08
  • For problem #1 (`it is taking a lot of time for reading the file and saving the changes`)... consider loading the file with data only set true, so that style information is ignored, that might speed things up a bit.... but without knowing real details, I can't offer any real solutions to vague problems where you talk about errors without saying what the error messages actually say – Mark Baker Sep 08 '17 at 16:24
  • 1
    Proof of concept or not, as the developer of PHPExcel, I'd strongly recommend finding a better solution than using a spreadsheet as a database – Mark Baker Sep 08 '17 at 16:25
  • Unless your proof of concept is limited to being almost completely useless, there's no way you can even begin to touch the functionality of a real database. – Brian Gottier Sep 08 '17 at 18:03
  • I don't want to and I am not using Excel as a database, basically, it is used as a normal calculator file where we are sending some inputs through a web page, fetching relevant output data and displaying it back to the web page. The issue is I don't have mathematical formulas to accomplish this task using JavaScript, as mentioned earlier. Its just I am trying to do it. Kindly consider the situation and let me know the possible solutions. I can not take the excel sheets online, it has some private data in it. – CSNinja Sep 08 '17 at 18:03
  • Moreover, I can not transfer it to some other file format or database as there are many calculations in it which all lead to an output. I have referred you Quadratic.php on Github and it helped me to halfway up to complete the task. – CSNinja Sep 08 '17 at 18:03
  • Well perhaps if you indicated what error messages you're getting (and perhaps showed any of the formulae that are causing problems), then it might be possible to help – Mark Baker Sep 08 '17 at 19:53

1 Answers1

0

The error you are receiving is generated by an xdebug setting in your php.ini. How to disable that, or increase the limit is answered here: Increasing nesting function calls limit

Using Excel as a database solution is very bad practice indeed, because, like the error you received, you will soon run into issues like "memory limit exceeded" or even worse, a corrupted Excel file when multiple processes try to write to the same file at the same time. So i'd really look into a different storage solution. MySQL comes to mind.

Erik Baars
  • 2,278
  • 1
  • 8
  • 14
  • Thank you so much for your quick response, I am still stuck at my problem, the solution you provided is now resulting in one more error- "The site can't be reached". If possible, you could more elaborate you suggested the solution. The issue with MySQL is I can not directly transfer the data to another database as it has many calculations in it with no available mathematical formula, else would have preferred JavaScript. Moreover, it is a very specific task to be done, kind of proof of concept building. My problem is not solved. – CSNinja Sep 08 '17 at 15:52