Быстрое заполнение данными файла MS EXCEL

Одна из самых ненавидимых мной фраз: «и еще сделай, пожалуйста, выгрузку это всего в Excel так чтобы…» Хоть я и начинал кодить в Экселе переносить в него все, что уже наворотили и еще навротят в 1С занятие нудное и не благодарное. Но еще хуже, когда готовый и устоявшийся шаблон экселя просят заполнить / дополнить данными из 1С.

Довольно долго и по разному я решал задачу заполнения файла Excel данными из 1С пока наконец не догадался использовать объект рабочего листа QueryTables. По сравнению с записью в Value ячеек это на порядки быстрее, а кроме того позволяет задать форматы заполняемых данных.

В сравнении с ADO этот способ имеет такие преимущества как то, что заполнение идет не в цикле и позволяет адресоваться на место вставки, вместо того, чтобы вставлять записи с самого начала листа.

Недостатком в сравнении с ADO является то, что нам нужен установленный Excel, так как работа идет через COM. 

В любом случае я привожу здесь ссылки на две замечательные статьи по заполнению Excel через ADO. Статьи, которые мне реально помогали, но все-таки не смогли полностью решить моих проблем с быстродействием и адресацией, вставляемых данных.

Выгружаем в EXCEL с помощью ADO //infostart.ru/public/65045/ 

Выгружаем в EXCEL с помощью ADO (часть 2) //infostart.ru/public/69248/

Чтобы не утомлять читателей приведу сразу код процедуры. Работает на клиенте и на сервере.

//ПараметрыЭкселя - Структура
//  .ФайлДляВставки - Строка. Полное имя файла Excel куда вставляются данные.
//  .НомерЛиста  - Целое. Порядковый номер листа для вставки данных в книге.
//  .НачСтрока   - Целое. Номер строки верхней левой ячейки дипазона вставки.
//  .НачСтолбец  - Целое. Номер столбца верхней левой ячейки дипазона вставки.
//ПараметрыОбмена - Структура
//  .ТекстДляВставки  - Строка/Текстовый документ. Многострочный текст с разделителями для вставки в эксель.
//  .МассивФорматов  - Массив. Состоит из целых чисел, определяющих формат для каждого столбца вставляемых данных
//        1 - число/формула, 2 - текст, 4 - дата, 9 - пропустить столбец
//  .СимволРазделитель  - Символ, используемый в тексте для разделения столбцов.
Процедура ПоместитьВЭксельПоСтолбцам(ПараметрыЭкселя,ПараметрыОбмена)

ExcelCOM  = Новый COMОбъект("Excel.Application");
КнигаExcel  = ExcelCOM.WorkBooks.Open(СокрЛп(ПараметрыЭкселя.ФайлДляВставки));
ЛистExcel  = ExcelCOM.WorkSheets(ПараметрыЭкселя.НомерЛиста);

ExcelCOM.DisplayAlerts = 1;

ТекстовыйФайл = ПолучитьИмяВременногоФайла("txt");

Если ТипЗнч(ПараметрыОбмена.ТекстДляВставки) = Тип("ТекстовыйДокумент") ТОгда
ТекстовыйФайл = ПолучитьИмяВременногоФайла("txt");
ПараметрыОбмена.ТекстДляВставки.Записать(ТекстовыйФайл, "windows-1251");
ИначеЕсли ТипЗнч(ПараметрыОбмена.ТекстДляВставки) = Тип("Строка") Тогда
тф = Новый ТекстовыйДокумент;
тф.УстановитьТекст(ПараметрыОбмена.ТекстДляВставки);
тф.Записать(ТекстовыйФайл, "windows-1251");
Иначе
Возврат;
КонецЕсли;

МассивФорматов = Новый COMSafeArray(ПараметрыОбмена.МассивФорматов,"VT_UINT");

QueryTable = ЛистExcel.QueryTables.Add("TEXT;" + ТекстовыйФайл, ЛистExcel.Cells(ПараметрыЭкселя.НачСтрока,ПараметрыЭкселя.НачСтолбец));
QueryTable.Name = "Text_to_Excel";
QueryTable.FieldNames = True;
QueryTable.RowNumbers = False;
QueryTable.FillAdjacentFormulas = False;
QueryTable.PreserveFormatting = True;
QueryTable.RefreshOnFileOpen = False;
QueryTable.RefreshStyle = 0;
QueryTable.SavePassword = False;
QueryTable.SaveData = True;
QueryTable.AdjustColumnWidth = True;
QueryTable.RefreshPeriod = 0;
QueryTable.TextFilePromptOnRefresh = False;
QueryTable.TextFilePlatform = 1252;
QueryTable.TextFileStartRow = 1;
QueryTable.TextFileConsecutiveDelimiter = False;
QueryTable.TextFileTabDelimiter = True;
QueryTable.TextFileSemicolonDelimiter = False;
QueryTable.TextFileCommaDelimiter = False;
QueryTable.TextFileSpaceDelimiter = False;
QueryTable.TextFileOtherDelimiter = ПараметрыОбмена.СимволРазделитель;
QueryTable.TextFileColumnDataTypes = МассивФорматов;
QueryTable.TextFileTrailingMinusNumbers = True;
Успех = QueryTable.Refresh(false);

КнигаExcel.Save();

ExcelCOM.DisplayAlerts = 0;
ExcelCOM.ActiveWorkbook.Close();
ExcelCOM.Quit();
ExcelCOM.DisplayAlerts = 1;

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

Вот почти и все.

В приложении демо обработка, если понадобится пример использования. Работает на всех формах.