In this tutorial you can find the solution of How to Export or Download the HTML Table Data in Excel Sheet by using JavaScript. Exporting Data to Excel is required feature in our web application. Because by export data functionality will helps to download data from web application to different file format for offline use of data and for this excel format is an ideal for exporting data in file for offline use. There many tutorial we have published for export data to excel at server side scripting using PHP. But if we can perform at client-side for export data into Excel sheet, so it will reduce load on server. So for this for export data to excel , here we will use JavaScript for perform client-side export data to excel sheet.
The client-side export feature will makes our web application more user-friendly. So with the help of JavaScript, we can export HTML table data to Excel format without refresh of web page. Under this tutorial, you can learn How to export HTML table data to excel using JavaScript. In this tutorial, we will use SheetJS JavaScript Library for export HTML table data to Excel.
Steps to Export HTML Table Data to Excel using JavaScript
HTML Table Data:
JavaScript Code:
1. HTML Table Data
For Export HTML data to Excel, here first we have to load some data in HTML table. So here we have make fetch employee table data and load in HTML table with table column like name, address, gender, designation and age. Here we have create HTML table with id employee_data. So this id we will use for fetch this HTML table data in JavaScript code. Under this HTML code we have make one button tag with id export_button, so when use has click on this button, then HTML table data will be download in Excel file format without refresh of web page using JavaScript.
<?php
$connect = new PDO("mysql:host=localhost;dbname=testing", "root", "");
$query = "SELECT * FROM tbl_employee ORDER BY name ASC";
$result = $connect->query($query);
?>
<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8" />
<title>Export HTML table data to excel using JavaScript</title>
<meta name="viewport" content="width=device-width, initial-scale=1" />
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">
<script type="text/javascript" src="https://unpkg.com/xlsx@0.15.1/dist/xlsx.full.min.js"></script>
</head>
<body>
<div class="container">
<h2 class="text-center mt-4 mb-4">Export HTML table data to excel using JavaScript</h2>
<div class="card">
<div class="card-header">
<div class="row">
<div class="col col-md-6">Sample Data</div>
<div class="col col-md-6 text-right">
<button type="button" id="export_button" class="btn btn-success btn-sm">Export</button>
</div>
</div>
</div>
<div class="card-body">
<table id="employee_data" class="table table-striped table-bordered">
<tr>
<th>Name</th>
<th>Address</th>
<th>Gender</th>
<th>Designation</th>
<th>Age</th>
</tr>
<?php
foreach($result as $row)
{
echo '
<tr>
<td>'.$row["name"].'</td>
<td>'.$row["address"].'</td>
<td>'.$row["gender"].'</td>
<td>'.$row["designation"].'</td>
<td>'.$row["age"].'</td>
</tr>
';
}
?>
</table>
</div>
</div>
</div>
</body>
</html>
2. JavaScript Code
In this tutorial, we have use SheetJS JavaScript Library for export HTML table data to Excel using JavaScript. So first we have to include following SheetJS library link at header of this HTML web page.
In JavaScript code part, first we have make html_table_to_excel(type) function. This function has use SheetJS Library function and convert or Write HTML table data to Excel format and download in browser without refresh of web page.
Once function is ready then we have to called html_table_to_excel(type) function on button click event, so for trigger button click event, we have use addEventListener method. So when user has click on button then html_table_to_excel(type) function has been called with xlsx file type. So it will download HTML table data in .xlsx format Excel file in browser without refresh of web page at client-side.
function html_table_to_excel(type)
{
var data = document.getElementById('employee_data');
var file = XLSX.utils.table_to_book(data, {sheet: "sheet1"});
XLSX.write(file, { bookType: type, bookSST: true, type: 'base64' });
XLSX.writeFile(file, 'file.' + type);
}
const export_button = document.getElementById('export_button');
export_button.addEventListener('click', () => {
html_table_to_excel('xlsx');
});
Conclusion
This tutorial will helps you to add export feature of download HTML table data in Excel sheet without using third-party jQuery plugin or any server-side script. By follow this tutorial you can easily export HTML table data to Excel using minimal JavaScript code.
If you want to get complete source with .sql file, so please write your email address in comment box. We will send you complete source code file at your define email address.
This is one more post on Yajra Laravel Datatables package and in this post, we will learn How to add export buttons to Yajra Laravel Datatables package. If you are using Laravel framework as your web application development task. So, in Laravel framework if you want to use jQuery Datatable plugin for display data on web page in tabular format with searching of data, pagination and data sorting feature. For all those things you have to use Yajra Datatables package in Laravel framework. By using this package you can perform server-side processing of searching, sorting and pagination of data without writing any line of code.
So, If you want to export Yajra DataTable data in excel or csv file format, then you have to add csv and excel export button in your DataTable. In Laravel framework, Yajra Datatables package has provide DataTable Buttons plugin for add export button in your Laravel Datatable. After adding of export button, you can easily export your Laravel DataTable data to CSV file or Excel sheet file format without writing any line of code. So, If want to learn How to add export button in Yajra Laravel Datatable, then you have to follow below step. After following below steps you can easily integrate csv excel export button with your Yajra Laravel Datatable and you can simply export your datatable data in csv or excel file format.
Download Latest Version of Laravel Framework
Make MySql Database Connection
Generate Fake Data
Install Yajra DataTables Package
Create DataTable Class
Create Controller Class
Create View Blade File
Set Route
Run Laravel Application
1 - Download Latest Version of Laravel Framework
First we have to download latest version of Laravel framework, So If you have use window OS then you have to go command prompt in which Composer application must be installed and then after write following command.
This command will make export folder in define directory, download and install latest version of Laravel framework.
2 - Make MySql Database Connection
After downloading of Laravel framework, first want to make database connection. For this we have to open Laravel framework .env file and in that file we have to define Mysql database configuration.
After making of Mysql Database connection, now we want to make table in mysql databaes connection and then after we want to fill that table with fake data. For this we have to follow following steps.
Create Model Class
Migrate Table Defination to Database
Create Factory Class
Create Seed Class
Create Model Class
Here we will use Model class for perform database related operation. For this, we have to create model class file. For this we have to go command prompt and write following command.
php artisan make:model Export -m
This command will make Export.php model class file in app folder. In this file we have to define table column name which you can find below.
app/Export.php
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Export extends Model
{
protected $fillable = ['firstName', 'lastName'];
}
Migrate Table Defination to Database
When we have create model class, then at that time that model class migration table file has been created in database/migrations folder. In that file, we have to define table column defination which you can seen below.
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreateExportsTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('exports', function (Blueprint $table) {
$table->id();
$table->string('firstName');
$table->string('lastName');
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('exports');
}
}
After define table defination, now we want to migrate this table defination to mysql database and create exports table. For this we have go to command prompt and write following command.
php artisan migrate
Above command will make exports table in Mysql database. So, this way we can create table in mysql database from Laravel our laravel application.
Create Factory Class
For generate fake data and fill mysql exports table with fill data, we have to create one factory class. This class will allowed you to build fake data for your modelds. For create model factories, we have to go command prompt and write following command.
php artisan make:factory ExportFactory
This command will create model factories with name ExportFactory.php in database/factories folder. In this file, first we have import our model class and then after we have to define in which mysql table column we have to define fake data column name which can find below.
database/factories/ExportFactory.php
<?php
/** @var \Illuminate\Database\Eloquent\Factory $factory */
use App\Model;
use Faker\Generator as Faker;
use App\Export;
$factory->define(App\Export::class, function (Faker $faker) {
return [
'firstName' => $faker->firstName,
'lastName' => $faker->lastName
];
});
Create Seed Class
In Laravel Seed classes is used for seeding your database with test data. In Laravel framework all seed classes has been store in database/seeds folder. For create seed class, we have to command prompt and write following command.
php artisan make:seeder ExportTableSeeder
This command will create ExportTableSeeder.php seed class in database/seeds directory. In this class first we want to import our model class then after in this class you can find run() and under this method we can define how many number of fake data has been genrated by using model factory class. You can find source below it.
database/seeds/ExportTableSeeder.php
<?php
use Illuminate\Database\Seeder;
use App\Export;
class ExportTableSeeder extends Seeder
{
/**
* Run the database seeds.
*
* @return void
*/
public function run()
{
$count = 500;
factory(Export::class, $count)->create();
}
}
After this we have to open database/seeds/DatabaseSeeder.php file which is already created under this database/seeds directory. So we have to open this file and under this file we can find run() method and under that method we have to called ExportTableSeeder class.
database/seeds/DatabaseSeeder.php
<?php
use Illuminate\Database\Seeder;
class DatabaseSeeder extends Seeder
{
/**
* Seed the application's database.
*
* @return void
*/
public function run()
{
// $this->call(UserSeeder::class);
$this->call(ExportTableSeeder::class);
}
}
After this for generate fake data, we have to go command prompt and write following command.
composer dump-autoload
This command will regenerates the list of all classed which we have included in this Laravel project. Running of this command is required for generate fake data. And lastly for generate fake data we have to run following command.
php artisan db:seed
This command will seed your test data and it will fill exports mysql table with fake data. So, this is complete step by step process for creating fake data in Laravel framework.
Here we have use Yajra Datatables package has been used for load data in jQuery Datatable plugin in Laravel framework. For use Yajra Datatable package, we have to install in Laravel framework. For this we have go to command prompt and write following command.
composer require yajra/laravel-datatables-oracle
This command will download and install in latest version of Yajra DataTable package in Laravel framework. Here we want to add export button in Yajra Laravel Datatable, for this we have to download and intall Datatable Button plugin. For this we have to go command prompt and write followng command.
composer require yajra/laravel-datatables-buttons
This command will download and install DataTables Button plugin of Yajra Datatable package. By using this plugin we can add export button Yajra DataTables for export data for Datatable. Now we want to yajra datatable package service in Laravel framework. For this we have to open config/app.php file and under this file in provider array we have to define this two package service, which you can see below.
This command will publish datatables buttons plugin service in Laravel. Now we can use datatables buttons in Laravel framework for add export button Datatable.
5 - Create DataTable Class
In this tutorial, we will use DataTable class for load data in jQuery Datatable plugin and add export button in DataTable for export data in CSV or Excel file. For this we have to create DataTable class, for this we have go to command prompt and write following command.
php artisan datatables:make ExportDataTable
This command will make DataTables/ExportDataTable.php class file in app directory. So, we have to open this file, and in this class file we have to import model class. In this class file, we can see following method.
dataTable($query) - This method will build DataTable class. query(ExportDataTable $model) - This method is used for get the query source of DataTable. In this method we have define data fetch query. html() - This method will build your html code and Ajax jQuery script for initialize jQuery Datatable plugin. In this method we can define which export button you want to add in Datatable. getColumns() - In this method we have to define Mysql table column name. filename() - This method is used for get the file name for export.
app/DataTables/ExportDataTable.php
<?php
namespace App\DataTables;
use App\Export;
use App\DataTables\ExportDataTable;
use Yajra\DataTables\Html\Button;
use Yajra\DataTables\Html\Column;
use Yajra\DataTables\Html\Editor\Editor;
use Yajra\DataTables\Html\Editor\Fields;
use Yajra\DataTables\Services\DataTable;
class ExportDataTable extends DataTable
{
/**
* Build DataTable class.
*
* @param mixed $query Results from query() method.
* @return \Yajra\DataTables\DataTableAbstract
*/
public function dataTable($query)
{
return datatables()
->eloquent($query);
}
/**
* Get query source of dataTable.
*
* @param \App\ExportDataTable $model
* @return \Illuminate\Database\Eloquent\Builder
*/
public function query(ExportDataTable $model)
{
//return $model->newQuery();
$data = Export::select();
return $this->applyScopes($data);
}
/**
* Optional method if you want to use html builder.
*
* @return \Yajra\DataTables\Html\Builder
*/
public function html()
{
return $this->builder()
->columns($this->getColumns())
->minifiedAjax()
->dom('Bfrtip')
->orderBy(1)
->buttons(
Button::make('csv'),
Button::make('excel')
);
}
/**
* Get columns.
*
* @return array
*/
protected function getColumns()
{
return [
Column::make('id'),
Column::make('firstName'),
Column::make('lastName'),
Column::make('created_at'),
Column::make('updated_at'),
];
}
/**
* Get filename for export.
*
* @return string
*/
protected function filename()
{
return 'Export_' . date('YmdHis');
}
}
6 - Create Controller Class
In Laravel framework for handle http request, we have to ceate Controller class. In Laravel framework controller class file has been store in app/Http/Controllers follder. For create controller class file, we have to go command prompt and write following command.
php artisan make:controller ExportController
This command will create ExportController.php controller class file. In this file, first we want to import ExportDataTable.php class and then after we have to make index method which will render ExportDataTable class data and load in export.blade.php view file.
app/Http/Controllers/ExportController.php
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\DataTables\ExportDataTable;
class ExportController extends Controller
{
public function index(ExportDataTable $dataTable)
{
return $dataTable->render('export');
}
}
7 - Create View Blade File
For display output in Laravel framework, it has been used blade templating engine for display HTML output in browser. In Laravel framework view files has been store in resources/views folder. In this directory we have already create export.blade.php file. This file will display Mysql table data in DataTable by using Yajra Datatable package. And with data you can also find export csv or excel button also.
In next we want to set the route of controller class method. For set route we have to open routes/web.php file and in this file we have to define route which you can find below.
routes/web.php
Route::resource('export', 'ExportController');
9 - Run Laravel Application
Now all set and we are ready to check output in browser. But before check output in browser, we have to first start Laravel application server. For this we have go to command prompt and write following command.
php artisan serve
This command will start Laravel server and provide base url of Laravel application. For test above code we have to write following url.
http://127.0.0.1:8000/export
So this is step by step processing of How to add export button in Laravel Yajra DataTable package. In this tutorial, we have first learn How to generate fake data in Laravel and then after we have seen how to load data in Datatable in Laravel framework by using Yajra Datatable package and then after we have seen how can we export Yajra DataTable data in CSV or Excel file format by using Yajra DataTable buttons plugin. So If you have found this tutorial helpful to you then do not forget to share it.
This is one more post on Exporting of Mysql data. In this post we will learn How to export Mysql data to CSV file by using PHP script. But here we add on feature like date range, that means here we will discuss how to export only those mysql data which has come between two define date to CSV file format by using PHP script. So, here you can learn How to export mysql data for specific date into Excel sheet or CSV file format via PHP.
If you have already learn How to Export Data to CSV file or Excel Sheet using PHP script. But suppose we do not want to export whole Mysql data into CSV file or Excel spread sheet but we want to Export those Data to CSV file with PHP which has come between select date range. So, at that time this turial will help you to learn How to export mysql data to CSV file or Excel sheet by using date range filter with PHP script. This is feature will add usability of your web application and use can freely to export those data which he want to export and he do not export whole unwanted data. This feature will reduce your website bandwidth and reduce load on your mysql database because only required or filtered data only has been exported into CSV file or Excel sheet.
In this post we want to Export date range filter data in CSV file using PHP then in PHP there are many PHP build in file system function is available for export data to CSV file. Here we have use some PHP function like fopen(), fputcsv(), fclose() for Export data to CSV file. Here fopen() function will open file in PHP stream. After this fputcsv() function will write data in open file and fclose() function will close open file. So, this basic PHP function has been used for export data to CSV file in PHP. But here we have not only export mysql data to csv file but also here we have export filtered data exported into csv file. So for filter data here we have use date range that means define two date and only export only those data which has come between this two define data. This we can done in mysql query which you can find below. For date range selection here we have use bootstrap date picker plugin. So, this simple tutorial on How to use Date Range Filter with Export Data to CSV File in PHP. Below you can find complete source code also.
DROP TABLE IF EXISTS `tbl_order`;
CREATE TABLE `tbl_order` (
`order_id` int(11) NOT NULL AUTO_INCREMENT,
`order_customer_name` varchar(255) NOT NULL,
`order_item` varchar(255) NOT NULL,
`order_value` double(12,2) NOT NULL,
`order_date` date NOT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
/*Data for the table `tbl_order` */
insert into `tbl_order`(`order_id`,`order_customer_name`,`order_item`,`order_value`,`order_date`) values
(1,'David E. Gary','Shuttering Plywood',1500.00,'2019-06-14'),
(2,'Eddie M. Douglas','Aluminium Heavy Windows',2000.00,'2019-06-08'),
(3,'Oscar D. Scoggins','Plaster Of Paris',150.00,'2019-05-29'),
(4,'Clara C. Kulik','Spin Driller Machine',350.00,'2019-05-30'),
(5,'Christopher M. Victory','Shopping Trolley',100.00,'2019-06-01'),
(6,'Jessica G. Fischer','CCTV Camera',800.00,'2019-06-02'),
(7,'Roger R. White','Truck Tires',2000.00,'2019-05-28'),
(8,'Susan C. Richardson','Glass Block',200.00,'2019-06-04'),
(9,'David C. Jury','Casing Pipes',500.00,'2019-05-27'),
(10,'Lori C. Skinner','Glass PVC Rubber',1800.00,'2019-05-30'),
(11,'Shawn S. Derosa','Sony HTXT1 2.1-Channel TV',180.00,'2019-06-03'),
(12,'Karen A. McGee','Over-the-Ear Stereo Headphones ',25.00,'2019-06-01'),
(13,'Kristine B. McGraw','Tristar 10\" Round Copper Chef Pan with Glass Lid',20.00,'2019-05-30'),
(14,'Gary M. Porter','ROBO 3D R1 Plus 3D Printer',600.00,'2019-06-02'),
(15,'Sarah D. Hunter','Westinghouse Select Kitchen Appliances',35.00,'2019-05-29'),
(16,'Diane J. Thomas','SanDisk Ultra 32GB microSDHC',12.00,'2019-06-05'),
(17,'Helena J. Quillen','TaoTronics Dimmable Outdoor String Lights',16.00,'2019-06-04'),
(18,'Arlette G. Nathan','TaoTronics Bluetooth in-Ear Headphones',25.00,'2019-06-03'),
(19,'Ronald S. Vallejo','Scotchgard Fabric Protector, 10-Ounce, 2-Pack',20.00,'2019-06-03'),
(20,'Felicia L. Sorensen','Anker 24W Dual USB Wall Charger with Foldable Plug',12.00,'2019-06-04');
If you looking for web tutorial on exporting mysql data to CSV file in Codeignier. So, you have come on right place, in this post we have covered topic like export mysql data to CSV file in Codeigniter framework. Every body knows Codeigniter framework is used for boost you coding for make faster web based application. Once application has been make perfectly and end use your application completely, then you web application has large amount of data. Then at that time you have store that in readable file format. At that time you have to export your web application data in CSV (Comma-Seperated Values) file format. Because data inthis file format is widly used for importing and exporting data in web based application. And CSV file format is lightweight for store data in spread format.
Now question is arise How could you export mysql data in CSV file and download data in CSV file from live application in Codeigniter. First of all, we have already publish tutorial on how to import data from CSV file in Codeigniter framework. Now, In this post, we will discuss how to export data from Mysql database into CSV file using Codeigniter. Here also you can learn how to create CSV file in Codeigniter and download into local computer or Save Mysql data into CSV file in Codeigniter.
For describe Mysql data export to CSV file feature, we have make script in Codeigniter MVC framework, in which export student data from Mysql database table and insert into CSV file using Codeigniter.
Once student table has been ready, then you have to make database connection in Codeigniter, for database connection in Codeigniter, you have to go to application/config/database.php. In this file you have make database connection in Codeigniter.
After making database connection, you have to create Export_csv.php file in application/controllers folder. Controllers files mainly used for handle HTTP request of application. In this file we have to make follow method.
index() - This method is a root method of this class, this method will received student data from model and send data to view file.
export() - This method is received form submission request for export mysql data to CSV file. This function will write mysql data under CSV file and after file send for download.
application/controllers/Export_csv.php
<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Export_csv extends CI_Controller {
public function __construct()
{
parent::__construct();
$this->load->model('export_csv_model');
}
function index()
{
$data['student_data'] = $this->export_csv_model->fetch_data();
$this->load->view('export_csv', $data);
}
function export()
{
$file_name = 'student_details_on_'.date('Ymd').'.csv';
header("Content-Description: File Transfer");
header("Content-Disposition: attachment; filename=$file_name");
header("Content-Type: application/csv;");
// get data
$student_data = $this->export_csv_model->fetch_data();
// file creation
$file = fopen('php://output', 'w');
$header = array("Student Name","Student Phone");
fputcsv($file, $header);
foreach ($student_data->result_array() as $key => $value)
{
fputcsv($file, $value);
}
fclose($file);
exit;
}
}
Export_csv_model.php (Models)
Models files in Codeigniter mainly used for database operation. You have to create models file in application/models folder. In this file there is only one method we have make. Here fetch_data() method is used for fetch data from student table.
<?php
class Export_csv_model extends CI_Model
{
function fetch_data()
{
$this->db->select("student_name, student_phone");
$this->db->from('tbl_student');
return $this->db->get();
}
}
?>
export_csv.php (Views)
This view file is used for display output in HTML format on web page. This file you have to make in application/views folder. This file first received student data from controllers, and when used click on export button, then it has received data export to CSV request for export data to CSV file.
<html>
<head>
<title>How to Export Mysql Data to CSV File in Codeigniter</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.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">How to Export Mysql Data to CSV File in Codeigniter</h3>
<br />
<form method="post" action="<?php echo base_url(); ?>export_csv/export">
<div class="panel panel-default">
<div class="panel-heading">
<div class="row">
<div class="col-md-6">
<h3 class="panel-title">Student Data</h3>
</div>
<div class="col-md-6" align="right">
<input type="submit" name="export" class="btn btn-success btn-xs" value="Export to CSV" />
</div>
</div>
</div>
<div class="panel-body">
<div class="table-responsive">
<table class="table table-bordered table-striped">
<tr>
<th>Student Name</th>
<th>Student Phone</th>
</tr>
<?php
foreach($student_data->result_array() as $row)
{
echo '
<tr>
<td>'.$row["student_name"].'</td>
<td>'.$row["student_phone"].'</td>
</tr>
';
}
?>
</table>
</div>
</div>
</div>
</form>
</div>
</body>
</html>
This tutorial will you help to learn exporting of Mysql Data to CSV file format in Codeigniter.
