Выгрузка базы данных в excel

Обновлено: 22.01.2025

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

В этой статье

Экспорт данных в Excel: основы

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

Стандартные сценарии экспорта данных в Excel

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

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

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

В приложении Access нет команды "Сохранить как” с возможностью сохранения данных в формате Excel. Чтобы скопировать данные в Excel, необходимо воспользоваться функций экспорта, описанной в этой статье, либо скопировать данные Access в буфер обмена, а затем вставить их в таблицу Excel.

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

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

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

В ходе операции экспорта можно экспортировать лишь один объект базы данных. Однако после нескольких операций экспорта можно выполнить в Excel слияние нескольких листов.

Подготовка к экспорту

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

Исходный объект

Поля и записи

Форматирование

Таблица или запрос

Примечание: Формы и отчеты невозможно экспортировать без форматирования.

Экспортируются все поля и записи базового объекта.

Параметры свойства "Формат" пропускаются в ходе операции.

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

Таблица, запрос, отчет или форма

Экспортируются только поля и записи, отображаемые в текущем представлении или объекте. Фильтрованные записи, скрытые столбцы таблицы, а также не отображаемые в форме или отчете поля не экспортируются.

В мастере соблюдаются параметры свойства "Формат".

В случае полей подстановки экспортируются значения подстановки.

При экспорте полей гиперссылок значения экспортируются в виде гиперссылок.

При экспорте полей форматированного текста текст экспортируется без форматирования.

Выберите целевую книгу и ее формат. Имейте в виду, что отчеты можно экспортировать только в более старом формате XLS (но не в формате XLSX).

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

Целевая книга

Исходный объект

Экспортируемые данные

Таблица, запрос, отчет или форма

Данные с форматированием или без него

В ходе операции экспорта создается книга.

Таблица или запрос

Данные без форматирования

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

Таблица, запрос, отчет или форма

Данные с форматированием

Файл перезаписывается экспортируемыми данными. Все имеющиеся листы удаляются, и создается новый лист с именем экспортируемого объекта. Данные листа Excel наследуют параметры форматирования исходного объекта.

Данные всегда добавляются в новый лист. Невозможно добавить данные в имеющийся лист или именованный диапазон.

Выполнение экспорта

Если целевая книга Excel открыта, закройте ее перед выполнением операции.

На панели навигации исходной базы данных выберите объект, который требуется экспортировать.

Экспорт только части данных

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

Открытие формы в режиме таблицы

Чтобы открыть форму, дважды щелкните ее.

Щелкните форму правой кнопкой мыши, а затем щелкните элемент Режим таблицы. Если он недоступен, выполните действия, описанные ниже.

Выберите пункт Режим конструктора.

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

На вкладке Конструктор в группе Представления выберите пункт Режим таблицы.

Примечание: Часть отчета экспортировать нельзя. Однако можно выбрать или открыть таблицу или запрос, на которых основан отчет, и затем экспортировать часть данных в этот объект.

На вкладке Внешние данные в группе Экспорт нажмите кнопку Excel.

В диалоговом окне Экспорт - Электронная таблица Excel просмотрите предлагаемые имена файлов рабочей книги Excel (Access использует имена исходных объектов). При необходимости имя файла можно изменить.

В поле Формат файла выберите нужный формат файла.

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

Примечание: При экспорте формы или отчета этот флажок установлен по умолчанию и недоступен для изменения.

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

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

Примечание: Если записи не выделены, этот флажок недоступен (неактивен).

Что еще важно знать об экспорте

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

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

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

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

Решение проблем, связанных с отсутствующими и неверными значениями

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

Совет: Если отсутствует всего несколько значений, введите их в книгу Excel самостоятельно, в противном случае исправьте исходный объект в базе данных Access и повторите экспорт.

Источник проблемы

Описание и решение

Экспортируются результаты вычисляемых полей; выражения, на основе которых производится вычисление, не экспортируются.

Многозначные поля экспортируются в виде списка значений, разделенных точками с запятой (;).

Рисунки, объекты и вложения

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

При экспорте формы или отчета, содержащих объект Microsoft Graph, он не экспортируется.

Данные в неверном столбце

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

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

Значения дат, предшествующих 1 января 1900 г., не экспортируются. Соответствующие ячейки на листе содержат значения Null.

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

Отсутствующие подчиненные формы, отчеты и таблицы

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

Отсутствующие или неправильно отформатированные столбцы

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

Форматирование столбца в Excel

Откройте целевую книгу Excel и перейдите на лист с экспортированными данными.

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

На вкладке Число в группе Числовые форматы выберите нужный формат, например Текстовый, Числовой, Дата или Время.

Индикаторы или значения ошибки

Есть в 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 вам понравился и сэкономит много времени и нервов — как сэкономил нам.

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

Выгрузка информации из 1C в Excel

Если загрузка данных из Excel в 1С – это довольно сложная процедура, автоматизировать которую можно только с помощью сторонних решений, то обратный процесс, а именно выгрузка из 1С в Эксель – это сравнительно простой набор действий. Его можно легко выполнить с помощью встроенных инструментов вышеуказанных программ, причем сделать это можно несколькими способами, в зависимости от того, что именно пользователю нужно перенести. Рассмотрим, как это выполнить на конкретных примерах в 1С версии 8.3.

Способ 1: копирование содержимого ячейки

Одна единица данных содержится в ячейке 1С. Её можно перенести в Эксель обычным методом копирования.
1. Выделяем ячейку в 1С, содержимое которой нужно скопировать. Кликаем по ней правой кнопкой мыши. В контекстном меню выбираем пункт «Копировать». Можно воспользоваться также универсальным способом, который действует в большинстве программ, работающих на ОС Windows: просто выделяем содержимое ячейки и набираем комбинацию клавиш на клавиатуре Ctrl+C.
Вместо этого действия можно после выделения ячейки, находясь во вкладке «Главная», щелкнуть по значку «Вставить», который расположен на ленте в блоке «Буфер обмена».

Содержимое ячейки 1С будет вставлено в Excel.

Способ 2: вставка списка в существующую книгу Excel

Но вышеуказанный способ подойдет лишь в том случае, если нужно перенести данные из одной ячейки. Когда же нужно совершить перенос целого списка, то следует воспользоваться другим способом, ведь копирование по одному элементу отберет очень много времени.
1. Открываем любой список, журнал или справочник в 1С. Кликаем на кнопку «Все действия», которая должна располагаться вверху от обрабатываемого массива данных. Запускается меню. Выбираем в нем пункт «Вывести список».
Поле «Выводить в» имеет два значения:

По умолчанию установлен первый вариант. Для переноса данных в Эксель он как раз и подходит, так что тут мы ничего не меняем.

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

Список вставлен в документ.

Способ 3: создание новой книги Excel со списком

Также список из программы 1С можно сразу вывести в новый файл Эксель.
Ещё проще сделать переход, нажав на кнопку «Сохранить», которая имеет вид дискеты и расположена в блоке инструментов 1С в самом верху окна. Но такая возможность доступна только пользователям, которые применяют программу версии 8.3. В ранних версиях можно использовать только предыдущий вариант.

Весь список будет сохранен отдельной книгой.

Способ 4: копирование диапазона из списка 1С в Excel

Бывают случаи, когда нужно перенести не весь список, а только отдельные строки или диапазон данных. Этот вариант тоже вполне воплотим с помощью встроенных инструментов.
Способ 5: Сохранение документов в формате Excel

В Excel иногда нужно сохранять не только списки, но и созданные в 1С документы (счета, накладные платежные поручения и т.д.). Это связано с тем, что для многих пользователей редактировать документ проще в Экселе. К тому же в Excel можно удалить заполненные данные и, распечатав документ, использовать его при необходимости как бланк для ручного заполнения.
Документ будет сохранен в формате Эксель. Этот файл теперь можно открывать в данной программе, и проводить дальнейшую его обработку уже в ней.

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

Мы рады, что смогли помочь Вам в решении проблемы.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Вы можете установить компонент MySQL для Excel при установке MySQL Community Server 6. Или вы можете установить его как дополнение к существующей установке MySQL Server. Однако для установки этого компонента есть некоторые предпосылки.

MySQL для Excel Требования

Для установки MySQL для Excel Installer на вашем компьютере должно быть установлено следующее:

Microsoft Office Excel 2007 или выше, для Microsoft Windows.

Visual Studio Tools для Office 4.0 и MySQL Installer могут установить это для вас.

Доступное соединение с MySQL Server.

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

Редактировать данные MySQL в Excel

MySQL для Excel позволяет загружать и редактировать данные MySQL непосредственно из Microsoft Excel, или же вы можете сделать это вручную, нажав «Принять изменения».

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

Вот пошаговое руководство по редактированию и фиксации данных:

Шаг 1:

Загрузить Microsoft Office Excel 7

Шаг 2:

Нажмите на вкладку данных , см. Рисунок выше, значок базы данных «MySQL для Excel» появится ниже.

Шаг 3:

Нажмите на значок базы данных «MySQL для Excel». Он открывает новую боковую панель Excel с доступными параметрами MySQL для Excel. Панель навигации со значком MySQL для Excel показана на следующем рисунке.

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

Шаг 4:

В боковой панели MySQL для Excel откройте соединение MySQL двойным щелчком мыши. Здесь наше соединение - Локальный экземпляр MySQL5.6, и для принятия пароля появится следующий экран.

Шаг 5:

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

Шаг 6:

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

Шаг 7:

Выберите таблицу, которую вы хотите редактировать, нажмите «Редактировать данные MySQL» на панели навигации, как показано выше, и посмотрите следующий снимок экрана.

Шаг 8:

Шаг 9:

Здесь, в нашей таблице, идентификатор первого столбца является первичным ключом. Если мы изменим значение ID в ячейке A19 вместо 17 и нажмем клавишу ввода, цвет этой ячейки будет зеленым. Теперь мы изменили значение ячейки F16 Лондона и нажмите клавишу ввода, цвет ячейки изменится на зеленый, а желтый цвет в последнюю очередь означает, что ячейки принимают новые данные. Введенные здесь данные вставляются в таблицу MySQL.

Шаг 10:

Теперь, если мы нажмем кнопку «Восстановить данные», посмотрите следующий снимок экрана.

Здесь на картинке выше, если вы нажмете кнопку «Обновить данные из БД», данные будут обновлены и отобразятся исходные данные из БД, а если вы нажмете «Восстановить измененные данные», вы потеряете только что сделанные изменения. На этом этапе, если вы нажмете кнопку «Применить изменения», посмотрите следующий снимок экрана.

На изображении выше показано, как цвета ячейки F16 преобразованы из зеленого в синий, что означает успешное выполнение изменений. Теперь вы можете закрыть сохранение окна Excel или нет, но таблица базы данных была обновлена. Вы можете видеть это, чтобы сделать шаг снова. Если вы установили флажок «Автоматическая фиксация», эффект сразу же отобразится на вашем листе, и данные будут обновлены в базе данных MySQL.

Импорт данных MySQL в Excel

Данные можно импортировать из MySQL в электронную таблицу Microsoft Excel с помощью параметра «Импорт данных MySQL» после выбора таблицы, представления или процедуры для импорта.

Прежде всего, вы делаете первые 6 шагов, описанных выше в «Редактировании данных MySQL в Excel», затем выбираете таблицу, которую хотите импортировать. Здесь наш стол - местоположение. Поэтому выберите таблицу местоположений, а затем нажмите «Импорт данных MySQL» и посмотрите на скриншот, показанный ниже.

Выбор столбцов для импорта

По умолчанию все столбцы выбраны и будут импортированы. Определенные столбцы могут быть выделены (или не выбраны) с использованием стандартного метода Microsoft Windows: либо Control + щелчок мышью для выбора отдельных столбцов, либо Shift + щелчок мышью для выбора диапазона столбцов.

Белый цвет фона указывает, что столбец или столбцы были выбраны, и они готовы к импорту, с другой стороны, серый цвет указывает, что столбцы не выбраны и столбец не будет импортирован.

Если щелкнуть правой кнопкой мыши в любом месте сетки предварительного просмотра, откроется контекстное меню с параметром «Нет» или «Выбрать все» в зависимости от текущего состояния.

Таблица импорта

Включить имена столбцов в качестве заголовков: по умолчанию этот параметр включен, и он обрабатывает имена столбцов в верхней части электронной таблицы Microsoft Excel как строку «заголовков» и будет вставлен в качестве заголовка.

Ограничить и строки и начать с строки : по умолчанию этот параметр отключен, если включен, это ограничивает диапазон импортируемых данных. Параметр Limit to по умолчанию равен 1, и этот предел может быть изменен путем определения количества строк для импорта. Параметр «Начать со строки» по умолчанию равен 1, т. Е. Начинается с первой строки, и его можно изменить, определяя номер, с которого начинается импорт. Каждый параметр имеет максимальное значение COUNT (строки) в таблице.

Теперь предполагается, что мы хотим импортировать столбцы LOATION_ID и CITY. Щелкните мышью по столбцу LOCATION_ID, затем нажмите и удерживайте клавишу CTRL, щелкните столбец CITY и посмотрите следующий снимок экрана.

Теперь, если мы нажмем кнопку «Импорт», все строки для этих двух столбцов будут импортированы в Microsoft Excel Worksheet.

Предполагается, что мы хотим импортировать только 6 строк, начиная с 3-го ряда. Теперь посмотрите следующий снимок экрана.

Здесь на рисунке выше показано, что все столбцы выбраны, и значение Limit to равно 6, что означает, что будет импортировано 6 строк, и начало импорта начнется с 3-й строки, потому что мы установили значение Start with Строка 3. Теперь нажмите кнопку «Импорт» и посмотрите следующий снимок экрана.

Добавить данные Excel в MySQL

Данные электронной таблицы Microsoft Excel можно добавлять в таблицу базы данных MySQL с помощью параметра «Добавить данные Excel в таблицу».

Отображения столбцов

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

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

Теперь нажмите «Добавить данные Excel в таблицу», как упомянуто выше, и посмотрите на экран как.

Здесь на картинке выше, вы ищете два цвета для легенды. Один красный, а другой зеленый.

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

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

Здесь на рисунке выше целевой столбец 4 стал красным, а исходный столбец 4 - серым.

Красный цвет означает, что целевой столбец не сопоставлен, а серый цвет означает, что исходный столбец не сопоставлен.

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

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

Методы картирования

Вот посмотрите три метода отображения:

Метод автоматического сопоставления пытается сопоставить имена исходных столбцов Excel с именами столбцов целевой таблицы MySQL.

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

Вы можете сохранить свои собственные стили сопоставления с помощью кнопки « Сохранить сопоставление» и сохранить имя, например «name (dbname.tablename)», и оно будет доступно в поле со списком «Метод сопоставления».

Сохраненные сопоставления могут быть удалены или переименованы в диалоговом окне « Дополнительные параметры ».

Расширенные настройки

Если мы нажмем кнопку «Дополнительные параметры», диалоговое окно будет выглядеть примерно так:

Выполните автоматическое сопоставление при открытии диалогового окна : с помощью этих параметров автоматически сопоставляется цель и источник при открытии диалогового окна «Добавить данные».

Автоматически сохранять сопоставление столбцов для данной таблицы . Чтобы проверить этот параметр, сохраняется каждая подпрограмма сопоставления после выполнения операции добавления. Процедура отображения сохраняется в формате «tablenameMapping (dbname.tablename)». Это может быть выполнено вручную с помощью кнопки Store Mapping.

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

Расширенные параметры поля данных:

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

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

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

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

Экспорт данных Excel в MySQL

Данные электронной таблицы Microsoft Excel можно экспортировать в новую таблицу базы данных MySQL с помощью параметра « Экспорт данных Excel в новую таблицу» . Сначала вы делаете первые 6 шагов, описанных выше, в разделе « Редактирование данных MySQL в Excel» . Затем введите несколько записей с нужным заголовком в электронную таблицу Excel и выберите записи. Теперь посмотрите пример записи на следующем рисунке -

Теперь нажмите «Экспорт данных Excel в новую таблицу» с помощью стрелки на изображении выше и посмотрите на следующий экран.

1 - Укажите уникальное имя для таблицы MySQL. Предположим, что имя таблицы MySQL "счет-фактура"

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

3 - Когда вы установите переключатель «Использовать существующие столбцы», имена столбцов будут доступны в поле со списком, и вы сможете выбрать столбец первичного ключа из списка, и столбец будет выбран. Предположим, вы выбрали столбец INVOICE_NO, посмотрите следующую картинку.

Здесь на рисунке выше был выбран столбец INVOICE_NO, и теперь вы можете изменить имя столбца (точка 6), тип данных (точка 7) и ограничение (точка 9), которые активны.

4 - Если вы установите флажок Первая строка содержит имена столбцов (пункт 4), первый столбец в ваших данных Excel будет заголовком, в противном случае это будет столбец1, столбец2 и т. Д.

5 - Предположим, вы изменили тип данных и имя столбца для других столбцов, которые вы можете нажать на заголовок столбца. Предполагая, что мы хотим изменить тип данных INVOICE_DT, нажмите на INVOICE_DT, и этот столбец будет выбран, а затем вы можете использовать пункты 6,7 и 9.

6 - Вы можете изменить имя столбца.

7 - Вы можете изменить тип данных

8 - Расширенный параметр показывает следующее диалоговое окно.

Расширенные настройки

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

Анализировать и пытаться определить правильный тип данных на основе содержимого поля столбца. Этот параметр пытается анализировать данные и определять тип данных для столбца. Когда столбец содержит несколько типов данных, он определяется как VARCHAR.

Добавить дополнительный буфер к длине VARCHAR (округлить до 12, 25, 45, 125, 255) : если он включен, он автоматически определяет тип данных и устанавливает значение VARCHAR, а затем находит максимальную длину для всех строк в столбце, и округляет максимальную длину до одной из указанных выше длин и, если она отключена, то длина VARCHAR устанавливается равной длине самой длинной записи в электронной таблице Excel.

Автоматически установите флажок «Индекс» для столбцов с целочисленными значениями : по умолчанию этот параметр включен, и тогда для столбцов с целочисленным типом данных будет включен параметр «Создать индекс» по умолчанию.

Автоматически установите флажок «Разрешить пустой» для столбцов без индекса: по умолчанию этот параметр включен, а при включении для столбцов без флажка «Создать индекс» автоматически включается параметр конфигурации «Разрешить пустой».

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

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

После завершения всех настроек нажмите кнопку «Экспортировать» и увидите следующий снимок экрана:

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