Как получить данные из файла excel

Обновлено: 04.07.2024

Бывают ситуации, когда на рабочей станции отсутствуют такие средства взаимодействия с БД как: MS SQL Server Management Studio, Aquafold Aqua Data Studio, DBeaver и т.п., а вероятность их установки в краткосрочной перспективе близка к нолю. В то же время, присутствует острая необходимость подключения к этой самой БД и работы с данными. Как оказалось, на помощь может прийти старый добрый MS Excel.

В моем случае требовалось подключиться к MS SQL Server, однако, MS Excel умеет устанавливать соединение не только с ним, но и с большинством современных БД: MySQL, PostgreeSQL, IBM DB2 и даже Oracle и Teradata, а также с файлами данных CSV, XML, JSON, XLS(X), MDB и другими.

Теперь немного о действиях, совершенных мной с целью подключения к базе:

В новой книге на ленте выбираем «(1) Данные» -> «(2) Получение внешних данных» -> «(3) Из других источников» -> «(4) С сервера SQL Server».

Далее, в окне Мастера подключения к данным, заполняем «(1) Имя сервера» -> «(2) Учетные сведения»[ -> «(3) Имя пользователя» и «Пароль»]. Таким образом, мы сообщаем MS Excel, с каким сервером мы хотим установить соединение и какой метод аутентификации хотим использовать. Я использовал «проверку подлинности Windows», но возможно также указать учетные данные отличные от установленных в Windows.

Выбираем целевую «(1) Базу данных» -> «(2)(3) Определенную таблицу» или «Несколько таблиц» или же базу в целом (тогда оба «чекбокса» оставляем пустыми).

После всех проделанных манипуляций, Мастер подключения предложит сохранить файл подключения. Потребуется задать «(1) Имя файла». Желательно также указать «(2) Описание» и «(3) Понятное имя файла», чтобы спустя время было понятно какой файл подключения к какой базе или таблице обращается.

Теперь выбрать созданное подключение можно будет следующим образом: «(1) Данные» -> «(2) Получение внешних данных» -> «(3) Существующие подключения».

Открыв только что созданное подключение, в случае если вы соединялись с базой в целом, MS Excel опять предложит выбрать одну или несколько конкретных таблиц:

Определив таблицы, MS Excel предложит выбрать «(1) Способ представления данных» и «(2) Куда следует поместить данные». Для простоты я выбрал табличное представление и размещение на уже имеющемся листе, чтобы не плодить новые. Далее следует нажать на «(3) Свойства».

В свойствах подключения, нужно перейти на вкладку «(1) Определение». Здесь можно выбрать «(2) Тип команды». Даже если требуется выгружать лишь одну таблицу без каких-либо связей, настоятельно рекомендую выбрать SQL команду, чтобы иметь возможность ограничить размер выгружаемой таблицы (например, с помощью TOP(n)). Так, если вы попытаетесь выгрузить целиком таблицу базы, это может привести в лучшем случае к замедлению работы MS Excel, а в худшем к падению программы, к тому же – это необоснованная нагрузка на сам сервер базы данных и на сеть. После того как «(3) Текст команды» будет введен и нажата кнопка «ОК», MS Excel предложит сохранить изменения запроса – отвечаем положительно.

Microsoft Excel является одним из наиболее широко используемых бизнес-приложений. Оно также является одним из наиболее распространенных способов получения данных в Power BI.

Какие типы книг поддерживает Power BI?

Power BI поддерживает импорт данных и подключение к книгам, созданным в Excel 2007 и более поздних версиях. Книги необходимо сохранять в формате XLSX или XLSM с размером не более 1 ГБ. Некоторые функции, описанные в этой статье, доступны только в более поздних версиях Excel.

Книги с диапазонами или с таблицами данных

Если книга содержит простые листы с диапазонами данных, для максимально эффективного получения данных в Power BI необходимо отформатировать эти диапазоны как таблицы. В этом случае при создании отчетов в Power BI вы увидите в области полей таблицы и столбцы с именами, что существенно облегчит визуализацию данных.

Книги с моделями данных

Книги с подключениями к внешним источникам данных

При использовании Excel для подключения к внешнему источнику данных, когда книга уже загружена в Power BI, можно создавать отчеты и информационные панели на основе данных из подключенного источника данных. Вы также можете настроить запланированное обновление, чтобы автоматически подключаться прямо к источнику данных и получать обновления. Вам больше не потребуется выполнять обновление вручную на ленте "Данные" в Excel. Все визуализации в отчетах и плитки на информационных панелях, основанные на данных из этого источника данных, будут обновляться автоматически. Дополнительные сведения см. в разделе Обновление данных в Power BI.

Книги с листами Power View, сводными таблицами и диаграммами

Отображение или отсутствие отображения листов PowerView, сводных таблиц и диаграмм в Power BI зависит от того, где сохранен файл книги и от выбранного способа его получения в Power BI. Рассмотрим это подробнее ниже.

Типы данных

Power BI поддерживает следующие типы данных: целое число, десятичное число, валюта, дата, True или False, текст. Если пометить данные как относящиеся к определенному типу в Excel, это улучшит эффективность работы Power BI.

Подготовка книги для Power BI

Просмотрите это полезное видео, чтобы узнать больше о подготовке книг Excel для Power BI.

В этом видео могут использоваться более ранние версии Power BI Desktop или службы Power BI.

Важно, где сохранен файл книги

Локально. В случае сохранения файла книги на локальный диск на компьютере или в другом расположении в вашей организации вы можете загрузить файл в Power BI. На самом деле файл останется на локальном диске, поэтому файл не импортируется в Power BI целиком. На самом деле создается новый набор данных в Power BI, и в него загружаются данные и модель данных (если она существует) из файла книги. Если книга содержит листы Power View, они будут отображаться на сайте Power BI в области "Отчеты". Excel 2016 также включает функцию публикации (в меню Файл). Функция публикации фактически аналогична получению данных из Power BI (Получить данные > Файлы > Локальный файл), но при этом часто бывает легче обновить набор данных в Power BI, если в книгу регулярно вносятся изменения.

OneDrive — бизнес. Если вы используете хранилище OneDrive для бизнеса и для входа в него используете ту же учетную запись, что и для Power BI, вне всяких сомнений, это самый эффективный способ синхронизировать вашу работу в Excel с набором данных, отчетами и информационными панелями в Power BI. Так как Power BI и OneDrive находятся в облаке, Power BI подключается к вашему файлу книги в OneDrive примерно раз в час. При обнаружении каких-либо изменений ваш набор данных, отчеты и информационные панели в Power BI автоматически обновляются. Так же, как при сохранении книги на локальном диске, можно использовать функцию публикации для немедленного обновления набора данных и отчетов в Power BI. В противном случае Power BI автоматически выполнит синхронизацию, обычно в течение часа.

OneDrive — персональный. Если вы сохраняете файлы книг в свою учетную запись OneDrive, то получите многие преимущества, доступные для OneDrive для бизнеса. Главное отличие — при первом подключении к файлу (щелкнув "Получить данные" > "Файлы" > "OneDrive — персональный") необходимо войти в OneDrive с учетной записью Майкрософт, которая обычно отличается от учетной записи, используемой для входа в Power BI. При входе в OneDrive с учетной записью Майкрософт убедитесь, что установлен флажок "Оставаться в системе". Тогда Power BI сможет примерно раз в час подключаться к вашему файлу книги и синхронизировать его с набором данных и отчетами в Power BI.

Сайты рабочих групп SharePoint. Сохранение файлов Power BI Desktop на сайтах рабочих групп SharePoint очень похоже на сохранение файлов в OneDrive для бизнеса. Главное отличие состоит в подключении к файлу из Power BI. Можно указать URL-адрес или подключаться к корневой папке.

Одна книга Excel — два способа ее использования

Если сохранить файлы книг в OneDrive, вы сможете использовать несколько способов просмотра данных в Power BI.

Импортировать данные Excel в Power BI

Если выбрать Импорт, все поддерживаемые данные в таблицах и (или) в модели данных будут импортированы в новый набор данных в Power BI. Если у вас есть листы Power View, они будут созданы повторно в Power BI как отчеты.

Вы можете продолжить редактирование в книге. При сохранении изменений они будут синхронизированы с набором данных в Power BI, обычно в течение часа. Если вам требуется более быстрый результат, можно просто повторно щелкнуть "Опубликовать", и ваши изменения экспортируются. При этом также будут обновлены все визуализации в отчетах и на панелях мониторинга на основе следующей таблицы.

Плитки отчета	Плитки панели мониторинга
Открытие отчета (после окончания срока действия кэша)	Открытие панели мониторинга (после обновления кэша)
Выбор элемента Обновить в отчете	Выбор элемента Обновить на панели мониторинга
Автоматически для закрепленных плиток (при обновлении кэша), если панель мониторинга уже открыта

Для закрепленных страниц не предусмотрена функция автоматического обновления.

В Excel 2016 можно также щелкнуть "Опубликовать" > "Экспорт". Это даст такой же результат. Дополнительные сведения см. в разделе Публикация в Power BI из Excel 2016.

Подключение к данным Excel, а также управление ими и просмотр их в Power BI

Если выбрать Подключить, ваша книга отобразится в Power BI так же, как в Excel Online. При этом у вас будет несколько удобных функций, не доступных в Excel Online, которые помогут вам закрепить элементы из листов прямо на информационных панелях.

Книгу нельзя редактировать в Power BI. Но если необходимо внести какие-либо изменения, нажмите кнопку "Изменить" и отредактируйте книгу в Excel Online или откройте ее в Excel на своем компьютере. Любые внесенные изменения сохраняются в книге в OneDrive.

Выберите этот параметр, только если хотите закрепить на информационных панелях какие-либо данные с листов, диапазоны, сводные таблицы или диаграммы.

Импорт или подключение к книге Excel из Power BI

В Power BI в области навигации щелкните Получить данные.

В разделе "Файлы" щелкните Получить.

Найдите свой файл.

Если файл книги находится в OneDrive или на сайтах рабочих групп SharePoint, выберите Импорт или Подключить.

Локальные книги Excel

Можно также использовать локальный файл Excel и загрузить его в Power BI. Просто выберите Локальный файл в предыдущем меню, а затем перейдите в расположение, где сохранены нужные книги Excel.

Выбрав книгу, отправьте файл в Power BI.

После загрузки книги вы получаете уведомление о ее доступности в вашем списке книг. Книга отобразится в области навигации в разделе Книги рабочей области, а также в списке содержимого для рабочей области.

Локальные книги Excel открываются в Excel Online в Power BI. В отличие от книг Excel, хранящихся на узлах OneDrive или SharePoint, нельзя изменять локальные файлы Excel в Power BI.

Публикация из Excel 2016 на сайт Power BI

Функция Публикация в Power BI в Excel 2016 позволяет выполнить импорт или подключиться к файлу так же, как с помощью функции получения данных в Power BI. Не будем сейчас углубляться в подробности, но дополнительные сведения доступны в разделе Публикация в Power BI из Excel 2016.

Устранение неполадок

Файл книги имеет слишком большой размер? Ознакомьтесь с разделом Уменьшение размера книги Excel для ее просмотра в Power BI.

Дальнейшие действия

Изучите данные. После передачи данных и отчетов из файла в Power BI пришло время для их изучения. Просто щелкните правой кнопкой мыши новый набор данных и выберите команду "Просмотреть". Если при выполнении шага 4 вы выбрали подключение к файлу книги в OneDrive, ваша книга отобразится в отчетах. Если щелкнуть ее, она откроется в Power BI так же, как если бы она была в Excel Online.

Установите расписание обновлений. Если файл книги Excel подключается к внешним источникам данных или был импортирован с локального диска, можно установить расписание обновлений, чтобы гарантировать актуальность набора данных или отчета. В большинстве случаев настроить расписание обновлений довольно просто, но подробное описание этой функции выходит за рамки данной статьи. Дополнительные сведения см. в разделе Обновление данных в Power BI.

Сегодня мы поговорим о получении данных из закрытых файлов Excel. В одном из предыдущих уроков рассматривалась та же задача, но с применением макроса. Теперь посмотрим как обойтись без него.

Способ №1

Воспользуемся строкой формул, это самый простой и понятный способ действий. Представим картину: есть папка с несколькими книгами Excel; в каждой книге несколько листов, они заполнены данными; эти данные нужно собрать в один общий файл.

Итак, книга "Итог" должна собрать из файла "Магазины" наименование магазина за определённый месяц и из файла "Продажи" сумму продаж за каждый месяц по магазинам.

Взглянем в строчку формул книги "Итого" на листе "январь".

Весь фокус заключается в чём - необходимо путь до папки с файлом заключить в кавычки, имя файла заключить в квадратные скобки, далее через знак восклицания указать адрес ячейки откуда хотим забрать данные.

Точно так же и с именем магазина, который подгружается из файла "Магазины".

Итог: необходимо правильно прописать имена файлов и их пути.

Способ №2

Напомню - если у вас Excel версии 2013 придётся установить этот модуль дополнительно. Если Excel 2016 он уже будет в комплекте.

Переходим на вкладку "Power Query", нажимаем кнопку "Из файла" и выбираем вариант "Из файла Excel".

В появившемся окне выбираем файл из которого нужно выгрузить информацию.

Далее выберем информацию с каких листов нужно загрузить. Щёлкаем галку "Несколько элементов" и отмечаем листы.

Нажимаем кнопку "Загрузить в" и выбираем "Таблица", будет выгружена информация на новые листы. Если хотим всё на один лист - придётся по очереди выгружать каждый лист.

Каждый лист загрузится в таблицу "Итог" и можно будет оперировать данными как угодно.

Наведя курсор в часть запроса, можно будет увидеть подробную статистику по элементу Power Query.

Вот такие два способа получения данных из закрытых файлов Excel. Всем удачи!

Достаточно часто появляется вопрос: как извлечь данные из закрытой книги Excel через VBA? Звучит может быть странновато, но это так: вопрос регулярно поднимается на форумах. Собственно, именно в связи с этим и появилась на свет данная статья. В принципе ничего сложного в задаче нет. При этом получить данные можно разными способами, в том числе при помощи функций пользователя(UDF).
Хотя если вдаваться в технические подробности, то получить данные из закрытой книги вообще нельзя. Так или иначе, на уровне системы файл все равно открывается, различие лишь в том как именно и к чему при этом предоставляется доступ. Поэтому переозвучим классическую постановку задачи в более распространенную в жизни: "Как получить данные из книги, не открывая её так, чтобы об этом узнал пользователь"

Попробуем разобраться с некоторыми методами, их плюсами и минусами:

Получение данных из закрытой книги при помощи процедуры VBA

Sub Get_Value_From_Close_Book_Formula() Dim sPath As String, sFile As String, sShName As String sPath = "C:\Documents and Settings\" '" sFile = "Книга1.xls" '" sShName = "Лист1" '" Application.DisplayAlerts = 0 With Range("A1:A100") .Formula = "='" & sPath & "[" & sFile & "]" & sShName & "'!" & "A1" '" '"A1" - указывается начальная ячейка диапазона, из которого необходимо получить значения .Value = .Value End With Application.DisplayAlerts = 1 End Sub

Данный код работает достаточно медленно, но с его помощью можно "вытащить" из закрытой книги значения сразу нескольких ячеек. Код ниже работает быстрее, но с его помощью можно извлечь значения лишь одной ячейки:

Sub Get_Value_From_Close_Book_Excel4Macro() Dim sPath As String, sFile As String, sShName As String Dim sAddress As String, vData sPath = "C:\Documents and Settings\" '" sFile = "Книга1.xls" '" sShName = "Лист1" '" sAddress = "'" & sPath & "[" & sFile & "]" & sShName & "'!" & Range("A1").Address(ReferenceStyle:=xlR1C1) '" vData = ExecuteExcel4Macro(sAddress) End Sub

Если честно, сам я не очень-то люблю ни один из данных методов, т.к. они совершенно лишены гибкости. С их помощью можно получить исключительно значения ячеек. Форматы, формулы или другие свойства ячеек получить уже не получится. Поэтому я предпочитаю открывать книгу и копировать то, что мне надо. Делаю это, скрывая от пользователя при помощи свойства ScreenUpdating объекта Application.

Sub Get_Value_From_Close_Book() Dim sShName As String, sAddress As String, vData Dim objCloseBook As Workbook 'Отключаем обновление экрана Application.ScreenUpdating = False Set objCloseBook = Workbooks.Open("C:\Documents and Settings\Книга1.xls") sAddress = "A1:C100" 'или одна ячейка - "A1" 'получаем значение vData = Sheets("Лист1").Range(sAddress).Value 'Записываем данные на активный лист книги, 'с которой запустили макрос If IsArray(vData) Then [A1].Resize(UBound(vData, 1), UBound(vData, 2)).Value = vData Else [A1] = vData End If 'если надо копировать ячейки с форматами, 'то можно использовать стандартные методы копирования вставки 'objCloseBook.Sheets("Лист1").Range(sAddress).Copy '[A1].PasteSpecial xlPasteValues 'вставляем значения '[A1].PasteSpecial xlPasteFormats 'вставляем форматы 'закрываем книгу(из которой получали значения) без сохранения objCloseBook.Close False 'Включаем обновление экрана Application.ScreenUpdating = True End Sub

Есть и более экзотический метод - при помощи GetObject:

Sub Get_Value_From_Close_Book2() Dim sShName As String, sAddress As String, vData Dim objCloseBook As Object 'Отключаем обновление экрана Application.ScreenUpdating = False Set objCloseBook = GetObject("C:\Documents and Settings\Книга1.xls") sAddress = "A1:C100" 'или одна ячейка - "A1" 'получаем значение vData = objCloseBook.Sheets("Лист1").Range(sAddress).Value 'Записываем данные на активный лист книги, 'с которой запустили макрос If IsArray(vData) Then [A1].Resize(UBound(vData, 1), UBound(vData, 2)).Value = vData Else [A1] = vData End If 'если надо копировать ячейки с форматами, 'то можно использовать стандартные методы копирования вставки 'objCloseBook.Sheets("Лист1").Range(sAddress).Copy '[A1].PasteSpecial xlPasteValues 'вставляем значения '[A1].PasteSpecial xlPasteFormats 'вставляем форматы 'закрываем книгу(из которой получали значения) без сохранения objCloseBook.Close False 'Включаем обновление экрана Application.ScreenUpdating = True End Sub

При таком подходе пользователь разницы не увидит, а действия можно производить с ячейками разные: и сравнение, и отбор по критериям, и фильтровать, и сортировать и т.д. Плюс из книги можно переносить не только значения ячеек, но и форматы, формулы. Но выбирать метод получения значений из закрытых книг вам. Все зависит от ситуации. Все указанные коды работают. Если не работают - то проверьте верно ли указаны все исходные данные(имя книги и расширение, имя листа, путь к папке с книгой).

Получение данных из закрытой книги при помощи UDF
Тот же код, что уже был рассмотрен выше, но оформленный в виде UDF(функции пользователя):

Function Get_Value_From_Close_Book(sWb As String, sShName As String, sAddress As String) Dim vData, objCloseBook As Object Set objCloseBook = GetObject(sWb) 'получаем значение vData = objCloseBook.Sheets(sShName).Range(sAddress).Value objCloseBook.Close False Set objCloseBook = Nothing 'Возвращаем данные в ячейку с функцией Get_Value_From_Close_Book = vData End Function

Синтаксис функции (вызов с листа):
=Get_Value_From_Close_Book("C:\Книга1.xls";"Лист1";"B1")
sWb - полный путь до книги, данные из которой необходимо извлечь ( "C:\Книга1.xls" )
sShName - имя листа в указанной книге, данные из которого необходимо извлечь ( "Лист1" )
sAddress - адрес ячейки(диапазона) данные которой необходимо получить ( "B1" )

Чтобы получить массив ячеек(например B1:B10), необходимо выделить необходимое количество ячеек и ввести в них эту функцию, как формулу массива.
Думаю, не надо пояснять, что любой аргумент может быть задан не статичным текстом, а ссылкой на ячейку с этим текстом. Именно в этом и преимущество использования именно функций, а не процедур.

ПОЛУЧЕНИЕ ДАННЫХ ПРИ ПОМОЩИ ЗАПРОСА ADO
Так же есть еще один достаточно экзотический метод получения данных из действительно закрытой книги - через ADO(ActiveX Data Objects). По сути это получение данных через запрос SQL, используя для этого технологию ADO.

Вызывать эту функцию следует из другой процедуры или функции. Пример процедуры, для вызова этой функции:

Для вызова функции Extract_Value_ADO непосредственно с листа(в виде функции UDF) придется несколько изменить приведенный выше код функции, либо извлекать функцией значение только одной ячейки, что будет не очень экономично с точки зрения ресурсов и использование для этого ADO будет слишком неоправданным. Если кому необходимо, то для вызова функции с ячейки листа и возврата значения одной ячейки, необходимо заменить строку:

Cells(1, 1).CopyFromRecordset objRS

Синтаксис вызова с листа в таком случае будет следующим:
=Extract_Value_ADO("C:\"; "Книга1.xls"; "Лист1"; "A1")
Важно: если данные извлекаются только из одной ячейки, то следует указать две ячейки: А1:А2 . Это особенность работы с запросами

Если же необходимо извлекать данные диапазона ячеек, то в этом случае можно применить такую функцию:

Синтаксис вызова с листа точно такой же как и в функции выше, только нужно будет выделить необходимое количество ячеек и ввести в них эту функцию, как формулу массива.:
=Extract_Value_ADO_Sh("C:\"; "Книга1.xls"; "Лист1"; "A1:B10")
sPath - путь к папке с книгой, данные из которой необходимо извлечь ( "C:\" )
sWb - имя книги, включая расширение(.xls в примере), данные из которой необходимо извлечь ( "Книга1.xls" )
sShName - имя листа в указанной книге, данные из которого необходимо извлечь ( "Лист1" )
sAddress - адрес ячейки(диапазона) данные которой необходимо получить ( "A1" )
Важно: если данные извлекаются только из одной строки, то следует все равно указать минимум две строки: А1:B10 . Это особенность работы с запросами. При попытке указать только одну строку А1:A10 функция вернет значение ошибки. При этом первая строка воспринимается как заголовки. Т.е. данные должны начинаться как минимум со второй строк(A2), а в A1 - заголовок
Хоть эта функция имеет определенные недостатки - она может быть в разы быстрее предыдущей.

Получение данных из закрытой книги при помощи Power Query

Если еще не работали с надстройкой PowerQuery и не знаете что это такое, то для начала лучше ознакомиться со статьей: Power Query - что такое и почему её необходимо использовать в работе?

Переходим на вкладку Данные(для Excel ниже 2016 вкладка PowerQuery) -Получить данные -Из файла -Из книги

Выбираем нужный лист

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

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

Клиент-Серверный вариант в управляемых формах.

1. Считываем данные из файла в массив из структур и затем этот массив передаем на сервер для дальнейших манипуляций с данными.

2-ой способ - это передать файл Excel на сервер через хранилище данных и далее работать с ним уже на сервере.

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

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

На выходе получаем двумерный массив, который содержит все данные указанного листа Excel

Полезные функции при работе с Excel:

Устанавливает видимость Excel при работе

Добавление новой книги в файл Excel

Сохранение книги Excel

Добавление нового листа к книге

Переименование листа

Изменение маштаба листа

Изменение ориентации листа

Отступы листа

Обращение к ячейки
чтение/запись

Обращение к области ячеек

Очень часто при чтении или записи значений в Excel ставятся лишние пробелы в числе, например, вместо 1502 он читает как 1 502 и в дальнейшем это значение не приводится к числу. Эту проблему можно решить заменой.

Объединение ячеек

Работа с именованными ячейками в Excel

Удаление ячейки

Удаление области ячеек

Обращение к строке

Изменение ширины колонки

Обращение к колонке

Удаление Строки

Фон ячейки / области / Строки /

Функция переводит цвет из формата RGB в формат Excel

Управление шрифтом в ячейки/строке/области

Р азрешает переносить по словам в ячейке

Управление рамкой ячейки

Устанавливаем формат ячейки

Формула в ячейки

Функция для получения ширины колонки Excel
Спасибо пользователю goodwill

Разрешить перенос слов в ячейке
Спасибо пользователю roofless

Группировки данных на листе
Спасибо пользователю dr-wit, ignor

При работе с Excel мы оперируем столбцами как числом (Например, 1 столбец), а у Excel адресация столбцов производится с помощью символов. И когда нам нужно отредактировать формулу, то нам нужно номер столбца преобразовать в символ. В таких случаях вам пригодится эта функция.

2. Работа с Excel через ТабличныйДокумент 1С

С помощью данного метода можно и загружать из Excel и выгружать в Excel. Но на мой взгляд этот метод идепально подходит когда вам необходимо посто сохранить информацию в Excel без дальнейшей манипуляции.

Итак, приступим: загрузка из Excel:

1. Загружаем файл Excel в табличный документ

2. Производим манипуляции уже с ТабличнымДокументом

Давайте теперь разберем сохранение в Excel с помощью данного метода:

Тут все очень просто сначала мы формируем обычный Табличный документ и затем записываем его в Excel

2. Работа с Excel ADODB

Выражаю особую благодарность коллеги Fragster за хороший комментарий

Данный метод позволяет работать с Excel через ODBC и имеет ряд преимуществ:

Не требует установки самой Excel, необходима лишь установить ODBC. Но как правило он уже установлен. Это особенность позволяет работать на стороне сервера без дополнительных установок Excel.
Позволяет работать с таблицой Excel как с БД и строить к ней запросы на T-SQL. Таким образом мы можем делать отборы еще на этапе чтения данных и другие преимущества что дает Т-SQL. Что на мой взгляд огромный плюс.

Подключение к ADO

Отключение от ADO

Выполнение запроса

Пример запроса:

Запись в Excel тоже производится в виде запроса:

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

ADODB предоставляет ряд объектов, с которыми мы работаем

ADODB.Recordset

В файле продемонстрированны оба варианта работы с запросами.

На этом пока все. По возможности буду дополнять статью :)

В архиве находится обработка, которая демонстрирует все описанные и другие возможности при работе с Excel.

Читайте также: