phpMyExcel - table with formulas in PHP
Good day, dear readers of Habr!
About a year ago, at night, sitting behind the wheel of my nine, moving along the winding roads of the Krasnodar Territory to my parents in the village, I thought of making a simple tool for creating reports on visitor statistics on the site, similar (though only externally) to an Excel spreadsheet.
The fact is that, by the nature of my activity, I had to display all kinds of figures from the mysql tables and monitor them in every way: how many visitors were there, how many unique types of addresses the update checked, how many people used it, all amounts, count (*) and tons .P. Of course, I placed the basic queries on a special statistics page, and I usually did all sorts of curious, but not very valuable samples in phpMyAdmin, and I was too lazy to commit them to this page. Yes, and did not want to heavily load the page statistics with all sorts of dubious need for numbers. These requests went nowhere, it upset me, and all the time I wanted to make some kind of report-making tool that remembers my last requests.
')
The road took 3 hours on a length of 250 km of Adyghe asphalt, and I had a great opportunity to think it over carefully. When I came home to my parents, I sat down at a laptop and wrote the simplest thing that looks like an Excel table, internally consisting of one mysql table.
The implementation consisted of a single table of cells containing the fields for the sheet name, the X, Y coordinates, and, in fact, the formula itself:
In the index.php file, all the cells for the current sheet were selected (the sheet was set with the GET parameter sheet = xxx in the address line) and the contents of the src field were either displayed on the screen or executed using the PHP function eval () if its first character was ".
Thus, formulas were able to use all PHP functions, including all user functions from the user_functions.php file. For example, I wrote the functions sql () and val () :
All this helps me out quite well, and I decided to write about it here, maybe it may be useful to someone else. The source is open and anyone can participate in the improvement of the script and get the password for the commit by writing me a personal message, describing in it the essence of the desired improvement.
Download the source in one zip archive using this link . After unpacking, you need to run the install.php file - it will create the table necessary for the work.
Distributed under the MIT license . Those. you can do absolutely anything.
About a year ago, at night, sitting behind the wheel of my nine, moving along the winding roads of the Krasnodar Territory to my parents in the village, I thought of making a simple tool for creating reports on visitor statistics on the site, similar (though only externally) to an Excel spreadsheet.
The fact is that, by the nature of my activity, I had to display all kinds of figures from the mysql tables and monitor them in every way: how many visitors were there, how many unique types of addresses the update checked, how many people used it, all amounts, count (*) and tons .P. Of course, I placed the basic queries on a special statistics page, and I usually did all sorts of curious, but not very valuable samples in phpMyAdmin, and I was too lazy to commit them to this page. Yes, and did not want to heavily load the page statistics with all sorts of dubious need for numbers. These requests went nowhere, it upset me, and all the time I wanted to make some kind of report-making tool that remembers my last requests.
')
The road took 3 hours on a length of 250 km of Adyghe asphalt, and I had a great opportunity to think it over carefully. When I came home to my parents, I sat down at a laptop and wrote the simplest thing that looks like an Excel table, internally consisting of one mysql table.
The implementation consisted of a single table of cells containing the fields for the sheet name, the X, Y coordinates, and, in fact, the formula itself:
In the index.php file, all the cells for the current sheet were selected (the sheet was set with the GET parameter sheet = xxx in the address line) and the contents of the src field were either displayed on the screen or executed using the PHP function eval () if its first character was ".
Thus, formulas were able to use all PHP functions, including all user functions from the user_functions.php file. For example, I wrote the functions sql () and val () :
/** sql */ function sql($sql){ return Database::selectAndFetch($sql); } /** val("A0") A0 */ function val($cell){ return MyExcel::getCellValue(get_current_sheet(), $cell); } All this helps me out quite well, and I decided to write about it here, maybe it may be useful to someone else. The source is open and anyone can participate in the improvement of the script and get the password for the commit by writing me a personal message, describing in it the essence of the desired improvement.
Download the source in one zip archive using this link . After unpacking, you need to run the install.php file - it will create the table necessary for the work.
Distributed under the MIT license . Those. you can do absolutely anything.
Source: https://habr.com/ru/post/126736/
All Articles