Вставка строк в файл Excel из кода 1С:Предприятия 8.3: четыре рабочих способа

Добавить строку в Excel из 1С проще всего без самого Excel: соберите данные в ТабличныйДокумент, выведите нужную область макета через Вывести() или ВставитьОбласть() и сохраните файл методом Записать(ИмяФайла, ТипФайлаТабличногоДокумента.XLSX). Если же нужно вставить строку в середину уже готового файла заказчика и сохранить все его формулы и оформление, работайте через COM-объект Excel.Application и метод Rows(N).Insert().

Типичная ситуация: бухгалтер присылает шаблон реестра в Excel с шапкой, подписями и формулами итогов, а 1С должна дописывать туда строки по документам за месяц. Для такой задачи подходят оба подхода, но с разной ценой по скорости и совместимости. Всё написанное относится к платформе 1С:Предприятие 8.3 и не зависит от конфигурации: код одинаково работает в БП, ЗУП, УТ и в самописных базах.

Какой способ выбрать: сравнение до начала работы

Сначала ответьте себе на два вопроса: где выполняется код (на клиенте или на сервере) и стоит ли на этом компьютере Microsoft Excel. От этого зависит почти всё.

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

Способ 1. Табличный документ: строка за строкой без Excel

ТабличныйДокумент — встроенный объект платформы. Он умеет сохранять себя в формат XLSX, поэтому Excel на компьютере не нужен. Код работает и в файловой базе, и на сервере под Linux.

Самый простой вариант — писать прямо в ячейки. Ячейку адресуют методом Область(Строка, Колонка), а номер следующей свободной строки даёт свойство ВысотаТаблицы.

&НаСервере
Функция ВыгрузитьРеестр(Знач Данные)

    ТабДок = Новый ТабличныйДокумент;
    ТабДок.Область(1, 1).Текст = "Документ";
    ТабДок.Область(1, 2).Текст = "Дата";
    ТабДок.Область(1, 3).Текст = "Сумма";

    Для Каждого Стр Из Данные Цикл
        НомерСтроки = ТабДок.ВысотаТаблицы + 1;
        ТабДок.Область(НомерСтроки, 1).Текст = Стр.Документ;
        ТабДок.Область(НомерСтроки, 2).Текст = Формат(Стр.Дата, "ДФ=dd.MM.yyyy");
        ТабДок.Область(НомерСтроки, 3).Текст = Формат(Стр.Сумма, "ЧДЦ=2; ЧГ=0");
    КонецЦикла;

    ИмяФайла = ПолучитьИмяВременногоФайла("xlsx");
    ТабДок.Записать(ИмяФайла, ТипФайлаТабличногоДокумента.XLSX);

    Адрес = ПоместитьВоВременноеХранилище(Новый ДвоичныеДанные(ИмяФайла));
    УдалитьФайлы(ИмяФайла);
    Возврат Адрес;

КонецФункции

Обратите внимание на последние строки. На сервере Записать() кладёт файл в файловую систему сервера, а не на компьютер пользователя. Поэтому файл переносят во временное хранилище и уже на клиенте сохраняют туда, куда выберет пользователь.

Число в Excel должно остаться числом

Когда пишете в Текст, Excel получает строку, и суммы по колонке не считаются. Чтобы в файле было настоящее число, задайте ячейке тип значения:

Ячейка = ТабДок.Область(НомерСтроки, 3);
Ячейка.СодержитЗначение = Истина;
Ячейка.ТипЗначения = Новый ОписаниеТипов("Число");
Ячейка.Значение = Стр.Сумма;
Ячейка.Формат = "ЧДЦ=2";

Вставка строки в середину таблицы

Вывести() всегда дописывает в конец. Чтобы вставить строку, например, перед итогами, используйте ВставитьОбласть() со смещением вниз:

// вставить пустую строку на место 5-й, остальные сдвинуть вниз
ОбластьСтроки = ТабДок.Область("R5");
ТабДок.ВставитьОбласть(ОбластьСтроки, ОбластьСтроки,
    ТипСмещенияТабличногоДокумента.ПоВертикали, Истина);

Последний параметр Истина копирует оформление соседней строки, поэтому новая строка получает те же шрифты и рамки.

Способ 2. Макет: шаблон строки рисуют один раз

Заполнять ячейки по номерам удобно для простых выгрузок. Как только появляются шапка, объединённые ячейки и рамки, код превращается в кашу из координат. Здесь выручает табличный макет.

Макет создают в Конфигураторе у обработки или отчёта: ветка Макеты → добавить → тип «Табличный документ». На листе рисуют области: выделяют строки и задают им имена через свойства, например Шапка, Строка, Подвал. В ячейках строки включают заполнение «Параметр» и пишут имя параметра: Документ, Дата, Сумма.

Дальше вывод сводится к трём действиям в цикле: получить область, заполнить параметры, вывести.

Макет = РеквизитФормыВЗначение("Объект").ПолучитьМакет("Реестр");
ОбластьШапка  = Макет.ПолучитьОбласть("Шапка");
ОбластьСтрока = Макет.ПолучитьОбласть("Строка");
ОбластьПодвал = Макет.ПолучитьОбласть("Подвал");

ТабДок = Новый ТабличныйДокумент;
ТабДок.Вывести(ОбластьШапка);

Итого = 0;
Для Каждого Стр Из Данные Цикл
    ОбластьСтрока.Параметры.Заполнить(Стр);
    ТабДок.Вывести(ОбластьСтрока);
    Итого = Итого + Стр.Сумма;
КонецЦикла;

ОбластьПодвал.Параметры.Итого = Итого;
ТабДок.Вывести(ОбластьПодвал);

Метод Параметры.Заполнить() берёт значения из структуры, строки таблицы значений или выборки запроса по совпадающим именам. Назовите поля запроса так же, как параметры макета, и заполнять их по одному не придётся.

💡 Если нужно доработать макет типовой печатной формы, не меняйте саму конфигурацию: сделайте расширение конфигурации и правьте макет в нём. Так доработка переживёт обновление.

Способ 3. Запись готового табличного документа в XLSX

Сохранение — отдельный шаг, и у него есть свои тонкости. Метод Записать() принимает тип файла из перечисления ТипФайлаТабличногоДокумента. Для современных версий Excel выбирайте XLSX; вариант XLS даёт старый формат и годится только для очень старых программ.

Дописать строки в существующий файл без Excel

Иногда задача звучит так: есть файл, нужно добавить в него несколько строк. В платформе 8.3 табличный документ умеет читать файлы Excel методом Прочитать():

ТабДок = Новый ТабличныйДокумент;
ТабДок.Прочитать(ИмяФайла);

НомерСтроки = ТабДок.ВысотаТаблицы + 1;
ТабДок.Область(НомерСтроки, 1).Текст = "Новая запись";

ТабДок.Записать(ИмяФайла, ТипФайлаТабличногоДокумента.XLSX);

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

Пользовательский вариант без программирования

Если строку нужно добавить один раз, а не по расписанию, код не нужен. Сформируйте отчёт, нажмите значок дискеты на панели табличного документа, выберите тип файла «Лист Excel» с расширением xlsx. Откройте файл в Excel, щёлкните правой кнопкой по номеру строки и выберите Вставить.

Способ 4. COM-объект Excel.Application: вставка в живой шаблон

COM даёт доступ ко всему, что умеет сам Excel: вставка строк со сдвигом, формулы, стили, листы. Цена — Excel должен быть установлен, а код выполняется только в Windows. Удобнее всего запускать такой код на клиенте.

&НаКлиенте
Процедура ВставитьСтрокиВШаблон(ИмяФайла, Данные)

    Excel = Новый COMОбъект("Excel.Application");
    Excel.Visible = Ложь;
    Excel.DisplayAlerts = Ложь;

    Книга = Excel.Workbooks.Open(ИмяФайла);
    Лист = Книга.Worksheets(1);

    // последняя заполненная строка по колонке A (-4162 = xlUp)
    Последняя = Лист.Cells(Лист.Rows.Count, 1).End(-4162).Row;

    Для Каждого Стр Из Данные Цикл
        // вставить строку перед итоговой (-4121 = xlShiftDown)
        Лист.Rows(Последняя).Insert(-4121);
        Лист.Cells(Последняя, 1).Value = Стр.Документ;
        Лист.Cells(Последняя, 2).Value = Стр.Дата;
        Лист.Cells(Последняя, 3).Value = Стр.Сумма;
        Последняя = Последняя + 1;
    КонецЦикла;

    Книга.Save();
    Книга.Close(Ложь);
    Excel.Quit();

КонецПроцедуры

Здесь последняя строка листа — итоговая. Каждая новая запись вставляется перед ней, итог сдвигается вниз, а формула СУММ в нём автоматически расширяет диапазон, если вставка идёт внутри него.

Константы Excel в 1С недоступны по именам, поэтому их пишут числами. Чаще всего нужны эти:

Чтобы записать формулу, присвойте её свойству Formula на английском: Лист.Cells(Последняя, 4).Formula = "=C5*1.2". Свойство FormulaLocal принимает формулу на языке установленного Excel.

Ошибки при выгрузке в Excel и как их обработать

Основная беда COM — «висячие» процессы EXCEL.EXE. Если код упал между Новый COMОбъект и Excel.Quit(), Excel остаётся в памяти невидимым и держит файл открытым. Через неделю таких процессов десятки.

В языке 1С нет блока «finally», поэтому закрывайте Excel и в обычной ветке, и в ветке исключения:

Excel = Неопределено;
Попытка
    Excel = Новый COMОбъект("Excel.Application");
    Excel.DisplayAlerts = Ложь;
    Книга = Excel.Workbooks.Open(ИмяФайла);
    // ... запись строк ...
    Книга.Save();
    Книга.Close(Ложь);
    Excel.Quit();
Исключение
    Текст = ОписаниеОшибки();
    Если Excel <> Неопределено Тогда
        Excel.DisplayAlerts = Ложь;
        Excel.Quit();
    КонецЕсли;
    Сообщить("Не удалось записать файл: " + Текст);
КонецПопытки;

Скорость: почему выгрузка идёт минутами и как ускорить

Каждое обращение к Лист.Cells(...) — отдельный межпроцессный вызов. На 10 000 строк по 10 колонок это 100 000 вызовов, и выгрузка растягивается на минуты. Табличный документ работает в памяти 1С, поэтому та же таблица сохраняется за секунды.

Если без COM не обойтись, ускорить его можно в разы тремя приёмами.

1. Отключите обновление экрана и пересчёт формул перед записью: Excel.ScreenUpdating = Ложь и Excel.Calculation = -4135. После записи верните -4105.
2. Вставляйте строки одним вызовом, а не по одной: Лист.Rows("5:104").Insert(-4121) сразу добавит сто строк.
3. Записывайте значения целым блоком через массив COMSafeArray, а не по ячейкам.

Третий приём даёт самый большой выигрыш. Массив заполняется в памяти 1С, а в Excel уходит одним присваиванием:

ЧислоСтрок = Данные.Количество();
Массив = Новый COMSafeArray("VT_VARIANT", ЧислоСтрок, 3);

Для Индекс = 0 По ЧислоСтрок - 1 Цикл
    Стр = Данные[Индекс];
    Массив.SetValue(Индекс, 0, Стр.Документ);
    Массив.SetValue(Индекс, 1, Стр.Дата);
    Массив.SetValue(Индекс, 2, Стр.Сумма);
КонецЦикла;

Лист.Rows(Формат(Начало, "ЧГ=0") + ":" + Формат(Начало + ЧислоСтрок - 1, "ЧГ=0")).Insert(-4121);
Лист.Range(Лист.Cells(Начало, 1), Лист.Cells(Начало + ЧислоСтрок - 1, 3)).Value = Массив;

Индексы COMSafeArray начинаются с нуля, первая размерность — строки, вторая — колонки. Начало — номер строки, с которой вставляется блок. Номера строк склеиваются в адрес через Формат(..., "ЧГ=0"), иначе для строки 1000 получится «1 000» с пробелом, и Excel не поймёт адрес.

Порядок цифр в таблице оценочный: на скорость влияют мощность компьютера, версия Excel и число колонок.

Частые вопросы