Excel выполнение фонового запроса

Обновлено: 07.07.2024

Представим себе ситуацию, при которой Вам надо совместить данные из файла excell и действующей базы на 1С. Впринципе можно выгрузить данные из 1С в тот же Эксель и "ПроВПРить". Но во-первых, нам могут понадобиться более продвинутые средства получения данных, чем ВПР, во-вторых, для сложных конструкций это долго, а в третьих, для порядочного 1С-ника это ФИ (не зря же мы изучали язык запросов! да и конструктор запросов- вещь крутая). Но сосредоточимся на главном - это получение данных из файла Эксель(или другого табличного файла, можно и какого-нибудь csv) и использование их в запросе.

Я использую следующую методику:

1) Сначала читаем этот файл и загоняем данные в таблицу значений (ТЗ).

2) Передаем полученную ТЗ как параметр запроса и создаем на основе нее временную таблицу (ВТ).

3) Используем полученную ВТ для дальнейших манипуляций с запросами (через 2-ой пакет запросов).

Для удобства я доработал Консоль запросов (для управляемых форм), чтобы она выполняла первые 2 пункта на автомате, читая файл и дописывая запрос до нужного нам вида. Первая строка таблицы используется как заголовки(Она обязательно должна быть!). Нам останется лишь в конструкторе запросов добавить второй пакет и в нем уже получить нужные нам выборки. В параметрах Вы можете назначить типы данных. Так же для функционала добавил исполняемый код для строк выборки. Все параметры и код сохраняются в файл вместе с запросом.

Обработку прилагаю, можете посмотреть, что да как сделано, и использовать вместо обычной консоли запросов. Или не использовать. Если обработка окажется полезной, доработаю её под другие типы файлов(csv например).

Чтобы добавить несколько таблиц, надо переименовать параметр с Таб например не Таб2 в параметрах и в самом запросе в первом пакете. так же переименовать временную таблицу, тогда после прочтения другого файла, будет добавлен новый пакет запросов и новый параметр.

Поработал с обработкой и допилил её до вполне юзабельного на мой взгляд вида:

On-Line Analytical Processing (OLAP) — это способ организации больших коммерческих баз данных. Базы данных OLAP позволяют получать и анализировать данные, что облегчает создание необходимых отчетов.

Базы данных OLAP спроектированы так, чтобы ускорить загрузку данных. Поскольку суммирует данные сервер OLAP, а не Microsoft Excel, при создании и изменении отчета требуется передача в Microsoft Excel гораздо меньшего объема информации. Этот подход позволяет работать с гораздо большими объемами информации, чем при использовании обычной базы данных. В этом случае Microsoft Excel должен загрузить все отдельные записи и подсчитать итоговые значения.

Запросы отчетов для внешних баз данных большого объема могут занимать много времени, а также требовать для своего выполнения слишком большого объема памяти и других ресурсов. Чтобы избежать этих трудностей, можно извлекать только исходные данные, которые необходимы для вывода текущей страницы отчета, а при смене страницы считывать новые данные.

Каким образом поля страниц позволяют работать с большим объемом данных

Обычно Microsoft Excel извлекает все данные для отчета, включая данные для страниц, не отображаемых в данный момент, при создании или обновлении отчета. При настройке поля страницы на извлечение данных только при отображении новой страницы, будет производиться извлечение только необходимых данных меньшего объема. Каждый раз при отображении другого элемента поля страницы будет извлекаться набор данных для этого элемента.

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

Влияние на производительность

Если для поля страницы установлен параметр извлечения данных по страницам, элемент Все, суммирующий полный набор данных, и команда Отобразить страницы недоступны. При отображении различных страниц необходимо дождаться получения данных. В случае отключения от внешней базы данных, отображение другого элемента поля страницы будет невозможно до восстановления подключения к исходным данным. Однако эти установки позволяют работать с большими объемами исходных данных в обход ограничений, накладываемых системой.

Если все страницы запрашиваются одновременно (параметр по умолчанию), первоначальное извлечение данных может потребовать больше времени, но после этого можно будет изменять страницу без задержек, поскольку все необходимые данные будут находиться в памяти. Таким же образом можно отображать итог целого набора данных (элемент Все) и реорганизовывать отчет, чтобы использовать поля страницы в других областях. Этот параметр обеспечивает наибольшую гибкость, когда общий объем извлеченных данных находится в пределах ограничений, накладываемых системой.

Перемещение поля страницы

При перетаскивании поля страницы, настроенного на последовательный запрос страниц, в другую позицию, например при попытке сделать его полем строки, будет предпринята попытка выбора данных для всех элементов, в результате чего системе может не хватить ресурсов.

Когда данный параметр недоступен

Отчеты, основанные на исходных данных из баз данных OLAP автоматически запрашивают необходимые данные, поэтому данный параметр для них недоступен.

Поле страницы не должно иметь сгруппированных элементов; чтобы параметр стал доступным, их требуется разгруппировать.

Если несколько отчетов сводной таблицы или диаграммы основаны на одном отчете (в мастере использовался параметр Другой отчет сводной таблицы или отчет сводной диаграммы), постраничная передача данных в отчетах недоступна. Однако при наличии единственного отчета сводной диаграммы и связанного с ним отчета сводной таблицы параметр доступен.

Если драйвер ODBC для внешней базы данных, к которой выполняется запрос, не поддерживает запросы с параметрами, постраничный запрос невозможен. Все драйверы ODBC, поставляемые с Microsoft Query, поддерживают запросы с параметрами. Для получения сведений о поддержке запросов с параметрами драйверами других производителей, обратитесь к поставщику.

Примечание. Если отчет создан на основе файла автономного куба, при его обновлении файл куба будет полностью собран заново из данных с OLAP-сервера, что может занять продолжительное время (необходимое для создания файла куба).

Если требуется обновление отчета при открытии книги, установите соответствующий режим.

Если отчет создан на основе внешних данных, и его требуется обновлять через определенные интервалы времени, установите соответствующий режим.

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

Если при каждом обновлении необходимо указывать пароль на доступ к базе данных, можно сохранить этот пароль в Microsoft Excel для автоматического ввода. На панели инструментов Сводные таблицы нажмите кнопку Сводная таблица или Сводная диаграмма, выберите команду Параметры таблицы или Параметры, а затем установите флажок сохранить пароль. В этом случае пароль сохраняется в виде открытого текста, поэтому если безопасность важнее удобства, не следует использовать этот режим.

Этот параметр недоступен в отчетах, созданных на основе исходных данных OLAP.

Выполните одно из следующих действий.

Чтобы ожидать завершения получения данных в ходе выполнения запроса, снимите флажок фоновый запрос.

Чтобы выполнить запрос с новыми параметрами, нажмите на панели инструментов Сводные таблицы кнопку Обновить данные .

Примечание. При записи в Microsoft Excel макроса, включающего запрос, этот запрос выполняется в режиме ожидания. Чтобы сменить режим выполнения макроса на фоновый, воспользуйтесь редактором Visual Basic для изменения выражения «BackgroundQuery := False» на «BackgroundQuery := True».

Импорт данных позволяет избежать повторного их ввода для анализа в Microsoft Excel. Можно также при каждом занесении в базу данных новой информации автоматически обновлять отчеты и сводки.

Импорт данных

Импорт данных из баз данных и файлов

Для импорта данных в Microsoft Excel из большинства источников нужно выбрать команду Импорт внешних данных в меню Данные, затем выбрать подпункт Импортировать данные, после чего в диалоговом окне Выбор источника данных выбрать данные, которые требуется импортировать.

Мастер подключения данных, запускающийся при нажатии кнопки Создать источник в диалоговом окне Выбор источника данных, позволяет импортировать данные из внешних источников, недоступных из диалогового окна Выбор источника данных. Эти источники могут включать источники данных OLE DB (включая кубы OLAP и серверы обмена) и любые источники данных, поставляемые системным администратором. В мастере подключения данных нельзя фильтровать или объединять данные.

По умолчанию при импорте данных с помощью мастера подключения данных соединение устанавливается через поставщиков OLE DB. Созданные при этом ODC-файлы можно открыть для просмотра в обозревателе Microsoft Internet Explorer, а для их редактирования использовать Microsoft Excel, блокнот и другие приложения Microsoft Office, если файл не указывает на источник данных OLAP.

Примечание. Чтобы импортировать внешние данные в Microsoft Excel, необходимо иметь доступ к этим данным. Если внешний источник данных находится на другом компьютере, может возникнуть необходимость получить у администратора внешней базы данных пароль, право доступа или сведения, требующиеся для доступа к базе данных.

Импорт данных с помощью Microsoft Query

В большинстве случаев можно импортировать данные с помощью команды Импорт данных, как это описано в предыдущем разделе. Microsoft Query или другое приложение следует использовать, только если требуется выполнить специализированные задачи:

Microsoft Query обладает всеми необходимыми для выполнения этих специализированных задач свойствами.

Microsoft Query можно использовать для задания источников данных ODBC для загрузки данных. Можно использовать мастер запросов для создания простых запросов или использовать расширенные возможности Microsoft Query для создания более сложных запросов. Можно получить доступ к Microsoft Query из Microsoft Excel или создать запрос в мастере сводных таблиц и сводных диаграмм.

Кроме того, с Microsoft Query можно использовать динамический обмен данными (DDE). Более подробную информацию о DDE можно получить из справки по Microsoft Query.

Чтобы импортировать данные с помощью Microsoft Query, необходимо выполнить следующее.

Для получения дополнительных сведений обратитесь к справочной системе Microsoft Query.

Импорт данных из Интернета

Для импорта данных с веб-страницы нужно выбрать команду Импорт внешних данных в меню Данные, а затем выбрать подпункт Создание веб-запроса. При этом необходим доступ в Интернет через локальную сеть организации или через модем. Кроме того, можно создать запрос, относящийся к локальным источникам данных HTML или XML.

Импорт данных с помощью Microsoft Visual Basic для приложений (VBA)

Макрос Microsoft Visual Basic для приложений может быть использован для получения доступа к внешним источникам данных.

В зависимости от источника данных, для получения данных с помощью VBA следует использовать объекты данных Microsoft ActiveX или объекты доступа к данным (DAO).

Для того чтобы использовать макрос, созданный в Microsoft Excel версии 5.0 или более ранней, выберите команду Надстройки в меню Сервис и установите флажок Надстройка ODBC.

Сведения о создании макросов на языке Microsoft Visual Basic для приложений содержатся в справочной системе Microsoft Visual Basic.

Примечание. Во время записи макроса, содержащего запрос, Microsoft Excel не может выполнить этот запрос в фоновом режиме, даже если выбрать этот способ выполнения. Для того чтобы записанный макрос мог выполняться в фоновом режиме, замените с помощью редактора Microsoft Visual Basic строку "BackgroundQuery:= False" на "BackgroundQuery := True".

Обновление данных и именование диапазонов

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

Если для доступа к данным во внешнем источнике данных требуется пароль, можно установить режим запроса пароля при каждом обновлении диапазона внешних данных.

При расширении диапазона внешних данных и возврате дополнительных записей Microsoft Excel может заполнить формулами соседние столбцы или столбцы внутри диапазона данных, чтобы они оставались рядом с соответствующими данными.

Также можно выбрать способ добавления новых данных на лист.

Именование диапазонов внешних данных

Microsoft Excel автоматически именует диапазоны внешних данных следующим образом.

Если лист имеет несколько диапазонов внешних данных из одного источника, они нумеруются. Например MyText, MyText_1, MyText_2 и так далее.

Кроме того, изменять названия диапазонов внешних данных можно в диалоговом окне Свойства диапазона данных.

Щелкните ячейку, в которую требуется поместить данные из текстового файла.

Эти процедуры позволяют импортировать данные из большинства источников данных, включая базы данных OLAP.

Для получения сведений об отборе и объединении данных перед импортом в Microsoft Excel см. справку Microsoft Query.

Выполните одно из следующих действий.

Откройте имеющийся источник данных

Создайте подключение к новому источнику данных

В окне мастера подключения данных выберите источник данных для подключения, а затем нажмите кнопку Далее.

Сразу оговорюсь, что Microsoft Office на сервере был установлен и при обычном запуске обработки (не в фоновом задании) все проходило на ура.

Так как фоновые задания выполняются от имени пользователя, под которым работает сервер 1С:Предприятие (обычно это USR1CV82), то возникло логичное предположение, то у этого пользователя не достаточно прав. Для эксперимента этому пользователю были выданы права администратора и фоновое задание завершилось успешно. Однако, это был не вариант. Требовались минимально возможные права и я окунулся в мир поиска информации в глобальной сети.

Итак. Первое что удалось выяснить, это то, что пользователю USR1CV82 необходимо дать права на запуск DCOM Microsoft Excel Application (прошу прошения, могу путаться в понятиях, но смысл, что делать, думаю, будет понятен).

Для этого необходимо:

1. Нажать кнопку Пуск и выбрать Панель управления.

2. Дважды щелкнуть значок Администрирование и выбрать пункт Службы компонентов.

3. В левой панели окна "Службы компонентов" дважды щелкнуть пункт Службы компонентов, дважды щелкнуть пункт Компьютеры и выбрать Мой компьютер.

4. Выбрать папку Настройка DCOM.

5. В окне сведений найти объект Microsoft Excel Application, щелкнуть его правой кнопкой и выбрать пункт Свойства (здесь есть подводный камень, с которым я довольно долго промучался - если сервер 64-х разрядный, то и выпуск Office так же должен быть х64, иначе объекта Microsoft Excel Application не будет в списке и права для него мне так и не удалось настроить пока не переустановил Office).

6. Открыть вкладку Безопасность.

7. В разделе Разрешения на запуск выбрать пункт Настроить и нажать кнопку Изменить.

8. Добавить пользователя USR1CV82.

Ниже поясняющие скриншоты.

Однако, после произведенных описанных выше настроек фоновое задание так и не заработало.

При работе с COM объектом экземляр Excel успешно создавался, но открыть файл не получалось

Произошла исключительная ситуация (Microsoft Excel): Приложению Microsoft Excel не удается получить доступ к файлу "C:\xls_test\test.xls". Это может быть вызвано одной из следующих причин.

• Указан несуществующий файл или путь.
• Файл используется другой программой.
• Имя книги, которую вы пытаетесь сохранить, совпадает с именем другой книги, открытой в данный момент.

Поиски в просторах сети были продолжены и решение таки было найдено:

Необходимо создать папку

Данное расположение папок указано для Office 2007. Для Office 2010 не зависимо от разрядности платформы необходимо создавать папку C:\Windows\System32\config\systemprofile\Desktop (данный факт так же потратил не мало моего времени). В общем, если создали папку, а ошибки не исчезли, создайте вторую. Так же необходимо дать права на созданную папку пользователю USR1CV82. Практика показала, что достаточно прав, выдаваемых системой по умолчанию.

Читайте также: