PHP – выгрузка данных в Excel из базы MySQL

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

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

Для начала приведу пример конечного xls файла, в программе Excel выгрузка будет выглядеть примерно так:

Скриншот 1

Другими словами, никаких картинок, стилей выгружено не будет, только заголовки столбцов и сами данные.

До того как я пришел именно к такому варианту выгрузки я пробовал выгружать в формат 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 как стандарта, таким образом, Вы сможете работать в любой системе управления базами данных. Курс включает много практики: онлайн-тестирование, задания и многое другое.

Понравилась статья? Поделиться с друзьями:
Заметки IT специалиста
Комментарии: 33
  1. Александр

    Пример ваш помог мне большое спасибо, талька у меня с кодировкой проблема в xcel

  2. Админ (автор)

    Александр: Попробуйте сохранить файл, с данным кодом, в кодировке ANSI. Чтобы это не затронуло другой ваш код, который скорей всего в кодировке UTF, лучше всего вынесите код для выгрузки в отдельный файл с кодировкой ANSI

  3. Андрей

    Добрый день!
    Благодарю за код! Но тоже возникла проблема с кодировкой. Сам файл в ANSI, кириллический текст из файла отображается норм, а вот кириллический текст из БД кракозябрами. Правда я попытался адаптировать этот код для работы на вордпресс, может какая то несостыковка из-за этого?

  4. Админ (автор)

    Андрей: Привет, а в базе ты данные хранишь в какой кодировке?

  5. Андрей

    Цитирую Softvt:Андрей: Привет, а в базе ты данные хранишь в какой кодировке?
    В вордпресс данные храняться в utf-8.

  6. Кирилл

    Привет. Ребята, подскажите, пожалуйста, как решить проблему того, что Excel «жалуется» на то, что файл не соответствует своему расширению. Видимо, какие-то стандарты в оформлении не соблюдаются.
    Может, нужно что-то дописать. У вас нет такой проблемы?

  7. Админ (автор)

    Цитата:У вас нет такой проблемы?
    Нет, работает 100%

    1. Eu

      а у меня точно также -жалуется на тоже самое

  8. psevdo

    а где взять класс ExportToExcel?

  9. Админ (автор)

    Цитирую psevdo:а где взять класс ExportToExcel?
    Тебе его брать то и не нужно, ты его сам создаешь в начале кода (который указан в статье). Ты его можешь даже назвать подругому

  10. funlord

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

  11. Nurlan

    У меня тоже проблема с кодировкой. Попробовал изменить кодировку файла на UTF8 — в этом случае Excel жалуется на то что, файл не соответствует своему расширению.
    Но данные с БД отображаются нормально. Что сделать чтобы все было ок?

  12. Аркадий

    У меня тоже была проблема с кодировкой. Решил проблему так:
    После запроса к бд
    $sql=»SELECT * FROM test.test where id = $id»;//запрос к базе

    добавляем

    mysql_query(«set names cp1251»); и радуемся жизни!))

  13. sanchous

    Огромное спасибо автору! Очень просто и доходчиво. Но с кодировками проблема есть и ни ANSI ни cp1251 не помогают..

  14. Azis

    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

    А мне выдает такую ошибку что делать ???

    1. NikolayS93

      Я думаю проблема в том что до загрузки этого кода, не должно быть не какого вывода.

      А в файле header.php идет вывод.

      Большое спасибо автору!

  15. WisesT

    База у меня в ЮТФ8
    файл в ЮТФ8 без БОМ
    после формирования запроса добавил
    mysql_query(«set names cp1251»);
    все отлично выводится. спасибо!

  16. sky_host

    Привет! Хороший пример экспорта в Exel!!!
    У меня возникла проблема при експорте большого текста в Exel ругается что файл поврежден Что это может быть???

  17. Александр

    sky_host, у меня подобная проблема, как ее победить?

  18. Rabbiteggs

    Такая же ситуация что и у Azis

  19. Антон

    А для обратного действия случаем кода не найдется? =)

    1. Евгений

      Цитирую Антон:А для обратного действия случаем кода не найдется? =)
      Я здесь взял: http://pishemsite.ru/php/kak-perenesti-dannie-iz-excel-v-mysql-s-pomoschyu-php
      На выходе получаешь массив. Пользуюсь, работает великолепно!

  20. Макс

    Угу, что-то кодировка совсем страдает, какую не выставляй..

  21. Василий

    А как сделать что бы просто сохраняло результаты в файл без вывода в окно. Понятно что запрос переделать надо задать id сразу. Подскажите пожалуйста.

  22. Андрей

    У меня почему-то некоторые поля не полностью копируются. В эксель есть какие-то ограничения на длину поля?
    Если да, то как поправить?

  23. Kir_lays

    Приветствую, подскажите, как выгружать всю таблицу, а не конкретные ID

    1. Админ (автор)

      Привет, скорректируй запрос к базе, т.е. вместо
      SELECT * FROM test.test WHERE id = $id
      Напиши
      SELECT * FROM test.test

  24. Eu

    уже задавался о проблеме экспорта большого файла(жалоба что поврежден). так как ее(эту проблему) решить?

  25. Eu

    не скажете, почему-то все разделители не действуют и в EXCELL’е все слеплено в единую строку?

  26. barkmann

    Конвертить кракозябры можно этим: iconv(«UTF-8», «windows-1251», $row[‘name’]).

    1. J

      Как это использовать, где данный код вставлять?

  27. Богдан

    Как мне выгружать данные допустим с возрастом.
    Выгрузить все строки, у кого возраст равен 27.
    Мне нужно все строки выгрузить

  28. J

    Не работает на php 7

Добавить комментарий для barkmann Отменить ответ

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:
Нажимая на кнопку «Отправить комментарий», я даю согласие на обработку персональных данных и принимаю политику конфиденциальности.