Передача данных из excel в excel

Обновлено: 07.07.2024

В этой статье приводится информация о подключении, которую вы должны предоставить, и необходимые настройки подключения для импорта данных из Excel и экспорта данных в Excel с помощью SQL Server Integration Services (SSIS).

В следующих разделах содержится вся информация, которая потребуется для успешной работы с приложением Excel из служб SSIS, а также для понимания и устранения распространенных проблем:

Средства, которые можно использовать.

Файлы, которые вам потребуются.

Сведения о подключении, которые необходимо предоставить, и параметры, которые необходимо настроить для загрузки данных в/из Excel с использованием SSIS.

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

Известные проблемы и ограничения.

Проблемы с типами данных.
Проблемы с импортом.
Проблемы с экспортом.

Средства, которые можно использовать

Вы можете импортировать и экспортировать данные в Excel с использованием SSIS при помощи любого из следующих инструментов.

SQL Server Integration Services (SSIS) . Создайте пакет SSIS, настроив для него Excel в качестве источника или назначения с помощью диспетчера подключений Excel. (В этой статье не рассматривается создание пакетов SSIS.)

Получение файлов, необходимых для подключения к Excel

Прежде чем импортировать или экспортировать данные в Excel с использованием SSIS, вам может потребоваться скачать компоненты подключения для Excel, если они еще не установлены. По умолчанию компоненты подключений для Excel не устанавливаются.

Примечания по загрузке и установке

Обратите внимание, что необходимо скачать распространяемый пакет ядра СУБД Access 2016, а не среду выполнения Microsoft Access 2016.

Если на компьютере уже установлена 32-разрядная версия Office, необходимо установить 32-разрядную версию компонентов. Кроме того, следует выполнять пакет служб SSIS в 32-разрядном режиме или 32-разрядную версию мастера импорта и экспорта.

При возникновении проблем с установкой распространяемого пакета версии 2016 установите вместо него распространяемый пакет версии 2010 со следующей страницы: Распространяемый пакет ядра СУБД Microsoft Access 2010. (Для Excel 2013 распространяемый пакет не предусмотрен.)

Назначение Excel в качестве источника данных

Сначала необходимо указать, что вам требуется установить подключение к Excel.

В службах SSIS

В службах SSIS создайте диспетчер соединений Excel для подключения к файлу источника или назначения Excel. Создать диспетчер соединений можно несколькими способами:

Щелкните правой кнопкой в области Диспетчеры соединений и выберите команду Создать соединение. В диалоговом окне Добавление диспетчера соединений со службами SSIS выберите EXCEL и затем Добавить.

В меню Службы SSIS выберите Создать соединение. В диалоговом окне Добавление диспетчера соединений со службами SSIS выберите EXCEL и затем Добавить.

Диспетчер соединений необходимо создавать в то время, как вы настраиваете источник Excel или назначение Excel на странице Диспетчер соединений в редакторе источника Excel или в редакторе назначения Excel.

В мастере импорта и экспорта SQL Server

В мастере импорта и экспорта на странице Выбор источника данных или Выбор назначения выберите Microsoft Excel в списке Источник данных.

Если вы не видите Excel в списке источников данных, убедитесь, что используется 32-разрядная версия мастера. Компоненты подключений для Excel обычно представляют собой 32-разрядные файлы и не отображаются в 64-разрядной версии мастера.

Файл Excel и путь к нему

В первую очередь необходимо предоставить имя файла Excel и путь к нему. Это можно сделать в редакторе диспетчера соединений Excel в пакете служб SSIS либо на странице Выбор источника данных или Выбор назначения в мастере импорта и экспорта.

Введите имя файла и путь к нему в следующем формате:

Для файла на локальном компьютере: C:\TestData.xlsx.

Для файла в общей сетевой папке: \\Sales\Data\TestData.xlsx.

Также можно нажать кнопку Обзор и выбрать электронную таблицу в диалоговом окне Открыть.

Подключить защищенный паролем файл Excel нельзя.

Версия Excel

Далее следует предоставить сведения о версии файла Excel. Это можно сделать в редакторе диспетчера соединений Excel в пакете служб SSIS либо на странице Выбор источника данных или Выбор назначения в мастере импорта и экспорта.

Выберите версию Microsoft Excel, в которой был создан файл, или другую совместимую версию. Например, если вам не удалось установить компоненты подключений для версии 2016, вы можете установить компоненты для версии 2010 и выбрать в этом списке вариант Microsoft Excel 2007-2010.

Если у вас установлены старые версии компонентов подключений, вы не сможете выбрать более новые версии Excel в этом списке. Список Версия Excel содержит все версии Excel, поддерживаемые службами SSIS. Наличие элементов в этом списке не означает, что необходимые компоненты подключений были установлены. Например, вариант Microsoft Excel 2016 будет присутствовать в этом списке даже в том случае, если у вас не установлены компоненты подключений для версии 2016.

Первая строка содержит имена столбцов

Если вы импортируете данные из Excel, далее необходимо указать, содержит ли первая строка данных имена столбцов. Это можно сделать в редакторе диспетчера соединений Excel в пакете служб SSIS или на странице Выбор источника данных в мастере импорта и экспорта.

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

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

Листы и диапазоны

В качестве источника или назначения для данных можно использовать три типа объектов Excel: лист, именованный диапазон или неименованный диапазон ячеек, который задается с помощью адреса.

Лист Чтобы указать лист, добавьте в конец имени листа символ $ и окружите строку разделителями, например [Листt1$] . Также можно выполнить поиск имени, заканчивающегося символом $ , в списке существующих таблиц и представлений.

Именованный диапазон Чтобы указать именованный диапазон, используйте имя диапазона, например Мой_диапазон. Также можно выполнить поиск имени, не заканчивающегося символом $ , в списке существующих таблиц и представлений.

Неименованный диапазон Чтобы указать диапазон ячеек, которым не были заданы имена, добавьте символ $ после имени листа, добавьте спецификацию диапазона и окружите строку разделителями, например [Лист1$A1:B4] .

Чтобы выбрать или указать тип объекта Excel, который вы хотите использовать в качестве источника или назначения для данных, выполните одно из следующих действий:

В службах SSIS

В службах SSIS на странице Диспетчер соединений в редакторе источника Excel или редакторе назначения Excel выполните одно из следующих действий:

Чтобы использовать лист или именованный диапазон, выберите Таблица или представление в разделе Режим доступа к данным. Затем выберите лист или именованный диапазон в списке Имя листа Excel.

Чтобы использовать неименованный диапазон, который задается по адресу, выберите Команда SQL в разделе Режим доступа к данным. Затем в поле Текст команды SQL введите запрос, аналогичный представленному ниже примеру:

В мастере импорта и экспорта SQL Server

В мастере импорта и экспорта выполните одно из следующих действий:

При импорте из Excel выполните одно из следующих действий:

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

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

При экспорте в Excel выполните одно из следующих действий:

Чтобы использовать лист или именованный диапазон, на странице Выбор исходных таблиц и представлений в столбце Назначение выберите целевые листы и именованные диапазоны.

Чтобы использовать неименованный диапазон, который задается по адресу, на странице Выбор исходных таблиц и представлений в столбце Назначение укажите диапазон в следующем формате без использования разделителей: Sheet1$A1:B5 . Мастер автоматически добавит разделители.

После выбора или ввода объектов Excel для импорта или экспорта вы также можете выполнить следующие действия на странице Выбор исходных таблиц и представлений мастера:

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

Выполнить предварительный просмотр данных, чтобы убедиться в правильности выбора, с помощью команды Предварительный просмотр.

Проблемы с типами данных

Типы данных

Драйвер Excel распознает только ограниченный набор типов данных. Например, все числовые столбцы воспринимаются как тип double (DT_R8), а все строковые столбцы (кроме столбцов типа memo) воспринимаются как строки в Юникоде длиной 255 символов (DT_WSTR). Службы SSIS сопоставляют типы данных Excel следующим образом:

Числовой — с плавающей запятой двойной точности (DT_R8)

Денежный — денежный (DT_CY)

Логический — логический (DT_BOOL)

Дата и время — datetime (DT_DATE)

Строковый — строка в Юникоде длиной в 255 символов (DT_WSTR)

Memo — текстовый поток в Юникоде (DT_NTEXT)

Преобразования типов данных и длины по умолчанию

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

Ниже приведены некоторые примеры преобразований, которые могут потребоваться:

Преобразование между строковыми столбцами Excel в Юникоде и строковыми столбцами в формате с конкретными кодовыми страницами, отличными от Юникода.

Преобразование между строковыми столбцами Excel длиной в 255 символов и строковыми столбцами другой длины.

Преобразование между числовыми столбцами Excel с плавающей запятой двойной точности и числовыми столбцами других типов.

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

Проблемы с импортом

Пустые строки

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

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

Отсутствующие значения

Драйвер Excel считывает определенное количество строк (по умолчанию 8 строк) в указанном источнике для определения типа данных каждого столбца. Если столбец содержит смешанные типы данных, особенно если числовые данные смешаны с текстовыми данными, драйвер принимает решение в пользу того типа данных, которого больше, и возвращает значения NULL в ячейки, содержащие данные другого типа. (В случае равенства преимущество получает числовой тип.) Большинство параметров форматирования ячеек в листе Excel не затрагивает это определение типа данных.

Можно изменить поведение драйвера Excel, указав режим импорта для импорта всех значений в виде текста. Чтобы указать режим импорта, добавьте IMEX=1 к значению расширенных свойств в строке соединения диспетчера соединений с Excel в окне "Свойства".

Усеченный текст

Когда драйвер определяет, что столбец Excel содержит текстовые данные, он выбирает тип данных (строковый или memo) на основании самого длинного значения. Если драйвер не обнаруживает значений длиннее 255 символов в выбираемых строках, он считает, что столбец является строковым с длиной 255 символов, а не столбцом типа memo. Поэтому значения длиннее 255 символов могут быть усечены.

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

Убедитесь, что столбец типа memo как минимум в одной из выбранных строк содержит значение длиной более 255 символов.

Увеличьте число строк в выборке драйвера, чтобы включить такую строку. Чтобы увеличить количество строк, включаемых в выборку, достаточно увеличить значение TypeGuessRows в следующем разделе реестра:

Проблемы с экспортом

Создание нового файла назначения

В службах SSIS

Создайте диспетчер соединений Excel с именем создаваемого файла Excel и путем к нему. Затем в редакторе назначения Excel в разделе Имя листа Excel выберите Создать, чтобы создать лист назначения. В этот момент службы SSIS создают новый файл Excel с указанным листом.

В мастере импорта и экспорта SQL Server

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

Экспорт в достаточно большой диапазон

Экспорт длинных текстовых значений

Чтобы успешно сохранять в столбцы Excel строки, имеющие длину более 255 символов, драйвер должен распознать тип данных целевого столбца как memo , а не как string.

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

См. также

Дополнительные сведения о компонентах и процедурах, описываемых в этой статье, см. в следующих статьях:

— получаем строчку из 16 символов(байт), в подпрограмме обработки этого события(NETComm1_OnComm())
разбираем ее райтами и лефтами на четверки байт и пихаем в соответствующие ячейки. После двадцати измерений порт отключается. Тут тонкое место, в передаваемых байтах первая тетрада должна быть равна 3(в кодировке ASCII зто значит число), тогда Excel все правильно понимает, выбрасывает нули спереди и считает данные числом. Четверки байт появились из-за того, что результат преобразования используемого десятиразрядного АЦП четырехзначен (0-1023) и при его разборе получаются четыре цифры. Понятно что и с например, двенадцатиразрядным АЦП будет тоже самое. Ну и результат-)

Перемещение данных из Excel в Access

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке) .

В этой статье показано, как перенести данные из Excel в Access и преобразование данных в реляционных таблиц, чтобы можно было использовать Microsoft Excel и Access друг с другом. Для подведения итогов, лучше всего подходит для записи, сохранения, запросы и общий доступ к данным Access и Excel лучше всего подходит для расчета, анализа и визуализации данных.

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

При перемещении данных из Excel в Access существует три основных этапов процесса.

Примечание: Подробнее о моделирования данных и связи в приложении Access: Основные сведения о создании баз данных.

Шаг 1: Импорт данных из Excel в Access

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

Перед импортом очистки данных

Перед импортом данных в Access, в Excel это хороший способ:

С помощью команды СЖПРОБЕЛЫ для удаления начальные, конечные и нескольких пробелы.

Удалите непечатаемые символы.

Обнаружение и исправление ошибок правописания и знаки пунктуации.

Удаление повторяющихся строк или повторяющиеся поля.

Убедитесь, что столбцы данных не содержат смешанные форматов, особенно числа, отформатированные как текст или числа в формате даты.

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

Примечание: Если очистка потребностей данные сложны или у вас нет времени и ресурсов для автоматизации процесса собственные, можно с помощью стороннего поставщика. Для получения дополнительных сведений искать «программное обеспечение очистки данных» или «качество данных» с любой поисковой системы в веб-браузере.

Выбор оптимального типа данных при импорте

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

Числового формата Excel

Тип данных Access

Текстовое поле Memo

Тип данных текст Access сохраняет буквенно-цифровые данные до 255 знаков. Тип данных МЕМО Access сохраняет буквенно-цифровые данные до 65535 знаков.

Выберите команду Записка во избежание ошибок усечения все данные.

Число, процент, дроби, научных

Access имеет один числовой тип данных, которая зависит от на основе размер поля свойства (байтовое целое число, длинное целое одного двойной, десятичные).

Выберите двойной во избежание ошибок преобразования данных.

Access и Excel для хранения дат и используется то же число последовательных даты. В приложении Access, больше диапазон дат: от от -657434 (1 января 100 г. н.э.) до 2 958 465 (31 декабря 9999 г. н.э.).

Так как Access не распознает система дат 1904 (используется в Excel для компьютеров Макинтош), необходимо преобразование дат в Excel или Access, чтобы избежать путаницы.

Access и Excel оба содержат значения времени с помощью одинаковый тип данных.

Выберите время, обычно используется по умолчанию.

Выберите параметр Денежный, обычно используется по умолчанию.

Доступ к использует -1 для всех значений Да и значение 0 для без значения, тогда как Excel использует 0 и 1 для всех TRUE значения для всех значений FALSE.

Выберите Да/Нет, которая автоматически преобразует исходные.

Гиперссылки в Excel и Access содержит URL-адрес или веб-адрес, который можно щелкнуть и следуйте.

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

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

Дополнительные сведения приведены в разделе справки Access Импорт или связывание данных в книге Excel.

Простой способ автоматически добавлять данные

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

Лучше всего использовать Access, где можно легко импортировать и добавлять данные в одной таблице с помощью мастера импорта электронной таблицы. Кроме того можно добавить большой объем данных в одной таблице. Сохранение операции импорта, добавьте их в качестве задачи, запланированные Microsoft Outlook и даже с помощью макросов для автоматизации процесса.

Шаг 2: Нормализация данных с помощью мастера анализа таблиц

На первый взгляд пошаговом процесс Нормализация данных может показаться сложной задачей. К счастью нормализация таблиц в Access выполняется намного проще, Спасибо мастер анализа таблиц.

1. Перетащите выделенные столбцы в новую таблицу и автоматическое создание связей

2. с помощью кнопки команды переименовать таблицы, Добавление первичного ключа, сделать существующего столбца первичного ключа и Отмена последнего действия

Этот мастер можно использовать для выполнения следующих действий:

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

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

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

Создание резервной копии исходной таблицы и переименуйте его путем добавления «_OLD» его имени. Создайте запрос, который восстанавливает исходной таблицы с исходным именем таблицы, чтобы любой существующий форм и отчетов на основе исходной таблицы совместимы с новой структуры таблиц.

Шаг 3: Подключение к данным Access из Excel

После были нормализовать данных в Access и запрос или таблица была создана, восстанавливает исходные данные, это просто о соединении для доступа к данным из Excel. Теперь в Access как внешнего источника данных и данных, могут быть подключены к книге через подключение к данным, — это контейнер, который используется для поиска информации, войдите в систему и получить доступ к внешнему источнику данных. Сведения о подключении хранится в книге, а также могут быть сохранены в файл подключения, например файл подключения к данным Office (ODC) (.odc с расширением) или имя источника данных (с расширением .dsn). После подключения к внешним данным, можно также автоматического обновления (или) книгу Excel из Access при каждом обновлении данных в Access.

Для получения дополнительных сведений ознакомьтесь со статьей Обзор подключении (импорте) данных.

Получение данных в Access

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

Пример данных в форму без нормализовать

Следующий лист содержит-элементарной значений в столбцах Продавец и адрес. Столбцы, следует разбить на несколько отдельных столбцов. В этом примере также содержит сведения о продавцов, продукты, клиентах и заказах. Эти сведения должны быть разделение дальнейшей, по темам, по отдельным таблицам.

Четыре способа экспорта и передачи данных в Excel

Верси для печати

Описание проблемы:

Я хотел бы получать данные по интерфейсу RS232 и передавать их в Microsoft Excel для дальнейшего использования и анализа.

Требования:

Advanced Serial Data Logger Professional или пробная версия;
Некоторые дополнительные модули для этого ПО.

Подразумевается что:

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

Решение:

Наша программа позволяет передавать данные из RS232 в Excel следующими путями:

Не требует установленного Excel на рабочем компьютере;
Может создавать файлы Excel в фоновом режиме.

Пересоздает файл каждый раз при приеме новых данных, поэтому требуется относительно больше ресурсов компьютера, если поток данных быстр;
Невозможно экспортировать данных из нескольких портов в один файл.

Прочитайте этот пример с использованием данного метода.

Позволяет записывать данные в любые колонки и строки;
Очень удобно, если требуется наблюдать данные из RS232 в реальном времени.

Требуется установленный Excel на рабочем компьютере. Если вы добавите графики или диаграммы в файл Excel, то этот способ будет очень медленный.

Прочитайте этот пример об использовании данного метода.

Отправка данных из RS232 в Excel с помощью интерфейса DDE

Позволяет записывать данные в любые колонки и строки;
Очень удобно, если требуется наблюдать данные из RS232 в реальном времени.
Excel автоматически запускает логгер;
Позволяет использовать логгер на удаленном компьютере (в этом случае используется DDE через сеть);
Позволяет обрабатывать данные RS232 в Excel с помощью кода VBA.

Требуется установленный Excel на рабочем компьютере. Если вы добавите графики или диаграммы в файл Excel, то этот способ будет очень медленный;
Требуются знания VBA.

Прочитайте этот пример об использовании данного метода.

Получение данных из RS232 в Excel с помощью нашего компонента ActiveX

Позволяет обрабатывать данные как вам хочется;
Позволяет передавать необработанные данные (RAW) из RS232 в Excel;
Позволяет принимать уведомления о событиях программы;
Позволяет передавать данные из Excel в RS232;
Excel автоматически запускает логгер;
Очень удобно, если требуется наблюдать данные из RS232 в реальном времени;
Позволяет обрабатывать данные RS232 в Excel с помощью кода VBA.

Требуется установленный Excel на рабочем компьютере.
Требуются хорошие знания VBA;
Компонент ActiveX платный и для него необходима дополнительная лицензия.

Скачайте ActiveX компонент по этой ссылке после установки компонента, пример и файл справки по нему будут в папке с компонентом.

Как из Excel обратиться к другому приложению

Tips_Macro_OpenWord.xls (49,5 KiB, 4 079 скачиваний)

В принципе, активировать или вызвать(если закрыто) другое приложение Офиса можно одной строкой:

Sub Open_AnotherApp() Application.ActivateMicrosoftApp xlMicrosoftWord End Sub

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

Затем объявляем переменную и присваиваем ей тип нужного приложения:

Sub OpenWord() Dim objWrdApp As Word.Application Set objWrdApp = New Word.Application objWrdApp.Visible = True End Sub

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

Так же можно нажать F2 и через поиск найти Word и просмотреть все методы и свойства данного приложения.

Главная ошибка новичка
И хочу так же упомянуть про ошибку, которую очень часто совершают при обращении к одному приложению из другого. Допустим, необходимо скопировать из Word все данные в Excel. Часто начинающие делают это так:

Вместо Range ту же ошибку делают и с Selection(потому что Selection часто присутствует в записанных макрорекордером макросах), т.к. этот объект есть и в Excel и в Word и без явного указания приложения будет относится к приложению, в котором записано.

Tips_Macro_OpenWord.xls (49,5 KiB, 4 079 скачиваний)

Статья помогла? Поделись ссылкой с друзьями!

Передача данных в Excel из arduino

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

Как передавать данные из arduino на компьютер

Настройка PLX DAQ для excel

После установки дополнения PLX DAQ, на рабочем столе появится ярлык «PLX-DAQ Spreadsheet». Нужно его запустить, тогда откроется лист excel с уже запущенным макросом. Для связи с arduino достаточно указать, какой нужно слушать com-порт, и на какой скорости происходит передача данных. Скорость передачи должна соответствовать той, что указана в arduino: Serial.begin(9600);
После настройки порта и скорости передачи данных нужно нажать кнопку «connect» и сразу же начнется получение и вывод данных из arduino. Пример работы можно посмотреть на скриншоте ниже.

Что использовалось в проекте:

Arduino (я использовал arduino uno, но можно любую другую). Покупал тут: arduino uno

Тестовый скетч для передачи данных в Excel из arduino

Ниже приведен скетч с подробными комментариями, а также его можно скачать себе на компьютер: скачать.

Послесловие

И в заключение приведу короткий ролик, который показывает, как в экселе выводятся данные, полученные от термометра. И на их основе строится график с двумя переменными – для комнатной и уличной температуры:

Передача данных в Excel из arduino: 1 комментарий

Здравствуйте. Вы бы не могли подсказать как принимать данные с датчика влажности и температуры в exel?. Не могу разобраться

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

Все инструменты для импорта данных расположены на ленте на вкладке "Данные".

В новых версиях Excel по умолчанию встроена надстройка Power Query, которая и занимается импортом всех данных из внешних источников (пример показывали тут ). В более ранних версиях использовался специальный мастер импорта (пример был тут ). Если Вы привыкли именно к нему, то включить его в новой версии Excel можно пройдя по пути "Файл"-"Параметры"-"Данные" и поставив галочку на пункте "Из интернета (прежних версий)"

Как Вы уже догадались, для импорта данных из мировой паутины нужно найти команду "Из интернета", после чего перед вами откроется окно старого мастера импорта или новое окно Power Query. Разберем оба способа.

Старый мастер импорта данных из интернета

Если у Вас Excel 2013 или более старый, то этот мастер откроется по умолчанию при выборе команды "Из интернета" на вкладке "Данные". Если у Вас Excel 2016, то после того, как Вы включили старый мастер в настройках, как показано выше, он будет доступен по следующему пути: "Данные"-"Получить данные"-"Устаревшие мастеры"-"Из интернета (прежних версий)".

После запуска откроется окно "Создание веб-запроса". Оно похоже на стандартное окно браузера. В адресную строку вверху нужно ввести URL страницы, с которой необходимо выгрузить данные. После того, как страница будет загружена, все данные, которые Excel может импортировать, будут помечены небольшой желтой стрелкой. Кликните по ней и нажмите кнопку "Импорт" в правом нижнем углу. Если на странице нет желтых стрелок или они расположены не там, где нужно, то данным способом выгрузить данные в Excel не получится.

До нажатия кнопки импорт, можете задать некоторые настройки, кликнув на кнопке "Параметры" в правом верхнем углу. Наиболее полезной здесь является настройка "Отключить распознавание дат". Она позволяет избежать частой ошибки, вызванной особенностями региональных настроек системы, когда число в формате "2.15" выгружается в Excel как "фев.15".

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

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

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

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

2. Импорт данных с помощью Power Query

После нажатия кнопки "Из интернета" в Excel 2016 Вы увидите не окно "Создание веб-запроса", а маленькое окошко, в котором нужно будет указать адрес страницы.

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

Если результат предпросмотра Вас устраивает, жмите кнопку "Загрузить". Если нет, то можно нажать кнопку "Правка". Тогда Вы попадете в окно редактора Power Query. Здесь можно настроить обработку данных после получения их из Интернета и перед выгрузкой на лист. Например, удалить лишние столбцы, изменить заголовки или поменять регистр текста.

После того, как данные примут нужный вид, нажмите кнопку "Закрыть и загрузить" на вкладке "Главная" в редакторе запросов.

Данные будут выгружены на новый лист. Обратите внимание, что импортированная таблица будет автоматически преобразована в "умную таблицу", а в списке запросов ("Данные" - "Запросы и подключения") появится новый запрос.

Обновить его можно кликнув правой кнопкой мыши и выбрав команду "Обновить", либо нажав на иконку в правом верхнем углу рядом с названием запроса. Там же (в контекстном меню по правой кнопке мыши) есть команда "Свойства", с помощью которой можно, например, настроить автообновление запроса.

В отличие от старого мастера импорта, Power Query имеет ряд преимуществ:

1) Может работать с гораздо большим числом сайтов и страниц;

2) Может осуществлять промежуточную обработку данных перед тем, как они будут выгружены на лист;

3) Выгружает данные из Интернета гораздо быстрее;

4) Автоматически создает "Умную таблицу".

Учитывая всё вышесказанное, настоятельно рекомендуем Вам пользоваться новыми версиями Excel в целом и Power Query в частности (в Excel 2016 он встроен по умолчанию, в Excel 2010 и 2013 может быть установлен как бесплатная надстройка).

Ваши вопросы по статье можете задавать через нашего бота обратной связи в Telegram: @ExEvFeedbackBot

Есть в IT-отрасли задачи, которые на фоне успехов в big data, machine learning, blockchain и прочих модных течений выглядят совершенно непривлекательно, но на протяжении десятков лет не перестают быть актуальными для целой армии разработчиков. Речь пойдёт о старой как мир задаче формирования и выгрузки Excel-документов, с которой сталкивался каждый, кто когда-либо писал приложения для бизнеса.

Какие возможности построения файлов Excel существуют в принципе?

VBA-макросы. В наше время по соображениям безопасности идея использовать макросы чаще всего не подходит.
Автоматизация Excel внешней программой через API. Требует наличия Excel на одной машине с программой, генерирующей Excel-отчёты. Во времена, когда клиенты были толстыми и писались в виде десктопных приложений Windows, такой способ годился (хотя не отличался скоростью и надёжностью), в нынешних реалиях это с трудом достижимый случай.
Генерация XML-Excel-файла напрямую. Как известно, Excel поддерживает XML-формат сохранения документа, который потенциально можно сгенерировать/модифицировать с помощью любого средства работы с XML. Этот файл можно сохранить с расширением .xls, и хотя он, строго говоря, при этом не является xls-файлом, Excel его хорошо открывает. Такой подход довольно популярен, но к недостаткам следует отнести то, что всякое решение, основанное на прямом редактировании XML-Excel-формата, является одноразовым «хаком», лишенным общности.
Наконец, возможна генерация Excel-файлов с использованием open source библиотек, из которых особо известна Apache POI. Разработчики Apache POI проделали титанический труд по reverse engineering бинарных форматов документов MS Office, и продолжают на протяжении многих лет поддерживать и развивать эту библиотеку. Результат этого reverse engineering-а, например, используется в Open Office для реализации сохранения документов в форматах, совместимых с MS Office.

Но у прямого использования Apache POI есть и недостатки. Во-первых, это Java-библиотека, и если ваше приложение написано не на одном из JVM-языков, вы ей вряд ли сможете воспользоваться. Во-вторых, это низкоуровневая библиотека, работающая с такими понятиями, как «ячейка», «колонка», «шрифт». Поэтому «в лоб» написанная процедура генерации документа быстро превращается в обильную «лапшу» трудночитаемого кода, где отсутствует разделение на модель данных и представление, трудно вносить изменения и вообще — боль и стыд. И прекрасный повод делегировать задачу самому неопытному программисту – пусть ковыряется.

Но всё может быть совершенно иначе. Проект Xylophone под лицензией LGPL, построенный на базе Apache POI, основан на идее, которая имеет примерно 15-летнюю историю. В проектах, где я участвовал, он использовался в комбинации с самыми разными платформами и языками – а счёт разновидностей форм, сделанных с его помощью в самых разнообразных проектах, идёт, наверное, уже на тысячи. Это Java-проект, который может работать как в качестве утилиты командной строки, так и в качестве библиотеки (если у вас код на JVM-языке — вы можете подключить её как Maven-зависимость).

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

Шаблон документа (xls/xlsx template) выглядит примерно следующим образом:

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

Когда «оформительская» работа выполнена, разработчику остаётся

Создать процедуру выгрузки необходимых данных в формате XML.
Создать дескриптор, описывающий порядок обхода элементов XML-файла и копирования фрагментов шаблона в результирующий отчёт
Обеспечить привязку ячеек шаблона к элементам XML-файла с помощью XPath-выражений.

Если бы в форме, которую мы создаём, не было повторяющихся элементов с разным количеством (таких, как строки накладной, которых разное количество у разных накладных), то дескриптор выглядел бы следующим образом:

Здесь root – название корневого элемента нашего XML-файла с данными, а диапазон A1:Z100 – это прямоугольный диапазон ячеек из шаблона, который будет скопирован в результат. При этом, как можно видеть из предыдущей иллюстрации, подстановочные поля, значения которых заменяются на данные из XML-файла, имеют формат

(тильда, фигурная скобка, XPath-выражение относительно текущего элемента XML, закрывающая фигурная скобка).

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

Красными квадратиками отмечены ячейки, которые будут являться левым верхним углом очередного прямоугольного фрагмента, который пристыковывает генератор отчёта.

Есть и ещё один возможный вариант повторяющихся элементов: листы в книге Excel. Возможность организовать такую итерацию тоже имеется.

Рассмотрим чуть более сложный пример. Допустим, нам надо получить сводный отчёт наподобие следующего:

Пусть диапазон лет для выгрузки выбирает пользователь, поэтому в этом отчёте динамически создаваемыми являются как строки, так и столбцы. XML-представление данных для такого отчёта может выглядеть следующим образом:

Мы вольны выбирать названия тэгов по своему вкусу, структура также может быть произвольной, но с оглядкой на простоту конвертации в отчёт. Например, выводимые на лист значения я обычно записываю в атрибуты, потому что это упрощает XPath-выражения (удобно, когда они имеют вид @имяатрибута ).

Шаблон такого отчёта будет выглядеть так (сравните XPath-выражения с именами атрибутов соответствующих тэгов):

Теперь наступает самая интересная часть: создание дескриптора. Т. к. это практически полностью динамически собираемый отчёт, дескриптор довольно сложен, на практике (когда у нас есть только «шапка» документа, его строки и «подвал») всё обычно гораздо проще. Вот какой в данном случае необходим дескриптор:

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

element — переход в режим чтения элемента XML-файла. Может или являться корневым элементом дескриптора, или находиться внутри iteration . С помощью атрибута name могут быть заданы разнообразные фильтры для элементов, например
- name="foo" — элементы с именем тэга foo
- name="*" — все элементы
- name="tagname[@attribute='value']" — элементы с определённым именем и значением атрибута
- name="(before)" , name="(after)" — «виртуальные» элементы, предшествующие итерации и закрывающие итерацию.
- mode="horizontal" — режим вывода по горизонтали (по умолчанию — vertical)
- index=0 — ограничить итерацию только самым первым встреченным элементом
- sourcesheet —лист книги шаблона, с которого берётся диапазон вывода. Если не указывать, то применяется текущий (последний использованный) лист.
- range – диапазон шаблона, копируемый в результирующий документ, например “A1:M10”, или “5:6”, или “C:C”. (Применение диапазонов строк типа “5:6” в режиме вывода horizontal и диапазонов столбцов типа “C:C” в режиме вывода vertical приведёт к ошибке).
- worksheet – если определён, то в файле вывода создаётся новый лист и позиция вывода смещается в ячейку A1 этого листа. Значение этого атрибута, равное константе или XPath-выражению, подставляется в имя нового листа.
Ну что же, настало время скачать Xylophone и запустить формирование отчёта.
Возьмите архив с bintray или Maven Central (NB: на момент прочтения этой статьи возможно наличие более свежих версий). В папке /bin находится shell-скрипт, при запуске которого без параметров вы увидите подсказку о параметрах командной строки. Для получения результата нам надо «скормить» ксилофону все приготовленные ранее ингредиенты:

Открываем файл report.xlsx и убеждаемся, что получилось именно то, что нам нужно:

Так как библиотека ru.curs:xylophone доступна на Maven Central под лицензией LGPL, её можно без проблем использовать в программах на любом JVM-языке. Пожалуй, самый компактный полностью рабочий пример получается на языке Groovy, код в комментариях не нуждается:

У класса XML2Spreadsheet есть несколько перегруженных вариантов статического метода process , но все они сводятся к передаче всё тех же «ингредиентов», необходимых для подготовки отчёта.

Важная опция, о которой я до сих пор не упомянул — это возможность выбора между DOM и SAX парсерами на этапе разбора файла с XML-данными. Как известно, DOM-парсер загружает весь файл в память целиком, строит его объектное представление и даёт возможность обходить его содержимое произвольным образом (в том числе повторно возвращаясь в один и тот же элемент). SAX-парсер никогда не помещает файл с данными целиком в память, вместо этого обрабатывает его как «поток» элементов, не давая возможности вернуться к элементу повторно.

Использование SAX-режима в Xylophone (через параметр командной строки -sax или установкой в true параметра useSax метода XML2Spreadsheet.process ) бывает критически полезно в случаях, когда необходимо генерировать очень большие файлы. За счёт скорости и экономичности к ресурсам SAX-парсера скорость генерации файлов возрастает многократно. Это даётся ценой некоторых небольших ограничений на дескриптор (описано в документации), но в большинстве случаев отчёты удовлетворяют этим ограничениям, поэтому я бы рекомендовал использование SAX-режима везде, где это возможно.

Надеюсь, что способ выгрузки в Excel через Xylophone вам понравился и сэкономит много времени и нервов — как сэкономил нам.

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