Как открыть файл excel на сервере

О том, как мы на PHP запускали настоящий MS Excel и что из этого вышло

Не секрет, что зачастую PHP-программистам приходится решать задачи, весьма далёкие от бытового представления о «веб-разработке». Развитие языка в последние годы привело к тому, что PHP всё чаще считают языком общего назначения, пригодным не только для сайтов, но и для других задач.

Одну из таких «других» задач мне с коллективом пришлось решать совсем недавно. Мы решили поделиться «картой граблей» с теми, кто, возможно, пойдет по этому же пути.

• Многие наши партнёры (скажем прямо — это крупные банки) любят считать что-то в 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-файл:

Первоначальная настройка сервера закончилась успешным выводом страницы с 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-файл пишем сами и он будет значительно проще:

Вроде бы всё готово. Запускаем сборку… fail!

Причина 1. Нужно установить расширение php_openssl.dll, иначе Phing не сможет работать с репозиториями через SSL. Проблем не доставило.

Причина 2. Более серьезная. В нашем сценарии сборки используется техника переключения симлинка на папку со свежей сборкой на последнем шаге. Примерно так:

В результате получается что-то вроде
symlink: «c:serverdomainsthis.servicemastercurrent» => «c:serverdomainsthis.servicemaster2016-04-01-12-34-56»

Оказалось, что создать символическую ссылку на NTFS — не проблема. Проблема ее удалить… Отчего-то операция удаления симлинка требует прав администратора, которых у обычного PHP нет и быть не может.

Нам помогла утилита junction ( technet.microsoft.com/en-us/sysinternals/bb896768 ). С ней вышеуказанный кусок сценария стал выглядеть примерно так:

Итак, всё встало на свои места, сборка заработала, как ей и положено. Настала пора писать код!

COM-объекты в PHP

Надо отметить, что собственно код сервиса не доставил никаких проблем.

Как запустить приложение Microsoft Excel и загрузить в приложение существующий файл?

Как закрыть приложение после окончания работы скрипта?

Получить список всех именованных диапазонов?

Установить значение ячейки или диапазона?

Прочесть значение из ячейки или диапазона?
Возвращается либо скалярное значение из одной ячейки, либо массив значений, если мы запрашиваем диапазон.

Экспортировать книгу в PDF?

Что надо сделать, чтобы вся эта безумная магия заработала?
Добавить расширение php_com_dotnet.dll

Вместо заключения

Довольно феерично наблюдать за сервером: при приходе запроса мгновенно открывается Excel, запускается экспорт и потом также мгновенно всё это хозяйство закрывается.

Удачи и не наступайте на те же грабли!

Литература

По итогам обсуждения в комментариях я решил добавить к статье несколько важных дополнений.

1. Нет, задача не сводится к тому, чтобы какой-то библиотекой для PHP уметь писать в файлы формата Excel или читать данные из них. Статья не об этом. Задача — запускать расчеты, алгоритм которых задан извне в виде файлов Excel (и нет никакой возможности алгоритм преобразовать во что-то другое), подавать на вход данные, получать ответы, формировать отчет. Для этой цели нет другого решения, кроме как запустить файл в «родном» приложении Microsoft Excel.

2. Файлов — сотни. Запросов на расчеты (то есть на запуск таких файлов) — тысячи в день. Это приводит нас к невозможности решения «а пусть кто-то сам вручную запускает эти файлы». Требуется полная и надежная автоматизация.

3. См. предыдущий пункт. Перевод алгоритмов на какой-либо язык программирования невозможен, поскольку затраты на верификацию и QA превысят затраты на windows-сервер на три порядка.

4. Веб-сервис написан для использования другими сервисами, а не клиентами-людьми.

5. Немного изменил метод public function getValue($range), чтобы показать, как прочесть диапазон ячеек, как одно целое. Добавил метод получения списка всех именованных диапазонов.

Источник

Загрузка из EXCEL на сервере

Здравствуйте! Подскажите что может быть, ситуация следующая.
Есть сервер, на нем крутится 1С.
Есть база в которую идет загрузка из 1С(КС вариант).
Так вот при передачи фала EXCEL с клиента на сервер происходит сохранение данного файла в определенной папке(в контексте сервера), потом так же в контексте сервера происходит попытка открытия этого файла — но вылетает ошибка :
————————————————-
<Документ.КонкурсПоУслугам.Форма.ФормаЗагрузкиИзEXCEL.Форма(36)>: Ошибка при вызове метода контекста (Open)
Книга = Эксель.WorkBooks.Open(ИмяФайла);
по причине:
Произошла исключительная ситуация (Microsoft Excel): Приложению Microsoft Excel не удается получить доступ к файлу «G:1C_TempШахматка_08_11_2012_02_06_32.xlsx». Это может быть вызвано одной из следующих причин.

• Указан несуществующий файл или путь.
• Файл используется другой программой.
• Имя книги, которую вы пытаетесь сохранить, совпадает с именем другой книги, открытой в данный момент.
————————————————-
Код который все это делает ниже:
————————————————-
//2. Получим файлик для загрузки из временного хранилища
ИмяФайла = «G:1C_TempШахматка_»+Формат(ТекущаяДата(),»ДФ=dd_MM_yyyy_hh_mm_ss»)+».xlsx»;//Сисадмины сказали что в темпе хранит не феншуйно и выделили отдельную папку
Файлик = ПолучитьИзВременногоХранилища(АдресВовременномХранилище);
Файлик.Записать(ИмяФайла);

//3. Попробуем открыть EXCEL
Попытка Эксель = Новый COMОбъект(«Excel.Application»);
Исключение Сообщить(ОписаниеОшибки()); УдалитьФайлы(ИмяФайла); Возврат Неопределено;
КонецПопытки;
————————————————-
Перед открытием файлика, в режиме отладки, проверял — он существует(ФС.Существует()). Другой программой он не используется так как создается. у пользователя под которым выполняется код на стороне сервера разрешение на чтение/запись файла есть.
Что может быть, куда копать?

выложил не весь код.Вот весь:
//2. Получим файлик для загрузки из временного хранилища
ИмяФайла = «G:1C_TempШахматка_»+Формат(ТекущаяДата(),»ДФ=dd_MM_yyyy_hh_mm_ss»)+».xlsx»;//Сисадмины сказали что в темпе хранит не феншуйно и выделили отдельную папку
Файлик = ПолучитьИзВременногоХранилища(АдресВовременномХранилище);
Файлик.Записать(ИмяФайла);

//3. Попробуем открыть EXCEL
Попытка Эксель = Новый COMОбъект(«Excel.Application»);
Исключение Сообщить(ОписаниеОшибки()); УдалитьФайлы(ИмяФайла); Возврат Неопределено;
КонецПопытки;

//4. Откроем файлик и книгу
Книга = Эксель.WorkBooks.Open(ИмяФайла);
Лист = Книга.WorkSheets(НомерЛиста);

(1) если бы.Ставил точку останова на
Книга = Эксель.WorkBooks.Open(ИмяФайла);
находил этот файлик по ИмяФайла, открывал его, закрывал и продолжал откладку.Все равно куй.

Вызов на стороне клиента:
&НаКлиенте
Процедура Загрузить(Команда)

//1. Поместим наш файлик во временное хранилище
Перем АдресВовременномХранилище;
Если Не ЗначениеЗаполнено(Файл) Тогда Возврат; КонецЕсли;
ПоместитьФайл(АдресВовременномХранилище, Файл, , Ложь);

//2. Произведем чтение и разбор этого файла
Адрес = ПроизвестиЧтениеГоризонтальнойШахматки(АдресВовременномХранилище);

//3. Оповестим о том что все сделано
Если ЭтоАдресВременногоХранилища(Адрес) Тогда
ОповеститьОВыборе(Новый Структура(«ДанныеЗагрузки», Адрес));
Иначе
ЭтаФорма.Закрыть();
КонецЕсли;
КонецПроцедуры

Вызов на стороне сервера:
&НаСервере
Функция ПроизвестиЧтениеГоризонтальнойШахматки(АдресВовременномХранилище)

//1. Сформируем табличку в которую будем загружать
тзУслуги = Новый ТаблицаЗначений;
тзУслуги.Колонки.Добавить(«Лот» , Новый ОписаниеТипов(«СправочникСсылка.Объекты»));
тзУслуги.Колонки.Добавить(«Услуга» , Новый ОписаниеТипов(«СправочникСсылка.Объекты»));
тзУслуги.Колонки.Добавить(«Марка» , Новый ОписаниеТипов(«Строка»));
тзУслуги.Колонки.Добавить(«ЕдиницаИзмерения», Новый ОписаниеТипов(«СправочникСсылка.ЕдиницыИзмерения»));
тзУслуги.Колонки.Добавить(«Количество» , Новый ОписаниеТипов(«Число»));
тзУслуги.Колонки.Добавить(«ПДЦБезНДС» , Новый ОписаниеТипов(«Число»));
тзУслуги.Колонки.Добавить(«ПДЦ» , Новый ОписаниеТипов(«Число»));
тзУслуги.Колонки.Добавить(«Дорога» , Новый ОписаниеТипов(«СправочникСсылка.Дороги»));
тзУслуги.Колонки.Добавить(«УчастникКонкурса», Новый ОписаниеТипов(«СправочникСсылка.Организации»));
тзУслуги.Колонки.Добавить(«ФКП» , Новый ОписаниеТипов(«Число»));
тзУслуги.Колонки.Добавить(«ФКП_БезНДС» , Новый ОписаниеТипов(«Число»));

//2. Получим файлик для загрузки из временного хранилища
//ИмяФайла = «G:1C_TempШахматка_»+Формат(ТекущаяДата(),»ДФ=dd_MM_yyyy_hh_mm_ss»)+».xlsx»;//Сисадмины сказали что в темпе хранит не феншуйно и выделили отдельную папку
ИмяФайла = «G:1С ОбработкиШахматка.xlsx»;
Файлик = ПолучитьИзВременногоХранилища(АдресВовременномХранилище);
Файлик.Записать(ИмяФайла);
Файлик = «»;

//3. Попробуем открыть EXCEL
Попытка Эксель = Новый COMОбъект(«Excel.Application»);
Исключение Сообщить(ОписаниеОшибки()); УдалитьФайлы(ИмяФайла); Возврат Неопределено;
КонецПопытки;

//4. Откроем файлик и книгу
Книга = Эксель.WorkBooks.Open(ИмяФайла);
Лист = Книга.WorkSheets(НомерЛиста);

//5. Получим количество колонок и строк
ВсегоКолонок = Лист.Cells(1,1).SpecialCells(11).Column;
ВсегоСтрок = Лист.Cells(1,1).SpecialCells(11).Row;

//Далше всякая ерунда, она не нужна что бы глаз не мозолить
КонецФункции

Источник

Как прочитать файл Excel в 1С 8.3

Весьма частая задача, с которой сталкивается 1С разработчик – это чтение файлов Excel и загрузка их в 1С. Есть как минимум два способа прочитать файл Excel – средствами платформы 1С, или используя COM-объект приложения MS Excel. В статье рассматривается чтение файлов xls, xlsx при помощи табличного документа.

Содержание

Общая информация

В платформе 1С 8 существует объект ТабличныйДокумент, который позволяет формировать и выводить на экран печатные формы в табличном виде. Но у табличного документа есть еще одно применение – чтение данных из табличных файлов. Поддерживаются форматы xls, xlsx и ods. Т.е. можно загружать табличные файлы, созданные в пакете MS Office или OpenOffice Calc.

Значения типа Число или Дата, содержащиеся в ячейках исходного табличного файла, можно считать двумя способами – как значение, либо как текст. Рассмотрим пример: пусть в ячейке содержится число 123,456, и установлен формат с округлением до целого числа; в этом случае отображаться будет текст “123”, а значение будет храниться 123,456

1. Если прочитать с указанием способа чтения как “Значение”, в табличный документ 1С будет прочитано исходное значение 123,456
2. Если прочитать с указанием способа чтения как “Текст”, значение будет потеряно, и в табличный документ прочитается текст “123”

Использование объекта ВременноеХранилище

Вспомогательные процедуры — диалог выбора файла

Следующий код не является обязательным для решения задачи – путь к исходному файлу можно указать в коде, ввести вручную, скопировать из адресной строки ОС или прочитать из БД – вариантов очень много. Мы же рассмотрим пример, когда пользователь сам указывает файл на клиентском компьютере, и путь к этому файлу помещается в реквизит. В примере будем использовать реквизит формы ПутьКФайлу

Создадим обработчик события НачалоВыбора:

Для того чтобы код был универсальным, и работал в случае отказа от модальных синхронных методов, нам понадобится создать асинхронную процедуру ВыбратьФайлНаКлиенте(). В ней мы создаем диалог выбора для открытия файла, указываем фильтр с нужными форматами – xls и xlsx, а также отключаем множественный выбор файлов. Перед методом ВыбратьАсинх добавляем ключевое слово Ждать, с тем чтобы дальнейший код дождался возвращаемого значения.

Двоичные данные во временное хранилище

Логика кода будет следующая. Нам понадобятся двепроцедуры – клиентский обработчик команды, и серверный метод который и будет непосредственно считывать переданный файл.

1. проверяем, указан ли путь к файлу. Если не указан – прекращаем выполнение
2. Получаем двоичные данные из файла по указанному пути
3. Помещаем эти двоичные данные во временное хранилище
4. Далее передаем на сервер адрес этого временного хранилища

1. Создаем временный файл с нужным расширением (в примере – xlsx)
2. Получаем двоичные данные из временного хранилища
3. Записываем во временный файл
4. Определяем вариант чтения файла – читать значения табличного документа как текст, или как значения
5. Далее используем метод Прочитать, и считываем из временного файла данные в табличный документ

Метод Прочитать лучше делать в попытке, и отдельно продумать и написать код обработки исключения. для упрощения примера мы это исключение никак не обрабатываем. В случае, если передан поврежденный файл, или его невозможно прочитать по какой-то другой причине, он просто не считается в табличный документ.

Рассмотренный пример является достаточно универсальным – процедуру ПрочитатьИзВХНаСервере() можно использовать с любыми двоичными данными, помещенными во временное хранилище. Это могут быть двоичные данные, полученные с веб-сервиса, считанные из базы данных, полученные из макета, созданного в конфигураторе – код от этого не изменится.

Помещение файла на сервер в 1С

Более простой, но менее универсальный способ, предназначенный исключительно для передачи файла с клиента на сервер – это использование метода НачатьПомещениеФайлаНаСервер. В платформе предусмотрено несколько методов для помещения файлов – как одиночных, так и нескольких одновременно. Эти методы делятся на синхронные и асинхронные. Начиная с версии 8.3.18 появились методы, использующие ключевые слова Асинх и Ждать. Аналогичный метод мы рассмотрели при работе с диалогом. Далее разберем “классический” вариант с обработкой оповещения.

Логика кода следующая:

1. Создаем описание оповещения и указываем в нем процедуру, которая будет вызвана после окончания помещения файла на сервер
2. Указываем параметры диалога выбора файла и добавляем фильтр
3. Вызываем помещение файла на сервер
4. После того как файл помещен во временное хранилище, используем ту же процедуру, которую мы реализовали в первом примере – ПрочитатьИзВХНаСервере. В этом примере мы для разнообразия используем способ чтения значений как “Текст”.

Источник