Подключение к эксель через ado 1с
Используя 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.
Существует несколько способов программной работы с файлами Excel из 1С. Каждый из них имеет свои преимущества и недостатки.
Обмен через табличный документ
Данный способ простой. Его суть заключается в том, что объект ТабличныйДокумент имеет методы:
- Записать (< ИмяФайла >, < ТипФайлаТаблицы >) для выгрузки данных в файл;
- Прочитать (< ИмяФайла >, < СпособЧтенияЗначений >) для загрузки данных из файла.
Внимание!
Метод Записать () доступен как на клиенте, так и на сервере. Метод Прочитать () доступен только на стороне сервера. Необходимо помнить об этом
при планировании клиент-серверного взаимодействия.
Рассмотрим пример сохранения табличного документа в файл. Необходимо любым способом создать и заполнить объект ТабличныйДокумент, а выгрузка в файл осуществляется всего лишь одной строкой:
ТабДок . Записать ( ПутьКФайлу , ТипФайлаТабличногоДокумента . XLSX );
Загрузка из файла осуществляется также достаточно просто:
ТабДок = Новый ТабличныйДокумент ;
ТабДок . Прочитать ( ПутьКФайлу , СпособЧтенияЗначенийТабличногоДокумента.Значение );
Обмен через OLE
Обмен через технологию OLE automation, пожалуй, самый распространенный вариант программной работы с файлами Excel. Он позволяет использовать весь функционал, предоставляемый Excel, но отличается медленной скоростью работы по сравнению с другими способами. Для обмена через OLE требуется установка MS Excel:
- На компьютере конечного пользователя, если обмен происходит на стороне клиента;
- На компьютере сервера 1С:Предприятие, если обмен происходит на стороне сервера.
Пример выгрузки:
Примеры чтения:
Для обхода всех заполненных строк листа Excel можно использовать следующие приемы:
Вместо последовательного обхода всех строк листа можно выгрузить все данные в массив и работать с ним. Такой подход будет быстрее при чтении большого объема данных:
ВсегоКолонок = Лист . Cells ( 1 , 1 ). SpecialCells ( 11 ). Column ;
ВсегоСтрок = Лист . Cells ( 1 , 1 ). SpecialCells ( 11 ). Row ;Область = Лист . Range ( Лист . Cells ( 1 , 1 ), Лист . Cells ( ВсегоСтрок , ВсегоКолонок ));
Данные = Область . Value . Выгрузить ();
В таблице ниже приведены наиболее востребованные свойства и методы для работы с Excel через OLE:
Для того, чтобы узнать какое свойство нужно менять или какой метод вызвать можно воспользоваться макросами Excel. Если записать макрос с требуемыми действиями, то после можно посмотреть программный код на VBA записанного макроса.
Использование COMSafeArray
Обмен через ADO
Файл Excel при обмене через ADO представляет собой базу данных, к которой можно обращаться при помощи SQL-запросов. Установка MS Excel не требуется, но обязательно наличие драйвера ODBC, при помощи которого будет осуществляться доступ. Используемый драйвер ODBC определяется при указании строки соединения к файлу. Обычно требуемый драйвер уже установлен на компьютере.
Обмен через ADO заметно быстрее обмена через OLE, но при выгрузке нет возможности использовать функционал Excel для оформления ячеек, разметки страниц, задания формул и т.д.
Пример выгрузки:
Для создания нового листа и формирования его структуры можно воспользоваться объектами ADOX.Catalog и ADOX.Table. В этом случае код примет вид:
В приведенном примере в методе
во втором параметре указывается тип колонки. Параметр необязательный, вот некоторые значения типа колонки:
Пример чтения:
В строке соединения параметр HDR определяет как будет восприниматься первая строка на листе. Возможны варианты:
В приведенных примерах рассмотрено лишь несколько объектов ADO. Объектная модель ADO состоит из следующих объектов:
- Connection;
- Command;
- Recordset;
- Record;
- Fields;
- Stream;
- Errors;
- Parameters;
- Properties.
Выгрузка без программирования
Для сохранения данных из 1С в Excel не всегда целесообразно прибегать к программированию. Если в режиме Предприятия пользователь может отобразить требуемые для выгрузки данные, то, их возможно сохранить в 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.
Завершаю триптих кодом, как загружать в Excel из ADO.
Плюсы - в том что на компьютере, где используется загрузка через ADO не требуется быть в наличии Excel. Скорость очень большая, быстрее, чем чере COM. Ну и в целом, универсальный механизм.
Минусы - ADO не переваривает файлы с большим количеством колонок. Не работает простым образом под Linux, т.к. все же использует COM-объекты.
Код функции под катом.
Функция ЗагрузитьExcelФайлВМассивЛистовЧерезAdo ( ИмяФайла , ЗНАЧ СоответствиеФильтраЛистов = Неопределено)
//СоответствиеФильтраЛистов - соответствие содержащее числа с номерами листа (начиная с единицы)
//Или названия листов в виде строки.
//Возвращается массив структур с полями:
// Номер - номер листа с единицы
// Имя - имя листа строкой
// Строк - количество строк
// Колонок - количество колонок
// Ячейки - двумерный массив колонок, первое измерение - строка, второе - колонка
МассивЛистов = Новый Массив (); //Это массив листов
АДОСоединение =Новый COMОбъект ( "ADODB.Connection" );
АДОСоединение . ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source red">+ СокрЛП ( ИмяФайла )+ ";Extended Properties=""Excel 8.0;HDR=NO;IMEX=1;""" ;
//АДОСоединение.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+СокрЛП(Родитель.ИмяФайла)+";Ext ended Properties=""Excel 8.0;""";
АДОСоединение . Open ();
АДОКаталог = Новый COMОбъект ( "ADOX.Catalog" );
АДОКаталог . ActiveConnection = АДОСоединение ;
НомерЛиста = 0 ; //Листов может быть меньше таблиц за счет видов View
Для iCount = 0 По АДОКаталог . Tables . Count - 1 Цикл
Если АДОКаталог . Tables . Item ( iCount ). Type = "VIEW" Тогда
Продолжить;
КонецЕсли;
НомерЛиста = НомерЛиста + 1 ;
ИмяИсходное = АДОКаталог . Tables . Item ( iCount ). Name ;
ИмяЛиста = ИмяИсходное ;
Если Прав ( ИмяЛиста , 1 )= "'" Тогда
ИмяЛиста = ЛЕВ ( ИмяЛиста , СтрДлина ( СокрЛП ( ИмяЛиста ))- 1 );
КонецЕсли;
Если Прав ( ИмяЛиста , 1 )= "$" Тогда
ИмяЛиста = ЛЕВ ( ИмяЛиста , СтрДлина ( СокрЛП ( ИмяЛиста ))- 1 );
КонецЕсли;
Если Лев ( ИмяЛиста , 1 )= "'" Тогда
ИмяЛиста = Сред ( ИмяЛиста , 2 );
КонецЕсли;
Если СоответствиеФильтраЛистов <> Неопределено Тогда
Если СоответствиеФильтраЛистов [ НомерЛиста ] = Неопределено
И СоответствиеФильтраЛистов [ ИмяЛиста ] = Неопределено
Тогда
Продолжить; //Если лист грузить не надо
КонецЕсли;
КонецЕсли;
АДОЗапись = Новый COMОбъект ( "ADODB.Recordset" );
АДОЗапись . ActiveConnection = АДОСоединение ;
АДОЗапись . CursorType = 0 ;
АДОЗапись . LockType = 1 ;
АДОЗапись . Open ( "Select * from [" + ИмяИсходное + "]" );
КоличествоКолонок = АДОЗапись . Fields . Count ;
Ячейки = Новый Массив ();
Стр = 0 ;
АДОЗапись . MoveFirst ();
Пока АДОЗапись . EOF () = 0 Цикл
Стр = Стр + 1 ;
МассивСтроки = Новый Массив ( КоличествоКолонок );
Ячейки . Добавить ( МассивСтроки );
Для Кол = 1 ПО КоличествоКолонок Цикл
МассивСтроки [ Кол - 1 ] = АДОЗапись . Fields ( Кол - 1 ). Value ;
КонецЦикла;
АДОЗапись . MoveNext ();
КонецЦикла;
СтруктураЛиста = Новый Структура ();
СтруктураЛиста . Вставить ( "Строк" , КоличествоСтрок );
СтруктураЛиста . Вставить ( "Колонок" , КоличествоКолонок );
СтруктураЛиста . Вставить ( "Ячейки" , Ячейки );
СтруктураЛиста . Вставить ( "Номер" , НомерЛиста );
СтруктураЛиста . Вставить ( "Имя" , ИмяЛиста );
Читайте также: