Полезные приемы при работе с Excel из 1С (Версия 3.1)

0. 656 12.09.15 22:46 Сейчас в теме
Программисту 1С часто приходится работать с таблицами Excel из 1С. Я постарался собрать небольшой FAQ и набор функций для работы с файлами Excel. Надеюсь, кому-то будет полезна данная статья.

Перейти к публикации

Вознаграждение за ответ
Показать полностью
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. Krasnyj 1232 23.09.15 09:13 Сейчас в теме
Дельная подборка. Полезная. Спасибо.
vkozak; Gendelf; Spakus; Мах; sergio199; +5 Ответить
2. Fragster 1109 23.09.15 10:24 Сейчас в теме
Забыто про UsedRange, а также про то, что передача через ComSafeArray работает в обе стороны.
SCHNEIDER GROUP; sergpogo; kovgard; psih12; +4 Ответить
3. Fragster 1109 23.09.15 10:32 Сейчас в теме
Ну и про вариант через ADO с плюшками SQL запросов типа фильтрации, группировок и джоинов данных с разных листов
KostyaLavr; _also; Zerocl; Йожкин Кот; +4 Ответить
9. Zerocl 656 30.09.15 10:05 Сейчас в теме
(3) Fragster, Добавил небольшое описание ADO
Fragster; +1 Ответить
4. Zerocl 656 23.09.15 11:58 Сейчас в теме
Спасибо за очень полезные комментарии. В ближайшее время добавлю по работе с Excel через Табличный документ и потом постараюсь осветить ваши темы.
5. roofless 24 23.09.15 15:48 Сейчас в теме
отлично, добавьте
Лист.Cells(1, 1).WrapText = 1;

разрешает переносить по словам в ячейке
arakelyan; +1 Ответить
6. Zerocl 656 23.09.15 16:52 Сейчас в теме
(5) roofless, Спасибо большое! Добавил
7. spy-83 195 24.09.15 06:10 Сейчас в теме
помоему конструкция
Excel = Новый COMОбъект("Excel.Application");

на сервере не взлетит. не?
8. roofless 24 24.09.15 07:51 Сейчас в теме
(7) spy-83, если апликейшен будет на сервере, то взлетит
гаврюша; Zerocl; +2 Ответить
24. spy-83 195 15.03.16 10:40 Сейчас в теме
(8) roofless, а если сервер на линуксе?
27. Zerocl 656 16.03.16 20:18 Сейчас в теме
(24) spy-83, На линуксе я не пробовал, но он там где то же временные файлы хранит
57. dobrynin.i.s 92 05.04.19 07:41 Сейчас в теме
(24) на линуксе не взлетит , так как нет ком объектов там
10. malets 2 30.09.15 12:07 Сейчас в теме
Спасибо большое, очень полезная статья!
11. volit 01.10.15 13:57 Сейчас в теме
Спасибо все очень понятно и подробно!
62. kotov2000 5 25.11.19 09:13 Сейчас в теме
Коллеги, подскажите.
Раз есть команда Область.Value.Выгрузить(), то, наверное, есть что-то наподобие загрузить в лист Excel?

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

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

Лист = Соединение["Листы"][0];

ВсегоКолонок = Лист.Cells(1,1).SpecialCells(11).Column;
ВсегоСтрок = Лист.Cells(1,1).SpecialCells(11).Row;

Область = Лист.Range(ЛистЭксель.Cells(1,1), ЛистЭксель.Cells(ВсегоСтрок,ВсегоКолонок));
Данные = Область.Value.Выгрузить();
12. rasswet 82 01.10.15 14:58 Сейчас в теме
опыта работы с именованными ячейками у вас нет?
если есть может есть смысл добавить сюда в статью?
13. Zerocl 656 01.10.15 15:13 Сейчас в теме
(12) rasswet, Добавил. Смотрите табличку
14. Shokei 19.11.15 13:25 Сейчас в теме
Спасибо автору ,попробуем
15. skif47 345 19.11.15 13:40 Сейчас в теме
Плюсую, для больших файлов ADO дает просто огромную скорость обработки, только им и пользуюсь.
16. dwarkin 05.02.16 14:47 Сейчас в теме
Очень хорошая статья, сильно помогла, особенно ссылка на connectionstrings.com. Спасибо большое.
Для ADO, если система 64 бит, а 1Ска - 32, то надо ставить Microsoft Access Database Engine 2010 Redistributable (16/12/2010) 32 и 64 - разрядные версии.
В моем случае помогла 32бит версия (система 64бит (Вин 7, Вин2008Ент), 1Ска - 32 бит).
На Вин7 стоял Офис 2010, но видать какой-то резанный. Все равно пришлось так же ставить Access Database Engine 32 бит.
kulak1974; +1 Ответить
17. tormozit 6663 06.02.16 15:20 Сейчас в теме
Для редактирования и отладки запросов к ADO можно использовать консоль запросов из подсистемы Инструменты разработчика на обычных формах 8.2.13+. Нужно выбрать тип запроса ADO, для которого будет доступен конструктор запросов (непохожий на штатный), дерево запроса, контекстная подсказка и 1с-ная подсветка синтаксиса.
AlexiyI; speshuric; TeMochkiN; teflon; +4 Ответить
18. Чародей 17 29.02.16 08:51 Сейчас в теме
Скачал "Демонстрация работы с Excel через Табличный документ 1С" а он тоже только для управляемых форм ((
Может дополните описание?
19. Zerocl 656 29.02.16 11:21 Сейчас в теме
(18) Чародей, Так на алгоритм работы через табличный документ не влияет.
Я учту и дополню описание. Спасибо.
20. igormiro 713 29.02.16 13:39 Сейчас в теме
Спасибо за статью.
Единственное, что я хотел уточнить, для тех кто не знает, где можно скачать ODBC ODBC .
21. babys 89 06.03.16 15:33 Сейчас в теме
Автор:
Вот это что за шняга:
//Сразу отбираем только не пустые номенклатуры
ТекстЗапроса = "SELECT * FROM [Лист1$] WHERE `Номенклатура`<>""""";

Вы когда что-то пишите, пытайтесь думать не как 80-ти летний профессор и член-корр АН.
22. Zerocl 656 09.03.16 22:54 Сейчас в теме
(21) babys, Не пойму суть вашей претензии. Это SQL запрос к Excel он вроде как по Русски не понимает
25. babys 89 15.03.16 12:17 Сейчас в теме
(22) слово "Номенклатура" не смущает, в предложении where? Меня очень. Я привык писать
[F2] <> ""НДФЛ""
26. Zerocl 656 16.03.16 20:17 Сейчас в теме
он берет(25) babys, Он берет название из первой строки. Вариант в поле рабочий. Может конечно не привычный но это просто пример :)
23. Yashazz 4340 12.03.16 20:59 Сейчас в теме
На самом деле обзорчик не полный. С экселем можно работать ещё множеством разных способов. Можно, если современная версия, xml парсить, а можно SafeArray юзать. Ну и так далее.
28. Sirruf 175 27.07.16 17:41 Сейчас в теме
Подскажите, кто знает - как быстро вставить гиперссылки в экселевский файл?
Через COMОбъект("Excel.Application") при большом количестве строк, в которые нужно добавить Hyperlinks очень долго работает...
29. Sirruf 175 27.07.16 17:49 Сейчас в теме
Есть вообще какие-нибудь хорошие библиотеки для работы с файлами в формате xlsx помимо COMОбъект("Excel.Application"), который использовать вроде как не рекомендует Майкрософт?
30. dr-wit 5 30.08.16 08:58 Сейчас в теме
Спасибо, отличная статья!

Еще по группировкам данных на листе, если требуется заполнить подготовленный шаблон листа:
1. Развернуть все группы (строки и колонки):
Excel.ActiveSheet.Outline.ShowLevels(3, 3);

2. Сернуть все группы (строки и колонки) до первого уровня:
Excel.ActiveSheet.Outline.ShowLevels(1, 1);

31. Rustam1984 12.09.16 22:58 Сейчас в теме
Спасибо,
Взял на вооружение!
32. skyvixen 32 13.09.16 10:33 Сейчас в теме
Коллеги, кто может подсказать, как сохранить в Excel гиперссылку (например, в расшифровке ячейки табличного документа 1C у меня указан WEB-адрес)
33. goodwill 20 29.11.16 17:20 Сейчас в теме
Ширина в Excel задается в символах, что не очень удобно при выводе картинок. Вот функция для перевода ширины в пикселя в ширину в символах.

&НаКлиенте
Функция ПолучитьШиринуКолнкиЭксель(ПараметрШиринаВПикселях)
	
	Если ПараметрШиринаВПикселях > 9 Тогда 
		ШиринаВСимволах = (ПараметрШиринаВПикселях/0.75-5)/7;
	Иначе
		ШиринаВСимволах = ПараметрШиринаВПикселях/9;
	КонецЕсли;

	Возврат ШиринаВСимволах;
	
КонецФункции

Лист.Columns("A").ColumnWidth = ПолучитьШиринуКолнкиЭксель(РазмерШиринаВПикселях);
Показать
69. Greek26rusa 2 03.10.20 23:14 Сейчас в теме
(33)Добрый вечер. А нету куска кода который картику вставляет? в Эксель
34. panovisp 29.11.16 17:25 Сейчас в теме
Спасибо за полезную информацю!
35. Gasilin 45 09.12.16 15:43 Сейчас в теме
Хорошая статья. Но вот пытаюсь на сервере рисовать диаграммы в Excel и при попытке соединения ПодключитьсяКExcelСервер(ИмяВременногоФайла) ругается: ...Open. Произошла исключительная ситуация (MS Excel). К сожалению, нам не удалось найти файл ... . Возможно, он был перемещен, переименован или удален?
Это проблема с отсутствием папки C:\Windows\SysWOW64\config\systemprofile\Desktop?
36. ImHunter 260 09.12.16 17:03 Сейчас в теме
Еще есть способ чтения Excel 2007-выше (*.xlsx) через XML. Ведь это зазипованный XML. В БСП это подсмотрел в обработке ЗагрузкаДанныхИзФайла.
42. su_mai 89 25.03.17 07:49 Сейчас в теме
(36) Структура файлов docx, xlsx и пр., соответствует спецификации Microsoft Open Packaging Convention, сокращенно OPC: https://en.wikipedia.org/wiki/Open_Packaging_Conventions.

По такому же принципу построены файлы комплектов бюджетной бухгалтерской отчетности 1С, для конфигураций БГУ и Свод отчетов (repx).
46. XelOla 17 30.03.17 15:54 Сейчас в теме
(42) Скажите, а можно с Эксель проработать как в статье http://infostart.blog-buh.ru/public/237032/
37. Gasilin 45 26.12.16 15:00 Сейчас в теме
Во втором случае на сервере в строке разве в качестве параметре должно выступать Объект.ИмяФайла?
Соединение = ПоключитьсяКExcelСервер(Объект.ИмяФайла);
38. roofless 24 09.01.17 10:42 Сейчас в теме
(37) код этой процедуры тут не приведен, так что вполне может быть и имя файла, если параметры подключения жестко заданы в коде
39. Gasilin 45 09.01.17 14:18 Сейчас в теме
Код процедуры есть в файле публикации.
Даю фрагмент:
&НаСервере
Процедура Загрузить2НаСервере(пФайл)

лФайл = ПолучитьИмяВременногоФайла("xlsx");
лДвоичДанные = ПолучитьИзВременногоХранилища(пФайл);
лДвоичДанные.Записать(лФайл);

Соединение = ПоключитьсяКExcelСервер(Объект.ИмяФайла);
Если Соединение = Неопределено Тогда
Возврат;
КонецЕсли;
47. =Kollega= 3 30.03.17 18:10 Сейчас в теме
(39) Сразу скажу - полный бред. Смотрите вы получаете имя временого файла в лФайл, куда и сохраняется сам файл. А потом пытаетесь открыть файл, который задан в реквизите объекта ИмяФайла. Я так полагаю это файл который пользователь выбрал на КЛИЕНТЕ. Соответственно открывать нужно именно лФайл, который сохранен во временном каталоге сервера.
Да и после окончания работы с ним, неплохо бы почистить за собой - удалить временный файл с сервера.
40. KazanKokos 9 21.03.17 09:36 Сейчас в теме
Народ. А как в Запросе T-SQL вытащить уровень группировки? При строчном переборе я уже научился и пользуюсь, но на больших файлах сами понимаете тормоза. Буду переходить на ODBC и хотелосьбы раскидывать по папкам
41. su_mai 89 25.03.17 07:45 Сейчас в теме
>Подключаемся к OLE и октрываем файл Excel:
Есть более удобный метод получения Com объекта: "ПолучитьCOMОбъект". Из СП: Основное применение функции ПолучитьCOMОбъект - это получение COM-объекта, соответствующего файлу. Доступность: Тонкий клиент, веб-клиент, сервер, толстый клиент, внешнее соединение.
43. BigBoss 2 26.03.17 10:51 Сейчас в теме
Полезно достаточно. Спасибо
44. spezc 746 26.03.17 10:56 Сейчас в теме
45. XelOla 17 30.03.17 13:55 Сейчас в теме
Скажите, а можно с Эксель проработать как в статье http://infostart.ru/public/237032/
48. su_mai 89 31.03.17 20:43 Сейчас в теме
(45) Можно только с форматом xslx.
49. pekus 25.04.17 09:12 Сейчас в теме
Можете еще добавить про установку сквозных строк:
ActiveSheet.PageSetup.PrintTitleRows = "$1:$4"

где $1 - номер первой строки, $4 - номер последней строки.

Была задача, чтобы шапка таблицы excel при выводе на печать отображалась на каждой странице. Пробовал использовать шаблон файла excel с настроенными сквозными строками, но после обработки в 1С и сохранения файла эта настройка пропадала. Долго искал как сделать эту настройку на стороне 1С - и вот нашел) Задача решена.
50. Key-Zed 108 14.09.17 11:09 Сейчас в теме
Подскажите, не могу найти метод как выделить несколько листов в книге, и сохранить к примеру в PDF.
51. creatermc 23 26.02.18 09:24 Сейчас в теме
Большой плюс автору и goodwill за материал, многое собрано в одном месте, все очень доступно и грамотно прописано
52. Bene_Valete 182 17.07.18 06:24 Сейчас в теме
Спасибо за публикацию, очень наглядно и содержательно
53. rrustam11983 01.08.18 07:00 Сейчас в теме
54. strelec13 18 24.10.18 17:46 Сейчас в теме
Может пропустил. Непонятно, неужели ни кто не сталкивался с проблемой перезаписи файла Эксель после его изменения. Вместо записи и закрытия файла, выводится системное сообщение о предложении перезаписи файла, вместо того, чтобы записаться и закрыться.
55. user790109 8 31.10.18 10:07 Сейчас в теме
Как определить какие строки ексель заполнены? Мне требуется зачищать ексель кроме 1 строки(т.к там наименования столбцов) и загружать туда новые данные
56. user790109 8 31.10.18 11:12 Сейчас в теме
Попытка
		Excel = Новый COMОбъект("Excel.Application");
		Excel.Application.DisplayAlerts = False;
		Excel.WorkBooks.Open(ИмяФайла);
		Excel.visible = Истина;

		Excel.Sheets("LRU_LET").Select();
		НС = 1;
		НК = 1;
		Excel.Range(Excel.Cells(2, 1), Excel.Cells(100, 3)).Delete();
		Для каждого стр Из ТЗ Цикл
			НС = НС + 1;
			Excel.Cells(НС, НК).Value = стр.ITEM;  
			Excel.Cells(НС, НК+1).Value = стр.DSCA;
			Excel.Cells(НС, НК+2).Value = стр.QHND;
		КонецЦикла;
		Excel.ActiveWorkBook.Save();

		Excel.DisplayAlerts = 0; 
		Excel.ActiveWorkbook.Close();
		Excel.DisplayAlerts = 1;
		Excel.Application.Quit();
		Excel = Неопределено;
	Исключение
		Сообщить("Ошибка при открытии файла с помощью Excel! Загрузка не будет произведена!");
		Сообщить(ОписаниеОшибки());
	КонецПопытки;
Показать

После выполнения, файл доступен только для чтения. В процессе заметил что visible остается Ложь, и процесс екселя остается активным(не удает удалить файл, пока не грохнешь процесс). Что я делаю не так?
58. Alex_1066 04.06.19 17:29 Сейчас в теме
Вопрос, а в примере клиент-серверного варианта строчка "ОтобразитьТабНаФорме(Таб);" что обозначает? Или другими словами - что там и как реализовано в этой процедуре/функции дальше?
59. Zerocl 656 04.06.19 21:47 Сейчас в теме
(58) Эта процедура отображает произвольную таблицу значений на форме
60. semx800 71 27.06.19 11:28 Сейчас в теме
Долго боролся с проблемой, не читался эксель в серверном варианте и проблема оказалась не в коде, а в экселе.

Решение тут https://infostart.ru/public/196323/ спасибо этому челу, и тебе конечно за статью.
Divedition; +1 Ответить
61. ignor 235 16.11.19 23:47 Сейчас в теме
Лист.Rows(Инд).OutlineLevel

Возвращает глубину для деревьев, то есть для сворачиваемых и разворачиваемых строк
63. pavel_pss 281 15.01.20 15:48 Сейчас в теме
Пример удаление листов в файле эксель. по списку из справочника Подарок :)





ДокExcel = ПолучитьCOMОбъект("", "Excel.Application");
ExcelФайл = ДокExcel.WorkBooks.Open(ИмяВременногоФайла);

Для Сч = 1 По ExcelФайл.Sheets.Count Цикл

Если Сч > ExcelФайл.Sheets.Count Тогда
Прервать;
КонецЕсли;

ИмяЛиста = ExcelФайл.Sheets(Сч).Name;

Запрос = Новый Запрос;
Запрос.Текст = "ВЫБРАТЬ
| СписокЛистовВФайлеЭксельДляУдаления.Ссылка КАК Ссылка,
| СписокЛистовВФайлеЭксельДляУдаления.Наименование КАК Наименование
|ИЗ
| Справочник.СписокЛистовВФайлеЭксельДляУдаления КАК СписокЛистовВФайлеЭксельДляУдаления
|ГДЕ
| СписокЛистовВФайлеЭксельДляУдаления.Используется = ИСТИНА
| И СписокЛистовВФайлеЭксельДляУдаления.Наименование = &Наименование";

Запрос.УстановитьПараметр("Наименование", ИмяЛиста);

Результат = Запрос.Выполнить();
Если НЕ Результат.Пустой() Тогда
ExcelФайл.Sheets(ИмяЛиста).Select();
ExcelФайл.ActiveSheet.Delete();

Сч = Сч - 1;
КонецЕсли;
КонецЦикла;

// начинаем закрывать файл
ДокExcel.DisplayAlerts = 0;

// сохраняем его в новый временный, для помещения во врем.хранилище на сервер
ИмяВременногоФайлаНовый = ПолучитьИмяВременногоФайла(ТипВыбранногоФайла); //"xlsx"
ExcelФайл.SaveAs(ИмяВременногоФайлаНовый);
ExcelФайл.Close();


ДокExcel.DisplayAlerts = 1;
ДокExcel.Quit();
ДокExcel = Неопределено;
64. snurk 8 14.02.20 03:48 Сейчас в теме
Несколько дополнений.

Работа с Excel-файлами через ТабличныйДокумент удобна, довольно быстра и кроссплатформенна, я предпочитаю именно её, но, к сожалению, тут есть ограничение - метод Прочитать() недоступен в тонком клиенте. Это кажется довольно нелогичным, т.к. тонкий клиент без проблем может открывать файлы Excel просто через меню Файл/Открыть, но это так.

Второе - по поводу работы с файлами, содержащими несколько листов, через ТабличныйДокумент. При загрузке листы файла преобразуются в именованные области. К сожалению, при этом 1С делает некоторые изменения имён листов - например, пробел заменяется на символ подчёркивания. Способа получить оригинальное имя листа при загрузке файла только средствами 1С, похоже, нет.

Наконец, последнее - есть малоизвестный объект ПакетОтображаемыхДокументов. Он очень удобен, когда надо сохранить файл Excel с несколькими листами и заданием их имён. Также этот объект хорош для печати, через него можно задать имя задания печати. К сожалению, через него нельзя прочитать файл.
65. 5H1 25.02.20 09:58 Сейчас в теме
ADODB.Command Для выполнения комманд на языке T-SQL

Как оказалось, это не совсем так. Используется язык Microsoft Access SQL. В нем нет множества полезных функций и методов, которые есть в T-SQL.

Например, я пытался добавить колонку ID (SEL ECT IDENTITY (int, 0, 1) AS ID), которая отсутствует в исходном excel-файле для того, чтобы отобрать нужные мне записи по ID, так как файл имеет достатоно много лишних полей. Как оказалось, напрямую это сделать нельзя.

Был составлен примерно такой запрос:

SEL ECT
	IDENTITY (int, 0, 1) AS ID
	, *
INTO #EXCEL
FR OM [EXCEL3]...[Page1$] AS EXCEL

SELECT * FR OM #EXCEL
WH ERE ID > 9
ORDER BY ID 

DR OP   TABLE #EXCEL
Показать


Который отлично отрабатывает в Microsoft SQL Server Management Studio 18, но не в 1С.

Если есть доступ к SQL серверу, можно открыть excel-файл запросом:

SELECT *
FR OM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;HDR=NO;Database=T:\temp\Test.xlsx',
    'select * fr om [sheet1$]')


И произвести дальнейшие манипуляции на стороне SQL сервера используя синатксис T-SQL, а не Microsoft Access SQL. Но теперь стоит вопрос целеобразности всего этого.

Если я не прав, поправьте меня.
66. noon 11 18.09.20 10:37 Сейчас в теме
Не нашел тут вроде, копирование диапазона ячеек:
исходныйДиапазон.Copy( диапазонВставки );
Может кому полезно будет.
user1189839; +1 Ответить
67. lev6975 29.09.20 12:36 Сейчас в теме
Через табдок лучший способ, пожалуй...
Кроме 1С ничего не требуется
Ни экселя, ни ADODB - драйверов, которые тоже капризные
И с комом не возиться, который любит слетать при обновлении платформы
А для облак это вообще находка, там никакие Com не работают принципиально, например во фрешах...
68. lev6975 29.09.20 12:41 Сейчас в теме
(67) Единственный минус, не работает на тонком клиенте, нужно на сервер все гнать...
То есть, всяки эффекты во внешних обработках типа полос прогрессов чтения сделать будет проблематично, придется пользовать фоновые задания
70. Азбука Морзе 102 23.10.20 16:34 Сейчас в теме
Метод получения последней строки в эксельной таблице зависит от установленных фильтров:

ВсегоСтрок = Лист.Cells(1,1).SpecialCells(11).Row;

Если в эксельной таблице установлен фильтр, то последней строкой является строка из удовлетворяющих фильтру.

Может кто знает как получить последнюю строку в таблице несмотря на наложенные фильтры?
Или как сбросить фильтр программно?
72. user852481 03.01.21 22:16 Сейчас в теме
(70)
Или как сбросить фильтр программно?

Если висят "головы" фильтров по столбцам, то:
Excel.Sheets(1).ShowAllData

с условием:
If Excel.Sheets(1).FilterMode = True Then Excel.Sheets(1).ShowAllData
71. user852481 03.01.21 21:54 Сейчас в теме
Sh = Excel.Sheets(1); //наш COM объект
Sh.Range("K4:N" + Sh.Cells(Sh.Rows.Count, "N").End(xlUp).Row);//вычисляем последнюю заполненную ячейку в столбце "N".

В 1С не проверял, в VBA внутри самого экселя это работает.
73. user852481 04.01.21 22:07 Сейчас в теме
xlUp - одна из множества констант в Excel. -4162
a = Sh.Cells(Sh.Rows.Count, "A").End(-4162).Row

в переменную попадет последняя заполненная ячейка, т.е. в которой есть хоть какие-то данные, в столбце "A". Столбец можно указывать числом.
74. kaliuzhnyi 125 25.02.21 16:43 Сейчас в теме
Обход табличного документа, как по мне вообще дело не благодарное.
Используйте построитель запроса.

	// Чтение данных из временного файла в табличный документ.
	ТабличныйДокумент = Новый ТабличныйДокумент;
	ТабличныйДокумент.Прочитать(ИмяВременногоФайла);
	
	// Обход данных табличного документа и загрузка их(данных) в таблицу значений.
	ПостроительЗапроса = Новый ПостроительЗапроса;
	ПостроительЗапроса.ИсточникДанных = Новый ОписаниеИсточникаДанных(ТабличныйДокумент.Область());
	ПостроительЗапроса.ДобавлениеПредставлений = ТипДобавленияПредставлений.НеДобавлять;
	ПостроительЗапроса.ЗаполнитьНастройки();
	ПостроительЗапроса.Выполнить();
	
	// Получение таблицы значений
	ТаблицаЗначений = ПостроительЗапроса.Результат.Выгрузить();
Показать
75. kaliuzhnyi 125 25.02.21 16:52 Сейчас в теме
(74) Вот полный и готовый шаблон загрузки и табличного документа, в данном случае из экселя.

&НаКлиенте
Процедура ЗагрузкаДанныхИзТабличногоДокумента(Команда)
	
	ДиалогВыбораФайла = Новый ДиалогВыбораФайла(РежимДиалогаВыбораФайла.Открытие);
	ДиалогВыбораФайла.Заголовок = "Выберите табличный документ";
	ДиалогВыбораФайла.Фильтр = НСтр("ru='Текстовый документ(*.xls;*.xlsx)|*.xls;*.xlsx'");
	ДиалогВыбораФайла.МножественныйВыбор = Ложь;
	
	НачатьПомещениеФайла(Новый ОписаниеОповещения("ЗагрузкаДанныхИзТабличногоДокументаНаСервере", ЭтотОбъект),,
		ДиалогВыбораФайла);

КонецПроцедуры

&НаСервере
Процедура ЗагрузкаДанныхИзТабличногоДокументаНаСервере(Результат, Адрес, ВыбранноеИмяФайла, ДополнительныеПараметры) Экспорт
	
	УстановитьПривилегированныйРежим(Истина);
	
	// Проверка результат выбора файла.
	Если Не Результат Тогда
		// Ошибка помещения файла.
		Возврат;	
	КонецЕсли;
	
	// Проверка типа данных из временного хранилища.
	ДвоичныеДанные = ПолучитьИзВременногоХранилища(Адрес);
	Если ТипЗнч(ДвоичныеДанные) <> Тип("ДвоичныеДанные") Тогда
		// Ошибка данных. Непригодный тип.
		Возврат;
	КонецЕсли;

	// Сохранение двоичных данных на сервере.
	РасширениеВременногоФайла = Сред(ВыбранноеИмяФайла, СтрНайти(ВыбранноеИмяФайла, ".", НаправлениеПоиска.СКонца));
	ИмяВременногоФайла = ПолучитьИмяВременногоФайла(РасширениеВременногоФайла);
	ДвоичныеДанные.Записать(ИмяВременногоФайла);
	
	// Чтение данных из временного файла в табличный документ.
	ТабличныйДокумент = Новый ТабличныйДокумент;
	ТабличныйДокумент.Прочитать(ИмяВременногоФайла);
	
	// Обход данных табличного документа и загрузка их(данных) в таблицу значений.
	ПостроительЗапроса = Новый ПостроительЗапроса;
	ПостроительЗапроса.ИсточникДанных = Новый ОписаниеИсточникаДанных(ТабличныйДокумент.Область());
	ПостроительЗапроса.ДобавлениеПредставлений = ТипДобавленияПредставлений.НеДобавлять;
	ПостроительЗапроса.ЗаполнитьНастройки();
	ПостроительЗапроса.Выполнить();
	
	// Получение таблицы значений с данными.
	ТаблицаЗначений = ПостроительЗапроса.Результат.Выгрузить();
	
	// Обработка таблицы значений.
	
	
КонецПроцедуры
Показать
eeeio; aguriev; +2 Ответить
78. nikolav 16.06.21 07:51 Сейчас в теме
(75) для получения первого листа книги Excel после чтения табличного документа нужно добавить
// полученине первого листа
Попытка
	ТабличныйДокумент = ТабличныйДокумент.ПолучитьОбласть(ТабличныйДокумент.Области[0].Имя);
Исключение
	Сообщить("Ошибка получения области листа");
	Возврат;
КонецПопытки;

иначе запрос будет выполнен по всем листам.

Также если на листе есть столбцы с одинаковыми именами, то после запроса их данные будут утеряны, и результаты будут кривые. Чтобы это обойти нужно переименовать заголовки в табличном документе.
ПерваяСтрока = 1; //строка заголовков (последняя перед данными)
ПерваяКолонка = 1;
ПоследняяСтрока = ТабличныйДокумент.ВысотаТаблицы;
ПоследняяКолонка = ТабличныйДокумент.ШиринаТаблицы;

// переименование столбцов
Для НомерКолонки = 1 По ПоследняяКолонка Цикл
	ТабличныйДокумент.Область(ПерваяСтрока, НомерКолонки).Текст = ("к" + НомерКолонки);
КонецЦикла;
Показать


Дальше уже в таблице значений можно задать удобные имена колонок.
ТаблицаЗначений.Колонки[0].Имя = "ИмяКолонки";
zagsa; eeeio; +2 Ответить
76. scarabey2006 17 09.04.21 17:44 Сейчас в теме
Как загрузить из листа если стоит фильтр, только те строки которые соответствуют фильтру?
77. KSM 04.05.21 13:19 Сейчас в теме
79. dadel 13 28.06.21 15:36 Сейчас в теме
Хороший труд!
Но может ещё кто расширит? Требуется выгрузить в эксель результат отчета. И пользователи его потом редактируют и он обратно загружается изменяя откорректированные пользователями данные.
Так вот, некоторые поля нужно пользователям выбирать строго из выпадающего списка, что бы вольности и многообразия с опечатками исключить.
Как этот список делается просто в эксель, понятно. Но вот как выгрузить программно результат запроса и при выгрузке проставить там возможность выбирать значения из списка - вопрос?
82. djek88 1 17.01.22 15:06 Сейчас в теме
(79)
Это решается путем программного открытия файла ecxel и добавления признака Validation в ячейку

Вот пример выпадающего списка, например, материалов, список которых прописан в листе "ЛистДиапазономДанныхВыбираемыхИзСписка" :

xlValidateList = 3;
xlValidAlertInformation = 3;
xlBetween = 1;
Формула = "="+ЛистДиапазономДанныхВыбираемыхИзСписка.Name+"!R2C1:R"+Формат((мКолвоСтрокЛиста_ЛистДиапазономДанныхВыбираемыхИзСписка),"ЧГ=0")+"C1";
Sheet.Cells(НомерСтроки, НомерКолонки).Validation.Add(
				xlValidateList,
				xlValidAlertInformation,
				xlBetween,
				Формула);
Sheet.Cells(НомерСтроки, НомерКолонки).Validation.InCellDropdown=1;
Показать


более подробно можно посмотреть в онлайн справке Excel
84. dadel 13 17.01.22 20:24 Сейчас в теме
(82) Спасибо!
Сейчас уже не помню как тогда выкрутился, но выпадающего списка не осилил точно я тогда.
80. sponchebob 8 03.08.21 07:42 Сейчас в теме
Спасибо за статью, очень полезная! По ней сделал выгрузку файла эксель!
81. ssn5810 73 14.10.21 10:52 Сейчас в теме
у меня в документе таблица (колонки 1,2,3...), как в вашем случае загрузить в колонки ? Спасибо.
83. djek88 1 17.01.22 15:08 Сейчас в теме
А кто подскажет как зафиксировать первых n-срок в листе (по-аналогии, как табличном документе)?
85. bohdan-k 20.01.22 16:21 Сейчас в теме
(83). В Excel включаете запись макроса, интерактивно выполняете необходимые действия (фиксируете первые n строк, или любое другое действие), и выключаете запись макроса. Затем заходите в записанный макрос на VBA и анализируете ).
86. SlavaKron 05.04.22 10:36 Сейчас в теме
Если файл excel открыт пользователем, то открыть его программно в 1С через табличный документ не получится – выскочит:
Ошибка совместного доступа к файлу 'ИмяФайла'. 32(0x00000020): Процесс не может получить доступ к файлу, так как этот файл занят другим процессом.
Для обхода придумал костыль, суть которого заключается в том, чтобы через ком получить открытый файл excel и с помощью метода SaveCopyAs сохранить его копию во временный файл, из которого уже грузить данные в табличный документ. При чем в копию сохраняются изменения, еще не сохраненные в оригинальном файле:
&НаКлиенте
Процедура ПослеВыбораФайла(ВыбранныеФайлы, ДополнительныеПараметры) Экспорт
	
	Если ВыбранныеФайлы <> Неопределено Тогда
		ОП = Новый ОписаниеОповещения("ПослеПомещенияФайлаНаСервер", ЭтотОбъект);
		ПутьКФайлу = ВыбранныеФайлы[0];
		РезультатЧтения.Очистить();
		Попытка
			НачатьПомещениеФайлаНаСервер(ОП, , , , ПутьКФайлу, УникальныйИдентификатор);
		Исключение
			КомЭксель = ПолучитьCOMОбъект(ПутьКФайлу, "Excel.Application");
			Расширение = Сред(ПутьКФайлу, СтрНайти(ПутьКФайлу, ".", НаправлениеПоиска.СКонца) + 1);
			ИмяВременногоФайла = ПолучитьИмяВременногоФайла(Расширение);
			КомЭксель.SaveCopyAs(ИмяВременногоФайла);
			КомЭксель = Неопределено;
			НачатьПомещениеФайлаНаСервер(ОП, , , , ИмяВременногоФайла, УникальныйИдентификатор);
		КонецПопытки;
		
	КонецЕсли;
	
КонецПроцедуры
Показать
87. alexander-lubich 16 10.06.22 12:13 Сейчас в теме
Привет ,
Метод выгрузки файла в Табдок ограничен, я получаю не все возможности как если при работе через COM (тот который требует установки Ексель)

через com я могу получить путь гиперссылки на какой либо файл используя код Лист.Cells(n,3).Hyperlinks.Item(1).Address

при работе через Табдок часть свойств ячеек ексель не доступна.

при использовании ADODB возможность вынуть гиперссылку из ячейки сохраняется или вся его "модность" в том что не надо ексель ставить и отборы в него кинуть сразу по Т-SQL ?
88. JaSergey 10.08.22 10:23 Сейчас в теме
Всем привет!
Есть такая проблема, с 1С я на вы, а с Excel тем более, но потребовалось создать внешний отчет для 1С с выгрузкой в Excel для руководства.
Условия: создать файл на нескольких листах. В них выгружаются данные из 1С с указанием даты операций в 1С. Основное условие, чтобы при добавлении информации, оставалось видно, только информация по свежим датам.
Может, я плохо умею пользоваться поисковиком, но я не смог найти метод создания автофильтра по условию с типом дата, а тем более когда нужно отфильтровать по нескольким датам.

Прочитал справочник VBA для Office (https://docs.microsoft.com/ru-ru/office/vba/api/excel.xlautofilteroperator) написал такой код:
УсловиеФильтраВычерков = Новый Массив;
ТекЛист.Range(ТекЛист.Cells(1,1),ТекЛист.Cells(АктивнаяСтрока,ВсегоКолонок)).AutoFilter();
ТекЛист.Range(ТекЛист.Cells(1,1),ТекЛист.Cells(АктивнаяСтрока,ВсегоКолонок)).AutoFilter();
УсловиеФильтраВычерков.Добавить(2);
УсловиеФильтраВычерков.Добавить(Формат(Дата(2022,03,03),"ДФ=М/д/гггг"));
ТекЛист.Range(ТекЛист.Cells(1,1),ТекЛист.Cells(АктивнаяСтрока-1,ВсегоКолонок)).AutoFilter(1,,7,УсловиеФильтраВычерков);

Не работает. Помогите решить проблему!
P.S. Руководство хочет только распечатать(без манипуляций с фильтрами, т.к. совсем никак с работой на компьютере(странно, но такие еще остались)) и идти к своему руководству.
89. JaSergey 10.08.22 11:15 Сейчас в теме
(88) Забыл уточнить. Платформа 8.2.13... Обычные формы.

Текст ошибки:
{Форма.Форма.Форма(226)}: Ошибка при вызове метода контекста (AutoFilter)
ТекЛист.Range(ТекЛист.Cells(1,1),ТекЛист.Cells(АктивнаяСтрока,ВсегоКолонок)).AutoFilter();
по причине:
Неизвестная ошибка

В Excel запуск Макроса делал. Оттуда формат даты взял такой.
90. user1428658 16.08.22 12:39 Сейчас в теме
Оставьте свое сообщение
Вакансии
Консультант-аналитик 1С
Нижний Новгород
зарплата от 100 000 руб.
Полный день

Разработчик 1С
Нижний Новгород
зарплата от 100 000 руб.
Полный день

Программист 1С
Екатеринбург
зарплата до 150 000 руб.
Полный день

Разработчик 1С
Санкт-Петербург
зарплата от 130 000 руб. до 170 000 руб.
Временный (на проект)

Программист, аналитик, эксперт 1С
Санкт-Петербург
По совместительству