Программисту 1С часто приходится работать с таблицами Excel из 1С. Я постарался собрать небольшой FAQ и набор функций для работы с файлами Excel. Надеюсь, кому-то будет полезна данная статья.
Очень хорошая статья, сильно помогла, особенно ссылка на 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 бит.
Для редактирования и отладки запросов к ADO можно использовать консоль запросов из подсистемы Инструменты разработчика на обычных формах 8.2.13+. Нужно выбрать тип запроса ADO, для которого будет доступен конструктор запросов (непохожий на штатный), дерево запроса, контекстная подсказка и 1с-ная подсветка синтаксиса.
На самом деле обзорчик не полный. С экселем можно работать ещё множеством разных способов. Можно, если современная версия, xml парсить, а можно SafeArray юзать. Ну и так далее.
Подскажите, кто знает - как быстро вставить гиперссылки в экселевский файл?
Через COMОбъект("Excel.Application") при большом количестве строк, в которые нужно добавить Hyperlinks очень долго работает...
Есть вообще какие-нибудь хорошие библиотеки для работы с файлами в формате xlsx помимо COMОбъект("Excel.Application"), который использовать вроде как не рекомендует Майкрософт?
Еще по группировкам данных на листе, если требуется заполнить подготовленный шаблон листа:
1. Развернуть все группы (строки и колонки):
Excel.ActiveSheet.Outline.ShowLevels(3, 3);
2. Сернуть все группы (строки и колонки) до первого уровня:
Excel.ActiveSheet.Outline.ShowLevels(1, 1);
Хорошая статья. Но вот пытаюсь на сервере рисовать диаграммы в Excel и при попытке соединения ПодключитьсяКExcelСервер(ИмяВременногоФайла) ругается: ...Open. Произошла исключительная ситуация (MS Excel). К сожалению, нам не удалось найти файл ... . Возможно, он был перемещен, переименован или удален?
Это проблема с отсутствием папки C:\Windows\SysWOW64\config\systemprofile\Desktop?
Во втором случае на сервере в строке разве в качестве параметре должно выступать Объект.ИмяФайла?
Соединение = ПоключитьсяКExcelСервер(Объект.ИмяФайла);
(39) Сразу скажу - полный бред. Смотрите вы получаете имя временого файла в лФайл, куда и сохраняется сам файл. А потом пытаетесь открыть файл, который задан в реквизите объекта ИмяФайла. Я так полагаю это файл который пользователь выбрал на КЛИЕНТЕ. Соответственно открывать нужно именно лФайл, который сохранен во временном каталоге сервера.
Да и после окончания работы с ним, неплохо бы почистить за собой - удалить временный файл с сервера.
Народ. А как в Запросе T-SQL вытащить уровень группировки? При строчном переборе я уже научился и пользуюсь, но на больших файлах сами понимаете тормоза. Буду переходить на ODBC и хотелосьбы раскидывать по папкам
>Подключаемся к OLE и октрываем файл Excel:
Есть более удобный метод получения Com объекта: "ПолучитьCOMОбъект". Из СП: Основное применение функции ПолучитьCOMОбъект - это получение COM-объекта, соответствующего файлу. Доступность: Тонкий клиент, веб-клиент, сервер, толстый клиент, внешнее соединение.
где $1 - номер первой строки, $4 - номер последней строки.
Была задача, чтобы шапка таблицы excel при выводе на печать отображалась на каждой странице. Пробовал использовать шаблон файла excel с настроенными сквозными строками, но после обработки в 1С и сохранения файла эта настройка пропадала. Долго искал как сделать эту настройку на стороне 1С - и вот нашел) Задача решена.
Может пропустил. Непонятно, неужели ни кто не сталкивался с проблемой перезаписи файла Эксель после его изменения. Вместо записи и закрытия файла, выводится системное сообщение о предложении перезаписи файла, вместо того, чтобы записаться и закрыться.
Как определить какие строки ексель заполнены? Мне требуется зачищать ексель кроме 1 строки(т.к там наименования столбцов) и загружать туда новые данные
Попытка
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 остается Ложь, и процесс екселя остается активным(не удает удалить файл, пока не грохнешь процесс). Что я делаю не так?
Вопрос, а в примере клиент-серверного варианта строчка "ОтобразитьТабНаФорме(Таб);" что обозначает? Или другими словами - что там и как реализовано в этой процедуре/функции дальше?
Если Сч > ExcelФайл.Sheets.Count Тогда
Прервать;
КонецЕсли;
ИмяЛиста = ExcelФайл.Sheets(Сч).Name;
Запрос = Новый Запрос;
Запрос.Текст = "ВЫБРАТЬ
| СписокЛистовВФайлеЭксельДляУдаления.Ссылка КАК Ссылка,
| СписокЛистовВФайлеЭксельДляУдаления.Наименование КАК Наименование
|ИЗ
| Справочник.СписокЛистовВФайлеЭксельДляУдаления КАК СписокЛистовВФайлеЭксельДляУдаления
|ГДЕ
| СписокЛистовВФайлеЭксельДляУдаления.Используется = ИСТИНА
| И СписокЛистовВФайлеЭксельДляУдаления.Наименование = &Наименование";
// сохраняем его в новый временный, для помещения во врем.хранилище на сервер
ИмяВременногоФайлаНовый = ПолучитьИмяВременногоФайла(ТипВыбранногоФайла); //"xlsx"
ExcelФайл.SaveAs(ИмяВременногоФайлаНовый);
ExcelФайл.Close();
Работа с Excel-файлами через ТабличныйДокумент удобна, довольно быстра и кроссплатформенна, я предпочитаю именно её, но, к сожалению, тут есть ограничение - метод Прочитать() недоступен в тонком клиенте. Это кажется довольно нелогичным, т.к. тонкий клиент без проблем может открывать файлы Excel просто через меню Файл/Открыть, но это так.
Второе - по поводу работы с файлами, содержащими несколько листов, через ТабличныйДокумент. При загрузке листы файла преобразуются в именованные области. К сожалению, при этом 1С делает некоторые изменения имён листов - например, пробел заменяется на символ подчёркивания. Способа получить оригинальное имя листа при загрузке файла только средствами 1С, похоже, нет.
Наконец, последнее - есть малоизвестный объект ПакетОтображаемыхДокументов. Он очень удобен, когда надо сохранить файл Excel с несколькими листами и заданием их имён. Также этот объект хорош для печати, через него можно задать имя задания печати. К сожалению, через него нельзя прочитать файл.
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. Но теперь стоит вопрос целеобразности всего этого.
Через табдок лучший способ, пожалуй...
Кроме 1С ничего не требуется
Ни экселя, ни ADODB - драйверов, которые тоже капризные
И с комом не возиться, который любит слетать при обновлении платформы
А для облак это вообще находка, там никакие Com не работают принципиально, например во фрешах...
(67) Единственный минус, не работает на тонком клиенте, нужно на сервер все гнать...
То есть, всяки эффекты во внешних обработках типа полос прогрессов чтения сделать будет проблематично, придется пользовать фоновые задания
Sh = Excel.Sheets(1); //наш COM объект
Sh.Range("K4:N" + Sh.Cells(Sh.Rows.Count, "N").End(xlUp).Row);//вычисляем последнюю заполненную ячейку в столбце "N".
В 1С не проверял, в VBA внутри самого экселя это работает.
Обход табличного документа, как по мне вообще дело не благодарное.
Используйте построитель запроса.
// Чтение данных из временного файла в табличный документ.
ТабличныйДокумент = Новый ТабличныйДокумент;
ТабличныйДокумент.Прочитать(ИмяВременногоФайла);
// Обход данных табличного документа и загрузка их(данных) в таблицу значений.
ПостроительЗапроса = Новый ПостроительЗапроса;
ПостроительЗапроса.ИсточникДанных = Новый ОписаниеИсточникаДанных(ТабличныйДокумент.Область());
ПостроительЗапроса.ДобавлениеПредставлений = ТипДобавленияПредставлений.НеДобавлять;
ПостроительЗапроса.ЗаполнитьНастройки();
ПостроительЗапроса.Выполнить();
// Получение таблицы значений
ТаблицаЗначений = ПостроительЗапроса.Результат.Выгрузить();