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 сайта в Интернете. Надеюсь, данный способ Вам помог. Удачи!

Понравилась статья? Поделиться с друзьями:
Заметки IT специалиста
Комментариев: 30
  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’]).

Добавить комментарий

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