Одну из таких «других» задач мне с коллективом пришлось решать совсем недавно. Мы решили поделиться «картой граблей» с теми, кто, возможно, пойдет по этому же пути.
Дано
- Многие наши партнёры (скажем прямо — это крупные банки) любят считать что-то в Excel. Причем «любят» — это очень нежно сказано. Сложнейшие скоринговые модели могут быть «запрограммированы» в Excel, в файле из сотни листов с десятками макросов
- Перевести «программы», написанные в Excel на какой-либо язык программирования — практически нереально. Это займет уйму времени, а проблема постоянного обновления и проверки корректности делает такую задачу и вовсе нерешаемой
Требуется
- Основная информационная система нашей компании написана на PHP. Она содержит в себе как веб-интерфейсы, так и множество консольных сервисов и воркеров.
- С этими «программами» в Excel нужно как-то взаимодействовать из консольных приложений на PHP — передавать в них данные, обсчитывать, получать результаты
Некоторое время нам хватало возможностей популярной библиотеки PHPExcel. Но когда от бизнеса поступило очередное требование «нужно, чтобы работали макросы, и еще бы хорошо всё это сохранять в PDF», стало понятно, что выбранный путь — тупиковый. Нужно не парсить файлы xlsx, не имитировать просчёт, и даже не использовать Open Office, а научиться взаимодействовать с «настоящим» Microsoft Excel.
В результате недолгих изысканий было решено создать внутренний микро-веб-сервис, который умел бы принять данные, открыть указанный файл Excel, вставить в него принятые данные, просчитать результат и выдать его в качестве ответа клиенту. Заинтересованность в таком веб-сервисе выразили несколько внутренних проектов и работа закипела.
Сервер под Windows? А почему бы нет!
Первым под удар попал отдел dev-ops. Им предстояло подготовить сервер для будущего сервиса. Дело было необычным, поскольку актуального опыта работы с Windows ни у кого нет…
В качестве серверной платформы был выбран Windows Server 2012 R2 standart. Нужно сразу отметить, что «из коробки» Windows совершенно не приспособлена к хостингу приложений на PHP. Требовалось доведение системы до нужного уровня.
Для начала был установлен PowerShellServer. Это позволило нам подключаться к windows-серверу по привычному всем протоколу ssh, не изобретая велосипедов. Поддерживается авторизация по ключам, работает rsync (это важно). Жаль, что в Personal Edition ограничение только на одно одновременное подключение, но для нас это некритично.
Nginx был установлен штатным образом. Взят со страницы nginx.org/ru/download.html Имейте в виду — под windows есть существенные ограничения: только один рабочий процесс, который держит не более 1024 соединений. Впрочем, это опять же было некритично для внутреннего микро-сервиса.
PHP 7.0.9 взят с windows.php.net/download, установлен штатным образом.
Для упрощения перезапуска всего этого «добра» был написан несложный cmd-файл:
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"
Первоначальная настройка сервера закончилась успешным выводом страницы с phpinfo(). Однако это было еще только самое начало…
Настраиваем сборку на Windows или Как наступить на все подводные камни?
Мы внутри компании используем Continuous Integration. Всегда. Для любого, сколь угодно малого проекта. Примерный план развертывания выглядит так:
- Сервер Teamcity следит за изменениями в нужных ветках репозитория (в данном конкретном случае workflow был упрощен до предела и ветка была фактически одна — master)
- Он же запускает сборку проекта при появлении изменений:
- Содержимое репозитория с сервера TeamCity с помощью rsync доставляется на целевой сервер, во временную папку (это даёт нам возможность сэкономить на агентах TeamCity)
- Там же, с помощью ssh, запускается билд-скрипт на phing, который и делает основную работу:
- Переносит код в постоянное место
- Устанавливает зависимости через composer
- Раскладывает конфиги
- Применяет миграции и так далее...
- И, наконец, переключает симлинк current (это у нас wwwroot), на новую папку
Что потребовалось далее? ssh-сервер уже установлен, rsync выполняется корректно. Установим phing:
- Исполняемый файл (phing.phar) берем с www.phing.info/trac/wiki/Users/Installation
- Аккуратный и красивый bat-ник можно взять на www.phing.info/trac/browser/bin/phing.bat
- Не забываем добавить путь до phing в PATH, чтобы получить system-wide команду
Git for Windows берем с git-scm.com, устанавливаем, проверяем корректную работу.
Точно по такой же схеме поступаем с composer, только bat-файл пишем сами и он будет значительно проще:
@echo off
if "%PHPBIN%" == "" set PHPBIN=C:\server\php\php.exe
"%PHPBIN%" "C:\nginx\php\composer.phar" %*
Вроде бы всё готово. Запускаем сборку… fail!
Причина 1. Нужно установить расширение php_openssl.dll, иначе Phing не сможет работать с репозиториями через SSL. Проблем не доставило.
Причина 2. Более серьезная. В нашем сценарии сборки используется техника переключения симлинка на папку со свежей сборкой на последнем шаге. Примерно так:
<symlink target="${current.dir}" link="${home.dir}/${build.branch}/current" overwrite="true" />
В результате получается что-то вроде
symlink: "c:\server\domains\this.service\master\current" => "c:\server\domains\this.service\master\2016-04-01-12-34-56"
Оказалось, что создать символическую ссылку на NTFS — не проблема. Проблема ее удалить… Отчего-то операция удаления симлинка требует прав администратора, которых у обычного PHP нет и быть не может.
Нам помогла утилита junction ( technet.microsoft.com/en-us/sysinternals/bb896768 ). С ней вышеуказанный кусок сценария стал выглядеть примерно так:
<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" />
Итак, всё встало на свои места, сборка заработала, как ей и положено. Настала пора писать код!
COM-объекты в PHP
Надо отметить, что собственно код сервиса не доставил никаких проблем.
Как запустить приложение Microsoft Excel и загрузить в приложение существующий файл?
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();
}
}
Как закрыть приложение после окончания работы скрипта?
public function __destruct()
{
$this->xls->Workbooks[1]->Close(false);
$this->xls->Quit();
}
Получить список всех именованных диапазонов?
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;
}
}
Установить значение ячейки или диапазона?
public function setValue($range, $value)
{
$this->xls->Range($range)->Value = iconv('UTF-8', 'Windows-1251', $value);
}
Прочесть значение из ячейки или диапазона?
Возвращается либо скалярное значение из одной ячейки, либо массив значений, если мы запрашиваем диапазон.
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;
}
}
Экспортировать книгу в PDF?
const FORMATS = [
'PDF' => 0
];
public function saveAs($filename, $format = self::FORMATS['PDF'])
{
// Будь проклят тот день, когда разработчики MS-DOS придумали обратные слэши!
$this->xls->Workbooks[1]->ExportAsFixedFormat($format, str_replace('/', '\\', $filename));
}
Что надо сделать, чтобы вся эта безумная магия заработала?
Добавить расширение php_com_dotnet.dll
Вместо заключения
Довольно феерично наблюдать за сервером: при приходе запроса мгновенно открывается Excel, запускается экспорт и потом также мгновенно всё это хозяйство закрывается.
Удачи и не наступайте на те же грабли!
Литература
- php.net/manual/en/book.com.php
- msdn.microsoft.com/ru-ru/library/wss56bz7.aspx
- geektimes.ru/post/50878
P.S.
По итогам обсуждения в комментариях я решил добавить к статье несколько важных дополнений.
1. Нет, задача не сводится к тому, чтобы какой-то библиотекой для PHP уметь писать в файлы формата Excel или читать данные из них. Статья не об этом. Задача — запускать расчеты, алгоритм которых задан извне в виде файлов Excel (и нет никакой возможности алгоритм преобразовать во что-то другое), подавать на вход данные, получать ответы, формировать отчет. Для этой цели нет другого решения, кроме как запустить файл в «родном» приложении Microsoft Excel.
2. Файлов — сотни. Запросов на расчеты (то есть на запуск таких файлов) — тысячи в день. Это приводит нас к невозможности решения «а пусть кто-то сам вручную запускает эти файлы». Требуется полная и надежная автоматизация.
3. См. предыдущий пункт. Перевод алгоритмов на какой-либо язык программирования невозможен, поскольку затраты на верификацию и QA превысят затраты на windows-сервер на три порядка.
4. Веб-сервис написан для использования другими сервисами, а не клиентами-людьми.
5. Немного изменил метод public function getValue($range), чтобы показать, как прочесть диапазон ячеек, как одно целое. Добавил метод получения списка всех именованных диапазонов.
Комментарии (102)
mihmig
26.07.2016 16:02С конвертацией файла понятно, но Вы упомянули требование работы макросов. Приведёте пример?
AlexLeonov
26.07.2016 16:08-1Пример макроса на VBA? https://msdn.microsoft.com/ru-ru/library/office/ee814737(v=office.14).aspx, тут неплохо описано, что это такое и зачем нужно.
mihmig
26.07.2016 16:12Не, я так понял — вам нужно было обеспечить открытие Excel-файлов (с макросами), помещение в них данных, запуск макросов на обсчёт?
asmm
26.07.2016 16:36Есть более простой, но не безопасный способ работы с макросами.
1. Пишется Excel со следующим макросом:
а) считываем имя файла и берём оттуда идентификатор
б) идём по определённому URL, идентификатор из пункта (а) параметром, там 2 таблицы, одна с данными, вторая с макросом
в) данные сохраняем на лист, макрос eval'им
2. На сервере, по окончании выполнения отчёта, складываем данные и макрос для этого отчёта, сохраняем идентификатор данных.
3. Выдаём пользователю Excel-файл из п.1 в названии которого будет идентификатор из п.2
Основная проблема заключается в том, что уровни безопасности макросов необходимо снизить чуть более чем полностью.AlexLeonov
26.07.2016 16:36Задача не решена. Пользователю не нужен Excel-файл, ему нужны данные из него. Результат обсчета подставленных данных, например в JSON и красивый PDF.
Более того, пользователь — не человек :)
Это веб-сервис для других сервисов. Часть микросервисной архитектуры.asmm
26.07.2016 16:47В таком случае согласен. Если нужен Эксель как язык-интерпритатор который обсчитает данные и дальше отправит, то моё решение не подойдёт.
AlexLeonov
26.07.2016 16:54-1Именно так.
Не было бы печали, если бы наши партнеры предоставляли алгоритмы расчета. Но вместо этого они зачастую дают просто «толстый» Excel-файл и говорят «ну… у нас же всё считается!» :)
VitalKoshalew
26.07.2016 17:09-1Для начала был установлен PowerShellServer. Это позволило нам подключаться к windows-серверу по привычному всем протоколу ssh, не изобретая велосипедов. Поддерживается авторизация по ключам, работает rsync (это важно). Жаль, что в Personal Edition ограничение только на одно одновременное подключение, но для нас это некритично.
EULA
6. NON-COMMERCIAL LICENSE. If the Licensed Software product you have downloaded or otherwise obtained is marked as “PERSONAL”, “HOBBYIST”, “NON-COMMERCIAL”, or “EDUCATIONAL” the following license terms apply: Subject to the terms, conditions, and restrictions set forth in this Agreement, /N SOFTWARE hereby grants to you a limited, personal, non-exclusive, non-transferable license to Use the Licensed Software as follows: (i) personal and non-Commercial Use of the most recent version and edition of the Licensed Software specified in the License Key; (ii) non-Commercial Use of the Licensed Software in conjunction with the maximum number of Authorized User(s) specified in the product option You have purchased; and (iii) make one backup copy of the Licensed Software for archival purposes. You may not distribute Applications that use the Non-Commercial Software as a runtime component.
Стыдно должно быть: клиенты — банки, а вы $99 авторам пожалели, спиратили.AlexLeonov
26.07.2016 17:16+1Мне не стыдно.
Я для ознакомления и исследовательских целей волен ставить любой софт, какой захочу. Это вполне себе fair use. Убедившись в эффективности, можно и купить.
Откуда вы знаете, что «спиратили»? По себе людей судите?VitalKoshalew
26.07.2016 17:47+2Возможно, в вашей юрисдикции существуют какие-то законы, частично отменяющие действие EULA, но если нет, то не можете. Если бы авторы предоставили вам ознакомительную версию на 30 дней в соответствии с пунктом 5 EULA, тогда да, а просто взять Personal Edition и исследовать в рабочее время, получая при этом зарплату — не можете согласно пунктам 1 и 6.
Мне кажется, вы зря в штыки так это воспринимаете. Ну ошиблись, нарушили лицензию. Цена вопроса — $99 долларов. Не страшно пойти и заплатить таким же разработчикам, как и вы.
По себе людей судите?
Скорее, с собой сравниваю. Я вот из статьи узнал об этом продукте. Заинтересовался, первое, что сделал — прочитал условия предоставления бесплатной лицензии. Чего и всем желаю — уж IT-шники труд коллег должны ценить, к нам же это и вернётся в первую очередь.AlexLeonov
26.07.2016 17:55-3Вы не являетесь для меня лицом, осуществляющим распорядительную функцию либо правосудие. Поэтому не имеете права судить о том, нарушил ли я лично какую-либо лицензию. Это раз.
Второе. Да, в моей юрисдикции существует понятие добросовестного использования. В личных (а я лично изучал применимость утилиты для моих собственных целей), научных и учебных целях. Что и было проделано.
К моменту запуска сервиса в продакшн коммерческая лицензия будет приобретена, либо использование утилиты будет прекращено.
Считаете, что я неправ — можете подать в суд. Полные личные реквизиты могу предоставить по запросу.
leotsarev
27.07.2016 09:40-1Еще про лицензии: так использовать Excel (в серверном приложении) нельзя. Возможно, можно если у каждого клиента вашего сервиса есть лицензия на Excel, но не уверен.
Также точно будут проблемы с одновременными запросами, не закрывшимся Excel и всем этим.
Как вариант: с помощью какой-либо из тысяч библиотек открывать базовый xslx, подставлять туда нужные входные параметры и предлагать пользователю скачать результат и уже у него на машине пусть считает.AlexLeonov
27.07.2016 10:10-1Также точно будут проблемы с одновременными запросами, не закрывшимся Excel и всем этим.
Нет таких проблем. Excel корректно закрывается. Одновременные запросы работают одновременно, открывая каждый свой экземпляр приложения.
Чтобы избежать проблем с дурацким «восстановлением», файл перед открытием копируется во временную папку и открывается оттуда. При корректном завершении скрипта файл удаляется, если что-то валится — его собирает отдельный сборщик мусора.
AlexLeonov
27.07.2016 10:23-1И да. Никакого «пользователя» нет. Вы невнимательно читали. Это веб-сервис, предназначенный для использования другими сервисами.
RumataEstora
26.07.2016 17:20+5создать символическую ссылку на NTFS — не проблема. Проблема ее удалить
тоже не проблема
создание и удаление линков на каталог (важно: опция /D)
mklink /D new_dir_link c:\Users
rd new_dir_link
создание и удаление линков на файл
mklink new_file_link %windir%\system32\cmd.exe
del new_file_link
gearbox
26.07.2016 17:25Довольно феерично наблюдать за сервером: при приходе запроса мгновенно открывается Excel, запускается экспорт и потом также мгновенно всё это хозяйство закрывается.
а Вы случайно эту мгновенность в rps-ах не меряли? Возникают обоснованные (на мой взгляд) вопросы по производительности. (интересно под виндой вообще можно заставить приложение headless работать?)
AlexLeonov
26.07.2016 17:28В коде выше я привел специально один маленький и незаметный комментарий:
// @todo: выключить, если не требуется видеть работу приложения $this->xls->Application->Visible = 1;
Вот эта строчка, если в ней присвоить свойству Visible = 0, как раз и предотвращает появление приложения на рабочем столе.
Производительность, честно говоря, никакая. Запрос с расчетом простейшей формулы типа «2+2» занимает чуть ли не секунду. Но мне это некритично, к счастью.RumataEstora
26.07.2016 17:43вероятный способ ускорения: при старте веб-сервера заранее запускать эксель, запоминать его идентификатор процесса и по требованию обработать новые данные передавать этому процессу файл, данные.
gearbox
26.07.2016 19:11+3я привел специально один маленький и незаметный комментарий
Это ж тусовка программистов, а где вы видели программистов читающих комментарии ) Спасибо!
laronov
26.07.2016 17:29Сделали большую работу — молодцы!
Но для крупного заказчика все-таки стоили предложить промышленный подход — проанализировать алгоритмы в файле (раз уж заказчик не готов их сам описать) и разработать решение без костылей…AlexLeonov
26.07.2016 17:30+1Это невозможно.
1. «Крупных заказчиков» десятки
2. Файлов сотни, их список меняется несколько раз в день
3. Алгоритмы в файлах также могут меняться ежедневно
Анализ такого объема алгоритмов, а, главное, их верификация и QA обошелся бы на три порядка дороже, чем один маленький виртуальный сервер с «виндой».
gibson_dev
26.07.2016 19:48-6Наверное это уже 10 статья про PHP и Excel и каждый раз поражаюсь, нафига все это? Есть библиотека для работы с Excel -libxl называется — работает быстро, памяти не ест и совместимость отличная. Работает на всех системах от ios и далее по списку. Стоит 200$. Есть врапер для PHP — не песнь, а сказка.
Но мыши продолжают колотся…
P.S. я не со зла) Просто стиль изложения такой)AlexLeonov
26.07.2016 19:53+1Если бы вы прочитали статью, то поняли бы «нафига это всё».
Мне не нужно читать или писать из/в файлы формата Excel. Мне было нужно их запустить — в родном Microsoft Office.
AntiHelper
26.07.2016 19:52Насколько я понял — Вы создали приложение для извлечения данных из однотипных файлов, которое не хранит централизованно данные и для которого нужна отдельная инфраструктура?
Может было проще сделать ещё один макрос в экселе и добавить его в существующие файлы(раз макросы и так неизбежное зло)? Тогда сотрудники(или клиенты) не зависели бы от внутреннего сервера…
Ни в коем случае не осуждаю — каждый решает задачу самым удобным для себя инструментом. Просто на мой взгляд сотрудники были бы оперативнее без внутренних сервисов.AlexLeonov
26.07.2016 19:56-1Создано веб-приложение, которое имеет API.
По запросу это приложение открывает нужный файл (а точнее, его копию), подставляет в этот файл данные, запускает пересчет, считывает данные после пересчета, инициирует импорт книги в PDF.
В ответе клиенту в формате JSON передается массив считанных результатов просчета и ссылка на файл PDF.
Скажите, как «еще один макрос» решил бы эту задачу?
Добавлю для понимания — «клиенты» не являются людьми. Эти данные требуются другим сервисами.Exel_Wild
26.07.2016 23:38Добавляем в файл заказчика «еще один макрос», который он запускает самостоятельно. Скрипт подключается к вашему api, считывает данные, подставляет, запускает пересчет, получает результат, оправляет данные вам через api. А если вообще правильно, то можно написать своё дополнение к Excel, чтобы без скриптов.
AlexLeonov
27.07.2016 10:16Вы не в теме, к сожалению.
Нет никакого «заказчика». Никто не будет ничего запускать вручную. Я уже подробно это объяснял в комментариях.Exel_Wild
27.07.2016 10:53т.е. все эти костыли ради одного файлика экселя?
В комментариях вы пишите про заказчиков, потом говорите что их нет))
1. «Крупных заказчиков» десятки
2. Файлов сотни, их список меняется несколько раз в день
3. Алгоритмы в файлах также могут меняться ежедневноAlexLeonov
27.07.2016 11:01Кавычки говорят о том, что термин употреблен в переносном смысле. В данном случае я повторяю то, что ранее сказал собеседник.
Какие костыли? Я не понимаю вас. Штатным образом запускается приложение, совершенно штатным и документированным — идет взаимодействие с ним. Где тут костыли?
То, что сервер на windows — да, лично для меня это странно. Но каких только чудес на свете не бывает ))Exel_Wild
27.07.2016 12:23Костыли — это городить сервак с экселем и всей обвязкой ради использования макросов в файлах, вместо того, чтобы разобраться и переписать макросы на бекэнд. :)
AlexLeonov
27.07.2016 12:47Вы невнимательно читали статью. «Переписать макросы» вышло бы значительно дороже.
Exel_Wild
27.07.2016 16:43Цена — понятие субъективное. Для меня наличие в бизнес модели непонятного черного ящика и костылей, которые необходимо поддерживать было бы намного дороже.
SamDark
26.07.2016 20:28+4Проблема с одним процессом решается так: https://github.com/deemru/php-cgi-spawner
Вот тут я комплект собирал для локальной разработки: https://github.com/samdark/wnmp-dev
Кстати, у вас процесс будет отваливаться через какое-то количество запросов. Чтобы этого не происходило без spawner-а надо
PHP_FCGI_MAX_REQUESTSустановить в0.
VadimPanov
26.07.2016 20:58-1Вы с ума что-ли сошли запускать MSExcel на сервере? Юзайте это: www.aspose.com/products/cellshttp://www.aspose.com/products/cells
AlexLeonov
26.07.2016 21:00+4Перечитайте еще раз постановку задачи. Нужно не читать файлы или писать в них, нужно запускать MS Excel.
Наверное пятый раз за сегодня это объясняю ))
mrigi
26.07.2016 20:59Вы же не забыли ограничить количество одновременно выполняемых запросов до одного? Да? Excel — это как бы не то приложение, которое стоит гонять параллельно. И готовьтесь к глюкам плагинов. Обычно если нужны расчеты в Excel, то чаще для всяких проприетарных поделок вроде Bloomberg, Factset и прочего Г. Так вот они стартуют крайне медленно, переодически вешая сам Excel намертво. После перезапуска Excel автоматом стартует рекавери повторно блокирующий работу. И весь этот кизяк всплывает естественно в самое неподходящее время. Так что я вам где-то даже сочувствую с выбором пути.
AlexLeonov
26.07.2016 21:00Вполне себе нормально «гоняется параллельно». Более того, внутри одного Application спокойно открываются несколько независимых Workbooks. Правда, нам это не требовалось.
springimport
27.07.2016 18:11Я имел опыт работы с Word`ом в таком стиле и могу сказать что человек выше прав. Сейчас вы еще не столкнулись с таким поведением, но это очень реально и лучше иметь обработчики ошибок.
AlexLeonov
27.07.2016 22:13Хорошо, буду иметь в виду.
Некоторые моменты уже учли, на некоторые, видимо, придется наступить…
Exel_Wild
26.07.2016 23:05-1Можно слезть с костылей ;) Сложный метод: даже на php можно сверстать Excel файл, это архивированный xml по сути.
Думаю, самый простой способ, это сделать нужную таблицу в базе данных со всеми необходимыми расчетами, и дать заказчику к ней доступ. Заказчик в любимом Excel будет получать все необходимые данные и управлять ими через Excel Power Pivot или Excel Power Query, а, через последний можно, уже сейчас, данные забирать через веб. Но через базу будет проще и надёжнее. Конечно, всё зависит от целей, но если это Excel, то 95% это графики и отчеты. Тут удобнее Excel Power Pivot, а если корпоративное решение, это Power BI.AlexLeonov
26.07.2016 23:07+1Статья не о том.
Нет никаких «заказчиков», никто не собирается ничего править в таблицах.
Есть заранее данные файлы Excel, содержащие расчеты. Запустили — подставили данные — считали результат. Таких файлов сотни. Подставлять в них данные нужно сотни и тысячи раз в день.
Никакая «вёрстка» на PHP не справится с задачей «Запустить файл с макросами».
Пожалуйста, перечитайте статью внимательнее.Exel_Wild
26.07.2016 23:29-2Да, перечитал, статью и ваши комментарии, без которых совершенно не понятно зачем все эти грабли :) Как временное решение — супер. На постояннку, я бы рекомендовал принимать данные через нормальные источкини: базы данных от заказчиков.
AlexLeonov
26.07.2016 23:35+1Нет никаких заказчиков. Есть суровая данность.
Вы никогда не работали, скажем, со «Сбербанком»? Рассказать, как вас далеко пошлют с такими «рацпредложениями»? И как вы будете кувыркаться?
И потом, где вы нашли в задачах «принимать данные»? Нужно не данные принимать, а запускать алгоритмы, реализованные в виде Excel-файлов. Данные поступают совершенно из других источников.Exel_Wild
26.07.2016 23:47-1Весь вопрос в обоюдной заинтерисованности в безопасности и надёжности сервиса.
remzalp
27.07.2016 08:52+3Есть крупная финансовая структура, руководитель отдела которой написал сложный прогнозный алгоритм в экселе. Работает всё замечательно, человек уже ушел. Алгоритм и криворукость автора были настолько гениальны в сумме, что никто из более поздних поколений не сумел понять — как это работает. Причем работает замечательно.
Так что теперь, через 10 лет после разработки есть экселевский «черный ящик», куда нужно вбить информацию и получить достоверный прогноз. Поэтому отлично понимаю такой подход из статьи.
Даже при большом желании бизнес уже не сможет дать алгоритмы по данному конкретному случаю, а работать то надо…
woonem
26.07.2016 23:27>Настраиваем сборку на Windows или Как наступить на все подводные камни?
Наступают обычно на грабли. А об камни спотыкаютсяAlexLeonov
26.07.2016 23:28+1Вы в море ни разу не заходили на галечном пляже?
Alexufo
27.07.2016 00:48актуального опыта работы с Windows ни у кого нет
Вот бедолаги)))
По хорошему нужно было под IIS писать на шарпе сервис. Вебсервисы под винду так и заточен. Хотя и на com объектах можно.
Notimer
27.07.2016 08:52> Что потребовалось далее? ssh-сервер уже установлен
Надо полагать OpenSSH через cygwin, потому как адекватно мелкомягкие SSH к серверным осям еще не прикрутили… *facepalm*
paluke
27.07.2016 09:42-2Да вы еще и не начинали ходить по граблям. Ну например, а как вы узнаете, куда в очередной полученный файл нужно подставлять исходные данные и откуда получать результат? У вас запрограммирован искусственный интеллект, который это понимает, или вы наивно предполагаете, что оно всегда будет расположено в одном и том же месте?
Потом, использовать ole automation на сервере — очень плохая идея. Старая статья на эту тему support.microsoft.com/en-us/kb/257757
К примеру, Excel запросто может на ровном месте выдать какой-нибудь модальный диалог и ждать, пока человек не нажмет кнопочку.AlexLeonov
27.07.2016 10:14+2Ну например, а как вы узнаете, куда в очередной полученный файл нужно подставлять исходные данные и откуда получать результат?
Странный вопрос. Во-первых файлы поступают вместе с документацией. Во-вторых места, куда нужно подставлять исходные данные и откуда забирать результат, помечены именованными диапазонами. Получить их список — элементарно.
К примеру, Excel запросто может на ровном месте выдать какой-нибудь модальный диалог
Это ему делать запрещено:
$this->xls->DisplayAlerts = 0;
См. https://msdn.microsoft.com/en-us/library/office/ff839782.aspx
Afadeev
27.07.2016 10:24-1А в чем была необходимость на винде поднимать сервер для php, если опыта на винде нет? Я понимаю так поступают те, кто наоборот знают винду и не знают линукс, а вы из каких соображений?
AlexLeonov
27.07.2016 10:25+3Необходимость в том, что на Linux как-то плохо запускается Microsoft Office и не работает технология COM…
OksikOneC
27.07.2016 12:47+1Знаком, знаком с банками :) Суровая олдовщина, где могут быть не только сотни, но и тысячи вот таких «расчетных» ведомостей, с хреновой тучей листов, формулы которых вполне могут содержать линки на другие книги, в тех других книгах — еще дальше джойны. Иногда, чтобы найти конец-сорец, нужно пропутешествовать по десятку книг, лежащих на разных шарах, к некоторым из которых, у тебя даже и доступа может не быть. И когда присутствуешь на совещаниях «по автоматизации», там никогда не стоит вопроса, как это_все переложить на какие-то другие рельсы. Вопрос всегда стоит так: как задешево автоматизировать заполнение этой цепочки файлов. Задешево и забыстро. Если Вы попытаетесь там открыть рот и сказать что-то типо — не правильно все у вас (это теория, конечно), больше в этот банк вы ходить уже не будете на совещания :) Будут ходить другие ребята, которые смогут сделать ровно так, как их попросили. Это и есть суровая банковская правда, где во главу угла всегда становится тезис «Работает — не трогай». Именно он, а также категорическое нежелание вкладывать средства изменения такого вот «порядка», приводит к таким метаморфозам, которые обычные человеки, не работающие ни с банковским беком, ни с фронтом никогда не смогут осознать и им всегда будет казаться, что предлагаемые решения — костыли. Поверьте, это не костыли, а столпы банковской реальности. Из-за нее, много ит-шного автоматизаторского люда потеряла много нервов. В т.ч. и ваш покорный слуга. Задачу, которую описал автор, если бы у банковского народца было бы желание и возможности, можно было бы последовательно свести к таким шагам:
— на первом этапе поставить консолидирующую базу, которая бы для начала только агрегировала данные с екселев
— на втором этапе можно было бы заставить эту консолидированную базу посредством веб-сервисов получать/отдавать данные во внешний мир
— на третье этапе, можно было как-то бить эти ексели на логически законченные куски, и переносить формулы «как есть» в консолидирующую базу в виде готовых моделей. Самый тяжелый этап. Т.к. информация в виде сорцов есть, то можно будет сравниваться всегда с эталонными данными. После того, как какой-то расчет перенесен в консолидацию, можно настроить какой-нибудь джоб, который бы сравнивал, что получилось расчетно, а что — через первый этап. Все недочеты, конечно, оперативно исправлять. И так, шажком за шажком, за какое-то время можно все имеющиеся расчеты перенести в консолидацию. И что самое главное, можно все сверить на эталонных данных. Естественно, что все новые расчеты нужно сразу делать в консолидирующей базе. В итоге, тысячи екселевских книг должны уйти в небытие, а вместо них должны получиться отчеты в консолидирующей базе.
Под «консолидирующей» базой я понимал продукт с которым сам имел дело, под названием 1С: Консолидация или же его новой реинкарнацией 1С: Управление Холдингом. Естественно, на все_это мало кто идет, т.к. в этом случае требуются значительные затраты со стороны заказчика-банка + понимание того, а зачем при всем работающем — вот_это_все нужно. Плюсом практически у всех ит-отцов банков при одном упоминании аббревиатуры «1С» на лице читается «лолшта???» 1С в беке банка? Парень, да ты безумец! И тяжело таким отцам, практически невозможно обосновать понятие «технического долга», который заказчик уже накопил и продолжает копить. Все работает, нужно цифры в ексель вбить! Какой долг? О чем вы вообще? Еще 1С приплел? Аха-ха-ха..1С Консолидации вообще пофик с какими екселями работать, самой платформе — тоже. Таки в чем проблема? Аха-ха-ха, давай до свиданья, шутник!
Поэтому все так. Автору — крепких нервов, глубокого сна, и железобетонной веры в то, что все что он делает — светлое и чистое! Если такие кульбиты, по поддержке банковской суровой реальности делать постоянна, эта вера, по собственному опыту, тает со временем.springimport
27.07.2016 18:17Раз уж так все плохо, то со стороны MS можно было бы выпустить спец. версию Excel с API. Причем без GUI.
AlexLeonov
27.07.2016 20:00+2Она есть. И называется эта технология… пам-пам… COM-объекты!
API имеется и прекрасно документировано. Биндинги есть ко всем нормальным языкам программирования. GUI не запускается, если вы этого не пожелаете отдельно.
maxru
27.07.2016 16:18+1нужно, чтобы работали макросы, и еще бы хорошо всё это сохранять в PDF
Могу ещё добавить, что мы переехали на Win+COM, когда появилась потребность обрабатывать/генерировать большие xls/xlsx файлы.
Производительность PHPExcel просто удручает.AlexLeonov
27.07.2016 22:17Этот довод рассматривали, но подход «добавить еще памяти» всегда побеждал. Если бы не необходимость реального запуска — так бы и сидели на PHPExcel.
maxru
28.07.2016 11:45+11) Добавить памяти
2) Давайте выделенную виртуалку сделаем
3) Надоело, переезжаем на Win+COM
…
n) PROFIT
klirichek
27.07.2016 20:13Т.е. если отбросить, в общем-то, не очень важные детали (как подключиться к виндовому серверу, как удалить ссылку), всё сводится к COM.
Что ж, всё новое — хорошо забытое старое! ИМХО, COM — это ОЧЕНЬ неплохой механизм; прямо таки претендует на фундаментальную фичу!
Весь мир поделить на интерфейсы, каждому присвоить уникальный GUID, потом свести все прочие параметры/указатели к void** — и так, максимально всё абстрагировав, по GUID дальше тупо вызывать методы!
MS хороши тем, что достаточно последовательно следовали этому своему механизму, что позволяло (и позволяет) весьма легко реверс-инженерить практически любую MS-"системную" программу (Скачай отладочные символы для нужного бинаря. Найди из множества применений компонента наиболее очевидное в дизассемблере. Прокомментируй понятно смысл работы его отдельных методов — и вот, дальше по тому же GUID распутывается цепочка остальной логики программы!) Например, тот же Windows mail (aka Outlook Express) свою базу для хранения писем подключает через CoCreateInstance с guid базы. И потому если даже внешне он выглядит неприступным (подписанный .exe загрузил свою собственную подписанную .dll, что лежит прям рядом), по факту подмена единственного ключа в реестре позволяет загрузить что-то своё, и дальше рулить, следуя тем же правилам COM (подписи/приоритеты/доверие ничто. GUID — всё!). К слову, если убрать глобаьные моменты (вроде общесистемного CoCreateInstance), то более локально (спросить у произвольного поинтера, что же он из себя представляет и что он может) — как раз таки и есть вполне фундаментальная фича.AlexLeonov
27.07.2016 22:16Всё сводится к COM
Да.
COM — это ОЧЕНЬ неплохой механизм; прямо таки претендует на фундаментальную фичу!
Главное, имхо, что эта фича языко-независима. Любой C-based язык программирования легко делает биндиг объектов, методов и свойств COM.
ALexhha
28.07.2016 16:20Для упрощения перезапуска всего этого «добра» был написан несложный cmd-файл:
а смотрели в сторону Windows Service Wrapper, от автора Jenkins?
karser
02.08.2016 10:05Не уверен, насколько надежно запускать php из под windows, да еще и работать с COM объектами.
Я бы написал демона на .net, имеющий API методы, которые отражают ваши операции над XLS документами и возвращают только нужные данные.AlexLeonov
02.08.2016 10:20А в чем проблема, кроме вашей неуверенности?
karser
02.08.2016 11:12Технологии работают лучше, когда находятся на своих местах, вот преимущества:
— .net сделан для windows, огромное сообщество, у него есть typehinting для COM объектов.
— php/nginx под линукс можно установить/обновить из пакетов парой строчек.
Ну а минусы вы сами знаете, у вас вся статья состоит из костылей и ограничений.AlexLeonov
02.08.2016 11:23Ну нет у меня тайпхинтинга. И что? Я сразу лишаюсь права использовать COM-объекты?
Странное у вас представление о решении производственных задач. Это не костыли, это решение в заданных условиях.
Помните цитату про то, что надо унитазы драить, а не псалмы петь? Я предпочитаю такой подход.
clockworkbird
02.08.2016 11:04Знакомые грабли.
Несколько лет назад ходил по таким же.
ТЗ — рассчет калькуляторов часто меняется, как он работает — никто не знает, нужно не программировать аналоги, а использовать готовые XLS в продакшене с возможностью их оперативной замены.
Все от жадности. Видимо, пока проект дойдет проект до конечного исполнителя весь бюджет откусывается и остается денег только на это.
В итоге — система была построена (PHP на windows-хостинге), все автоматически открывалось, заполнялось и вычислялось. Но оказалось, что один и тот же файл в один и тот же момент времени винда умеет открывать только один раз (на тот момент, по крайней мере, было так). Для продакшена не самый приятный момент.AlexLeonov
02.08.2016 11:23Решается копированием файла с темповым случайным именем перед открытием.
clockworkbird
02.08.2016 11:58Да, конечно. Но ресурсов каждое открытие жрет немеряно. Для хайлоада не самый удобный вариант.
В любом случае, извращение.AlexLeonov
02.08.2016 12:06Хайлоад? MS Excel? Вы шутите, верно ))
clockworkbird
02.08.2016 12:19Это не я ) Постановщик ТЗ. Планировалась достаточно серьезная нагрузка на проект.
AlexLeonov
02.08.2016 12:23Хайлоад, в моем понимании, это Яндекс, Мэйл.ру, ВК, Badoo.
Вы из этого ряда?
clockworkbird
02.08.2016 12:23А по факту открытие 5 копий файлов в один и тот же момент вешало весь хостинг. По сравнению с этим PHPExcel просто пулемет. Но вся прелесть задачи была в том, что нужно было именно запускать XLS-файл, т.к. он был защищен, плюс еще и обновлялся каждую неделю.
AlexLeonov
02.08.2016 12:24Ах, хостинг…
Ну тогда мы на разных языках говорим. У нас, в рамках решения этой задачи, серверные ресурсы не ограничены.clockworkbird
02.08.2016 12:37Под хостингом имею ввиду сервер.
«Хайлоад» — имел ввиду не яндекс, конечно, но и не хоумпэйдж. А работать такое решение будет только в последнем случае.
Не помню, какая нагрузка планировалась (давно было дело), но рассчитывали делать сервис по кредитным калькуляторам с поиском лучших условий по разным банкам.
В любом, случае, я думаю, важно понимать, какие ресурсы и ограничения потребуются для такого решения.
Если под каждого клиента поднимать «на лету» сервер и инициализировать приложение, то да, работать будет. В противном случае — маловероятно.
Да и с открытием копий были тоже проблемы — не нравилось майкрософту, по моему, что у двух открытых документов были одинаковые заголовки.
А вы реально проект тестировали или пока только радуетесь тому, что технически все сошлось?
У меня тогда все работало, но в реальный продакшн это запустить не получилось — слишком много ограничений + требовательность к ресурсам.AlexLeonov
02.08.2016 13:19А вы реально проект тестировали или пока только радуетесь тому, что технически все сошлось?
Реально. И задача очень близкая вашей. Только не кредитные калькуляторы, а скоринговые модели.
не нравилось майкрософту, по моему, что у двух открытых документов были одинаковые заголовки.
Не удалось воспроизвести такой кейс.
EminH
для внутреннего микросервиса можно было готовую сборку под виндовс брать, их достаточно много (wamp, denwer). Есть даже portable варианты.
AlexLeonov
Не считаю это приемлемым по ряду причин.
Во-первых Denwer чудовищно устарел. PHP 5.3? Нет, спасибо!
Во-вторых все «сборки» тащат за собой множество ненужных компонентов. И всё равно требуют настройки.
В-третьих нам нужна идентичность версий nginx, PHP и прочего на всех серверах. Чтобы этого добиться — всё равно придется ставить заново все перечисленные компоненты. Так зачем же нужна «сборка»?
EminH
ок, ну раз вы заморочились, упакуйте вашу сборку и выложите, чтобы другие не мучались.
AlexLeonov
Сборку? У меня нет никакой сборки. И я не знаю, что такое «упаковывать», извините. Мало опыта работы под Windows, не до конца понимаю слэнг.
EminH
упаковать — автоматизировать все то что вы описали в статье (до момента «Настала пора писать код!»)
AlexLeonov
А, вот что.
Вы меня не так поняли.
Сборка — это процесс деплоя приложения в данном случае.
— TeamCity видит изменения в репозитории
— запускает шаги СБОРКИ
— первый шаг: передача файлов на целевой сервер
— второй шаг: запуск на целевом сервере сценария СБОРКИ
— который уже и делает нужные действия
Вы, видимо, под сборкой имели в виду что-то другое.
EminH
да, я имел ввиду nginx, php и ваши скрипты
AlexLeonov
Я вас понял. Нет, такую «сборку» я делать не буду. Кому интересно — то по инструкции в статье сделает сам.
springimport
А что скажите на счет OpenServer?
AlexLeonov
Нормальная «сборка». Для целей обучения. Разумеется, в продакшн выставлять такое нет смысла.
Noiwex
Есть MAMP под Windows для особо нетерпеливых.
AlexLeonov
Да, но зачем?
Почитал документацию — опять тоже самое. Фиксированные версии Apache (он вообще нам не нужен), PHP. Всё равно нужно обновлять, а под Windows это равнозначно «ставить заново».
Для обучения — может быть. Для продакшн-сервера — конечно нет.