MS Excel давно стал стандартом для работы с электронными таблицами. В статье показаны способы программной загрузки и выгрузки из 1С в Excel файла.
Есть несколько способов программной работы с файлами 1С Excel. У каждого из них есть свои достоинства и недостатки.
Обмен через табличный документ
Это простой способ. Его суть заключается в том, что у объекта TabularDocument есть методы:
- Напишите (FileName>, TableFileType>), чтобы загрузить данные в файл;
- Прочтите (FileName>, ValuesReader>), чтобы загрузить данные из файла.
Внимание!
Метод Write () доступен как на клиенте, так и на сервере. Метод Read () доступен только на стороне сервера. Запомни это
при планировании взаимодействия клиент-сервер.
Рассмотрим пример сохранения электронной таблицы в файл. Вы должны создать и заполнить объект TabularDocument любым способом, а загрузка в файл выполняется одной строкой:
TabDoc. Запись (FilePath, TabularDocumentFileType. XLSX);
Здесь TabDok — это сгенерированный документ электронной таблицы, Путь к файлу — это имя файла для загрузки, Тип файла табличного документа. XLSX — это формат создаваемого файла. Поддерживаются следующие форматы Excel:
- XLS95 — формат Excel 95;
- XLS97 — формат Excel 97;
- XLSX — формат Excel 2007.
Загрузить из файла тоже довольно просто:
TabDoc = Новый табличный документ ;
TabDoc. Читать (путь к файлу, как читать значения табличного документа. Значение);
Здесь FilePath — это путь к загруженному файлу Excel. Как читать значения листа документа Значение определяет, как следует интерпретировать данные, считанные из исходного документа. Возможные варианты:
Обмен через OLE
Обмен с помощью технологии автоматизации OLE, пожалуй, самый распространенный способ программной работы с файлами Excel. Он позволяет использовать все функции, предоставляемые Excel, но работает медленнее, чем другие методы. Для обмена через OLE необходимо установить MS Excel:
- На компьютере конечного пользователя, если обмен происходит на стороне клиента;
- На компьютере с сервером 1С: Предприятия, если обмен происходит на стороне сервера.
Пример разгрузки:
// Создаем COM-объект
Excel = Новый COMObject («Excel.Приложение»);
// Отключаем отображение предупреждений и вопросов
Excel. DisplayAlerts = False;
// Создаем новую книгу
Книга = Excel. Трудовые книжки. Добавить ();
// Размещение на первом листе
Лист = Книга. Рабочие листы (1);
// Записываем значение в ячейку
Простыня. Ячейки (RowNumber, ColumnNumber). Значение = значение ячейки ;
// Сохраняем файл
Книга . Сохранить как (имя файла);
// Закройте Excel и освободите память
Excel. Выходить ();
Excel = 0 ;
Примеры чтения:
// Создаем COM-объект
Excel = Новый COMObject («Excel.Приложение»);
// Открыть книгу
Книга = Excel. Трудовые книжки. Открыть (путь к файлу);
// Размещение на желаемом листе
Лист = Книга. Рабочие листы (1);
// Считываем значение ячейки, обычно это цикл для прохождения ячеек
Значение ячейки = Лист. Ячейки (RowNumber, ColumnNumber). Ценить ;
// Закрой книгу
Книга . Закрыть (0);
// Закройте Excel и освободите память
Excel. Выходить ();
Excel = 0 ;
// Открыть книгу
Книга = GetCOMObject (Путь к файлу);
// Размещение на желаемом листе
Лист = Книга. Рабочие листы (1);
// Считываем значение ячейки, обычно это цикл для прохождения ячеек
Значение ячейки = Лист. Ячейки (RowNumber, ColumnNumber). Ценить ;
// Закрой книгу
Книга . Заявка. Выходить ();
вы можете использовать следующие методы для обхода всех заполненных строк на листе Excel:
// — ОПЦИЯ 1 ——
Количество строк = Лист. Ячейки (1, 1). SpecialCells (11). Рига ;
Для номера строки = 1 для количества циклических строк
Значение ячейки = Лист. Ячейки (RowNumber, ColumnNumber). Ценить ;
Конец цикла;
// — ВАРИАНТ 2 ——
NumeroLinea = 0 ;
Хотя цикл истины
RowNumber = RowNumber + 1 ;
Значение ячейки = Лист. Ячейки (RowNumber, ColumnNumber). Ценить ;
Если НЕ ValueFilled (CellValue), то
Стоп;
Конец, если;
Конец цикла;
Вместо того, чтобы последовательно просматривать все строки на листе, вы можете выгрузить все данные в массив и работать с ними. Этот подход будет быстрее при чтении больших объемов данных:
Всего столбцов = лист. Ячейки (1, 1). SpecialCells (11). Столбец ;
TotalLines = Лист. Ячейки (1, 1). SpecialCells (11). Рига ;
Площадь = Лист. Интервал (Лист. Ячейки (1, 1), Лист. Ячейки (TotalLines, TotalColumns));
Данные = объем. Ценить. Скачать ();
В следующей таблице показаны наиболее популярные свойства и методы работы с Excel через OLE:
| Действие | Код | Комментарий |
| Настройка видимости окна приложения | Excel. Видимый = Ложь; | |
| Настройка режима отображения предупреждений (отображение / не отображение) | Excel. DisplayAlerts = False; | |
| Закрытие приложения | Excel. Выходить (); | |
| Создать новую книгу | Книга = Excel. Трудовые книжки. Добавить (); | |
| Открытие существующей книги | Книга = Excel. Трудовые книжки. Открыть (имя файла); | |
| Сохранить книгу | Книга . Сохранить как (имя файла); | |
| Закрой книгу | Книга . Закрыть (0); | |
| Установка текущего листа | Лист = Книга. Рабочие листы (номер листа); | |
| Настройка имени | Простыня. Name = Имя ; | |
| Настройка защиты | Простыня. Защищать (); | |
| Снятие защиты | Простыня. Не защищен (); | |
| Установка ориентации страницы | Простыня. Настройка страницы. Ориентация = 2 ; | 1 — портрет, 2 — пейзаж |
| Настройка левого края | Простыня. Настройка страницы. Левое поле = Excel. CentimetersToPoints (сантиметры); | |
| Установка верхнего предела | Простыня. Настройка страницы. Верхнее поле = Excel. CentimetersToPoints (сантиметры); | |
| Установите правый край | Простыня. Настройка страницы. Правое поле = Excel. CentimetersToPoints (сантиметры); | |
| Настройка нижней границы | Простыня. Настройка страницы. BottomMargin = Excel. CentimetersToPoints (сантиметры); | |
| Установка ширины столбца | Простыня. Колонны. СтолбецW> = Ширина ; | |
| Удалить строку | Простыня. Полосы. Удалить (); | |
| Устранение колонны | Простыня. Колонны. Удалить (); | |
| Удалить ячейку | Простыня. Ячейки (RowNumber, ColumnNumber). Удалить (); | |
| Установка значения | Простыня. Ячейки (RowNumber, ColumnNumber). Значение = Значение ; | |
| Союз ячеек | Простыня. Диапазон (Лист. Ячейки (RowNumber, ColumnNumber), Sheet.Cells (RowNumber1, ColumnNumber1)). Слияние (); | |
| Установка шрифта | Простыня. Ячейки (RowNumber, ColumnNumber). Шрифт. Имя = Имя персонажа ; | |
| Установите размер шрифта | Простыня. Ячейки (RowNumber, ColumnNumber). Шрифт. Размер = Размер шрифта ; | |
| Установка жирного шрифта | Простыня. Ячейки (RowNumber, ColumnNumber). Шрифт. Жирный = 1 ; | 1 — жирный, 0 — обычный |
| Курсив | Простыня. Ячейки (RowNumber, ColumnNumber). Курсивный шрифт = 1 ; | 1 — курсив, 0 — обычный |
| Установите подчеркнутый символ | Простыня. Ячейки (RowNumber, ColumnNumber). Шрифт. Подчеркивание = 2 ; | 2 — подчеркнутый, 1 — нет |
Чтобы узнать, какое свойство нужно изменить или какой метод вызвать, вы можете использовать макросы Excel. Если вы записываете макрос с необходимыми действиями, вы можете посмотреть программный код VBA записанного макроса.
Использование COMSafeArray
При загрузке больших объемов данных из 1С в Excel для ускорения можно использовать объект COMSafeArray. Как определено в помощнике по синтаксису, COMSafeArray представляет собой оболочку объекта в многомерном массиве COM SafeArray. Позволяет создавать и использовать SafeArray для обмена данными между COM-объектами. Проще говоря, это массив значений, которые можно использовать для обмена между приложениями с использованием технологии OLE.
// Создаем COMSafeArray
ArrayCom = Новый COMSafeArray («VT_Variant», TotalColumns, TotalStrings);
// Заполняем COMSafeArray
Для строк = 0 Для всего строк — 1 цикл
Для Num = 0 Для всего столбцов — 1 цикл
ArrayCom. SetValue (Количество, Стр, Значение);
Конец цикла;
Конец цикла;
// Присваиваем значения из COMSafeArray области листа Excel
Простыня. Диапазон (Лист. Ячейки (1, 1), Лист. Ячейки (TotalRows, TotalColumns)). Значение = ArrayCom ;
Обмен через ADO
Файл Excel, передаваемый через ADO, представляет собой базу данных, доступ к которой можно получить с помощью запросов SQL. Устанавливать MS Excel не обязательно, но должен присутствовать драйвер ODBC, который будет использоваться для доступа. Используемый драйвер ODBC определяется путем указания строки подключения к файлу. Обычно требуемый драйвер уже установлен на компьютере.
Обмен через ADO происходит значительно быстрее, чем обмен через OLE, но при загрузке вы не можете использовать функции Excel для разработки ячеек, страниц разметки, установки формул и т.д.
Пример разгрузки:
// Создаем COM-объект для подключения
Подключение = Новый COMObject («ADODB.Connection»);
// Устанавливаем строку подключения
Связь. ConnectionString = «
Prov >
Источник данных = «+ Имя файла + «;
Расширенные свойства = «» Excel 12.0 XML; HDR = S»»;» ;
Связь. Открытым (); // Открываем соединение
// Создаем COM-объект для команды
Команда = Новый COMObject («ADODB.Command»);
Команда. ActiveConnection = Подключение ;
// Назначаем текст команды для создания таблицы
Командование. CommandText = «СОЗДАТЬ ТАБЛИЦУ [Sheet1] (Column1 char (255), Column2 date, Column3 int, Column4 float)» ;
Команда. Выполнить (); // Запускаем команду
// Назначаем текст команды для добавления строки таблицы
Командование. CommandText = «INSERT INTO [Sheet1] (Column1, Column2, Column3, Column4) values (‘abvgdeyo’, ‘8/11/2017’, ‘12345’, ‘12345.6789’)» ;
Command.Execute (); // Запускаем команду
// Удаляем команду и закрываем соединение
Команда = Не определено;
Связь. Закрывать ();
Соединение = Не определено ;
Для создания нового листа и формирования его структуры можно использовать объекты ADOX.Catalog и ADOX.Table. В этом случае код примет вид:
// Создаем COM-объект для работы с книгой
Книга = Новый COMObject («ADOX.Catalog»);
Книга . ActiveConnection = Подключение ;
// Создаем COM-объект для работы со структурой данных на листе
Таблица = Новый COMObject («ADOX.Table»);
Стол . Имя = «Лист1» ;
Стол . Колонны. Добавить («Столбец 1», 202);
Стол . Колонны. Добавить («Столбец2», 7);
Стол . Колонны. Добавить («Столбец 3», 5);
Стол . Колонны. Добавить («Столбец4», 5);
// Создаем лист со структурой, описанной в книге
Книга . Таблицы. Добавить (таблица);
Таблица = Не определено;
Книга = Бессрочно;
В приведенном выше примере в методе
Стол . Колонны. Добавить («Столбец 1», 202);
второй параметр указывает тип столбца. Параметр необязательный, вот некоторые значения типа столбца:
- 5 — adDouble;
- 6 — адвалута;
- 7 — adDate;
- 11 — adBooleano;
- 202 — adVarWChar;
- 203 — adLongVarWChar.
Пример чтения:
// Создаем COM-объект для подключения
Подключение = Новый COMObject («ADODB.Connection»);
// Устанавливаем строку подключения
Связь. ConnectionString = «
Prov >
Источник данных = «+ Имя файла + «;
Расширенные свойства = «» Excel 12.0 XML; HDR = S»»;» ;
Связь. Открытым (); // Открываем соединение
// Создаем COM-объект, чтобы получить выбор
Выделение = Новый COMObject («ADODB.Recordset»);
TestoRichiesta = «ВЫБРАТЬ * ИЗ [Лист1$]» ;
// Выполняем запрос
Отбор проб. Открыть (QueryText, Connection);
// Игнорировать результат выделения
Еще НЕ выборка. EOF () Цикл
Значение Column1 = Образец. Поля. Элемент («Столбец1»). Ценить; // Ссылка по имени столбца
Столбец2-Значение = Образец. Поля. Статья (0). Ценить; // Ссылаясь на индекс столбца
Отбор проб. MoveNext ();
Конец цикла;
Отбор проб. Закрывать ();
Образец = Не определено;
Связь. Закрывать ();
Соединение = Не определено;
В строке подключения параметр HDR определяет, как будет восприниматься первая строка на листе. Возможные варианты:
- S — первая строка интерпретируется как названия столбцов. Вы можете получить доступ к значениям по имени столбца и индексу.
- НЕТ — первая строка обрабатывается как данные. Доступ к значениям можно получить только по индексу столбца.
В приведенных примерах обсуждались только некоторые объекты ADO. Объектная модель ADO состоит из следующих объектов:
Выгрузка без программирования
Для сохранения данных из 1С в Excel не всегда целесообразно прибегать к программированию. Если в корпоративном режиме пользователь может просматривать данные, необходимые для загрузки, их можно сохранить в Excel без программирования.
Чтобы сохранить электронную таблицу (например, результат отчета), вы можете вызвать команду «Сохранить» или «Сохранить как…» из главного меню.
В открывшемся окне нужно выбрать каталог, имя и формат сохраняемого файла.
Чтобы сохранить данные из динамических списков (например, список элементов), вам необходимо:
- Просматривайте данные в электронной таблице с помощью команды «Больше просмотра списка…;
- Сохраните таблицу в нужном формате.
