Иногда вам может потребоваться экспорт в формат Excel из MySQL для локального использования. База данных или таблицы базы данных MySQL могут быть экспортированы в различные форматы файлов, такие как CSV, XML, SQL, Excel и т. д., С помощью клиента PHP, phpMyAdmin. Также можно экспортировать данные MySQL с помощью сценария PHP вместо экспорта данных вручную. Когда веб-приложению требуется, чтобы данные из базы данных MySQL хранились в формате Excel, для выполнения этой задачи лучше всего использовать скрипт PHP. В этой статье показано, как экспортировать данные MySQL с помощью сценария PHP.
Предпосылки
Перед экспортом любых данных MySQL выполните следующие задачи. Вы должны открыть клиент MySQL в терминале или клиент phpMyAdmin в браузере для выполнения следующих задач. Таблица с пятью записями будет создана в базе данных после выполнения следующих команд SQL.
A. Создайте базу данных MySQL
Выполните следующую команду SQL, чтобы создать базу данных с именем компании.
CREATE database `company`;
Б. Создайте таблицу
Выполните следующую команду SQL, чтобы создать таблицу с именем items.
CREATE TABLE `items` ( `id` int(11) AUTO_INCREMENT PRIMARY KEY, `name` varchar(100) NOT NULL, `type` varchar(20) NOT NULL, `brand` varchar(50) NOT NULL, `price` double(5,2) NOT NULL ) ENGINE=InnoDB;
C. Вставить данные в таблицу
INSERT INTO `items` (`name`,`type`,`brand`,`price`) VALUES
('Samsung A40s', 'Mobile','Sansung',300.00),
('Samsung 4523 40” inche', 'TV','Samsung',250.00),
('Walton Large Fridge', 'Fridge','Walton',400.00),
('LG 15” Monitor', 'Monitor','LG',100.00),
('DELL gaming Laptop', 'Laptop','Dell',450.00);
Если вы откроете таблицу элементов из клиента phpMyAdmin.
Экспорт данных MySQL в файл Excel
Здесь вы должны создать файл PHP с именем read_and_export.php, чтобы создать следующий сценарий, который считывает записи в таблице элементов и создает файл Excel с содержимым таблицы элементов. В сценарии объявляется объект подключения к базе данных для извлечения данных из таблицы базы данных. Затем определяется запрос выбора для чтения всех записей из таблицы элементов, которые хранятся в переменной с именем $ items. Эта переменная используется для отображения содержимого таблицы в табличной форме и создания файла Excel с содержимым таблицы на основе действий пользователя. Когда пользователь нажимает кнопку, которая позже будет создана в документе HTML для экспорта данных таблицы в формате Excel, функция isset() этого скрипта вернет значение «true». Чтобы создать файл Excel, имя файла определяется с расширением xsl. Необходимая информация заголовка передается с помощью функции header(). Затем переменная $heading используется для установки заголовка столбцов в файле Excel. Ключевые значения переменной $ items используются в качестве значений заголовка. Затем функция implode() используется для записи записей таблицы элементов в файл Excel.
read_and_export.php
<?php
query($query);
$items = array();
// Сохраняем записи таблицы в массив
while( $row = $result->fetch_assoc() ) {
$items[] = $row;
}
// Проверяем, нажата ли кнопка экспорта
if(isset($_POST["export"])) {
// Определим имя файла с текущей датой
$fileName = "itemdata-".date('d-m-Y').".xls";
// Устанавливаем информацию заголовка для экспорта данных в формате Excel
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename='.$fileName);
// Устанавливаем переменную в false для заголовка
$heading = false;
// Добавляем данные таблицы MySQL в файл Excel
if(!empty($items)) {
foreach($items as $item) {
if(!$heading) {
echo implode("\t", array_keys($item)) . "\n";
$heading = true;
}
echo implode("\t", array_values($item)) . "\n";
}
}
exit();
}
?>
Создайте еще один файл PHP с именем index.php со следующим кодом для отображения записей в таблице элементов в браузере с помощью кнопки «Экспорт в Excel». Здесь файл read_and_export.php включен в начало сценария, чтобы установить соединение с базой данных, прочитать данные таблицы и создать файл Excel с данными таблицы, когда пользователь нажимает кнопку. Данные таблицы будут отображаться в табличном формате с использованием начальной загрузки и jQuery.
index.php
<? php
// Добавить скрипт для чтения данных MySQL и экспорта в Excel
include ( "read_and_export.php" ) ;
?>
<!DOCTYPE html>
<html>
<head>
<title>Экспорт данных MySQL в Excel с помощью PHP</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link rel="stylesheet"
href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css">
<link rel="stylesheet"
href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap-theme.min.css">
<script
src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script
src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container">
<center><br/><br/><h2
style='color:green'>Информация о таблице товаров</h2></center>
<div class="col-sm-12">
<div>
<form action="#" method="post">
<button type="submit" id="export" name="export"
value="Export to excel" class="btn btn-success">Экспорт в Excel</button>
</form>
</div>
</div>
<br/>
<table id="" class="table table-striped table-bordered">
<tr>
<th>ID</th>
<th>Имя</th>
<th>Тип</th>
<th>Марка</th>
<th>Цена</th>
</tr>
<tbody>
<?php foreach($items as $item) { ?>
<tr>
<td><?php echo $item ['id']; ?></td>
<td><?php echo $item ['name']; ?></td>
<td><?php echo $item ['type']; ?></td>
<td><?php echo $item ['brand']; ?></td>
<td>$<?php echo $item ['price']; ?></td>
</tr>
<?php } ?>
</tbody>
</table>
</div>
</body>
</html>
Выход
Здесь файлы read_and_export.php и index.php хранятся в папке var var/www/html/php/export. Запустите следующий URL-адрес в браузере, чтобы запустить index.php.
http://localhost/php/export
При нажатии кнопки «Экспорт в Excel» появится следующее диалоговое окно. Установите переключатель «Сохранить файл» и нажмите кнопку «ОК», чтобы создать и загрузить файл Excel с данными таблицы.
Когда вы щелкаете загруженный файл, чтобы открыть его, появляется следующее диалоговое окно, в котором вы можете выбрать необходимые параметры перед отображением файла. Данные в каждом столбце разделяются табуляцией (\t) во время создания файла Excel. Таким образом, здесь выбирается вкладка перед созданием файла.
Вывод
Файл Excel можно создать из таблицы MySQL, разделив значения столбцов определенным разделителем. Разделителем может быть запятая (,) табуляция (\t), точка с запятой (;), пробел ( ) или другие конкретные символы. В этой статье табуляция используется в качестве разделителя. Перед отображением содержимого файла Excel необходимо выбрать соответствующий символ в диалоговом окне «Импорт текста». Если в содержимом данных таблицы MySQL используются одинарные или двойные кавычки, то эти данные не будут сохранены в файле Excel. Я надеюсь, что это руководство помогло вам узнать, как экспортировать данные таблицы MySQL в файл Excel.