Excel application свойства и методы 1с
Использование COM-соединения, выгрузка в Excel через COM-объект Excel.Application
Вообще COM-объекты используют для соединения информационной базы 1С с файлом Word, Excel, Outlook или любой другой программой, поддерживающей данный интерфейс обмена данными. В этой статье рассмотрим задачу выгрузки/загрузки данных из/в MS Excel. Чтобы это осуществить воспользуемся COM-соединением и объектом Excel.Application. Для примера возьмём задачу выгрузки/загрузки данных о номенклатуре. Пример рассмотрим ниже.
COM-соединение
Что же такое COM-соединение? Component Object Model (или COM) – это технология (фирмы Microsoft) взаимодействующих компонентов, которые одновременно могут быть использованы в разных приложениях. При этом весь функционал соответствующего компонента наследуется внутрь разрабатываемого приложения. В нашем случае COM-объект Excel.Application используется внутри кода 1С для операций с файлом книги MS Excel.
Объект Excel.Application
У объекта Excel.Application существует ряд методов, которые нам могут пригодиться для реализации нижепоставленной задачи:
- ОбъектExcel.WorkBooks.Open(ИмяФайла) – Открытие книги MS Excel
- ОбъектExcel.ActiveWorkbook.Close() – Закрытие текущей книги
- ОбъектExcel.Quit() – Закрытие COM-объекта
- ОбъектExcel.Sheets(ИмяЛиста) – Получает лист книги
- ЛистExcel.Cells(НачалоСтрока, НачалоСтолбец) – Ячейка таблицы на данном листе
- ЛистExcel.Range(Ячейка1, Ячейка2) – Выделенная область
- ЯчейкаExcel.Value – Значение ячейки таблицы
- ЯчейкаExcel.Text – Текст ячейки таблицы
Постановка задачи
Итак, предположим, что в обработке 1С у нас имеется табличная часть, состоящая из следующих колонок:
Необходимо реализовать 2 функционала (сделать на форме 2 основные кнопки):
- Выгрузка табличной части в подготовленный файл MS Excel
- Загрузка табличной части из файла.
Алгоритм выгрузки/загрузки в MS Excel
Алгоритм выгрузки следующий:
- Выгружаем табличную часть в таблицу значений
- Создаём новый COM-объект Excel.Application
- Выбираем файл, открываем файл книги MS Excel
- Переходим на заданный лист книги
- Выгружаем данные в файл
- Закрываем книгу, выходим из COM-объекта.
Алгоритм загрузки следующий:
- Создаём новый COM-объект Excel.Application
- Выбираем файл, открываем файл книги MS Excel
- Переходим на заданный лист книги
- Загружаем данные из файла в таблицу значений
- Закрываем книгу, выходим из COM-объекта
- Таблицу значений выгружаем в табличную часть.
Операция выгрузки и загрузки данных о номенклатуре происходит в заранее подготовленный шаблон MS Excel.
Пример кода 1С
Код 1С я постарался разделить на отдельные функции, чтобы, скопировав, с ними можно было работать где угодно. На форме обработки 1С были созданы 3 кнопки:
В итоге в реализации алгоритма получились следующие основные процедуры и функции 1С:
- ПолучитьExcel – Получает COM-объект MS Excel;
- ЗакрытьExcel – Закрывает использование COM-объекта MS Excel;
- ПолучитьЛистExcel – Получает лист книги Excel;
- ДобавитьТабличныйДокументВExcel – Добавляет табличный документ на лист Excel (нужно для выгрузки данных);
- ПрочитатьОбластьИзExcel – Читает область ячеек с листа Excel (нужно для загрузки данных);
- ШиринаЛистаExcel – Ширина листа Excel;
- ВысотаЛистаExcel – Высота листа Excel;
- ПреобразоватьТДвТЗ – Преобразует табличный документ в таблицу значений;
- ПреобразоватьТЗвТД – Преобразует таблицу значений в табличный документ;
Для начала приведу вспомогательную функцию для открытия/сохранения файла на диске:
Также в реализации алгоритма были задействованы следующие вспомогательные функции:
// Преобразует в массив переменную любого типа данных. // // Параметры: // Объект - Произвольный - произвольный объект данных; // Проверка - - Осуществление проверки на заполненное значение. // // Возвращаемое значение: // - Массив с теми же данными. // Функция ПреобразоватьВМассив(Объект, Проверка = Ложь) Экспорт ОбъектМассив = Новый Массив; Если НЕ Проверка ИЛИ ЗначениеЗаполнено(Объект) Тогда Если ТипЗнч(Объект) = Тип("Массив") Тогда ОбъектМассив = Объект; ИначеЕсли ТипЗнч(Объект) = Тип("СписокЗначений") Тогда ОбъектМассив = Объект.ВыгрузитьЗначения(); ИначеЕсли ТипЗнч(Объект) = Тип("Строка") Тогда ОбъектМассив = РазобратьСтрокуВМассивПоРазделителю(Объект); ИначеЕсли ТипЗнч(Объект) = Тип("Структура") Тогда Для Каждого Элемент Из Объект Цикл ОбъектМассив.Добавить(Элемент.Значение); КонецЦикла; Иначе ОбъектМассив.Добавить(Объект); КонецЕсли; КонецЕсли; Возврат ОбъектМассив; КонецФункции;
// Разбирает строку в массив подстрок по разделителю. // При этом пробелы между подстроками не учитываются. // // Параметры: // Стр - исходная строка; // СтрРазделитель - разделитель, по умолчанию ","; // ИгнорироватьПустые - игнорировать ли пустые места между разделителями. // // Возвращаемое значение: // Массив строк // Функция РазобратьСтрокуВМассивПоРазделителю(Знач Стр, СтрРазделитель = ",", ИгнорироватьПустые = Ложь) Экспорт Результат = Новый Массив; ВхождениеРазделителя = Найти(Стр, СтрРазделитель); Пока ВхождениеРазделителя <> 0 Цикл ЧастьДоРазделителя = СокрЛП(Лев(Стр, ВхождениеРазделителя - 1)); Если НЕ (ИгнорироватьПустые И ПустаяСтрока(ЧастьДоРазделителя)) Тогда Результат.Добавить(ЧастьДоРазделителя); КонецЕсли; Стр = СокрЛП(Сред(Стр, ВхождениеРазделителя + 1)); ВхождениеРазделителя = Найти(Стр, СтрРазделитель); КонецЦикла; Если НЕ (ИгнорироватьПустые И ПустаяСтрока(Стр)) Тогда Результат.Добавить(СокрЛП(Стр)); КонецЕсли; Возврат Результат; КонецФункции;
// Создаёт новую таблицу значений с заданными колонками. // // Параметры: // пКолонки - , , , , - // Набор колонок для таблицы значений. // // Возвращаемое значение: // - Созданная таблица. // Функция ПолучитьТаблицуВывода(пКолонки) Экспорт ТЗ = Новый ТаблицаЗначений; Если пКолонки <> Неопределено Тогда Если ТипЗнч(пКолонки) = Тип("Строка") Тогда пКолонки = РазобратьСтрокуВМассивПоРазделителю(пКолонки); КонецЕсли; Если ТипЗнч(пКолонки) = Тип("Структура") Тогда Для Каждого Поле Из пКолонки Цикл СтрокаТабл = ТЗ.Колонки.Добавить(Поле.Ключ, Поле.Значение); КонецЦикла; ИначеЕсли ТипЗнч(пКолонки) = Тип("СписокЗначений") Тогда Для Каждого Поле Из пКолонки Цикл Если Поле.Пометка Тогда СтрокаТабл = ТЗ.Колонки.Добавить(Поле.Значение, пКолонки.ТипЗначения, Поле.Представление); КонецЕсли; КонецЦикла; ИначеЕсли ТипЗнч(пКолонки) = Тип("ТаблицаЗначений") Тогда ЕстьНаименование = (пКолонки.Колонки.Найти("Наименование") <> Неопределено); ЕстьТипЗначения = (пКолонки.Колонки.Найти("ТипЗначения") <> Неопределено); ЕстьЗаголовок = (пКолонки.Колонки.Найти("Заголовок") <> Неопределено); ЕстьШирина = (пКолонки.Колонки.Найти("Ширина") <> Неопределено); Для Каждого Поле Из пКолонки Цикл СтрокаТабл = ТЗ.Колонки.Добавить(?(ЕстьНаименование, Поле.Наименование, ""), ?(ЕстьТипЗначения, Поле.ТипЗначения, Новый ОписаниеТипов), ?(ЕстьЗаголовок, Поле.Заголовок, ""), ?(ЕстьШирина, Поле.Ширина, 0)); КонецЦикла; Иначе Для Каждого Поле Из пКолонки Цикл СтрокаТабл = ТЗ.Колонки.Добавить(Поле); КонецЦикла; КонецЕсли; КонецЕсли; Возврат ТЗ; КонецФункции;
// Создаёт структуру колонок из таблицы значений. // // Параметры: // ТабЗначений - - Любая таблица. // // Возвращаемое значение: // - Созданная таблица. // Функция ПолучитьСтруктуруКолонокИзТаблицы(ТабЗначений) Экспорт СтруктураКолонок = Новый Структура; Для Каждого ЭлементКолонка Из ТабЗначений.Колонки Цикл СтруктураКолонок.Вставить(ЭлементКолонка.Имя, ЭлементКолонка.ТипЗначения); КонецЦикла; Возврат СтруктураКолонок; КонецФункции;
Основные функции обработки алгоритма следующие:
// Записывает данные из табличного документа в файл MS Excel. // // Параметры: // ЛистExcel - - Лист из файла MS Excel; // Таблица - - Документ, по порядку следования колонок и строк // соответствующий листу файла MS Excel; // Шапка - - Количество первых строк для шапки в файле MS Excel; // СписокСтолбцов - , - Список номеров столбцов, которые будут выгружены // в файл MS Excel; // СписокСтрокЗапрета - , - Список номеров строк, которые не должны // выгружаться в файл MS Excel. Если этот параметр не задан, то выгружаются все строки; // ПроверятьЗначения - - Определяет будут ли проверяться ячейки табличного // документа на содержание в них значения, а не текстовое представление этого значения. // По умолчанию этот параметр "Ложь". // Процедура ДобавитьТабличныйДокументВExcel(ЛистExcel, Таблица, Шапка, СписокСтолбцов, СписокСтрокЗапрета = Неопределено, ПроверятьЗначения = Ложь) Экспорт Если ТипЗнч(СписокСтолбцов) = Тип("Строка") Тогда СписокСтолбцов = РазобратьСтрокуВМассивПоРазделителю(СписокСтолбцов); КонецЕсли; Если ТипЗнч(СписокСтрокЗапрета) = Тип("Строка") Тогда СписокСтрокЗапрета = РазобратьСтрокуВМассивПоРазделителю(СписокСтрокЗапрета); КонецЕсли; ЕстьМассив = (СписокСтрокЗапрета = Неопределено); Если ЕстьМассив Тогда Попытка МассивCOM = Новый COMSafeArray("VT_VARIANT", 1, Таблица.ВысотаТаблицы - Шапка); Исключение ЕстьМассив = Ложь; КонецПопытки; КонецЕсли; Для Каждого НомерСтолбца Из СписокСтолбцов Цикл Для НомерСтроки = Шапка+1 По Таблица.ВысотаТаблицы Цикл Если СписокСтрокЗапрета = Неопределено ИЛИ (СписокСтрокЗапрета.Найти(Строка(НомерСтроки)) = Неопределено И СписокСтрокЗапрета.Найти(Число(НомерСтроки)) = Неопределено) Тогда Область = Таблица.Область(НомерСтроки, Число(НомерСтолбца)); Значение = ?(ПроверятьЗначения И Область.СодержитЗначение, Область.Значение, Область.Текст); Если ЕстьМассив Тогда МассивCOM.SetValue(0, НомерСтроки-Шапка-1, Значение); Иначе ЛистExcel.Cells(НомерСтроки, Число(НомерСтолбца)).Value = Значение; КонецЕсли; КонецЕсли; КонецЦикла; Если ЕстьМассив Тогда ЛистExcel.Range(ЛистExcel.Cells(Шапка+1, Число(НомерСтолбца)), ЛистExcel.Cells(Таблица.ВысотаТаблицы, Число(НомерСтолбца))).Value = МассивCOM; КонецЕсли; КонецЦикла; КонецПроцедуры;
// Читает область ячеек из MS Excel в табличный документ. // // Параметры: // ЛистExcel - - Лист из файла MS Excel; // ТабДок - - Исходный табличный документ, поле табличного // документа или таблица значений. Если этот параметр не задан, то создаётся // новый табличный документ; // НачалоСтрока - - Начальная строка в файле MS Excel; // НачалоСтолбец - - Начальный столбец в файле MS Excel; // КонецСтрока - - Конечная строка в файле MS Excel; // КонецСтолбец - - Конечный столбец в файле MS Excel. // // Возвращаемое значение: // - Возвращает прочитанный из области в MS Excel табличный // документ, который передавался в эту функцию параметром "ТабДок". // Функция ПрочитатьОбластьИзExcel(ЛистExcel, ТабДок = Неопределено, НачалоСтрока, НачалоСтолбец, КонецСтрока, КонецСтолбец) Экспорт Если ТабДок = Неопределено Тогда ТабДок = Новый ТабличныйДокумент; КонецЕсли; Попытка МассивCOM = Новый COMSafeArray("VT_VARIANT", КонецСтолбец - НачалоСтолбец + 1, КонецСтрока - НачалоСтрока + 1); ЕстьМассив = Истина; Исключение ЕстьМассив = Ложь; КонецПопытки; Если ЕстьМассив Тогда МассивCOM = ЛистExcel.Range(ЛистExcel.Cells(НачалоСтрока, НачалоСтолбец), ЛистExcel.Cells(КонецСтрока, КонецСтолбец)).Value; Для ИндексКолонка = НачалоСтолбец По КонецСтолбец Цикл Для ИндексСтрока = НачалоСтрока По КонецСтрока Цикл Значение = МассивCOM.GetValue(ИндексКолонка - НачалоСтолбец + 1, ИндексСтрока - НачалоСтрока + 1); ТабДок.Область(ИндексСтрока, ИндексКолонка).СодержитЗначение = Истина; ТабДок.Область(ИндексСтрока, ИндексКолонка).Значение = Значение; КонецЦикла; КонецЦикла; Иначе Для ИндексКолонка = НачалоСтолбец По КонецСтолбец Цикл Для ИндексСтрока = НачалоСтрока По КонецСтрока Цикл Значение = ЛистExcel.Cells(ИндексСтрока, ИндексКолонка).Value; ТабДок.Область(ИндексСтрока, ИндексКолонка).СодержитЗначение = Истина; ТабДок.Область(ИндексСтрока, ИндексКолонка).Значение = Значение; КонецЦикла; КонецЦикла; КонецЕсли; Возврат ТабДок; КонецФункции;
// Преобразовать таблицу значений в табличный документ. // // Параметры: // ТабЗначений - - Исходная таблица значений; // ТабДок - - Полученный табличный документ. Если параметр не задан, // то документ создаётся заново и возвращается функцией; // НачалоСтрока - - Строка начала области; // НачалоСтолбец - - Столбец начала области; // ВыводитьЗаголовки - - Определяет выводить ли имена колонок или нет. // // Возвращаемое значение: // - Полученный табличный документ (возвращает параметр "ТабДок"). // Функция ПреобразоватьТЗвТД(ТабЗначений, ТабДок = Неопределено, Знач НачалоСтрока = Неопределено, Знач НачалоСтолбец = Неопределено, ВыводитьЗаголовки = Ложь) Экспорт Если ТабДок = Неопределено Тогда ТабДок = Новый ТабличныйДокумент; КонецЕсли; // Определение габаритов таблицы НачалоСтрока = ?(НачалоСтрока = Неопределено, 1, НачалоСтрока); НачалоСтолбец = ?(НачалоСтолбец = Неопределено, 1, НачалоСтолбец); // Преобразование ИндексСтроки = НачалоСтрока; Если ВыводитьЗаголовки Тогда ИндексКолонки = НачалоСтолбец; Для Каждого Колонка Из ТабЗначений.Колонки Цикл ТабДок.Область(ИндексСтроки, ИндексКолонки).Текст = ?(ПустаяСтрока(Колонка.Заголовок), Колонка.Имя, Колонка.Заголовок); ИндексКолонки = ИндексКолонки + 1; КонецЦикла; ИндексСтроки = ИндексСтроки + 1; КонецЕсли; Для Каждого Элемент Из ТабЗначений Цикл ИндексКолонки = НачалоСтолбец; Для Каждого Колонка Из ТабЗначений.Колонки Цикл ТабДок.Область(ИндексСтроки, ИндексКолонки).СодержитЗначение = Истина; ТабДок.Область(ИндексСтроки, ИндексКолонки).ТипЗначения = Новый ОписаниеТипов(Колонка.ТипЗначения); ТабДок.Область(ИндексСтроки, ИндексКолонки).Значение = Элемент[Колонка.Имя]; ИндексКолонки = ИндексКолонки + 1; КонецЦикла; ИндексСтроки = ИндексСтроки + 1; КонецЦикла; Возврат ТабДок; КонецФункции;
В реализации алгоритма был дополнительно использован объект COMSafeArray с типом VT_VARIANT. Этот COM-объект выступает в качестве двумерного массива, в котором удобно хранить значения ячеек. Также использование данного объекта повышает быстродействие операций чтения/записи для ячеек таблицы.
Целиком получившуюся обработку 1С, а также шаблон для загрузки/выгрузки можно скачать по ссылке ниже.
Указанные здесь процедуры и функции я постарался сделать универсальными. Они могут быть применены для широкого круга прикладных задач.
Собрал различные полезности для работы с Excel из 1С. Иногда приходится форматировать документ Excel программно из 1С. Так вот, чтобы не искать постоянно на просторах интернета как сделать левое выравнивание в ячейке Excel из 1С и т.п. решил опубликовать это.
СПОСОБ ПОИСКА НАИМЕНОВАНИЙ СВОЙСТВ И МЕТОДОВ VBA EXCEL С ПОМОЩЬЮ ЗАПИСИ МАКРОСОВ
На практике часто случается, что нужно решить какую-либо задачу, иногда довольно тривиальную с точки зрения пользователя Excel (к примеру изменить фон ячейки в Excel, добавить левую границу и т.д.), а решение в сети/документации искать не хочется или не получается, тогда можно попробовать начать запись макроса, выполнить нужную последовательность действий (изменить фон, добавить границу) , после чего записать макрос и открыть код VBA для анализа кода, названий свойств и методов. Такой способ довольно часто помогает быстро решить проблему.
1. Подключение к Excel
При подключении к Excel не забываем закрывать приложение после работы дабы не наплодить кучу ненужных процессов. Они также могут появиться если во время выполнения модуля выскочит ошибка до того как система дойдет до строк, где происходит закрытие Excel.
2. Запись значения в ячейку
Количество листов книги
3. Шрифт и размер шрифта
4. Ширина колонки
Чтобы скрыть колонку, нужно установить ширину в значение 0.
Получение последней ячейки в используемом диапазоне
Например, при открытии таблицы нам нужно узнать до какой строки и/или до какого столбца присутствуют заполненные ячейки.
4. Установка фильтра
5. Горизонтальное выравнивание ячейки
Режим выравнивания
Константа в Excel
Значение в ISBL
По центру выделения
По правому краю
6. Вертикальное выравнивание ячейки
Режим выравнивания
Константа в Excel
Значение в ISBL
По нижнему краю
По верхнему краю
7. Установка свойства ячейки "переносить по словам"
8. Отключение/включение режима показа предупреждений
9. Формулы
Для формул с русскими названиями нужно использовать не Formula, а FormulaLocal. Таким образом правильными вариантами написания формул будут:
10. Таблица цветов с индексами
11. Установка границы ячейки и тип линии границы
Значения константы XlBordersIndex
Значение в Excel
Значение в ISBL
В виде тире и точек
В виде тире и двойных точек
В виде двойной линии
В виде наклонной пунктирной
Свойство LineStyle (тип линии) может принимать следующие значения
Расположение линии
Значение в Excel
Значение в ISBL
Линия по диагонали сверху – вниз
Линия по диагонали снизу – вверх
Линия, обрамляющая диапазон слева
Линия, обрамляющая диапазон сверху
Линия, обрамляющая диапазон снизу
Линия, обрамляющая диапазон справа
Все вертикальные линии внутри диапазона
Все горизонтальные линии внутри диапазона
Значения константы XlBordersIndex
Расположение линии | Значение в Excel | Константа в ISBL |
---|---|---|
Линия по диагонали сверху вниз | xlDiagonalDown | 5 |
Линия по диагонали снизу – вверх | xlDiagonalUp | 6 |
Линия, обрамляющая диапазон слева | xlEdgeLeft | 7 |
Линия, обрамляющая диапазон сверху | xlEdgeTop | 8 |
Линия, обрамляющая диапазон снизу | xlEdgeBottom | 9 |
Линия, обрамляющая диапазон справа | xlEdgeRight | 10 |
Все вертикальные линии внутри диапазона | xlInsideVertical | 11 |
Все горизонтальные линии внутри диапазона | xlInsideHorizontal | 12 |
Свойство LineStyle (тип линии) может принимать следующие значения:
Тип линии | Значение в Excel | Константа в ISBL |
---|---|---|
Нет линии | xlLineStyleNone | 0 |
Непрерывная | xlContinuous | 1 |
В виде тире и точек | xlDashDot | 4 |
В виде тире и двойных точек | xlDashDotDot | 5 |
В виде точек | xlDot | 8 |
В виде двойной линии | xlDouble | 9 |
В виде наклонной пунктирной | xlSlantDashDot | 13 |
Получение порядкового номера колонки Excel по буквенному обозначению имени
Однажды столкнулся с тем, что нужно было получить буквенное обозначение колонки Excel по её порядковому номеру. Например, для колонки номер 3 буквенное обозначение равно "C". Так вот ниже приведены две замечательные функции, которые вполне справляются с решением данной проблемы.
Наоборот, получение буквенного обозначения имени колонки по номеру колонки
После манипуляции с файлом необходимо его закрыть. В противном случае он останется захваченным в системе, и мы не сможем его использовать в других приложениях.
Клиент-Серверный вариант в управляемых формах.
1. Считываем данные из файла в массив из структур и затем этот массив передаем на сервер для дальнейших манипуляций с данными.
2-ой способ - это передать файл Excel на сервер через хранилище данных и далее работать с ним уже на сервере.
Очень часто приходится работать с очень большими файлами Excel, и его обработка путем перебора строк занимает огромное количество времени.
В таких случаех удобно в одно действие загрузить всю таблицу в массив и в потом работать уже непосредственно с массивом.
На выходе получаем двумерный массив, который содержит все данные указанного листа Excel
Полезные функции при работе с Excel:
Устанавливает видимость Excel при работе |
Добавление новой книги в файл Excel |
Сохранение книги Excel |
Добавление нового листа к книге |
Переименование листа |
Изменение маштаба листа |
Изменение ориентации листа |
Отступы листа |
Обращение к ячейки чтение/запись |
Обращение к области ячеек |
Очень часто при чтении или записи значений в Excel ставятся лишние пробелы в числе, например, вместо 1502 он читает как 1 502 и в дальнейшем это значение не приводится к числу. Эту проблему можно решить заменой. |
Объединение ячеек |
Работа с именованными ячейками в Excel |
Удаление ячейки |
Удаление области ячеек |
Обращение к строке |
Изменение ширины колонки |
Обращение к колонке |
Удаление Строки |
Фон ячейки / области / Строки / |
Функция переводит цвет из формата RGB в формат Excel |
Управление шрифтом в ячейки/строке/области |
Р азрешает переносить по словам в ячейке |
Управление рамкой ячейки |
Устанавливаем формат ячейки |
Формула в ячейки |
Формула в ячейки |
Формула в ячейки |
Формула в ячейки |
Функция для получения ширины колонки Excel Спасибо пользователю goodwill |
Разрешить перенос слов в ячейке Спасибо пользователю roofless |
Группировки данных на листе Спасибо пользователю dr-wit, ignor |
При работе с Excel мы оперируем столбцами как числом (Например, 1 столбец), а у Excel адресация столбцов производится с помощью символов. И когда нам нужно отредактировать формулу, то нам нужно номер столбца преобразовать в символ. В таких случаях вам пригодится эта функция.
2. Работа с Excel через ТабличныйДокумент 1С
С помощью данного метода можно и загружать из Excel и выгружать в Excel. Но на мой взгляд этот метод идепально подходит когда вам необходимо посто сохранить информацию в Excel без дальнейшей манипуляции.
Итак, приступим: загрузка из Excel:
1. Загружаем файл Excel в табличный документ
2. Производим манипуляции уже с ТабличнымДокументом
Давайте теперь разберем сохранение в Excel с помощью данного метода:
Тут все очень просто сначала мы формируем обычный Табличный документ и затем записываем его в Excel
2. Работа с Excel ADODB
Выражаю особую благодарность коллеги Fragster за хороший комментарий
Данный метод позволяет работать с Excel через ODBC и имеет ряд преимуществ:
- Не требует установки самой Excel, необходима лишь установить ODBC. Но как правило он уже установлен. Это особенность позволяет работать на стороне сервера без дополнительных установок Excel.
- Позволяет работать с таблицой Excel как с БД и строить к ней запросы на T-SQL. Таким образом мы можем делать отборы еще на этапе чтения данных и другие преимущества что дает Т-SQL. Что на мой взгляд огромный плюс.
Подключение к ADO
Отключение от ADO
Выполнение запроса
Пример запроса:
Запись в Excel тоже производится в виде запроса:
Хочу отметить что наименование полей производится по первой строке в таблице
ADODB предоставляет ряд объектов, с которыми мы работаем
ADODB.Recordset
В файле продемонстрированны оба варианта работы с запросами.
На этом пока все. По возможности буду дополнять статью :)
В архиве находится обработка, которая демонстрирует все описанные и другие возможности при работе с Excel.
Как осуществить чтение/запись данных из/в Excel на языке 1с (используя COM-объект)
Здесь можно скачать шаблонную обработку, разработанную в среде «1С:Предприятие v8″ для работы с файлами Excel: [download В обработке осуществляются все основные действия с файлом Excel. Даны подробные комментарии. Можно использовать в качестве шаблона для разработки собственных выгрузок/загрузок в/из Excel.
Основные методы, принципы и хитрости, используемые при работе с EXCEL через COM-объект
Чтение данных из Excel
Доступ из 1С к Excel производится посредством OLE. Создание COM-объекта:
Теперь используя переменную Эксель можно управлять приложением Excel.
- Внимание! Microsoft Excel должен быть установлен на компьютере!
Следующая команда откроет книгу:
Перед тем, как начать считывание данных, укажем лист книги, с которого будем считывать данные:
Нумерация листов книги начинается с 1. Общее количество листов можно получить, используя следующую команду:
Лист можно выбрать по имени листа в книге:
Имя листа в книге можно получить по номеру:
Точно так же можно задать имя листа:
Итак, мы открыли книгу и выбрали лист, теперь посмотрим, сколько строк и колонок на выбранном листе:
Получим значения ячейки листа в строке НомерСтроки и в колонке НомерКолонки:
Ниже приведен отрывок кода, запустив который мы прочитаем все данные с первой страницы:
Где ПутьКФайлу — полный путь к файлу книги Excel (включая имя).
После выполнения действий необходимо закрыть книгу:
Выгрузка данных в Excel
Для вывода (выгрузки) данных в Excel необходимо либо открыть существующую книгу, либо создать новую, и выбрать рабочий лист для вывода данных.
Для создания новой книги можно использовать следующий код:
При создании книги автоматически создаются листы (по умолчанию 3). Нам остается только выбрать нужный:
Или добавить в книгу новый лист:
Добавим в ячейку на листе значение:
Где ПутьКФайлу — полный путь к файлу книги Excel (включая имя).
После выполнения действий необходимо закрыть книгу:
Как программно сохранить файл Excel в формате 2003 года
Синтаксис команды «SaveAs» во втором параметре разрешает указать формат сохраняемого файла.
Числовое значение фрмата файла Excel 2003: FileFormatNum = -4143
Т.е. команду сохранения для этого можно написатьтак:
Часто используемые методы Excel
Эксель.Visible = Видимость; | 0 — Excel не виден, 1 — виден. |
Книга = Эксель.WorkBooks.Add(); | Создание новой книги (файла) Excel. |
Книга = Эксель.WorkBooks.Add(ИмяФайлаШаблона); | Создание новой книги (файла) Excel по шаблону «ИмяФайлаШаблона» |
Книга.SaveAs(ИмяФайла); | Сохранение книги Excel. |
Лист = Книга.WorkSheets.Add(); | Добавление нового листа в книгу. |
Книга = Эксель.WorkBooks.Open(ИмяФайла); | Открытие существующей книги (файла) Excel. |
Лист = Книга.WorkSheets(НомерЛиста); | Установка листа в качестве рабочего с номером НомерЛиста. |
Лист.Name = ИмяЛиста; | Задание рабочему листу имени ИмяЛиста |
Лист.PageSetup.Zoom = Масштаб; | Задание параметра страницы «Масштаб» (от 10 до 400). |
Лист.PageSetup.Orientation = Ориентация; | Ориентация: 1 — книжная, 2 — альбомная. |
Лист.PageSetup.LeftMargin = Эксель.CentimetersToPoints(Сантиметры); | Задание левой границы (в сантиметрах). |
Лист.PageSetup.TopMargin = Эксель.CentimetersToPoints(Сантиметры); | Задание верхней границы (в сантиметрах). |
Лист.PageSetup.RightMargin = Эксель.CentimetersToPoints(Сантиметры); | Задание правой границы (в сантиметрах). |
Лист.PageSetup.BottomMargin = Эксель.CentimetersToPoints(Сантиметры); | Задание нижней границы (в сантиметрах). |
Лист.Columns(НомерКолонки).ColumnWidth = Ширина; | Задание ширины колонке. |
Лист.Cells(НомерСтроки,НомерКолонки).ColumnWidth = 0; | Скрыть всю колонку, в которой расположена ячейка |
Лист.Cells(НомерСтроки, НомерКолонки).Value = Значение; | Ввод данных в ячейку. |
Лист.Cells(НомерСтроки,НомерКолонки).Font.Name = ИмяШрифта; | Установка шрифта в ячейке. |
Лист.Cells(НомерСтроки,НомерКолонки).Font.Color = ЦветШрифта; | Установка цвета шрифта в ячейке. Тип переменной ЦветШрифта — число десятичное. |
Лист.Cells(НомерСтроки,НомерКолонки).Borders.Color = ЦветРамки; | Установка цвета рамки в ячейке. |
Лист.Cells(НомерСтроки,НомерКолонки).Interior.Color = ЦветФона; | Установка цвета фона в ячейке. |
Лист.Cells(НомерСтроки,НомерКолонки).Font.Size = РазмерШрифта; | Установка размера шрифта в ячейке. |
Лист.Cells(НомерСтроки,НомерКолонки).Font.Bold = Жирный; | 1 — жирный шрифт, 0 — нормальный. |
Лист.Cells(НомерСтроки,НомерКолонки).Font.Italic = Курсив; | 1 — наклонный шрифт, 0 — нормальный. |
Лист.Cells(НомерСтроки,НомерКолонки).Font.Underline = Подчеркнутый; | 2 — подчеркнутый, 1 — нет. |
Лист.Cells(НомерСтроки, НомерКолонки).NumberFormat = Формат; | Установка формата данных ячейки. |
Лист.Cells(НомерСтроки,НомерКолонки).Borders.Linestyle = ТипЛинии; | Установка рамок ячейки. 1 — тонкая сплошная. |
Лист.Cells(НомерСтроки,НомерКолонки).WrapText = Истина; | Осуществлять перенос по словам в указанной ячейке |
Лист.Protect(); | Установка защиты на лист |
Лист.UnProtect(); | Снятие защиты с листа |
Лист.Cells(Строка, Столбец).Locked=0; | Ячейка будет доступной (и после установки защиты на лист) |
ПолучитьCOMОбъект(, ); | Основное применение функции ПолучитьCOMОбъект — это получение COM-объекта, соответствующего файлу. |
Хитрости Excel
Как выборочно разрешить / запретить редактирование ячеек листа
Как запретить появление на экране всяких вопросов от Excel
Excel, чтоб вопрос не задавал:
Как добавить лист Excel в конец списка листов книги или после конкретного листа (а не в начало книги)
Метод работает для платформ 1С v8.
Как программно скрыть колонку файла Excel
Как программно назначить ячейке файла Excel перенос по словам
Как обработать файл xls, если Excel не установлен на компьютере
Для этого можно использовать метод
Код для 7.7, решающий такую задачу, будет выглядеть примерно так:
Как указать цвет шрифта в ячейке, цвет рамки, цвет фона
Организация автоматической обработки файлов xls из выбранной папки
Создание кнопки в Excel в 7.7
Процедура открывает Эксель, втавляет на первый лист кнопку «Очистить» и назначает ей макрос,
устанавливающий автофильтр на колонку Е по не нулевым значениям. Текст макроса любой,
главное разделять Симв(13) строки
текст макроса пишется в переменную st
Как подключиться к запущенному Excel-евскому файлу в реальном времени, изменить его и даже не сохранять, а просто переключить окно на 1С и сразу же выгружать данные в табличную часть, лишь переключив окна
При этом первый параметр нужно оставить пустым. В этом случае при этом подцепится тот файл экселя, который был открыт последним, даже если порядок переключения окон был таким:
Excel1, Excel2, IE, Проводник, 1С (т.е. что-то и было открытым между 1с и экселевским файлом) — все равно откроется Excel2, потому что он был активен последним.
Описание команды ПолучитьCOMОбъект
Глобальный контекст
ПолучитьCOMОбъект (GetCOMObject)
Синтаксис:
ПолучитьCOMОбъект(, )
Параметры:
(необязательный)
Тип: Строка. Имя файла, включающее полный путь.
(необязательный)
Тип: Строка. Имя класса COM, экземпляр которого должен быть создан или получен. Если расширение имени файла, указанное в первом параметре полностью идентифицирует класс объекта, то параметр может быть опущен.
Возвращаемое значение:
Тип: COMОбъект.
Описание:
Основное применение функции ПолучитьCOMОбъект — это получение COM-объекта, соответствующего файлу. Для этого следует в качестве первого параметра функции задать имя файла, который будет определять COM-объект. Например, фрагмент кода
создает объект Excel.Application и открывает с его помощью файл документа «C:DATADATA.XLS». Если указанный файл во время выполнения данного фрагмента уже открыт с помощью MS Excel, то будет получена ссылка на уже существующий объект.
Для файлов, указываемых в качестве параметра данной функции, должно быть установлено соответствие расширения имени файла и класса COM.
Если в качестве имени файла указана пустая строка, то будет создан новый экземпляр объекта. В этом случае необходимо указать имя класса COM.
Например, фрагмент кода
создает новый документ Excel. В дальнейшем этот документ может быть программно заполнен и сохранен в файл.
Если первый параметр функции пропущен, то будет произведена попытка получить активный объект указанного типа. Если активного объекта указанного типа в настоящий момент не существует, то будет вызвано исключение. Например, в результате выполнения оператора
Переменная П получит значение типа COMОбъект, соответствующее активному приложению MS Excel, если таковое имелось, или будет вызвано исключение, если активных экземпляров MS Excel не было.
Пример:
Нередко многие сталкиваются с задачей перенесения большого количества данных и выгрузкой в Excel из 1С. В данной статье мы не будем рассматривать создание обработки для этих целей, а просто разберем некоторые методы для COM объектов, которые можно использовать при программном создании файла Excel.
Создавать объект данного типа следует при помощи конструкции «Попытка-Исключение» в Excel.Applications. Данная конструкция поможет предотвратить лишние ошибки, а также будет выводить на экран описание, если ошибка, все же, случится.
Использование конструкции Попытка-Исключение
Когда файл типа Excel создан можно перейти непосредственно к работе с ним.
Для более удобной работы можно изменить видимость (0 - Excel не виден, 1 – виден)
Видимость файла типа Excel
1. Листы книги Microsoft Excel
Данное свойство позволяет изменять способ работы Excel (в фоновом режиме или в обычном).
После установки видимости производим создание книги Excel.
Создание книги Excel
Данный метод добавит в файл новую книгу в Excel и сохранит ее в переменную.
Далее можно обратиться к листу книги Microsoft Excel.
Добавление в файл новой книги в Excel
Или добавить новый.
Добавление листа книги Microsoft Excel
У листа книги Microsoft Excel есть свойства, которые можно изменять. Вот некоторые из них:
· Name – Имя листа;
· PageSetup.Zoom– Масштаб (принимает значения от 10 до 400);
· PageSetup.Orientation – ориентация листа (1 - книжная, 2 – альбомная);
· PageSetup.LeftMargin – управляет отступом слева;
· PageSetup.TopMargin - управляет отступом сверху;
· PageSetup.RightMargin- управляет отступом справа;
· PageSetup.BottomMargin- управляет отступом снизу.
Для получения значения названия ячейки листа или записи в ячейку какого-либо значения нужно обратиться к ней по номеру:
Процедура получения значения названия ячейки листа
Таким же образом можно обратиться и к любому другому полю ячейки Excel:
Обращение к полю ячейки Excel
2. Поля ячеек Excel
Ниже приведен основной список полей ячеек Excel с указанием назначений.
· NumberFormat – формат ячейки ("@" – текстовый, "0.00" – числовой);
· Value – Содержимое ячейки (то что увидит пользователь, открыв файл);
· Borders.Linestyle – Рамка вокруг ячейки;
· Height – Высота ячейки в пикселях;
· WrapText – перенос текста по словам (1 – переносить, 0 – не переносить);
· VerticalAlignment – Вертикальное положение текста в ячейки;
· HorizontalAlignment – Горизонтальное положение текста в ячейки;
· FormulaLocal – формула ячейки;
· Font.Name – наименование шрифта текста;
· Font.Size – Размер текста;
· Font.Bold – жирность текста (1 – жирный, 0 – нормальный);
· Font.Italic – курсив текста (1 – курсив, 0 – нормальный);
· Font.Underline – подчеркивание текста (1 – подчеркнутый, 0 – нормальный);
· Delete () – удаляет ячейку;
· Interior.Color – отвечает за фон ячейки, чтобы Excel «понял» цвет его необходимо передать в формате Excel. Для этого можно использовать следующую функцию.
Форматирование цвета в Excel
Форматирование цвета в Excel принимает на вход параметры R, G, B – номера цвета в стандартном формате и возвращает номер цвета в формате, подходящем для Excel.
Аналогичным образом можно обращаться к колонкам и строкам:
Обращение к строке
Методы для COM объекта – обращение к колонке
3. Объекты Range
Следует заметить, что свойства Cells, Columns и Rows возвращают не наборы объектов Cell, Column и Row, а наборы объектов Range. На использовании объекта Range построена в Excel почти вся работа с ячейками и их значениями.
К наборам объектов Range так же можно обращаться при помощи ячеек:
Обращение к объектам Range
А также назначать областям имя:
Назначение имен областям объектов Range
Обращение к области можно использовать, например, для объединения группы ячеек:
Объединение ячеек объектов Range
Колонки, строки и области в Excel имеют такие же свойства, как и ячейки, поэтому, например, можно без проблем изменить оформление сразу всей строки.
По окончании редактирования файла его необходимо сохранить при помощи специального метода.
Сохранение файла типа Excel
Где имя файла - это полный путь к файлу типа Excel.
После сохранения необходимо завершить транзакцию.
Завершение транзакции с файлом типа Excel
Специалист компании «Кодерлайн»
Вас могут заинтересовать следующие статьи:
Читайте также: