How we started real MS Excel in PHP and what came of it
It’s no secret that PHP programmers often have to solve problems that are very far from everyday understanding of “web development”. The development of the language in recent years has led to the fact that PHP is increasingly considered a general-purpose language, suitable not only for sites, but also for other tasks.
I had to solve one of such “other” tasks quite recently. We decided to share the “rake card” with those who may follow the same path.
')
For a while we had enough of the features of the popular PHPExcel library. But when another demand came from the business “it is necessary for the macros to work, and it would be good to save all this in PDF,” it became clear that the chosen path was a dead end. It is necessary not to parse xlsx files, not to imitate a miscalculation, and not even use Open Office, but to learn how to interact with the “real” Microsoft Excel.
As a result of a brief survey, it was decided to create an internal micro-web service that would be able to receive data, open the specified Excel file, insert the received data into it, calculate the result and issue it as a response to the client. Interest in such a web service was expressed by several internal projects and the work began to boil.
The first to be hit was the dev-ops department. They had to prepare the server for the future service. It was unusual, because no one has any current experience with Windows ...
Windows Server 2012 R2 standart was chosen as the server platform. It should be immediately noted that out of the box, Windows is completely unsuitable for hosting PHP applications. Required to bring the system to the desired level.
To begin with, PowerShellServer was installed. This allowed us to connect to the windows-server using the usual ssh protocol without reinventing bicycles. Authorization by keys is supported, rsync works (this is important). It is a pity that the Personal Edition restricts only one simultaneous connection, but for us it is not critical.
Nginx was installed in a regular way. Taken from the page nginx.org/ru/download.html Keep in mind - under windows there are significant limitations: only one workflow that holds no more than 1024 connections. However, this again was uncritical for the internal micro-service.
PHP 7.0.9 taken from windows.php.net/download , installed in a regular way.
To simplify the restart of all this "good", a simple cmd-file was written:
The initial server setup ended with a successful page output with phpinfo (). However, this was only the very beginning ...
We inside the company use Continuous Integration. Is always. For any small project. A rough deployment plan looks like this:
What was required next? The ssh server is already installed, rsync is running correctly. Install phing:
We take Git for Windows from git-scm.com , install, check for correct operation.
We follow exactly the same scheme with the composer, only we write the bat file ourselves and it will be much simpler:
Everything seems to be ready. Run the build ... fail!
Cause 1. You need to install the php_openssl.dll extension, otherwise Phing will not be able to work with repositories via SSL. No problems delivered.
Reason 2. More serious. In our build script, we use the technique of switching the symlink to the folder with the fresh build in the last step. Like that:
The result is something like
It turned out that creating a symbolic link to NTFS is not a problem. The problem to remove it ... For some reason, the operation of deleting a symlink requires administrative rights, which ordinary PHP does not and cannot have.
The junction utility ( technet.microsoft.com/en-us/sysinternals/bb896768 ) helped us. With her, the above piece of script began to look something like this:
So, everything fell into place, the assembly earned, as it should be. It is time to write the code!
It should be noted that the actual service code did not cause any problems.
How to run Microsoft Excel and load an existing file into the application?
How to close the application after the script ends?
Get a list of all named ranges?
Set cell or range value?
Read value from cell or range?
It returns either a scalar value from a single cell or an array of values if we request a range.
Export book to pdf?
What needs to be done to make all this crazy magic work?
It's quite enchanting to watch the server: when a request arrives, Excel instantly opens, the export is started, and then all this business is instantly closed.
Good luck and do not step on the same rake!
Following the discussion in the comments, I decided to add a few important additions to the article.
1. No, the task is not to ensure that some kind of PHP library can write to Excel files or read data from them. The article is not about that. The task is to run calculations, the algorithm of which is set from the outside in the form of Excel files (and there is no way to convert the algorithm to something else), to submit data to the input, to receive answers, to generate a report. For this purpose, there is no other solution than to run the file in the native Microsoft Excel application.
2. Files - hundreds. Requests for calculations (that is, the launch of such files) - thousands per day. This leads us to the impossibility of solving "and let someone himself manually run these files." Requires complete and reliable automation.
3. See previous paragraph. Translation of algorithms into any programming language is impossible, since the costs of verification and QA will exceed the costs of the windows-server by three orders of magnitude.
4. The web service is written for use by other services, not by human clients.
5. Slightly changed the public function getValue ($ range) method to show how to read a range of cells as one unit. Added a method to get a list of all named ranges.
I had to solve one of such “other” tasks quite recently. We decided to share the “rake card” with those who may follow the same path.
Given
- Many of our partners (let's say frankly - these are big banks) like to count something in Excel. And “love” is very gently said. Complicated scoring models can be “programmed” in Excel, in a file of hundreds of sheets with dozens of macros
- Translate "programs", written in Excel into any programming language - almost unreal. It will take a lot of time, and the problem of constant updating and validation makes such a task completely unsolvable.
')
Is required
- The main information system of our company is written in PHP. It contains both web interfaces, and a lot of console services and workers.
- With these “programs” in Excel, you need to somehow interact from console applications in PHP — transfer data to them, calculate, get results
For a while we had enough of the features of the popular PHPExcel library. But when another demand came from the business “it is necessary for the macros to work, and it would be good to save all this in PDF,” it became clear that the chosen path was a dead end. It is necessary not to parse xlsx files, not to imitate a miscalculation, and not even use Open Office, but to learn how to interact with the “real” Microsoft Excel.
As a result of a brief survey, it was decided to create an internal micro-web service that would be able to receive data, open the specified Excel file, insert the received data into it, calculate the result and issue it as a response to the client. Interest in such a web service was expressed by several internal projects and the work began to boil.
Server under Windows? Why not!
The first to be hit was the dev-ops department. They had to prepare the server for the future service. It was unusual, because no one has any current experience with Windows ...
Windows Server 2012 R2 standart was chosen as the server platform. It should be immediately noted that out of the box, Windows is completely unsuitable for hosting PHP applications. Required to bring the system to the desired level.
To begin with, PowerShellServer was installed. This allowed us to connect to the windows-server using the usual ssh protocol without reinventing bicycles. Authorization by keys is supported, rsync works (this is important). It is a pity that the Personal Edition restricts only one simultaneous connection, but for us it is not critical.
Nginx was installed in a regular way. Taken from the page nginx.org/ru/download.html Keep in mind - under windows there are significant limitations: only one workflow that holds no more than 1024 connections. However, this again was uncritical for the internal micro-service.
PHP 7.0.9 taken from windows.php.net/download , installed in a regular way.
To simplify the restart of all this "good", a simple cmd-file was written:
cd C:\nginx taskkill /f /IM nginx.exe taskkill /f /IM php-cgi.exe rm C:\nginx\logs\* start nginx start -WindowStyle Hidden php\php-cgi -A "-b 0.0.0.0:9000 -c C:\server\php\php.ini" The initial server setup ended with a successful page output with phpinfo (). However, this was only the very beginning ...
Customize the assembly on Windows or How to step on all the pitfalls?
We inside the company use Continuous Integration. Is always. For any small project. A rough deployment plan looks like this:
- Teamcity server monitors changes in the necessary branches of the repository (in this particular case, the workflow was simplified to the limit and the branch was actually one - master)
- He also starts the project build when changes occur:
- The contents of the repository from the TeamCity server using rsync are delivered to the target server, to a temporary folder (this gives us the opportunity to save on TeamCity agents)
- In the same place, with the help of ssh, a build script is launched on phing, which does the main work:
- Transfer code to a permanent place.
- Installs dependencies through composer
- Lays configs
- Applies migrations and so on ...
- And, finally, it switches the symlink current (this is wwwroot), to a new folder
What was required next? The ssh server is already installed, rsync is running correctly. Install phing:
- The executable file (phing.phar) is taken from www.phing.info/trac/wiki/Users/Installation
- A neat and beautiful bat-nickname can be found at www.phing.info/trac/browser/bin/phing.bat
- Do not forget to add the path to phing in PATH to get the system-wide command
We take Git for Windows from git-scm.com , install, check for correct operation.
We follow exactly the same scheme with the composer, only we write the bat file ourselves and it will be much simpler:
@echo off if "%PHPBIN%" == "" set PHPBIN=C:\server\php\php.exe "%PHPBIN%" "C:\nginx\php\composer.phar" %* Everything seems to be ready. Run the build ... fail!
Cause 1. You need to install the php_openssl.dll extension, otherwise Phing will not be able to work with repositories via SSL. No problems delivered.
Reason 2. More serious. In our build script, we use the technique of switching the symlink to the folder with the fresh build in the last step. Like that:
<symlink target="${current.dir}" link="${home.dir}/${build.branch}/current" overwrite="true" /> The result is something like
symlink: "c:\server\domains\this.service\master\current" => "c:\server\domains\this.service\master\2016-04-01-12-34-56"
It turned out that creating a symbolic link to NTFS is not a problem. The problem to remove it ... For some reason, the operation of deleting a symlink requires administrative rights, which ordinary PHP does not and cannot have.
The junction utility ( technet.microsoft.com/en-us/sysinternals/bb896768 ) helped us. With her, the above piece of script began to look something like this:
<exec command="junction -d ${home.dir}/${build.branch}/current" checkreturn="true" passthru="true" /> <symlink target="${current.dir}" link="${home.dir}/${build.branch}/current" overwrite="true" /> So, everything fell into place, the assembly earned, as it should be. It is time to write the code!
COM Objects in PHP
It should be noted that the actual service code did not cause any problems.
How to run Microsoft Excel and load an existing file into the application?
namespace App\Components; class Excel { protected $xls; public function __construct($filename = null) { $this->xls = new \COM("Excel.Application"); // @todo: , $this->xls->Application->Visible = 1; $this->xls->DisplayAlerts = 0; if (empty($filename)) { $this->xls->Workbooks->Add(); } else { $this->xls->Workbooks->Open($filename); } $this->xls->Workbooks[1]->Activate(); } } How to close the application after the script ends?
public function __destruct() { $this->xls->Workbooks[1]->Close(false); $this->xls->Quit(); } Get a list of all named ranges?
public function getNames() { $names = $this->xls->Names; if ($names->Count == 0) { return []; } else { $ret = []; foreach ($names as $name) { $ret[$name->Name] = $name->Value; } return $ret; } } Set cell or range value?
public function setValue($range, $value) { $this->xls->Range($range)->Value = iconv('UTF-8', 'Windows-1251', $value); } Read value from cell or range?
It returns either a scalar value from a single cell or an array of values if we request a range.
public function getValue($range) { $range = $this->xls->Range($range); if ($range->Count == 1) { $val = $range->Value; return is_string($val) ? iconv('Windows-1251', 'UTF-8', $val) : $val; } else { $ret = []; foreach ($range as $cell) { $val = $cell->Value; $ret[$cell->Address] = is_string($val) ? iconv('Windows-1251', 'UTF-8', $val) : $val; } return $ret; } } Export book to pdf?
const FORMATS = [ 'PDF' => 0 ]; public function saveAs($filename, $format = self::FORMATS['PDF']) { // , MS-DOS ! $this->xls->Workbooks[1]->ExportAsFixedFormat($format, str_replace('/', '\\', $filename)); } What needs to be done to make all this crazy magic work?
php_com_dotnet.dll
Instead of conclusion
It's quite enchanting to watch the server: when a request arrives, Excel instantly opens, the export is started, and then all this business is instantly closed.
Good luck and do not step on the same rake!
Literature
PS
Following the discussion in the comments, I decided to add a few important additions to the article.
1. No, the task is not to ensure that some kind of PHP library can write to Excel files or read data from them. The article is not about that. The task is to run calculations, the algorithm of which is set from the outside in the form of Excel files (and there is no way to convert the algorithm to something else), to submit data to the input, to receive answers, to generate a report. For this purpose, there is no other solution than to run the file in the native Microsoft Excel application.
2. Files - hundreds. Requests for calculations (that is, the launch of such files) - thousands per day. This leads us to the impossibility of solving "and let someone himself manually run these files." Requires complete and reliable automation.
3. See previous paragraph. Translation of algorithms into any programming language is impossible, since the costs of verification and QA will exceed the costs of the windows-server by three orders of magnitude.
4. The web service is written for use by other services, not by human clients.
5. Slightly changed the public function getValue ($ range) method to show how to read a range of cells as one unit. Added a method to get a list of all named ranges.
Source: https://habr.com/ru/post/306408/
All Articles