Как изменить значения в ячейках таблицы Excel используя технологию ADO

Форум Программирование Общие вопросы программирования (Dev)

1. lgg 20 22.12.23 05:16 Сейчас в теме

Доброго времени суток, коллеги.
Столкнулась с проблемой.
Нужно заполнить таблицу Excel по некому шаблону (довольно сложные таблицы), Нужно заполнить этот шаблон данными из документа 1С. Файлы большие и ole на некоторых не просто тормозит, а уходит и не возвращается. При помощи ADO все читается на ура. Но нужно внести новые значения в некие позиции, не меняя форматирования и пр. И тут возникает проблема. Читаю все строки листа Excel и в нужные позиции вношу изменения в соотвтетствии с документом. Если без лишних подробностей то код такой:

 	Connection = Новый COMОбъект("ADODB.Connection");
	СтрокаПодключения = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ПутьКФайлу;
	СтрокаПодключения = СтрокаПодключения + "; Extended Properties=""Excel 12.0 Xml;HDR=NO"";";				
					
	Connection.Open(СтрокаПодключения);
    Catalog = Новый COMОбъект("ADOX.Catalog");                                                
    Catalog.ActiveConnection = Connection; 			
		
	Счетчик		= 1;
	ИмяТаблицы 	= "";
	СписокЛистов  = Новый СписокЗначений;	
	Для Каждого Table ИЗ Catalog.Tables Цикл
		Если НомерЛиста = Счетчик Тогда
			ИмяТаблицы = Table.Name;
		КонецЕсли; 
		СписокЛистов.Добавить(Table.Name, "Лист " + Счетчик); 
		Счетчик = Счетчик + 1;				
	КонецЦикла;			
		
	Если ПустаяСтрока(ИмяТаблицы) Тогда
		Connection.Close();
		Возврат;
	Иначе	
		Command   = Новый COMОбъект("ADODB.Command");				
		RecordSet = Новый COMОбъект("ADODB.RecordSet");
		
        Command.ActiveConnection = Connection;					
		Command.CommandText = "SEL ECT * FR OM [" + ИмяТаблицы + "]";	
		Command.CommandType = 1;
						
		RecordSet 		  = Command.Execute();
		КоличествоКолонок = RecordSet.Fields.Count;
		
		Счетчик = 1;
		
		Пока НЕ RecordSet.EOF() Цикл
			  Если Счетчик = 1 Тогда
					RecordSet.Fields(2).Value = "ТЕСТ ADODB!!!";
					Прервать;
			  КонецЕсли;
       		RecordSet.MoveNext();
			Счетчик = Счетчик + 1;
		КонецЦикла;
    КонецЕсли;
	
Connection.Close();

Показать

Смысл всего этого - в 3-ю колонку первой строки пытаюсь записать текст "ТЕСТ ADODB!!!"
Но это не прокатывает. Ошибка "Текущий объект Recordset не поддерживает обновление. Это связано с ограничением поставщика или с выбранным типом блокировки".
Можно пойти от обратного - по данным запроса к документу (например по штрихкоду товара) искать ячейки на листе в которые нужно внести данные, но с запросом UPDATE я не справилась.
Может быть кто-нибудь делал что-либо подобное?
Прошу помощи.

По теме из базы знаний

Вознаграждение за ответ

Сумма: 0 $m

Добавили:
lgg (1.00 $m)

Получили:

lgg (1 $m)

Показать полностью

Ответы

Подписаться на ответы Инфостарт бот

Свернуть все

2. user856012 13 22.12.23 12:02 Сейчас в теме

(1)

Может быть кто-нибудь делал что-либо подобное?

Приходилось - самописная выгрузка квитанций ЖКХ для последующей загрузки в ГИС. Там надо было сформировать большую таблицу XLS по шаблону, то есть берется файл с заголовками и в него дописывается куча строчек с данными.

В памяти отложилось, что надо тщательно подбирать параметры подключения к ADO, иначе не работает. Вот рабочий кусочек кода, задача решалась еще для 7.7, но думаю, что будет понятно:

	Connection=СоздатьОбъект("ADODB.Connection");
	Connection.ConnectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+ИмяФайла+";Mode=ReadWrite;Extended Properties=""Excel 12.0;HDR=NO;IMEX=0;"""; 
	Connection.Mode=3; 
	Connection.Open();

Сразу видно главное отличие: Mode=ReadWrite, ну и IMEX=0, хотя вот он уже не помню, на что влияет.

3. lgg 20 22.12.23 13:46 Сейчас в теме

(2)
Спасибо. Увы, не помогло.
Добавление в строку подключения

Mode=ReadWrite;I

никак не повлияло на работу.

IMEX=0;

- вызвало исключение "(Microsoft Access Database Engine): Невозможно найти устанавливаемый ISAM."

5. user856012 13 22.12.23 15:23 Сейчас в теме

(3) Ну, я же говорю - c параметрами надо шаманить, и не только строки подключения - Connection.Mode, RecordSet.CursorType и т.д

Поэтому просто приведу немного больше моего кода - подключение к файлу и прогон до конца строк заголовка:

	Connection=СоздатьОбъект("ADODB.Connection");
	Connection.ConnectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+ИмяФайла+";Mode=ReadWrite;Extended Properties=""Excel 12.0;HDR=NO;IMEX=0;"""; 
	Connection.Mode=3; 
	Connection.Open(); 

	RecordSet1=СоздатьОбъект("ADODB.RecordSet");
	RecordSet1.ActiveConnection=Connection;
	RecordSet1.CursorType = 3;
	RecordSet1.CursorLocation=3;
	RecordSet1.LockType=3;
	RecordSet1.Source="SEL ECT * FR OM [Раздел 1$]";
	RecordSet1.Open();
	нПерваяСтр1=2;
	Если (RecordSet1.EOF=0) ИЛИ (RecordSet1.BOF=0) Тогда
		RecordSet1.MoveFirst();
	КонецЕсли;
	нСтр1=0;
	Пока RecordSet1.EOF=0 Цикл
		Если нСтр1>=нПерваяСтр1 Тогда
			Прервать;
		КонецЕсли;
		RecordSet1.MoveNext();
		нСтр1=нСтр1+1;
	КонецЦикла;

Показать

Дальше там только добавление и заполнение новых строк:

		Если RecordSet1.EOF<>0 Тогда
			RecordSet1.AddNew();
		КонецЕсли;
		нСтр1=нСтр1+1;
		RecordSet1.Fields(0).Value=ИдентЖКУ;
		RecordSet1.Fields(1).Value="Текущий";
		RecordSet1.Fields(2).Value=НомерПД;
		RecordSet1.Fields(21).Value=СокрЛП(Константа.ОГРН);
		RecordSet1.Fields(22).Value=Константа.КПП;
		RecordSet1.Upd ate();

Показать

И обратите внимание: изменение значений заканчивается RecordSe t1.Update(), чего я у вас не наблюдаю.

6. lgg 20 22.12.23 16:49 Сейчас в теме

(5)

RecordSet1.ActiveConnection=Connection;
RecordSet1.CursorType = 3;
RecordSet1.CursorLocation=3;
RecordSet1.LockType=3;

Эти манипуляции тоже не помогли. Глючит, как и прежде на строке
RecordSet.Fields(0).Value = "!ТЕСТ ADODB!";
Насчет RecordSe t1.Update() Вы правы. Добавила, но до этого не доходит.

9. user856012 13 22.12.23 19:46 Сейчас в теме

(6)

Эти манипуляции тоже не помогли. Глючит, как и прежде на строке
RecordSet.Fields(0).Value = "!ТЕСТ ADODB!";

А прочитать содержимое ячеек получается? Если да, то все-таки неправильно открыт файл, не с теми параметрами.

Или драйвер ADO вообще не тот - может, надо использовать не ACE, а Jet: https://infostart.ru/1c/articles/163640/

Смутно помнится, что где-то в подобной задаче выбор драйвера делал программно (в коде), а от чего он зависел - уже не помню, кажется от формата файла: XLS или XLSX. Точнее вряд ли скажу - ту обработку найти не получается.

Хотя и с Jet возможны глюки: https://forum.infostart.ru/forum9/topic259625/

10. lgg 20 22.12.23 19:57 Сейчас в теме +1 $m

(9)

А прочитать содержимое ячеек получается? Если да, то все-таки неправильно открыт файл, не с теми параметрами.

Да. Прочитать получается.

(9)

выбор драйвера делал программно (в коде), а от чего он зависел - уже не помню, кажется от формата файла: XLS или XLSX

от формата. На самом деле я это делаю

		
Если Найти(ВРег(Файл.Расширение), "XLSX") > 0  ИЛИ
			Найти(ВРег(Файл.Расширение), "XLSM") > 0 Тогда
				СтрокаПодключения = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + СтруктураПараметров.ИмяФайла;
				СтрокаПодключения = СтрокаПодключения + "; Extended Properties=""Excel 12.0 Xml;HDR=NO"";";				
		Иначе
				СтрокаПодключения = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source = " + СтруктураПараметров.ИмяФайла;
				СтрокаПодключения = СтрокаПодключения + "; Extended Properties = ""Excel 8.0;HDR=NO;IMEX=1"";";
		КонецЕсли; 
		СтрокаПодключения = СтрокаПодключения +  "Mode=ReadWrite;";

Показать

Но, тот самый "вредный" файл с расширением xlsx поэтому в данном случае дело не в глюке драйвера Jet.
Пока проблему не смогла решить.

11. independ 1521 23.12.23 10:59 Сейчас в теме

(1)варианты
1) извлечь из xlsx xml-файл с данными, подправить, запаковать обратно,
2) записать нужные данные из 1с во внешний файл (txt или xls), создать vbs макрос, открыть xlsx запустить макрос обработать данные из 1с

12. user856012 13 23.12.23 11:56 Сейчас в теме

(11)

варианты

Еще вариант - выложить тут этот "вредный файл" (если он не содержит критической информации) - глядишь, и найдутся желающие поковыряться в авторском коде.

P.S. Можно еще добавить $m - для большей заинтересованности. ;-)

4. starik-2005 3042 22.12.23 15:08 Сейчас в теме

ConString = Provider=Microsoft.ACE.OLEDB.12.0;Data Source=file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=NO";

Q =
UPD ATE [Sheet1$A2:C100] a
SE T a.F2 = "123", a.F3 = "456"
WHERE a.F1 = "XXX";

Execute(Q, , 129);

7. lgg 20 22.12.23 17:08 Сейчас в теме

(4)

Q =
UPD ATE [Sheet1$A2:C100] a
SE T a.F2 = "123", a.F3 = "456"
WHERE a.F1 = "XXX";

Спасибо.
Это мне не понятно. Скажите, а что за параметр в квадратных скобках?

8. starik-2005 3042 22.12.23 17:23 Сейчас в теме

(7)

в квадратных скобках

Область первого листа.

Оставьте свое сообщение

E-mail: