Автоматизированный перенос данных из формата Microsoft Excel 97-2003 и Excel 2007 в базу данных MySQL

 

Кафедра Информатики, ВТ и МПИ

КОНТРОЛЬНАЯ РАБОТА

По дисциплине Мультимедиа-технологии

На тему Автоматизированный перенос данных из формата Microsoft Excel 97-2003 и Excel 2007 в базу данных MySQL

ОГЛАВЛЕНИЕ

ВВЕДЕНИЕ

. ПРОБЛЕМНАЯ ОБЛАСТЬ

. ПОСТАНОВКА ЗАДАЧИ

. АНАЛИЗ ИСХОДНЫХ ДАННЫХ

.1 Общие сведения о форматах файлов .xls и .xlsx

.2 Данные, содержащиеся в исходном файле

. ПРОЕКТИРОВАНИЕ БАЗЫ ДАННЫХ

. АЛГОРИТМ РАБОТЫ ПРИЛОЖЕНИЯ

. РЕАЛИЗАЦИЯ АЛГОРИТМА

. ТЕСТИРОВАНИЕ И ОТЛАДКА

ЗАКЛЮЧЕНИЕ

СПИСОК ИСПОЛЬЗОВАННЫХ ИСТОЧНИКОВ

ВВЕДЕНИЕ

Формат файлов Excel является широко используемым форматом хранения табличной информации. На данный момент наиболее широко применяется формат .xls, применяющийся в программных пакетах Microsoft Excel 97-2003. Более простым, но менее распространённым в силу своей новизны является формат .xlsx, применяемый в Microsoft Excel, начиная с версии 2007.

Целью работы является автоматизация процесса разбора данных файла с расписанием занятий Института Естественных Наук и Биомедицины САФУ (образец файла находится см. в прикрепленных файлах). Необходимо считать данные из файла и перенести их в базу данных MySQL.

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

Для простоты разработки приложения в качестве локального сервера используется Denwer, языком, на котором разработано предлагаемое решение, является PHP, для ускорения процесса разработки приложения использована библиотека PHPExcel, доработанная для решения поставленной задачи.

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

1. ПРОБЛЕМНАЯ ОБЛАСТЬ

Один раз в неделю для Института Естественных Наук и Биомедицины составляется расписание, сохраняемое в формате таблиц Excel. Для переноса на сайт института данные из таблицы на данный момент обрабатываются вручную. Поскольку на данный момент расписание составляется для тридцать одной группы, перенос данных занимает длительное время. Объектом курсовой работы являются форматы файлов .xls и .xlsx, а также средства, предназначенные для чтения и обработки их текстового содержимого в среде вэб-сервера. Предметом выполненной работы является перенос данных из таблицы, содержащей расписание института в базу данных, находящуюся на вэб-сервере.

2. ПОСТАНОВКА ЗАДАЧИ

Необходимо создать приложение, работающее на локальном вэб-сервере, написанное на языке программирования PHP. Приложение должно осуществлять корректный перенос данных из файла формата .xls или .xlsx в базу данных MySQL вэб-сервера.

Для создания данного приложения необходимо выполнить этапы:

-Анализ исходных данных

Проектирование базы данных в соответствии с исходными данными

Написание алгоритма работы приложения

Реализация алгоритма

3. АНАЛИЗ ИСХОДНЫХ ДАННЫХ

3.1 Общие сведения о форматах файлов .xls и .xlsx

автоматизация занятие сервер база

Несмотря на то, что формат .xls является закрытым, сведения о структуре формата можно найти в открытом доступе. Начиная с версии формата Excel 7.0, данные таблиц и листов хранятся в BIFF8 (Binary Interchange File Format) формате, инкапсулированные в контейнер OLE2. OLE файл состоит из так называемых виртуальных потоков. Виртуальный поток - это данные, которые читаются как линейный поток, хотя их физическое расположение в файле может быть фрагментировано. Это могут быть данные пользователя или структуры, контролирующие работу файла.файл построен как файловая система. Все пространство файла разбито на сектора. Размер сектора определяется при создании файла и, как правило, равен 512 байтам. Виртуальный поток состоит из последовательности секторов. Сектора нумеруются от -1 (Header) с шагом 1. Почти все переменные ссылаются на номер сектора, а не на смещение.структура представляет собой подряд идущие записи. Все записи имеют следующий общий формат: ID (2 байта), Размер данных, sz (2 байта), Данные (sz байт)

Первые 4 байта (ID и размер) - это заголовок записи. Размер записи не включает размер заголовка. Все данные записываются в Intel-нотации.

Есть много стандартных записей Excel. Каждая внутри (секция данных) имеет свой определенный формат. Записи могут группироваться в потоки. Ограничителем групп служат 2 специальные записи: BOF (Begin Of File) и EOF (End Of File).

Формат файла .xlsx основан на Open XML и методе сжатия ZIP, что существенно облегчает работу с форматом. После распаковки архива, наибольший интерес представляет директория /xl/worksheets/, файлы /xl/sharedStrings.xml, /xl/workbook.xml.

В файле /xl/workbook.xml хранится описание листов, в файле xl/sharedStrings.xml содержатся текстовые данные из ячеек исходного документа со всех листов. В директории /xl/worksheets/ содержатся xml файлы, содержащие описание данных листов.

3.2 Данные, содержащиеся в исходном файле

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

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

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

Рисунок 1 - Пример исходных данных

4. ПРОЕКТИРОВАНИЕ БАЗЫ ДАННЫХ

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

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

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

-Курсы

Группы (наименования специальностей)

Номер группы и подгруппы

Расписание для группы (подгруппы), содержащее поля, в которых записаны занятия, преподаватели, которые их проводят, номера аудиторий, номер занятия

5. АЛГОРИТМ РАБОТЫ ПРИЛОЖЕНИЯ

Алгоритм работы приложения изображён на рисунке 2.

Рисунок 2 - Алгоритм работы приложения

6. РЕАЛИЗАЦИЯ АЛГОРИТМА

Для реализации алгоритма была взята за основу PHP библиотека PHPExcel, где реализованы базовые функции извлечения данных обоих форматов файлов (.xls и .xlsx), такие как чтение OLE, разбор записей в BIFF, выбор данных из конкретных строк и ячеек таблицы. Таким образом, реализация самого алгоритма сводится к написанию загрузчика файлов на локальный сервер и разбору данных из массива с последующим внесением данных в локальную БД. Для выбора загружаемого и загрузки его на сервер файла используется javascript.

Рисунок 3 - Исходный код формы загрузки файла.

Далее, тремя вложенными циклами, перебираем дни недели расписания, имена групп, занятия групп, основываясь на том, что ежедневно в расписании стоит до 7 учебных занятий, каждая колонка с расписанием для подгруппы занимает 5 объединённых по ширине ячеек, ячейки, выделенные под описание одного занятия могут содержать наименование занятия, ФИО преподавателя и номер учебной аудитории. Полученные таким образом данные формируются в запрос, добавляющий данные в БД.

7. ТЕСТИРОВАНИЕ И ОТЛАДКА

При отладке приложения выяснилось, что в разных частях документа, наименование предмета Физическая культура может повторяться в объединённых по группам ячейках произвольное число раз через запятую. Для устранения возникшей проблемы, поскольку подобное объединение предметов более нигде не используется, была применена PHP функция str_replace.

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

ЗАКЛЮЧЕНИЕ

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

В процессе выполнения данной работы я достиг следующих результатов:

-Спроектировал и написал приложение, позволяющее перенести данные, содержащиеся в .xls и .xlsx файлах расписания института

Ознакомился со спецификациями на данные форматы файлов

Овладел навыками разбора информации, содержащейся в файлах форматов .xls и .xlsx

СПИСОК ИСПОЛЬЗОВАННЫХ ИСТОЧНИКОВ

OpenOffice: портал [Электронный ресурс]. - Режим доступа: <http://www.openoffice.org/sc/excelfileformat.pdf>.

Хабрахабр: портал [Электронный ресурс] // Разбираем xlsx в PHP без готовых библиотек. - Режим доступа: <http://habrahabr.ru/post/140352/>.

PHP: портал [Электронный ресурс] - Режим доступа: <http://php.net/>.

Кафедра Информатики, ВТ и МПИ КОНТРОЛЬНАЯ РАБОТА По дисциплине Мультимедиа-технологии На тему Автом

Больше работ по теме: