Экспорт данных из MySQL в Excel. Два простых способа

 

Понадобилось мне недавно прикрутить экспорт таблиц к сайту, но с PHP Exсel сразу решил не заморачиваться. Сначала я воспользовался самым простым способом, используя  MIME TYPE application/vnd.ms-excel. Создал файл excel.php (листинг ниже). Вызов файла exel.php сделав через событие onclick.

Следует учесть, что экспорт становится доступным на странице  мой_домен/excel.php, поэтому, чтобы не переходить на другую страницу, я и использовал событие onclick.

<button  type="submit" class="btn btn-primary" 
onClick="document.location.href='/excel.php'">Экспорт в Exel
</button>

Ниже привожу листинг кода:

<?PHP
function cleanData(&$str)
{
$str = preg_replace("/\t/", "\\t", $str);
$str = preg_replace("/\r?\n/", "\\n", $str);
if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
}
 // Имя загружаемого файла файла. 
 //В моём примере получится otched_20150331.xls
$filename = "otchet_" . date('Ymd') . ".xls";

header("Content-Disposition: attachment; filename=\"$filename\"");
header("Content-Type: application/vnd.ms-excel");

// Подключение к бд
mysql_connect ("Localhost","user","pass");// Хост юзер и пароль
mysql_select_db("my_bd") or die (mysql_error());// Имя базы данных

//Указать кодировку выводимых данных
mysql_query('SET character_set_database = cp1251_general_ci'); 
mysql_query ("SET NAMES 'cp1251'");

//запрос и вывод данных
$flag = false;
 $result = mysql_query("SELECT * FROM user ORDER BY Data DESC") 
 or die('Запрос не выполнен!');
 while(false !== ($row = mysql_fetch_assoc($result))) {
   if(!$flag) {
     // Вывод заголовков
     echo implode("\t", array_keys($row)) . "\r\n";
     $flag = true;
    }
    //Вывод данных столбцов    
     array_walk($row, 'cleanData');
     echo implode("\t", array_values($row)) . "\r\n";
  }
  exit;
?>

Подробнее о типах данных, особенностях их использования и диапазонах чисел: https://www.delay-delo.com/content/shpargalka-tipy-dannyh-mysql

 

Но вскоре изменились требования. Во первых, стало необходимым добавлять права доступа к отчётам разным пользователям. Доступ к информации само собой ограничивался правами. Пришлось скрипт значительно апгрейдить. За основу я взял давно гуляющий по сети класс ExportToExcel, добавив небольшую проверку на права. Всё тот же файл excel.php  стал вызываться несколько иным способом. Точнее, я к событию onclick добавил просто код уровня доступа. И ссылка на страницу экспорта приняла следующий вид мой_домен/excel.php?id=23. В листинге переменная $accsess - передаёт в виде цифры код или уровень  доступа. Таким образом, каждый пользователь получит именно "свой" отчёт.

<button type="submit" class="btn btn-primary"
onClick="document.location.href='/excel.php?id=<?php echo $accsess;?>'">
Экспорт в Exel</button>

Ниже привожу листинг кода:

<?php
 mysql_connect ("localhost","user","pass");//подключение к серверу
 mysql_select_db("my_db") or die (mysql_error());//выбор базы данных
 //Указать кодировку выводимых данных
 mysql_query('SET character_set_database = cp1251_general_ci');
 mysql_query ("SET NAMES 'cp1251'");
 
 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 = 'user_export_'.$id; // задаем имя файла
 $excel = new ExportToExcel(); // создаем экземпляр класса
 $accsess = $id;  //не обязательная строка,сохранена для логики
 //проверка доступа, в моём случае доступ 0 означает полный доступ
 if (  $accsess == 0)  {   $sql="SELECT * FROM users"; //запрос к базе}
 else { $sql="SELECT * FROM users WHERE lvl = '$accsess'";//запрос к базе  согласно уровню допуска}
 $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['Name']);
 $excel->InsertText($row['Post']); 
 $excel->GoNewLine();
 }
 $excel->SaveFile($filename);
} 
?>

 

Прикреплённые файлы
excel_v1.rar917 байт
excel_v2.rar1.55 КБ
Сергей 27 июля, 2016

Здравствуйте! А как можно сделать по первому способу заголовки столбцов, такие как мне нужно, а не вывод названий переменных?

Андрей 27 июля, 2016

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

//запрос и вывод данных
$flag = false;
$result = mysql_query("SELECT * FROM user ORDER BY Data DESC")
or die('Запрос не выполнен!');
$arr = array('Заголовок1', 'Заголовок2', 'Заголовок3');
echo implode("\t", $arr) . "\r\n";
while(false !== ($row = mysql_fetch_assoc($result))) {
//Вывод данных столбцов
array_walk($row, 'cleanData');
echo implode("\t", array_values($row)) . "\r\n";
}
exit;

Елена 01 сентября, 2016

Здравствуйте! Подскажите, как можно сделать (по первому способу) выборку данных и последующий экспорт в excel?

SuperAdmin 01 сентября, 2016

Строки все прокомментированы.
Во первых присоединиться к базе данных в строках
// Подключение к базе данных
mysql_connect ("Localhost","user","pass");// Хост(обчно это "localhost") пользователь и пароль к базе данных - заменить на свои
mysql_select_db("my_bd") or die (mysql_error());// Имя базы данных "my_bd" - заменить на свою
Второй шаг создать свой запрос где звёздочка все данные из таблицы а "user" это название таблицы в базе данных
$result = mysql_query("SELECT * FROM user ORDER BY Data DESC")

После чего файл запустить скрипт и рядом появится otchet_"дата".xls файл.

Елена 06 сентября, 2016

Спасибо за подробное объяснение! Но я имела ввиду, если создать запрос с выборкой:
$result = mysql_query("SELECT * FROM user WHERE столбец(из таблицы)='Иван'")
После чего получить .xls файл таблицы с выборкой по запросу.
(Я пыталась, но скачивается пустой файл.)

SuperAdmin 06 сентября, 2016

Тут два варианта.
Первый.
Это неправильно построен sql запрос.
Второй
Функция mysql_connect() устаревшая и возможно в xml записывается ошибка.
В первом случае напишите название таблицы и по каким критериям сортировать я подскажу. Во втором нужно использовать версию php не старше 5.6
Данные можно выслать чрез форму обратной связи на сайте.

Анна 21 ноября, 2016

Здравствуйте! Скажите, а как можно настроить ширину столбцов и цвет ячеек в заголовках? ( По первому способу)

SuperAdmin 21 ноября, 2016

Это легко сделать стилями css. Используя плагин http://tablesorter.com/docs/ можно добиться удобной сортировки.

Михаил 21 декабря, 2016

Как сделать запрос через BETWEEN ?
Я пробовал делать типо вот этого
//запрос и вывод данных
$flag = false;
$data1=$_GET['data1'];
$data2=$_GET['data2'];
$result = mysql_query("SELECT * FROM `dogovor` WHERE `okonchanie_deystvia` BETWEEN '$data1' AND '$data2'")
or die('Запрос не выполнен!');
while(false !== ($row = mysql_fetch_assoc($result))) {
if(!$flag) {

А на странице где ввожу дату вот так
d="get" action="ot1.php">';
echo "Окончание действия договора:";
echo "";
echo "";
echo "";
echo "";
?>

Экспорт в Exel

<?php
echo'';
echo "Начало действия договора:";
echo "";
echo "";
echo "";
echo "";

?>

Экспорт в Exel

Алексей 22 декабря, 2016

Запрос верен. Если не работает, проблема в формате даты. Советую в timestamp привести

Василий 22 декабря, 2016

Как сделать что бы при выполнении скрипта номер 1 не выскакивало меню, (сохранить или открыть) а сразу сохраняло в указанное место. Спасибо.

Василий 22 декабря, 2016

Пользуйте этот код - он сохраняет без запроса. Спасибо за правку кода Metotron-у

<?PHP
session_start();
$z1 = $_SESSION['session_full_name'];
$z2 = $_SESSION['session_email'];

function cleanData(&$str)
{
$str = preg_replace("/\t/", "\\t", $str);
$str = preg_replace("/\r?\n/", "\\n", $str);
if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
}
// Имя загружаемого файла файла.
$filename = "otchet_" . date('Ymd') . ".xls";

header('HTTP/1.0 200 OK');

#header("Content-Disposition: attachment; filename=\"$filename\"");
#header("Content-Type: application/vnd.ms-excel");
# unlink($filename);
#die;
#exit;

// Подключение к бд
mysql_connect ("localhost","root","");// Хост юзер и пароль
mysql_select_db("bas") or die (mysql_error());// Имя базы данных

//Кодировка выводимых данных
mysql_query('SET character_set_database = cp1251_general_ci');
mysql_query ("SET NAMES 'cp1251'");

//запрос и вывод данных
$flag = false;

$result = mysql_query("SELECT id, tseh, model, virez, razmer, zakritost, zgestkost, polnota, material, tsvet, kolvo, prime4anie, date_today FROM items WHERE email_1='". $_SESSION['session_email']."'");

while(false !== ($row = mysql_fetch_assoc($result))) {
if(!$flag) {
// Вывод заголовков

$arr = array('id', 'цех', 'модель', 'вырез', 'размер', 'закрытость', 'жесткость','полнота', 'материал', 'цвет', 'кол-во', 'примечание', 'дата');
echo implode(" ", $arr) . "\r\n";

# echo implode("\t", array_keys($row)) . "\r\n";
file_put_contents($filename, implode("\t", array_keys($row))."\r\n", LOCK_EX | FILE_APPEND);
$flag = true;
}
//Вывод данных столбцов
array_walk($row, 'cleanData');
# echo implode("\t", array_values($row)) . "\r\n";
file_put_contents($filename, implode("\t", array_values($row)). "\r\n", LOCK_EX | FILE_APPEND);
}

die;
exit;
?>

Никита 20 июня, 2017

здравствуйте, а можно сделать просто вывод числа 10 с базы данных в таблицу excel?

Sergey 10 августа, 2017

Если заголовки ставить так $arr = array('Заголовок1', 'Заголовок2', 'Заголовок3'); то выводит кракозябры. Как лечится?

Игорь 17 марта, 2018

Доволен первым простым способом . Только одно но. Уже когда открываю экспортированный файл excel в одном из столбцов данные отображаются не как число с десятичной точкой (25.4) а 25.апреля. При смене типа ячеек в этом столбце в числовой- вообще отображается что-то типа 43215,0 хотя должно быть 25.4 Где берутся левые числа? Пробовал запускать часть файла, где только подключение к БД и вывод данных , данные выводятся нормально, как 25.4 Что делать?

Игорь 17 марта, 2018

Сам и нашел сразу ))). В базе данных числа хранятся с десятичной точкой. Excel их не понимает как числа. Я в коде после $str = preg_replace("/\r?\n/", "\\n", $str); вставил $str = str_replace(' . ' , ' , ' ,$str); ,где точки заменяем запятыми, а Excel числа с десятичными запятыми уже нормально отображает.

Игорь 17 марта, 2018

Возник еще один вопрос при открытии экспортированного файла, выдается предупреждение "Действительный формат открываемого файла (имя файла) отличается от указываемого его расширением имени файла. Перед открытием данного файла убедитесь что он не поврежден и получен из надежного источника."
Когда открываешь файл- в нем все красиво отображается. В чем проблема может быть такого сообщения?

SuperAdmin 17 марта, 2018

Возможно проблема в форматах .xls и .xlsx. Попробуйте конвертировать.

billov 29 июня, 2018

Вот мы скачали таблицу отредактировали и как ее загрузить обратно с измененными данными?

SuperAdmin 05 июля, 2018

Это скрипт экспорта данных в exel, и импорт не предусмотрен. Он пригодиться только для формирования отчётов.

Добрый день 21 августа, 2018

Подскажите пожалуйста в чем может быть проблема если в скаченном файле слова: "Запрос не выполнен"

your-name 21 августа, 2018

У меня в БД таблица из 11 колонок

SuperAdmin 21 августа, 2018

Во втором варианте просто добавить заголовки таблиц с $excel->InsertText('Номер'); с названием полей и название полей в цикле While в виде $excel->InsertNumber($row['название столбца']);
Добавить можно любое количество.

SuperAdmin 21 августа, 2018

Скорее все запрос не верен. Нужно проверить синтаксис. Неправильно указаны название полей или иные критерии выборки

Василий 24 августа, 2018

Подскажите как отправить этот выгружаемый файл на E-Mail?

VabriokWorry 15 декабря, 2018

Если вы захотели получить быстрый займ, вам следует определиться с компанией, где оптимально взять займ online. В наши дни не все компании предоставляют срочные займы. Если вы стремитесь получить мгновенные займы, вам надо просмотреть перечень МФО, которые предоставляют займы на приемлемых условиях.

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

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

Если вы желаете взять займ на любую карту мгновенно круглосуточно без отказа , вам нужно выбрать условия. Если вы добросовестный заемщик, финансы будут переведены вам в сжатые сроки. Получить средства реально в рублях на определенный срок. Надо отметить, что сервис гарантирует максимальную конфиденциальность. На портале собраны МФО, которые всегда идут клиентам навстречу. Новые лица также не будут ощущать какого-то дискомфорта. Лояльные условия кредитования также доступны для многих клиентов.

У вас есть возможность получить микрозаймы быстрее, чем это проходит в банковских учреждениях. Вам не нужно собирать документы. Быстрые займы – универсальный выход, если вам срочно необходимы деньги. Для получения средств необходимо заполнить анкету на портале МФО. С вами моментально свяжется менеджер и обсудит все детали.

Если вы не уверены, где можно изучить список лучших МФО, можете смело изучить его на сайте. Лучшие микрофинансовые организации есть на портале. У всех сервисов есть техническая поддержка. Получить займ вы можете на банковскую карту любого банка. Вы можете также получить финансовые сбережения за 5 минут без каких-то вопросов от банковских сотрудников.

После того, как ваша заявка будет подтверждена, вы получите подтверждение. Более 90% заявок получают одобрение. Оформить мирозайм реально даже через мобильный телефон. Для этого понадобится иметь выход в сеть. Также вам нужен будет паспорт и банковская карта, куда вы будете принимать деньги.

Оставить комментарий

Похожие материалы