Запрос к 1с из excel
Используя Excel можно получать из 1С различные отчеты.
Excel при доступе к данным DBF не требует авторизации, т.е. может быть использован для взлома.
О доступе в 1С через Excel администраторам и опытным пользователям
Данная статья предназначена для опытных пользователей и разработчиков, которых хотят получать отчетность из 1С в Excel. Кроме того, данная статья затрагивает вопросы безопасности в подобных решениях.
Доступ к данным локальной и сетевой версии 1С (DBF-версия)
на ANSI. Затем в файле 1CV7.DD смотрят описание таблиц с данным 1С. В конце составляют запрос для получения нужной информации из 1С через ODBC-драйвер FoxPro. Например, для получения информации о приходных накладных нужен примерно такой запрос:
SELECT dh434.IDDOC, dh434.SP440, dh434.SP910, dh434.SP441, dh434.SP453, sc46.DESCR, sc33.DESCR, dt434.SP449, dt434.SP451, dt434.SP453 FROM dh434.dbf dh434, dt434.dbf dt434, sc33.dbf sc33, sc46.dbf sc46 WHERE sc46.ID = dh434.SP437 AND dh434.IDDOC = dt434.IDDOC AND dt434.SP448 = sc33.ID
Для создания запроса не обязательно знать язык SQL, его можно построить с помощью мастера запросов Microsoft Query, приведенный запрос был сделан именно так. Полученные данные из можно Microsoft Query можно анализировать в Excel, если вызвать Microsoft Query из пункта меню "Данные/ Импорт внешних данных/ Создать запрос".
Замечания по безопасности.
Как видим, DBF-файлы в принципе не подразумевают авторизацию при доступе к данным. Таким образом, любой пользователь 1С используя данный метод может получить полный доступ к данным в 1С. Серьезных мер противодействия этому нет, но есть несколько методических мер, которые могут смягчить проблему:
- Не раскидывайте Excel-отчеты предназначенные для работы с 1С где попало. Устанавливайте их только тем пользователям, которым они нужны.
- Используйте средства Windows Trusted Security, для изоляции разных баз друг от друга.
- Не устанавливайте Microsoft Query и ODBC-драйверы для DBF-файлов обычным операторам БД.
Доступ к данным версии 1С для SQL
SELECT dh434.IDDOC, dh434.SP440, dh434.SP910, dh434.SP441, dh434.SP453, sc46.DESCR, sc33.DESCR, dt434.SP449, dt434.SP451, dt434.SP453
FROM dh434, dt434, sc33, sc46 WHERE sc46.ID = dh434.SP437 AND dh434.IDDOC = dt434.IDDOC AND dt434.SP448 = sc33.ID
По сравнению с анализом DBF-базы запрос имеет несущественные отличия в разделе FROM и для составления запроса следует пользоваться описанием базы в разделе 1CV7.DDS.
Замечания по безопасности
В случае MS SQL для получения данных требуется авторизация на сервере. Рекомендуем использовать метод Trusted Security для входа в MS SQL. Это позволяет не хранить пароли в настройках системы и блокирует атаку через подбор пароля (password attack). Следует помнить, что если пользователи смогут скопировать файл 1CV7.DBA, то довольно легко они смогут расшифровать пароль MS SQL с помощью таких программ как unsql.exe. Надежная защита от такого взлома строится с помощью таких продуктов как "Защита 1С: Предприятия для SQL".
. ну, конечно, не прям так типовым языком запросов, а расширенным языком запросов. Речь пойдет о том, как "залить" данные в таблицы 1С и не пилить при этом бесконечного количества наколеночных обработок.
Вполне известно, что фирма 1С не рекомендует "заливать" данные в типовые конфигурации, иначе чем в самые простейшие справочники. По крайней мере такой вывод можно сделать из того, как работает типовая "заливка". Но, кто когда их слушал как правило, в простейших справочниках данных бывает немного и вколотить их руками не представляет большой трудности. А вот "тяжелые" справочники, типа "номенклатура" и "контрагенты" залить типовой обработкой не получается, пользователи вводить 100500 элементов вручную почему-то отказываются и приходится колхозить на коленке.
Но есть способ лучше! )
Консоль расширенных запросов теперь поддерживает параметры запроса типа ТаблицаЗначений. Иными словами, вы можете в запросе ВСТАВИТЬ манипулировать таблицей, скопированной из Excel. Это дает огромный потенциал для массовых манипуляций с данными, которые хранятся в какие-то внешних источниках.
Самая приятная часть заливки состоит в том, что вам не нужно перечислять бесконечное количество реквизитов номенклатуры и потом мучительно выяснять, что вы забыли про реквизит "ЕдиницаИзмеренияСрокаГодности". Вы можете попросить пользователя ввести одну "эталонную" номенклатуру в пользовательском интерфейсе и затем только описывать изменения. Понятно, что при этом нужно не забыть, например, что у разных видов номенклатуры могут быть разные значения реквизитов, (которые, впрочем можно также скопировать из вида) но это все равно проще, чем стараться заполнить все значимые для системы, но не значимые для текущего заказчика реквизиты.
Каждый набор данных (каждый справочник), заливаемый в систему, у нас будет характеризоваться двумя файлами:
- Данные (это может быть эксель с непосредственными значениями, но более вероятно что это будет текст запроса к какой-то старой системе заказчика)
- Способ их помещения в систему (текст расширенного запроса для нашей системы)
Имея такой набор на черный день, вы всегда сможете доверить перезаливку данных в систему кому-то вроде консультанта или даже продвинутого пользователя.
Текст запроса, например, для заливки новой номенклатуры по образцу будет такой:
Здесь стоит обратить внимание на следующие особенности:
Во-первых, вы спокойно можете писать конструкции вида Образец.* не опасаясь того, что в новой номенклатуре окажутся наименования от старой. Все, что вы переопределите выше (Артикул, Наименование..) в таблице "со звездочкой" автоматически переименуется (Артикул1, Наименование1..) и успешно проигнорируется.
Во-вторых, чтобы из образца у нас не взялась ссылка (в этом случае мы получим ошибку вставки с неуникальной ссылкой) мы должны заместить это поле. Чем заместить - не так важно, если это будут идентификаторы, которые вам важно получить во вставленных элементах - просто вставьте их строковые представления в эксель, иначе - используйте любые но уникальные значения. Если расширению не удастся привести их к ГУИДу - оно просто проигнорирует их.
В-третьих, если есть подозрение, что данные придется заливать не один раз (а чаще всего так и бывает) - позаботьтесь заранее о нахождении уже загруженных значений. т.е. устанавливайте соответствия, например, по коду (да, с уникальными идентификаторами в запросах все сложно) и напишите сначала запрос УДАЛИТЬ, а затем вставляйте новые значения фразой ВСТАВИТЬ. Это поможет вам избежать дублирования элементов.
В-четвертых, как всегда, вы можете формировать данные сколь угодно сложным способом, включая вложенные запросы, значения через точку, ВЫБОР КОГДА и т.д.
В-пятых, обратите внимание на ссылочные типы. Никакой язык запросов, даже трижды расширенный, не приведет вам строку "Кондиционеры" к ссылке на вид номенклатуры. Вам нужно сделать это самостоятельно. Для этого, вам, опять-же, придется иметь какое-то уникальное значение в excel таблице (благо, добавить номер строки в экселе не долго) и написать конструкцию вида:
Такие конструкции конечно не облегчают процесс загрузки и в простейшем случае, когда соответствие устанавливается по коду, вероятно, не оправданы (проще воспользоваться другим загрузчиком с просторов Инфостарта), но если "все не так просто": нужно получать много чего через точку или тип значения не может быть выбран "в лоб" - консоль расширенных запросов вас выручит.
Представим себе ситуацию, при которой Вам надо совместить данные из файла excell и действующей базы на 1С. Впринципе можно выгрузить данные из 1С в тот же Эксель и "ПроВПРить". Но во-первых, нам могут понадобиться более продвинутые средства получения данных, чем ВПР, во-вторых, для сложных конструкций это долго, а в третьих, для порядочного 1С-ника это ФИ (не зря же мы изучали язык запросов! да и конструктор запросов- вещь крутая). Но сосредоточимся на главном - это получение данных из файла Эксель(или другого табличного файла, можно и какого-нибудь csv) и использование их в запросе.
Я использую следующую методику:
1) Сначала читаем этот файл и загоняем данные в таблицу значений (ТЗ).
2) Передаем полученную ТЗ как параметр запроса и создаем на основе нее временную таблицу (ВТ).
3) Используем полученную ВТ для дальнейших манипуляций с запросами (через 2-ой пакет запросов).
Для удобства я доработал Консоль запросов (для управляемых форм), чтобы она выполняла первые 2 пункта на автомате, читая файл и дописывая запрос до нужного нам вида. Первая строка таблицы используется как заголовки(Она обязательно должна быть!). Нам останется лишь в конструкторе запросов добавить второй пакет и в нем уже получить нужные нам выборки. В параметрах Вы можете назначить типы данных. Так же для функционала добавил исполняемый код для строк выборки. Все параметры и код сохраняются в файл вместе с запросом.
Обработку прилагаю, можете посмотреть, что да как сделано, и использовать вместо обычной консоли запросов. Или не использовать. Если обработка окажется полезной, доработаю её под другие типы файлов(csv например).
Чтобы добавить несколько таблиц, надо переименовать параметр с Таб например не Таб2 в параметрах и в самом запросе в первом пакете. так же переименовать временную таблицу, тогда после прочтения другого файла, будет добавлен новый пакет запросов и новый параметр.
Поработал с обработкой и допилил её до вполне юзабельного на мой взгляд вида:
Загрузка из Excel в 1С, довольно частая задача, которую приходится решать программистам 1С. В данной заметке будет показано, как произвести загрузку данных из файла Excel в 1С, наиболее быстрым и оптимальным способом. Перед тем как показать примеры кода, объяснюю как производить отладку и проверку выполнения SQL запросов к файлу Excel, для последующей загрузки данных в программу 1С.
Для примера будет использоваться файл формата Excel со следующей структурой и данными:
Данные Excel для загрузки в 1С
Чтобы выполнить SQL запрос к файлу Excel и проверить результат, необходимо запустить программу Microsoft Excel, перейти в главном меню программы на вкладку «Данные», далее нажать кнопку «Из других источников» и в появившемся меню выбрать пункт «Из Microsoft Query»:
Запуск MSQuery для загрузки из Эксель в 1С
После появится окно «Выбор источника данных», в нем необходимо выбрать пункт «Excel Files*» и нажать «ОК»:
Выбор источника для загрузки
Далее выбрать непосредственно ваш файл Excel для выборки данных. Может появится предупреждение, что «Данный источник данных не содержит видимых таблиц», игнорируем его и нажимаем «ОК». После необходимо в параметрах установить пункт отображать системные таблицы:
Выбор системные таблицы
После включения данной опции, появится возможность выбора листов из книги Excel и столбцов для запроса:
Выбор столбцы запроса
Дальше необходимо несколько раз нажать «Далее» и на последнем шаге, выбрать пункт «Просмотр или изменение данных в Microsoft Query» и «Готово»:
Откроется окно «Microsoft Query», в котором мы видим выборку данных из нашего файл Excel. Для тестирования и редактирования SQL запросов к файлу Excel, используется кнопка «SQL», при помощи которой вызывается окно редактирования запроса:
Теперь, когда вы знаете, как тестировать ваши SQL запросы к файлу Excel, попробуем загрузить данные в программу 1С, для этого создадим внешнюю обработку, добавим основную форму, для удобства вывода информации добавим реквизит формы с типом «Табличный документ» и отобразим его на форме. В модуле формы добавим процедуру «ПриОткрытии».
Далее приведен код с поясняющими комментариями:
Результат выполнения внешней обработки по загрузке данных из файла Excel в 1С с помощью запроса SQL, показан на изображении:
Подключимся и загрузим из файла Excel данные в таблицу значений 1С. Сделать теперь это очень просто.
1. В конфигурации добавляет новый объект метаданных типа "Внешние источники данных" и назовем его просто "Excel".
2. На закладке "Данные" созданного объекта "Excel" добавляем новую таблицу. При этом появится окно мастера добавления таблицы внешнего источника.
Выбираем первый пункт "Вручную", т.к. лично у меня второй пункт "Выбрать из списка таблиц внешнего источника данных" работает нестабильно.
Если Вы все же хотите попробовать добавить таблицу через второй пункт, то в качестве строки соединения в появившемся окне указываем:
Driver=;DBQ=C:\Documents and Settings\Admin\Мои документы\finance3.xls; DriverID=790
В этой строке укажите путь к своему файлу. Имя пользователя и пароль я не указывал.
Отмечу, что путь к файлу и строка соединения в дальнейшем не запоминается и нужны эти параметры единожды, чтобы создать автоматически описание полей таблицы источника.
Если соединение прошло нормально, то должен отобразиться список таблиц источника. Флажком можно выбрать нужную таблицу и ее поля.
3. Даем имя новой таблице "Данные". Переходим на вкладку таблицы "Данные". В поле "Имя в источнике данных" указываем имя листа в файле Excel и добавляем в конце знак "$". В моем случае это "Данные$". В табличной части "Поля" добавляем поля и редактируем их тип.
У каждого поля в свойствах должно быть задано как минимум "Имя" и "Имя в источнике данных".
4. Создаем процедуру в которой пишем подключение к источнику и запрос:
Код 1C v 8.х
Параметры соединения для файла Excel выглядят вот так:
Файлы XLS (ранее)
Driver=; DriverID=790; DBQ=D:\ФайлExcel.xls;
Похожие FAQ
ZIP-архив Создание 0
Для того чтобы записать файлы в ZIP-архив необходимо выполнить несколько простых действий: * Создать архив с необходимыми параметрами, в который будут помещаться файлы. * Поместить в архив необходимые файлы. * Записать архив. Создание объекта За Активация открытой книги Excel для корректировки 0
Нужно было получить для изменения уже открытую книгу Excel, а если она закрыта, то открыть и изменять. Ниже код, который данную задачу решает. Попытка Эксель = Неопределено; Книга = Неопределено; Лист = Неопределено; Состояние(" Выполняется Быстрая загрузка данных большого размера файла Excel в многомерный Массив 0
// OldthiefXXX Перем СерверExcel; Процедура КнопкаВыполнитьНажатие(Кнопка) ФайлХЛС=СерверExcel.Application.Workbooks.Open(ДокументХЛС); КонечнаяСтрокаДанных=ФайлХЛС.ActiveSheet.UsedRange.Rows.Count; КоличествоКолонокДанных=ФайлХЛС.ActiveSheet Быстрая функция чтения данных с листа Excel 9
При чтении файла Excel я использую вариантный массив. Он позволяет быстро получить ВСЮ таблицу листа в память, а также получать данные массива целыми колонками. Тем самым время на чтение области файла Excel в таблицу значений сокращается в десятки ра Вставить формулу, функцию в ячейку Excel 0
Ячейка = Лист.Cells(1,8); Ячейка.Formula=" =SUM(R11C:R C)" ; Процедура Сформировать() Перем Кат,Шаб; Ехсел=СоздатьОбъект(" Excel.Application" ); Книга=Ехсел.Workbooks; Если ФС.ВыбратьФайл(0,Кат,Шаб," Выбери файл Еxcel" ,," *.xls" ,)=1 Тогда Посмотреть все результаты поиска похожих
Еще в этой же категории
получение данных 0
Всем доброго дня. Суть проблемы. В бухгалтерии стоит 1с8 . Нужно дописать модуль, который будет дергать несколько справочников и документов из 1с, бухгалтерии. Главный бухгалтер залезть к ним и дописать свой модуль не дает. Написал свое, отдельно. Посмотреть все в категории Внешние источники данных
Читайте также: