При работе с внешними приложениями, такими как Microsoft Excel, из системы 1С, особенно когда мы имеем дело со сложными шаблонами, содержащими формулы и макросы, возникает ряд специфических задач. Одна из таких задач – это корректное управление временными файлами, которые могут создаваться в процессе выгрузки данных, особенно в режиме предварительного просмотра. Рассмотрим подробно, как мы можем решить эту проблему, используя возможности 1С и COM-объекты Excel, а также какие нюансы нам необходимо учесть для обеспечения стабильной и чистой работы системы.
Представим ситуацию: нам требуется выгрузить данные из базы 1С в предопределенный макет Excel. Этот макет не просто таблица, а полноценный документ с зашитыми формулами, которые должны пересчитываться, и, возможно, макросами, обеспечивающими дополнительную логику. Имя итогового файла не статично, оно формируется динамически на основе выгружаемых данных. Более того, пользователь должен иметь выбор: либо сразу сохранить файл в указанный каталог, либо сначала просмотреть его, а затем уже решить, сохранять ли его и куда.
Стандартный подход к работе с такими шаблонами из 1С включает использование COM-объектов. Мы создаем экземпляр приложения Excel, открываем наш макет, заполняем его данными, а затем либо показываем пользователю, либо сохраняем. Именно на этапе предварительного просмотра и возникают основные сложности с временными файлами.
// Фрагмент кода из обсуждения, демонстрирующий начальную логику
ВременныйКаталог = КаталогВременныхФайлов();
ПолноеИмяФайла = ?(ВыводимСразу, КаталогВыбранныйПользователем, ВременныйКаталог) + ИмяФайла;
Попытка
Если IsWindows() Тогда
Excel = Новый COMОбъект("Excel.Application");
КонецЕсли;
Исключение
ТекстОшибки = "Ошибка при запуске Microsoft Excel: " + ОписаниеОшибки() + Формат(ТекущаяДатаВремя(), "ДЛФ=DT");
БВ_СлужебныеПроцедуры.ВыводСообщений(ТекстОшибки);
Возврат;
КонецПопытки;
Макет = ПолучитьМакетСервер();
Макет.Записать(ПолноеИмяФайла);
Книга = Excel.Workbooks.Open(ПолноеИмяФайла);
// Далее код заполнения екселя.
// И после сохраняем или выводим на экран.
Если ПредварительныйПросмотр Тогда
Excel.Visible = True;
Иначе
Книга.SaveAs(ПолноеИмяФайла);
Excel.Quit();
КонецЕсли;
В этом коде мы видим, что если выбран режим предварительного просмотра (ПредварительныйПросмотр = Истина), файл записывается во временный каталог, открывается, и Excel становится видимым для пользователя. Однако, после закрытия Excel пользователем (или если он вообще не закрывает файл), временный файл остается лежать в системе. Наша задача – разработать эффективный механизм для работы с такими файлами, учитывая все возможные сценарии.
ТабличныйДокумент не подходит для сложных Excel файлов?Прежде чем углубляться в детали работы с COM-объектами, давайте разберем, почему в случае с Excel-шаблонами, содержащими формулы и макросы, нам не подойдет стандартный для 1С объект ТабличныйДокумент. Этот вопрос был поднят в ходе обсуждения, и автор подтвердил его актуальность.
Мы используем ТабличныйДокумент для формирования отчетов и вывода табличных данных с базовым форматированием. Он умеет читать и записывать файлы Excel, однако его возможности ограничены интерпретацией и исполнением сложной логики:
ТабличныйДокумент при чтении файла Excel может прочитать результат вычисления формулы, но не саму формулу. Более того, он не умеет пересчитывать формулы, если мы изменяем данные в ячейках. Для работы с активными, пересчитываемыми формулами нам нужен реальный движок Excel.ТабличныйДокумент полностью игнорирует макросы. Он не может их выполнить или даже сохранить. Если ваш шаблон Excel содержит макросы, которые должны быть активны после выгрузки, использование ТабличныйДокумент сделает файл неработоспособным.ТабличныйДокумент, либо поддерживается очень ограниченно.ТабличныйДокумент может работать с несколькими листами, полноценное взаимодействие между ними, включая формулы, ссылки и макросы, возможно только через COM-объекты.Таким образом, для сценария, описанного автором, когда макет Excel "зашит формулами и макросами", использование COMОбъект("Excel.Application") является единственно верным подходом, и именно на нем мы и сосредоточимся.
Рассмотрим две основные стратегии, которые мы можем применять при выгрузке данных в Excel с использованием COM-объектов, и проанализируем их особенности.
Эта стратегия является наиболее простой и чистой с точки зрения управления временными файлами, поскольку она их просто не создает. В этом случае, мы сразу записываем файл в тот каталог, который выбрал пользователь.
Преимущества:
Недостатки:
Пример кода для этой стратегии:
// Предполагаем, что ПолноеИмяФайлаДляСохранения уже получено от пользователя
// Например, через диалог ВыбратьКаталог() или ЗапросФайла()
// Инициализация Excel
Попытка
Если IsWindows() Тогда
Excel = Новый COMОбъект("Excel.Application");
Excel.DisplayAlerts = False; // Отключаем запросы Excel (например, о перезаписи файла)
КонецЕсли;
Исключение
ТекстОшибки = "Ошибка при запуске Microsoft Excel: " + ОписаниеОшибки();
Сообщить(ТекстОшибки);
Возврат;
КонецПопытки;
МакетДвоичныхДанных = ПолучитьМакетСервер(); // Получаем макет как ДвоичныеДанные
МакетДвоичныхДанных.Записать(ПолноеИмяФайлаДляСохранения); // Сохраняем макет в целевой файл
Книга = Неопределено;
Попытка
Книга = Excel.Workbooks.Open(ПолноеИмяФайлаДляСохранения);
// Здесь мы можем отключить автоматический пересчет формул на время заполнения
// для повышения производительности, если нужно.
// Книга.Application.Calculation = -4135; // xlCalculationManual (ручной пересчет)
// *** ДАЛЕЕ КОД ЗАПОЛНЕНИЯ EXCEL ДАННЫМИ ИЗ 1С ***
// Например:
// Лист = Книга.Sheets(1);
// Лист.Cells(1, 1).Value = "Данные из 1С";
// ...
// Если был отключен автоматический пересчет, включаем его или выполняем принудительный
// Книга.Application.Calculation = -4105; // xlCalculationAutomatic (автоматический пересчет)
// Книга.Calculate(); // Принудительный пересчет всей книги
Если ПредварительныйПросмотр Тогда
Excel.Visible = True;
// В этом режиме пользователь сам управляет сохранением и закрытием файла/Excel.
// Мы не вызываем SaveAs и Quit, чтобы не мешать пользователю.
// Рекомендуется оставить объекты COM активными и не очищать их сразу.
// Если пользователь не закроет Excel, процесс может остаться висеть.
// Обязательно предупреждаем пользователя о необходимости закрыть Excel.
Сообщить("Файл Excel открыт для просмотра. Пожалуйста, закройте Excel по завершении работы.");
Иначе
// Сохраняем файл и закрываем Excel
// Если файл уже был открыт и изменен пользователем, SaveAs может вызвать запрос
Книга.SaveAs(ПолноеИмяФайлаДляСохранения);
Excel.Quit();
КонецЕсли;
Исключение
ТекстОшибки = "Ошибка при работе с Excel: " + ОписаниеОшибки();
Сообщить(ТекстОшибки);
// Если произошла ошибка, попытаемся закрыть Excel, если он невидимый
Если Не Excel = Неопределено И Excel.Visible = False Тогда
Excel.Quit();
КонецЕсли;
Наконец
// Освобождаем COM-объекты только если Excel был закрыт или невидимым
Если Не Excel = Неопределено И Excel.Visible = False Тогда
Книга = Неопределено;
Excel = Неопределено;
КонецЕсли;
КонецПопытки;
Эта стратегия необходима, когда мы хотим показать файл пользователю для предварительного просмотра, но не хотим, чтобы он был сохранен в каком-либо "постоянном" месте до тех пор, пока пользователь не примет решение о сохранении. В этом случае мы создаем файл во временном каталоге системы.
Преимущества:
Недостатки:
Разберем эту стратегию по шагам.
Для создания временного файла мы можем использовать функцию КаталогВременныхФайлов() для получения пути к системному временному каталогу. Однако, гораздо предпочтительнее использовать функцию ПолучитьИмяВременногоФайла(). Эта функция не просто возвращает путь к временному каталогу, но и генерирует уникальное имя файла в нем, что минимизирует риск конфликтов имен и упрощает последующую очистку.
Мы записываем наш макет в этот сгенерированный временный файл.
// Получаем уникальное имя временного файла
ПолноеИмяВременногоФайла = ПолучитьИмяВременногоФайла("xls"); // Расширение без точки, может быть "xlsx", "xlsm"
// Записываем макет в этот временный файл
МакетДвоичныхДанных = ПолучитьМакетСервер();
МакетДвоичныхДанных.Записать(ПолноеИмяВременногоФайла);
После записи макета во временный файл мы открываем его с помощью COM-объекта Excel и делаем приложение видимым для пользователя.
Книга = Excel.Workbooks.Open(ПолноеИмяВременногоФайла);
Excel.Visible = True;
В этот момент пользователь видит файл. Он может его просмотреть, отредактировать, а затем закрыть или сохранить. Важно понимать: пока файл открыт пользователем в Excel, мы не можем его удалить программно. Это критический момент для дальнейшей логики удаления.
Это самый сложный этап. Мы должны обеспечить удаление временного файла, но только если он не используется. Если файл открыт, попытка удаления приведет к ошибке.
Существуют несколько подходов к удалению временных файлов:
Если мы не используем предварительный просмотр (то есть Excel.Visible = False или мы принудительно закрываем Excel через Excel.Quit()), то после сохранения или завершения работы с файлом мы можем сразу же попытаться удалить временный файл. В этом случае мы обернем удаление в конструкцию Попытка...Исключение, чтобы обработать возможные ошибки, если файл каким-то образом остался заблокированным.
// ... (код инициализации Excel и заполнения файла) ...
Если ПредварительныйПросмотр Тогда
Excel.Visible = True;
// Файл открыт для пользователя. Не удаляем его немедленно.
// При необходимости, пользователь должен закрыть Excel.
Сообщить("Файл Excel открыт для просмотра. Пожалуйста, закройте Excel по завершении работы.");
Иначе
Книга.SaveAs(ПолноеИмяВременногоФайла); // Сохраняем изменения во временном файле
Excel.Quit(); // Закрываем Excel
// Теперь пытаемся удалить временный файл
Если ФайлСуществует(ПолноеИмяВременногоФайла) Тогда
Попытка
УдалитьФайлы(ПолноеИмяВременногоФайла);
ЗаписатьВЖурналРегистрации("Временный файл Excel успешно удален: " + ПолноеИмяВременногоФайла, УровеньВажностиЖурнала.Информация);
Исключение
// Если не удалось удалить (например, Excel не успел закрыть файл полностью)
ЗаписатьВЖурналРегистрации("Не удалось удалить временный файл Excel: " + ПолноеИмяВременногоФайла + ". Причина: " + ОписаниеОшибки(), УровеньВажностиЖурнала.Предупреждение);
КонецПопытки;
КонецЕсли;
КонецЕсли;
Если мы разрешаем предварительный просмотр (Excel.Visible = True), то файл остается открытым для пользователя. В таком сценарии немедленное программное удаление невозможно. Автор темы предложил хороший подход: удалять такие "зависшие" временные файлы при следующем запуске обработки или в специальной фоновой/регламентной задаче.
Этот метод требует аккуратности. Мы не должны удалять чужие файлы или файлы, которые еще могут понадобиться пользователю. Лучше всего, если наши временные файлы имеют некий уникальный префикс или хранятся в подкаталоге, чтобы мы могли точно идентифицировать их для удаления.
Мы должны использовать Попытка...Исключение при удалении каждого файла, поскольку любой из них может быть открыт пользователем в данный момент.
// Пример процедуры для отложенной очистки временных файлов
// Эту процедуру можно вызывать при старте обработки,
// при закрытии программы или в регламентном задании.
Процедура ОчиститьВременныеФайлыExcel() Экспорт
ВременныйКаталог = КаталогВременныхФайлов();
// Используем маску, если у наших файлов есть общий шаблон имени.
// Например, "МойОтчет_*.xls*" или "*_ВыгрузкаДанных_*.xlsx".
// Если мы использовали ПолучитьИмяВременногоФайла(),
// то имена будут уникальными, но мы можем ориентироваться
// на расширение и возраст файла.
// Получаем список всех файлов в каталоге, соответствующих маске Excel
СписокФайлов = НайтиФайлы(ВременныйКаталог, "*.xls*");
Для Каждого ФайлНайденный Из СписокФайлов Цикл
// Можно добавить проверку на возраст файла, чтобы не удалять
// свежесозданные файлы, которые могли быть только что открыты.
// Например, удаляем только файлы старше 1 часа.
Если РазностьДат(ФайлНайденный.ДатаИзменения, ТекущаяДатаСеанса(), ЕдиницаИзмеренияДаты.Час) > 1 Тогда
Попытка
// Важно: Файл.ПолноеИмя содержит полный путь к файлу
УдалитьФайлы(ФайлНайденный.ПолноеИмя);
ЗаписатьВЖурналРегистрации("Удален старый временный файл Excel: " + ФайлНайденный.ПолноеИмя, УровеньВажностиЖурнала.Информация);
Исключение
// Файл мог быть открыт другим процессом.
// Логируем ошибку, но не прерываем процесс очистки.
ЗаписатьВЖурналРегистрации("Не удалось удалить временный файл Excel: " + ФайлНайденный.ПолноеИмя + ". Причина: " + ОписаниеОшибки(), УровеньВажностиЖурнала.Предупреждение);
КонецПопытки;
КонецЕсли;
КонецЦикла;
КонецПроцедуры
Мы видим, что в этой процедуре используются:
КаталогВременныхФайлов(): Получает путь к системному каталогу для временных файлов.НайтиФайлы(): Позволяет нам найти все файлы, соответствующие определенной маске (например, "*.xls*" для всех файлов Excel).РазностьДат(): Мы можем использовать эту функцию для проверки возраста файла, чтобы не удалять файлы, которые могли быть созданы только что и еще используются.УдалитьФайлы(): Функция для удаления файлов.Попытка...Исключение: Критически важно использовать эту конструкцию при удалении, так как файл может быть открыт или заблокирован, и попытка удаления приведет к ошибке, если ее не обработать.ЗаписатьВЖурналРегистрации(): Помогает отслеживать, какие файлы были удалены, а какие нет, и почему.Независимо от выбранной стратегии, крайне важно правильно завершать работу с COM-объектами Excel. Несоблюдение этого правила может привести к "зависшим" процессам Excel в диспетчере задач, что потребляет системные ресурсы и может блокировать файлы.
Всегда вызывайте метод Excel.Quit() для закрытия приложения Excel, если оно было открыто в невидимом режиме (Excel.Visible = False) или если вы хотите принудительно его закрыть после программной работы. Если Excel был открыт в видимом режиме (Excel.Visible = True) для пользователя, то Quit() вызывайте только в том случае, если вы уверены, что пользователь уже закончил работу или если вы хотите принудительно закрыть Excel. В противном случае, пользователь может потерять несохраненные изменения.
После вызова Quit() или если Excel не был запущен, мы должны явно освободить переменные, ссылающиеся на COM-объекты, присвоив им значение Неопределено. Это помогает сборщику мусора очистить память и ресурсы.
Наконец
// Если Excel был запущен и не является видимым для пользователя
Если Не Excel = Неопределено Тогда
Если Excel.Visible = False Тогда
Excel.Quit(); // Закрываем приложение Excel
КонецЕсли;
Книга = Неопределено; // Освобождаем ссылку на книгу
Лист = Неопределено; // Освобождаем ссылку на лист (если была)
Excel = Неопределено; // Освобождаем ссылку на приложение Excel
КонецЕсли;
КонецНаконец;
Мы используем блок Наконец в конструкции Попытка...Исключение...Наконец, чтобы гарантировать выполнение освобождения объектов, даже если в процессе работы произошла ошибка.
Помимо основных задач, нам необходимо учитывать и другие важные аспекты при работе с Excel из 1С:
Всегда используйте блоки Попытка...Исключение...КонецПопытки для обертывания операций с COM-объектами. Это позволяет нам перехватывать ошибки, возникающие при запуске Excel, открытии/сохранении файлов, доступе к ячейкам и т.д., и корректно реагировать на них, вместо того чтобы программа просто "вылетала".
Работа с COM-объектами может быть относительно медленной, особенно при большом объеме данных. Чтобы оптимизировать производительность:
Лист.Cells(Строка, Колонка).Value = Значение;), старайтесь собирать данные в массивы 1С, а затем записывать эти массивы в диапазон ячеек Excel за одну операцию (например, Лист.Range("A1:C10").Value = МассивДанных;).Excel.Calculation = -4135; // xlCalculationManual), а затем включить его или принудительно пересчитать книгу (Книга.Calculate();) после завершения заполнения.Excel.DisplayAlerts = False;, чтобы избежать появления диалоговых окон Excel, которые могут остановить выполнение скрипта (например, при попытке перезаписать существующий файл без запроса).После заполнения данных в шаблон, который содержит формулы, может потребоваться принудительный пересчет. Хотя Excel обычно делает это автоматически, в некоторых случаях (особенно при сложном взаимодействии формул) вызов методов Книга.Calculate() или Лист.Calculate() может быть необходим для обновления всех результатов до сохранения или отображения.
Если ваш шаблон содержит макросы, убедитесь, что на компьютере пользователя соответствующим образом настроены параметры безопасности Excel. Если макросы отключены или требуют подтверждения, функциональность файла может быть нарушена. Информирование пользователя о необходимости включения макросов или настройка доверенных расположений файлов являются частью решения.
Подход с COM-объектами Excel.Application является специфичным для операционной системы Windows. Если ваша система 1С работает на сервере под Linux, этот метод не будет работать. В таких случаях нам придется использовать сторонние библиотеки для работы с форматом XLSX, которые обычно не поддерживают выполнение макросов и сложных Excel-функций, а сосредоточены на данных и форматировании.
В завершение, мы видим, что работа с Excel из 1С, особенно с учетом формул, макросов и временных файлов, требует внимательного и продуманного подхода. Комбинируя стратегию сохранения, корректное управление временными файлами с использованием ПолучитьИмяВременногоФайла() и тщательную обработку COM-объектов и исключений, мы можем создать надежное и эффективное решение. Помните, что ключ к успеху лежит в детализации и предвидении возможных проблемных сценариев.