Имя: Пароль:
1C
1С v8
Чтение данных XLS, XLSX запросом (мануал)
,
0 aleks-id
 
12.12.11
09:24
Решил выложить еще один мануал по чтению запросом данных из файлов Excel
Сначала надо скачать и установить на компьютер(сервер) вот этот провайдер:
http://www.microsoft.com/download/en/details.aspx?id=23734

Для доступа к данным Excel используем следующий код:

   СтрокаПодключения = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source = "+Путь;
   СтрокаПодключения = СтрокаПодключения + "; Extended Properties = "+"""Excel 12.0"+";HDR=NO;IMEX=1"";";
       
   // Подключаемся
   Об_Конект = Новый COMОбъект("ADODB.Connection");
   Попытка
       Об_Конект.Open(СтрокаПодключения);
   Исключение
       Сообщить ("Невозможно подключится к Microsoft Excel Driver!!!
       |Возможно файл ["+Путь+"] открыт другим пользователем.");
       Возврат;
   КонецПопытки;
       
   СтрЗапроса = "
   |SELECT *
   |FROM [A" + Формат(НачальнаяСтрока,"ЧГ=0") + ":CZ" + Формат(КонечнаяСтрока,"ЧГ=0") + "]
   |"; //данный запрос выбирает все заполненные ячейки листа, однако можно наложить условия отбора. синтаксис SQL
       
   Попытка
       RecordSet = Об_Конект.Execute(СтрЗапроса);
   Исключение
       Сообщить("Не удалось выполнить запрос к файлу Excel
       |"+ ОписаниеОшибки(),СтатусСообщения.Важное);
       Возврат;
   КонецПопытки;

   Пока НЕ RecordSet.EOF() Цикл    
       //Дальше обрабатываем RecordSet
   КонецЦикла;
1 Defender aka LINN
 
12.12.11
09:30
Мож лучше в КЗ черканешь тогда?
2 aleks-id
 
12.12.11
09:32
(1) у меня прав нет. перенеси, если не сложно.
3 Wobland
 
12.12.11
09:35
(0) спасибо!
4 golden-pack
 
12.12.11
09:36
Круто.
5 Defender aka LINN
 
12.12.11
09:38
(2) Создать статью вроде каждый может
6 aleks-id
 
12.12.11
09:38
(5) счас попробую
7 Rie
 
12.12.11
09:39
(2) В базу знаний уже внесено.
Напиши статью в Книгу знаний.
8 aleks-id
 
12.12.11
09:46
9 Aprobator
 
12.12.11
09:52
(0)  а со ссылкой на конкретный лист можно делать?
10 Aprobator
 
12.12.11
09:53
+(9) иначе то в чем прелесть во сравнению со стандартной строкой подключения при уже установленном Excel на компе?
11 Александр_
Тверь
 
12.12.11
09:54
да, не знал про такой вариант.
12 aleks-id
 
12.12.11
10:10
(9) select * from [Лист1$]
13 aleks-id
 
12.12.11
10:10
(10) прелесть в том, что за эксель надо платить, а этот провайдер бесплатный ;)
14 Александр_
Тверь
 
12.12.11
10:11
Кстати, забыл сказать - Спасибо!

(12) а выборка реально быстрее происходит?
15 Zixxx
 
12.12.11
10:12
А как выбрать только именованные ячейки?
16 aleks-id
 
12.12.11
10:14
кстати, проходят даже такие варианты
"SELECT
  [Лист1$].F1,
  [Лист2$].F2,
  [Лист1$].F2
FROM
  [Лист1$]
  INNER JOIN [Лист2$]
     ON [Лист1$].F1 = [Лист2$].F1
WHERE [Лист2$].F2<>0"
17 aleks-id
 
12.12.11
10:15
(15) HDR=YES означает что первая строка таблицы - заголовок и берет имена столбцов из нее.
18 Escander
 
12.12.11
10:16
(0)Полезный хинт - вполне может пригодиться!
19 aleks-id
 
12.12.11
10:17
(14) я не делал замеров. на больших файлах на глаз гораздо быстрее
20 andrewks
 
12.12.11
10:18
прикольно. автору респект
21 vmv
 
12.12.11
11:06
если этот провайдер(строку) подключения использовать в 8.2.14 в качестве провайдера внешнего источника, то теоретически можно будет ваять запросы без установленного эксель и без явного создания COMОбъект("ADODB.Connection") в коде

надо проверить, мне пока некогда
22 VitaliySm
 
12.12.11
11:43
только при этом нужно помнить, что все полученные значения будут строковыми. например, в Excel дата отформатирована в таком виде: 25-фев-2011, запросом ты получишь дату именно в таком виде, т.е. нужно писать функции конвертации даты из строкового представления. по поводу скорости, действительно прирост сужественный. у меня например  при обычном , построчном считывании из файла на 10000 записей уходило 10 минут, при чтении запросом - 90 сек.
23 Rie
 
12.12.11
12:27
(22) Если данные в колонке однотипные, то можно из строке подключения убрать IMEX=1 (или сказать IMEX=0).
24 aleks-id
 
12.12.11
14:32
(23) можно. там несколько доп.параметров. если на МСДН найду полное описание - прикреплю к статье
25 shuhard
 
12.12.11
15:03
(0) + 5 копеек к статье
кроме ADO есть и ADOX
например определение наименования листа:

MyCon = Новый COMОбъект ("ADODB.Connection");
   MyCon.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\1.xls;Extended Properties=""Excel 8.0;HDR=No;IMEX=1""");
   MyCat=Новый COMОбъект ("ADOX.Catalog");
   MyCat.ActiveConnection = MyCon;
   ИмяПервойСтраницы=MyCat.Tables.Item(0).Name;
   MyRst = Новый COMОбъект ("ADODB.Recordset");
   MyStr = "select * from ["+ИмяПервойСтраницы+"]";
   MyRst.Open (MyStr, MyCon, 2, 3);//adOpenDynamic, adLockOptimistic
   Пока НЕ MyRst.EOF() Цикл
       Сообщить(Строка(MyRst.Fields(MyRst.Fields.Item(0).Name).Value));    
       MyRst.MoveNext();    
   КонецЦикла;
   MyRst.Close();
   MyCon.Close();