August 7, 2019

jQuery Datatables Export to Excel, CSV, PDF, Copy and Print using PHP

Export to Excel, PDF, CSV From jQuery Datatables using PHP Ajax

jQuery plugin that helps to convert your HTML table into a grid layout. Using jQuery plugin you can create Datatable into full functional data grid with many features like live search, pagination, sorting data and exporting table in various format. In this article, we show you how to export jQuery Datatable data into Excel, CSV, PDF, Copy and Print using PHP & MySQL

Now using the Ajax & PHP, you going to display the data from MySQL to Datagrid. If you like to export your data in various format like Excel, CSV, PDF, Copy and Print. For this, you need to work for separately, but with the help of jQuery Datatables plugin, we can export tables data into different file format whatever which you like. If you like to use this plugin, first you want to add some jQuery Datatables library to enable this feature.

By using this jQuery library, you going to enable export button features. Then you need to define HTML table for initialization jQuery Datatable plugin on the web page based on using the section ID “#user_info”. After you need to use the Datatable() method for initialized this plugin. Once you finished this process, you going to define Ajax which helps to send a request to fetch.php page and from there the data will fetch from MySQL table and that will display on the webpage. For the export button, you need to add “dom: lBfrtip” option which only leads to export in a various format which you like. The following code will help to integrate into your project.

Database

--
-- Table structure for table `export_excel_more`
--

CREATE TABLE `export_excel_more` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `gender` varchar(10) NOT NULL,
  `age` varchar(15) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(15) NOT NULL,
  `organization` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `export_excel_more`
--

INSERT INTO `export_excel_more` (`id`, `name`, `gender`, `age`, `email`, `phone`, `organization`) VALUES
(1, 'Carla', 'Female', '34', 'gravida.nunc.sed@dictumsapien.co.uk', '1-373-535-7651', 'Turpis In Consulting'),
(2, 'Jolene', 'Female', '41', 'vitae.risus@est.net', '653-1022', 'Fringilla Cursus Purus LLC'),
(3, 'Ursa', 'Female', '46', 'felis.orci.adipiscing@torquentperconubia.ca', '993-1262', 'Nunc Institute'),
(4, 'Mercedes', 'Female', '33', 'cursus.in@ultrices.org', '1-689-239-1253', 'Sed Et LLC'),
(5, 'Jennifer', 'Female', '43', 'augue.Sed@inlobortis.com', '206-2027', 'Ut Nulla Incorporated'),
(6, 'Ella', 'Female', '26', 'mauris.id@euduiCum.com', '614-9630', 'Nullam Institute'),
(7, 'Lester', 'Male', '37', 'varius.et@imperdiet.co.uk', '1-319-749-9980', 'Integer Vulputate Industries'),
(8, 'Orson', 'Male', '45', 'faucibus@dictumcursus.net', '1-312-142-9909', 'Tempus Corp.'),
(9, 'Amery', 'Male', '37', 'egestas.Sed.pharetra@Vivamusnon.org', '657-0661', 'Et Euismod Et Consulting'),
(10, 'Malcolm', 'Male', '36', 'magna.Nam.ligula@duiFuscediam.edu', '851-1070', 'Id Ante Institute'),
(11, 'Abbot', 'Male', '26', 'Sed.et.libero@interdumCurabiturdictum.edu', '1-427-809-7539', 'Odio Consulting'),
(12, 'Marshall', 'Male', '46', 'non.quam.Pellentesque@Nuncuterat.com', '887-3954', 'Ante Ipsum Industries'),
(13, 'Clinton', 'Male', '20', 'vitae.dolor@odiotristiquepharetra.co.uk', '648-0121', 'Ut Erat Sed Company'),
(14, 'Paki', 'Male', '34', 'elit@non.ca', '1-767-920-1169', 'A Ultricies LLC'),
(15, 'Medge', 'Female', '29', 'in@id.com', '995-6705', 'Libero PC'),
(16, 'Jason', 'Male', '24', 'Cum@Suspendisseseddolor.net', '230-4849', 'Nulla Vulputate Institute'),
(17, 'Igor', 'Male', '47', 'elit.Curabitur.sed@nibh.co.uk', '304-5807', 'Bibendum Consulting'),
(18, 'Arthur', 'Male', '29', 'commodo@placeratvelit.co.uk', '1-984-332-7829', 'Urna Industries'),
(19, 'Len', 'Male', '20', 'conubia@Morbi.net', '758-0177', 'Mauris Ltd'),
(20, 'Nadine', 'Female', '54', 'aliquet.vel.vulputate@convallisligula.com', '1-771-923-9224', 'Eget Volutpat Ornare Inc.'),
(21, 'Breanna', 'Female', '26', 'ac.libero.nec@sed.ca', '1-620-872-5823', 'A Felis Ullamcorper Foundation'),
(22, 'Gloria', 'Female', '38', 'eget@sitametfaucibus.org', '1-860-680-0582', 'Odio A Purus Company'),
(23, 'Florence', 'Female', '25', 'sollicitudin.a@felisadipiscing.org', '791-1534', 'Libero Donec Consectetuer Inc.'),
(24, 'Amity', 'Female', '27', 'quam.elementum.at@tellusSuspendisse.ca', '112-8515', 'Lobortis Limited'),
(25, 'Lara', 'Female', '21', 'Quisque.libero@amet.ca', '1-267-162-6135', 'Tempus Eu Industries'),
(26, 'Lynn', 'Female', '43', 'orci@faucibus.com', '1-174-565-6520', 'Arcu Consulting'),
(27, 'Russell', 'Male', '47', 'elementum.lorem.ut@ullamcorper.com', '1-503-967-2488', 'Proin Sed Incorporated'),
(28, 'Fritz', 'Male', '29', 'Donec.fringilla@id.org', '1-560-623-4566', 'Fusce Incorporated'),
(29, 'Otto', 'Male', '37', 'adipiscing.ligula@sedturpisnec.edu', '537-0181', 'Aliquet PC'),
(30, 'Quintessa', 'Female', '30', 'elementum.sem@luctusCurabitur.co.uk', '1-609-321-3473', 'Ipsum Suspendisse Inc.');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `export_excel_more`
--
ALTER TABLE `export_excel_more`
  ADD PRIMARY KEY (`id`);
COMMIT;

-- Table structure for table `export_excel_more`

CREATE TABLE `export_excel_more` (

`id` int(11) NOT NULL,

`name` varchar(255) NOT NULL,

`gender` varchar(10) NOT NULL,

`age` varchar(15) NOT NULL,

`email` varchar(255) NOT NULL,

`phone` varchar(15) NOT NULL,

`organization` varchar(255) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping data for table `export_excel_more`

INSERT INTO `export_excel_more` (`id`, `name`, `gender`, `age`, `email`, `phone`, `organization`) VALUES

(1, 'Carla', 'Female', '34', '[email protected]', '1-373-535-7651', 'Turpis In Consulting'),

(2, 'Jolene', 'Female', '41', '[email protected]', '653-1022', 'Fringilla Cursus Purus LLC'),

(3, 'Ursa', 'Female', '46', '[email protected]', '993-1262', 'Nunc Institute'),

(4, 'Mercedes', 'Female', '33', '[email protected]', '1-689-239-1253', 'Sed Et LLC'),

(5, 'Jennifer', 'Female', '43', '[email protected]', '206-2027', 'Ut Nulla Incorporated'),

(6, 'Ella', 'Female', '26', '[email protected]', '614-9630', 'Nullam Institute'),

(7, 'Lester', 'Male', '37', '[email protected]', '1-319-749-9980', 'Integer Vulputate Industries'),

(8, 'Orson', 'Male', '45', '[email protected]', '1-312-142-9909', 'Tempus Corp.'),

(9, 'Amery', 'Male', '37', '[email protected]', '657-0661', 'Et Euismod Et Consulting'),

(10, 'Malcolm', 'Male', '36', '[email protected]', '851-1070', 'Id Ante Institute'),

(11, 'Abbot', 'Male', '26', '[email protected]', '1-427-809-7539', 'Odio Consulting'),

(12, 'Marshall', 'Male', '46', '[email protected]', '887-3954', 'Ante Ipsum Industries'),

(13, 'Clinton', 'Male', '20', '[email protected]', '648-0121', 'Ut Erat Sed Company'),

(14, 'Paki', 'Male', '34', '[email protected]', '1-767-920-1169', 'A Ultricies LLC'),

(15, 'Medge', 'Female', '29', '[email protected]', '995-6705', 'Libero PC'),

(16, 'Jason', 'Male', '24', '[email protected]', '230-4849', 'Nulla Vulputate Institute'),

(17, 'Igor', 'Male', '47', '[email protected]', '304-5807', 'Bibendum Consulting'),

(18, 'Arthur', 'Male', '29', '[email protected]', '1-984-332-7829', 'Urna Industries'),

(19, 'Len', 'Male', '20', '[email protected]', '758-0177', 'Mauris Ltd'),

(20, 'Nadine', 'Female', '54', '[email protected]', '1-771-923-9224', 'Eget Volutpat Ornare Inc.'),

(21, 'Breanna', 'Female', '26', '[email protected]', '1-620-872-5823', 'A Felis Ullamcorper Foundation'),

(22, 'Gloria', 'Female', '38', '[email protected]', '1-860-680-0582', 'Odio A Purus Company'),

(23, 'Florence', 'Female', '25', '[email protected]', '791-1534', 'Libero Donec Consectetuer Inc.'),

(24, 'Amity', 'Female', '27', '[email protected]', '112-8515', 'Lobortis Limited'),

(25, 'Lara', 'Female', '21', '[email protected]', '1-267-162-6135', 'Tempus Eu Industries'),

(26, 'Lynn', 'Female', '43', '[email protected]', '1-174-565-6520', 'Arcu Consulting'),

(27, 'Russell', 'Male', '47', '[email protected]', '1-503-967-2488', 'Proin Sed Incorporated'),

(28, 'Fritz', 'Male', '29', '[email protected]', '1-560-623-4566', 'Fusce Incorporated'),

(29, 'Otto', 'Male', '37', '[email protected]', '537-0181', 'Aliquet PC'),

(30, 'Quintessa', 'Female', '30', '[email protected]', '1-609-321-3473', 'Ipsum Suspendisse Inc.');

-- Indexes for dumped tables

-- Indexes for table `export_excel_more`

ALTER TABLE `export_excel_more`

ADD PRIMARY KEY (`id`);

COMMIT;

conn.php

<?php
 $host = "localhost";  
 $db_user = "root";  
 $db_pass = "";  
 $dbname = "pdf_export";  

 $connect = new PDO("mysql:host=$host; dbname=$dbname", $db_user, $db_pass); 
?>

<?php

$host = "localhost";

$db_user = "root";

$db_pass = "";

$dbname = "pdf_export";

$connect = new PDO("mysql:host=$host; dbname=$dbname", $db_user, $db_pass);

index.php

<html>

<head>
    <title>jQuery Datatables Export to Excel, CSV, PDF, Copy and Print using PHP</title>
    <link rel="stylesheet" type="text/css" href="css/datatables.min.css" />
    <link rel="stylesheet" href="css/bootstrap.min.css" />
    <style>
        .dataTables_length {
            margin-bottom: 30px;
        }
        
        .dataTables_length select {
            border: 1px solid #e4e4e4;
        }
        
        .dt-buttons a {
            margin-left: 12px;
            font-size: 12px;
            padding: 6px;
            border: 1px solid #e4e4e4;
            background: #FFF;
            box-shadow: 0px 0px 14px 0px #ececec;
        }
        
        .dataTables_filter input {
            border: 1px solid #e4e4e4;
        }
        
        .table-striped tbody tr {
            line-height: 30px;
        }
    </style>
</head>

<body>
    <div class="container box">
        <br/>
        <br/>
        <h3 align="center">jQuery Datatables Export to Excel, CSV, PDF, Copy and Print using PHP</h3>
        <br />
        <div class="table-responsive">
            <table id="user_info" class="table table-bordered table-striped">
                <thead>
                    <tr>
                        <th>Name</th>
                        <th>Gender</th>
                        <th>Age</th>
                        <th>Email</th>
                        <th>Phone</th>
                        <th>Organization</th>
                    </tr>
                </thead>
            </table>
        </div>
    </div>
    <br />
    <br />
</body>

</html>
<script src="js/jquery.min.js"></script>
<script type="text/javascript" src="js/datatables.min.js"></script>
<script src="js/bootstrap.min.js"></script>
<script type="text/javascript" language="javascript">
    $(document).ready(function() {

        $('#user_info').DataTable({
            "processing": true,
            "serverSide": true,
            "ajax": {
                url: "fetch.php",
                type: "POST"
            },
            dom: 'lBfrtip',
            buttons: [
                'excel', 'csv', 'pdf', 'copy', 'print'
            ],
            "lengthMenu": [
                [10, 25, 50, -1],
                [10, 25, 50, "All"]
            ]
        });

    });
</script>

<html>

<head>

<title>jQuery Datatables Export to Excel, CSV, PDF, Copy and Print using PHP</title>

<style>

.dataTables_length {

margin-bottom: 30px;

}

.dataTables_length select {

border: 1px solid #e4e4e4;

}

.dt-buttons a {

margin-left: 12px;

font-size: 12px;

padding: 6px;

border: 1px solid #e4e4e4;

background: #FFF;

box-shadow: 0px 0px 14px 0px #ececec;

}

.dataTables_filter input {

border: 1px solid #e4e4e4;

}

.table-striped tbody tr {

line-height: 30px;

}

</style>

</head>

<body>

<br/>

<h3 align="center">jQuery Datatables Export to Excel, CSV, PDF, Copy and Print using PHP</h3>

<br />

<thead>

<tr>

<th>Gender</th>

<th>Email</th>

<th>Phone</th>

<th>Organization</th>

</tr>

</thead>

</table>

</div>

<br />

</body>

</html>

$(document).ready(function() {

$('#user_info').DataTable({

"processing": true,

"serverSide": true,

"ajax": {

url: "fetch.php",

type: "POST"

dom: 'lBfrtip',

buttons: [

'excel', 'csv', 'pdf', 'copy', 'print'

"lengthMenu": [

[10, 25, 50, -1],

[10, 25, 50, "All"]

]

});

</script>

fetch.php

<?php
include('conn.php');

$column = array(
    'name',
    'gender',
    'age',
    'email',
    'phone',
    'organization'
);

$query = "SELECT * FROM export_excel_more";

if (isset($_POST['search']['value'])) {
    $query .= '
 WHERE name LIKE "%' . $_POST['search']['value'] . '%" 
 OR gender LIKE "%' . $_POST['search']['value'] . '%" 
 OR age LIKE "%' . $_POST['search']['value'] . '%" 
 OR email LIKE "%' . $_POST['search']['value'] . '%" 
 OR phone LIKE "%' . $_POST['search']['value'] . '%" 
 OR organization 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['name'];
    $sub_array[] = $row['gender'];
    $sub_array[] = $row['age'];
    $sub_array[] = $row['email'];
    $sub_array[] = $row['phone'];
    $sub_array[] = $row['organization'];
    $data[]      = $sub_array;
}

function count_all_data($connect)
{
    $query     = "SELECT * FROM export_excel_more";
    $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);

?>

<?php

include('conn.php');

$column = array(

'name',

'gender',

'age',

'email',

'phone',

'organization'

);

$query = "SELECT * FROM export_excel_more";

if (isset($_POST['search']['value'])) {

$query .= '

WHERE name LIKE "%' . $_POST['search']['value'] . '%"

OR gender LIKE "%' . $_POST['search']['value'] . '%"

OR age LIKE "%' . $_POST['search']['value'] . '%"

OR email LIKE "%' . $_POST['search']['value'] . '%"

OR phone LIKE "%' . $_POST['search']['value'] . '%"

OR organization 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['name'];

$sub_array[] = $row['gender'];

$sub_array[] = $row['age'];

$sub_array[] = $row['email'];

$sub_array[] = $row['phone'];

$sub_array[] = $row['organization'];

$data[] = $sub_array;

}

function count_all_data($connect)

{

$query = "SELECT * FROM export_excel_more";

$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);

Preview & Download

DataTables

Mraj

Creative Designer & Developer specialist by the spirit and a loving blogger by thoughts. If you have any questions let me drop an email with the article name to the following email id: [email protected]

jQuery Datatables Export to Excel, CSV, PDF, Copy and Print using PHP

Share:

Database

conn.php

index.php

fetch.php

Preview & Download

Mraj

Don't miss on latest updates