Многие пользователи 1С v7 сталкиваются с необходимостью выгружать данные в актуальный формат Excel — .xlsx. Легкий обмен данными с MS Excel и Power BI становится критически важным для современного бизнес-анализа (поможет сбор данных из 1С 7.7 для Excel), однако штатные средства 1С v7 исторически ориентированы на старый формат .xls. Это создает определенные неудобства и ограничения, особенно когда требуется сохранение больших таблиц, работа с которыми в старом формате может быть крайне медленной или приводить к ошибкам. В этой статье мы подробно рассмотрим, как решить эту задачу, используя как готовые внешние компоненты, так и программные методы.
Давайте вместе разберем доступные подходы и выберем наиболее подходящий для вашей ситуации.
Одним из наиболее удобных и эффективных способов работы с форматом .xlsx в 1С v7 является применение специализированной внешней компоненты. Мы рассмотрим компонент v7Moxel, который значительно упрощает процесс сохранения табличных данных.
Что это за компонент?
v7Moxel — это внешняя компонента, разработанная специально для 1С v7. Она предоставляет расширенные возможности по сохранению табличных документов в различных форматах, включая .xlsx, .html и .pdf. Главное ее преимущество в том, что она умеет перехватывать стандартные функции 1С по интерактивному и программному сохранению. Это значительно упрощает экспорт, исключая проблемы, когда 1С задумывается надолго при формировании отчетов в 5000 строк и более.
Где найти компоненту?
Компонента v7Moxel доступна на платформе GitHub. Мы можем скачать последнюю версию компоненты из раздела "Releases" в репозитории проекта. Обычно она распространяется в виде архива, который содержит все необходимые файлы для установки и использования. Проанализируем ситуацию: разработчик компоненты указал, что она больше не поддерживается, но доступна для свободного использования.
Как ее использовать?
После получения файлов компоненты, ее необходимо зарегистрировать в системе (как любую внешнюю компоненту 1С v7) и подключить в конфигурации. После успешной установки и подключения, v7Moxel начинает работать "прозрачно", перехватывая вызовы к стандартным методам сохранения. Это минимизирует необходимость вносить существенные изменения в существующий код.
Преимущества подхода:
.xlsx, но и .html, .pdf.Если вы не хотите использовать внешние компоненты, можно реализовать сохранение в .xlsx программно, используя технологию ADO (ActiveX Data Objects). Подобные алгоритмы часто включает в себя комплексная библиотека процедур и функций для платформы 7.7, позволяющая эффективно работать с современными типами данных. Этот метод позволяет напрямую взаимодействовать с файлами Excel 2007 и новее с помощью SQL-подобных запросов.
Необходимый драйвер:
Для работы с .xlsx файлами через ADO нам понадобится драйвер Microsoft Access Database Engine, который включает провайдер Microsoft.ACE.OLEDB.12.0. Убедимся, что он установлен на компьютере, с которого будет производиться выгрузка.
Основные объекты ADO:
Мы будем работать со следующими объектами ADO:
ADODB.Connection: для установки соединения.ADODB.Command: для выполнения SQL-команд.ADODB.Recordset: для чтения данных.ADOX.Catalog: для получения информации о листах.Разберем по шагам реализацию функций:
Представим набор функций, которые можно разместить в глобальном модуле 1С v7. Эти функции позволят нам подключаться к файлу .xlsx, создавать в нем листы, записывать данные и читать их обратно в ТаблицуЗначений 1С.
В начале глобального модуля объявим глобальные переменные для соединения и команды:
Перем ConnectionXLSX Экспорт;
Перем CommandXLSX Экспорт;
Вспомогательная функция для подготовки строковых значений:
Функция ПодготовитьСтрокуКПечати(лСтрока)
лРез = СокрЛП(лСтрока);
лРез = СтрЗаменить(лРез,"'",""); // Экранируем одинарные кавычки
Возврат лРез;
КонецФункции
1. Подключение к файлу Excel:
Функция ЭксельADO_Подключиться(лПутьКФайлу, ЧитЗаг = 0) Экспорт
Если ЧитЗаг = 0 Тогда // Если не читаем заголовки, то предполагаем, что их нет
СтрокаПодключения = "
|Provider=Microsoft.ACE.OLEDB.12.0;
|Data Source='" + лПутьКФайлу + "';
|Extended Properties=""Excel 12.0 xml; HDR=YES"";";
Иначе // Если читаем, то устанавливаем HDR=NO, чтобы первая строка воспринималась как данные
СтрокаПодключения = "
|Provider=Microsoft.ACE.OLEDB.12.0;
|Data Source='" + лПутьКФайлу + "';
|Extended Properties=""Excel 12.0 xml; HDR=NO"";";
КонецЕсли;
Попытка
// Создаем соединение
ConnectionXLSX = СоздатьОбъект("ADODB.Connection");
ConnectionXLSX.Open(СтрокаПодключения);
CommandXLSX = СоздатьОбъект("ADODB.Command");
CommandXLSX.ActiveConnection = ConnectionXLSX;
CommandXLSX.CommandType = 1; // Указываем тип команды как adCmdText
Исключение
Сообщить("Ошибка подключения к файлу Excel: " + ОписаниеОшибки());
Возврат 0;
КонецПопытки;
Возврат 1;
КонецФункции
2. Отключение от файла Excel:
Процедура ЭксельADO_Отключиться() Экспорт
Состояние("Запись файла");
CommandXLSX = 0;
Попытка
ConnectionXLSX.Close();
Исключение
КонецПопытки;
ConnectionXLSX = 0;
КонецПроцедуры
3. Создание листа (таблицы) с колонками:
Функция СоздатьТаблицуСКолонками(ТЗ, ИмяЛиста, БезЗаголовков = 0) Экспорт // Добавлен параметр БезЗаголовков
СчКол = 0;
СтрДанных = "";
Если (ТЗ.КоличествоСтрок() = 0) И (БезЗаголовков = 1) Тогда
Возврат 0;
КонецЕсли;
Для СчКол = 1 По ТЗ.КоличествоКолонок() Цикл
Тип = "";
Размер = "";
Точн = "";
Заг = "";
ТЗ.ПолучитьПараметрыКолонки(СчКол, Тип, Размер, Точн, Заг);
Если БезЗаголовков = 1 Тогда
Заг = СокрЛП(ТЗ.ПолучитьЗначение(1, СчКол)); // Если без заголовков, берем значение из первой строки ТЗ
КонецЕсли;
Если Тип = "Число" Тогда
Если ПустоеЗначение(Точн) = 0 Тогда
Если Точн > 0 Тогда
ТипКол = "float"; // Число с плавающей точкой
Иначе
ТипКол = "int"; // Целое число
КонецЕсли;
Иначе
ТипКол = "int";
КонецЕсли;
ИначеЕсли Тип = "Дата" Тогда
ТипКол = "datetime"; // Тип для даты и времени
Иначе
ТипКол = "char(" + ?(Размер > 0, Размер, 255) + ")"; // Строковый тип, если размер не указан, по умолчанию 255
КонецЕсли;
СтрДанных = СтрДанных + "[" + Заг + "] " + ТипКол + ", ";
КонецЦикла;
СтрДанных = СокрЛП(СтрДанных);
ДлинаСтрДанных = СтрДлина(СтрДанных);
СтрДанных = Лев(СтрДанных, ДлинаСтрДанных - 1); // Убираем последнюю запятую
СтрКоманды = "CREATE TABLE [" + ИмяЛиста + "] (" + СтрДанных + ")";
CommandXLSX.CommandText = СтрКоманды;
CommandXLSX.Execute();
Возврат 1;
КонецФункции
4. Печать строк в файл Excel:
Важно помнить, что после выгрузки иногда требуется изменить числовой формат ячеек на текстовый для корректного отображения артикулов или кодов. В таких случаях пригодится утилита изменения всех ячеек книги Excel на текстовый, которая автоматизирует этот процесс.
Процедура ЭксельADO_ПечатьСтрок(ТЗ, ИмяЛиста, БезЗаголовков = 0, ЕстьШабл = 0) Экспорт
Перем НазваниеКолонки, ШиринаКолонки, ТипПеременной, ДлинаПеременной, ФорматСтроки;
Если ТЗ.КоличествоСтрок() = 0 Тогда
Возврат;
КонецЕсли;
ВсегоСтрок = ТЗ.КоличествоСтрок();
СчСтр = 0;
СтарПроц = 0;
Состояние("Идет вывод файла Excel, ожидайте...");
ТЗ.ВыбратьСтроки();
Пока ТЗ.ПолучитьСтроку() = 1 Цикл
СчСтр = СчСтр + 1;
Если СчСтр = 1 Тогда
Если ЕстьШабл = 0 Тогда
СоздатьТаблицуСКолонками(ТЗ, СокрЛП(ИмяЛиста), БезЗаголовков);
Если БезЗаголовков = 1 Тогда // Если заголовки были в первой строке данных, пропускаем ее
Продолжить;
КонецЕсли;
КонецЕсли;
КонецЕсли;
ВывСтр = СчСтр;
ПроцентВыполнения = Окр(ВывСтр / ВсегоСтрок * 100);
Если СтарПроц <> ПроцентВыполнения Тогда
ТекстСостояния = Шаблон("Вывод в файл Excel: [ПроцентВыполнения]% ([СчСтр]/[ВсегоСтрок])");
Состояние(ТекстСостояния);
СтарПроц = ПроцентВыполнения;
КонецЕсли;
лСписокЗначенийКолонок = СоздатьОбъект("СписокЗначений");
лСтрокаПараметров = "";
Для НомерКолонки = 1 По ТЗ.КоличествоКолонок() Цикл
ИдентификаторКолонки = ТЗ.ПолучитьПараметрыКолонки(НомерКолонки,ТипПеременной,ДлинаПеременной,,НазваниеКолонки,ШиринаКолонки,ФорматСтроки);
Если ШиринаКолонки <> -1 Тогда
ЗначениеЯчейки = ТЗ.ПолучитьЗначение(СчСтр,НомерКолонки);
Если ТипПеременной = "Число" Тогда
Если ДлинаПеременной = 1 Тогда
лСтрокаПараметров = лСтрокаПараметров + "'" + ?(ЗначениеЯчейки = 0,"ОК", "Ошибка") + "', ";
Иначе
ПечЗначение = Формат(ЗначениеЯчейки, ФорматСтроки);
лСтрокаПараметров = лСтрокаПараметров + ПечЗначение + ", ";
КонецЕсли;
ИначеЕсли ТипПеременной = "Документ" Тогда
Попытка
ПечЗначение = ЗначениеЯчейки.НомерДок;
Исключение
ПечЗначение = "";
КонецПопытки;
лСтрокаПараметров = лСтрокаПараметров + "'" + ПодготовитьСтрокуКПечати(ПечЗначение) + "', ";
ИначеЕсли ТипПеременной = "Дата" Тогда
ПечЗначение = ?(ПустоеЗначение(ЗначениеЯчейки) = 1, "", Формат(ЗначениеЯчейки,"Д ДДММГГГГ"));
лСтрокаПараметров = лСтрокаПараметров + "'" + ПечЗначение + "', ";
ИначеЕсли ТипПеременной = "Строка" Тогда
ПечЗначение = СокрЛП(ЗначениеЯчейки);
лСтрокаПараметров = лСтрокаПараметров + "'" + ПодготовитьСтрокуКПечати(ПечЗначение) + "', ";
Иначе
ПечЗначение = ЗначениеЯчейки;
лСтрокаПараметров = лСтрокаПараметров + "'" + ПодготовитьСтрокуКПечати(ПечЗначение) + "', ";
КонецЕсли;
КонецЕсли;
КонецЦикла;
лСтрокаПараметров = Лев(лСтрокаПараметров, СтрДлина(лСтрокаПараметров) - 2);
CommandXLSX.CommandText = "INSERT INTO [" + ИмяЛиста+ "$] VALUES (" + лСтрокаПараметров + ")";
CommandXLSX.Execute();
КонецЦикла;
КонецПроцедуры
5. Чтение данных из Excel в ТаблицуЗначений 1С:
Функция Excel2007вТЗ(Файл) Экспорт
Рез = ЭксельADO_Подключиться(СокрЛП(Файл), 1);
Если Рез = 0 Тогда
Возврат 0;
КонецЕсли;
Коннект = ConnectionXLSX;
axCatalog = СоздатьОбъект("ADOX.Catalog");
axCatalog.ActiveConnection = ConnectionXLSX;
Если axCatalog.Tables.Count = 0 Тогда
Сообщить("В документе Excel нет листов!");
ЭксельADO_Отключиться();
Возврат 0;
КонецЕсли;
ListName = axCatalog.Tables.Item(0).Name;
Если Прав(ListName, 1) <> "$" Тогда
ListName = ListName + "$";
КонецЕсли;
Коннект.CursorLocation = 3;
ТекстЗапроса = "SELECT * FROM [" + ListName + "]";
RS = СоздатьОбъект("ADODB.Recordset");
Попытка
RS.Open(ТекстЗапроса, Коннект);
Исключение
Сообщить("Ошибка открытия Recordset: " + ОписаниеОшибки());
ЭксельADO_Отключиться();
Возврат 0;
КонецПопытки;
Если (RS.EOF = 1) ИЛИ (RS.BOF = 1) Тогда
Сообщить("Нет строк в документе Excel!");
RS.Close();
ЭксельADO_Отключиться();
Возврат СоздатьОбъект("ТаблицаЗначений");
КонецЕсли;
КолвоКолонокExcel = RS.Fields.Count;
ТЗ = СоздатьОбъект("ТаблицаЗначений");
СчКол = 0;
Для СчКол = 1 По КолвоКолонокExcel Цикл
ИмяКол = СокрЛП(RS.Fields.Item(СчКол - 1).Name);
ПозДвоеточия = Найти(ИмяКол, ":");
Если ПозДвоеточия > 0 Тогда
ИмяКол = Лев(ИмяКол, ПозДвоеточия - 1);
КонецЕсли;
ИмяКол = СтрЗаменить(ИмяКол, "-", "");
ТЗ.НоваяКолонка(ИмяКол);
КонецЦикла;
RS.MoveFirst();
НомерСтроки = 0;
Пока RS.EOF() = 0 Цикл
НомерСтроки = НомерСтроки + 1;
ТЗ.НоваяСтрока();
СчКол = 0;
Для СчКол = 1 По КолвоКолонокExcel Цикл
ТЗ.УстановитьЗначение(НомерСтроки, СчКол, RS.Fields.Item(СчКол - 1).Value);
КонецЦикла;
RS.MoveNext();
КонецЦикла;
RS.Close();
ЭксельADO_Отключиться();
RS = 0;
ConnectionXLSX = 0;
Возврат ТЗ;
КонецФункции
Для сохранения данных:
Если ЭксельADO_Подключиться(ИмяФайлаЭксель) = 0 Тогда
Предупреждение("Ошибка подключения к Excel-файлу!",30);
Возврат 0;
КонецЕсли;
ЭксельADO_ПечатьСтрок(ТЗ, "Данные");
ЭксельADO_Отключиться();
Преимущества подхода:
Мы рассмотрели два основных подхода к сохранению данных из 1С v7 в формат .xlsx. Если вашей целью является не просто выгрузка, а программное создание сводной таблицы Excel из 1С или глубокая аналитическая обработка, то метод ADO или COM-соединение (поможет выгрузка данных из 1С 7.7 через COM/OLE) будут наиболее предпочтительны. Для устранения типичных недостатков в копируемых данных рекомендуем обратить внимание на набор инструментов Коррекция 1С для Excel, который помогает довести выгруженный файл до идеала.
Выбор метода зависит от ваших требований к производительности и возможности администрирования рабочих мест пользователей.