Webslesson Tutorial | Export HTML table to Excel File using Jquery with PHP

How To Export HTML to Excel using PHP

Excel is among the most used file types to export your website data, so in this tutorial I will show you how to export html to excel using php. It’s not that hard I will give you the main class of this functionality, then you only need to call the function and provide the required data to construct the table of the excel and that’s it. All the hard work is done by using this class, in the below code you will find all necessary functions to create an excel file with your custom data. Besides this class you will need to construct your data in a special way so the excel to be generated but I will show you how your arrays should look like.

#', '-', $result); return preg_replace('#(^\-+|\-+$)#D', '', $result); > /** * Builds a new Excel Spreadsheet object * @return Excel The Spreadsheet */ function __construct($title) < $this->title = $title; $this->col = 0; $this->row = 0; $this->data = ''; $this->bofMarker(); > /** * Transmits the proper headers to cause a download to occur and to identify the file properly * @return nothing */ function headers() < header("Content-Type: application/force-download"); header("Content-Type: application/octet-stream"); header("Content-Type: application/download"); header("Content-Disposition: attachment;filename=" . Excel::filename($this->title) . ".xls "); header("Content-Transfer-Encoding: binary "); > function send() < $this->eofMarker(); $this->headers(); echo $this->data; > /** * Writes the Excel Beginning of File marker * @see pack() * @return nothing */ private function bofMarker() < $this->data .= pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0); > /** * Writes the Excel End of File marker * @see pack() * @return nothing */ private function eofMarker() < $this->data .= pack("ss", 0x0A, 0x00); > /** * Moves internal cursor left by the amount specified * @param optional integer $amount The amount to move left by, defaults to 1 * @return integer The current column after the move */ function left($amount = 1) < $this->col -= $amount; if($this->col < 0) < $this->col = 0; > return $this->col; > /** * Moves internal cursor right by the amount specified * @param optional integer $amount The amount to move right by, defaults to 1 * @return integer The current column after the move */ function right($amount = 1) < $this->col += $amount; return $this->col; > /** * Moves internal cursor up by amount * @param optional integer $amount The amount to move up by, defaults to 1 * @return integer The current row after the move */ function up($amount = 1) < $this->row -= $amount; if($this->row < 0) < $this->row = 0; > return $this->row; > /** * Moves internal cursor down by amount * @param optional integer $amount The amount to move down by, defaults to 1 * @return integer The current row after the move */ function down($amount = 1) < $this->row += $amount; return $this->row; > /** * Moves internal cursor to the top of the page, row = 0 * @return nothing */ function top() < $this->row = 0; > /** * Moves internal cursor all the way left, col = 0 * @return nothing */ function home($col) < if(empty($col))< $this->col = 0; > else< $this->col = $col; > > /** * Writes a number to the Excel Spreadsheet * @see pack() * @param integer $value The value to write out * @return nothing */ function number($value) < $this->data .= pack("sssss", 0x203, 14, $this->row, $this->col, 0x0); $this->data .= pack("d", $value); > /** * Writes a string (or label) to the Excel Spreadsheet * @see pack() * @param string $value The value to write out * @return nothing */ function label($value) < $length = strlen($value); $this->data .= pack("ssssss", 0x204, 8 + $length, $this->row, $this->col, 0x0, $length); $this->data .= $value; > >

This is the main class you will all the time to use in order to generate excel files. Now just copy paste the code in a file and upload to your server. Now we need to create a function which will send the data to this function to download the file later. In the below example you will see I am using $wpdb, I am using this because I am using wordpress to get the data I need to put in the excel, but this class can be used in any framework or cms.

public function export_to_excel($fid)< global $wpdb; $fid = intval($fid); $form_title = 'Excel'; $data = $this->getFieldsByFormId($fid); if(!empty($data)) < //Check that the class exists before trying to use it $arrHeader = array(); foreach($data[0] as $k =>$v) < $arrHeader[] = $k; >if(!class_exists('Excel'))< //Include excel class file require_once(dirname(__FILE__).'/inc/Excel.class.php'); //create excel class object $xls = new Excel($form_title); $i=0; foreach($arrHeader as $colName )< $xls->home($i); $xls->label($colName); $i++; > foreach ($data as $k => $v)< $i=0; $xls->down(); foreach ($v as $k2 => $v2)< $colVal = ((isset($v[$k2])) ? htmlspecialchars_decode($v[$k2]) : ''); $xls->home($i); $xls->label($colVal); $i++; > > $xls->send(); exit; > > >

As you can see in the above function first time I get all fields I need then, first I create the header of the table represented by the arrHeader array then I pass all the data after I check if the class Excel exists. Using the xls->send() it will create the excel then it will be auto downloaded by the browser. Is quite simple to implement this functionality and and it is very helpful. However if you need an even easier method to export your data you can follow this tutorial where I show you how to export html table in csv using jquery. I hope you this tutorial will help you to achive this functionality, let me know in the comments section.

Источник

Saved searches

Use saved searches to filter your results more quickly

You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session. You switched accounts on another tab or window. Reload to refresh your session.

A php library based on PHPExcel to convert html tables to excel files.

License

Ticketpark/HtmlPhpExcel

This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.

Name already in use

A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?

Sign In Required

Please sign in to use Codespaces.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching Xcode

If nothing happens, download Xcode and try again.

Launching Visual Studio Code

Your codespace will open once ready.

There was a problem preparing your codespace, please try again.

Latest commit

Git stats

Files

Failed to load latest commit information.

README.md

This is a php library based on PhpSpreadsheet which simplifies converting html tables to excel files. It allows styling right within the html template with specific attributes.

Add HtmlPhpExcel to your composer.json:

composer require ticketpark/htmlphpexcel 

 require_once('../vendor/autoload.php'); $html = '
Column A
Column B
Value A
Value B
'; $htmlPhpExcel = new \Ticketpark\HtmlPhpExcel\HtmlPhpExcel($html); // Create and output the excel file to the browser $htmlPhpExcel->process()->output(); // Alternatively create the excel and save to a file $htmlPhpExcel->process()->save('myFile.xlsx'); // or get the \PhpOffice\PhpSpreadsheet\Spreadsheet object to do further work with it $phpExcelObject = $htmlPhpExcel->process()->getExcelObject();

For a more complex example with styling options see example directory.

There is support for specific html attributes to allow styling of the excel output. The attributes expect the content to be json_encoded.

• _excel-styles
  Supports everything which is possible with PhpSpreadsheet’s applyFromArray() method (also see here).

table> tr> td _excel-styles='>>'>Footd> tr> table>

table> tr _excel-dimensions='>'> td _excel-dimensions='>'>Footd> tr> table>

table> tr> td _excel-explicit='PhpSpreadsheet_Cell_DataType::TYPE_STRING'>0022td> tr> table>

table> tr> td _excel-comment='Comment content'>Cell valuetd> tr> table>

About

A php library based on PHPExcel to convert html tables to excel files.

Источник

Самый простой способ генерации xls в PHP

Вообще казалось бы вынесенная в заголовок задача не должно быть сложной, да и гугл дает кучу ссылок, в том числе на хабр, однако для того чтобы сделать выгрузку БД с сайта в эксель мне пришлось несколько помучаться.

С модулем Spreadsheet_Excel_Writer я предпочел не связываться, по причине отсутствия необходимых ему модулей php на трех имеющихся у меня в наличии для тестирования серверах, хочется все-таки сделать код перетаскиваемый между серверами легко и просто.
php_write_excel оттолкнул полным отсутствием документации при необходимости сделать работу быстро (хотя в дальнейшем я хочу с этим модулем поразбираться).

В итоге я выбрал самый простейший способ из найденных в интернете — выведение стандартной html таблицы под видом xls-файла. На этом пути лично у меня возникла проблема с кодировкой, эксель упорно не хотел видеть кириллицу в честном Windows-1251. В итоге рабочей оказалась следующая конструкция.

header('Content-Type: text/html; charset=windows-1251'); header('P3P: CP="NOI ADM DEV PSAi COM NAV OUR OTRo STP IND DEM"'); header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); header('Cache-Control: no-store, no-cache, must-revalidate'); header('Cache-Control: post-check=0, pre-check=0', FALSE); header('Pragma: no-cache'); header('Content-transfer-encoding: binary'); header('Content-Disposition: attachment; filename=list.xls'); header('Content-Type: application/x-unknown'); echo  htmlentities(iconv("utf-8", "windows-1251", $val),ENT_QUOTES, "cp1251"));   HTML;

Соответственно iconv нужен в случае если данные в БД пишутся в utf-8, htmlentities переводит в доступный экселю формат. Попытка применить htmlentities к тексту в utf-8 привела к очень большому количеству китайских иероглифов в экселе.

Этот способ позволяет с помощью стандартных же html тэгов задавать жирный и курсивный текст, но пока не удалось понять, можно ли сделать заливку ячеек цветом. Впрочем для генерации простенького .xls файла способ все равно вполне пригоден. Данные потом нормально просматриваются, редактируются и сохраняются в экселе.

Надеюсь этот пост кому-нибудь поможет в решении аналогичной задачи.

Источник

Webslesson

PHP, MySql, Jquery, AngularJS, Ajax, Codeigniter, Laravel Tutorial

Friday, 19 August 2016

Export HTML table to Excel File using Jquery with PHP

If you are willing to learn how can we generate excel file by using jquery, so this is the right place in which I will learn you how to use jquery with php programming for export of html table data to Excel file. This is my short PHP web development tutorial in which We will talk about how we can use jquery code for export html table data to excel file format by using PHP. In number of web pages in your web application, you want to give one option for generate excel file from your html data table. With the help of this clean php code execute with the help jquery. You can efficiently build the functionality like export any type of html table data to excel file format by using php programming with Jquery. In this PHP web development tutorial first I will fetch data from mysql table and display that table data on the web page in html table and below html table I have put one button for export html table data to excel file. When user click on that button html data will be exported to excel file. I will jquery code on button click event. This code is working on most of the modern browser like a Old and New Internet Explorer, Google Chrome Fire Fox etc. I wish you have something learn from this post.

Source Code

Database

 -- -- Table structure for table `tbl_employee` -- CREATE TABLE IF NOT EXISTS `tbl_employee` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `gender` varchar(10) NOT NULL, `designation` varchar(100) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=22 ; -- -- Dumping data for table `tbl_employee` -- INSERT INTO `tbl_employee` (`id`, `name`, `gender`, `designation`) VALUES (1, 'Bruce Tom', 'Male', 'Driver'), (5, 'Clara Gilliam', 'Female', 'Programmer'), (6, 'Barbra K. Hurley', 'Female', 'Service technician'), (7, 'Antonio J. Forbes', 'Male', 'Faller'), (8, 'Charles D. Horst', 'Male', 'Financial investigator'), (9, 'Beau L. Clayton', 'Male', 'Extractive metallurgical engin'), (10, 'Ramona W. Burns', 'Female', 'Electronic typesetting machine operator'), (11, 'Jennifer A. Morrison', 'Female', 'Rigging chaser'), (12, 'Susan M. Juarez', 'Female', 'Control and valve installer'), (13, 'Ellan D. Downie', 'Female', 'Education and training manager'), (14, 'Larry T. Williamson', 'Male', 'Teaching assistant'), (15, 'Lauren M. Reynolds', 'Female', 'Internet developer'), (16, 'Joseph L. Judge', 'Male', 'Refrigeration mechanic'), (17, 'Eric C. Lavelle', 'Male', 'Model'), (18, 'Cheryl T. Smithers', 'Female', 'Personal banker'), (19, 'Tonia J. Diaz', 'Female', 'Facilitator'), (20, 'Stephanie P. Lederman', 'Female', 'Mental health aide'), (21, 'Edward F. Sanchez', 'Male', 'Marine oiler'); 

index.php

          
 
Export HTML table to Excel File using Jquery with PHP
 
 
     ?>
 
Id
 
Name
 
Gender
 
Designation
 
  
 
 
Create Excel File