При разработке механизмов обмена данными часто возникает задача не просто заполнить уже существующий файл Microsoft Excel, а создать новый документ «на лету» и заполнить его данными из табличной части или результата запроса. Рассмотрим подробнее, как реализовать эту задачу различными способами, разберем их преимущества и недостатки, а также выясним причины возможных ошибок при интеграции 1С и Excel.
Этот метод является классическим. Он основан на обращении к установленному в операционной системе приложению Microsoft Excel через механизм COM-интерфейса. Проанализируем ситуацию: если ваша текущая процедура требует указания пути к уже созданному файлу через метод Workbooks.Open(), то для автоматического создания новой книги нам необходимо использовать метод Workbooks.Add().
Рассмотрим пример кода, который создает новое приложение Excel, добавляет в него чистую книгу, заполняет заголовки и данные из табличной части документа:
&НаСервере
Процедура ВыгрузитьНаСервереВExcel()
// Создаем объект приложения Excel
Попытка
ApplicationExcel = Новый COMОбъект("Excel.Application");
Исключение
Сообщить("Не удалось инициализировать Excel. Проверьте, установлено ли приложение на сервере.");
Возврат;
КонецПопытки;
// Делаем приложение невидимым для ускорения работы
ApplicationExcel.Visible = Ложь;
// ВМЕСТО Open используем Add для создания нового файла
НоваяКнига = ApplicationExcel.Workbooks.Add();
Лист = НоваяКнига.Sheets(1);
// Формируем шапку
Лист.Cells(1,1).Value = "Номенклатура";
Лист.Cells(1,2).Value = "Количество";
Лист.Cells(1,3).Value = "Цена";
Лист.Cells(1,4).Value = "Стоимость";
ТЧ = Объект.ТабличнаяЧасть1;
Сч = 2;
Для каждого Строчка Из ТЧ Цикл
Если Строчка.Галка Тогда
Лист.Cells(Сч, 1).Value = Строка(Строчка.Номенклатура);
Лист.Cells(Сч, 2).Value = Строчка.Количество;
Лист.Cells(Сч, 3).Value = Строчка.ЦенаПоступления;
Лист.Cells(Сч, 4).Value = Строчка.Стоимость;
Сч = Сч + 1;
КонецЕсли;
КонецЦикла;
// Автоматический подбор ширины колонок для красоты
Лист.Columns("A:D").AutoFit();
// Сохраняем файл и закрываем Excel
ПутьКФайлу = "C:\Выгрузка\Отчет_" + Формат(ТекущаяДата(), "ДФ=yyyyMMdd_HHmmss") + ".xlsx";
НоваяКнига.SaveAs(ПутьКФайлу);
ApplicationExcel.Quit();
ApplicationExcel = Неопределено;
Сообщить("Файл успешно сохранен: " + ПутьКФайлу);
КонецПроцедуры
Важный нюанс: Обратите внимание на завершение работы. Если мы не вызовем ApplicationExcel.Quit() и не обнулим переменную, процесс EXCEL.EXE останется висеть в памяти сервера, что со временем приведет к утечке ресурсов и сбоям системы.
Современным стандартом разработки в 1С является отказ от COM-объектов в пользу платформенных механизмов. Платформа 1С, начиная с версии 8.3.6, умеет самостоятельно формировать файлы формата XLSX. Это работает значительно быстрее, стабильнее и, что немаловажно, не требует наличия установленного Microsoft Office на сервере или компьютере пользователя. Разберем по шагам, как это сделать.
Мы сначала формируем стандартный ТабличныйДокумент, а затем записываем его в нужном формате:
&НаСервере
Процедура ВыгрузитьЧерезТабДокНаСервере()
ТабДок = Новый ТабличныйДокумент;
// Создаем области или просто заполняем ячейки
ТабДок.Область(1, 1).Текст = "Номенклатура";
ТабДок.Область(1, 2).Текст = "Количество";
Сч = 2;
Для каждого СтрокаТЧ Из Объект.ТабличнаяЧасть1 Цикл
ТабДок.Область(Сч, 1).Текст = Строка(СтрокаТЧ.Номенклатура);
ТабДок.Область(Сч, 2).Текст = СтрокаТЧ.Количество;
Сч = Сч + 1;
КонецЦикла;
// Прямая запись в файл формата XLSX
Путь = "C:\Temp\Выгрузка.xlsx";
ТабДок.Записать(Путь, ТипФайлаТабличногоДокумента.XLSX);
КонецПроцедуры
Этот метод незаменим, если ваш сервер работает под управлением Linux, так как COM-технология там недоступна. Кроме того, такой способ позволяет избежать проблем с разрядностью (32/64 бит), которые часто возникают при использовании COM-объектов.
Проанализируем ситуацию, когда в целевом Excel-файле должны присутствовать сложные формулы или макросы (например, для последующей загрузки в SAP). При прямой записи из ТабличныйДокумент макросы не сохранятся. В этом случае лучшим решением будет использование заранее подготовленного шаблона.
.xlsm или .xltm).
// Пример логики работы с шаблоном
ИмяВременногоФайла = ПолучитьИмяВременногоФайла("xlsm");
МакетШаблона = У обработки.ПолучитьМакет("МойШаблонExcel");
МакетШаблона.Записать(ИмяВременногоФайла);
ApplicationExcel = Новый COMОбъект("Excel.Application");
Книга = ApplicationExcel.Workbooks.Open(ИмяВременногоФайла);
// ... заполняем данные в существующие ячейки ...
Книга.SaveAs("C:\ИтоговыйФайл.xlsm");
Если в вашем документе тысячи строк, поочередное заполнение ячеек (Cells(x, y).Value = ...) будет работать крайне медленно из-за постоянных обращений 1С к стороннему приложению. Посмотрим, как это оптимизировать. Самый эффективный способ — передача данных через массив.
Проанализируем алгоритм:
1. Формируем двумерный массив в 1С.
2. Выделяем в Excel диапазон (Range) соответствующего размера.
3. Передаем массив целиком в свойство Value этого диапазона.
// Пример ускоренной выгрузки
КоличествоСтрок = Объект.ТабличнаяЧасть1.Количество();
Данные = Новый Массив(КоличествоСтрок, 4); // Массив строк и колонок
Для НомерСтроки = 0 По КоличествоСтрок - 1 Цикл
ТекущаяСтрока = Объект.ТабличнаяЧасть1[НомерСтроки];
Данные[НомерСтроки][0] = Строка(ТекущаяСтрока.Номенклатура);
Данные[НомерСтроки][1] = ТекущаяСтрока.Количество;
// ... заполнение остальных колонок
КонецЦикла;
// Вставляем массив одним вызовом
Диапазон = Лист.Range(Лист.Cells(2, 1), Лист.Cells(КоличествоСтрок + 1, 4));
Диапазон.Value = Данные;
Использование этого приема позволяет сократить время выгрузки больших объемов данных с нескольких минут до нескольких секунд.
Подведем итоги нашего разбора. При выборе способа выгрузки придерживайтесь следующих правил:
ТабличныйДокумент и метод Записать().Workbooks.Add().Попытка...Исключение.ПолучитьИмяВременногоФайла(), чтобы избежать конфликтов имен при одновременной работе нескольких пользователей.Выяснив причину медленной работы или ошибок с доступом к файлам, вы сможете построить надежную систему экспорта данных, которая будет удобна конечным пользователям.