Многие пользователи 1С v7 сталкиваются с необходимостью выгружать данные в актуальный формат Excel — .xlsx. Штатные средства 1С v7 исторически ориентированы на старый формат .xls, что создает определенные неудобства и ограничения, особенно при работе с большими объемами данных или при необходимости использовать современные функции Excel. В этой статье мы подробно рассмотрим, как решить эту задачу, используя как готовые внешние компоненты, так и программные методы без их применения.
Давайте вместе разберем доступные подходы и выберем наиболее подходящий для вашей ситуации.
Одним из наиболее удобных и эффективных способов работы с форматом .xlsx в 1С v7 является применение специализированной внешней компоненты. Мы рассмотрим компонент v7Moxel, который значительно упрощает процесс сохранения табличных данных.
Что это за компонент?
v7Moxel — это внешняя компонента, разработанная специально для 1С v7. Она предоставляет расширенные возможности по сохранению табличных документов в различных форматах, включая .xlsx, .html и .pdf. Главное ее преимущество в том, что она умеет перехватывать стандартные функции 1С по интерактивному и программному сохранению в Excel, HTML и TXT. Это означает, что в диалоге выбора файла вместо привычного .xls пользователь сможет выбрать .xlsx (а также .pdf вместо .txt).
Где найти компоненту?
Компонента v7Moxel доступна на платформе GitHub. Мы можем скачать последнюю версию компоненты из раздела "Releases" в репозитории проекта. Обычно она распространяется в виде архива, например, Release.v0.12.zip, который содержит все необходимые файлы для установки и использования. Проанализируем ситуацию: разработчик компоненты указал, что она больше не поддерживается, но доступна для свободного использования.
Как ее использовать?
После получения файлов компоненты, ее необходимо зарегистрировать в системе (как любую внешнюю компоненту 1С v7) и подключить в конфигурации. После успешной установки и подключения, v7Moxel начинает работать "прозрачно", перехватывая вызовы к стандартным методам сохранения. Это значительно упрощает работу, так как не требуется вносить существенные изменения в существующий код для сохранения табличных документов.
Преимущества подхода:
.xlsx, но и .html, .pdf.Если мы не хотим использовать внешние компоненты или столкнулись с ограничениями на их установку, мы можем реализовать сохранение в .xlsx программно, используя технологию ADO (ActiveX Data Objects). Этот метод позволяет напрямую взаимодействовать с файлами Excel 2007 и новее с помощью SQL-подобных запросов. Он требует наличия на клиентской машине специального драйвера, но не зависит от установленного Microsoft Office.
Необходимый драйвер:
Для работы с .xlsx файлами через ADO нам понадобится драйвер Microsoft Access Database Engine (обычно версии 2007 или 2010), который включает провайдер Microsoft.ACE.OLEDB.12.0. Этот драйвер можно скачать с официального сайта Microsoft. Убедимся, что он установлен на компьютере, с которого будет производиться выгрузка.
Основные объекты ADO:
Мы будем работать со следующими объектами ADO:
ADODB.Connection: для установки соединения с файлом Excel.ADODB.Command: для выполнения SQL-команд (создание таблиц, вставка данных).ADODB.Recordset: для чтения данных из файла Excel.ADOX.Catalog: для получения информации о листах (таблицах) в файле.Разберем по шагам реализацию функций:
Представим набор функций, которые можно разместить в глобальном модуле 1С v7. Эти функции позволят нам подключаться к файлу .xlsx, создавать в нем листы, записывать данные и читать их обратно в ТаблицуЗначений 1С.
В начале глобального модуля объявим глобальные переменные для соединения и команды:
Перем ConnectionXLSX Экспорт;
Перем CommandXLSX Экспорт;
Вспомогательная функция для подготовки строковых значений:
Функция ПодготовитьСтрокуКПечати(лСтрока)
лРез = СокрЛП(лСтрока);
лРез = СтрЗаменить(лРез,"'",""); // Экранируем одинарные кавычки
Возврат лРез;
КонецФункции
1. Подключение к файлу Excel:
Функция ЭксельADO_Подключиться устанавливает соединение с указанным .xlsx файлом. Параметр ЧитЗаг определяет, следует ли рассматривать первую строку как заголовки (HDR=YES) или как обычные данные (HDR=NO).
Функция Эксель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_Отключиться закрывает соединение и освобождает объекты ADO.
Процедура ЭксельADO_Отключиться() Экспорт
Состояние("Запись файла");
CommandXLSX = 0;
Попытка
ConnectionXLSX.Close();
Исключение
КонецПопытки;
ConnectionXLSX = 0;
КонецПроцедуры
3. Создание листа (таблицы) с колонками:
Функция СоздатьТаблицуСКолонками формирует SQL-команду CREATE TABLE для создания нового листа в файле Excel. Мы анализируем параметры колонок ТаблицыЗначений 1С (тип, размер, точность) для определения соответствующих типов данных в Excel (например, float, int, char(N)).
Функция СоздатьТаблицуСКолонками(ТЗ, ИмяЛиста, БезЗаголовков = 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:
Процедура ЭксельADO_ПечатьСтрок итерируется по строкам ТаблицыЗначений и вставляет их в лист Excel, используя SQL-команду INSERT INTO. Мы уделяем внимание правильному форматированию значений для разных типов данных (числа, даты, строки, ссылки на документы) и экранированию строковых данных.
Процедура ЭксельADO_ПечатьСтрок(ТЗ, ИмяЛиста, БезЗаголовков = 0, ЕстьШабл = 0) Экспорт
Перем НазваниеКолонки, ШиринаКолонки, ТипПеременной, ДлинаПеременной, ФорматСтроки;
Если ТЗ.КоличествоСтрок() = 0 Тогда
Возврат;
КонецЕсли;
ВсегоСтрок = ТЗ.КоличествоСтрок();
СчСтр = 0;
СтарПроц = 0;
Состояние("Идет вывод файла Excel, ожидайте...");
ТЗ.ВыбратьСтроки();
Пока ТЗ.ПолучитьСтроку() = 1 Цикл
СчСтр = СчСтр + 1;
Если СчСтр = 1 Тогда
Если ЕстьШабл = 0 Тогда
СоздатьТаблицуСКолонками(ТЗ, СокрЛП(ИмяЛиста), БезЗаголовков);
Если БезЗаголовков = 1 Тогда // Если заголовки были в первой строке данных, пропускаем ее
Продолжить;
КонецЕсли;
КонецЕсли;
КонецЕсли;
ВывСтр = СчСтр;
ПроцентВыполнения = Окр(ВывСтр / ВсегоСтрок * 100);
Если СтарПроц <> ПроцентВыполнения Тогда
ТекстСостояния = Шаблон("Вывод в файл Excel: [ПроцентВыполнения]% ([СчСтр]/[ВсегоСтрок])");
Состояние(ТекстСостояния);
СтарПроц = ПроцентВыполнения;
КонецЕсли;
лСписокЗначенийКолонок = СоздатьОбъект("СписокЗначений"); // Этот объект не используется в текущей реализации, можно убрать
лСтрокаПараметров = "";
Для НомерКолонки = 1 По ТЗ.КоличествоКолонок() Цикл
ИдентификаторКолонки = ТЗ.ПолучитьПараметрыКолонки(НомерКолонки,ТипПеременной,ДлинаПеременной,,НазваниеКолонки,ШиринаКолонки,ФорматСтроки);
// ШиринаКолонки == -1 означает, что колонка скрыта или неактивна, не выводим ее
Если ШиринаКолонки <> -1 Тогда
ЗначениеЯчейки = ТЗ.ПолучитьЗначение(СчСтр,НомерКолонки);
Если ТипПеременной = "Число" Тогда
// Пример специальной обработки для булевых значений, представленных числом 1/0
Если ДлинаПеременной = 1 Тогда
лСтрокаПараметров = лСтрокаПараметров + "'" + ?(ЗначениеЯчейки = 0,"ОК", "Ошибка") + "', ";
Иначе
// Форматируем число, если указан формат
ПечЗначение = Формат(ЗначениеЯчейки, ФорматСтроки);
лСтрокаПараметров = лСтрокаПараметров + ПечЗначение + ", ";
КонецЕсли;
ИначеЕсли ТипПеременной = "Документ" Тогда
Попытка // Пытаемся получить номер документа
ПечЗначение = ЗначениеЯчейки.НомерДок;
Исключение
ПечЗначение = "";
КонецПопытки;
лСтрокаПараметров = лСтрокаПараметров + "'" + ПодготовитьСтрокуКПечати(ПечЗначение) + "', ";
ИначеЕсли ТипПеременной = "Дата" Тогда
// Форматируем дату в нужный вид
ПечЗначение = ?(ПустоеЗначение(ЗначениеЯчейки) = 1, "", Формат(ЗначениеЯчейки,"Д ДДММГГГГ"));
лСтрокаПараметров = лСтрокаПараметров + "'" + ПечЗначение + "', ";
ИначеЕсли ТипПеременной = "Строка" Тогда
ПечЗначение = СокрЛП(ЗначениеЯчейки);
лСтрокаПараметров = лСтрокаПараметров + "'" + ПодготовитьСтрокуКПечати(ПечЗначение) + "', ";
Иначе
// Для остальных типов просто приводим к строке
ПечЗначение = ЗначениеЯчейки;
лСтрокаПараметров = лСтрокаПараметров + "'" + ПодготовитьСтрокуКПечати(ПечЗначение) + "', ";
КонецЕсли;
КонецЕсли;
КонецЦикла;
лСтрокаПараметров = Лев(лСтрокаПараметров, СтрДлина(лСтрокаПараметров) - 2); //убираем последнюю запятую и пробел
CommandXLSX.CommandText = "INSERT INTO [" + ИмяЛиста+ "$] VALUES (" + лСтрокаПараметров + ")";
CommandXLSX.Execute();
КонецЦикла;
КонецПроцедуры
5. Чтение данных из Excel в ТаблицуЗначений 1С:
Функция Excel2007вТЗ позволяет нам читать данные из .xlsx файла и загружать их в ТаблицуЗначений 1С. Мы используем объект ADODB.Recordset для получения данных и ADOX.Catalog для определения имени первого листа.
Функция Excel2007вТЗ(Файл) Экспорт
Рез = ЭксельADO_Подключиться(СокрЛП(Файл), 1); // второй параметр - читать заголовки, HDR=NO
Если Рез = 0 Тогда
Возврат 0;
КонецЕсли;
Коннект = ConnectionXLSX;
axCatalog = СоздатьОбъект("ADOX.Catalog");
axCatalog.ActiveConnection = ConnectionXLSX;
Если axCatalog.Tables.Count = 0 Тогда // Проверяем наличие листов в файле
Сообщить("В документе Excel нет листов!");
ЭксельADO_Отключиться();
Возврат 0;
КонецЕсли;
ListName = axCatalog.Tables.Item(0).Name; // Получаем имя первого листа
// Если имя листа заканчивается на '$', значит, оно уже в правильном формате.
// Иначе добавляем '$' для корректной работы ADO
Если Прав(ListName, 1) <> "$" Тогда
ListName = ListName + "$";
КонецЕсли;
Коннект.CursorLocation = 3; // adUseClient. Важно для корректного определения RecordCount.
ТекстЗапроса = "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;
// Создаем колонки в ТЗ, используя заголовки из Excel
Для СчКол = 1 По КолвоКолонокExcel Цикл
ИмяКол = СокрЛП(RS.Fields.Item(СчКол - 1).Name); // Получаем имя колонки
// Дополнительная обработка имени колонки, если требуется
ПозДвоеточия = Найти(ИмяКол, ":");
Если ПозДвоеточия > 0 Тогда
ИмяКол = Лев(ИмяКол, ПозДвоеточия - 1);
КонецЕсли;
ИмяКол = СтрЗаменить(ИмяКол, "-", "");
ТЗ.НоваяКолонка(ИмяКол);
КонецЦикла;
// Переходим на первую строку данных (если HDR=NO, то это вторая строка файла)
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_Отключиться();
Для чтения данных:
// Предположим, что у нас есть путь к файлу ОткрФайл
ТЗ = Excel2007вТЗ(ОткрФайл);
Если ТЗ.КоличествоСтрок() > 0 Тогда
Сообщить("Данные успешно загружены из Excel. Количество строк: " + ТЗ.КоличествоСтрок());
Иначе
Сообщить("Не удалось загрузить данные или файл пуст.");
КонецЕсли;
Преимущества подхода:
.dll или .ocx файлы, кроме драйвера Access Database Engine.ADO часто оказывается быстрее, чем прямое использование COM-объектов Excel, особенно при работе с большими объемами данных, так как он минимизирует количество вызовов к объектам Excel.Особенности и возможные проблемы:
ПодготовитьСтрокуКПечати), чтобы избежать ошибок SQL-запросов.$ (например, [Лист1$]).Мы рассмотрели два основных подхода к сохранению данных из 1С v7 в формат .xlsx. Выбор конкретного метода зависит от ваших предпочтений, требований к производительности и возможности установки дополнительных компонентов или драйверов. Внешняя компонента v7Moxel предлагает более простое "коробочное" решение, тогда как использование ADO предоставляет большую гибкость и независимость от внешних компонентов за счет более сложной программной реализации.