Do you know jQuery Datatables supports to exports it's data to different file format like Excel sheet, CSV (comma-separated values) and PDF by using PHP script with Ajax. That means if we have use jQuery Datatables plugin has been use for display our dynamic data in tabular format on web page then it is very easy for us to export jQuery Datatables data to Excel, CSV and PDF file format.
Currently we want to make Excel, CSV or PDF file from HTML table we have to mainually copy data from webpage to different file and make CSV or Excel or PDF file for our data. But this jQuery Datatables plugin has feature for export table data into different file format like Excel, CSV, PDF. We want to just enable this feature in Datatables at the time of initialization of this plugin. We have to add some jQuery Datables library for enable this feature.
So, In this post you can find Data exporting functionality by using jQuery Datatables plugin with PHP script and Ajax. There are many viewers has request us to add this type of tutorial, so we have publish this tutorial on our website. In this tutorial we have step by step describe how to export data from jQuery Datatables to Excel sheet, CSV file, Copy into Clipboart and make PDF file from Data using PHP script with Ajax.
If you want to use this data export feature in your project, so first you have to load all data on web page, because this functionality only export only those data which we can see on web page. So for export all data to different file we want to first load on web page then after we can export into Excel, CSV, PDF. Otherwise it will export only those data which we can see on web page, not export whole data to Excel or CSV or PDF file format.
See Also
Source Code
database_connection.php
First we have to make database connection, so we can fetch data from Mysql database and display on webpage.
<?php
//database_connection.php
$connect = new PDO("mysql:host=localhost;dbname=testing", "root", "");
?>
index.php
After making of database connection, We have to go to index.php file, here we can see we have imported jQuery Datatables library for enable export button feature for this we have to import following library into our index.php file.
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/r/dt/jq-2.1.4,jszip-2.5.0,pdfmake-0.1.18,dt-1.10.9,af-2.0.0,b-1.0.3,b-colvis-1.0.3,b-html5-1.0.3,b-print-1.0.3,se-1.0.1/datatables.min.css"/>
<script type="text/javascript" src="https://cdn.datatables.net/r/dt/jq-2.1.4,jszip-2.5.0,pdfmake-0.1.18,dt-1.10.9,af-2.0.0,b-1.0.3,b-colvis-1.0.3,b-html5-1.0.3,b-print-1.0.3,se-1.0.1/datatables.min.js"></script>
By using this library we can enable export button feature in jQuery Datatables. After this we have define HTML table for initialization jQuery Datatable plugin on this page based on id selectore #customer_data. After this we have initialize this plugin by using Datatable() method. In this method we have define Ajax request which has been send to fetch.php for fetch data from Mysql table display on web page. For make export button we have add dom: 'lBfrtip', option. This option will make export button above jQuery Datatable and for button name we have add buttons option. In this option we can define which button we want to display on web page. This whole source code you can find below.
<html>
<head>
<title>Export jQuery Datatables Data to Excel, CSV, PDF using PHP Ajax</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/r/dt/jq-2.1.4,jszip-2.5.0,pdfmake-0.1.18,dt-1.10.9,af-2.0.0,b-1.0.3,b-colvis-1.0.3,b-html5-1.0.3,b-print-1.0.3,se-1.0.1/datatables.min.css"/>
<script type="text/javascript" src="https://cdn.datatables.net/r/dt/jq-2.1.4,jszip-2.5.0,pdfmake-0.1.18,dt-1.10.9,af-2.0.0,b-1.0.3,b-colvis-1.0.3,b-html5-1.0.3,b-print-1.0.3,se-1.0.1/datatables.min.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container box">
<h3 align="center">Export jQuery Datatables Data to Excel, CSV, PDF using PHP Ajax</h3>
<br />
<div class="table-responsive">
<table id="customer_data" class="table table-bordered table-striped">
<thead>
<tr>
<th>Customer Name</th>
<th>Gender</th>
<th>Address</th>
<th>City</th>
<th>Postal Code</th>
<th>Country</th>
</tr>
</thead>
</table>
</div>
</div>
<br />
<br />
</body>
</html>
<script type="text/javascript" language="javascript" >
$(document).ready(function(){
$('#customer_data').DataTable({
"processing" : true,
"serverSide" : true,
"ajax" : {
url:"fetch.php",
type:"POST"
},
dom: 'lBfrtip',
buttons: [
'excel', 'csv', 'pdf', 'copy'
],
"lengthMenu": [ [10, 25, 50, -1], [10, 25, 50, "All"] ]
});
});
</script>
fetch.php
This file has received ajax request for fetch data from Mysql database. Here you can find complete PHP script for fetch data from Mysql table and send this data to Ajax request in json format.
<?php
//fetch.php
include('database_connection.php');
$column = array('CustomerName', 'Gender', 'Address', 'City', 'PostalCode', 'Country');
$query = "SELECT * FROM tbl_customer ";
if(isset($_POST['search']['value']))
{
$query .= '
WHERE CustomerName LIKE "%'.$_POST['search']['value'].'%"
OR Gender LIKE "%'.$_POST['search']['value'].'%"
OR Address LIKE "%'.$_POST['search']['value'].'%"
OR City LIKE "%'.$_POST['search']['value'].'%"
OR PostalCode LIKE "%'.$_POST['search']['value'].'%"
OR Country LIKE "%'.$_POST['search']['value'].'%"
';
}
if(isset($_POST['order']))
{
$query .= 'ORDER BY '.$column[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'].' ';
}
else
{
$query .= 'ORDER BY CustomerID DESC ';
}
$query1 = '';
if($_POST['length'] != -1)
{
$query1 = 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}
$statement = $connect->prepare($query);
$statement->execute();
$number_filter_row = $statement->rowCount();
$statement = $connect->prepare($query . $query1);
$statement->execute();
$result = $statement->fetchAll();
$data = array();
foreach($result as $row)
{
$sub_array = array();
$sub_array[] = $row['CustomerName'];
$sub_array[] = $row['Gender'];
$sub_array[] = $row['Address'];
$sub_array[] = $row['City'];
$sub_array[] = $row['PostalCode'];
$sub_array[] = $row['Country'];
$data[] = $sub_array;
}
function count_all_data($connect)
{
$query = "SELECT * FROM tbl_customer";
$statement = $connect->prepare($query);
$statement->execute();
return $statement->rowCount();
}
$output = array(
'draw' => intval($_POST['draw']),
'recordsTotal' => count_all_data($connect),
'recordsFiltered' => $number_filter_row,
'data' => $data
);
echo json_encode($output);
?>
Database
By using below SQL script you can make customer table in your database.
--
-- Database: `testing`
--
-- --------------------------------------------------------
--
-- Table structure for table `tbl_customer`
--
CREATE TABLE `tbl_customer` (
`CustomerID` int(11) NOT NULL,
`CustomerName` varchar(250) NOT NULL,
`Gender` varchar(30) NOT NULL,
`Address` text NOT NULL,
`City` varchar(250) NOT NULL,
`PostalCode` varchar(30) NOT NULL,
`Country` varchar(100) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `tbl_customer`
--
INSERT INTO `tbl_customer` (`CustomerID`, `CustomerName`, `Gender`, `Address`, `City`, `PostalCode`, `Country`) VALUES
(1, 'Maria Anders', 'Female', 'Obere Str. 57', 'Berlin', '12209', 'Germany'),
(2, 'Ana Trujillo', 'Female', 'Avda. de la Construction 2222', 'Mexico D.F.', '5021', 'Mexico'),
(3, 'Antonio Moreno', 'Male', 'Mataderos 2312', 'Mexico D.F.', '5023', 'Mexico'),
(4, 'Thomas Hardy', 'Male', '120 Hanover Sq.', 'London', 'WA1 1DP', 'United Kingdom'),
(5, 'Paula Parente', 'Female', 'Rua do Mercado, 12', 'Resende', '08737-363', 'Brazil'),
(6, 'Wolski Zbyszek', 'Male', 'ul. Filtrowa 68', 'Walla', '01-012', 'Poland'),
(7, 'Matti Karttunen', 'Male', 'Keskuskatu 45', 'Helsinki', '21240', 'Finland'),
(8, 'Karl Jablonski', 'Male', '305 - 14th Ave. S. Suite 3B', 'Seattle', '98128', 'United States'),
(9, 'Paula Parente', 'Female', 'Rua do Mercado, 12', 'Resende', '08737-363', 'Brazil'),
(10, 'John Koskitalo', 'Male', 'Torikatu 38', 'Oulu', '90110', 'Finland'),
(39, 'Ann Devon', 'Female', '35 King George', 'London', 'WX3 6FW', 'United Kingdom'),
(38, 'Janine Labrune', 'Female', '67, rue des Cinquante Otages', 'Nantes', '44000', 'Finland'),
(37, 'Kathryn Segal', 'Female', 'Augsburger Strabe 40', 'Ludenscheid Gevelndorf', '58513', 'Germany'),
(36, 'Elizabeth Brown', 'Female', 'Berkeley Gardens 12 Brewery', 'London', 'WX1 6LT', 'United Kingdom'),
(30, 'Trina Davidson', 'Female', '1049 Lockhart Drive', 'Barrie', 'ON L4M 3B1', 'Canada'),
(31, 'Jeff Putnam', 'Male', 'Industrieweg 56', 'Bouvignies', '7803', 'Belgium'),
(32, 'Joyce Rosenberry', 'Female', 'Norra Esplanaden 56', 'HELSINKI', '380', 'Finland'),
(33, 'Ronald Bowne', 'Male', '2343 Shadowmar Drive', 'New Orleans', '70112', 'United States'),
(34, 'Justin Adams', 'Male', '45, rue de Lille', 'ARMENTIERES', '59280', 'France'),
(35, 'Pedro Afonso', 'Male', 'Av. dos Lusiadas, 23', 'Sao Paulo', '05432-043', 'Brazil'),
(100, 'Kathryn Segal', 'Female', 'Augsburger Strabe 40', 'Ludenscheid Gevelndorf', '58513', 'Germany'),
(101, 'Tonia Sayre', 'Female', '84 Haslemere Road', 'ECHT', 'AB32 2DY', 'United Kingdom'),
(102, 'Loretta Harris', 'Female', 'Avenida Boavista 71', 'SANTO AMARO', '4920-111', 'Portugal'),
(103, 'Sean Wong', 'Male', 'Rua Vito Bovino, 240', 'Sao Paulo-SP', '04677-002', 'Brazil'),
(104, 'Frederick Sears', 'Male', 'ul. Marysiuska 64', 'Warszawa', '04-617', 'Poland'),
(105, 'Tammy Cantrell', 'Female', 'Lukiokatu 34', 'HAMEENLINNA', '13250', 'Finland'),
(106, 'Megan Kennedy', 'Female', '1210 Post Farm Road', 'Norcross', '30071', 'United States'),
(107, 'Maria Whittaker', 'Female', 'Spresstrasse 62', 'Bielefeld Milse', '33729', 'Germany'),
(108, 'Dorothy Parker', 'Female', '32 Lairg Road', 'NEWCHURCH', 'HR5 5DR', 'United Kingdom'),
(109, 'Roger Rudolph', 'Male', 'Avenida Julio Saul Dias 78', 'PENAFIEL', '4560-470', 'Portugal'),
(110, 'Karen Metivier', 'Female', 'Rua Guimaraes Passos, 556', 'Sao Luis-MA', '65025-450', 'Brazil'),
(111, 'Charles Hoover', 'Male', 'Al. Tysiaclecia 98', 'Warszawa', '03-851', 'Poland'),
(112, 'Becky Moss', 'Female', 'Laivurinkatu 6', 'MIKKELI', '50120', 'Finland'),
(113, 'Frank Kidd', 'Male', '2491 Carson Street', 'Cincinnati', 'KY 45202', 'United States'),
(114, 'Donna Wilson', 'Female', 'Hallesches Ufer 69', 'Dettingen', '73265', 'Germany'),
(115, 'Lillian Roberson', 'Female', '36 Iolaire Road', 'NEW BARN', 'DA3 3FT', 'United Kingdom');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `tbl_customer`
--
ALTER TABLE `tbl_customer`
ADD PRIMARY KEY (`CustomerID`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `tbl_customer`
--
ALTER TABLE `tbl_customer`
MODIFY `CustomerID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=116;
So, this is complete source code of How to export jQuery Datatables data to Excel, CSV, PDF and Copy in Clipboard using PHP Ajax. If you have any query regarding this tutorial you can comment in comment box.
Thank you so much. The best of the all
ReplyDeleteHi, I'm following your posts. Thank you. Will I have a request?
ReplyDeleteIn the database, 1-to-100 firms have 4-column data and 10-line expenses (Firm Name, Expense Name, Amount, Amount).
I want to;
1.In the first line, the first line will list the expenses in the next 10 lines.
Companies will be listed side by side. Excel will turn into pdf. (For example, A4 page also 8 companies.)
How can we do that? Can you make a video about that?
(PHP + MYSQL (PDO) + JQUERY method)
it has error on line 62 in fetch.php: PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 2097160 bytes).
ReplyDeleteAzam the problem is gonna be from fetch.php like you are calling column which is not in your table or the page of view have more or less column than fetch.php.....
ReplyDeleteThank very much
ReplyDeletecheck the model please
ReplyDeletehi im looking for server side datatables with export in laravel. i got above video but i dont know haw to make in laravel. so please help me to get that in laravel. past two days im looking for that. i got client side but not server side. and i repeat SERVERSIDE DATATABLES WITH EXPORT IN LARAVEL
ReplyDeletethanks
Good
ReplyDeleteMerci le meilleur des meilleurs
ReplyDeleteHow do you print all of the data? You show only printing displayed values.
ReplyDeletehi does not export 2000 above datas
ReplyDeletePlease help me
ReplyDeleteNotice: Undefined index: length in C:\xampp\htdocs\SGKA\admin\fetch.php on line 33
Notice: Undefined index: start in C:\xampp\htdocs\SGKA\admin\fetch.php on line 35
Notice: Undefined index: length in C:\xampp\htdocs\SGKA\admin\fetch.php on line 35
Notice: Undefined variable: connect in C:\xampp\htdocs\SGKA\admin\fetch.php on line 38
Fatal error: Uncaught Error: Call to a member function prepare() on null in C:\xampp\htdocs\SGKA\admin\fetch.php:38 Stack trace: #0 {main} thrown in C:\xampp\htdocs\SGKA\admin\fetch.php on line 38
Demo version, clipbord and PDF dont work.
ReplyDeleteThanks it solved my problem :)
ReplyDeletegood awesome information, thanks
ReplyDeleteyaser
Discreet Soft
good awesome information, thanks
yaser
Discreet Soft
PDF not working....
ReplyDeleteI cant figure out how to dumb this down by removing the search function and the choice of how many rows to show. I have it showing all the results, but I cant figure out how to remove those two things. Any help would be appreciated!
ReplyDeletePDF not working
ReplyDeleteIs there any multi search with this??
ReplyDeletethanks
ReplyDeletefrom where to download jquery database library ?
ReplyDeletepdf is not working
ReplyDeleteThank you !!!
ReplyDeleteAnd who is using to database sql server please ? 🙏🏻🙏🏻🙏🏻
PDF export is not working
ReplyDeletethank you
ReplyDeleteyou are genius
Thanks a lot for the coding, but PDF not working
ReplyDelete