У многих при работе с PHP в связке с MySQL возникает такая потребность, как экспорт данных из базы в формат xls, для того чтобы люди, которым нужны эти данные, обрабатывали их в программе Excel или просто пользователям было удобно смотреть эти данные. Недавно у меня возникла такая потребность и сегодня я расскажу, как это дело можно реализовать.
Сразу скажу, что этот способ достаточно простой, но данные выгружаются нормально.
Для начала приведу пример конечного xls файла, в программе Excel выгрузка будет выглядеть примерно так:
Другими словами, никаких картинок, стилей выгружено не будет, только заголовки столбцов и сами данные.
До того как я пришел именно к такому варианту выгрузки я пробовал выгружать в формат csv, но получалось немного коряво, потом пробовал отрисовывать таблицу и сохранять ее с расширением xls, также получалось какая-то ерунда, способ, который я сейчас опишу, меня полностью устроил, и сейчас с Вами я им поделюсь.
Для начала приведу весь код, который я максимально прокомментировал, можете выделить его и сохранить с расширением php и пробовать, только не забудьте прописать настройки подключения к базе данных.
Экспорт данных из MySQL в Excel на PHP
А еще чтобы Вы понимали, какие данные я выгружаю, приведу пример простой таблицы в БД (у меня ее название test):
Тестовая таблица:
id | firstname | name |
1 | Иванов | Иван |
2 | Петров | Петр |
2 | Петров2 | Петр2 |
Код:
<?php mysql_connect ("localhost", "root","");//подключение к серверу mysql_select_db("test") or die (mysql_error());//выбор базы данных mysql_query('SET character_set_database = utf8'); mysql_query ("SET NAMES 'utf8'"); error_reporting(E_ALL); ini_set("display_errors", 1); if( !defined( "ExcelExport" ) ) { define( "ExcelExport", 1 ); class ExportToExcel { var $xlsData = ""; var $fileName = ""; var $countRow = 0; var $countCol = 0; var $totalCol = 3;//общее число колонок в Excel //конструктор класса function __construct (){ $this->xlsData = pack( "ssssss", 0x809, 0x08, 0x00,0x10, 0x0, 0x0 ); } // Если число function RecNumber( $row, $col, $value ){ $this->xlsData .= pack( "sssss", 0x0203, 14, $row, $col, 0x00 ); $this->xlsData .= pack( "d", $value ); return; } //Если текст function RecText( $row, $col, $value ){ $len = strlen( $value ); $this->xlsData .= pack( "s*", 0x0204, 8 + $len, $row, $col, 0x00, $len); $this->xlsData .= $value; return; } // Вставляем число function InsertNumber( $value ){ if ( $this->countCol == $this->totalCol ) { $this->countCol = 0; $this->countRow++; } $this->RecNumber( $this->countRow, $this->countCol, $value ); $this->countCol++; return; } // Вставляем текст function InsertText( $value ){ if ( $this->countCol == $this->totalCol ) { $this->countCol = 0; $this->countRow++; } $this->RecText( $this->countRow, $this->countCol, $value ); $this->countCol++; return; } // Переход на новую строку function GoNewLine(){ $this->countCol = 0; $this->countRow++; return; } //Конец данных function EndData(){ $this->xlsData .= pack( "ss", 0x0A, 0x00 ); return; } // Сохраняем файл function SaveFile( $fileName ){ $this->fileName = $fileName; $this->SendFile(); } // Отправляем файл function SendFile(){ $this->EndData(); header ( "Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT" ); header ( "Cache-Control: no-store, no-cache, must-revalidate" ); header ( "Pragma: no-cache" ); header ( "Content-type: application/x-msexcel" ); header ( "Content-Disposition: attachment; fileName=$this->fileName.xls" ); print $this->xlsData; } } } if(isset($_GET['id'])) { //фильтруем данные $id = mysql_real_escape_string(stripslashes(trim(htmlspecialchars($_GET['id'],ENT_QUOTES)))); $filename = 'Файл_с_id_'.$id; // задаем имя файла $excel = new ExportToExcel(); // создаем экземпляр класса $sql="SELECT * FROM test.test where id = $id";//запрос к базе $rez=mysql_query($sql); $excel->InsertText('Идентификатор'); $excel->InsertText('Фамилия'); $excel->InsertText('Имя'); $excel->GoNewLine(); While($row=mysql_fetch_assoc($rez)){ $excel->InsertNumber($row['id']); $excel->InsertText($row['firstname']); $excel->InsertText($row['name']); $excel->GoNewLine(); } $excel->SaveFile($filename); } ?>
Вся идея здесь заключается в функции pack(), которая упаковывает данные в бинарную строку, а мы в свою очередь последовательно заполняем эту строку данными, которые мы выгрузили из базы данных MySql с помощью обычной функции mysql_query().
Для того чтобы проверить работоспособность данного кода, с учетом того, что Вы настроили подключение к базе данных и создали аналогичную таблицу, можете послать такой запрос:
http://ваш_сайт/название _файла. php?id=2
И у Вас должно выгрузиться две строки с id равным 2.
И теперь каждый, которому Вы разрешите выгружать данные может легко экспортировать их на свой локальный компьютер через web интерфейс. Данный способ удобен как для корпоративных пользователей, если Вы разрабатываете приложение для своей организации, так и для пользователей Вашего web сайта в Интернете. Надеюсь, данный способ Вам помог. Удачи!
Заметка! Если Вас интересует язык SQL, рекомендую пройти мой онлайн-курс по основам SQL, который ориентирован на изучение SQL как стандарта, таким образом, Вы сможете работать в любой системе управления базами данных. Курс включает много практики: онлайн-тестирование, задания и многое другое.
Пример ваш помог мне большое спасибо, талька у меня с кодировкой проблема в xcel
Александр: Попробуйте сохранить файл, с данным кодом, в кодировке ANSI. Чтобы это не затронуло другой ваш код, который скорей всего в кодировке UTF, лучше всего вынесите код для выгрузки в отдельный файл с кодировкой ANSI
Добрый день!
Благодарю за код! Но тоже возникла проблема с кодировкой. Сам файл в ANSI, кириллический текст из файла отображается норм, а вот кириллический текст из БД кракозябрами. Правда я попытался адаптировать этот код для работы на вордпресс, может какая то несостыковка из-за этого?
Андрей: Привет, а в базе ты данные хранишь в какой кодировке?
Цитирую Softvt:Андрей: Привет, а в базе ты данные хранишь в какой кодировке?
В вордпресс данные храняться в utf-8.
Привет. Ребята, подскажите, пожалуйста, как решить проблему того, что Excel «жалуется» на то, что файл не соответствует своему расширению. Видимо, какие-то стандарты в оформлении не соблюдаются.
Может, нужно что-то дописать. У вас нет такой проблемы?
Цитата:У вас нет такой проблемы?
Нет, работает 100%
а у меня точно также -жалуется на тоже самое
а где взять класс ExportToExcel?
Цитирую psevdo:а где взять класс ExportToExcel?
Тебе его брать то и не нужно, ты его сам создаешь в начале кода (который указан в статье). Ты его можешь даже назвать подругому
У меня проблема — в базе данных существует текст страницы на сайте с тэгами, этот текст почему то не проходит в вашем скрипте, не могу понять почему, тэги убираю, и все равно не проходит…
У меня тоже проблема с кодировкой. Попробовал изменить кодировку файла на UTF8 — в этом случае Excel жалуется на то что, файл не соответствует своему расширению.
Но данные с БД отображаются нормально. Что сделать чтобы все было ок?
У меня тоже была проблема с кодировкой. Решил проблему так:
После запроса к бд
$sql=»SELECT * FROM test.test where id = $id»;//запрос к базе
добавляем
mysql_query(«set names cp1251»); и радуемся жизни!))
Огромное спасибо автору! Очень просто и доходчиво. Но с кодировками проблема есть и ни ANSI ни cp1251 не помогают..
Warning: Cannot modify header information — headers already sent by (output started at Z:\home\localho st\www\za\blok\ header.php:10) in Z:\home\localho st\www\za\test.php on line 82
Warning: Cannot modify header information — headers already sent by (output started at Z:\home\localho st\www\za\blok\ header.php:10) in Z:\home\localho st\www\za\test.php on line 83
Warning: Cannot modify header information — headers already sent by (output started at Z:\home\localho st\www\za\blok\ header.php:10) in Z:\home\localho st\www\za\test.php on line 84
Warning: Cannot modify header information — headers already sent by (output started at Z:\home\localho st\www\za\blok\ header.php:10) in Z:\home\localho st\www\za\test.php on line 85
Warning: Cannot modify header information — headers already sent by (output started at Z:\home\localho st\www\za\blok\ header.php:10) in Z:\home\localho st\www\za\test.php on line 86
А мне выдает такую ошибку что делать ???
Я думаю проблема в том что до загрузки этого кода, не должно быть не какого вывода.
А в файле header.php идет вывод.
Большое спасибо автору!
База у меня в ЮТФ8
файл в ЮТФ8 без БОМ
после формирования запроса добавил
mysql_query(«set names cp1251»);
все отлично выводится. спасибо!
Привет! Хороший пример экспорта в Exel!!!
У меня возникла проблема при експорте большого текста в Exel ругается что файл поврежден Что это может быть???
sky_host, у меня подобная проблема, как ее победить?
Такая же ситуация что и у Azis
А для обратного действия случаем кода не найдется? =)
Цитирую Антон:А для обратного действия случаем кода не найдется? =)
Я здесь взял: http://pishemsite.ru/php/kak-perenesti-dannie-iz-excel-v-mysql-s-pomoschyu-php
На выходе получаешь массив. Пользуюсь, работает великолепно!
Угу, что-то кодировка совсем страдает, какую не выставляй..
А как сделать что бы просто сохраняло результаты в файл без вывода в окно. Понятно что запрос переделать надо задать id сразу. Подскажите пожалуйста.
У меня почему-то некоторые поля не полностью копируются. В эксель есть какие-то ограничения на длину поля?
Если да, то как поправить?
Приветствую, подскажите, как выгружать всю таблицу, а не конкретные ID
Привет, скорректируй запрос к базе, т.е. вместо
SELECT * FROM test.test WHERE id = $id
Напиши
SELECT * FROM test.test
уже задавался о проблеме экспорта большого файла(жалоба что поврежден). так как ее(эту проблему) решить?
не скажете, почему-то все разделители не действуют и в EXCELL’е все слеплено в единую строку?
Конвертить кракозябры можно этим: iconv(«UTF-8», «windows-1251», $row[‘name’]).
Как это использовать, где данный код вставлять?
Как мне выгружать данные допустим с возрастом.
Выгрузить все строки, у кого возраст равен 27.
Мне нужно все строки выгрузить
Не работает на php 7