Exporting mysql to excel php

dipakcg / export.php

This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode characters

/***** EDIT BELOW LINES *****/

$ DB_Server = » localhost «; // MySQL Server

$ DB_Username = » username «; // MySQL Username

$ DB_Password = » password «; // MySQL Password

$ DB_DBName = » databasename «; // MySQL Database Name

$ DB_TBLName = » tablename «; // MySQL Table Name

$ xls_filename = ‘export_’ .date( ‘Y-m-d’ ). ‘.xls’ ; // Define Excel (.xls) file name

/***** DO NOT EDIT BELOW LINES *****/

// Create MySQL connection

$ sql = » Select * from $ DB_TBLName «;

$ Connect = @mysql_connect( $ DB_Server , $ DB_Username , $ DB_Password ) or die(» Failed to connect to MySQL: » . mysql_error() . » » . mysql_errno());

// Select database

$ Db = @mysql_select_db( $ DB_DBName , $ Connect ) or die(» Failed to select database: » . mysql_error(). » » . mysql_errno());

// Execute query

$ result = @mysql_query( $ sql , $ Connect ) or die(» Failed to execute query: » . mysql_error(). » » . mysql_errno());

// Header info settings

header(» Content-Type: application/xls «);

header(» Content-Disposition: attachment; filename= $ xls_filename «);

header(» Pragma: no-cache «);

header(» Expires: 0 «);

/***** Start of Formatting for Excel *****/

// Define separator (defines columns in excel & tabs in word)

$ sep = «\t»; // tabbed character

// Start of printing column names as names of MySQL fields

for ( $ i = 0 ; $ i

echo mysql_field_name( $ result , $ i ) . «\t»;

>

print(«\n»);

// End of printing column names

// Start while loop to get data

while ( $ row = mysql_fetch_row( $ result ))

$ schema_insert = «»;

for( $ j = 0 ; $ j

if (!isset( $ row [ $ j ]))

$ schema_insert .= » NULL «. $ sep ;

>

elseif ( $ row [ $ j ] != «»)

$ schema_insert .= » $ row [ $ j ]». $ sep ;

>

else

$ schema_insert .= «». $ sep ;

>

>

$ schema_insert = str_replace( $ sep .» $ «, «», $ schema_insert );

$ schema_insert = preg_replace(» / \r\n | \n\r | \n | \r / «, » «, $ schema_insert );

$ schema_insert .= «\t»;

print(trim( $ schema_insert ));

print «\n»;

>

?>

Источник

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

Если нужно быстро и единоразово выгрузить данные из таблицы MySQL в Exel файл, то помогут следующие способы:

Экспорт через PHPMyAdmin

В PHPMyAdmin при экспорте можно выбрать формат «CSV for MS Excel»:

Результат:

Экспорт через HTML таблицу

prepare(«SELECT * FROM `test`»); $sth->execute(); $items = $sth->fetchAll(PDO::FETCH_ASSOC); ?>

ID Категория Название Описание

Результат работы скрипта:

Экспорт через PHPExcel

//spl_autoload_unregister('autoload'); require_once __DIR__ . '/PHPExcel/Classes/PHPExcel.php'; require_once __DIR__ . '/PHPExcel/Classes/PHPExcel/Writer/Excel2007.php'; $xls = new PHPExcel(); $xls->setActiveSheetIndex(0); $sheet = $xls->getActiveSheet(); // Шапка $sheet->getStyle("A1:D1")->getFont()->setBold(true); $sheet->setCellValue("A1", 'ID'); $sheet->setCellValue("B1", 'Категория'); $sheet->setCellValue("C1", 'Название'); $sheet->setCellValue("D1", 'Описание'); // Выборка из БД $dbh = new PDO('mysql:dbname=db_name;host=localhost', 'логин', 'пароль'); $sth = $dbh->prepare("SELECT * FROM `test`"); $items = $sth->fetch(PDO::FETCH_ASSOC); $index = 2; foreach ($items as $row) < $sheet->setCellValue("A" . $index, $row['id']); $sheet->setCellValue("B" . $index, $row['category']); $sheet->setCellValue("C" . $index, $row['name']); $sheet->setCellValue("D" . $index, $row['name']); $index++; > // Отдача файла в браузер header("Expires: Mon, 1 Apr 1974 05:00:00 GMT"); header("Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT"); header("Cache-Control: no-cache, must-revalidate"); header("Pragma: no-cache"); header("Content-type: application/vnd.ms-excel" ); header("Content-Disposition: attachment; filename=prods.xlsx"); $objWriter = new PHPExcel_Writer_Excel2007($xls); $objWriter->save('php://output'); exit(); 

Источник

How to Export MySQL Database Data to Excel/CSV Using PHP

Recently I published an article on How to Read CSV and Excel Files in PHP Using PhpSpreadsheet. One of the readers asked how to use PhpSpreadsheet to export MySQL database records to the Excel file. The user may need their MySQL data in the Excel or CSV file to read or share it easily. In this article, we discussed how one can export data from a database to Excel and CSV files using PHP. In addition to this, we will also study how one can send this exported file as an attachment in the email.

Getting Started

For getting started, you first need to install the PhpSpreadsheet library. I recommend using Composer for the installation of the library. Open the terminal in your project root directory and run the command:

composer require phpoffice/phpspreadsheet

PhpSpreadsheet is the library that provides support for reading and writing different types of file formats. Below is the screenshot of the supported file formats.

Our end goal is to export database table records to the Excel/CSV file. For this, we require a few entries in our database. As an example, I am creating the ‘products’ table by running the below SQL query.

CREATE TABLE `products` ( `id` int(11) NOT NULL AUTO_INCREMENT, `product_name` varchar(255) NOT NULL, `product_sku` varchar(255) NOT NULL, `product_price` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Next, add some dummy entries in the table so you will see a few records in the exported file.

INSERT INTO `products` (`product_name`, `product_sku`, `product_price`) VALUES ('Apple', 'app_111', '$1000'), ('Lenovo', 'len_222', '$999'), ('Samsung', 'sam_689', '$1200'), ('Acer', 'ace_620', '$900');

After this create a config.php file where we write the code for database connection.

connect_error)< die("Unable to connect database: " . $db->connect_error); >

Make sure to replace the placeholders with the actual values in the above code. This file will be included later to fetch records from the products table.

In the next part of the tutorial, I am allocating a separate section for exporting data to Excel, exporting data to CSV, and sending the email with an exported file as an attachment.

Export MySQL Database Data to Excel Using PHP

You have installed the library and also have a database table with few entries. Now you can go ahead and write the actual code which will export an Excel file with data filled in it.

Create export-to-excel.php file in the root directory. In this file, I will write a SQL query to fetch data from the database. This fetched data will be written to Excel and sent the Excel file to the browser to download automatically.

export-to-excel.php

setActiveSheetIndex(0); $activeSheet = $spreadsheet->getActiveSheet(); $activeSheet->setCellValue('A1', 'Product Name'); $activeSheet->setCellValue('B1', 'Product SKU'); $activeSheet->setCellValue('C1', 'Product Price'); $query = $db->query("SELECT * FROM products"); if($query->num_rows > 0) < $i = 2; while($row = $query->fetch_assoc()) < $activeSheet->setCellValue('A'.$i , $row['product_name']); $activeSheet->setCellValue('B'.$i , $row['product_sku']); $activeSheet->setCellValue('C'.$i , $row['product_price']); $i++; > > $filename = 'products.xlsx'; header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename='. $filename); header('Cache-Control: max-age=0'); $Excel_writer->save('php://output');

When you run this code on the browser, an Excel file will be downloaded automatically and the Excel sheet will have the following entries.

Export MySQL Database Data to CSV Using PHP

In the previous section, we exported data to an Excel file. If someone is looking to export data in a CSV file then you need to change a few lines in the above code.

export-to-csv.php

setActiveSheetIndex(0); $activeSheet = $spreadsheet->getActiveSheet(); $activeSheet->setCellValue('A1', 'Product Name'); $activeSheet->setCellValue('B1', 'Product SKU'); $activeSheet->setCellValue('C1', 'Product Price'); $query = $db->query("SELECT * FROM products"); if($query->num_rows > 0) < $i = 2; while($row = $query->fetch_assoc()) < $activeSheet->setCellValue('A'.$i , $row['product_name']); $activeSheet->setCellValue('B'.$i , $row['product_sku']); $activeSheet->setCellValue('C'.$i , $row['product_price']); $i++; > > $filename = 'products.csv'; header('Content-Type: application/text-csv'); header('Content-Disposition: attachment;filename='. $filename); header('Cache-Control: max-age=0'); $Excel_writer->save('php://output');

Send an Email with the File as an Attachment

We have seen how to download the file with data in Excel/CSV format. Some users may want to send the exported file as an attachment in the email. Let’s see how to achieve it.

First, install the PHPMailer library using the command:

composer require phpmailer/phpmailer

After installing the library, you can use any SMTP server to send an email. It’s up to you. You may use your hosting’s SMTP server, AWS SES, or Gmail SMTP server. If you are going with the Gmail SMTP server read our article Send Email Using Gmail API and PHPMailer which explains the configuration needed for it.

The previous code download Excel/CSV file automatically. But now, instead of making it downloadable, we will save the file in the directory and then send it as an attachment. In short, we will write the code below for saving the file.

. . $filename = 'products.csv'; if (!file_exists('files')) < mkdir('files', 0755); >$Excel_writer->save('files/'.$filename);

Our final code to send the attachment in an email will be as follows:

setActiveSheetIndex(0); $activeSheet = $spreadsheet->getActiveSheet(); $activeSheet->setCellValue('A1', 'Product Name'); $activeSheet->setCellValue('B1', 'Product SKU'); $activeSheet->setCellValue('C1', 'Product Price'); $query = $db->query("SELECT * FROM products"); if($query->num_rows > 0) < $i = 2; while($row = $query->fetch_assoc()) < $activeSheet->setCellValue('A'.$i , $row['product_name']); $activeSheet->setCellValue('B'.$i , $row['product_sku']); $activeSheet->setCellValue('C'.$i , $row['product_price']); $i++; > > $filename = 'products.csv'; if (!file_exists('files')) < mkdir('files', 0755); >$Excel_writer->save('files/'.$filename); // send as an attachment $mail = new PHPMailer(true); try < $mail->isSMTP(); $mail->Host = 'SMTP_HOST'; $mail->SMTPAuth = true; $mail->Username = 'SMTP_USERNAME'; //username $mail->Password = 'SMTP_PASSWORD'; //password $mail->SMTPSecure = 'ssl'; $mail->Port = 465; $mail->setFrom('FROM_EMAIL_ADDRESS', 'FROM_NAME'); $mail->addAddress('RECEPIENT_EMAIL_ADDRESS', 'RECEPIENT_NAME'); $mail->addAttachment('files/'.$filename); $mail->isHTML(true); $mail->Subject = 'Products Sheet'; $mail->Body = 'Products Sheet'; $mail->send(); echo 'Message has been sent'; > catch (Exception $e) < echo 'Message could not be sent. Mailer Error: '. $mail->ErrorInfo; >

Replace the placeholders with the actual values and an email will be sent with an attachment to the recipient’s email address.

I hope you understand how to export MySQL database data to the Excel or CSV file and also send it as an attachment. Please share your thoughts and suggestions in the comment section below.

Related Articles

If you liked this article, then please subscribe to our YouTube Channel for video tutorials.

Источник